1.MySQL数据库管理之主从复制

1.1MySQL主从复制的原理

mysql的主从复制是一个异步的复制过程（虽然一般情况下感觉是实时的），数据将从一个mysql数据库（我们称之为master）复制到另一个mysql数据库（我们称之为slave），在master与slave之间实现整个主从复制的过程是由三个线程参与完成的，其中有两个线程（SQL线程和IO线程）在slave端，另外一个线程（I/O线程）在master端，要实现mysql的主从复制，首先必须打开master端的binlog记录功能，否则就无法实现，因为整个复制过程实际上就是slave从master端获取binlog日志，然后在slave上以相同顺序执行获取的binlog日志中所记录的各种SQL操作

1.2主从复制的原理图

解释说明：
复制的方向为单向的复制从主到从，复制的过程当作有三个线程来完成，主库有一个IO线程（bin-logdump线程）；从库有两个线程来完成
一个是IO线程一个是SQL线程，主从复制要想完成需要靠bin-log功能，所以①必须打开bin-log开关，"log-bin = /data/3306/mysql-bin"
bin-log记录增删改（无查）数据，②建立一个复制的账号为reb，给从库连接用，授权为replication client，（建立从库访问的账号）
③进行停库全备拿到bin-log的post位置点--master-data=1或2；全备的数据恢复到从库④从库上寻找post位置点，执行change master to
连接主库信息，位置点，告诉从库从那个位置点同步的信息存在于从库的master.info文件里面，这个文件的作用就是记录Slave连接主库的账号
主机等信息，还包括从那个bin-log的post位置点进行复制(执行change master to就会写到mster.info里面)change master to需要人工操作，
⑤从库开启复制开关：start slave；回车复制开始，开始之后从库连接主库带着账号，密码等信息请求主库，当主库验证从库通过时（replication
client进行同步的权限）主库的IO线程会进行与从库的IO线程进行沟通，主库IO线程读取到从库的IO线程的请求后，会进行判断mster.info里面的信
息，要同步那个位置点以下的数据的bin-log，然后返回给从库IO线程（信息里有binlog当前的pos），接着从库会把收到的binlog信息存放到relay-log
里面（跟bin-log一样也是二进制文件），从库的IO线程也会把主库发过来的pos位置更新到master.info里面的，最后IO线程会重复请求主库从更新的post
位置点继续请求没读取完的内容；⑥当第一次从库的IO线程把bin-log写入到relay-log里面会出发一个SQL线程，SQL线程会不断的读取relay-log，它会
把relay-log的内容按顺序进行解析成SQL语句，会按照主库执行的顺序写入到数据库里面；同理也会有一个relay-log.info文件同样记录relaylog的位置
进行下次读取

实现主从复制小结：

1. 主库开启bin-log
2. 主库建立同步账号，进行授权
3. 从库进行配置master.info(change master to)
4. 打开start slave复制开关
5. server-id不能相同

1.3实践MySQL主从复制

1.3.1环境准备

mysql多实例，其中向外提供服务的端口分别为3306，3307，其中3306为主库，开启了binlog功能，3307为从库，并且两个数据库中的server-id是不同

1.3.2主库操作

0.确认服务启动
[root@node ~]# netstat -lntup|grep 330
tcp       0        0 0.0.0.0:3306       0.0.0.0:*      LISTEN         4657/mysqld 
tcp       0        0 0.0.0.0:3307       0.0.0.0:*      LISTEN         5397/mysqld

1.开启binlog开关
[root@node ~]# grep log-bin /data/3306/my.cnf 
log-bin = /data/3306/mysql-bin

2.server-id不能相同
[root@node ~]# egrep "log-bin|server-id" /data/{3306..3307}/my.cnf 
/data/3306/my.cnf:log-bin = /data/3306/mysql-bin
/data/3306/my.cnf:server-id = 6
/data/3307/my.cnf:#log-bin = /data/3307/mysql-bin
/data/3307/my.cnf:server-id = 7

