MySQL 主从复制

配置

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
64
65
66
67
# 创建复制帐号,主库和从库都需创建
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repl@'192.168.100.%' IDENTIFIED BY '123456';


# 修改主库 my.cnf 指定一个独一无二的服务器 ID(server ID)
log_bin = mysql-bin
server_id = 21
# 如果之前没有在 Mysql 配置文件中指定 log-bin 选项,就需要重启 mysql
# 使用 SHOW MASTER STATUS 命令,检查二进制日志文件是否已经在主库上创建
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+


# 从库上修改 my.cnf 配置,需重启 Mysql
log_bin = mysql-bin
server_id = 22
relay_log = /var/lib/mysql/mysql-relay-bin
log_slave_updates = 1
read_only = 1


# 启动复制
# 下一步告诉从库如何连接到主库并重放其二进制日志。
mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.21',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=0;
# 开启复制
mysql> START SLAVE;

# 主库可以看到由从库 IO 线程向主库发起的连接
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 5
User: repl
Host: 192.168.100.22:54294
db: NULL
Command: Binlog Dump
Time: 88
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL

# 从库可以看到 IO 线程和 SQL 线程
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 2005
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 1184
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL

参考

0%