目录
一、MySQL在服务器中的部署方法
- 在企业中90%的服务器操作系统均为Linux
- 在企业中对于Mysql的安装通常用源码编译的方式来进行
1.1 编译安装MySQL
[root@mysql-node1 ~]# tar zxf mysql-boost-5.7.44.tar.gz
安装依赖性:
要拖入需要软件包:
[root@mysql-node1 ~]# yum install cmake gcc-c++ openssl-devel \
> ncurses-devel.x86_64 libtirpc-devel-1.3.3-8.el9_4.x86_64.rpm rpcgen.x86_64
[root@mysql-node1 ~]# cd mysql-5.7.44/
[root@mysql-node1 ~]# cmake \
> -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
> -DMYSQL_DATADIR=/data/mysql \
> -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \
> -DWITH_EXTRA_CHARSETS=all \
> -DDEFAULT_CHARSET=utf8mb4 \
> -DDEFAULT_COLLATION=utf8mb4_unicode_ci \
> -DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/
[root@mysql-node2 mysql-5.7.44]# make -j4 && make install
1.2 部署MySQL
####################生成启动文件:########################
[root@mysql-node2 mysql]# cd support-files/
[root@mysql-node2 support-files]# cp mysql.server /etc/init.d/mysqld
####################生成配置文件:########################
[root@mysql-node2 support-files]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
#####################修改环境变量:#################
[root@mysql-node2 support-files]# vim ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin/
export PATH
[root@mysql-node2 support-files]# source ~/.bash_profile
#############初始化数据库并建立MySQL基本数据:######################
[root@mysql-node2 ~]# mysqld --initialize --user=mysql
[root@mysql-node2 ~]# /etc/init.d/mysqld start
[root@mysql-node2 ~]# chkconfig mysqld on
[root@mysql-node2 ~]# cd /usr/local/mysql/
[root@mysql-node2 mysql]# useradd -s /sbin/nologin -M mysql
[root@mysql-node2 mysql]# mkdir /data/mysql -p
[root@mysql-node2 mysql]# chown mysql.mysql -R /data/mysql/
#####################数据库安全初始化:############################
[root@mysql-node2 ~]# mysql_secure_installation
进去之后输入初始化的初始密码,新建密码,然后跟着走,两个no,其它全是yes
#######################测试#####################
[root@mysql-node2 ~]# mysql -uroot -p123
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
二、MySQL主从复制
2.1 配置master
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=10
[root@mysql-node1 ~]# /etc/init.d/mysqld restart
[root@mysql-node1 ~]# mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-log Source distribution
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@mysql-node1 ~]# cd /data/mysql/
[root@mysql-node1 mysql]# mysqlbinlog mysql-bin.000001 -vv ---- 查看二进制日志
mysql> CREATE DATABASE xiaoding;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE xiaoding.userlist (
-> username varchar(10) not null,
-> password varchar(50) not null
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO xiaoding.userlist values ('ding1',203);
Query OK, 1 row affected (0.01 sec)
2.2 配置slave
[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
super_read_only=on ---- 让它只能读取,不能写入。
server-id=20
[root@mysql-node2 ~]# /etc/init.d/mysqld restart
[root@mysql-node2 ~]# mysql -uroot -p123
mysql> CHANGE MASTER TO
MASTER_HOST='172.25.254.10',MASTER_USER='ding',MASTER_PASSWORD='123',MASTER_LOG_F
ILE='mysql-bin.000001',MASTER_LOG_POS=(这个值是看你上面master上的Pos值);
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave; #########启动Slave
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.10
Master_User: ding
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-node2-relay-bin.000010
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 746
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: b2e05366-6030-11ef-b06b-000c2909b7ec
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
master上面创建新的数据库表:
[root@mysql-node1 ~]# mysql -uroot -p123
mysql> CREATE DATABASE ding;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE ding.userlist (
-> username varchar(10) not null,
-> password varchar(50) not null
-> );
mysql> insert into ding.userlist values ('ding1','203');
Query OK, 1 row affected (0.01 sec)
mysql> insert into ding.userlist values ('ding2','621');
Query OK, 1 row affected (0.00 sec)
mysql>)
master上查看数据:
mysql> select * from ding.userlist;
+----------+----------+
| username | password |
+----------+----------+
| ding1 | 203 |
| ding2 | 621 |
+----------+----------+
2 rows in set (0.00 sec)
mysql>
在Slave中查看数据是否同步过来
[root@mysql-node2 ~]# mysql -uroot -p123
mysql> select * from ding.userlist;
+----------+----------+
| username | password |
+----------+----------+
| ding1 | 203 |
| ding2 | 621 |
+----------+----------+
2 rows in set (0.00 sec)
mysql>
2.3 添加slave2
master上面把MySQL传到slave2上面:
[root@mysql-node1 ~]# rsync -al -r /usr/local/mysql root@172.25.254.30:/usr/local/
master上备份数据:
[root@mysql-node1 ~]# mysqldump -root -p123 ding > ding.sql ----- 备份数据库
利用scp命令传到slave2上面:
[root@mysql-node1 ~]# scp ding.sql root@172.25.254.30:/root/
slave2 上面:
[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30
[root@mysql-node3 ~]# vim ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin/
export PATH
[root@mysql-node3 ~]# source ~/.bash_profile
[root@mysql-node3 ~]# useradd -s /sbin/nologin -M mysql
[root@mysql-node3 ~]# mkdir /data/mysql -p
[root@mysql-node3 ~]# chown mysql.mysql /data/mysql/ -R
[root@mysql-node3 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
初始化:
[root@mysql-node3 ~]# mysqld --user=mysql --initialize
完了之后看/data/mysql/底下是否有数据,没有就是初始化没弄好
[root@mysql-node3 ~]# ls /data/mysql/
[root@mysql-node3 ~]# /etc/init.d/mysqld start
[root@mysql-node3 ~]# mysql_secure_installation ----- 上面一样的操作
slave2上进行拉平数据:
[root@mysql-node3 ~]# cd /mnt/
[root@mysql-node3 mnt]# ls
xiaoding.sql
[root@mysql-node3 mnt]# mysql -uroot -p123 -e "CREATE DATABASE ding;"
[root@mysql-node3 mnt]# mysql -uroot -p123 ding <ding.sql
要先去master上面查询日志:
[root@mysql-node1 ~]# mysql -uroot -p123
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1511 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
slave上面:
[root@mysql-node3 mnt]# mysql -uroot -p123
mysql> CHANGE MASTER TO
-> MASTER_HOST='172.25.254.10',MASTER_USER='ding',MASTER_PASSWORD='ding',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1511;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql>
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.10
Master_User: ding
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1511
Relay_Log_File: mysql-node3-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
master上面再写个数据:
[root@mysql-node1 ~]# mysql -uroot -p123
mysql> INSERT INTO ding.userlist values ('ding3','456');
Query OK, 1 row affected (0.00 sec)
slave2上面看是否能查到:
[root@mysql-node3 ~]# mysql -uroot -p123 -e "select * from ding.userlist;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| ding1 | 203 |
| ding2 | 621 |
| ding3 | 456 |
+----------+----------+
测试:
[root@mysql-node2 ~]# mysql -uroot -p123 -e "select * from ding.userlist;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| ding1 | 203 |
| ding2 | 621 |
| ding3 | 456 |
+----------+----------+
[root@mysql-node2 ~]#
[root@mysql-node3 ~]# mysql -uroot -p123 -e "select * from ding.userlist;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| ding1 | 203 |
| ding2 | 621 |
| ding3 | 456 |
+----------+----------+
2.4 延迟复制
设置延迟复制,就是为了防止误操作。
不用所有slave都做延迟复制,一台就够了
[root@mysql-node3 ~]# mysql -uroot -p123
mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_DELAY=60;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G;
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: 4e591de3-6167-11ef-a877-000c2909b7ec
Master_Info_File: /data/mysql/master.info
SQL_Delay: 60 ----- 设置的延迟时间
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
在master上面删除一个数据库:
[root@mysql-node1 ~]# mysql -uroot -p123
mysql> delete from ding.userlist where username='ding3';
Query OK, 1 row affected (0.01 sec)
mysql> select * from ding.userlist;
+----------+----------+
| username | password |
+----------+----------+
| ding1 | 203 |
| ding2 | 621 |
+----------+----------+
2 rows in set (0.00 sec)
slave2上面设置了延迟复制:
[root@mysql-node3 ~]# mysql -uroot -p123 -e "select * from ding.userlist;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| ding1 | 203 |
| ding2 | 621 |
| ding3 | 456 |
+----------+----------+
模拟误删了,就可以有时间出来使用mysqldump命令进行备份了。
2.5 慢查询日志
master上查看慢查询:
mysql> SHOW variables like "slow%";
+---------------------+----------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------+
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /data/mysql/mysql-node3-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.00 sec)
现在没开启:
可以把所有的set参数都写到/etc/my.cnf里面去。但是写在这里面需要重启才生效,只有在启动的时候生效
开启慢查询:
[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30
slow_query_log=on ----- 写上这个 开启慢查询
让它立即生效:
mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW variables like "slow%";
+---------------------+----------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------+
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /data/mysql/mysql-node3-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.00 sec)
mysql>
mysql> SHOW VARIABLES like "long%"; ---- 慢查询的时间。
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
mysql>
测试慢查询;
mysql> select sleep (10); ---- 等十秒才会出来
+------------+
| sleep (10) |
+------------+
| 0 |
+------------+
1 row in set (10.00 sec)
mysql>
查看日志:
[root@mysql-node1 ~]# cat /data/mysql/mysql-node1-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /data/mysql/mysql.sock
Time Id Command Argument
# Time: 2024-08-23T18:12:22.387364Z
# User@Host: root[root] @ localhost [] Id: 14
# Query_time: 10.000467 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1724436742;
select sleep (10); ----- 这里可以看到为什么导致慢查询。
[root@mysql-node1 ~]#
2.6 MySQL的并行复制
- 默认情况下slave中使用的是sql单线程回放
- 在master中时多用户读写,如果使用sql单线程回放那么会造成组从延迟严重
- 开启MySQL的多线程回放可以解决上述问题
slave上面看,默认是单线程:
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 6 | system user | | NULL | Connect | 7288 | Waiting for master to send event | NULL |
| 7 | system user | | NULL | Connect | 1227 | Slave has read all relay log; waiting for more updates | NULL |
| 10 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql>
[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
super_read_only=on
gtid_mode=ON ##打开gtid
enforce-gtid-consistency=ON ##保证gtid强一致
slave-parallel-type=LOGICAL_CLOCK ##基于组提交
slave-parallel-workers=16 ##开启的线程数量
master_info_repository=TABLE ##master信息在表中的记录,默认记录在/data/mysql/master.info中 日志都在磁盘里面,会很慢
relay_log_info_repository=TABLE ##回放日志信息在表中记录,默认记录在/data/mysql/relay-log.info中
relay_log_recovery=ON ##日志回放恢复功能开启
[root@mysql-node2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!
[root@mysql-node2 ~]#
查看进程:
[root@mysql-node2 ~]# mysql -uroot -p123
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 2 | system user | | NULL | Connect | 62 | Slave has read all relay log; waiting for more updates | NULL |
| 3 | system user | | NULL | Connect | 62 | Waiting for an event from Coordinator | NULL |
| 4 | system user | | NULL | Connect | 62 | Waiting for an event from Coordinator | NULL |
| 5 | system user | | NULL | Connect | 62 | Waiting for an event from Coordinator | NULL |
| 6 | system user | | NULL | Connect | 62 | Waiting for an event from Coordinator | NULL |
| 7 | system user | | NULL | Connect | 62 | Waiting for an event from Coordinator | NULL |
| 8 | system user | | NULL | Connect | 62 | Waiting for an event from Coordinator | NULL |
| 10 | system user | | NULL | Connect | 62 | Waiting for an event from Coordinator | NULL |
| 11 | system user | | NULL | Connect | 62 | Waiting for an event from Coordinator | NULL |
| 12 | system user | | NULL | Connect | 62 | Waiting for an event from Coordinator | NULL |
| 13 | system user | | NULL | Connect | 62 | Waiting for an event from Coordinator | NULL |
| 14 | system user | | NULL | Connect | 62 | Waiting for an event from Coordinator | NULL |
| 15 | system user | | NULL | Connect | 62 | Waiting for an event from Coordinator | NULL |
| 16 | system user | | NULL | Connect | 62 | Waiting for an event from Coordinator | NULL |
| 17 | system user | | NULL | Connect | 62 | Waiting for an event from Coordinator | NULL |
| 18 | system user | | NULL | Connect | 62 | Waiting for an event from Coordinator | NULL |
| 19 | system user | | NULL | Connect | 62 | Waiting for an event from Coordinator | NULL |
| 20 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
18 rows in set (0.00 sec)
mysql>
这就开启了16个SQL进程
2.7 MySQL主从复制原理剖析
- 实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中,会基于3 个线程来操作,一个主库线程,两个从库线程。二进制日志转储线程(Binlog dump thread)是一个主库线程。当从库线程连接的时候, 主库可以将二进制日志发送给从库,当主库读取事件(Event)的时候,会在 Binlog 上加锁,读取完成之后,再将锁释放掉。
- 从库 I/O 线程会连接到主库,向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分,并且拷贝到本地的中继日志 (Relay log)。
- 从库 SQL 线程会读取从库中的中继日志,并且执行日志中的事件,将从库中的数据与主库保持同步。
- 步骤1:Master将写操作记录到二进制日志(binlog)。
- 步骤2:Slave将Master的binary log events拷贝到它的中继日志(relay log);
- 步骤3:Slave重做中继日志中的事件,将改变应用到自己的数据库中。 MySQL复制是异步的且串行化 的,而且重启后从接入点开始复制。
- slaves端中设置了master端的ip,用户,日志,和日志的Position,通过这些信息取得master的认证及信息
- master端在设定好binlog启动后会开启binlog dump的线程
- master端的binlog dump把二进制的更新发送到slave端的
- slave端开启两个线程,一个是I/O线程,一个是sql线程。i/o线程用于接收master端的二进制日志,此线程会在本地打开relaylog中继日志,并且保存到本地磁盘。sql线程读取本地relog中继日志进行回放
-
什么时候我们需要多个 slave ?当读取的而操作远远高与写操作时。我们采用一主多从架构数据库外层接入负载均衡层并搭配高可用机制
2.8 架构缺陷
- 主从架构采用的是异步机制
- master更新完成后直接发送二进制日志到slave,但是slaves是否真正保存了数据master端不会检测
- master端直接保存二进制日志到磁盘
- 当master端到slave端的网络出现问题时或者master端直接挂掉,二进制日志可能根本没有到达slave
- master出现问题slave端接管master,这个过程中数据就丢失了
- 这样的问题出现就无法达到数据的强一致性,零数据丢失
三、MySQL的半同步模式
- 用户线程写入完成后master中的dump会把日志推送到slave端
- slave中的io线程接收后保存到relaylog中继日志
- 保存完成后slave向master端返回ack
- 在未接受到slave的ack时master端时不做提交的,一直处于等待当收到ack后提交到存储引擎
- 在5.6版本中用到的时after_commit模式,after_commit模式时先提交在等待ack返回后输出ok
3.1 gtid模式
当为启用gtid时我们要考虑的问题
- 在master端的写入时多用户读写,在slave端的复制时单线程日志回放,所以slave端一定会延迟与master端
- 这种延迟在slave端的延迟可能会不一致,当master挂掉后slave接管,一般会挑选一个和master延迟日志最接近的充当新的master
- 那么为接管master的主机继续充当slave角色并会指向到新的master上,作为其slave
- 这时候按照之前的配置我们需要知道新的master上的pos的id,但是我们无法确定新的master和slave之间差多少
激活gtid之后:
- 当master出现问题后,slave2和master的数据最接近,会被作为新的master
- slave1指向新的master,但是他不会去检测新的master的pos id,只需要继续读取自己gtid_next即可
设定gtid:
#####################master上面:##########################
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=10
gtid_mode=ON
enforce-gtid-consistency=ON
[root@mysql-node1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL............ SUCCESS!
Starting MySQL. SUCCESS!
[root@mysql-node1 ~]#
####################slave2上面:#########################
[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30
slow_query_log=on
gtid_mode=ON
enforce-gtid-consistency=ON
[root@mysql-node3 ~]# /etc/init.d/mysqld restart
Shutting down MySQL... SUCCESS!
Starting MySQL. SUCCESS!
[root@mysql-node3 ~]#
[root@mysql-node2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@mysql-node2 ~]#
master上看gtid是否开启:
[root@mysql-node1 ~]# mysqlbinlog -vv /data/mysql/mysql-bin.000002
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
重新设定slave:
[root@mysql-node2 ~]# /etc/init.d/mysqld restart
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to
-> master_host='172.25.254.10',
-> master_user='ding',
-> master_password='ding',
-> master_auto_position=1
-> ;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.10
Master_User: ding
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154 ---- 自动识别,全局ID
Relay_Log_File: mysql-node2-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@mysql-node3 ~]# /etc/init.d/mysqld restart
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to
-> master_host='172.25.254.10',
-> master_user='ding',
-> master_password='ding',
-> master_auto_position=1
-> ;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.10
Master_User: ding
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-node3-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3.2 开启半同步模式
master上面:
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=10
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1 ---- 开启半同步
安装插件:
[root@mysql-node1 ~]# mysql -uroot -p123
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS ---- 查看插件情况
-> FROM INFORMATION_SCHEMA.PLUGINS
-> WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 row in set (0.01 sec)
默认就是开启的,查看是否开启:
mysql> show variables like 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)
mysql>
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
mysql>
这个也能查看
在slave1上也设定半同步:
[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1
[root@mysql-node2 ~]# mysql -uroot -p123
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
[root@mysql-node2 mysql]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@mysql-node2 mysql]#
如果没用重启服务的话:
mysql> STOP SLAVE IO_THREAD; #重启io线程,半同步才能生效
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE IO_THREAD; ##重启io线程,半同步才能生效
Query OK, 0 rows affected (0.00 sec)
slave2:
[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20 #配置server唯一标识号
super_read_only=on
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1
[root@mysql-node3 ~]# mysql -uroot -p123
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
[root@mysql-node3 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@mysql-node3 ~]#
如果没有重启服务的话:
mysql> STOP SLAVE IO_THREAD; #重启io线程,半同步才能生效
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE IO_THREAD; ##重启io线程,半同步才能生效
Query OK, 0 rows affected (0.00 sec)
slave1上面:
mysql> STOP SLAVE IO_THREAD; ---- 关掉IO线程
Query OK, 0 rows affected, 1 warning (0.00 sec)
slave2上面:
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
master上写数据:
mysql> INSERT INTO ding.userlist values('ding3','456');
在这就会卡住
slave1上面:
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
slave2上面:
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
现在就写入不会卡住了,导致数据不一致
四、实现mysql组复制MGR(高可用)
4.1 实现MySQL组复制
############################三台主机都需要写本地解析:##########################
[root@mysql-node1 ~]# vim /etc/hosts
[root@mysql-node2 ~]# vim /etc/hosts
[root@mysql-node3 ~]# vim /etc/hosts
172.25.254.10 mysql-node1.timingding.org
172.25.254.20 mysql-node2.timingding.org
172.25.254.30 mysql-node3.timingding.org
###############################写入配置:##################################
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=10 #配置server唯一标识号
gtid_mode=ON #启用全局事件标识
enforce-gtid-consistency=ON #强制gtid一致
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" #禁用指定存储引擎
master_info_repository=TABLE #复制事件数据到表中而不记录在数据目录中
relay_log_info_repository=TABLE
binlog_checksum=NONE #禁止对二进制日志校验
log_slave_updates=ON #打开数据库中继,
#当slave中sql线程读取日志后也会写入到自己的binlog中
log_bin=binlog #重新指定log名称
binlog_format=ROW #使用行日志格式
plugin_load_add='group_replication.so' #加载组复制插件
transaction_write_set_extraction=XXHASH64 #把每个事件编码为加密散列
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #通知插件正式加入
#或创建的组名
#名称为uuid格式
group_replication_start_on_boot=off #在server启动时不自动启动组复制
group_replication_local_address="172.25.254.10:33061" #指定插件接受其他成员的信息端口
group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061" #本地地址允许访问成员列表
group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8" #主机白名单
group_replication_bootstrap_group=off #不随系统自启而启动
group_replication_single_primary_mode=OFF #使用多主模式
group_replication_enforce_update_everywhere_checks=ON #组同步中有任何改变检测更新
group_replication_allow_local_disjoint_gtids_join=1 #放弃自己信息以master事件为主
[root@mysql-node1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!
[root@mysql-node1 ~]# rm -rf /data/mysql/*
##############################重新初始化:#######################################
[root@mysql-node1 ~]# mysqld --initialize --user=mysql
(X;NP&0qyA:d ---- 记住初始密码
#########################修改密码并输入参数:###################################
[root@mysql-node1 ~]# mysql -uroot -p'(X;NP&0qyA:d'
mysql> alter user root@localhost identified by '123';
Query OK, 0 rows affected (0.01 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER ding@'%' IDENTIFIED BY 'ding';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO ding@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='ding', MASTER_PASSWORD='ding' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=ON; #这里需要开启组信息,因为现在还没有组信息,不开会启动不了组。
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.03 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
| group_replication_applier | 2e311876-61e5-11ef-96d2-000c2909b7ec | mysql-node1.timingding.org | 3306 | ONLINE |
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
1 row in set (0.00 sec)
###############################使用scp命令把配置传到20和30上面:##########################
[root@mysql-node1 ~]# scp /etc/my.cnf root@172.25.254.20:/etc/my.cnf
[root@mysql-node1 ~]# scp /etc/my.cnf root@172.25.254.30:/etc/my.cnf
############################172.25.254.20上面:##################################
[root@mysql-node2 ~]# vim /etc/my.cnf
进去后把表示改为20
还把 信息端口ip改为20
和上面一样的操作:
先暂停MySQL服务,然后删除/data/mysql/*下的文件
重新初始化,改密码,这里就不需要把组给打开,因为已经有组信息了。
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER ding@'%' IDENTIFIED BY 'ding';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO ding@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='ding', MASTER_PASSWORD='ding' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (5.70 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
| group_replication_applier | 2e311876-61e5-11ef-96d2-000c2909b7ec | mysql-node1.timingding.org | 3306 | ONLINE |
| group_replication_applier | 56159d07-61e6-11ef-a90f-000c299766ae | mysql-node2.timingding.org | 3306 | ONLINE |
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
2 rows in set (0.00 sec)
##########################172.25.254.30上面:#################################
[root@mysql-node2 ~]# vim /etc/my.cnf
进去后把表示改为30
还把 信息端口ip改为30
和上面一样的操作:
先暂停MySQL服务,然后删除/data/mysql/*下的文件
重新初始化,改密码,这里就不需要把组给打开,因为已经有组信息了。
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER ding@'%' IDENTIFIED BY 'ding';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO ding@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='ding', MASTER_PASSWORD='ding' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (3.06 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
| group_replication_applier | 2e311876-61e5-11ef-96d2-000c2909b7ec | mysql-node1.timingding.org | 3306 | ONLINE |
| group_replication_applier | 56159d07-61e6-11ef-a90f-000c299766ae | mysql-node2.timingding.org | 3306 | ONLINE |
| group_replication_applier | 8a71f4f2-61e6-11ef-be9c-000c2908e85e | mysql-node3.timingding.org | 3306 | ONLINE |
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
3 rows in set (0.00 sec)
五、mysql-router(mysql路由)
###############################安装所需软件包:#####################################
[root@mysql-node1 ~]# rpm -ivh mysql-router-community-8.4.0-1.el7.x86_64.rpm
[root@mysql-node1 ~]# vim /etc/mysqlrouter/mysqlrouter.conf
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.254.10:3306,172.25.254.20:3306,172.25.254.30:3306
routing_strategy = round-robin
[root@mysql-node1 ~]# systemctl start mysqlrouter.service
[root@mysql-node1 ~]# netstat -antlupe | grep 7001
tcp 0 0 0.0.0.0:7001 0.0.0.0:* LISTEN 988 150547 41013/mysqlrouter
[root@mysql-node1 ~]#
172.25.254.20和172.25.254.30上面创建远程登录用户:
[root@mysql-node2 ~]# mysql -uroot -p123
mysql> create user root@'%' identified by 'ding';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all ON *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)
[root@mysql-node3 ~]# mysql -uroot -p123
mysql> create user root@'%' identified by 'ding';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all ON *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)
测试:
[root@mysql-node1 ~]# mysql -uroot -pding -h172.25.254.10 -P 7001
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 30 |
+-------------+
1 row in set (0.00 sec)
重复来一次:
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 20 |
+-------------+
1 row in set (0.00 sec)
六、MySQL高可用(MHA)
MHA是用来解决单点故障问题的
- MHA(Master High Availability)是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。
- MHA 的出现就是解决MySQL 单点的问题。
- MySQL故障切换过程中,MHA能做到0-30秒内自动完成故障切换操作。
- MHA能在故障切换的过程中最大程度上保证数据的一致性,以达到真正意义上的高可用。
- MHA由两部分组成:MHAManager (管理节点) MHA Node (数据库节点),MHA Manager 可以单独部署在一台独立的机器上管理多个master-slave集群,也可以部署在一台 slave 节点上。
- MHA Manager 会定时探测集群中的 master 节点。
- 当 master 出现故障时,它可以自动将最新数据的 slave 提升为新的 master, 然后将所有其他的
- slave 重新指向新的 master
- 自动故障切换过程中,MHA从宕机的主服务器上保存二进制日志,最大程度的保证数据不丢失
- 使用半同步复制,可以大大降低数据丢失的风险,如果只有一个slave已经收到了最新的二进制日志,MHA可以将最新的二进制日志应用于其他所有的slave服务器上,因此可以保证所有节点的数据一致性
- 目前MHA支持一主多从架构,最少三台服务,即一主两从
- 目前MHA主要支持一主多从的架构,要搭建MHA,要求一个复制集群必须最少有3台数据库服务器, 一主二从,即一台充当Master,台充当备用Master,另一台充当从库。
- MHA Node 运行在每台 MySQL 服务器上
- MHAManager 会定时探测集群中的master 节点
- 当master 出现故障时,它可以自动将最新数据的slave 提升为新的master
- 然后将所有其他的slave 重新指向新的master,VIP自动漂移到新的master。
- 整个故障转移过程对应用程序完全透明。
6.1 搭建一主两从架构
配置master:
master:
[root@mysql-node1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!
[root@mysql-node1 ~]# rm -rf /data/mysql/*
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=10
gtid_mode=ON
enforce-gtid-consistency=ON
[root@mysql-node1 ~]# mysqld --user mysql --initialize
[root@mysql-node1 ~]# /etc/init.d/mysqld start
[root@mysql-node1 ~]# mysql -uroot -p';Hw>;ltvO9lZ'
mysql> alter user root@localhost identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER 'ding'@'%' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO ding@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 | 194 | | | 7bc1f80c-621d-11ef-8080-000c2909b7ec:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql>
配置slave:
slave1:
[root@mysql-node2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!
[root@mysql-node2 ~]# vim /etc/my.cnf
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=10
gtid_mode=ON
enforce-gtid-consistency=ON
[root@mysql-node2 ~]# rm -rf /data/mysql/*
[root@mysql-node2 ~]# mysqld --user mysql --initialize
[root@mysql-node2 ~]# /etc/init.d/mysqld start
[root@mysql-node2 ~]# mysql -uroot -p'UXaxjPBa2e>p'
mysql> alter user root@localhost identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='ding',MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.10
Master_User: ding
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 194
Relay_Log_File: mysql-node2-relay-bin.000004
Relay_Log_Pos: 407
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
配置slave2:
slave2:
[root@mysql-node3 ~]# /etc/init.d/mysqld stop
Shutting down MySQL........ SUCCESS!
[root@mysql-node3 ~]# rm -rf /data/mysql/*
[root@mysql-node3 ~]# mysqld --user mysql --initialize
[root@mysql-node3 ~]# /etc/init.d/mysqld start
[root@mysql-node3 ~]# mysql -uroot -p'18Pj-yLVX/wx'
mysql> alter user root@localhost identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='ding',MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.10
Master_User: ding
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 194
Relay_Log_File: mysql-node3-relay-bin.000004
Relay_Log_Pos: 407
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6.2 安装MHA所需要的软件
#在MHA中
[root@mysql-mha ~]# unzip MHA-7.zip
[root@mysql-mha MHA-7]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
mha4mysql-manager-0.58.tar.gz perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
mha4mysql-node-0.58-0.el7.centos.noarch.rpm perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm perl-Net-Telnet-3.03-19.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[root@mysql-mha MHA-7]# yum install *.rpm -y
[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm
root@172.25.254.10:/mnt
[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm
root@172.25.254.20:/mnt
[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm
root@172.25.254.30:/mnt
#在sql-node中
[root@mysql-node10 ~]# yum install /mnt/mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
[root@mysql-node20 ~]# yum install /mnt/mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
[root@mysql-node30 ~]# yum install /mnt/mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
1.Manager工具包主要包括以下几个工具:
masterha_check_ssh #检查MHA的SSH配置状况
masterha_check_repl #检查MySQL复制状况
masterha_manger #启动MHA
masterha_check_status #检测当前MHA运行状态
masterha_master_monitor #检测master是否宕机
masterha_master_switch #控制故障转移(自动或者手动)
masterha_conf_host #添加或删除配置的server信息
2.Node工具包 (通常由masterHA主机直接调用,无需人为执行)
save_binary_logs #保存和复制master的二进制日志
apply_diff_relay_logs #识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog #去除不必要的ROLLBACK事件(MHA已不再使用这个工具)
purge_relay_logs #清除中继日志(不会阻塞SQL线程)
6.2.1 MHA的故障切换
6.2.1.1 MHA故障手动切换
[root@mysql-mha ~]# masterha_master_switch \
> --conf=/etc/masterha/app1.cnf \
> --master_state=alive \
> --new_master_host=172.25.254.20 \ ----- 切换的新的master主机
> --new_master_port=3306 \
> --orig_master_is_new_slave \ ------ 故障的转换为slave
> --running_updates_limit=10000
172.25.254.10上面去检测:
[root@mysql-node1 ~]# mysql -uroot -p123
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.20 --- master指向了20
Master_User: ding
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-node1-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如何切换回来:
[root@mysql-mha ~]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.10 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
再去172.25.254.10上面查看master是否回来:
[root@mysql-node1 ~]# mysql -uroot -p123
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 | 833 | | | 7bc1f80c-621d-11ef-8080-000c2909b7ec:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql>
再去172.25.254.20上查看是否指向master为10:
[root@mysql-node2 ~]# mysql -uroot -p123
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.10
Master_User: ding
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 833
Relay_Log_File: mysql-node2-relay-bin.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6.2.1.2 模拟故障时的手动切换
把当前的master给down掉:
[root@mysql-node1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!
[root@mysql-node1 ~]#
[root@mysql-mha ~]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.25.254.10 --dead_master_port=3306 --new_master_host=172.25.254.20 --new_master_port=3306 --ignore_last_failover--dead_master_ip=<dead_master_ip> is not set. Using 172.25.254.10.
最后显示成功:
Started manual(interactive) failover.
Selected 172.25.254.20(172.25.254.20:3306) as a new master.
172.25.254.20(172.25.254.20:3306): OK: Applying all logs succeeded.
172.25.254.30(172.25.254.30:3306): OK: Slave started, replicating from 172.25.254.20(172.25.254.20:3306)
172.25.254.20(172.25.254.20:3306): Resetting slave info succeeded.
Master failover to 172.25.254.20(172.25.254.20:3306) completed successfully.
查看20是否成为master:
[root@mysql-node2 ~]# mysql -uroot -p123
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 7bc1f80c-621d-11ef-8080-000c2909b7ec:1-6
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
6.2.1.3 恢复故障的mysql节点:
启动mysql:
[root@mysql-node1 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
[root@mysql-node1 ~]#
[root@mysql-node1 ~]# mysql -uroot -p123
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.20',MASTER_USER='ding',MASTER_PASSWORD='123',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.20
Master_User: ding
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-node1-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
检测一主两从是否正常:
[root@mysql-mha ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf
Sun Aug 25 10:57:32 2024 - [info] Checking replication health on 172.25.254.10..
Sun Aug 25 10:57:32 2024 - [info] ok.
Sun Aug 25 10:57:32 2024 - [info] Checking replication health on 172.25.254.30..
Sun Aug 25 10:57:32 2024 - [info] ok.
Sun Aug 25 10:57:32 2024 - [warning] master_ip_failover_script is not defined.
Sun Aug 25 10:57:32 2024 - [warning] shutdown_script is not defined.
Sun Aug 25 10:57:32 2024 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
6.2.2 自动切换故障master
[root@mysql-mha ~]# cd /etc/masterha/
[root@mysql-mha masterha]# ls
app1.cnf app1.failover.complete
[root@mysql-mha masterha]# rm -rf app1.failover.complete -------- 删掉切换的锁文件
[root@mysql-mha masterha]# masterha_manager --conf=/etc/masterha/app1.cnf #使用这个命令进行监控
[root@mysql-node2 ~]# ip a a 172.25.254.11/24 dev eth0 ----- 20上给上临时ip172.25.254.11 ,之前配置的
现在把master挂掉:
[root@mysql-node1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!
复制会话172.25.254.50会话:
[root@mysql-mha masterha]# cat manager.log
Started automated(non-interactive) failover.
Selected 172.25.254.20(172.25.254.20:3306) as a new master.
172.25.254.20(172.25.254.20:3306): OK: Applying all logs succeeded.
172.25.254.30(172.25.254.30:3306): OK: Slave started, replicating from 172.25.254.20(172.25.254.20:3306)
172.25.254.20(172.25.254.20:3306): Resetting slave info succeeded.
Master failover to 172.25.254.20(172.25.254.20:3306) completed successfully.
最后显示成功了
去172.25.254.30上面看master是否到20上面了:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.20
Master_User: ding
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-node3-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
切换成功。
6.2.2.1 恢复节点
恢复之后就不是master了,变为slave
[root@mysql-node1 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.20', MASTER_USER='ding',MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.20
Master_User: ding
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-node1-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
每次做完自动切换之后会自动生成锁文件,需要把它删掉。
[root@mysql-mha masterha]# ls
app1.cnf app1.failover.complete manager.log
[root@mysql-mha masterha]# rm -rf app1.failover.complete
6.2.3 为MHA添加vip功能
6.2.3.1 自动切换模拟故障
复制脚本到目录下面并给上执行权限:
[root@mysql-mha ~]# cp master_ip_failover master_ip_online_change /usr/local/bin/
[root@mysql-mha ~]# chmod +x /usr/local/bin/master_ip_*
[root@mysql-mha ~]# vim /usr/local/bin/master_ip_failover
my $vip = '172.25.254.100/24'; ---- 修改这个vip就行。
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
[root@mysql-mha ~]# vim /usr/local/bin/master_ip_online_change
my $vip = '172.25.254.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
my $exit_code = 0;
手动在master上添加vip:
[root@mysql-node2 ~]# ip a a 172.25.254.100/24 dev eth0
[root@mysql-mha ~]# vim /etc/masterha/app1.cnf
master_ip_failover_script= /usr/local/bin/master_ip_failover ---- 这两个打开
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
master_ip_online_change_script= /usr/local/bin/master_ip_online_change
自动切换监控命令:
[root@mysql-mha masterha]# masterha_manager --conf=/etc/masterha/app1.cnf
把master挂掉:
[root@mysql-node1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!
查看vip是否到20上面:
[root@mysql-node2 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:97:66:ae brd ff:ff:ff:ff:ff:ff
inet 172.25.254.20/24 brd 172.25.254.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet 172.25.254.11/24 scope global secondary eth0
valid_lft forever preferred_lft forever
inet 172.25.254.100/24 scope global secondary eth0 ----- vip飘过来了
6.2.3.2 手动切换master
[root@mysql-mha masterha]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.10 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000;
此时master又变成10了,vip也随之迁回来了。
[root@mysql-node1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:09:b7:ec brd ff:ff:ff:ff:ff:ff
inet 172.25.254.10/24 brd 172.25.254.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet 172.25.254.100/24 scope global secondary eth0