Query OK, 0 rows affected (0.01 sec)
第二步，serv08清空日志
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 286 |
+------------------+-----------+
1 row in set (0.00 sec)mysql> reset master;
Query OK, 0 rows affected (0.00 sec)mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 107 |
+------------------+-----------+
1 row in set (0.00 sec)
第三步，serv01使用change master to命令修改从服务器设置
mysql> show slave status;
Empty set (0.00 sec)mysql> change master to
-> master_host='192.168.1.18',
-> master_user='larry',
-> master_password='larry',
-> master_port=3306,
-> master_log_file='mysql-bin.000001',
-> master_log_pos=107;
Query OK, 0 rows affected (0.01 sec)
第四步，serv01开启slave
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.1.18
Master_User: larry
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: serv01-relay-bin.000002
Relay_Log_Pos: 253
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: 107
Relay_Log_Space: 410
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:
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: 2
1 row in set (0.00 sec)ERROR:
No query specified
第五步，测试
--serv01查看数据
mysql> use larrydb;
Database changed
mysql> select * from test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)--serv08插入数据
mysql> use larrydb;
Database changed
mysql> select * from test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)mysql> insert into test values(2);
Query OK, 1 row affected (0.00 sec)--serv01查看数据，数据更新
mysql> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)--serv01插入数据
mysql> insert into test values(3);
Query OK, 1 row affected (0.01 sec)--serv01查询数据
mysql> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)--serv08查询数据，数据已更新
mysql> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
MySQL多级主从复制
好了，MySQL AB双向复制介绍完毕，我们又想了，不管是MySQL AB单向复制，还是MySQL 双向复制，都是双方的关系。MySQL AB单向复制可以是一对一，也就是一个Master对应一个Slave，或者一对多，也就是一个Master对应多个Slave；MySQL双向复制是一对一的关系。我们可不可以这样，实现多级关系，一个Master，接下来Slave，Slave下面还有Slave。这样做有什么好处呢？这样可以缓解数据库压力。这就是接下来要介绍的MySQL多级主从复制。多级也就是A---->B---->C，A作为主服务器，B是从服务器，B跟A建立主从关系；而且B是主服务器，C作为从服务器，B跟C建立主从关系。这样：A是主服务器，B既是主服务器，又是从服务器，C是