文章目录
一.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
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
[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
②从服务器设置
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
[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
3.主服务器
① mysql配置文件
vim /etc/my.cnf
server-id=11
log-bin=mysql-bin #添加，主服务器开启二进制日志
binlog_format=mixed
[root@www ~]# vim /etc/my.cnf
server-id=11
log-bin=mysql-bin
binlog_format=mixed
② 选配项
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设置
③ 实验操作
systemctl restart mysqld
mysql -u root -pabc123
grant replication slave on *.* to 'myslave'@'192.168.80.%' identified by '123456 #给从服务器授权
flush privileges;
show master status;
[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)
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在同一目录
[root@localhost yum.repos.d]# vim /etc/my.cnf
server-id = 22
relay-log=relay-log-bin
relay-log-index=relay-log-bin.index
② 选配项
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 从库上开启该功能，建议开启。
③ 实验操作
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代表同步正常
[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.主服务器
create database db_test;
mysql> create database db_test;
Query OK, 1 row affected (0.01 sec)
2.从服务器
show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
四.实验中会发生的问题及解决方案
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磁盘
④ 网络优化，避免跨机房实现同步
644
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
