MySQL Master/Slave Database Synchronization

Master Database: 192.168.40.4 DB: testsyn table: Users
Slave Database: 192.168.40.5

1. Configure the master database my. CNF

1. [Mysqld]
2. Log-bin = mysql-testsyn-bin -- prefix of the binary log file name in the master database
3. BINLOG-Do-DB = testsyn -- database to be synchronized
4. Server-id = 1 -- serverid and slave database are different.

[Mysqld] Log-bin = mysql-testsyn-bin -- prefix of the binary log file name of the master database BINLOG-do-DB = testsyn -- Database Server-id = 1 -- serverid and auxiliary different Libraries

Start MySQL

Create user testsyn. Have replication slave permission

1. Grant replication slave on *. * to "testsyn" @ "192.168.40. %" identified by 'testsyn 'with grant option;

grant replication slave on *.* to "testsyn"@"192.168.40.%" identified by 'testsyn' with grant option;

View the status of the master database:

1. Show Master status;

show master status;

2. Configure the auxiliary library my. CNF

1. Server-id = 2
2. Master-host = 192.168.40.4
3. Master-user = testsyn
4. Master-Password = testsyn
5. Master-Port = 3306
6. Master-connect-retry = 5 -- Retry Interval
7. Replicate-Do-DB = testsyn -- synchronize DB
8. # Replicate-wild-Do-Table = USER %. %
9. # Replicate-Do-DB = itemcontent
10. # Replicate-Do-DB = srcstore

Server-id = 2master-host = 192.168.40.4master-user = testsyn master-Password = testsynmaster-Port = 3306master-connect-retry = 5 -- Retry Interval replicate-do-DB = testsyn -- synchronize dB # replicate-wild -Do-table = USER %. % # replicate-do-DB = itemcontent # replicate-do-DB = srcstore

Start MySQL

View the slave database status:

1. Show slave status;

 show slave status;

Note:
Slave_io_running: connects to the master database and reads logs from the master database to the local computer to generate local log files.
Slave_ SQL _running: reads the local log file and executes the SQL statement in the log

3. Dump data in the master database to the slave database.

Master Database: 192.168.40.4:

1. Mysqldump -- Opt -- master-data-uspider-pcrawler testsyn>/home/mahaibo/Dump-testsyn &

mysqldump --opt --master-data -uspider -pcrawler testsyn > /home/mahaibo/dump-testsyn &

Note: -- master-Data

Slave Database: 192.168.40.5
Create Database testsyn first;

Import:

1. Stop slave
3. Mysql-uspider-pcrawler testsyn
6. Start slave;

stop slavemysql -uspider -pcrawler testsyn < /home/mahaibo/dump-testsyn start slave;

You must stop slave before importing

4. Insert a data entry in the users table on the master database 192.168.40.4:
Insert into users (username, password) values ('test1', 'test1 ');

The users table on the secondary database also has data.

5. view the binary log file of the master database:
Show BINLOG events;

Note:
1. The primary and secondary databases are synchronized mainly through binary logs.
2. When starting the secondary database, you must synchronize the data and delete the master.info file in the log directory. Because master.info records the information of the master database to be connected last time. If it is not deleted, it does not work even if it is modified in my. CNF. Because the read information is still in the master.info file.

 

Reference: http://www.javaeye.com/topic/171485