文章参考:https://jingyan.baidu.com/article/90808022ae0b8ffd91c80f8b.html
http://www.cnblogs.com/taosim/articles/4105181.html
环境:
192.168.1.81 :A服务器
192.168.1.112 :B服务器
步骤:
一:添加同步账户
创建同步账号:
grant replication slave,replication client on *.* to repl@'192.168.1.112' identified by '123456';
A服务器
grant replication slave,replication client on *.* to repl@'192.168.1.81' identified by '123456';
B服务器
注意:repl是创建的同步user
注:以上命令在A、B服务器上都要执行
二:配置A服务器
[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1
relay-log=mysql-relay-bin #开启中继日志
log_slave_updates = on #从服务器将时间记录到二进制日志中
auto_increment_increment=2 #自动增长的步长
auto_increment_offset=1 #自动增长的起始数值
重启mysql服务
三:配置B服务器
[mysqld]
server-id=2
log-bin=mysql-bin
log_slave_updates = on
auto_increment_increment=2
auto_increment_offset=2
#B服务器的配置,先关闭二进制日志
重启mysql服务器
四:创建测试表
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql>
select
* from info;
+-----+-------+-----+
| sid | name | age |
+-----+-------+-----+
| 1 | zhang | 23 |
| 2 | li | 12 |
| 3 | cheng | 34 |
| 4 | wang | 22 |
| 5 | chen | 44 |
+-----+-------+-----+
5 rows
in
set
(0.00 sec)
#在A服务器上创建测试用的表
|
将表导入到B服务器上
1
2
3
4
|
[root@oracle ~]
# mysqldump --databases data --lock-all-tables --master-data=2 > /root/dump.sql
[root@oracle ~]
# scp /root/dump.sql root@192.168.2.96:/root/
#A服务器上dump+拷贝
[root@node2 ~]
# mysql < dump.sql #B服务器上执行
|
五:服务器设置: 用 show master status\g;来查询名称和pos
change master to master_host='192.168.1.112', master_user='repl',master_password='12345',master_log_file='mysql-bin.000001', master_log_pos=106;
A服务器
1
2
3
4
|
[root@node2 ~]
# vim /etc/my.cnf
#将刚刚注释掉的参数生效
log-bin=mysql-bin
log_slave_updates = on
|
change master to master_host='192.168.1.81', master_user='repl',master_password='12345',master_log_file='mysql-bin.000001', master_log_pos=120;
B服务器
六:查询状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.112
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 106
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.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: 106
Relay_Log_Space: 406
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:
1 row in set (0.00 sec)
六:查看slave状态
查看A、B服务器的状态:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 192.168.2.96
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#A服务器上的状态
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 192.168.2.93
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#在B服务器上查看状态
|