Mysql Binlog 主从模式配置 与 验证
1）准备两台Mysql服务，并启动服务
127.0.0.1：3306 主机
127.0.0.1：3307 从机
主从配置前需要确认 两机实例间 库、表、数据一致，不然会导致无法同步。
2）主机 my.ini / my.cnf 文件配置
#二进制文件，主机环境必开
log-bin=mysql-bin
#主机服务ID ，必须唯一
server-id=111
3）从机 my.ini / my.cnf 文件配置
#二进制文件，从机可以不开，建议开启
log-bin=mysql-bin
#主机服务ID ，必须唯一
server-id=111
4）主机环境开通数据同步用户
GRANT REPLICATION SLAVE ON *.* to 'mycat_sync'@'%' identified by 'mycat_sync';
5）主机查年binLog状态
show master status;
6）从机配置同步 并 启动从机状态
change master to master_host='192.168.1.247'
,master_port=3306
,master_user='mycat_sync'
,master_password='mycat_sync'
,master_log_file='mysql-bin.000001'
,master_log_pos=832;
start slave;
7）关注Slave_IO_State,Slave_IO_Running,Slave_SQL_Running状态
若都为yes状态时确认同步配置完成
show slave status;
8）验证主从同步有效性，在主机中 Drop Database mycat
8.1）主机环境查询库状态
这里有个坑， 两机同步时需要确保两边的内容完全一致，后面验证时做了数据清理以完成所有验证步骤。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ecshopdb |
| mycat |
| mysql |
| performance_schema |
| shopnc |
| shopnc2 |
| test |
| testdb |
| xjh |
+--------------------+
10 rows in set (0.00 sec)
8.2）从机查询库状态
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mycat |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
8.3）主机 drop database mycat ，并再次确认
mysql> drop database mycat;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ecshopdb |
| mysql |
| performance_schema |
| shopnc |
| shopnc2 |
| test |
| testdb |
| xjh |
+--------------------+
9 rows in set (0.00 sec)
8.4）从机状态查询确认
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
9）Create Database、Create Table 验证
9.1）主机操作
mysql> create database mycat_sync_test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ecshopdb |
| mycat_sync_test |
| mysql |
| performance_schema |
| shopnc |
| shopnc2 |
| test |
| testdb |
| xjh |
+--------------------+
10 rows in set (0.00 sec)
mysql> use mycat_sync_test;
Database changed
mysql> CREATE TABLE `aaa` (
-> `id` INT NOT NULL,
-> `context` VARCHAR(45) NULL,
-> PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+---------------------------+
| Tables_in_mycat_sync_test |
+---------------------------+
| aaa |
+---------------------------+
1 row in set (0.00 sec)
9.2）从机验证
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mycat_sync_test |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mycat_sync_test;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mycat_sync_test |
+---------------------------+
| aaa |
+---------------------------+
1 row in set (0.00 sec)
10）Insert、Update、Delete 验证
10.1）主机操作 INSERT
mysql> insert into aaa values(111,'test context');
Query OK, 1 row affected (0.00 sec)
mysql> select * from aaa;
+-----+--------------+
| id | context |
+-----+--------------+
| 111 | test context |
+-----+--------------+
1 row in set (0.00 sec)
10.2）从机验证 INSERT
mysql> select * from aaa;
+-----+--------------+
| id | context |
+-----+--------------+
| 111 | test context |
+-----+--------------+
1 row in set (0.00 sec)
10.3）主机操作 UPDATE
mysql> update aaa set context='hello world' where id=111;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from aaa;
+-----+-------------+
| id | context |
+-----+-------------+
| 111 | hello world |
+-----+-------------+
1 row in set (0.00 sec)
10.4）从机验证 UPDATE
mysql> select * from aaa;
+-----+-------------+
| id | context |
+-----+-------------+
| 111 | hello world |
+-----+-------------+
1 row in set (0.00 sec)
10.5）主机操作 DELETE
mysql> truncate table aaa;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from aaa;
Empty set (0.00 sec)
10.6）从机验证 DELETE
mysql> select * from aaa;
Empty set (0.00 sec)
