MySQL Master-Slave synchronization

One, the principle of MySQL master-slave synchronization

The main machine turns on the Bin-log log, synchronizes the Bin-log log to the slave machine, from the machine to the Relay-log according to the Bin-log log, and updates the data from the machine's SQL thread according to the Relay-log.

Second, to build MySQL master-slave synchronization before the environment description:

1, it is recommended that the MySQL version of 2 machines is the same, or from a version higher than the main version.

2, turn off SELinux.

3, to ensure the data consistency of 2 machines, if inconsistent, it is recommended to use rsync for data synchronization.

4,192.168.2.66 main MySQL 192.168.2.69 for slave MySQL.

Third, build asynchronous MySQL master-slave synchronization (i.e. not on a single machine)

1, install MySQL on 2 machines respectively, the specific installation method such as Link: http://www.cnblogs.com/lin1/p/5645761.html

2, operate on the main machine:

2.1 Open Binlog Log, set Server-id, restart Mysqld service. Specific as follows:

Vi/etc/my.cnf
[Mysqld]
Log-bin=mysql-bin//[must] enable binary logging
server-id=222//[must be] server unique ID, default is 1.

/etc/init.d/mysqld restart

2.2 Create an account and authorize it to slave.

Grant Replication Slave on * * to ' 192.168.2.69 ' @ ' repl ' identified by ' Lin ';

2.3, refresh the permissions, and lock the table.

Flush privileges;

Flush tables with read lock;

2.4. View the master status.

Show master status;

It needs to be used behind the mark, so please remember.

3, in the operation from the machine;

3.1 Modify server_id, but here the server_id must not be consistent with the main MySQL, here I write is 2. Here the Bin-log log can not be opened, restart the service.

3.2 Stop Slave. Stop slave

3.3 Connect to master MySQL.

Change Master to master_host= ' 192.168.2.66 ', master_port=3306, master_user= ' repl ', master_password= ' Lin ', master_   Log_file= ' mysql-bin.000001 ', master_log_pos=624; The value of Show Master status is used here.

3.4, start slave. Start slave.

3.5, view the status from.

show slave status\g;

Note check:
Slave_io_running:yes
Slave_sql_running:yes
The values for these two parameters are yes, which means the configuration was successful!

3.6 When the configuration is successful, do not forget to return to our Lord to cancel the lock table.

Unlock tables;

Third, test master and slave.

Create a library in the Lord, create a table inside the library, and then view from above whether a library and a table are more than one. If the corresponding libraries and tables are also shown above, the master-slave test OK is indicated.

MySQL Master-Slave synchronization