Mysql之主从复制_mysql主从复制

一.Mysql主从复制介绍

1.Mysql主从复制原理

① 基于二进制文件实现

2.Mysql的复制类型

① 基于语句的复制（TSATEMENT,Mysql的默认类型）
② 基于行的复制（ROW）
③ 混合类型的复制（MIXED）

3.Mysql主从复制的工作过程

① Master节点将数据的改变记录成二进制日志（bin log），当Master上的数据发生改变时，则将其改变写入二进制日志中
② slave节点会在一定时间间隔内对Master的二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O线程请求 Master的二进制事件
③ 同时Master节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至slave节点本地的中继日志（Relay log）中，slave节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，即解析成 sql 语句逐一执行，使得其数据和 Master节点的保持一致，最后I/O线程和SQL线程将进入睡眠状态，等待下一次被唤醒

二.搭建 Mysql主从复制

1.首先关闭防火墙

systemctl stop firewalld
systemctl disable firewalld
setenforce 0
1
2
3

2.Mysql主从服务器时间同步

① 主服务器设置

yum install ntp -y
vim /etc/ntp.conf
--末尾添加--
  server 127.127.247.0							#设置本地是时钟源，注意修改网段
  fudge 127.127.247.0 stratum 8				#设置时间层级为8（限制在15内）
service ntpd start
1
2
3
4
5
6

[root@www ~]# yum install ntp -y
已加载插件：fastestmirror, langpacks
file:///mnt/repodata/repomd.xml: [Errno 14] curl#37 - "Couldn't open file /mnt/repodata/repomd.xml"
正在尝试其它镜像。
Loading mirror speeds from cached hostfile
软件包 ntp-4.2.6p5-25.el7.centos.2.x86_64 已安装并且是最新版本
无须任何处理
[root@www ~]# vim /etc/ntp.conf
server 127.127.247.0							
  fudge 127.127.247.0 stratum 8	
[root@www ~]#  service ntpd start
1
2
3
4
5
6
7
8
9
10
11

②从服务器设置

yum install ntp ntpdate -y
service ntpd start
/usr/sbin/ntpdate 192.168.247.131				#进行时间同步
crontab -e
  */30 * * * * /usr/sbin/ntpdate 192.168.247.131
1
2
3
4
5

[root@localhost yum.repos.d]# yum install ntp ntpdate -y
已加载插件：fastestmirror, langpacks
Loading mirror speeds from cached hostfile
软件包 ntp-4.2.6p5-25.el7.centos.2.x86_64 已安装并且是最新版本
软件包 ntpdate-4.2.6p5-25.el7.centos.2.x86_64 已安装并且是最新版本
无须任何处理
[root@localhost yum.repos.d]# service ntpd start
Redirecting to /bin/systemctl start ntpd.service
[root@localhost yum.repos.d]# /usr/sbin/ntpdate 192.168.247.131
25 Jun 19:28:22 ntpdate[35364]: the NTP socket is in use, exiting
[root@localhost yum.repos.d]# crontab -e
no crontab for root - using an empty one
crontab: installing new crontab
  */30 * * * * /usr/sbin/ntpdate 192.168.247.131
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3.主服务器

① mysql配置文件

vim /etc/my.cnf
  server-id=11
  log-bin=mysql-bin			#添加，主服务器开启二进制日志
  binlog_format=mixed
1
2
3
4

[root@www ~]# vim /etc/my.cnf
  server-id=11
  log-bin=mysql-bin			
  binlog_format=mixed
1
2
3
4

② 选配项

 vim /etc/my.cnf
  expire_logs_days=7			#设置二进制日志文件过期时间，默认值为0，表示logs不过期
  max_binlog_size=500M		#设置二进制日志限制大小，如果超出给定值，日志就会发生滚动，默认值是1GB
  skip_slave_start=1			#阻止从库崩溃后自动启动复制，崩溃后再自动复制可能会导致数据不一致的
  innodb_flush_logs_at_trx_commit=1     #双1设置
  sync_binlog=1                         #双1设置
