Mysql主从复制之延时同步

1.延时同步概念

通过人为配置从库和主库延时N小时可以实现延时同步，延时同步可以解决数据库故障出现的数据丢失问题(物理损坏如直接使用rm删除数据库数据和逻辑损坏如使用drop命令删除数据库)

2.延时同步实操

2.1先配置从库延时同步，并且设置sql线程300秒后读取relay log

mysql> stop replica;
mysql> CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 300;
mysql>start replica;
mysql> show replica status\G
SQL_Delay: 300   #在这里可以查看到延时时间已经设置为300秒
SQL_Remaining_Delay: NULL

2.2主库上创建库和表

#创建数据库为relay
mysql> create database relay;
mysql> use relay
#创建表
mysql> create table t1(id int);
mysql> insert into t1 values (1);
#删除表
mysql> drop database relay;

2.3从库上查看sql线程及找到误删数据的位置

#查看sql线程时间
mysql> show slave status \G
                    SQL_Delay: 300
          SQL_Remaining_Delay: 279
#在sql线程延迟300秒时间内发现误删除数据库则立马停止从库的sql线程
mysql> stop slave sql_thread;
#找到误删前relay log的起点和终点
mysql> show replica status\G
               Relay_Log_File: slave1-relay-bin.000002
                Relay_Log_Pos: 323  #当前读取的relay日志的位置

#输入以下命令，找到误删数据的位置
mysql> show relaylog events in "slave1-relay-bin.000002";

2.4从库进行备份，并解除从库身份变成主库

#将relay log进行备份
[root@rep1 mysql]# mysqlbinlog --start-position=323 --stop-position=1053  /data/mysql/slave1-relay-bin.000002 > /tmp/relay.sql

#恢复误删除的数据库
[root@rep1 mysql]# mysql -uroot -p -e 'source /tmp/relay.sql'
#查看数据是否恢复
mysql> select * from relay.t1;
+------+
| id   |
+------+
|    1 |
+------+

#解除从库身份
mysql> stop replica;
mysql> reset replica all;

2.5从库变主库，备份误删的库到/backup/jeams

[root@mysql-mster172 ~]# mkdir /backup
[root@mysql-mster172 ~]# mysqldump -uroot -p123 --all-databases  > /backup/jeams.sql
#scp传输过去
[root@mysql-mster172 ~]# scp /backup/jeams.sql 192.168.75.171:~

2.6将之前的主库给初始化，变为现在的从库

#删除之前mysql所存储的文件
[root@mysql-master171 ~]# rm -rf /data/mysql/*
#进行初始化
[root@mysql-master171 ~]# mysqld --initialize --user=mysql
#启动mysql
[root@mysql-master171 ~]# /etc/init.d/mysqld start
#登录mysql，在上一条会给出初始密码
[root@mysql-master171 ~]# mysql -uroot -poERi2Hf,7x.B
#修改密码为123，然后输入时会让添密码，这时再次输入给出的初始密码即可
[root@mysql-master171 ~]# mysqladmin -uroot -p password '123'
#将从新主库发送来的备份数据传输到mysql里
[root@mysql-master171 ~]#mysql -uroot -p < ~/jeams.sql
#查看库是否更新新主库里面的内容
[root@mysql-master171 ~]# mysql -uroot -p -e 'show databases;'
#登录密码为123即可
[root@mysql-master171 ~]# mysql -uroot -p123

2.7进入新主库创建账户

#登录mysql
[root@mysql-mster172 ~]# mysql -uroot -p123
#创建用户为rep并设置密码rep123，允许任何主机登录
mysql> create user 'rep'@'%' identified  by 'rep123';
#给用户rep权限
mysql> grant  replication slave on *.* to 'rep'@'%';
#查看权限
mysql>  show grants for 'rep'@'%';
+---------------------------------------------+
| Grants for rep@%                            |
+---------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `rep`@`%` |
+---------------------------------------------+

2.8旧主库进行从库的配置

2.8.1查看新主库的状态，即当前日志文件名和二进制日志偏移量

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000008 |      324 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.8.2之前的旧主库进行新从库的配置

#登录mysql
[root@mysql-master171 ~]# mysql -uroot -p123
#设定从库向主库同步
mysql> CHANGE REPLICATION SOURCE TO
    -> SOURCE_HOST='192.168.75.172', #主库的ip地址
    -> SOURCE_USER='rep',            #主库mysql账户
    -> SOURCE_PASSWORD='rep123',     #主库mysql账户密码
    -> SOURCE_LOG_FILE='binlog.000008 ',  #主库的文件
    -> SOURCE_LOG_POS=324,              #主库的位置
    -> SOURCE_SSL=1; 
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> start replica;
Query OK, 0 rows affected (0.01 sec)

mysql> show replica status\G

3.测试

3.1在新主库创建库，并在新从库查看

新主库：

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

新从库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| haha               |
| information_schema |
| mysql              |
| performance_schema |
| relay              |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

3.2在新主库创建表，并在新从库查看

新主库:

mysql> use haha;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

新从库:

mysql> select * from haha.t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)