17.1 MySQL主从介绍
MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的。
MySQL主从是基于binlog的，主上须开启binlog才能进行主从。
主从过程大致有3个步骤
1）主将更改操作记录到binlog里
2）从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里
3）从根据relaylog里面的sql语句按顺序执行
主上有一个log dump线程，用来和从的I/O线程传递binlog
从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地
MySQL主从原理图
原理：从会把主的binlog搞到从上，从会根据binlog生成自己的一份中继日志，然后根据中继日志去执行响应的更改，最终达到数据同步一致。
主从的工作场景：
数据备份-->目的是防止主机器发生意外导致无法正常使用，从而将在主上备份到从上的数据迁移出来，从而使其继续服务运作。
数据备份且在从上读取数据-->从库作为读库，减轻主库读的压力。
17.2 准备工作
准备2台Linux服务器，分别作为主库，从库。
centos7-01主库，centos7-02从库
截图
目的是让2台机器都正常启动mysql，方便后面做实验
17.3 主从配置——配置主
设置主的参数，
1 修改my.cnf，增加server-id=128和log_bin=aminglinux1
[root@centos7-01 src]# vim /etc/my.cnf
[mysqld] datadir=/data/mysql socket=/tmp/mysql.sock server-id=128 log_bin=aminglinux1
参数解释：
sever-id为自定义，这里定义的是ip的后缀，方便标记
log_bin名称不能有错误，它是binlog的前缀，不然重启的时候会出现报错。
2 重启mysql
[root@centos7-01 src]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS!
2.1 重启mysqld后，可以看看data产生的文件
[root@Centos7-01 mysql]# ls -lt /data/mysql/ 总用量 110632 -rw-rw---- 1 mysql mysql 50331648 5月 16 21:38 ib_logfile0 -rw-rw---- 1 mysql mysql 12582912 5月 16 21:38 ibdata1 -rw-rw---- 1 mysql mysql 12337 5月 16 21:38 Centos7-01.err -rw-rw---- 1 mysql mysql 6 5月 16 21:38 Centos7-01.pid -rw-rw---- 1 mysql mysql 120 5月 16 21:38 aminglinux1.000001 -rw-rw---- 1 mysql mysql 21 5月 16 21:38 aminglinux1.index -rw-rw---- 1 mysql mysql 56 5月 16 21:32 auto.cnf drwx------ 2 mysql mysql 4096 5月 16 21:28 mysql drwx------ 2 mysql mysql 4096 5月 16 21:28 performance_schema -rw-rw---- 1 mysql mysql 50331648 5月 16 21:28 ib_logfile1 drwx------ 2 mysql mysql 6 5月 16 21:28 test
分析：
其中前缀aminglinux1是binlog文件
aminglinux1.index 索引文件，这个文件必须要有
aminglinux1.000001二进制文件，这个随后还会产生00002,000003....如此类推
aminglinux1这类文件是实现主从的根本，如果没有aminglinux1这类文件，主从是没办法完成的。
2.2 创建实验数据（此步骤根据实际情况来定义，可做可不做）
[root@Centos7-01 mysql]# mkdir blog [root@Centos7-01 mysql]# touch 1.txt 2.txt 3.txt [root@Centos7-01 mysql]# mv 1.txt 2.txt 3.txt ./blog/ [root@Centos7-01 mysql]# mkdir zrlog1 [root@Centos7-01 mysql]# mkdir mysql2 [root@Centos7-01 mysql]# du -sh mysql2/ 64Kmysql2/ [root@Centos7-01 mysql]# du -sh zrlog1/ 48Kzrlog1/
2.3 在blog目录的基础上再备份一个出来
[root@Centos7-01 mysql]# mysqldump -uroot -paminglinux blog > /tmp/blog.sql
2.4 创建新库aming,并把刚刚备份的数据恢复至aming
创建新库aming
[root@Centos7-01 mysql]# mysql -uroot -paminglinux -e "create database aming" Warning: Using a password on the command line interface can be insecure.
2.5 把刚刚备份的数据恢复至aming
[root@Centos7-01 mysql]# mysql -uroot -paminglinux aming < /tmp/blog.sql Warning: Using a password on the command line interface can be insecure.
2.6 以aming这个库作为主从参考对象
[root@Centos7-01 mysql]# ls -lt 总用量 110632 drwx------ 2 mysql mysql 20 5月 16 22:07 aming -rw-rw---- 1 mysql mysql 359 5月 16 22:07 aminglinux1.000001
恢复完数据后，可以看得出aminglinux1.000001这个文件容量在慢慢增大
3 创建用作同步数据的用户
进入mysql mysql>grant replication slave on *.* to 'repl'@192.168.189.129 identified by 'aminglinux111';
解释：
replication slave 给与从权限
*.* 所有的库，所有的表
'repl' 针对这个用户
@slave_ip 针对从的ip
identified by 'password' 密码
4 锁表，让数据停止更新（主从配置完成后，需要开启）
mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)
5 查看主的状态
mysql> show master status; +--------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+-------------------+ | aminglinux1.000001 | 571 | | | | +--------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
记住File和Position的参数内容,稍后会用上它。
6 同步数据库
其中mysql目录不做备份，因为里面太多密码文件，涉及安全性。
[root@Centos7-01 mysql]# ls -lt 总用量 110632 drwxr-xr-x 2 root root 45 5月 16 22:39 zrlog1 drwxr-xr-x 2 root root 58 5月 16 22:38 mysql2 -rw-rw---- 1 mysql mysql 571 5月 16 22:23 aminglinux1.000001 drwx------ 2 mysql mysql 20 5月 16 22:07 aming drwxr-xr-x 2 root root 45 5月 16 21:57 blog -rw-rw---- 1 mysql mysql 50331648 5月 16 21:38 ib_logfile0 -rw-rw---- 1 mysql mysql 12582912 5月 16 21:38 ibdata1 -rw-rw---- 1 mysql mysql 12337 5月 16 21:38 Centos7-01.err -rw-rw---- 1 mysql mysql 6 5月 16 21:38 Centos7-01.pid -rw-rw---- 1 mysql mysql 21 5月 16 21:38 aminglinux1.index -rw-rw---- 1 mysql mysql 56 5月 16 21:32 auto.cnf drwx------ 2 mysql mysql 4096 5月 16 21:28 mysql drwx------ 2 mysql mysql 4096 5月 16 21:28 performance_schema -rw-rw---- 1 mysql mysql 50331648 5月 16 21:28 ib_logfile1 drwx------ 2 mysql mysql 6 5月 16 21:28 test
同步数据库mysql2,zrlog1
[root@Centos7-01 mysql]# mysqldump -uroot -paminglinux mysql2 > /tmp/my2.sql Warning: Using a password on the command line interface can be insecure. [root@Centos7-01 mysql]# mysqldump -uroot -paminglinux zrlog1 > /tmp/zr1.sql Warning: Using a password on the command line interface can be insecure.
17.4 主从配置——配置从
1 设置从的参数
增加server-id，不需要填写bin_log
[root@Centos7-02 ~]# vim /etc/my.cnf [mysqld] datadir=/data/mysql socket=/tmp/mysql.sock server-id=129
1.1 增加完server-id之后，重启服务
[root@Centos7-02 ~]# /etc/init.d/mysqld stop Shutting down MySQL.. SUCCESS! [root@Centos7-02 ~]# /etc/init.d/mysqld start Starting MySQL. SUCCESS!
2 把刚刚做的主上库同步到从上
[root@Centos7-02 ~]# scp 192.168.189.128:/tmp/*.sql /tmp/ blog.sql 100% 1258 941.3KB/s 00:00 my2.sql 100% 1260 963.6KB/s 00:00 zr1.sql 100% 1260 1.2MB/s 00:00
3 进入mysql创建4个库:aming,zrlog1,blog,mysql2.
mysql> create database aming; Query OK, 1 row affected (0.00 sec) mysql> create database zrlog1; Query OK, 1 row affected (0.00 sec) mysql> create database blog; Query OK, 1 row affected (0.01 sec) mysql> create database mysql2; Query OK, 1 row affected (0.00 sec)
4 创建库后，恢复数据-->指定数据至指定的名称，
[root@Centos7-02 ~]# mysql -uroot blog < /tmp/blog.sql [root@Centos7-02 ~]# mysql -uroot zrlog1 < /tmp/zr1.sql [root@Centos7-02 ~]# mysql -uroot aming< /tmp/blog.sql [root@Centos7-02 ~]# mysql -uroot mysql2< /tmp/my2.sql
4.1 主和从的mysql建立的数据都要一致（针对刚刚创建的4个库）
[root@Centos7-02 mysql]# ls -lt /data/mysql/ 总用量 110644 drwx------. 2 mysql mysql 20 5月 16 23:27 mysql2 drwx------. 2 mysql mysql 20 5月 16 23:25 blog drwx------. 2 mysql mysql 20 5月 16 23:25 zrlog1 drwx------. 2 mysql mysql 20 5月 16 23:25 aming [root@Centos7-01 mysql]# ls -lt 总用量 110632 drwxr-xr-x 2 root root 45 5月 16 22:39 zrlog1 drwxr-xr-x 2 root root 58 5月 16 22:38 mysql2 -rw-rw---- 1 mysql mysql 571 5月 16 22:23 aminglinux1.000001 drwx------ 2 mysql mysql 20 5月 16 22:07 aming drwxr-xr-x 2 root root 45 5月 16 21:57 blog
因为从上面是没有bin_log文件的所以，aminglinux1.000001只会在主上，不会出现在从上。
5 实现主从
5.1 登录mysql
mysql> stop slave;
5.2 实现主从关键一步的命令
mysql> change master to master_host='192.168.189.128', master_user='repl', master_password='aminglinux111', master_log_file='aminglinux1.000001', master_log_pos=571; Query OK, 0 rows affected, 2 warnings (0.03 sec)
******主上的show mater status信息
+--------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+-------------------+ | aminglinux1.000001 | 571 | | | | +--------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
解释：
master_host=''主的ip
master_user='repl'主的用户
master_password=''住的用户密码
master_log_file='', 主的日志文件(show master status的file)
master_log_pos=xx,主的日日志position(show master status的position)，此处的数值不用加''符号
5.3 启动从
mysql>start slave Query OK, 0 rows affected (0.01 sec)
5.4 判断主从是否配置成功
mysql> show slave status; mysql> show slave status\G
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.189.128 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: aminglinux1.000001 Read_Master_Log_Pos: 571 Relay_Log_File: Centos7-02-relay-bin.000002 Relay_Log_Pos: 285 Relay_Master_Log_File: aminglinux1.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 571 Relay_Log_Space: 463 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: 128 Master_UUID: a69f92e6-590d-11e8-a239-000c2916c3f2 Master_Info_File: /data/mysql/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)
内容解释：
特别关注这两个参数，两个Yes为成功简历主从状态，如果其中一个不是Yes都不是正确的状态。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果发现其中一个状态为Connecting的，请前往链接：http://blog.51cto.com/13578154/2117258 根据实际环境进行参考解决
Seconds_Behind_Master: 0 //为主从延迟的时间
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
6 到主上执行 unlock tables
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
总结从上操作：
sever-id 跟主不同
主数据同步到从上
stop,change,start 三个动作
主上执行unlock tables
17.5 测试主从同步
1 假如在从上删掉aming库，可能导致主从不一致
mysql>drop database aming; Query OK, 0 rows affected (0.03 sec)
2 检查从的状态
mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes
从状态虽然显示没出现问题，但是理论主从是不一致的，
3 再到主上drop aming库操作，
mysql> drop database aming; Query OK, 0 rows affected (0.00 sec)
4 回到从上检查状态
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.189.128 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: aminglinux1.000001 Read_Master_Log_Pos: 658 Relay_Log_File: Centos7-02-relay-bin.000002 Relay_Log_Pos: 285 Relay_Master_Log_File: aminglinux1.000001 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1008 Last_Error: Error 'Can't drop database 'aming'; database doesn't exist' on query. Default database: 'aming'. Query: 'drop database aming'
可以看到两个Yes状态其中SQL已经是No了，
而且还有错误信息：aming库不存在。-->因为在从上已经把database干掉了，所以在主上删除，
此时aming库不存在的，所以不能被删除的。
如果遇到问题，主从会马上断掉。
5 修复方法：
两种方法
5.1 第一种是：stop slave,start slave.
5.2 第二种是：重新做主从
5.2.1 在主上获取主的状态信息
mysql> show master status; +--------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+-------------------+ | aminglinux1.000001 | 658 | | | | +--------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
5.2.2 从上操作
先停止slave
mysql> stop slave;
再change
mysql> change master to master_host='192.168.189.128', master_user='repl', master_password='aminglinux111', master_log_file='aminglinux1.000001', master_log_pos=658;
然后start slave
mysql> start slave;
查看slave状态
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.189.128 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: aminglinux1.000001 Read_Master_Log_Pos: 658 Relay_Log_File: Centos7-02-relay-bin.000002 Relay_Log_Pos: 285 Relay_Master_Log_File: aminglinux1.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes
Slave_IO_Running和Slave_SQL_Running的状态都是Yes,成功再次主从同步。
总结：
假如主从同步出错了，需要进行再次做主从的前提是，需要保持数据都要一致，
如果数据不一致，需要重新做多次备份同步，然后再change master。
可以在my.cnf定义的几个配置参数
主服务器上 binlog-do-db= //仅同步指定的库 binlog-ignore-db= //忽略指定库 从服务器上 replicate_do_db= replicate_ignore_db= replicate_do_table= replicate_ignore_table= replicate_wild_do_table= //如aming.%, 支持通配符% replicate_wild_ignore_table=
