潇湘夜雨移动版

         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可以重新定位。