3.主库授权rep账户
mysql> grant replication slave on *.* to 'rep'@'172.16.1.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'rep'@'172.16.1.%'\G
*************************** 1. row ***************************
Grants for rep@172.16.1.%: GRANT REPLICATION SLAVE ON *.* TO 'rep'@'172.16.1.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
1 row in set (0.00 sec)

4.主库锁表全备（--master-data=2）sed -n '22'p +备份文件名 过滤出change语句
mysql> flush table with read lock;   ##窗口不能退出
Query OK, 0 rows affected (0.00 sec)
查看binlog文件及位置点
mysql> show master status;  ##全备与增备的临界点
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000025 | 344      |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5.xshell窗口不能退出重新打开一个窗口导出全备
~]# mkdir -p /server/backup/
[root@node ~]# mysqldump -uroot -p12345678 -S /data/3306/mysql.sock -A -B --events |gzip >/server/backup/rep_bak_$(date +%F).sql.gz
[root@node ~]# ll /server/backup/rep_bak_$(date +%F).sql.gz
-rw-r--r-- 1 root root 146372 Jun 26 05:09 /server/backup/rep_bak_2018-06-26.sql.gz

6.解除锁表
mysql> unlock table;
Query OK, 0 rows affected (0.00 sec)

1.3.3从库操作

1.确保所有实例server-id不同
[root@node ~]# egrep "log-bin|server-id" /data/{3306..3307}/my.cnf 
/data/3306/my.cnf:log-bin = /data/3306/mysql-bin
/data/3306/my.cnf:server-id = 6
/data/3307/my.cnf:#log-bin = /data/3307/mysql-bin
/data/3307/my.cnf:server-id = 7

2.把主库的全备数据导入到从库
[root@node ~]# cd /server/backup/
[root@node backup]# gzip -d rep_bak_2018-06-26.sql.gz
[root@node backup]# mysql -uroot -S /data/3307/mysql.sock <rep_bak_2018-06-26.sql 

3.找位置点，配置master.info
mysql-bin.000025 |      344 |

4.查看从库是否有master.info文件
[root@node backup]# find /data/3307/data/ -type f -name "*.info"
命令语句：
CHANGE MASTER TO 
MASTER_HOST='172.16.1.68',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000025', 
MASTER_LOG_POS=344;
执行此命令语句之后再次查看
[root@node backup]# find /data/3307/data/ -type f -name "*.info"
/data/3307/data/master.info

5.开启从库slave开关进行同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

6.查看从库状态是否进行同步
mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 172.16.1.68
 Master_User: rep
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000025
 Read_Master_Log_Pos: 344
 Relay_Log_File: relay-bin.000002
 Relay_Log_Pos: 253
 Relay_Master_Log_File: mysql-bin.000025
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB: 
 Replicate_Ignore_DB: mysql
 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: 344
 Relay_Log_Space: 403
 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: 6
1 row in set (0.00 sec)

1.3.4进行验证

主库执行如下语句
mysql> create database cuncun01;
Query OK, 1 row affected (0.01 sec)

主库查看结果
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cuncun01           |
| john               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.01 sec)

登录从库查看是否同步
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cuncun01           |
| john               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

1.3.5问题小结

1. 如果change master里的东西设置错误，修改master.info文件不生效，解决办法执行reset slave all;语句
2. 主库授权必须是replication slave权限

1.4关于主从复制出现故障怎么解决

Slave_IO_Running: Yes  
Slave_SQL_Running: NO

解决办法1：
登录从库上操作：
1、stop slave； #<==临时停止同步开关
2、set global sql_slave_skip_counter = 1; #<==将同步指针向下移动一个，也可以多个，如果多次不同步，可以重复操作；
3、start slave；#<==重启主从复制开关

解决办法2：
在配置文件中加入参数（/etc/my.cnf）
slave-skip-errors = 1032,1062,1007
其他可能引起复制故障的问题：
1、mysql自身的原因及人为重复插入数据
2、不同的数据库版本会引起不同步，低版本到高版本可以，但是高版本不能往低版本同步
3、mysql的运行错误或者程序BUG
4、binlog记录模式，例如：row level模式就比默认的语句模式更好

1.5快速实现主从同步

