mysql(十三)

作者: Freestyle_0f85 | 来源:发表于2019-12-16 14:29 被阅读0次

mysql(十三)
「mysql优化专题」本专题总结终章(13)
MySql（十三）条件、循环结构
三十三：Django配置使用mysql
二十三、docker 安装mysql
Python（二十三）MySQL提升
Mysql数据库
MySQL教程
mysql密码修改
部署LAMP发布使用PHP语言开发的网站之MySQL安装

MySQL环境准备

主机名	wanIP	lanIP	角色
db01	10.0.0.51	172.16.1.51	主库,MHA客户端
db02	10.0.0.52	172.16.1.52	从库,MHA客户端
db03	10.0.0.53	172.16.1.53	从库,MHA客户端
db04	10.0.0.54	172.16.1.54	从库,MHA客户端
MHA-manager	10.0.0.53	172.16.1.53	MHA服务端

1.停库
[root@db01 ~]# /etc/init.d/mysqld stop
2.删除data目录
[root@db01 ~]# rm -fr /application/mysql/data
3.初始化配置文件
[root@db01 support-files]# cp my-default.cnf /etc/my.cnf
cp: overwrite ‘/etc/my.cnf’? y
4.初始化MySQL
[root@db01 support-files]# cd /application/mysql/scripts/
[root@db01 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data
5.启动数据库
[root@db01 scripts]# /etc/init.d/mysqld start

MySQL高可用方案

1.双主 + keepalived

image.png

2.MMM
3.MHA
4.MGR（半同步复制）

MHA简介

image

松信嘉範： MySQL/Linux专家 2001年索尼公司入职 2001年开始使用oracle 2004年开始使用MySQL 2006年9月-2010年8月MySQL从事顾问 2010年-2012年 DeNA 2012年~至今 Facebook

MHA原理

当Master出现故障时，它可以自动将最新数据的Slave提升为新的Master,然后将所有其他的Slave重新指向新的Master。

image.png

MHA架构介绍

image

1.MHA是C/S结构的服务
2.MHA的manager可以装在任何一台服务器上（尽量不要装在主库上）
3.一个MHA可以管理多套MySQL集群（上百套）
4.服务端：manager，客户端：node

MHA的工具

#MHA manager 服务端工具
[root@db01 ~]# tar xf mha4mysql-manager-0.56.tar.gz
[root@db01 ~]# cd /root/mha4mysql-manager-0.56/bin
[root@db01 bin]# ll
total 40
-rwxr-xr-x 1 4984 users 1995 Apr  1  2014 masterha_check_repl
-rwxr-xr-x 1 4984 users 1779 Apr  1  2014 masterha_check_ssh
-rwxr-xr-x 1 4984 users 1865 Apr  1  2014 masterha_check_status
-rwxr-xr-x 1 4984 users 3201 Apr  1  2014 masterha_conf_host
-rwxr-xr-x 1 4984 users 2517 Apr  1  2014 masterha_manager
-rwxr-xr-x 1 4984 users 2165 Apr  1  2014 masterha_master_monitor
-rwxr-xr-x 1 4984 users 2373 Apr  1  2014 masterha_master_switch
-rwxr-xr-x 1 4984 users 5171 Apr  1  2014 masterha_secondary_check
-rwxr-xr-x 1 4984 users 1739 Apr  1  2014 masterha_stop

image.png

#MHA node客户端工具
[root@db01 ~]# tar xf mha4mysql-node-0.56.tar.gz
[root@db01 ~]# cd mha4mysql-node-0.56/bin/

[root@db01 bin]# ll
total 44
-rwxr-xr-x 1 4984 users 16367 Apr  1  2014 apply_diff_relay_logs
-rwxr-xr-x 1 4984 users  4807 Apr  1  2014 filter_mysqlbinlog
-rwxr-xr-x 1 4984 users  8261 Apr  1  2014 purge_relay_logs
-rwxr-xr-x 1 4984 users  7525 Apr  1  2014 save_binary_logs

image.png

MHA的优点总结

1）Masterfailover and slave promotion can be done very quickly
自动故障转移快 10 - 30 秒

2）Mastercrash does not result in data inconsistency
主库崩溃不存在数据一致性问题

3）Noneed to modify current MySQL settings (MHA works with regular MySQL)
不需要对当前mysql环境做重大修改

4）Noneed to increase lots of servers
不需要添加额外的服务器(仅一台manager就可管理上百个replication)

5）Noperformance penalty
性能优秀，可工作在半同步复制和异步复制，当监控mysql状态时，仅需要每隔N秒向master发送ping包(默认3秒)，所以对性能无影响。你可以理解为MHA的性能和简单的主从复制框架性能一样。select ping

6）Works with any storage engine
只要replication支持的存储引擎，MHA都支持，不会局限于innodb

基于GTID的主从复制

什么是GTID？

GTID是一个唯一标识符，主库的UUID+ 事务提交号，TID组成

## UUID MySQL实例的ID
[root@db01 data]# cat auto.cnf 
[auto]
server-uuid=6b898418-23ec-11ea-9271-000c29e98743

TID：事务提交号

GTID

6b898418-23ec-11ea-9271-000c29e98743:4

GTID新特性

支持多线程复制（主库：dump线程，从库：IO线程和每一个库都开启一个SQL线程）
无需再找binlog的名字和位置点，（show master status；file/position）
- change master to
- master_host='10.0.0.51',
- master_user='rep',
- master_password='123',
- master_auto_position=1;
```
#1.grant replication slave on \*.\* to rep@'%' identified by '123';
#2.在从库上执行以上change语句
#3.start slave；
```
基于Row复制只保存改变的列,大大节省Disk Space/Network resources和Memory usage.
支持把Master 和Slave的相关信息记录在Table中，原来是记录在文件里,记录在表里,增强可用性
支持延时复制

GTID实践

mysql> show variables like '%gtid%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| binlog_gtid_simple_recovery     | OFF       |
| enforce_gtid_consistency        | OFF       |## 开启
| gtid_executed                   |           |
| gtid_mode                       | OFF       |## 开启
| gtid_next                       | AUTOMATIC |
| gtid_owned                      |           |
| gtid_purged                     |           |
| simplified_binlog_gtid_recovery | OFF       |
+---------------------------------+-----------+

[root@db01 data]# vim /etc/my.cnf

[mysqld]
gtid_mode=ON
enforce_gtid_consistency
log-bin=mysql-bin
binlog_format=row
log-slave-updates
server_id=1

#1.级联复制
#2.双主+keepalived
#3.gtid

[root@db01 data]# /etc/init.d/mysqld restart‘

#创建主从复制用户
mysql> grant replication slave on *.* to rep@'%' identified by '123';

#从库执行change master
change master to
master_host='10.0.0.51',
master_user='rep',
master_password='123',
master_auto_position=1;

#开启主从复制
mysql> start slave;

#检查主从复制状态
mysql> show slave status\G
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000004 |      191 |              |                  | 6b898418-23ec-11ea-9271-000c29e98743:1 |
+------------------+----------+--------------+------------------+----------------------------------------+


[root@db01 data]# tail -100 /application/mysql/data/db01.err

image.png

报错原因：如果想要开启gtid-mode参数，必须依赖 log-bin 和 log-slave-updates

解决办法：

[root@db01 data]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
log-slave-updates

image.png

报错原因：必须要开启GTID

解决办法：

[root@db03 scripts]# vim /etc/my.cnf
[mysqld]
server_id=3
gtid_mode=ON
enforce_gtid_consistency
log-bin=mysql-bin
binlog_format=row
log-slave-updates

基于GTID的MHA

传统

1.主库要开启binlog，从库不用开启binlog

2.主库要有中从复制用户，从库不需要创建主从复制用户

3.主库server_id=5,从库只要不等于5即可，从库之间可以相同

基于MHA的主从复制

1.主库要开启binlog，从库也要开启binlog

2.主库要创建主从复制用户，从库也必须创建主从复制用户

3.主库server_id=5，从库只要不等于5即可，从库之间必须不相同

<font color='red'>从库随时会被提升为新的主库</font>

1.关闭所有数据库自动清除relay log 的功能

主库要关闭
从库也要关闭
临时+永久关闭

2.从库要设置为只读开启read-only功能

主库不开启
从库要开启
临时开启

#临时关闭自动清除relay log功能（所有库）
set global relay_log_purge = 0;
#永久关闭自动清除relay log功能（所有库）
[root@db01 data]# vim /etc/my.cnf
relay_log_purge = 0

#临时开启只读功能（所有从库）
set global read_only=1;

部署MHA

image.png

[root@db01 ~]# ll
-rw-r--r-- 1 root root 36326 Dec 16 08:35 mha4mysql-node-0.56-0.el6.noarch.rpm

[root@db02 ~]# ll
-rw-r--r--  1 root root     87119 Dec 16 08:35 mha4mysql-manager-0.56-0.el6.noarch.rpm
-rw-r--r--  1 root root     36326 Dec 16 08:35 mha4mysql-node-0.56-0.el6.noarch.rpm

[root@db03 ~]# ll
-rw-r--r-- 1 root root 36326 Dec 16 08:35 mha4mysql-node-0.56-0.el6.noarch.rpm

[root@db04 ~]# ll
-rw-r--r-- 1 root root 36326 Dec 16 08:35 mha4mysql-node-0.56-0.el6.noarch.rpm

## 安装包下载地址
https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads

# 安装node
[root@db01 ~]# yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm
[root@db02 ~]# yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm
[root@db03 ~]# yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm
[root@db04 ~]# yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm

#安装manager
[root@db02 ~]# yum localinstall -y mha4mysql-manager-0.56-0.el6.noarch.rpm 

#创建MHA管理用户（所有库都要创建）
mysql> grant all on *.* to mha@'%' identified by 'mha';

#做命令的软链接
[root@db01 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@db01 ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql

[root@db02 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@db02 ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql

[root@db03 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@db03 ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql

[root@db04 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@db04 ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql

#创建密钥对
[root@db01 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db01 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.51
[root@db01 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.52
[root@db01 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.53
[root@db01 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.54

----

[root@db02 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db02 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.51
[root@db02 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.52
[root@db02 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.53
[root@db02 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.54

----

[root@db03 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db03 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.51
[root@db03 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.52
[root@db03 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.53
[root@db03 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.54

----

[root@db04 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db04 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.51
[root@db04 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.52
[root@db04 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.53
[root@db04 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.54


# 写MHA配置文件
[root@db02 ~]# mkdir /etc/mha
[root@db02 ~]# vim /etc/mha/app1.cnf
[server default]
manager_log=/etc/mha/app1/manager.log
manager_workdir=/etc/mha/app1
master_binlog_dir=/application/mysql/data
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=rep
ssh_user=root
ssh_port=22

[server1]
hostname=10.0.0.51
port=3306


[server2]
#candidate_master=1
#check_repl_delay=0
hostname=10.0.0.52
port=3306


[server3]
hostname=10.0.0.53
port=3306

[server4]
hostname=10.0.0.54
port=3306

## 启动前测试
[root@db02 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
Mon Dec 16 12:12:11 2019 - [info] All SSH connection tests passed successfully.

[root@db02 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
MySQL Replication Health is OK.

#启动MHA
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/app1/manager.log 2>&1 &

#检测MHA启动状态
[root@db02 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:121442) is running(0:PING_OK), master:10.0.0.51

#停止MHA
[root@db02 ~]# masterha_stop --conf=/etc/mha/app1.cnf

image.png

报错原因：将IP解析成主机名了

解决方法：

#在配置文件中，添加一个跳过反向解析的参数
skip_name_resolve

mysql(十三)
MySQL环境准备主机名wanIPlanIP角色db0110.0.0.51172.16.1.51主库,MHA客户...
「mysql优化专题」本专题总结终章(13)
一个月过去了，【mysql优化专题】围绕着mysql优化进行了十三篇的优化文章，下面进行一次完整的总结！我尝试用最...
MySql（十三）条件、循环结构
一、条件结构 (1)if 语句 if条件表达式1 then 语句块1; elseif条件表达式2 then 语...
三十三：Django配置使用mysql
一：配置数据库在settings.py文件的DATABASES中二：启动项目命令：python3 manag...
二十三、docker 安装mysql
https://hub.docker.com/_/mysql http://www.runoob.com/dock...
Python（二十三）MySQL提升
今天介绍四个程序员岗位方向：摸鱼、开发、测试、运维。摸鱼：本来一天可以完成的事情，花好几天的时间做，各个行业都有...
Mysql数据库
MySQL知识图谱 MySQL索引 MySQL架构 MySQL锁 MySQL事务 MySQL集群 MySQL分库分...
MySQL教程
MySQL 入门教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL ...
mysql密码修改
use mysql; mysql -uroot -p mysql> use mysql; mysql> updat...
部署LAMP发布使用PHP语言开发的网站之MySQL安装
mysql 安装 mysql介绍 mysql安装 mysql启动 mysql测试一、MySQL介绍瑞典AB公司...