MySQL主从复制原理实践

202次阅读

共计 3691 个字符，预计需要花费 10 分钟才能阅读完成。

MySQL 主从复制
MySQL 主从复制实则是将原本创建和修改数据库的 SQL 命令集合复制到从库本地，在从库本地重新执行这些 SQL 命令来建立与主库一样的数据。搭建从库以后，包括复制主库当前截止位置的所有数据，和接下来主库还在不断增长的数据。事实上，停库备份全部数据再复制过去没什么问题，问题在于怎么实时复制数据库增长的这段数据？而这就要借助 binlog 日志的功能 …

主从复制原理

MySQL 主从复制原理实践

1）开启主库 log-bin 功能，是为了用户在写数据库过程中，MySQL 会同时建立一份 binlog 日志来记录数据库所做的增删改等操作；还要建立一个从库和主库之间验证关系的换用账号 rep，并授权；

2）锁表，对数据库做全备操作，最大程度保证数据一致性，最重要的是获取当前数据库 binlog 日志分界点；
3）将停库后的数据库全量恢复到从库
4）在从库中执行 CHANGE MASTER TO… 命令来验证用户，和告诉从库当前备份的数据库 binlog 日志分界点在哪，并生成 master.info 文件；
5）开启从库，此时 mysql replication 功能才真正发挥作用，从库 B 会利用自己的 IO 线程不断询问主库 A：哥们，我有暗号（密码）是自己人，呃 … 我这本地有这些这些货，你那有没有新货（新增的数据），也给我搞点。然后主库 A 就会去看自己 binlog 日志，发现真有些新货，就会给这些新货发给从库 B，并且附赠一份清单，告诉从库 B 他现在仓库里有哪些货，下次从库 B 再来找他要新货只要拿出这个清单 A 就知道他那里的货是不是和自己一样的了，如果一样就不发了，如果不一样，就按照这个清单上的货截止位置发最新的，这里的新货就可以理解为 binlog 文件内容，清单就为 binlog 位置信息 post；
从库每次拿到新货就会很开心啊，启动自己的 SQL 线程将这些新货解析到自己的数据库中，保证与主库 A 完全一致。

主从复制案例实践

在本地多 mysql 多实例中，将 /data/3306/mysql.sock 作为主库，/data/3307/mysql.sock 作为从库

主库

1）开主库 binlog 功能，并且保证 server id 不一样
[root@db02 ~]# egrep “log-bin|server-id” log-bin /data/3306/my.cnf /data/3307/my.cnf
/data/3306/my.cnf:log-bin = /data/3306/mysql-bin
/data/3306/my.cnf:server-id = 1
/data/3307/my.cnf:#log-bin = /data/3307/mysql-bin
/data/3307/my.cnf:server-id = 3

2）主库授权从库账号 rep
mysql> grant replication slave on *.* to rep@’172.16.2.%’ identified by ‘oldboy123’;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;

3）锁表（锁表后不能退出当前 mysql 控制窗口）
mysql> flush table with read lock;

5）全备

[root@db02 ~]# mysqldump -uroot -poldboy1234 -S /data/3306/mysql.sock -A -B –events|gzip > /server/backup/mysql_$(date +%F).sql.gz
[root@db02 backup]# ll mysql_2016-07-07.sql.gz
-rw-r–r– 1 root root 144620 Jul 7 04:18 mysql_2016-07-07.sql.gz

6）解锁表
1 mysql> unlock table;

从库

1）确保 server ID 不同
[root@db02 ~]# grep server-id /data/3307/my.cnf
server-id = 4

2）恢复全备

[root@db02 backup]# mysql -uroot -poldboy456 -S /data/3307/mysql.sock </server/backup/mysql_2016-07-07.sql

3）配置 master，填写正确的 binlog 位置点

mysql> CHANGE MASTER TO MASTER_HOST=’172.16.2.10′, MASTER_PORT=3306, MASTER_USER=’rep’，
MASTER_PASSWORD=’oldboy123′, MASTER_LOG_FILE=’mysql-bin.000015′, MASTER_LOG_POS=3204;
## 此时如果 master 密码配置错误，重新改 master.info 文件不生效，需执行 reset slave all;
[root@db02 data]# cat master.info
18
mysql-bin.000015
3204
172.16.2.10
rep
oldboy123
3306
60
0

4）开启从库复制开关，验证数据

mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.2.10
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000015
Read_Master_Log_Pos: 3289
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 338
Relay_Master_Log_File: mysql-bin.000015
Slave_IO_Running: Yes #从库从主库复制 binlog 日志进程
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: 3289
Relay_Log_Space: 488
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