1
2
3
4
5
6

③ 实验操作

systemctl restart mysqld
mysql -u root -pabc123
grant replication slave on *.* to 'myslave'@'192.168.80.%' identified by '123456   #给从服务器授权
flush privileges;
show master status;
1
2
3
4
5

[root@www ~]# systemctl restart mysqld
[root@www ~]# mysql -u root -pabc123
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.41-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> 
mysql> grant replication slave on *.* to 'myslave'@'192.168.247.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
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.000017 |      604 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

4.从服务器

① mysql配置文件

vim /etc/my.cnf
  server-id = 22							#修改，注意id与Master的不同，两个slave的id也要不同
  relay-log=relay-log-bin					#开启中继日志，从主服务器上同步日志文件记录到本地
  relay-log-index=relay-log-bin.index		#定义中继日志文件的位置和名称，一般和relay-log在同一目录
1
2
3
4

[root@localhost yum.repos.d]# vim /etc/my.cnf
  server-id = 22							
  relay-log=relay-log-bin					
  relay-log-index=relay-log-bin.index
1
2
3
4

② 选配项

innodb_buffer_pool_size=2048M		#用于缓存数据和索引的内存大小，让更多数据读写内存中完成，减少磁盘操作，可设置为服务器总可用内存的 70-80%
sync_binlog=0						#MySQL不做任何强制性的磁盘刷新指令，而是依赖操作系统来刷新数据到磁盘
innodb_flush_log_at_trx_commit=2	#每次事务log buffer会写入log file，但一秒一次刷新到磁盘
log-slave-updates=0					#slave 从 master 复制的数据会写入二进制日志文件里，从库做为其他从库的主库时设置为 1
relay_log_recovery=1				#当 slave 从库宕机后，假如 relay-log 损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的 relay-log， 并且重新从 master 上获取日志，这样就保证了 relay-log 的完整性。默认情况下该功能是关闭的，将 relay_log_recovery 的值设置为 1 时， 可在 slave 从库上开启该功能，建议开启。
1
2
3
4
5

③ 实验操作

systemctl restart mysqld
mysql -u root -pabc123
change master to master_host='192.168.80.10',master_user='myslave',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=604;		#配置同步，注意 master_log_file 和 master_log_pos 的值要与Master查询的一致
start slave;						#启动同步，如有报错执行 reset slave;
show slave status\G					#查看 slave 状态
slave_IO_Running: Yes			#负责与主机的io通信，Yes代表同步正常
slave_SQL_Running: Yes			#负责自己的slave mysql进程，Yes代表同步正常
1
2
3
4
5
6
7

[root@localhost yum.repos.d]# systemctl restart mysqld
[root@localhost yum.repos.d]# mysql -uroot -pabc123
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 3
Server version: 5.7.41 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> change master to 
    -> master_host='192.168.247.131',master_user='myslave',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=604;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.247.131
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 604
               Relay_Log_File: relay-log-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
       ......
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

三.验证主从复制效果

1.主服务器

create database db_test;
1

mysql>  create database db_test;
Query OK, 1 row affected (0.01 sec)
1
2

2.从服务器

show databases;
1

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

四.实验中会发生的问题及解决方案

1.实验中可能发生的问题（ IO 和 SQL 线程是NO状态情况）

① 网络不通

② my.cnf配置有问题

③ 密码、file文件名、pos偏移量不对

④ 防火墙没有关闭

2.MySQL主从复制延迟

① master服务器高并发，形成大量事务

② 网络延迟

③ 主从硬件设备导致：cpu主频、内存io、硬盘io

④ 是同步复制、而不是异步复制

3.解决方案

① 从库优化Mysql参数。比如增大innodb_buffer_pool_size，让更多操作在Mysql内存中完成，减少磁盘操作

② 从库使用高性能主机。包括cpu强悍、内存加大。避免使用虚拟云主机，使用物理主机，这样提升了i/o方面性

③ 从库使用SSD磁盘

④ 网络优化，避免跨机房实现同步