MySQL主从复制原理实践
MySQL主从复制
MySQL主从复制实则是将原本创建和修改数据库的SQL命令集合复制到从库本地，在从库本地重新执行这些SQL命令来建立与主库一样的数据。搭建从库以后，包括复制主库当前截止位置的所有数据，和接下来主库还在不断增长的数据。事实上，停库备份全部数据再复制过去没什么问题，问题在于怎么实时复制数据库增长的这段数据？而这就要借助binlog日志的功能...
主从复制原理
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;
4）查看binlog临界点
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000015 | 3204 | | |
+------------------+----------+--------------+------------------+
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
验证完成。