一、主从复制企业应用场景
MySql主从复制使得MySql支持大规模高并发的读写操作成为可能。同时解决物理服务器宕机时候的数据备份和进行快速切换问题。
1.从服务器作为主服务器的实时数据备份
主从架构，大大加强DB架构的健壮性。如：当主服务器挂了，可以手动或自动切换到从服务器继续提供服务，此时从服务器的数据和宕机时主数据库几乎一致。（硬件软件问题从库备份数据有效，drop、delete语句无效，从服务器也会执行）
2.主从服务器实现读写分离，从服务器实现负载均衡
通过代理软件或程序进行对客户端的 读（select）写（update、insert、delete）分离，从库处理读、主库处理写。降低主库压力，从库可以扩展为多台，可以进行负载均衡
3.根据业务重要性对多个从服务器进行拆分访问
根据不同业务，对从服务器进行拆分，比如按照：对外部用户的slave，DBA备份专用的slave，数据统计、日志分析的slave等，做数据隔离。
架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能
二、复制的什么
- MySql主从复制和使用scp/rsync 命令进行异机文件级别复制类似，都是文件的远程传输。
- MySql主从复制不是直接复制的数据库磁盘上的文件，而是将逻辑记录DB更新的binlog日志，发送到其他数据库服务器，再由其本地数据库线程读取执行日志中的SQL语句。
- MySql主从复制默认都是异步的，不是严格实时的数据同步（用户体验几乎是实时的）
三、常见主从复制架构
MySql支持单向、双向、链式级联、环状等场景的主从复制。
一台服务器（实例）作为主库（Master）接收用户更新的SQL，一个或多个服务器作为从服务器（Slave）接收主服务器的binglog日志文件，并将日志内容解析出的SQL语句应用到从服务器，使主从服务器数据达到一致。
如果是链式级联复制，从服务器本身还会作为其下面的从服务器的主 （A==>B==>C）1.一主一从
2.一主多从
3.双向主主复制
此架构可以在Master1和Master2 进行数据写入，或者在两端同事写入（需特殊设置）
4.线性级联单向双主复制
此架构只能在Master1端进行数据写入，工作场景Master1和Master2作为主主互备，Slave1服务器作为从库，中间的Master2需要进行特殊的设置。
环状级联单向多主同步
任意一点都可以写入数据，此架构比较复杂，属于极端环境下的作品，一般场景慎用。
四、MySql主从复制原理
MySql主从复制是异步的。
Master、Slave主从复制的过程由三个线程(mysql5之前)参与：（从MySql6开始SQL线程可以有多个）
Slave端两个线程：SQL线程、IO线程
Master端一个线程：binlog dump线程
1、从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号），然后手工执行start slave开启从节点模式；
2、从库的IO线程和主库的dump线程建立连接；
3、从库根据change master to语句提供的file名和position号，IO线程向主库发起binlog的请求；
4、主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程；
5、从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息会记录到master.info中；
6、从库SQL线程利用relay-log文件进行数据同步，默认情况下，已经同步过的relay会自动被 purge线程 清理。
五、MySql主从复制实践
1.准备
1.环境准备
| 角色 | 主机 | 内网IP |
|---|---|---|
| 主库Master | db01 | 192.168.140.141 |
| 从库Slave | db02 | 192.168.140.142 |
2.检查数据库启动状态
主库
[root@db01 ~]# lsof -i:3306COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAMEmysqld 7037 mysql 32u IPv6 40463 0t0 TCP *:mysql (LISTEN)
从库
[root@db02 ~]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 7057 mysql 33u IPv6 40648 0t0 TCP *:mysql (LISTEN)
2.配置Master
设置server-id/开启binlog
默认没开启binlog
默认mysql是不开启binlog的，查看是否开启binlog：mysql> show variables like ‘log_bin’;
创建binlog目录
在 /var/lib/mysql/目录下创建binlog目录
mkdir -p /var/lib/mysql/mysql-bin
**
然后在[mysqld]模块下配置server-id、log-bin，
[mysqld]
log_bin=mysql-bin <===随便起名字
server_id=141
查看配置
看看配置生效没有：
[root@db01 ~]# egrep “server_id|log_bin” /etc/my.cnf
log_bin=/var/lib/mysql/mysql-bin
server_id=141
我是rpm安装的mysql，修改后的 /etc/my.cnf 长这样：
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
log_bin=/var/lib/mysql/mysql-bin
server_id=141
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
注意事项
- 主从同步每台机器的server-id不能重复，可以设置为机器ip最后.后的数字
- mysql5.6开始，参数中的“-”换成了“_”，高版本也兼容“-”
- 修改my.cnf需要重启数据库，要确认真正重启了
- log_bin 后可以不带等号以及目录部分，MySql会使用默认的路径以及日志名
3.重启Master
systemctl restart mysqld.service ```sql [root@db01 ~]# systemctl restart mysqld.service [root@db01 ~]# systemctl status mysqld.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Tue 2021-07-06 10:25:29 CST; 7s ago Docs: man:mysqld(8)
Process: 9709 ExecStart=/usr/sbin/mysqld —daemonize —pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 9691 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 9712 (mysqld) CGroup: /system.slice/mysqld.servicehttp://dev.mysql.com/doc/refman/en/using-systemd.html└─9712 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Jul 06 10:25:27 db01 systemd[1]: Stopped MySQL Server. Jul 06 10:25:27 db01 systemd[1]: Starting MySQL Server… Jul 06 10:25:29 db01 systemd[1]: Started MySQL Server.
<a name="ykIYr"></a>
### mysql 在配置文件中保存密码
可以把mysql的用户名密码保存在my.cnf配置文件，然后再登录客户端直接输入mysql就可以了，方便后续操作以，这里配置一下。<br />Master和Slave都配置一下， /etc/my.cnf <br />[client]<br />host=localhost<br />user=root<br />password=123456<br />都重启一下，然后直接输入[root@db02 ~]# mysql 就可以进入client命令行<br />
<a name="rQWaH"></a>
### 再次校验配置是否成功：
```sql
[root@db01 ~]# mysql -e "show variables like 'log_bin';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
[root@db01 ~]# mysql -e "show variables like 'server_id';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 141 |
+---------------+-------+
4.在主库上建立用于主从复制的账号
从库要想与主库同步，要有一个可以连接主库的账号，这个账号是在主库上创建的，权限是允许从库连接主库并同步数据。
登录mysql
[root@db01 ~]# mysql <=== 用户名密码已经配置在my.cnf 方便操作
建立从库复制账号以及授权
mysql> grant replication slave on . to ‘lhy_rep’@’192.168.140.%’ identified by ‘lhy_123456’;
mysql> flush privileges;
说明
- replication slave为允许MySql Slave 同步的必须权限，此处不要授权all权限。
- . 表示所有库所有表，也可以指定具体库、表进行复制。例如 oldboy.test 表示oldboy库test表
- ‘lhy_rep’@’192.168.140.%’ 中lhy_rep为同步账号，192.168.140.%表示授权主机网段，使用%表示允许192.168.140.0网段访问。
- identified by ‘lhy_123456’ 中lhy_123456为密码
- 如果密码太简单错误，设置密码等级为LOW：set global validate_password_policy=LOW;
- 授权后一般刷新下权限
5.对主库锁表只读后进行备份
如果是正在提供服务的服务（不是一开始就建立主从同步的），需要一次性全量备份之前的数据。对主库锁表只读
对主库进行锁表只读（当前窗口不要关掉），命令如下：mysql> flush table with read lock;对于不同存储引擎这个锁表命令的时间会收到下面参数的控制。锁表时如果超过了设置的时间会自动解锁。 默认情况下的解锁时长参数值设置如下：
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| interactive_timeout | 28800 |
| wait_timeout | 28800 |
+-----------------------------+----------+
锁表后查看主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 311 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
最关键的是前两项：
mysql-bin.000004 ：是binlog日志文件名
311：是二进制binlog日志偏移量位置
这些需要记录下来。在后面，从库导入主库的全量备份后，继续对主库进行复制时，就需要从这个binlog文件以及对应该位置开始进行复制。
（MySQL5.6开始支持GTID功能，后续再议）
导出主库全量数据
锁表后，一定单开一个ssh窗口否则锁表会失效，导出数据库所有数据，如果数据量很大（30G以上）且 允许停机，可以直接停机打包所有数据文件迁移加快速度。
创建备份目录
[root@db01 ~]# mkdir /server/backup -p
备份导出数据
[root@db01 ~]# mysqldump -A -B |gzip >/server/backup/bak$(date +%F).sql.gz
看看备份文件：
[root@db01 ~]# cd /server/backup/
[root@db01 backup]# ll -lh
total 188K
-rw-r—r—. 1 root root 188K Jul 6 16:19 bak_2021-07-06.sql.gz
或者这样只看刚备份的文件 ls -l /server/backup/bak$(date +%F).sql.gz
这里进行了文件自动切分，我看文件小的花，直接导一个文件也中。
再次确认下期间没有数据插入
导出主库完毕后，确保导出数据期间没有数据插入，再看下主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 311 | | | |
+------------------+----------+--------------+------------------+-------------------+
解锁主库
导出数据后，解锁主库，恢复可写，恢复主库对外提供服务。
mysql> unlock tables;
全量备份迁移
把主库导出的数据迁移到从库，常用的命令有 scp 、rsync，将备份数据复制到异机。
期间需要输入远程密码
[root@db01 backup]# scp -rp /server/backup/bak_$(date +%F).sql.gz root@192.168.140.142:/opt
The authenticity of host '192.168.140.142 (192.168.140.142)' can't be established.
ECDSA key fingerprint is SHA256:JvNIq7i2Wno0HBjG8yG7HvwvU4SU+WA1ihHbVLzipYo.
ECDSA key fingerprint is MD5:e5:7f:83:bf:f2:27:69:40:ef:c3:eb:86:9c:86:43:af.
Are you sure you want to continue connecting (yes/no)? y
Please type 'yes' or 'no': yes
Warning: Permanently added '192.168.140.142' (ECDSA) to the list of known hosts.
root@192.168.140.142's password:
bak_2021-07-06.sql.gz 100% 187KB 31.7MB/s 00:00
scp -rp local_folder remote_username@remote_ip:remote_folder -p：保留原文件的修改时间，访问时间和访问权限。 -r： 递归复制整个目录。
6.my.cnf配置
server_id
同主库一样，设置my.cnf里唯一的server_id ，不要和主库和其他从库重复。，
[mysqld]
server_id=42
关闭binlog
注释掉从库的bin_log参数配置，如果不对从库进行级联复制且不作为备份的使用，不要开启binlog，开启了会增加从库磁盘IO压力。
如下两种情况需要开始从库的binlog功能，记录数据库更新的SQL语句：
- 形如 A—>B—>C的级联同步，中间的B数据库需要开启binlog
- 在从库中做数据备份时需要开启binlog，因为数据库备份必须要有全量备份和binlog日志才是完整备份。
7.重启从库
[root@db02 opt]# systemctl restart mysqld.service
[root@db02 opt]# systemctl restart mysqld.service
[root@db02 opt]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2021-07-06 16:48:17 CST; 28s ago
重启后校验配置修改
[root@db02 opt]# mysql -e "show variables like 'log_bin';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
[root@db02 opt]# mysql -e "show variables like 'server_id';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 42 |
+---------------+-------+
8.将主库mysqldump导出的数据恢复到从库
在做主从复制之前，让主库与从库的数据保持一致，将主库导出的数据全量恢复到从库后，再设置主从复制。
使用zcat命令可以在不打开压缩包的情况下把压缩包内容读取出来，如zcat test.gz
如何导入sql.gz文件并将其插入数据库
在命令行上键入以下命令 zcat DB_File_Name.sql.gz | mysql -u username -p Target_DB_Name
DB_File_Name.sql.gz=要导入的sql.gz文件的完整路径 username=MySQL用户名 Target_DB_Name=要导入数据库的数据库名称
命令：
[root@db02 opt]# zcat /opt/bak_2021-07-06.sql.gz |mysql
不能有报错
9.配置从库复制参数
连接到主库
配置从库复制参数，MySQL从库连接主库信息
#连接主服务器，master_log_pos值必须和`show master status`中Position一致
mysql> change master to master_host='192.168.140.141',master_port=3306,master_user='lhy_rep',master_password='lhy_123456',master_log_file='mysql-bin.000004',master_log_pos=311;
启动slave
查看slave状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.140.141
Master_User: lhy_rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 311
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-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: 311
Relay_Log_Space: 526
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: 141
Master_UUID: cc269068-dc88-11eb-a666-000c29b7e94c
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)
ERROR:
No query specified
主从复制是否配置成功了，主要看三个参数：
- Slave_IO_Running: Yes ，这个表示I/O线程的状态，I/O线程负责从主库中读取Binlog日志，并将binlog日志写入从库的中继日志中，状态为Yes表示I/O线程工作正常。
- Slave_SQL_Running: Yes ，SQL线程的状态，SQL线程负责读取中继日志（relay-log）中的数据并转换为SQL语句应用到从数据库，Yes表示状态正常。
- Seconds_Behind_Master: 0 ， 表示复制过程中，从库比主库延迟的秒数，（更准确判断主从延迟：主库写时间戳，从库读取，与当前时间戳比较确认是否真的延迟）
10.测试主从复制
主库创建数据库：
mysql> create database xxoo;
从库：
至此 主从同步验证完成。mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | #mysql50#mysql-bin | | performance_schema | | sys | | xxoo | +--------------------+
六、MySQL主从同步线程状态
主库I/O线程状态
mysql> show processlist\G #<===查看主库所有的线程状态
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost
db: xxoo
Command: Query
Time: 0
State: starting
Info: show processlist
*************************** 2. row ***************************
Id: 6
User: lhy_rep
Host: 192.168.140.142:53324
db: NULL
Command: Binlog Dump #<===用于复制的主线程
Time: 2915
State: Master has sent all binlog to slave; waiting for more updates #<==当前状态
Info: NULL
2 rows in set (0.00 sec)
上述状态意思是，线程已经从binlog日志中读取到了所有的更新，并将更新发送到了所有的从服务器，线程现在为空闲状态，等待主服务器上二进制日志中的新事件更新。
主服务器的binlog dump线程State列常见状态（如果主服务器没看见binlog dump线程，说明复制没有运行）
| 主库I/O线程工作状态 | 说明 |
|---|---|
| Sending binlog event to slave | 线程已经从binlog中读取了一个事件，并且正将它发往从服务器 |
| Finished reading one binlog;switching to next binlog | 线程已经读完binlog，并正在打开下一个要发送到从服务器的binlog |
| Has sent all binlog to slave;waitting for binlog to be updated | 线程已经从binlog读取到所有更新并已经发送到了从服务器，线程现在空闲状态，等待主服务器binlog中的新事件更新 |
| Waitting to finalize terminztion | 线程停止时发生的一个简单状态 |
从库线程状态
mysql> show processlist\G
*************************** 1. row ***************************
Id: 5
User: root
Host: localhost
db: xxoo
Command: Query
Time: 0
State: starting
Info: show processlist
*************************** 2. row ***************************
Id: 6
User: system user
Host:
db: NULL
Command: Connect
Time: 3986
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 7
User: system user
Host:
db: NULL
Command: Connect
Time: 2165
State: Slave has read all relay log; waiting for more updates
Info: NULL
3 rows in set (0.00 sec)
…
七、遇到问题
IP变更导致主从坏了
我是VMware上CentOS7搭建的一主一从，由于IP是dhcp的有时候会变，重启虚拟机后，IP变了，此时主从同步就坏了。
问题再现：
IP地址
主库IP从192.168.140.141 —->变成了 192.168.140.152
从库IP从192.168.140.142 —->变成了 192.168.140.151
IP变了，主从就坏了。此时
大概率原因是主从数据不一致导致的，解决思路：
1.stop slave ;
2.主库全备，copy到从库，
3.从库导入全备
4.重新建立连接
5.重启主库从库mysql服务
此时查看从库状态，一切正常：
mysql> show slave status\G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: xxoo
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.140.152
Master_User: lhy_rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 154
Relay_Log_File: db02-relay-bin.000006
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000014
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: 739
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: 141
Master_UUID: cc269068-dc88-11eb-a666-000c29b7e94c
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.02 sec)
主库可以同步了。
连鹏举解决方案
- stop slave;
- reset slave;
- reset master;
- show master status; 查看最新的bin-log文件名和position
- change master to master_log_file=’mysql-bin-new’,master_log_pos=new_pos_num;
- start slave;
- show slave status\G校验
mysql日志： tail -100 /var/log/mysqld.log
只要主从position不一致，就需要全备份一次保证数据一致性。
TODO:
GTID
多线程复制 MTS
5.6 每个线程只能处理一个数据库，MySQL5.7 多线程处理一个数据库
半同步复制
若有收获，就点个赞吧
0 人点赞