启动3308数据库
[root@node ~]# netstat -lntup|grep 3308
tcp       0        0 0.0.0.0:3308       0.0.0.0:*      LISTEN         4673/mysqld

~]# mysqldump -u root -p12345678 -S /data/3306/mysql.sock -A joh -B -F -R -x --events --master-data=1 |gzip >/server/backup/3308reb_bak_$(date +%F).sql.gz
命令说明：
--master-data=1  ##恢复时自动执行change master语句
~]# cd /server/backup/
[root@node backup]# gzip -d 3308reb_bak_2018-06-26.sql.gz
[root@node backup]# sed -n '22'p 3308reb_bak_2018-06-26.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000026', MASTER_LOG_POS=107;

把数据导入到3308数据库
[root@node backup]# mysql -u root -S /data/3308/mysql.sock <3308reb_bak_2018-06-26.sql 
[root@node backup]# find /data/3308/data/ -type f -name "*.info"
/data/3308/data/master.info

3308从库执行的change语句为：
CHANGE MASTER TO 
MASTER_HOST='172.16.1.68',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456';

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

mysql> show processlist; ##查看线程的状态信息

1.6MySQL从库记录bin-log日志的方法

从库需要记录binlog的应用场景为：当前的从库还要作为其他从库的主库，例如：级联复制或双主互为主从的场景的情况下，下面是从库记录binlog日志的方法

在从库的my.cnf中加入如下参数，然后重启服务生效即可

log-slave-updates #<== 必须要有这个参数从库才能记录binlog
log-bin = /data/3307/mysql-bin
expire_logs_days = 7 #<==相当于find /data/3307/ -type f –name “mysql-bin.000*” –mtime +7|xargs rm –f

1.7MySQL主从复制集群架构的数据备份策略

有主从复制了，还需要做定时全量加增量备份么？

因为，如果主库有语句级误操作（例如：drop database john），从库也会执行drop database john；这样mysql主从库就都删除了该数据

把从库做为数据库备份服务器时，备份策略如下:

高并发业务场景备份时，可以选择在一台从库上备份（slave5），把从库作为数据库备份服务器时需要在从库开启binlog功能，其逻辑图如下所示

步骤如下：

1）选择一个不对外提供服务的从库，这样可以确保和主库更新最接近，专门做数据备份

2）开启从库的binlog功能

备份时可以选择只停止SQL线程，停止应用SQL语句到数据库，IO线程保留工作状态，执行命令为stop slave sql_thread;备份方式可以采取mysqldump逻辑备份或者直接物理备份，例如：物理备份使用cp、tar（针对/data目录）或xtrabackup（第三方的物理备份软件）进行备份，逻辑备份根据总的备份数据量的多少进行选择，数据量低于20G，建议选择mysqldump逻辑备份方案，安全稳定，最后把全备和binlog数据发送到备份服务器留存

1.8主从复制延迟问题原因及解决方案

问题一：一个主库的从库太多，导致复制延迟

建议从库数量3~5个为最佳，要复制的从库节点数量过多，会导致复制延迟

问题二： 从库硬件比主库差，导致复制延迟

查看master和slave的系统配置，可能会因为机器配置的问题，包括磁盘IO、CPU、内存等各方面因素造成复制的延迟，一般发生在高并发大数据量写入场景

问题三：慢SQL语句过多

假如一条SQL语句，执行时间是20秒，那么执行完毕，到从库上能查到数据也至少是20秒，这样就延迟20秒了
SQL语句的优化一般要作为常规工作不断的监控和优化，如果是单个SQL的写入时间长，可以修改后分 多次写入，通过查看慢查询日志或show full processlist命令找出执行时间长的查询语句或者大的事务

问题四：主从复制的设计问题

例如，主从复制单线程，如果主库写并发太大，来不及传送到从库就会导致延迟。更高版本的mysql可以支持多线程复制，门户网站则会自己开发多线程同步功能

问题五：主从库之间的网络延迟
主从库的网卡，网线，连接的交换机等网络设备都可能成为复制的瓶颈，导致复制延迟，另外，跨公网主从复制很容易导致主从复制延迟

