https://www.cnblogs.com/linuxk/p/9366838.html
-
一、MySQL主从复制原理
随机站点访问量的鞥集啊,单台的MySQL服务器压力也不断地增加,此时需要对MySQL进行优化,如果在MySQL优化无明显改善时期,可以使用高可用、主从复制、读写分离、分库分表等方式进行进一步的优化。
MySQL主从复制的目的是为了实现数据库冗余备份,将master数据库数据定时同步至slave库中,一旦master数据库宕机,可以将web应用数据库配置快速地切换到slave数据库,确保web应用的 可用性,MySQL主从复制架构图如下:
MySQL主从复制至少需要2台数据库服务器,其中1台为master,一台为slave库,MySQL主从数据同步是一个异步复制的过程,要实现复制首先需要在master上开启bin-log日志功能,bin-log日志用于记录在master库中所执行的增、删、改的SQL语句,整个过程需要开启3个线程,分别是master开启I/O线程,slave开启I/O线程和SQL线程,具体主从同步原理如下:
(1)slave上执行slave start,开启主从复制开关,产生I/O线程和SQL 线程。slave I/O线程会通过在master创建的授权用户连接上至master,并请求master从指定的文件和位置之后发送bin-log日志内容;
(2)master接受到来自slave I/O线程的请求后,首先进行验证,验证通过后,master I/O线程会根据slave I/O线程请求读取指定bin-log日志文件的指定position点之后的bin-log内容,然后返回给slave的I/O线程;
(3)返回的信息当中除了bin-log日志内容以外,还有master最新的bin-log文件名以及在bin-log中的下一个指定更新的position点;
(4)slave I/O线程接到信息后,将收到的日志内容依次添加到slave端的中继日志(relay-log)文件的最末端(MySQL-relay-bin.xxxxx),并将读取到的master端的bin-log的文件名和position点记录到master.info文件当中,以便下一次读取的时候能够告诉master从相应的bin-log文件名和最后一个position点开始发起请求;
(5)slave SQL线程检测到relay-log中内容有更新,会立刻解析relay-log日志中的内容,将解析后的SQL语句在slave中执行,执行成功后slave库和master库数据保持一致。
-
二、主从复制实战
1、环境说明
HOST | OS | MySQL version | Port | Path | Master/Slave |
192.168.56.11 | Centos 7.4 | MySQL 5.6.35 | 3306 | /usr/local/mysql/data/ | Master |
192.168.56.12 | Centos 7.4 | MySQL 5.6.35 | 3306 | /usr/local/mysql/data | Slave |
2、配置Master和Slave的my.cnf
[root@master ~]# vim /etc/my.cnf log-bin=/usr/local/mysql/data/mysql-bin #开启Master的bin-log日志记录功能 server-id=1 #配置Master的server-id,必须和slave不一样 [root@slave ~]# vim /etc/my.cnf server-id=2 #配置Slave的server-id
3、在Master上创建主从复制的用户rep并授权
[root@master ~]# mysql -uroot -p123456 mysql> grant replication slave on *.* to rep@'192.168.56.%' identified by '123456'; #创建主从复制授权用户 Query OK, 0 rows affected (0.03 sec) mysql> flush privileges; #刷新权限 Query OK, 0 rows affected (0.04 sec) mysql> select user,host from mysql.user; #查看创建的rep用户 +-----------+--------------+ | user | host | +-----------+--------------+ | root | % | | wordpress | % | | root | 127.0.0.1 | | rep | 192.168.56.% | | root | localhost | +-----------+--------------+ 5 rows in set (0.03 sec) mysql> show grants for rep@'192.168.56.%'; +---------------------------------------------------------------------------------------------------------------------------+ | Grants for rep@192.168.56.% | +---------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.56.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | +---------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
4、在Master上对数据库进行全备
mysql> flush table with read lock; #给主库加上读锁(备份结束前,该会话不能关闭,否则binlog的文件名或位置点会有变化 Query OK, 0 rows affected (0.02 sec) mysql> show master status; #查看主库的binlog日志名称和POS值 +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000005 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ********新开一个xshell窗口进行备份********** [root@master ~]# mkdir /server/backup -p #创建数据库备份目录 [root@master ~]# mysqldump -uroot -p123456 -A -B --events |gzip > /server/backup/rep_bak_$(date +%F).sql.gz #全备Master的库 [root@master ~]# ls -lrt /server/backup/ total 320 -rw-r--r-- 1 root root 324968 Jul 25 20:53 rep_bak_2018-07-25.sql.gz [root@master ~]# scp /server/backup/rep_bak_2018-07-25.sql.gz root@192.168.56.12:/tools/ #将备份拷贝到Slave上 rep_bak_2018-07-25.sql.gz 100% 317KB 5.9MB/s 00:00 *********拷贝完成后,进行解锁********** mysql> unlock tables;
5、在Slave上恢复Master上的数据
[root@slave ~]# gzip -d /tools/rep_bak_2018-07-25.sql.gz [root@slave ~]# mysql -uroot -p123456 < /tools/rep_bak_2018-07-25.sql
6、在Slave上配置master.info
[root@slave ~]# mysql -uroot -p mysql> stop slave; Query OK, 0 rows affected (0.02 sec) mysql> change master to master_host='192.168.56.11',master_user='rep',master_password='123456', master_log_file='mysql-bin.000005' ,master_log_pos=120; Query OK, 0 rows affected, 2 warnings (0.10 sec)
7、在Slave上启动主从复制
mysql> start slave; #开启主从复制 Query OK, 0 rows affected (0.02 sec) mysql> show slave status\G #查看slave上的IO和SQL线程是否运行 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.11 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 120 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes #表示Slave上的IO线程是否运行 Slave_SQL_Running: Yes #表示Slave上的SQL线程是否运行 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: 120 Relay_Log_Space: 456 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的秒数,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 Master_UUID: 885e2479-89f6-11e8-a128-000c29ce31fd Master_Info_File: /usr/local/mysql-5.6.35/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 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: Auto_Position: 0 1 row in set (0.00 sec) [root@slave ~]# ll /usr/local/mysql/data/slave-relay-bin.* #查看从上的relay-log是否生成 -rw-rw---- 1 mysql mysql 173 Jul 25 21:25 /usr/local/mysql/data/slave-relay-bin.000001 -rw-rw---- 1 mysql mysql 383 Jul 25 21:26 /usr/local/mysql/data/slave-relay-bin.000002 -rw-rw---- 1 mysql mysql 50 Jul 25 21:25 /usr/local/mysql/data/slave-relay-bin.index
8、测试在Master上的操作是否能同步到Slave上
mysql> create database repppp; #在主上创建数据库repppp Query OK, 1 row affected (0.02 sec) mysql> show databases; #在从上查看是否存在repppp数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | repppp | | test | | wordpress | +--------------------+ 6 rows in set (0.00 sec)
9、总结
(1)异步方式同步
(2) 逻辑同步模式(binlog 有三种模式: SQL、混合、 rowlevel),默认是通过 SQL 语句执行
(3)主库通过记录 binlog 实现对从库的同步
(4)主库 1 个线程(IO 线程),从库 2 个线程(IO 和 SQL)来完成
(5)从库的关键文件: master.info、 relay-log、 relay-info
(6)如果从库还想级联从库,需打开 log-bin 和 log-slave-updates 参数
10、生产场景快速配置 MySQL 主从复制的方案
(1)安装好要配置从库的数据库,配置好 log-bin 和 server-id 参数。
(2)无需配置主库的 my.cnf 文件,主库的 log-bin 和 server-id 参数默认就是配置好的。
(3)登陆主库增加用于从库连接的帐户,并授 replication slave 的权限。
(4)半夜使用 mysqldump 带--master-data=1 参数全备主库,并在从库进行恢复。
(5)在从库执行 change master to 语句,无需 binlog 文件及对应位置点。
(6)在从库开启同步开关 start slave。
(7)从库 show slave status\G,检查同步状态,并在主库进行更新测试。
tips:
(1)主从库服务器的配置差距不要太大。
(2)撰写方案文档和实施步骤。如可能需要停机维护,需要事先申请停机维护时间
11、遇到的问题
(1)当在执行start slave这条命令时,系统提示 ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO, 执行show slave status;又提示Empty set (0.00 sec)
原因:slave已经默认开启,要先关闭再开启,执行stop slave; 再执行change master to master_host='192.168.56.11',master_user='rep',master_password='123456', master_log_file='mysql-bin.000005' ,master_log_pos=120;
(2)出现错误提示 :Slave_IO_Running:NO ,mysql的error日志中信息: Slave I/O: error connecting to master 'replication@172.16.0.100:3306' - retry-time: 60 retries: 86400, Error_code: 1045
解决方法
1.在主mysql中创建slave远程连接访问时候的登录密码一定要设置对。
2.停止slave上的mysqld服务,从服务器上删除掉所有的二进制日志文件,包括一个数据目录下的master.info文件和hostname-relay-bin开头的文件,然后启动slave上的mysqld服务。
master.info::记录了Mysql主服务器上的日志文件和记录位置、连接的密码。
(3)slave上Slave_SQL_Running: No ,提示某个表定义不正确
解决方法:
清空drop掉master和slave上的已经存在所有表,然后从master库上导入新的数据即可。