专职DBA-MySQL主从延迟复制
MySQL主从延迟复制
本次实验环境延用MySQL主从异步复制的搭建环境
mysql集群企业级架构方案
1.根据对数据库的访问请求实现读写分离（读从写主）
2.根据不同的业务拆分多个从库以提供访问
一主五从
3从提供外部用户读请求访问（读写分离、LVS负载均衡）
1从用于内部用户读访问（业务后台、数据分析、搜索业务、财务统计、定时任务、开发查询等）
1从用于数据库定时全备份，以及增量备份（开启binlog）
3.实现对主库的高可用
(1).heartbeat+dbrd+mysql方案
通过dbrd工具对主数据库服务器实现基于block的异机物理复制，类似于网络RAID1.
优点：速度很快。
缺点：不能被访问，除非主节点宕机，备节点才可以提供访问。
(2).mysql-MMM（Master-Master replication Manager）方案
通过mysql的replication实现主主之间的数据同步。
优点：可以实现slaves负载均衡。
缺点：MMM无法完全保持数据的一致性。
(3).mysql-MHA（Master High Availability）+keepalived方案
通过mysql的replication实现数据库服务器之间的数据同步。
优点：同时可以实现从库负载均衡，主库宕机后自动选择最优的从库，将其切换为主库。
并尽最大的努力对有所有的库做数据补全操作，一直到最新。
并对其他从库和新主库实现复制，再加上keepalived是为了实现vip漂移。
(4).PXC
(5).共享存储方案
(6).数据库分布式部署方案
(7).MGR
mysql企业级备份策略方案
1.利用mysql主从复制的从库进行数据备份策略
(1).选择一个不对外提供服务的从库，专门做数据备份用。
(2).开启从库的binlog功能。
(3).数据量小于30GB用mysqldump逻辑备份；
数据库大于30GB用Xtrabackup物理热备工具。
mysql主从复制生产环境的常见延迟原因
易导致复制延迟的原因：
1.一个主库的从库太多
2.从库硬件比主库查
3.慢sql语句过多
4.主从复制的设计问题
5.主从复制之间的网络延迟
6.主库读写压力太大
mysql主从复制数据一致性企业级方案
1.采用半同步复制方案
2.当复制发生延迟时让程序改读主库
mysql多线程复制解决复制延迟实践
[root@db01 ~]# mysqld --defaults-file=/data/mysql/3306/my.cnf &
[root@db02 ~]# mysqld --defaults-file=/data/mysql/3306/my.cnf &
(1).查看当前slave服务器的SQL线程状态
[root@db02 ~]# mysql -S /data/mysql/3306/mysql.sock -p
Enter password:
Slave [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 49 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 48 | Slave has read all relay log; waiting for more updates | NULL |
| 4 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
(2).检查多线程的参数配置
默认为0表示单线程复制
Slave [(none)]> show variables like "%parallel%";
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| slave_parallel_type | DATABASE |
| slave_parallel_workers | 0 |
+------------------------+----------+
2 rows in set (0.01 sec)
(3).停止主从复制，在线修改线程数
Slave [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
Slave [(none)]> set global slave_parallel_workers = 4;
Query OK, 0 rows affected (0.00 sec)
Slave [(none)]> show variables like "%parallel%";
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| slave_parallel_type | DATABASE |
| slave_parallel_workers | 4 |
+------------------------+----------+
2 rows in set (0.00 sec)
(4).启动主从复制，查看SQL线程数
Slave [(none)]> start slave;
Query OK, 0 rows affected (0.04 sec)
Slave [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 4 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 5 | system user | | NULL | Connect | 28 | Waiting for master to send event | NULL |
| 6 | system user | | NULL | Connect | 28 | Slave has read all relay log; waiting for more updates | NULL |
| 7 | system user | | NULL | Connect | 28 | Waiting for an event from Coordinator | NULL |
| 8 | system user | | NULL | Connect | 28 | Waiting for an event from Coordinator | NULL |
| 9 | system user | | NULL | Connect | 28 | Waiting for an event from Coordinator | NULL |
| 10 | system user | | NULL | Connect | 28 | Waiting for an event from Coordinator | NULL |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
7 rows in set (0.00 sec)
(5).想永久生效就写入my.cnf
[root@db02 ~]# vim /data/mysql/3306/my.cnf
[mysqld]
slave_parallel_workers = 4
让mysql主从复制的从库只读访问
1.read-only参数允许数据库更新的条件
(1).具有super权限的用户可以更新，不受read-only参数影响。例如：root
(2).来自从服务器具备主从复制权限的线程可以更新，不受read-only参数的影响。例如：rep
2.如何配置read-only参数
(1).启动数据库时直接带--read-only参数启动。
mysqld_safe --read-only --user=mysql &
(2).在my.cnf文件中配置
[root@db02 ~]# vim /data/mysql/3306/my.cnf
[mysqld]
read-only
然后重启数据库
mysqladmin -S /data/mysql/3306/mysql.sock -p shutdown
mysqld --defaults-file=/data/mysql/3306/my.cnf &
mysql主从复制读写分离Web用户生产设置方案
在配置好mysql主从复制，并实现了读写分离以后，数据库授权程序访问的用户设置方法：
1.主库和从库使用不同的用户，授予不同的权限。
主库上对web_w用户的授权
grant select,insert,update,delete on `web`.* to ‘web_w‘@‘192.168.10.%‘ identified by ‘123‘;
从库上对web_r用户的授权
grant select on `web`.* to ‘web_r‘@‘192.168.10.%‘ identified by ‘123‘;
2.网站程序访问主库和从库时使用一套用户密码。
(1).主库和从库使用相同的用户，但授予不同的权限。
忽略主库的mysql授权库同步
[root@db01 ~]# vim /data/mysql/3306/my.cnf
binlog-ignore-db = mysql #mysql库不记录binlog日志
replicate-ignore-db = mysql #忽略复制mysql库
在主库上创建完web用户和权限之后，在从库上revoke回收对应的更新权限
主库：grant select,insert,update,delete on `web`.* to ‘web‘@‘192.168.10.%‘ identified by ‘123‘;
从库：grant select on `web`.* to ‘web‘@‘192.168.10.%‘ identified by ‘123‘;
在从库上设置read-only参数，让从库只读
[root@db02 ~]# vim /data/mysql/3306/my.cnf
[mysqld]
read-only
然后重启数据库
mysqladmin -S /data/mysql/3306/mysql.sock -p shutdown
mysqld --defaults-file=/data/mysql/3306/my.cnf &
mysql主从延迟复制方案及恢复实践
[root@db02 ~]# mysql -S /data/mysql/3306/mysql.sock -p
Enter password:
Slave [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)
Slave [(none)]> change master to master_delay = 60;
Query OK, 0 rows affected (0.01 sec)
Slave [(none)]> start slave;
Query OK, 0 rows affected (0.02 sec)
Slave [(none)]> show slave status\G
*************************** 1. row ***************************
SQL_Delay: 60 #延迟60秒进行复制
SQL_Remaining_Delay: NULL #还剩多少秒执行复制
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates #sql线程状态
[root@db01 ~]# mysql -S /data/mysql/3306/mysql.sock -p
Enter password:
Master [(none)]> create database app;
Query OK, 1 row affected (0.00 sec)
Master [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| app |
| mysql |
| performance_schema |
| shenzhen |
| sys |
+--------------------+
6 rows in set (0.00 sec)
Slave [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shenzhen |
| sys |
+--------------------+
5 rows in set (0.01 sec)
但是中继日志里面已经有创建的语句了，说明IO线程还是实时在工作的。
[root@db02 ~]# cd /data/mysql/3306/data/
[root@db02 /data/mysql/3306/data]# ls -l
total 122952
-rw-r----- 1 mysql mysql 56 Jul 15 05:52 auto.cnf
-rw-r----- 1 mysql mysql 206 Jul 16 01:43 db02-relay-bin.000001
-rw-r----- 1 mysql mysql 476 Jul 16 01:47 db02-relay-bin.000002
-rw-r----- 1 mysql mysql 48 Jul 16 01:43 db02-relay-bin.index
-rw-r----- 1 mysql mysql 599 Jul 15 06:54 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jul 16 01:23 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jul 16 01:23 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jul 15 05:52 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jul 16 01:23 ibtmp1
-rw-r----- 1 mysql mysql 122 Jul 16 01:48 master.info
drwxr-x--- 2 mysql mysql 4096 Jul 15 06:35 mysql
drwxr-x--- 2 mysql mysql 8192 Jul 15 05:52 performance_schema
-rw-r----- 1 mysql mysql 59 Jul 16 01:43 relay-log.info
drwxr-x--- 2 mysql mysql 48 Jul 15 06:50 shenzhen
drwxr-x--- 2 mysql mysql 8192 Jul 15 05:52 sys
-rw-r----- 1 mysql mysql 84 Jul 16 01:43 worker-relay-log.info.1
-rw-r----- 1 mysql mysql 84 Jul 16 01:43 worker-relay-log.info.2
-rw-r----- 1 mysql mysql 84 Jul 16 01:43 worker-relay-log.info.3
-rw-r----- 1 mysql mysql 84 Jul 16 01:43 worker-relay-log.info.4
[root@db02 /data/mysql/3306/data]# mysqlbinlog db02-relay-bin.000002
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database app
/*!*/;
过了1分钟后
Slave [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| app |
| mysql |
| performance_schema |
| shenzhen |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql的延迟复制实际上影响的只是SQL线程将数据应用到从库。
而IO线程早已把主库更新的数据写到了从库的中继日志里面。
因此，在延迟复制期间，即使主库宕机了，从库到了延迟复制的时间，也依然会把数据更新到与主库宕机时一致。
使用mysql主从延迟复制进行数据恢复实践
1.模拟环境，将从库延迟调整为3600秒
[root@db02 ~]# mysql -S /data/mysql/3306/mysql.sock -p
Enter password:
Slave [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)
[root@db02 ~]# mysql -u root -p -S /application/mysql/tmp/mysql.sock
Enter password:
mysql> stop slave ;
Query OK, 0 rows affected (0.00 sec)
Slave [(none)]> change master to master_delay = 3600;
Query OK, 0 rows affected (0.02 sec)
Slave [(none)]> start slave;
Query OK, 0 rows affected (0.03 sec)
Slave [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.11
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 350
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 350
Relay_Log_Space: 526
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 113306
Master_UUID: 7c145945-a680-11e9-baea-000c29a14cf7
Master_Info_File: /data/mysql/3306/data/master.info
SQL_Delay: 3600
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 7c145945-a680-11e9-baea-000c29a14cf7:1-4
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
2.模拟在主库写入数据，每隔5秒写入一个库，就当是模拟用户写入数据了
for n in {01..05}
do
mysql -S /data/mysql/3306/mysql.sock -p123 -e "create database app$n;"
sleep 5
done
[root@db01 ~]# for n in {01..05}
> do
> mysql -S /data/mysql/3306/mysql.sock -p123 -e "create database app$n;"
> sleep 5
> done
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@db01 ~]#
3.模拟人为破坏数据，也可以是不带where的update语句。
Master [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| app |
| app01 |
| app02 |
| app03 |
| app04 |
| app05 |
| mysql |
| performance_schema |
| shenzhen |
| sys |
+--------------------+
11 rows in set (0.00 sec)
删除oldboy5数据库，后面要做的就是把这个数据库恢复回来，别的数据还得保留。
Master [(none)]> drop database app05;
Query OK, 0 rows affected (0.01 sec)
Master [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| app |
| app01 |
| app02 |
| app03 |
| app04 |
| mysql |
| performance_schema |
| shenzhen |
| sys |
+--------------------+
10 rows in set (0.00 sec)
Master [(none)]> drop database app05;
Query OK, 0 rows affected (0.01 sec)
Master [(none)]> show databases like "%app%";
+------------------+
| Database (%app%) |
+------------------+
| app |
| app01 |
| app02 |
| app03 |
| app04 |