11.数据库主从复制 1.MySQL数据库传统复制 2.MySQL数据库Gtid复制 3.MySQL数据库多源复制 4.MysQL数据库读写分离 5.MySQL数据库架构演变 Mysql的主从架构模式，是很多企业⼴泛使⽤，并且是⼴为熟知的⼀种架构模式，这是 DBA 所应该熟练掌握的技 能。 1.mysql主从复制主要⽤途 a.⽤于备份，避免影响业务 b.实时灾备，⽤于故障切换 c.读写分离，提供查询服务 2.mysql主从复制存在的问题 a.主库宕机后, 数据可能丢失 b.主库写压⼒⼤, 复制可能会延时 3.mysql主从复制解决⽅法 a. 半同步复制 、或者 全同步复制 .要求: Mysql5.7版本 b. 并⾏复制 , 解决从库复制延迟的问题. 建议: 5.7版 半同步复制是指主库在把数据写⼊⼆进制⽇志之前，等待⾄少⼀个从库确认收到数据。 全同步复制是指主库在把数据写⼊⼆进制⽇志之前，等待所有从库确认收到数据。这种⽅法需要MySQL 5.7版本以上⽀持。 4 .mysql主从复制原理 0.依赖 主库必须打开binlog 1.在主库上把将更改 DDL DML DCL 记录到⼆进制⽇志 Binary Log 中。 2.备库 I/O 线程将主库上的⼆进制⽇志复制到⾃⼰的中继⽇志 Relay Log 中。 3.备库 SQL 线程读取中继⽇志中的事件，将其重放到备库数据库之上。
1.MySQL数据库传统主从复制 数据库传统主从复制如下, 如需实现多从, 将以下实验从案例在配置⼀次即可
/etc/hosts ⽂件解析 10.1.106.70 master1 10.1.106.66 slave1 Master 配置 //1.配置⽂件/etc/my.cnf log-bin server-id = 1 //2.重启mysql服务⽣效 [root@Master ~]# /etc/init.d/mysqld restart //3.模拟线上数据库 mysql> create database winglinuxdb; mysql> use winglinuxdb; mysql> create table t1(id int, name varchar(20)); mysql> insert into t1 values (1,'wing'); mysql> insert into t1 values (2,'wingsre'); mysql> select * from t1; +------+---------+ | id | name | +------+---------+ | 1 | wing | | 2 | wingsre | +------+---------+ //4.授权, 允许能够远程连接的主机(replicaiton) mysql> grant replication slave, replication client on *.* to 'rep'@'10.1.106.%' identified by 'WingRep@123'; mysql> flush privileges; //5.导出当前数据 [root@Master ~]# mysqldump -uroot -p'Wing@123' \ --all-databases \ --single-transaction \ --master-data=1 \ --flush-logs > /backup/db-$(date +%F)-all.sql //6.重置 binlog,如果是新数据库不需要重制binlog,此步骤在⽣产谨慎执⾏ mysql> reset master; Query OK, 0 rows affected (0.01 sec) //7.将备份⽂件传送⾄Slave [root@Master ~]# scp /backup/db-$(date +%F)-all.sql [email protected]:/backup/ 备注说明 grant replication slave, replication client on . to 'rep'@'10.1.106.%' identified by 'WingRep@123'; 授权⼀个名为 'rep' 的⽤户在 IP 地址为 10.1.106.% 的主机上进⾏主从复制操作。 该语句使⽤了 GRANT 命令，授权了 'rep' ⽤户在所有数据库（ .）上进⾏复制操作（replication slave）和连 接到服务器（replication client）,⽤户的密码为 'WingRep@123'。 注意 'rep' ⽤户需要在主库和从库上都存在，并且在从库上需要使⽤ CHANGE MASTER TO 命令将主库的信 息添加到从库中。此外，为了确保安全性，建议不要使⽤简单的密码，⽽是使⽤更加复杂的密码来保护数据 库。 Slave 配置 从库安装mysql #2.基础环境准备 [root@slave ~]# systemctl stop firewalld [root@slave ~]# systemctl disable firewalld [root@slave ~]# setenforce 0 //建⽴⽤户与相应⽬录 [root@slave ~]# groupadd mysql [root@slave ~]# useradd -r -g mysql -s /sbin/nologin mysql [root@slave ~]# mkdir /soft/src -p && cd /soft/src #3.下载`MySQL`并安装 [root@slave src]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.30- linux-glibc2.12-x86_64.tar.gz [root@slave src]# tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /soft [root@slave src]# ln -s /soft/mysql-5.7.30-linux-glibc2.12-x86_64/ /soft/mysql #4.进⾏`MySQL`初始化 //创建初始化⽬录以及数据库数据⽬录 [root@slave ~]# mkdir /soft/mysql/{mysql-file,data} [root@sqslaveslavel ~]# chown mysql.mysql /soft/mysql/ //初始化数据库 [root@sslaveql ~]# /soft/mysql/bin/mysqld --initialize \ --user=mysql --basedir=/soft/mysql \ --datadir=/soft/mysql/data ------- //初始化数据库会告诉默认登陆账户与密码 2023-05-28T01:27:22.174615Z 1 [Note] A temporary password is generated for root@localhost: u*ksA8+bP+iH //使⽤ssl连接, 初始化后重新授权⽬录权限[如不需要可忽略] [root@slave ~]# /soft/mysql/bin/mysql_ssl_rsa_setup \ --datadir=/soft/mysql/data/ [root@sql ~]# chown -R mysql.mysql /soft/mysql/ #使⽤(systemV)⽅式管理, [强烈推荐] [root@slave ~]# cp /soft/mysql/support-files/mysql.server /etc/init.d/mysqld [root@slave ~]# chkconfig --add mysqld [root@slave ~]# chkconfig mysqld on //修改安装⽬录与数据存放⽬录 [root@slave ~]# sed -i '/^basedir=/cbasedir=\/soft\/mysql' /etc/init.d/mysqld [root@slave ~]# sed -i '/^datadir=/cdatadir=\/soft\/mysql\/data' /etc/init.d/mysqld [root@slave ~]# mkdir -p /log/mysql/ && touch /log/mysql/err.log && mkdir -p /var/run/mysqld/ [root@slave ~]# chown mysql.mysql -R /log/mysql/ && chown mysql.mysql -R /var/run/mysqld/ //修改配置 cat > /etc/my.cnf <<EOF [mysqld] server-id = 2 //3.重启mysql数据库服务 [root@slave ~]#/etc/init.d/mysqld restart //4.导⼊数据,追master的bin_log [root@Slave ~]# mysql -uroot -p'Wing@123' -e "source /backup/db-$(date +%F)-all.sql" //5.指向Master,⽆需指定binlogfile和pos [root@Slave ~]# sed -n '22p' db-2023-05-31-all.sql CHANGE MASTER TO MASTER_LOG_FILE='sql_106_70.000024', MASTER_LOG_POS=154; // 如果master是新的数据库执⾏,这⾥跟上⽂reset master做关联,模拟是⼀个新的数据库 mysql> change master to master_host='master1', master_user='rep', master_password='WingRep@123'; //如果master已经运⾏很久了 需要查找binlog位置进⾏配置 mysql> change master to master_host='master1', master_user='rep', master_password='WingRep@123', master_log_file='sql_106_70.000005', master_log_pos=154; //6.启动slave⻆⾊ mysql> start slave; //7.查看⻆⾊是否同步 mysql> show slave status\G Slave_IO_State: Waiting for master to send event Master_Host: master1 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: sql_106_70.000024 Read_Master_Log_Pos: 639 Relay_Log_File: lb-node2-relay-bin.000002 Relay_Log_Pos: 806 Relay_Master_Log_File: sql_106_70.000024 Slave_IO_Running: Yes Slave_SQL_Running: Yes 2. MySQL数据库Gtid复制 GTID复制中的事务是指MySQL中的ACID（原⼦性、⼀致性、隔离性和持久性）事务。在MySQL中，事务是 由⼀系列的SQL语句组成的逻辑操作单元，可以保证这些操作的原⼦性、⼀致性、隔离性和持久性。GTID复 制使⽤事务ID来标识和跟踪事务，以确保数据的⼀致性和可靠性。 MySQL GTID（Global Transaction ID）复制是⼀种基于事务ID的复制⽅式，它可以解决主从复制中的⼀些问题， 例如主从复制延迟和数据丢失等。 在MySQL GTID复制中，每个事务都有⼀个全局唯⼀的ID，称为GTID。主库将GTID记录到⼆进制⽇志中，从库通 过读取主库的⼆进制⽇志，获取GTID并在⾃⼰的GTID集中记录。从库使⽤GTID集来跟踪其已经复制的事务，从⽽ 保证不会重复复制或者漏掉任何⼀个事务。 当从库连接到主库时，主库会将⾃⼰的GTID集发送给从库，从⽽使从库能够确定⾃⼰需要复制的数据。如果从库与 主库之间的连接中断，从库可以使⽤⾃⼰的GTID集来确定从哪个点继续复制数据。 使⽤GTID复制可以避免主从复制中的⼀些问题，例如 主从复制延迟 和 数据丢失 等。同时，GTID复制还可以简化主 从复制的配置和管理，提⾼数据库的可靠性和可维护性。 数据库 Gtid 主从复制如下, 如需实现多从, 将以下实验从案例在配置⼀次即可
/etc/hosts ⽂件解析 10.1.106.70 master1 10.1.106.66 Slave1 注意:如果实验过传统主从复制, 请重置Slave数据库,⽂章后续会介绍 Master 配置 //1.配置⽂件/etc/my.cnf log-bin server-id=1 gtid_mode = ON enforce_gtid_consistency=1 //2.重启mysql服务⽣效 [root@Master ~]# /etc/init.d/mysqld restart //3.模拟线上数据库 mysql>SET sql_log_bin=0; mysql> drop database winglinuxdb; mysql>SET sql_log_bin=1; mysql> create database winglinuxdb; mysql> use winglinuxdb; mysql> create table t1(id int, name varchar(20)); mysql> insert into t1 values (1,'wing'); mysql> insert into t1 values (2,'wing1'); mysql> select * from t1; +------+-------+ | id | name | +------+-------+ | 1 | wing | | 2 | wing1 | +------+-------+ //4.授权, 允许能够远程连接的主机(replicaiton) mysql> grant replication slave, replication client on *.* to 'rep'@'10.1.106.%' identified by 'WingRep@123'; mysql> flush privileges; //5.导出当前数据 [root@Master ~]# mysqldump -uroot -p'Wing@123' \ --all-databases \ --single-transaction \ --master-data=1 \ --flush-logs > /backup/db-$(date +%F)-all.sql //5.将备份⽂件传送⾄Slave [root@Master ~]# scp /backup/db-$(date +%F)-all.sql root@slave1:/backup/ Slave 配置 //1.检查是否能使⽤远程账户登录 [root@slave ~]# mysql -hmaster1 -urep -p'WingRep@123' //2.修改配置⽂件/etc/my.cnf server-id=2 gtid_mode = ON enforce_gtid_consistency=1 [可选,保存连接信息⾄表中] #log-info-repository=TABLE #relay-log-intp-repository=TABLE //3.重启mysql数据库服务 [root@slave ~]# /etc/init.d/mysqld restart //4.导⼊数据,追master的bin_log [root@Slave ~]# mysql -uroot -p'Wing@123' -e "source /backup/db-$(date +%F)-all.sql" //4.指向Master,gtid⾃动协商同步 mysql> change master to master_host='master1', master_user='rep', master_password='WingRep@123', master_auto_position=1; //7.启动slave⻆⾊ mysql> start slave; //8.查看⻆⾊是否同步 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master1 Master_User: rep Master_Port: 3306 Master_Log_File: sql_106_70.000003 Read_Master_Log_Pos: 194 Relay_Log_File: lb-node2-relay-bin.000002 Relay_Log_Pos: 369 Relay_Master_Log_File: sql_106_70.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 194 Relay_Log_Space: 579 Seconds_Behind_Master: 0 Master_Server_Id: 70 Master_UUID: 56490b35-ff03-11ed-94dc-00505694c38d Master_Info_File: /soft/mysql/data/master.info SQL_Delay: 0 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Executed_Gtid_Set: 56490b35-ff03-11ed-94dc-00505694c38d:1-6 Auto_Position: 1 1 row in set (0.00 sec) 以下是⼀些关键字段的解释： Slave_IO_State：从库当前正在等待主库发送事件。 Master_Host：主库的主机名。 Master_User：⽤于从主库复制数据的⽤户。 Master_Port：主库使⽤的端⼝号。 Master_Log_File ：主库当前正在写⼊的⼆进制⽇志⽂件的名称。 Read_Master_Log_Pos ：从库当前已经读取的主库⼆进制⽇志的位置。 Slave_IO_Running ：从库的I/O线程正在运⾏。 Slave_SQL_Running ：从库的SQL线程正在运⾏。 Last_Errno：最近⼀次错误的错误代码。 Last_Error：最近⼀次错误的错误消息。 Exec_Master_Log_Pos ：从库当前正在执⾏的主库⼆进制⽇志的位置。 Relay_Log_Space : 579 从库当前使⽤的中继⽇志⽂件的⼤⼩，以字节为单位 SQL_Delay : 从库SQL线程当前的延迟时间 Seconds_Behind_Master ：从库当前滞后于主库的秒数。如果为0，表示从库已经与主库同步。 Slave_SQL_Running_State: 从库已经读取了所有的中继⽇志，正在等待更多的更新。 Master_Retry_Count: 如果主库⽆法连接，则从库会尝试重新连接主库的次数。这⾥的值为86400，表示从库 将尝试86400次重新连接主库。 Executed_Gtid_Set: 已经执⾏的GTID集合，这⾥的值为"56490b35-ff03-11ed-94dc-00505694c38d:1-6"，表 示从库已经执⾏了这个GTID集合中的1-6个GTID。 Auto_Position: 是否开启了⾃动位点模式，这⾥的值为1，表示已经开启了⾃动位点模式。开启⾃动位点模式 后，从库会⾃动记录已经执⾏的GTID，并在重新连接主库时，从最后⼀个已知的GTID继续进⾏复制。 Seconds_Behind_Master 是show slave status命令中⼀个⾮常重要的字段，它表示从库当前滞后于主库的 秒数。具体来说，它表示从库当前正在执⾏的主库⼆进制⽇志事件的时间戳与主库上相应事件的时间戳之间 的差异，也就是从库当前的时间与主库上相应事件发⽣的时间之间的差异。 如果Seconds_Behind_Master的值为0，则表示从库已经追上了主库，两者的数据完全⼀致。如果 Seconds_Behind_Master的值为NULL，则表示从库当前没有与主库进⾏同步。如果 Seconds_Behind_Master的值为⾮零，则表示从库当前滞后于主库，即从库上的数据还没有完全同步到主库 上。 通常情况下，我们希望从库与主库的数据同步尽可能地及时，因此，我们需要定期检查 Seconds_Behind_Master的值，以确保从库与主库之间的数据同步正常。 查看MySQL主从复制是否基于GTID，可以使⽤以下命令： SHOW VARIABLES LIKE 'gtid_mode'; 如果输出结果为 ON ，则表示MySQL主从复制是基于GTID的。如果输出结果为 OFF ，则表示MySQL主从复制不是 基于GTID的。 注意事项: 学习场景重置主从关系 #master reset master; #slave stop slave; reset slave; 2.1 Gtid M-M复制, 基于主从模式,演变为双主 MySQL数据库双主复制 注意: 数据库双主M-M架构, 需要基于gtid的主从架构演变
[root@sql ~]# cat /etc/hosts #mysql 10.1.106.70 master1 10.1.106.66 master2 slave1 Master1 //1.配置⽂件/etc/my.cnf server-id = 1 log-bin = /log/mysql/bin/sql_106_70 expire_logs_days = 30 gtid_mode = ON enforce_gtid_consistency=1 //2.重启mysql服务⽣效 [root@Master ~]# /etc/init.d/mysqld restart //3.授权, 允许能够远程连接的主机(replicaiton) mysql> grant replication slave, replication client on *.* to 'rep'@'10.1.106.%' identified by 'WingRep@123'; mysql> flush privileges; //4.导出当前数据 [root@Master ~]# mysqldump -uroot -p'Wing@123' \ --all-databases \ --single-transaction \ --master-data=1 \ --flush-logs > /backup/db-$(date +%F)-all.sql //5.将备份⽂件传送⾄Master2 [root@Master ~]# scp /backup/db-$(date +%F)-all.sql root@master2:/backup/ Master2 //1.检查是否能使⽤远程账户登录 [root@slave ~]# mysql -hmaster1 -urep -p'WingRep@123' //2.修改配置⽂件/etc/my.cnf server-id = 2 gtid_mode = ON enforce_gtid_consistency=1 log-bin = /log/mysql/bin/sql_106_66 [可选,保存连接信息⾄表中] #log-info-repository=TABLE #relay-log-intp-repository=TABLE //3.重启mysql数据库服务 [root@slave ~]# /etc/init.d/mysqld restart //4.导⼊数据 [root@Slave ~]vim /backup/db-$(date +%F)-all.sql #注释这⼀段内容 24 -- SET @@GLOBAL.GTID_PURGED='56490b35-ff03-11ed-94dc-00505694c38d:1-2'; 30 -- CHANGE MASTER TO MASTER_LOG_FILE='sql_106_70.000002', MASTER_LOG_POS=194; [root@Slave ~]# mysql -uroot -p'Wing@123' -e "source /backup/db-$(date +%F)-all.sql" Master1 执⾏同步 master1和master2数据必须保持⼀致 在master1上执⾏ change to master1 --> mater2 change master to master_host='master2', master_user='rep', master_password='WingRep@123', master_auto_position=1; start slave; mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master2 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: sql_106_66.000001 Read_Master_Log_Pos: 154 Relay_Log_File: lb-node1-relay-bin.000002 Relay_Log_Pos: 369 Relay_Master_Log_File: sql_106_66.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Master2 执⾏同步 在master2上执⾏ change to master2 -->master1 change master to master_host='master1', master_user='rep', master_password='WingRep@123', master_auto_position=1; start slave; mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master1 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: sql_106_70.000002 Read_Master_Log_Pos: 194 Relay_Log_File: lb-node2-relay-bin.000002 Relay_Log_Pos: 369 Relay_Master_Log_File: sql_106_70.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes 验证 #master1 mysql> create database master1; #master2 mysql> create database master2;
3.MySQL数据库多源复制 数据库M-M-S-S
[root@Master1 ~]# cat /etc/hosts 10.1.106.70 master1 10.1.106.66 master2 10.1.106.67 slave2 10.1.106.68 slave3 Master1 1.修改配置 log -bin server_id=1 gtid_mode = ON enforce_gtid_consistency=1 2.重启mysql服务器 systemctl restart mysqld 3.授权(建议⽹段) mysql> grant replication slave, replication client on *.* to 'rep'@'10.1.106.%' identified by 'WingRep@123'; mysql> flush privileges; 4.导出对应的数据 mysqldump -uroot -p'Wing@123' -h 10.1.106.70 \ --all-databases \ --master-data=1 \ --single-transaction \ --flush-logs > /backup/$(date +%F)-mysql-all.sql 5.分发对应的数据 scp --> master2 scp --> slave1 scp --> slave2 Master2 1.修改配置 server_id=2 log_bin gtid_mode = ON enforce_gtid_consistency = 1 2.重启 3.导⼊数据 [root@Master2 ~]# mysql -uroot -pWing@123 -e "reset master;" [root@Master2 ~]# mysql -uroot -pWing@123 < /root/2023-05-28-mysql-all.sql 4.验证远程账户是否可⽤ 1.防⽕墙 2.账户密码 5.登陆数据库,清理从库的⼆进制⽇志 mysql> reset master; 6.指定master mysql> change master to master_host='master1', master_user='rep', master_password='WingRep@123', master_auto_position=1; 7.启动 slave ⻆⾊ mysql > start slave; 8.查看⻆⾊状态 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master1 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Master1-bin.000003 Read_Master_Log_Pos: 154 Relay_Log_File: Master2-relay-bin.000002 Relay_Log_Pos: 371 Relay_Master_Log_File: Master1-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Master1 mysql> flush privileges; mysql>change master to master_host='master2', master_user='rep', master_password='WingRep@123', master_auto_position=1; 2.启动slave⻆⾊ mysql > start slave; # 3.查看slave状态 mysql > show slave status\G 双主OK Slave1和Slave2⼀致 1. 修改配置 server_id=3 gtid_mode = ON enforce_gtid_consistency = 1 master-info-repository=TABLE relay-log-info-repository=TABLE 2.重启数据库 [root@slave2 ~]# /etc/init.d/mysqld restart 3. 初始化数据库 导⼊数据 [root@slave2 ~]# mysql -uroot -p'Wing@123' -e "reset master;" [root@slave2 ~]# mysql -uroot -p'Wing@123' < /backup/2023-05-28-mysql all.sql [root@slave2 ~]# mysql -uroot -p'Wing@123' -se 'show databases;' 4.清理⼆进制⽇志⽂件 reset master; //指向Master1 mysql> change master to master_host='master1', master_user='rep', master_password='WingRep@123', master_log_file='sql_106_70.000001', master_log_pos=154; //指向Master2 mysql> change master to master_host='master2', master_user='rep', master_log_file='sql_106_66.000001', master_log_pos=154; 5.mysql > start slave; 6.mysql > show slave status\G mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master1 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Master1-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: [email protected] Relay_Log_Pos: 371 Relay_Master_Log_File: Master1-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes mysql> show slave status\G *************************** 2. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master1 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Master1-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: [email protected] Relay_Log_Pos: 371 Relay_Master_Log_File: Master1-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes 4.MySQL数据库架构演变
标签：11,slave,Log,18,master,2023.6,mysql,Master,root From： https://www.cnblogs.com/BXXY5961/p/17489257.html