1. 环境说明

角色 系统 mysql 版本 ip
centos7 5.6 192.168.139.132
ubuntu 18.04.2 5.7 192.168.139.131

2. 两个数据库版本尽量保持一致

3. 创建复制账号

1
2
3
4
5
6
7
8
// master mysql 5.6
$ mysql -uroot -p
// Enter password
mysql> grant replication slave on *.* to 'backup'@'192.168.139.131' identified by '123456';

// 如果不行,就执行下面的步骤
mysql> create user 'backup'@'192.168.139.132' identified by '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.139.132';

4. 配置主服务器

mysql5.6 的配置文件在 /etc/my.cnf

1
vim /etc/my.cnf

找到[mysqld],在下面添加如下代码

1
2
3
4
5
6
[mysqld]
server-id=1 # 服务器id
log-bin = mysql-bin
binlog-do-db=gonghui #需要被复制的数据库名,如果被复制多个数据库,重复设置这个选项即可
binlog-ignore-db=mysql #不需要被复制的数据库名
innodb_flush_log_at_trx_commit = 0

5. 配置从服务器

mysql5.7 的配置文件是 /etc/mysql/mysql.conf.d/mysqld.cnf

找到[mysqld],在其下加入

1
2
3
4
5
[mysqld]
server-id=2 # 服务器id
log-bin = mysql-bin #binlog日志名
replicate-do-db = gonghui #需要复制的数据库名,如果复制多个数据库,重复设置这个选项即可
replicate-ignore-db= mysql #不需要复制的数据库名

配置主从服务器的my.cnf时,留心各自的server-id一定要彼此独立,不能重复,否则,会出现如下错误:

1
Slave: received end packet FROM server, apparent master shutdown

重启主从数据库,即可生效

1
$ systemctl restart mysql

然后在主数据库上输入

1
2
3
4
5
6
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000013 | 881 | gonghui | mysql |
+------------------+----------+--------------+------------------+

FilePosition信息,需要在下一步在从数据库上使用

6. 设置从数据库复制参数

  • master_log_file 为主数据库的File
  • master_log_pos为主数据库的Position
1
2
3
4
5
6
7
8
9
10
11
12
13
$ mysql -uroot -p

// 修改参数之前需要先关闭 slave
mysql> stop slave

mysql> CHANGE MASTER TO master_host = '192.168.139.132',
master_user = 'tld',
master_password = '123456',
master_log_file = 'mysql-bin.000013',
master_log_pos = 881;

// 修改完毕再开启,即可生效
mysql> start slave

输入以下代码即可查看是否成功完成主从复制,若是如下方所示,就是完成主从复制

  • Slave_IO_Running: Yes
  • Slave_SQL_Running: Yes

若是没有对应上,则根据Last_SQL_Error 的错误提示进行错误定位并修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.139.132
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 881
Relay_Log_File: ubuntu-relay-bin.000004
Relay_Log_Pos: 442
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: gonghui
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: 881
Relay_Log_Space: 650
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
Master_UUID:
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)

ERROR:
No query specified

若是要实现双向的主从复制,只要主从对调之后再次执行之前的步骤即可。