本文会详细描述：在MySQL 5.7中，Replication架构的实现细节。
环境说明：
涉及两台服务器：
Master：192.168.232.141
Slave：192.168.232.144
操作系统：Redhat Enterprise Linux 6.8
MySQL版本：5.7.13
如下：
Master。
[root@python ~]# cat /etc/redhat-release CentOS release 6.8 (Final) [root@python ~]# [root@python ~]# mysql -V mysql Ver 14.14 Distrib 5.7.13-6, for Linux (x86_64) using 6.0 [root@python ~]#
Slave。
[root@mysql ~]# cat /etc/redhat-release CentOS release 6.8 (Final) [root@mysql ~]# [root@mysql ~]# mysql -V mysql Ver 14.14 Distrib 5.7.13-6, for Linux (x86_64) using 6.0 [root@mysql ~]#
一、主库端
[root@python software]# cat /etc/my.cnf [mysqld] # Basic pid-file=/var/run/mysqld/mysqld.pid log-error=/var/log/mysqld.log datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # For Replicate log_bin=/var/lib/mysql/master.bin server_id=24 [mysqld_safe] # Basic log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [root@python software]#
启动主库数据库：
[root@python software]# ls -ltr /var/lib/mysql/ total 0 [root@python software]# [root@python software]# /etc/init.d/mysql status mysqld is stopped [root@python software]# [root@python software]# /etc/init.d/mysql start Initializing MySQL database: [ OK ] Installing validate password plugin: [ OK ] Starting mysqld: [ OK ] [root@python software]# [root@python software]# ls -ltr /var/lib/mysql/ total 126904 -rw-r----- 1 mysql mysql 50331648 Jul 13 07:32 ib_logfile1 -rw-r----- 1 mysql mysql 56 Jul 13 07:32 auto.cnf -rw------- 1 mysql mysql 1680 Jul 13 07:32 ca-key.pem -rw-r--r-- 1 mysql mysql 1079 Jul 13 07:32 ca.pem -rw------- 1 mysql mysql 1676 Jul 13 07:32 server-key.pem -rw-r--r-- 1 mysql mysql 1087 Jul 13 07:32 server-cert.pem -rw------- 1 mysql mysql 1676 Jul 13 07:32 client-key.pem -rw-r--r-- 1 mysql mysql 1087 Jul 13 07:32 client-cert.pem -rw-r--r-- 1 mysql mysql 452 Jul 13 07:32 public_key.pem -rw------- 1 mysql mysql 1680 Jul 13 07:32 private_key.pem drwxr-x--- 2 mysql mysql 4096 Jul 13 07:32 performance_schema drwxr-x--- 2 mysql mysql 4096 Jul 13 07:32 mysql drwxr-x--- 2 mysql mysql 12288 Jul 13 07:32 sys -rw-r----- 1 mysql mysql 99537 Jul 13 07:32 master.000001 -rw-r----- 1 mysql mysql 472 Jul 13 07:32 master.000002 -rw-r----- 1 mysql mysql 360 Jul 13 07:32 ib_buffer_pool -rw-rw---- 1 root root 6 Jul 13 07:32 mysqld_safe.pid -rw-r----- 1 mysql mysql 87 Jul 13 07:32 master.index -rw-r----- 1 mysql mysql 154 Jul 13 07:32 master.000003 -rw------- 1 mysql mysql 6 Jul 13 07:32 mysql.sock.lock srwxrwxrwx 1 mysql mysql 0 Jul 13 07:32 mysql.sock -rw-r----- 1 mysql mysql 3932160 Jul 13 07:32 xb_doublewrite -rw-r----- 1 mysql mysql 12582912 Jul 13 07:32 ibtmp1 -rw-r----- 1 mysql mysql 12582912 Jul 13 07:32 ibdata1 -rw-r----- 1 mysql mysql 50331648 Jul 13 07:32 ib_logfile0 [root@python software]#
登录主库，创建用于Replication的账户：
[root@python software]# cat /var/log/mysqld.log | grep --color "temporary password"
2016-07-13T14:32:12.105436Z 1 [Note] A temporary password is generated for root@localhost: kGyZ!7ni,onh
[root@python software]#
[root@python software]# mysql -u root -p'kGyZ!7ni,onh'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.13-6-log
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> set password for root@'localhost' = password('Abcd!234');
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
mysql> create user adamhuan@'%' identified by 'Abcd!234';
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> select user,host from mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| adamhuan | % |
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+
3 rows in set (0.00 sec)
mysql>创建了用于传输的用户：adamhuan。
给用户分配权限：
mysql> grant replication slave,replication client on *.* to adamhuan@'%'; Query OK, 0 rows affected (0.00 sec) mysql>
查看下主库的当前状态：
mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | master.000003 | 867 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> mysql> flush logs; Query OK, 0 rows affected (0.02 sec) mysql> mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | master.000004 | 154 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql>
二、辅助端
清空旧的数据：
[root@mysql ~]# ls -ltr /var/lib/mysql/ total 110760 -rw-r-----. 1 mysql mysql 50331648 Jul 13 05:25 ib_logfile1 -rw-r-----. 1 mysql mysql 15 Jul 13 05:25 slave.index -rw-r-----. 1 mysql mysql 56 Jul 13 05:25 auto.cnf -rw-------. 1 mysql mysql 1680 Jul 13 05:25 ca-key.pem -rw-r--r--. 1 mysql mysql 1079 Jul 13 05:25 ca.pem -rw-------. 1 mysql mysql 1680 Jul 13 05:25 server-key.pem -rw-r--r--. 1 mysql mysql 1087 Jul 13 05:25 server-cert.pem -rw-------. 1 mysql mysql 1680 Jul 13 05:25 client-key.pem -rw-r--r--. 1 mysql mysql 1087 Jul 13 05:25 client-cert.pem -rw-------. 1 mysql mysql 1680 Jul 13 05:25 private_key.pem -rw-r--r--. 1 mysql mysql 452 Jul 13 05:25 public_key.pem drwxr-x---. 2 mysql mysql 4096 Jul 13 05:25 performance_schema drwxr-x---. 2 mysql mysql 4096 Jul 13 05:25 mysql drwxr-x---. 2 mysql mysql 12288 Jul 13 05:25 sys -rw-r-----. 1 mysql mysql 99537 Jul 13 05:25 slave.000001 -rw-r-----. 1 mysql mysql 408 Jul 13 05:25 ib_buffer_pool -rw-r-----. 1 mysql mysql 50331648 Jul 13 05:25 ib_logfile0 -rw-r-----. 1 mysql mysql 12582912 Jul 13 05:25 ibdata1 [root@mysql ~]# [root@mysql ~]# rm -rf /var/lib/mysql/* [root@mysql ~]# [root@mysql ~]# ls -ltr /var/lib/mysql/ total 0 [root@mysql ~]#
编辑配置文件：
[root@mysql ~]# cat /etc/my.cnf [mysqld] pid-file=/var/run/mysqld/mysqld.pid log-error=/var/log/mysqld.log datadir=/var/lib/mysql #datadir=/data/mysql_db/salve socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log_bin=/var/lib/mysql/master.bin server_id=2 relay_log=/var/lib/mysql/slave.relay_log log_slave_updates=1 read_only=1 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [root@mysql ~]#
启动数据库，并登录修改初始口令：
[root@mysql ~]# ps -ef | grep mysql
root 40922 39410 0 07:43 pts/1 00:00:00 grep mysql
[root@mysql ~]#
[root@mysql ~]# /etc/init.d/mysql start
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]
[root@mysql ~]#
[root@mysql ~]# cat /var/log/mysqld.log | grep --color "temporary password"
2016-07-12T06:02:56.354249Z 1 [Note] A temporary password is generated for root@localhost: ieZR>gkEo0/B
2016-07-12T06:23:42.265244Z 1 [Note] A temporary password is generated for root@localhost: +yulyV=z=2ee
2016-07-12T06:27:16.936863Z 1 [Note] A temporary password is generated for root@localhost: pfd.xjq;y4yN
2016-07-12T06:45:18.254419Z 1 [Note] A temporary password is generated for root@localhost: J7ry)jcx&FN4
2016-07-13T12:25:51.125400Z 1 [Note] A temporary password is generated for root@localhost: lE,mKJ&.C80T
2016-07-13T14:43:38.750329Z 1 [Note] A temporary password is generated for root@localhost: syWuLtwg:3wc
[root@mysql ~]#
[root@mysql ~]# mysql -u root -p'syWuLtwg:3wc'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.13-6-log
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password for root@'localhost' = password('Abcd!234');
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql>
Slave配置到Master的连接：
mysql> show slave status\G
Empty set (0.00 sec)
mysql>
mysql> change master to master_host='192.168.232.141',
-> master_user='adamhuan',
-> master_password='Abcd!234',
-> master_log_file='/var/lib/mysql/master.bin',
-> master_log_pos=0;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql>
mysql> change master to master_log_file='master.000004',master_log_pos=154;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.232.141
Master_User: adamhuan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master.000004
Read_Master_Log_Pos: 154
Relay_Log_File: slave.000003
Relay_Log_Pos: 317
Relay_Master_Log_File: master.000004
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: 154
Relay_Log_Space: 514
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: 24
Master_UUID: 95b03d52-4906-11e6-93f5-000c29f6ffc1
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql>主要确认以下两个值的OK与否：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
后台日志（start slave）：
2016-07-13T14:54:35.796274Z 6 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 2016-07-13T14:54:35.863716Z 6 [Note] Slave I/O thread for channel '': connected to master 'adamhuan@192.168.232.141:3306',replication started in log 'master.000004' at position 154
这里的日志显示，不总是这样的。
比方说，出问题的时候：
2016-07-13T14:49:03.619151Z 4 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 2016-07-13T14:49:03.622533Z 5 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0 2016-07-13T14:49:03.623236Z 5 [Note] Slave SQL thread for channel '' initialized, starting replication in log '/var/lib/mysql/master.bin' at position 4, relay log '/var/lib/mysql/slave.000001' position: 4 2016-07-13T14:49:09.029738Z 4 [Note] Slave I/O thread for channel '': connected to master 'adamhuan@192.168.232.141:3306',replication started in log '/var/lib/mysql/master.bin' at position 4 2016-07-13T14:49:09.034315Z 4 [ERROR] Error reading packet from server for channel '': Could not find first log file name in binary log index file (server_errno=1236) 2016-07-13T14:49:09.034396Z 4 [ERROR] Slave I/O for channel '': Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Error_code: 1236 2016-07-13T14:49:09.034417Z 4 [Note] Slave I/O thread exiting for channel '', read up to log '/var/lib/mysql/master.bin', position 4
检查备库在Start slave的时候，设定的：
1. master_log_file
2. master_log_pos
与主库的“show master status”中的第一二列的值是否一致。
如果不一致，就会报出上面日志（/var/log/mysqld.log）中的错误。
三、查看一下进程状态。
主库端查看进程：
mysql> show processlist\G
*************************** 1. row ***************************
Id: 5
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
Rows_sent: 0
Rows_examined: 0
*************************** 2. row ***************************
Id: 7
User: adamhuan
Host: 192.168.232.144:57092
db: NULL
Command: Binlog Dump
Time: 442
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
Rows_sent: 0
Rows_examined: 0
2 rows in set (0.00 sec)
mysql>
辅助端进程状态：
mysql> show processlist\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 498
State: Slave has read all relay log; waiting for more updates
Info: NULL
Rows_sent: 0
Rows_examined: 0
*************************** 2. row ***************************
Id: 5
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
Rows_sent: 0
Rows_examined: 0
*************************** 3. row ***************************
Id: 6
User: system user
Host:
db: NULL
Command: Connect
Time: 498
State: Waiting for master to send event
Info: NULL
Rows_sent: 0
Rows_examined: 0
3 rows in set (0.02 sec)
mysql>
至此，MySQL的Replication就配置好了。
四、测试：
先查看下备库的状态：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql>
查看下主库的状态：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql>
主库端创建一个新的DB：
mysql> create database dilraba; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | dilraba | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql>
在查看下备库：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | dilraba | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql>
可以看到，主库的数据库增加信息被自动的同步到了备库端。
主库创建一个表，并添加一些记录：
mysql> create table dilraba.people(
-> name varchar(60),
-> location varchar(90)
-> );
Query OK, 0 rows affected (0.10 sec)
mysql>
mysql> insert into dilraba.people values('dilraba','urlwuki');
Query OK, 1 row affected (0.07 sec)
mysql> insert into dilraba.people values('adamhuan','china');
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> select * from dilraba.people;
+----------+----------+
| name | location |
+----------+----------+
| dilraba | urlwuki |
| adamhuan | china |
+----------+----------+
2 rows in set (0.00 sec)
mysql>
备库：
mysql> select * from dilraba.people; +----------+----------+ | name | location | +----------+----------+ | dilraba | urlwuki | | adamhuan | china | +----------+----------+ 2 rows in set (0.00 sec) mysql>
可以看到，备库自动应用了来自主库的变更。
至此，关于MySQL的Replication，完成。
————————————————
Done。