|
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
以下两个参数都为yes表示设置成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
三、测试
1.测试同步效果
在主库创建数据
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
查看从库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
接收到的用于同步的主库gtid号
Retrieved_Gtid_Set: fec611e4-99a2-11e6-b35d-000c29f8c231:1
Executed_Gtid_Set: fec611e4-99a2-11e6-b35d-000c29f8c231:1
2.网络数据库测试
授权网络连接数据库的用户
mysql> grant all on *.* to admin@'172.18.109.%' identified by 'admin';
测试php脚本
vim test.php
<?php
$conn=mysql_connect('172.18.109.235','admin','admin');
if ($conn)
echo "Success...";
else
echo "Failure...";
?>
将test.php脚本放到lamp或lnmp环境中测试是否连接数据库成功。
3.查看多线程复制状态
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 3 | system user | | NULL | Connect | 3770 | Waiting for master to send event | NULL |
| 4 | system user | | NULL | Connect | 100 | Slave has read all relay log; waiting for more updates | NULL |
| 5 | system user | | NULL | Connect | 100 | Waiting for an event from Coordinator | NULL |
| 6 | system user | | NULL | Connect | 611 | Waiting for an event from Coordinator | NULL |
| 7 | system user | | NULL | Connect | 618 | Waiting for an event from Coordinator | NULL |
| 8 | system user | | NULL | Connect | 618 | Waiting for an event from Coordinator | NULL |
| 9 | system user | | NULL | Connect | 3770 | Waiting for an event from Coordinator | NULL |
| 10 | system user | | NULL | Connect | 3770 | Waiting for an event from Coordinator | NULL |
| 11 | system user | | NULL | Connect | 3770 | Waiting for an event from Coordinator | NULL |
| 12 | system user | | NULL | Connect | 3770 | Waiting for an event from Coordinator | NULL |
| 13 | system user | | NULL | Connect | 3770 | Waiting for an event from Coordinator | NULL |
| 14 | system user | | NULL | Connect | 3770 | Waiting for an event from Coordinator | NULL |
| 15 | system user | | NULL | Connect | 3770 | Waiting for an event from Coordinator | NULL |
| 16 | system user | | NULL | Connect | 3770 | Waiting for an event from Coordinator | NULL |
| 17 | system user | | NULL | Connect | 3770 | Waiting for an event from Coordinator | NULL |
| 18 | system user | | NULL | Connect | 3770 | Waiting for an event from Coordinator | NULL |
| 19 | system user | | NULL | Connect | 3770 | Waiting for an event from Coordinator | NULL |
| 20 | system user | | NULL | Connect | 3770 | Waiting for an event from Coordinator | NULL |
| 21 | root | localhost | NULL | Query | 0 | starting | show processlist |
可以看出有16个用于同步的线程在等待
查看主库状态
mysql> show processlist;
+----+------+----------------------+------+------------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------------+------+------------------+------+---------------------------------------------------------------+------------------+
| 9 | lzh | 172.18.109.236:43941 | NULL | Binlog Dump GTID | 3524 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 10 | lzh | 172.18.109.237:59440 | NULL | Binlog Dump GTID | 3346 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 55 | root | localhost | NULL | Query | 0 | starting
| show processlist |
四、常见错误
1.使用start slave命令开启失败：
错误如下
mysql> start slave；
ERROR 1872 (HY000)： Slave failed to initialize relay log info structure from the repository
分析：由于修改主机名后，relay-log的名称发生变化，导致文件IO失败，reset slave可以重新定位。
(责任编辑：liangzh) |