mysql主从 设置时延同步的测试

myslq数据库设置时延测试
master、slave都要执行

[root@master ~]# systemctl  stop  mariadb firewalld
[root@master ~]# setenforce 0
[root@master ~]# rpm -qa | grep maria
mariadb-libs-5.5.68-1.el7.x86_64
[root@master ~]# rpm -e  mariadb-libs-5.5.68-1.el7.x86_64
[root@master ~]# rpm -e --nodeps mariadb-server mariadb  mariadb-libs
[root@master ~]# rm -rf /etc/my.cnf
[root@master ~]# rm -rf /var/lib/mysql/*
[root@master ~]# tar xf mysql-5.7.17.tar

[root@master ~]# ls *.rpm
mysql-community-client-5.7.17-1.el7.x86_64.rpm
mysql-community-common-5.7.17-1.el7.x86_64.rpm
mysql-community-devel-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm
mysql-community-libs-5.7.17-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm
mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm
mysql-community-server-5.7.17-1.el7.x86_64.rpm
mysql-community-test-5.7.17-1.el7.x86_64.rpm
[root@master ~]# yum  -y   install    mysql-community-*.rpm
[root@master ~]# systemctl enable mysqld  --now              
[root@master ~]# systemctl status mysqld    
[root@master ~]# grep -i  'password' /var/log/mysqld.log  #查看初始密码
[Note] A temporary password is generated for root@localhost: k/oBOBnr&8C0

[root@master ~]# mysql -uroot -p
mysql> alter user root@localhost identified by "123qqq...A";
Query OK, 0 rows affected (0.00 sec)

[root@master ~]# systemctl stop mysqld
[root@master ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=master1
...
[root@master ~]# systemctl start mysqld
[root@master ~]# mysql -uroot -p123qqq...A
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master18.000001 |      154 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+

[root@slave ~]# mysql -uroot -p
mysql> change master to master_host="192.168.1.11",master_user="repluser", master_password="qqq123...A",master_log_file="master18.000001",master_log_pos=154;
mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.11
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master18.000001
          Read_Master_Log_Pos: 604
               Relay_Log_File: node1-relay-bin.000002
                Relay_Log_Pos: 769
        Relay_Master_Log_File: master18.000001
             Slave_IO_Running: Yes   #线程Ok
            Slave_SQL_Running: Yes   #线程Ok
...
                    SQL_Delay: 0   #时延60s

主从测试

master

mysql> create database  first;

slave

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| first              |    #同步ok
| mysql              |
| performance_schema |
| sys                |
+--------------------+

延时配置测试

slave设置延时

mysql> stop slave sql_thread;
mysql> change master to master_delay = 60;    #单位秒
mysql> start slave sql_thread;
mysql> show slave status\G
...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...
                    SQL_Delay: 60

master

mysql> select current_timestamp, current_timestamp();
+---------------------+---------------------+
| current_timestamp   | current_timestamp() |
+---------------------+---------------------+
| 2022-09-27 00:57:16 | 2022-09-27 00:57:16 |
+---------------------+---------------------+
mysql> create database  first2;

slave

mysql> select current_timestamp, current_timestamp();
+---------------------+---------------------+
| current_timestamp   | current_timestamp() |
+---------------------+---------------------+
| 2022-09-27 00:57:35 | 2022-09-27 00:57:35 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| first              |    #未出现first2
| mysql              |
| performance_schema |
| sys                |
+--------------------+

等待一会儿

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| first              |
| first2             |   #此时出现了first2.查看当前时间，超过60s
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysql> select current_timestamp, current_timestamp();
+---------------------+---------------------+
| current_timestamp   | current_timestamp() |
+---------------------+---------------------+
| 2022-09-27 00:58:39 | 2022-09-27 00:58:39 |
+---------------------+---------------------+

select current_timestamp, current_timestamp();

stop slave;
CHANGE MASTER TO MASTER_DELAY = 600；//单位为秒
start slave;
show slave status \G;