MySql multi-Server Database Synchronization

1). Description of configuration statements (basic knowledge ):
# Server ID. the ID of each server cannot be set to the same number.
Server-id = 1

# Enable slave database logs for chained Replication
Log-slave-updates

# Whether the slave database is read-only. 0 indicates read/write, and 1 indicates read-only.
Read-only = 1

# Copy only a table
Replicate-do-table = tablename

# Copy only some tables (matching characters available)
Replicate-wild-do-table = tablename %

# Only copy a database
Replicate-do-db = dbname

# Copy only some databases
Replicte-wild-do-db = dbname %

# Do not copy a table
Replicate-ignore-table = tablename

# Do not copy some tables
Replicate-wild-ignore-table = tablename %

# Do not copy a database
Replicate-ignore-db = dbname

# Ignore when an error occurs. If this is not added, the synchronization process will terminate if any error occurs.
Slave-skip-errors = all

Sync_binlog = 1

# Whether the copied SQL statement is cleared immediately from the relay log. 1 indicates clearing immediately
Relay-log-purge = 1

# Slave server host, used to generate slave database list for show slave hosts
Report-host = hostname

# Master server host address and port used to connect from the slave server to the master server
Master-host = hostname
Master-port = 3306

# Master server database username and password (please grant copy permission (slave ))
Master-user = dbuser
Master-password = password

# Default Retry Interval
Master-connect-retry = 60

# Enable database logs
Log-bin = mysql-bin.log

# Name of the database to be synchronized
Binlog-do-db = dbname

# Name of the database to be synchronized
Binlog-ignore-db = dbname

2). instance Configuration

A). host a (master) ip Address: 210.21.104.237
B) server B (slave) ip: 58.177.114.3

(All configurations are added under the my. cnf [mysqld] configuration section)
Master: machine A configuration
Server-id = 1
Log-bin = mysql-bin.log
Binlog-do-db = test
Binlog-ignore-db = mysql

Restart mysql
Log on to mysql
To add a synchronization account, you 'd better specify the logon IP Address:

# Grant all Permissions
Mysql> grant all on *. * To 'test' @ '58. 177.114.3 'identified by 'test ';
# Grant replication permissions only
Mysql> grant replication slave on *. * To 'test' @ '58. 177.114.3 'identified by 'test ';
(Note: all the above commands must use on *. *; otherwise, an error is reported)
# Refresh
Mysql> flush privileges;

# Display Master Status
Mysql> show Master status;

-----------------------------------------------------------------------------
| File | position | binlog_do_db | binlog_ignore_db |
+ -------------------- + ---------- + ------------------ + -------------------- +
| Mysql-bin.000001 | 98 | test | mysql |
+ -------------------- + ---------- + ------------------ + -------------------- +
If the above information is displayed, the master configuration is successful. Write down the data in the File and Position columns for configuring slave.

Slave: machine B Configuration
Server-id = 2
Master-host = 210.21.104.237
Master-port = 3306
Master-user = test
Master-password = test
Master-connect-retry = 60
Replicate-do-db = test
Replicate-ignore-db = mysql

Restart mysql
Enter Database

# Stop slave
Mysql> slave stop;

# Configure synchronization file Parameters
Mysql> change master to MASTER_HOST = '2017. 172.241.12 ', MASTER_USER = 'your LaB _ authen', MASTER_PASSWORD = 'your LaB _ authen ', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 98;

Change master to MASTER_HOST = '58. 177.114.3 ', MASTER_USER = 'your LaB _ authen', MASTER_PASSWORD = 'your LaB _ authen ', MASTER_LOG_FILE = 'mysql-bin.000009', MASTER_LOG_POS = 98;

# Start slave
Mysql> start slave;

Mysql> show processlist;
+ ---- + ------------- + ----------- + ------ + ------------ + ------- + ----------------------------------------------------------------------- + -----------------------
| Id | User | Host | db | Command | Time | State | Info |
+ ---- + ------------- + ----------- + ------ + ------------ + ------- + ----------------------------------------------------------------------- + -----------------------
| 6 | system user | null | connection | 57236 | waiting for Master to send event | null |
| 7 | system user | null | connect | 31988 | has read all relay log; waiting for the slave I/O thread to update it | null |
| 58 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+ ---- + ------------- + ----------- + ------ + ------------ + ------- + ----------------------------------------------------------------------- + -----------------------
3 rows in set (0.00 sec)
If the above information is displayed, the synchronization is successful.