MYSQL cross-server data synchronization experience

The project requires you to find some information and configure it yourself.

(1) master server
Modify the configuration file/etc/my. cnf (my. ini)
[Mysqld]
# Mysql-bin is the prefix of the log file. You can also use other names, such as the server name.
# If the path is not included, the log file will be written to '/var/lib/mysql'.
Log-bin = mysql-bin
# Serverid must be unique in a synchronization system, and must be an integer greater than or equal to 1 and less than 2 ^ 32-1
Server-id = 1

Binlog-do-db = Database Name (the database you want to back up)
Binlog-ignore-db = Database Name (the database you do not need to back up)
# None of the above two are selected. Back up all

# If InnoDb is used, the following two lines should be set to ensure stability:
Innodb_flush_log_at_trx_commit = 1
Sync_binlog = 1
# Make sure that no skip-networking is set, and the network cannot be synchronized if it is disabled. However,
# Innodb_flush_log_at_trx_commit = 1 on some servers, causing a sharp decrease in the write speed,
# Try to adjust it to 2.

# Manually add and restart the mysql server

[Add an account]
# Procedure for setting up an account:
# Log on to the mysql master server,
[Root @ localhost ~] # Mysql-uroot-p123456 log on to mysql
# Here, You need to specify that 123456 is my mysql password.
# Add a user named beifen and authorize it to the slave server
Mysql> grant replication slave on *. * to 'beifen' @ '192. 168.1.2 'identified by '123 ';
# Create a beifen user, set the password to 123, and authorize it to 192.168.1.2 (the ip address is the slave ip address.

Mysql-> show master status; # view the information and write down the values of File and Position,
# This is used when configuring the slave Database

(2) slave server my. cnf settings (if you need two-way Master, except for the server-id, other configurations are the same as the master server)
Server-id is required, and binlog is not required.
Modify the configuration file/etc/my. cnf (my. ini)
Change service-id = 1 to server-id = 2
Restart the mysql server and log on

[Process data first if there is data]
(Some people say that mysql> stop slave is executed when data is imported. The test is not performed)
Data Processing Method:
Flush tables with read lock; 1 LOCK the master server
2. export data from the master server,
3. import data to the slave server
Unlock tables; 4 UNLOCK

# Set the master-slave relationship (which can also be used on the server, indicating two-way)
Mysql-> change master to master_host = '192. 168.1.1 ', (master server IP address)
Master_user = 'beifen', master_password = '000000 ',
Master_log_file = 'mysql-bin.000001 ', (the master server queries the file value)
Master_log_pos = 107; (the master server queries the Position value)

Start slave Synchronization
Mysql> start slave;

# Check the master-slave synchronization. If you see that Slave_IO_Running and Slave_ SQL _Running are both Yes,
# The Master-slave replication connection is normal.
Mysql> show slave status \ G