Mysql—主从复制的slave添加及延迟回放

MySQL 主从复制是什么？

MySQL 主从复制是指数据可以从一个 MySQL 数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

MySQL 主从复制原理

（1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；

（2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件

（3）同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

如何实现主从复制

我这里用两台虚拟机(Linux)演示，172.25.254.10为主(Master) ，172.25.254.20为从(Slave)

在172.25.254.10(Master)主机上进行配置

[root@nginx ~]# vim /etc/my.cnf
server-id=10  #设置id为10
[root@nginx ~]# /etc/init.d/mysqld restart 
Shutting down MySQL. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@nginx ~]# mysql -uroot -p -e "SELECT @@server_id"

生成二进制文件mysql-bin.000001

[root@nginx ~]# vim /etc/my.cnf
log-bin=mysql-bin
[root@nginx mysql]# /etc/init.d/mysqld restart
[root@nginx mysql-5.7.44]# cd /data/mysql/
[root@nginx mysql]#ls

建立用户repl@'%'使用远程连接，密码OpenXXX123!

这个权限允许用户作为复制从属服务器（slave）接收主服务器（master）的二进制日志事件并应用这些事件。

mysql> CREATE USER repl@'%' IDENTIFIED BY 'OpenXXX123!';#确保每台SLAVE都能连得上MASTERmysql> GRANT REPLICATION SLAVE ON . TO repl@'%';

mysql> SHOW MASTER status;

mysql> show master status\G;

172.25.254.20(Slave)主机上进行配置

[root@nginx ~]# vim /etc/my.cnf
server-id=20
[root@nginx ~]# /etc/init.d/mysqld restart 
Shutting down MySQL. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@nginx ~]# mysql -uroot -p -e "SELECT @@server_id"[root@nginx ~]# vim /etc/my.cnf
log-bin=mysql-bin
[root@nginx mysql]# /etc/init.d/mysqld restart
[root@nginx mysql-5.7.44]# cd /data/mysql/

mysql> change master to master_host='172.25.254.10',master_user='repl',master_password='OpenXXX123!',master_log_file='mysql-bin.000001',master_log_pos=595;mysql> start slave;mysql> show slave status\G;

当在172.25.254.10主机上创建timinglee库

mysql> create database timinglee;

再次创建timinglee.userlist表

mysql> create table timinglee.userlist(-> username varchar(10) not null,-> password varchar(50) not null);

在表中插入内容

mysql> INSERT INTO timinglee.userlist values('lee','123');

172.25.254.20主机上可以查看到timinglee库，表timinglee.userlist

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| timinglee          |
+--------------------+
mysql> SELECT * FROM timinglee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
+----------+----------+

自此主从复制配置完成

slave添加

在slave阶段中默认情况下是开启了写功能的，但是建议关闭slave节点的写功能来保证数据一致性

创建虚拟主机id为172.25.254.30充当slave2

完成基础配置

[root@mysql-node3 ~]# vim /etc/my.cnf[mysqld] 
datadir=/data/mysql 
socket=/data/mysql/mysql.sock 
symbolic-links=0 
server-id=30

在生产环境的时候需要对用户消息进行备份

从master（172.25.254.10）节点备份数据

[root@nginx ~]# mysqldump -uroot -p timinglee > timinglee.sql
Enter password: 
[root@nginx ~]# scp timinglee.sql root@172.25.254.30:/mnt/
root@172.25.254.30's password: 
timinglee.sql                                          100% 1952     1.6MB/s   00:00

将文件的内容传入到172.25.254.30的主机

172.25.254.30主机上面存在文件信息

利用master节点中备份出来的timinglee.sql在slave2（172.25.254.30）中拉平数据

[root@nginx ~]# mysql -uroot -p123 -e "create database timinglee;"[root@nginx ~]# mysql -uroot -p123 timinglee < /mnt/timinglee.sql

172.25.254.10和172.25.254.30必须一致

mysql> show master status;change master to master_host="172.25.254.10",master_user='relp',master_password='123',master_log_file="mysql-bin.00001",master_log_pos=1272;

当发现connnecting时为连接错误

mysql> stop slave;#停掉后进行更改mysql>change master to master_host='172.25.254.10',master_user='repl',master_password='OpenXXX123!',master_log_file='mysql-bin.000002',master_log_pos=154;#记得重启slavemysql> start slave;#使用命令进行查看mysql> show slave status\G;

显示为YES，则slave添加成功

172.25.254.10主机上进行验证

mysql> SELECT * FROM timinglee.userlist;

172.25.254.10主机上插入用户数据时

mysql> INSERT INTO timinglee.userlist values('lee1','123');

172.25.254.30主机上看到172.25.254.10插入的信息

mysql> SELECT * FROM timinglee.userlist;

延迟复制

延迟复制时用来控制sql线程的和i/o线程无关

这个延迟复制不是i/o线程过段时间复制，i/o是正常工作的

是日志已经保存在slave段，那个sql要等多久进行回放

当master端操作失误了，可以在salve端进行数据备份

在172.25.254.30进行数据备份

mysql> stop slave sql_thread;mysql> change master to master_delay=60;mysql> start slave sql_thread;mysql> show slave status\G;

在172.25.254.10主机上模拟误删操作

mysql> select * from timinglee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
| lee1     | 123      |
+----------+----------+
mysql>  delete from timinglee.userlist where username='lee1';
Query OK, 1 row affected (0.00 sec)mysql> select * from timinglee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
+----------+----------+
1 row in set (0.00 sec)

在172.25.254.30主机上查看表

60秒内信息还存在，过了60秒后信息不再存在

慢程序语句

慢查询，顾名思义，执行很慢的查询当执行SQL超过long_query_time参数设定的时间阈值（默认10s）时，就被认为是慢查询，这个 SQL语句就是需要优化的慢查询被记录在慢查询日志里慢查询日志默认是不开启的如果需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。

在172.25.254.10主机上查看慢查询功能是否开启

mysql>SHOW variables like "slow%"；

开启慢查询功能

mysql> SET GLOBAL slow_query_log=ON;

再次使用命令进行查看

mysql> show variables like 'slow%';

查看慢查询时间，时间为10s

mysql> SHOW VARIABLES like "long%";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

测试慢查询，结果显示为10秒