MySQL AB复制(八)
rv08查看slave状态
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.11
Master_User: larry
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 107
Relay_Log_File: serv08-relay-bin.000007
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000003
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: 556
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
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)ERROR:
No query specified
--如果查看slave状态出错，我们重启服务
[root@serv08 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
第四步，serv09搭建相同版本的MySQL，修改server-id，启动服务
[root@serv09 ~]# vim /etc/my.cnf
[root@serv09 ~]# cat /etc/my.cnf | grep server-id
server-id = 3
[root@serv09 ~]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
第五步，serv01插入数据
mysql> use larrydb;
Database changed
mysql> select * from t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
| 4 | larry03 |
| 6 | larry04 |
| 7 | larry05 |
+----+---------+
5 rows in set (0.00 sec)mysql> insert into t2(name) values('larry07');
Query OK, 1 row affected (0.01 sec)
第六步，serv08查看serv01插入的数据是否记录到日志文件。可以发现，和serv01建立关系的延时日志文件中有相关记录，而主日志文件mysql-bin.000003中没有相关记录
[root@serv08 data]# mysqlbinlog serv08-relay-bin.000009 | grep insert -i --color
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
SET INSERT_ID=9/*!*/;
insert into t2(name) values('larry07')
[root@serv08 data]# mysqlbinlog mysql-bin.000003 | grep larry07
第七步，我们要把serv08的数据同步到serv09，因为serv08中的mysql-bin.000003文件没有相关记录，所以不能通过日志文件同步，我们只有先serv08导出数据，然后serv09导入数据，再把log_slave_updates打开
--导出数据
[root@serv08 data]# mysqldump --help --verbose | grep database[root@serv08 data]# mysqldump --databases larrydb > larrydb.sql
--拷贝数据文件
[root@serv08 data]# scp larrydb.sql 192.168.1.19:/opt
root@192.168.1.19's password:
larrydb.sql 100% 2612 2.6KB/s 00:00 --serv09导入数据
mysql> source /opt/larrydb.sql;
Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 1 row affected (0.01 sec)Database changed
Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplic