MYSQL data synchronization experience sharing across servers

Project needs, I find some materials and personally configure after; Experience sharing.

(1) Primary server
Modify configuration file /etc/ my.cnf (my.ini)
[mysqld]
# ES11en-ES12en is the prefix for the log file. You can also use other names, such as the server name
# Without a path, the log file is written under '/var/lib/mysql'
log-bin=mysql-bin
# serverid must be the only integer greater than or equal to 1 and less than 2^32-1 in a synchronous system
server-id=1

binlog-do-db = Database name (the database you want to back up)
binlog-ignore-db = database name (database you do not need to back up)
Neither of the above options, backup all

# If using InnoDb, the following two lines should be set for stability:
innodb_flush_log_at_trx_commit=1
sync_binlog=1
# And make sure that ES43en-ES44en is not set, disable the network naturally unable to synchronize. However,
#innodb_flush_log_at_trx_commit=1 Causes a sharp drop in write speed on some servers,
# can try to adjust to 2.

Add it yourself manually and restart the mysql server

[Optional operation to add account]
# Account Setup Steps:
# Login to mysql primary server,
[root@localhost ~]# ES64en-ES65en? p123456 login to mysql
This 123456 is my mysql password
# Add a user called beifen and license it to the slave server
mysql > grant replication slave on *.* to 'beifen'@'192.168.1.2' identified by '123';
# Create beifen user with password set to 123 and authorize to 192.168.1.2 (ip for slave ip).

mysql- > SHOW MASTER STATUS; # View the information and note the values for File and Position,
This is used when configuring a database

(2) Slave server ES92en. cnf setting (if bidirectional master is required, other configuration is like master server 1 except ES94en-ES95en not one)
server-id must be set, binlog is not on.
Modify configuration file /etc/ ES102en. cnf (ES104en. ini)
service-id =1 is modified to ES109en-ES110en =2
And restart the mysql server and log in

[If there is data, process the data first]
(Some say mysql is executed when the data is imported > stop slave; No specific test)
Data processing method:
FLUSH TABLES WITH READ LOCK; Lock the primary server
2. Export data from the primary server,
Import from the server
UNLOCK TABLES; 4 the unlock

# Set the master-slave relationship (which can also be used on the server to indicate two-way traffic)
mysql- > change master to master_host='192.168.1.1', (master server IP)
master_user='beifen',master_password='123',
master_log_file=' ES149en-ES150en.000001 ', (file value found by primary server)
master_log_pos = 107; (Position value queried by the primary server)

Start the slave synchronization
mysql > start slave;

Check master-slave synchronization, if you see that Slave_IO_Running and Slave_SQL_Running are both Yes,
Then the master-slave copy connection is normal.
mysql > show slave status\G