1.1 企业Linux运维场景数据同步方案
1.1.1 文件级别的异机同步方案
1.1.2 文件系统级别的异机同步方案
1.2 MySQL主从复制
![](https://i-blog.csdnimg.cn/blog_migrate/0e58f27078b3767ba3dd8c4246dfba6a.jpeg)
![](https://i-blog.csdnimg.cn/blog_migrate/674e92058f210d4dc49e29bd7b5e114c.jpeg)
![](https://i-blog.csdnimg.cn/blog_migrate/b666cabb05f9d1ff5ed07989efb70507.jpeg)
![](https://i-blog.csdnimg.cn/blog_migrate/0b58a00e858c83d74098fc5927a78f2e.jpeg)
![](https://i-blog.csdnimg.cn/blog_migrate/23d69b71b35825922eab1310e4cbae2c.jpeg)
![](https://i-blog.csdnimg.cn/blog_migrate/ae122d0a115c7d08e25e1b7d02d40931.jpeg)
1.3 MySQL主从复制原理介绍
![](https://i-blog.csdnimg.cn/blog_migrate/1b7cdbc64dc3475d4cd58e41a6d99296.png)
![](https://i-blog.csdnimg.cn/blog_migrate/e076e3db51e8871d7fd300a027342757.jpeg)
画图:
1)在Slave 服务器上执行sart slave命令开启主从复制开关,开始进行主从复制。
2)此时,Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接master服务器,并请求从执行binlog日志文件的指定位置(日志文件名和位置就是在配置主从复制服务时执行change
master命令指定的)之后开始发送binlog日志内容
3)Master服务器接收到来自Slave服务器的IO线程的请求后,其上负责复制的IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息,然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外,还有在Master服务器端记录的IO线程。返回的信息中除了binlog中的下一个指定更新位置。
4)当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后,会将binlog日志内容依次写到Slave端自身的Relay Log(即中继日志)文件(Mysql-relay-bin.xxx)的最末端,并将新的binlog文件名和位置记录到master-info文件中,以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容
5)Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容,然后及时把Relay LOG 文件中的内容解析成sql语句,并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句,并在relay-log.info中记录当前应用中继日志的文件名和位置点
主从复制条件
知识点
小结:
1.4 环境搭建
[root@db02 3307]# netstat -lntup|grep 330
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3074/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 33364/mysqld
tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 34084/mysqld
[root@db02 3307]# grep log-bin /data/3306/my.cnf
log-bin = /data/3306/mysql-bin
[root@db02 3307]# grep server-id /data/3306/my.cnf
server-id = 1
[root@db02 3307]# grep server-id /data/3307/my.cnf
server-id = 3
[root@db02 3307]# grep server-id /data/3308/my.cnf
server-id = 2
- ================================================
[root@db02 3307]# grep server-id /data/{3306,3307,3308}/my.cnf
/data/3306/my.cnf:server-id = 1
/data/3307/my.cnf:server-id = 3
/data/3308/my.cnf:server-id = 2
mysql>grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';
mysql> flush privileges;
mysql>show grants for rep@'172.16.1.%';
mysql>select user,host from mysql.user
flush table with read lock; 锁表,窗口不能退出,退出失效
root@oldboy 05:16:22->show master status; 临界点,将来恢复就从0025开始
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000025 | 9155 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
备份
mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B --events|gzip >/server/backup/rep_bak$(date +%F).sql.gz
[root@db02 oldboy]# ls -lrt /server/backup/
total 308
-rw-r--r-- 1 root root 20 Dec 23 2015 bak_2015-12-23.sql.gz
-rw-r--r-- 1 root root 152214 Dec 23 2015 bak.sql.gz
-rw-r--r-- 1 root root 152238 Jun 29 17:20 rep_bak2016-06-29.sql.gz
解锁:unlock table;
root@oldboy 05:22:00->show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000025 | 9155 | | |
+------------------+----------+--------------+------------------+
#如果解锁之后还是mysql-bin.000025 说明是正确的,如果动了说明没有锁住表
#如果mysqldump 加了-F 他就会更改刷新binlog
从库操作
[root@db02 backup]# gzip -d rep_bak2016-06-29.sql.gz
[root@db02 backup]# mysql -uroot -p123456 -S /data/3307/mysql.sock <rep_bak2016-06-29.sql
CHANGE MASTER TO
MASTER_HOST='172.16.1.52', #这是主库的IP(域名也可以需要做解析)
MASTER_PORT=3306, #主库的端口,从库端口和主库不可以相同
MASTER_USER='rep', #这是主库上创建用来复制的用户rep
MASTER_PASSWORD='123456' #rep的密码
MASTER_LOG_FILE='mysql-bin.000025', #这里是show master status时看到的查询二进制日志文件名称,这里不能多空格
MASTER_LOG_POS=9155; #这里是show master status时看到的二进制日志偏移量,不能多空格
提示:3307操作此步:会在/data/3307/data下面产生master.info文件
root@oldboy 07:47:44->show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: www.etiantian.org
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000025
Read_Master_Log_Pos: 9706
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 453
Relay_Master_Log_File: mysql-bin.000025
Slave_IO_Running: Yes IO线程代表IO正常
Slave_SQL_Running: Yes SQL线程
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: 9706
Relay_Log_Space: 603
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: 1
1 row in set (0.00 sec)
从库提升主库步骤
- mysql主从复制中,需要将备库(从库)提升为主库,需要取消其从库角色,可以通过执行以下命令:
- stop slave;
- reset slave all;
- RESET SLAVE ALL是清除从库的同步复制信息,包括连接信息和二进制文件名、位置
- 从库上执行这个命令后,使用show slave status将不会有输出
1.5 生产场景下轻松部署MySQL主从复制
shell>mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B -F -R -x --master-data=1 -A --events|gzip >/server/backup/rep3307_(date +%F).sql.gz
shell>mysql -uroot -p123456 -S /data/3307/mysql.sock <./repo3307_2016-07-03.sql
mysql>CHANGE MASTER TO
MASTER_HOST='www.etiantian.org',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000025',
MASTER_LOG_POS=9815;
mysql>start slave
mysql>show slave status\G
错误提示:
stop slave;
set global sql_slave_skip_counter =1; #将同步指针向下移动,如果多次不同步,可以添加移动的数量
start slave;
- root@oldboy 08:51:37->show processlist;
- +----+------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
- | Id | User | Host | db | Command | Time | State | Info |
- +----+------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
- | 2 | rep | 172.16.1.52:51317 | NULL | Binlog Dump | 68 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
- | 3 | root | localhost | NULL | Query | 0 | NULL | show processlist |
- +----+------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
- 2 rows in set (0.00 sec)
主库I/O工作状态上方黄色
主库I/O 线程工作状态 | 解释说明 |
Sending binlog event to slave | 线程已经从二进制binlog日志读取了一个事件并且正将它发送到从服务器 |
Finished reading one binlog;switching to next binlog | 线程已经读完二进制binlog日志文件,并且整打开下一个要发送到从服务器的binlog日志文件 |
Has sent all binlog to slave;waiting for binlog to be updated | 线程已经从binlog日志读取所有更新并已经发送到了从数据库服务器。线程现在为空闲状态,等待由主服务器上二进制binlog日志中的新事件更新。 |
Waiting to finalize termination | 线程停止时发送的一个很简单的状态 |
从库I/O线程工作状态 | 解释说明 |
Connecting to master | 线程正试图连接主服务器 |
Checking master version | 同步主服务器之间建立后临时出现的状态 |
Registering slave to master | |
Requesting binlog dump | 建库同主服务器之间的连接后立即临时出现的状态,线程向主服务器发送一条请求,索取从请求的二进制binlog日志文件名和位置开始的二进制binlog日志的内容 |
Waiting to reconnect after a failed binlog dump request | 如果二进制binlog日志转存储请求失败,线程进行睡眠状态,尝试重新连接 |
Reading event from the relay log | 线程已经从中继日志读取了一个事件,可以对事件进行处理了。 |
Has read all relay log;waiting for the slave I/O thread to update it | 线程已经处理了中继日志文件中的所有事件,现在等待I.O线程将新事件写入中继日志 |
Waiting for slave mutex on exit | 线程停止时发生了一个很简单的状态 |
1.6 MySQL主从复制更多应用技巧实践
对于该冲突,解决方法为:
stop slave; #临时停止同步开关
set global sql_slave_skip_counter =1; #将同步指针向下移动一个
start slave;
[root@db02 oldboy]# grep slave-skip /data/3306/my.cnf
slave-skip-errors = 1032,1062
- MySQL自身的原因以及人为重复插入数据
- 不同的数据版本会引起不同步,低版本到高版本可以,但是高版本不能往低版本同步
- MySQL的运行错误或者程序BUG
- binlog记录模式,例如:row level模式就比默认的语句要好
log-slave-updates #必须要有这个参数
log-bin = /data/3307/mysql-bin
expire_logs_days = 7 #相当于删除7天之后的日志
1.7 Mysql主从复制延迟问题原因及解决方法
- 具有SUPER权限的用户可以更新,不受read-only参数影响,例如:管理员root。
- 来自从服务器线程可以更新,不受read-only参数影响,例如:rep用户
mysqladmin -uroot -p123456 -S /data/3307/mysql.sock shutdown
mysql_safe --defaults-file=/data/3307/my.cnf --read-only &
[mysqld]
read-only
1.8 Web用户专业设置方案:MySQL主从复制读写分离集群
如果给开发授权权限
用户:web_w 密码:123456 端口3306 主库VIP:10.0.0.1
权限:SELECT,INSERT,UPDATE,DELETE
命令:GRANT SELECT,INSERT,UPDATE,DELETE ON wen.* to 'web_w'@10.0.0.% identified by '123456';
用户:web_r 密码:123456 端口:3306 从库VIP:10.0.0.2
权限:SELECT
命令:GRANT SELECT ON web.* TO web@10.0.0.% identfied by '123456';
用户:web 密码:123456 端口:3306 主库VIP:10.0.0.1
权限:SELECT,INSERT,UPDATE,DELETE
命令:GRANT SELECT,INSERT,UPDATE,DELETE ON web.* TO web@10.0.0.% identified by '123456';
用户:web 密码:123456 端口:3306 从库VIP:10.0.0.2
权限:SELECT
#由于主库和从库是同步复制的,所以从库上的Web用户会自动和主库一直,既无法实现只读select的权限。
用户:web 密码:123546 端口:3306 主库VIP:10.0.0.8
权限:SELECT,INSERT,UPDATE,DELETE
命令:GRANT SELECT,INSERT,UPDATE,DELETE ON web.* to web_w@10.0.0.% identified by '123546';
由于从库设置了read-only,非super权限是无法写入的,因为通过read-only参数就可以
binlog-ignore-db = mysql
replicate-ignore-db = mysql
![](https://i-blog.csdnimg.cn/blog_migrate/ccf39d51448972a10c2e1ca571ffe6f2.png)
1.9 MySQL半同步
1)半同步从库(谷歌半同步插件 5.5自带)
[root@db02 oldboy]# ll /application/mysql-5.5.49/lib/plugin/
-rwxr-xr-x 1 root root 173428 Jun 16 12:57 semisync_master.so
-rwxr-xr-x 1 root root 94098 Jun 16 12:57 semisync_slave.so
2) 从库什么也不操作,只同步主库
3)临时选择从库
主库宕机有两种情况
[root@db02 data]# cat master.info
18
mysql-bin.000028
188
www.etiantian.org
rep
123456
3306
60
0
从库提升主库的操作步骤(简单说明)
登录:mysql -uroot -p123456 -S /data/3306/mysql.sock
stop slave;
retset master;
quit;
cd /data/3306/data
rm -rf master.info relay-log.info
检查mysql授权表(web用户权限以及从库同步的权限)是不是正确的
read-only等参数
确认bin-log是否开启
log-bin = /data/3306/mysql-bin
//如果存在log-slave-updates read-only等一定要注释掉它。
/data/3306/mysql restart
到此为止,提升主库完毕
stop slave;
CHANGE MASTER TO MASTER_HOST ='192.168.1.1'; #如果不同步,就指定位置点。
start slave;
show slave status\G
==========================主库宕机切换成功
修改程序配置文件从主数据库32指定32