Mysql Binlog Master-Slave mode configuration and verification

1) Prepare two MySQL services and start the service

127.0.0.1:3306 Host

127.0.0.1:3307 slave Machine

Before and after the master-slave configuration needs to confirm the two machine instances between the library, table, data consistent, otherwise it will cause synchronization.

2) host MY.INI/MY.CNF file configuration

#二进制文件, the host environment must be open

Log-bin=mysql-bin

#主机服务ID, must be unique

server-id=111

3) my.ini/my.cnf file configuration from the machine

#二进制文件, can not open from the machine, it is recommended to open

Log-bin=mysql-bin

#主机服务ID, must be unique

server-id=111

4) Host environment to open data synchronization users

GRANT REPLICATION SLAVE on * * to ' mycat_sync ' @ '% ' identified by ' Mycat_sync ';

5) host check year Binlog status

Show master status;

6) sync from machine configuration and start slave state

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) Focus on slave_io_state,slave_io_running,slave_sql_running status

Verify that the synchronization configuration is complete if both are Yes states

show slave status;

8) Verify master-Slave synchronization validity, Drop Database Mycat in Host

8.1) Host Environment Query library status

There's a pit here, and two machines need to ensure that the contents of both sides are exactly the same, and that data cleanup is done to complete all the verification steps after verification.

mysql> show databases;

+--------------------+
| Database |
+--------------------+
| Information_schema |
| ecshopdb |
| Mycat |
| MySQL |
| Performance_schema |
| SHOPNC |
| Shopnc2 |
| Test |
| TestDB |
| Xjh |
+--------------------+
Rows in Set (0.00 sec)

8.2) Query the library status from the machine

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Mycat |
| MySQL |
| Performance_schema |
+--------------------+
4 rows in Set (0.00 sec)

8.3) host drop database Mycat, and confirm again

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) Check confirmation from machine status

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
+--------------------+
3 Rows in Set (0.00 sec)

9) Create Database, create Table validation

9.1) Host operation

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 |
+--------------------+
Rows in Set (0.00 sec)

mysql> use mycat_sync_test;
Database changed
mysql> CREATE TABLE ' aaa ' (
' ID ' INT not NULL,
' Context ' VARCHAR () 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) Slave-to-machine verification

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)

Insert, Update, Delete validation

10.1) host Operation 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) Verify the INSERT from the machine

Mysql> SELECT * from AAA;
+-----+--------------+
| ID | Context |
+-----+--------------+
| 111 | Test Context |
+-----+--------------+
1 row in Set (0.00 sec)

10.3) host Operation 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) Verify the UPDATE from the machine

Mysql> SELECT * from AAA;
+-----+-------------+
| ID | Context |
+-----+-------------+
| 111 | Hello World |
+-----+-------------+
1 row in Set (0.00 sec)

10.5) host Operation DELETE

mysql> TRUNCATE TABLE AAA;
Query OK, 0 rows Affected (0.00 sec)

Mysql> SELECT * from AAA;
Empty Set (0.00 sec)

10.6) Verify the DELETE from the machine

Mysql> SELECT * from AAA;
Empty Set (0.00 sec)

Mysql Binlog Master-Slave mode configuration and verification