MySQL主从复制【一般架构】
1、MySQL主从架构
【在主库上进行写入操作，将数据同步到多个从库中，客户端读取从库上的数据，多个从库可以进行负载均衡】
2、主从架构特点
- 低成本、布署快速、方便；
- 读写分离；
- 可以通过及时增加从库减少读库压力
- 存在主库单点故障问题（没有冗余）
- 数据一致性问题（同步延迟造成）
3、主从复制
通过将MySQL的某一台主机（master）的数据复制到其他主机（slave）上，其它主机（slave）再将复制的数据重新执行一遍。
复制过程中一台服务器充当主服务器（master），而其它一台或多台服务器充当从服务器（slave）。
1）MySQL支持的复制类型
- 基于语句（statement）的复制
在主服务器上执行SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率较高。 - 基于行（row）的复制
把改变的内容复制过去，而不是把命令在从服务器上执行一遍。【从MySQL5.0开始支持】 - 混合型（mixed）的复制
默认采用基于语句的复制，一旦发现无法基于语句进行精确复制时，就会采用基于行的复制。
2）进行主从复制的原因
- 灾难备份
- 达到数据分布（达到最优执行效率）
- 负载平衡
- 实现读写分离
- 提高并发能力
3）主从复制原理
大致步骤：
1、MySQL将数据变化记录到二进制日志中；
2、slave 将MySQL的二进制日志拷贝到 slave 的中继日志中；
3、slave 将中继日志中的事件再做一次，将数据变化反应到自身（slave）的数据库。
详细步骤：
1、slave 通过手工执行 change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且告知 slave 二进制日志的起点位置（binlog 名 & position 号）【 position 号表示 binlog 偏移量】，再通过 start slave 命令与 master 发起连接；
2、slave 的 IO 线程和 master 的 dump 线程建立连接；
3、slave 根据 change master to 语句提供的（binlog 名 & position 号），IO 线程再向 master 发起 binlog 的复制请求；
4、master 的 dump 线程根据 slave 的复制请求，将本地的 binlog 以 events（事件） 的方式发给从库的 IO 线程；
5、slave 的 IO 线程接收 binlog events 后，将其存放到本地的 relay-log（中继日志） 中，同时，传送过来的信息，会记录到 slave 的 master.info 文件中；
6、slave 的 SQL 线程应用 relay-log（中继日志），并且把已应用的信息记录到 slave 的 relay-log.info 文件中，默认情况下，relay-log 中已经应用过的信息会自动被清理（purge）。
4）MySQL复制常用的拓扑结构
- 主从类型（Master-Slave）【服务器正常一个主，一个或多个从】
- 主主类型（Master-Master）【两个服务器互为主从关系】
- 级联类型（Master-Slave-Slave）【可以理解为嵌套 / 套娃】
5）实现主从复制
在主服务器（master）上
- 启用二进制日志；
- 自定义唯一的server-id（IPV4主机位）；
- 创建具有复制权限的用户；
在从服务器（slave）上
- 启用中继日志（二进制日志可开启，也可不开启）；
- 自定义唯一的server-id（IPV4主机位）；
- 与主服务器连接并完成同步；
6）MySQL主从复制的状况监测
主从状况监测主要参数:
Slave_IO_Running: //IO线程是否打开 YES/No/NULL
Slave_SQL_Running: //SQL线程是否打开 YES/No/NULL
Seconds_Behind_Master: //从库和主库相比，同步延迟的秒数（NULL表示无延迟）
可能导致主从延时的因素:
主从时钟是否一致
网络通信是否存在延迟（防火墙）
是否和日志类型，数据过大有关
从库性能，有没开启binlog
从库查询是否优化
常见状态错误排除：
错误一：
[root@localhost ~]# tail localhost.localdomain.err //发现IO进程错误，检查日志，排除故障
2015-11-18 10:55:50 3566 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593
原因：从5.6开始主从复制引入了uuid的概念，各个复制结构中的server_uuid需要保证不一样（从库是克隆机器【uuid相同】）
解决方法：
[root@localhost ~]# find / -name auto.cnf
/var/lib/mysql/auto.cnf
修改从库的uuid
# vim auto.cnf
server-uuid=··· //修改末尾即可（使uuid不相同）
错误二：
mysql> show slave status\G //查看状态
Error xxx doesn’t exist
解决方法：
stop slave;
set global sql_slave_skip_counter = 1;
start slave;
测试主从同步：
主库创建一个数据库：
[root@localhost ~]# mysql -uroot -p -e 'create database test_m_s;'
从库检查：
[root@localhost ~]# mysql -uroot -p -e 'show databases;' | grep "test_m_s"
7）生产环境其他常用设置
1、配置忽略权限库同步参数
binlog-ignore-db='information_schema mysql test'
2、从库备份开启binlog
log-slave-updates
log_bin = mysql-bin
expire_logs_days = 7 #日志保存时间
#应用场景：级联复制或从库做数据备份。
3、从库只读
read-only来实现
innodb_read_only = ON或1，或者innodb_read_only
#结论：当用户权限中没有SUPER权限(ALL权限是包括SUPER的)时，从库的read-only生效！
4、示例
1）环境准备
三台主机：一主、两从
主库（MySQL master）[ip为192.168.25.131]
从库（MySQL slave1）[ip为192.168.25.133]
从库（MySQL slave2）[ip为192.168.25.134]
2）配置主库
[root@localhost ~]# systemctl stop firewalld //关闭防火墙，否则主从无法建立连接（有能力者设定防火墙规则放行）
[root@localhost ~]# vim /etc/my.cnf //修改配置文件（开启binlog参数、设置server-id值）
[mysqld]
log_bin=/var/lib/mysql/mysql-bin //bin_log指定文件名和文件路径
server_id=131 //MySQL5.5版本后要开启bin_log必须给定一个唯一的服务器id（一般为IPV4地址主机位）
[mysql]
prompt="db01 [\\d] > "
[root@localhost ~]# systemctl restart mysqld
#创建同步账号
db01 [(none)] > grant replication slave on *.* to "rep"@"192.168.25.%" identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)
db01 [(none)] > show grants for "rep"@"192.168.25.%";
+--------------------------------------------------------+
| Grants for rep@192.168.25.% |
+--------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.25.%' |
+--------------------------------------------------------+
1 row in set (0.00 sec)
db01 [(none)] > select * from mysql.user where user="rep"\G
*************************** 1. row ***************************
Host: 192.168.25.%
User: rep
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: Y
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
password_expired: N
password_last_changed: 2022-05-09 23:58:13
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
#通过检查同步账号创建成功
#锁表设置只读
db01 [(none)] > flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
#查看锁表自动解锁时间
db01 [(none)] > show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
+-----------------------------+----------+
13 rows in set (0.00 sec)
#31536000秒后自动解锁
#查看主库状态（binlog 名 & position 号）
db01 [(none)] > show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 707 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
//备份数据库数据
[root@localhost ~]# mysqldump -uroot -p123456 -A -B > /backup/mysql_fullbak.$(date +%F).sql
#解锁
db01 [(none)] > unlock tables;
//将主库备份数据上传到从库
[root@localhost ~]# scp /backup/mysql_fullbak.2022-05-10.sql 192.168.25.133:/backup/
[root@localhost ~]# scp /backup/mysql_fullbak.2022-05-10.sql 192.168.25.134:/backup/
3）配置从库
//slave1（192.168.25.133）
[root@localhost ~]# vim /etc/my.cnf //修改配置文件（开启binlog参数、设置server-id值）
[mysqld]
log_bin=/var/lib/mysql/mysql-bin //bin_log指定文件名和文件路径
server_id=133 //MySQL5.5版本后要开启bin_log必须给定一个唯一的服务器id（一般为IPV4地址主机位）
[mysql]
prompt="db02 [\\d] > "
[root@localhost ~]# systemctl restart mysqld
//slave2（192.168.25.134）
[root@localhost ~]# vim /etc/my.cnf //修改配置文件（开启binlog参数、设置server-id值）
[mysqld]
log_bin=/var/lib/mysql/mysql-bin //bin_log指定文件名和文件路径
server_id=134 //MySQL5.5版本后要开启bin_log必须给定一个唯一的服务器id（一般为IPV4地址主机位）
[mysql]
prompt="db03 [\\d] > "
[root@localhost ~]# systemctl restart mysqld
#还原从主库备份数据
db02 [(none)] > source /backup/mysql_fullbak.2022-05-10.sql
db03 [(none)] > source /backup/mysql_fullbak.2022-05-10.sql
#设定主从同步
db02 [(none)] > change master to
-> master_host="192.168.25.131", #主库IP
-> master_user="rep", #同步账号
-> master_password="123456", #同步账号的密码
-> master_log_file="mysql-bin.000002", #binlog名
-> master_log_pos=707; #position号（binlog偏移量）
Query OK, 0 rows affected, 2 warnings (0.01 sec)
db02 [(none)] > system systemctl stop firewalld #关闭防火墙
db03 [(none)] > change master to
-> master_host="192.168.25.131",
-> master_user="rep",
-> master_password="123456",
-> master_log_file="mysql-bin.000002",
-> master_log_pos=707;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
db03 [(none)] > system systemctl stop firewalld #关闭防火墙
#启动从库同步开关
db02 [(none)] > start slave;
Query OK, 0 rows affected (0.00 sec)
db02 [(none)] > show slave status\G #查看结果
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.25.131
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 707
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes #此处必须为yes（表示成功）
Slave_SQL_Running: Yes #此处必须为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: 707
Relay_Log_Space: 531
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: 131
Master_UUID: 6c8e0df2-cfaf-11ec-b19d-000c29c1f77e
Master_Info_File: /var/lib/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)
db03 [(none)] > start slave;
Query OK, 0 rows affected (0.00 sec)
db03 [(none)] > show slave status\G #查看结果
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.25.131
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 707
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes #此处必须为yes（表示成功）
Slave_SQL_Running: Yes #此处必须为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: 707
Relay_Log_Space: 531
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: 131
Master_UUID: 6c8e0df2-cfaf-11ec-b19d-000c29c1f77e
Master_Info_File: /var/lib/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)
4）测试
db01 [(none)] > create database test;
Query OK, 1 row affected (0.00 sec)
db02 [(none)] > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
db03 [(none)] > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
db01 [test] > create table t1(id int);
Query OK, 0 rows affected (0.01 sec)
db01 [test] > insert into t1 values(1),(2);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
db02 [(none)] > use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
db02 [test] > select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
db03 [(none)] > use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
db03 [test] > select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
db01 [test] > drop database test;
Query OK, 1 row affected (0.00 sec)
db02 [test] > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
db03 [test] > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
通过上方测试，通过查看发现所有数据均已同步，主从复制成功