问题六： 主库读写压力大，导致复制延迟
主库硬件要搞好一点，架构的前端要加buffer以及缓存层

1.9通过read-only参数让从库只读访问

一般情况下从库禁止写入数据
read-only参数选项可以让从服务器只允许来自从服务器线程或具有super权限的数据库用户进行更新，可以确保从服务器不接受来自用户端的非法用户更新
read-only参数允许数据更新的条件为：
1、具有super权限的用户可以更新，不受read-only参数影响，例如：管理员root
2、来自从服务器线程可以更新，不受read-only参数影响，例如：前文的rep用户
在生产环境中，可以在从库slave中使用read-only参数，确保从库数据不被非法更新
read-only参数配置方法如下：

方法一：启动数据库时直接带--read-only参数启动或重启，使用
mysqladmin –uroot –p12345678 –S /data/3307/mysql.sock shutdown 
mysqld_safe –defaults-file=/data/3307/my.cnf --read-only &

方法二：在my.cnf里[mysqld]模块下加read-only参数，然后重启数据库，配置如下
[mysqld]
read-only

2.web用户企业级设置方案：MySQL主从复制读写分离集群

专业的运维人员提供给开发人员的读写分离的账号设置方法如下：
1） 访问主库和从库时使用一套用户密码，例如,用户为web，密码为john123
2） 即使访问IP不同，端口也尽量相同（3306），例如：写库VIP为10.0.0.7，读库VIP为10.0.0.8 
除了IP没办法修改之外，要尽量为开发人员提供方便，如果是数据库前端有DAL层（DBPROXY）,还可以只给开发人员一套用户、密码、IP、端口，这样就更专业了，剩下的都由运维人员搞定。
下面是授权web连接用户访问的方案：mysql主从复制读写分离集群。

方法1：主库和从库使用不同的用户，授权不同的权限
主库上对web_w用户授权如下：
用户：web_w 密码：john123 端口：3306 主库VIP：10.0.0.7
权限：select，insert，update，delete
命令：grant select,insert,update,delete on web.* to web_w@’172.16.1.%’ identified by ‘john123’
从库上对web_r用户授权如下：
用户：web_r 密码：john123 端口：3306 从库VIP：10.0.0.8 
权限：select
命令：grant select on web.* to web_r@’172.16.1.%’ identified by ‘john123’
提示：此法显得不够专业，但是可以满足开发需求

方法2：主库和从库使用相同的用户，但授予不同的权限（推荐）
主库上对web用户授权如下：
用户：web 密码：john123 端口：3306 主库VIP：10.0.0.7
权限：select，insert，update，delete
命令：grant select,insert,update,delete on web.* to web@’172.16.1.%’ identified by ‘john123’
从库上对web用户授权如下：
用户：web 密码：john123 端口：3306 从库VIP：10.0.0.8 
权限：select
命令：grant select on web.* to web@’172.16.1.%’ identified by ‘john123’
提示：由于主库和从库都是同步复制的，所以从库上的web用户会自动和主库的一致，即无法实现只读select的授权，要想实现方法2中的授权方案，有两个方法：
一是在主库上创建完用户和权限，从库上revoke收回对应更新权限（insert，update，delete）
二是忽略授权表mysql同步，主库的配置参数如下：
[mysqld]
binlog-ignore-db = mysql
replicate-ignore-db = mysql
提示：注意上面参数等号两边必须要有空格

方法三：在从库上设置read-only参数，让从库只读
主库从库：主库和从库使用相同的用户，授予相同的权限（非ALL权限）
用户：web 密码：john123 端口：3306 主库VIP：10.0.0.7 从库VIP：10.0.0.8
权限：select，insert，update，delete
命令：grant select,insert,update,delete on web.* to web@’172.16.1.%’ identified by ‘john123’
由于从库设置了read-only，非super权限是无法写入的，因此，通过read-only参数就可以很好的控制用户非法将数据写入从库

Get busy living or get busy dying. 努力活出精彩的人生，否则便如行尸走肉

AD：《熊孩子博客》熊孩子QQ:443060965