17.1 MySQL主从介绍
MySQL主从即Replication、AB复制
MySQL主从是指两台机器的数据同步，A机器上写数据，B机器也会跟着写数据，实现两台机器数据实时同步
MySQL主从是基于binlog的：
binlog是一个文件，在文件中记录了一些日志
该文件为一个二进制文件，无法使用cat等命令查看
必须要在主从的主上开启binlog才能进行主从
主从过程步骤：
1 主将更改操作（比如对某库某表的update或insert等操作）记录到binlog中
2 从将主的binlog事件（sql语句）同步到从机器上，并记录在relaylog（从的一个日志文件，同样记录sql语句）中
3 从将relaylog中的sql语句按顺序执行
在主和从机器上共有三个线程：
主上有一个log dump线程，用于和从的I/O线程传递binlog文件
从上有两个线程：
其中I/O线程用于同步主的binlog并生成relaylog
另一个sql线程用于把relaylog中的sql语句执行一遍
主从使用场景：
数据备份：从机器实时备份主的数据，一旦主机器故障，从机器可以马上上线继续提供服务
减轻主的压力：客户端读操作时，服务器可以将部分请求发给从，减轻主的压力，但向服务器写数据只能写在主上，否则会造成主从数据不一致
17.2 准备工作
确保主从机器均正确安装了mysql并可以正常运行
17.3 配置主
主的相关操作
修改主的配置文件my.cnf
[root@hyc-01-01 ~]# vim /etc/my.cnf
[mysqld]
federated
datadir=/data/mysql
socket=/tmp/mysql.sock
server-id=129 指定server-id，server-id无特殊要求
log_bin=hyc1 打开binlog，指定binlog的前缀
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
…
重启mysql
[root@hyc-01-01 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
查看/data/mysql
[root@hyc-01-01 ~]# ls -l /data/mysql
总用量 110676
-rw-rw---- 1 mysql mysql 56 8月 11 22:11 auto.cnf
-rw-rw---- 1 mysql mysql 55782 9月 2 20:07 hyc-01-01.err
-rw-rw---- 1 mysql mysql 5 9月 2 20:07 hyc-01-01.pid
-rw-rw---- 1 mysql mysql 120 9月 2 20:07 hyc1.000001
二进制binlog文件，可能生成多个
-rw-rw---- 1 mysql mysql 14 9月 2 20:07 hyc1.index 索引文件
-rw-rw---- 1 mysql mysql 12582912 9月 2 20:08 ibdata1
-rw-rw---- 1 mysql mysql 50331648 9月 2 20:08 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 8月 11 21:52 ib_logfile1
drwx------ 2 mysql mysql 4096 8月 11 21:52 mysql
drwx------ 2 mysql mysql 4096 8月 25 23:01 mysql2
drwx------ 2 mysql mysql 4096 8月 11 21:52 performance_schema
drwx------ 2 mysql mysql 20 8月 25 21:34 test1
drwx------ 2 mysql mysql 324 9月 1 19:40 zrlog
所有以hyc1为前缀的文件对实现主从非常重要，没有这些文件主从无法完成
准备测试环境
备份数据库测试时使用
[root@hyc-01-01 ~]# mysqldump -uroot -p123456 zrlog > zrlog.sql
Warning: Using a password on the command line interface can be insecure.
[root@hyc-01-01 ~]# du -sh zrlog.sql
12K zrlog.sql
创建新的库
[root@hyc-01-01 ~]# mysql -uroot -p123456 -e "create database hyc"
Warning: Using a password on the command line interface can be insecure.
将zrlog的备份恢复到库hyc
[root@hyc-01-01 ~]# mysql -uroot -p123456 hyc < zrlog.sql
Warning: Using a password on the command line interface can be insecure.
执行完上述操作后再执行ls -l /data/mysql，会发现文件hyc1.000001变大，因为该文件需要完整记录数据库创建过程（包括创建的库、表及表中的数据）
用户可以通过binlog文件恢复表中的数据
创建用户用于主从同步
mysql> grant replication slave on *.* to 'repl'@'192.168.31.128' identified by 'hyc940421';
进入mysql数据库
创建用户repl用于主从复制时从登录到主服务器时使用
创建用户repl指定从192.168.31.128登录，登录密码为hyc940421，授予该用户对所有库的所有表的replication slave权限
Query OK, 0 rows affected (0.00 sec)
锁表（拒绝再向表写数据）
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
锁表的目的是为了中止写操作，因为从要同步备份的数据，使两者保持一致，这样才能实现同步
mysql> show master status；
+-------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| hyc1.000001 | 12077 | | | |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
这里需要记住binlog文件的filename和position
使用mysqldump将库hyc、mysql2、test1、zrlog备份到tmp目录
[root@hyc-01-01 tmp]# mysqldump -uroot -p123456 mysql2 > /tmp/my2.sql
Warning: Using a password on the command line interface can be insecure.
[root@hyc-01-01 tmp]# mysqldump -uroot -p123456 test1 > /tmp/test1.sql
Warning: Using a password on the command line interface can be insecure.
[root@hyc-01-01 tmp]# mysqldump -uroot -p123456 hyc > /tmp/hyc.sql
Warning: Using a password on the command line interface can be insecure.
[root@hyc-01-01 tmp]# mysqldump -uroot -p123456 zrlog > /tmp/zrlog.sql
Warning: Using a password on the command line interface can be insecure.
17.4 配置从
编辑mysql配置文件
[root@hyc-01 usr]# vim /etc/my.cnf
…
# *** upgrade to a newer version of MySQL.
[mysqld]
datadir=/data/mysql
socket=/tmp/mysql.sock
server-id=128
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
…
server-id不能和主一致
从不需要配置binlog
重启mysql
将主服务器上的数据库备份文件拷贝到从上
[root@hyc-01 usr]# scp 192.168.31.129:/tmp/*.sql /tmp/
root@192.168.31.129's password:
hyc.sql 100% 11KB 3.1MB/s 00:00
my2.sql 100% 646KB 39.8MB/s 00:00
test1.sql 100% 1260 547.7KB/s 00:00
zrlog.sql
创建对应的几个数据库
[root@hyc-01 ~]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.39 MySQL Community Server (GPL)
…
mysql> create database hyc;
Query OK, 1 row affected (0.00 sec)
mysql> create database mysql2;
Query OK, 1 row affected (0.00 sec)
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
mysql> create database zrlog;
Query OK, 1 row affected (0.00 sec)
将对应数据恢复到对应的库
[root@hyc-01 tmp]# mysql -uroot hyc < /tmp/hyc.sql
[root@hyc-01 tmp]# mysql -uroot mysql2 < /tmp/my2.sql
[root@hyc-01 tmp]# mysql -uroot test1 < /tmp/test1.sql
[root@hyc-01 tmp]# mysql -uroot zrlog < /tmp/zrlog.sql
这里需要保证恢复后两边库的数据一致
登录从的mysql执行操作：
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.31.129', master_user='repl', master_password='hyc940421', master_log_file='hyc1.000001', master_log_pos=12077;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
master_host='192.168.31.129' 主的地址
master_user='repl' 登录主使用的用户
master_password='hyc940421' 用户的密码
master_log_file='hyc1.000001' 以下两个信息可以在主上使用show master status查看
master_log_pos=12077
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
查看主从同步配置是否成功：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.129
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: hyc1.000001 binlog文件
Read_Master_Log_Pos: 12077
Relay_Log_File: hyc-01-relay-bin.000002 relaylog文件（中继日志）
Relay_Log_Pos: 278
Relay_Master_Log_File: hyc1.000001
Slave_IO_Running: Yes 此处两个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: 12077
Relay_Log_Space: 452
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
…
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 129
Master_UUID: 7efbd772-9d70-11e8-a029-000c294d9d95
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
…
1 row in set (0.00 sec)
ERROR:
No query specified
在主服务器上解除锁表
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
总结从上的操作：
修改mysql配置文件添加server-id
将主的数据同步到从，保证主从数据一致，此时最好将主锁表，拒绝所有写操作
在从的mysql下：
Stop slave
change master to master_host='192.168.31.129', master_user='repl', master_password='hyc940421', master_log_file='hyc1.000001', master_log_pos=12077;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
Start slave
在主的mysql下：
Unlock tables
17.5 测试主从同步
相关配置参数：
主服务器
binlog-do-db= 设置成仅同步指定的库，指定多个库时可用，分割
binlog-ignore-db= 忽略某些库，同步其余的库
从服务器
replicate_do_db= 设置仅同步指定的库，指定多个库时用，分割
replicate_ignore_db= 忽略某些库，同步其余的库
replicate_do_table= 同步指定的表
replicate_ignore_table= 忽略某些表
假如用户忽略了mysql库，那么use mysql后的update、insert等操作将被忽略不再被记录到relaylog中，此时假如有一个联合查询用到了语句select zrlog.*，那么该select语句也将被忽略，这会导致同步的数据不完整
replicate_wild_do_table=
replicate_wild_ignore_table=
以上两个参数可以在=后面写hyc.%表示hyc库所有的表，第一个表示所有表都同步，第二个表示所有表都忽略
由于以上两个参数支持库.表（hyc.*）,一般可以尽量避免使用replicate_do_table和replicate_ignore_table
测试主从
在主上：
mysql> use hyc;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_hyc |
+---------------+
| comment |
| link |
| log |
| lognav |
| plugin |
| tag |
| type |
| user |
| website |
+---------------+
9 rows in set (0.00 sec)
mysql> select count(*) tag;
+-----+
| tag |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)
mysql> truncate table tag;
Query OK, 0 rows affected (0.04 sec)
mysql> select count(*) tag;
+-----+
| tag |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)
mysql> drop table tag; 在主上直接删掉hyc.tag表
Query OK, 0 rows affected (0.03 sec)
在从上：
mysql> select count(*) from hyc.tag;
+----------+
| count(*) |
+----------+
| 1 | 与主上数据一致，均为一行
+----------+
1 row in set (0.01 sec)
主上执行truncate后
mysql> select count(*) from hyc.tag;
+----------+
| count(*) |
+----------+
| 0 | 此时从上的数据变为0行
+----------+
1 row in set (0.00 sec)
mysql> select * from hyc.tag;
Empty set (0.00 sec)
主上执行drop table后
mysql> select * from hyc.tag; 在主上该表被删除，在从上该表也消失
ERROR 1146 (42S02): Table 'hyc.tag' doesn't exist
问题：
在从上
mysql> drop database hyc;
Query OK, 8 rows affected (0.10 sec)
mysql>show slave status\G
…
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 'hyc'; database doesn't exist' on query. Default database: 'hyc'. Query: 'drop database hyc'
…
如果用户在从上删了某个库，此时用户再到主上删除相同的库就会出现报错；因为用户在主上删除库时，从上会将该删除库的操作再在从上执行一遍，但此时从上已经没有对应的库了，此时遇到问题，主从断开
解决：
在从上
mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G
…
Slave_IO_Running: Yes
Slave_SQL_Running: No
…
若以上操作未解决则需要重做主从：
在主上
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| hyc1.000001 | 12370 | | | |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
再从上
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_host='192.168.31.129', master_user='repl', master_password='hyc940421', master_log_file='hyc1.000001', master_log_pos=12370;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
此时由于主和从的数据一致，所以只要将变化的position改成一样的，再重新start slave即可
mysql> show slave status\G
…
Read_Master_Log_Pos: 12370
Relay_Log_File: hyc-01-relay-bin.000002
Relay_Log_Pos: 278
Relay_Master_Log_File: hyc1.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
…
