本文详细介绍了MySQL主从复制的配置与故障转移，包括如何在已有大量数据的主节点上添加从节点，以及在主服务器宕机时提升从节点为新master。此外，还探讨了通过MHA 0.58搭建数据库集群，以及Percona XtraDB Cluster(PXC 5.7)的实战案例，最后讲解了如何使用Ansible部署二进制MySQL 8.0.19。
MySQL主从复制
1、如果主节点已经运行了一段时间，且有大量数据时，新增一个slave，如何配置并启动新增slave节点
-
思路步骤：
- 通过备份恢复数据至从服务器
- 复制起始位置为备份时，二进制日志文件及其POS
-
环境准备：
mysql-master: 10.0.0.8 slave-server: 10.0.0.18 newslave-server: 10.0.0.28
-
操作步骤：
（1）新建主从复制
1、主节点10.0.0.8： [root@master ~]#dnf -y install mariadb-server [root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=8 log-bin [root@master profile.d]#systemctl restart mariadb [root@master profile.d]#mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.3.17-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> #查看二进制文件和位置 MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 28212 | | mariadb-bin.000002 | 344 | +--------------------+-----------+ 2 rows in set (0.001 sec) #创建复制用户 MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%'identified by '123456'; 2、从节点： [root@salve ~]#dnf -y install mariadb-server [root@salve ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=18 log-bin read-only [root@slave ~]#systemctl restart mariadb [root@salve ~]#mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.3.17-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> change master to master_host='10.0.0.8', master_user='repluser', master_password='123456', master_port=3306, master_log_file='mariadb-bin.000002',master_log_pos=344; Query OK, 0 rows affected (0.009 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.004 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.8 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 540 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 753 Relay_Master_Log_File: mariadb-bin.000002 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: 540 Relay_Log_Space: 1064 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: 8 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Slave_DDL_Groups: 1 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.000 sec)（2）主服务器运行一段时间后，有大量数据，新增从节点服务器
1、在主服务器完全备份 [root@master ~]#mkdir /backup [root@master ~]#mysqldump -A -F --single-transaction --master-data=1 > /backup/fullbackup_`date +%F_%T`.sql root@master ~]#ll /backup total 480 -rw-r--r--. 1 root root 487931 Oct 17 15:28 fullbackup_2020-10-17_15:28:10.sql [root@master ~]#scp /backup/fullbackup_2020-10-17_15\:28\:10.sql 10.0.0.28:/data #建议优化主和从节点服务器的性能 MariaDB [(none)]> set global sync_binlog=0; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> set global innodb_flush_log_at_trx_commit=2; Query OK, 0 rows affected (0.000 sec) 2、将完全备份还原到新的从节点 [root@newsalve ~]#dnf -y install mariadb-server [root@newsalve ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=28 log-bin read-only [root@newsalve ~]#systemctl restart mariadb #配置从节点，从完全备份的位置之后开始复制 [root@newsalve ~]#grep '^CHANGE MASTER' /data/fullbackup_2020-10-17_15\:28\:10.sql CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000004', MASTER_LOG_POS=389; [root@newsalve ~]#vim /data/fullbackup_2020-10-17_15\:28\:10.sq CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000004', MASTER_LOG_POS=389; [root@newsalve ~]#mysql < /data/fullbackup_2020-10-17_15\:28\:10.sql [root@newsalve ~]#mysql MariaDB [(none)]> start slave; MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.8 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000004 Read_Master_Log_Pos: 389 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 557 Relay_Master_Log_File: mariadb-bin.000004 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: 389 Relay_Log_Space: 868 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: 8 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.001 sec)
2、当master服务器宕机，提升一个slave成为新的master
（1） 找到哪个从节点的数据库是最新，让它成为新master
[root@salve ~]#cat /var/lib/mysql/relay-log.info
5
./mariadb-relay-bin.000006
690
mariadb-bin.000004
389
0
0
[root@newsalve ~]#cat /var/lib/mysql/relay-log.info
5
./mariadb-relay-bin.000002
557
mariadb-bin.000004
389
0
上面两个从节点在相同状态点389,可以任选一个作为新master
（2）新master修改配置文件，关闭read-only配置
这里选择10.0.0.18为新master
[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
read-only=OFF
log-bin=/data/mysql/logbin/mysql-bin
（3）在新master上清除旧的master复制信息并完全备份
#清除旧的master复制信息
[root@salve ~]#mysql
MariaDB [(none)]> set global read_only=off;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.005 sec)
MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.001 sec)
#在新master上完全备份
[root@salve ~]#mysql
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28210 |
| mariadb-bin.000002 | 344 |
+--------------------+-----------+
2 rows in set (0.001 sec)
[root@salve ~]#mysqldump -A --single-transaction --master-data=1 -F > allbackup.sql
[root@salve ~]#scp allbackup.sql 10.0.0.28:/root
（4）分析旧的master 的二进制日志，将未同步到至新master的二进制日志导出来，恢复到新master,尽可能恢复数据
因为此实验两从节点都是最新状态，故无需此步
（5）其它所有 slave 重新还原数据库，指向新的master
[root@newsalve ~]#vim allbackup.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.18',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000003',
MASTER_LOG_POS=389;
[root@newsalve ~]#mysql
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.007 sec)
MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> set sql_log_bin=off;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> source allbackup.sql;
MariaDB [(none)]>set sql_log_bin=on;
MariaDB [(none)]>start slave;
MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.18
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000003
Read_Master_Log_Pos: 389
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: mariadb-bin.000003
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: 389
Relay_Log_Space: 868
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: 18
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.001 sec)
MySQL集群
3、通过 MHA 0.58 搭建一个数据库集群结构
说明: mha4mysql-manager-0.58-0.el7.centos.noarch.rpm ,支持MySQL 5.7 ,但和CentOS8版本上的Mariadb -10.3.17不兼容
（1）在管理节点上安装两个包mha4mysql-manager和mha4mysql-node
[root@mha-manager ~]#yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
[root@mha-manager ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
(2) 在所有MySQL服务器上安装mha4mysql-node包
[root@master ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@slave1 ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@slave2 ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
(3) 在所有节点实现相互之间ssh key验证
[root@mha-manager ~]#ssh-keygen
[root@mha-manager ~]#ssh-copy-id 127.0.0.1
[root@mha-manager ~]#rsync -av .ssh 10.0.0.8:/root/
[root@mha-manager ~]#rsync -av .ssh 10.0.0.18:/root/
[root@mha-manager ~]#rsync -av .ssh 10.0.0.28:/root/
(4) 在管理节点建立配置文件
[root@mha-manager ~]#mkdir /etc/mastermha/
[root@mha-manager ~]#vim /etc/mastermha/app1.cnf
[server default]
user=mhauser
#用于远程连接MySQL所有节点的用户,需要有管理员的权限
password=magedu
manager_workdir=/data/mastermha/app1/
#目录会自动生成,无需手动创建
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
#用于实现远程ssh基于KEY的连接,访问二进制日志
repl_user=repluser
#主从复制的用户信息
repl_password=magedu
ping_interval=1
#健康性检查的时间间隔
master_ip_failover_script=/usr/local/bin/master_ip_failover
#切换VIP的perl脚本
report_script=/usr/local/bin/sendmail.sh
#当执行报警脚本
check_repl_delay=0
#默认如果slave中从库落后主库relaylog超过100M，主库不会选择这个从库为新的master，因为这个从库进行恢复需要很长的时间.通过这个参数，mha触发主从切换的时候会忽略复制的延时，通过check_repl_delay=0这个参数，mha触发主从切换时会忽略复制的延时，对于设置candidate_master=1的从库非常有用，这样确保这个从库一定能成为最新的master
master_binlog_dir=/data/mysql/
#指定二进制日志存放的目录,mha4mysql-manager-0.58必须指定,之前版本不需要指定
[server1]
hostname=10.0.0.8
candidate_master=1
[server2]
hostname
最低0.47元/天 解锁文章
扫一扫
243
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
