MySQL Database master-Slave synchronization

1, getting ready for work
Prepare two machines and install each MySQL database. Like what

10.254.141.23 as the primary database
10.254.166.60 as from the database

Installing MySQL Database Reference "MySQL database Installation"
2, Main library open Binlog
Look at the main library, is not open binlog, master-Slave synchronization needs to open this.
Mysql> Show master status;

There are binlog files, and the current location display, indicating that the binglog opened, otherwise you need to open this, modify/ETC/MY.CNF, and then restart the database.
Add these sentences to the configuration file
Log-bin = Mysql-bin
Relay-log = Relay-bin
Binlog_format = MIXED
Binlog_cache_size = 1M
3. Create a sync account on the main library
Add an account to synchronize from the library on the host
Mysql> GRANT REPLICATION SLAVE on . to ' repl ' @ '% ' identified by ' 123456 ';
Query OK, 0 rows Affected (0.00 sec)
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)

4. Backing up data from the main library
If the data already exists on the primary server, you need to back up the data of the main library, import from the library once, and then synchronize with master and slave, otherwise there will be data conflicts.
If it is a newly installed master-slave database, there is no data to write, you can skip this step, directly to configure the master-slave.

4.1
Back up the main library data first
Mysql> set sql_log_bin=0;
[Email protected] ~]#/usr/local/bin/mysqldump-uroot-p123456–socket=/home/mysql/mysql.sock–all-databases– single-transaction–flush-logs–master-data=2 > db.sql.2012-12-05
Mysql> set sql_log_bin=1;

4.2
Copy the backup data to the slave server
[Email protected] ~]# scp/home/work/db.sql.2012-12-05 10.136.51.111:/home/work

To synchronize data from the server:
Mysql> source/home/work/db.sql.2012-12-05

5, configuring from the Library
Head-30 db.sql.2012-12-05
Locate the line that begins with –change MASTER to Master_log, as follows:
–change MASTER to master_log_file= ' mysql-bin.000002 ', master_log_pos=107;
Records the binary files used and the location where the statements are executed. Because you want to start the master-slave synchronization from this position.

Then execute from the library.
mysql> slave stop;
Mysql>
Change Master to
Master_host= ' 10.254.141.23 ',
Master_user= ' Repl ',
Master_password= ' 123456 ',
master_port=3306,
Master_log_file= ' mysql-bin.000007 ',
master_log_pos=107;
mysql> slave start;

Take a look at the status from the library.
Mysql> show Slave status\g;

         Slave_IO_Running: Yes        Slave_SQL_Running: Yes        Seconds_Behind_Master: 0   0显示从服务器比主服务器慢多少秒

Master-Slave synchronization complete.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL Database master-Slave synchronization