MySQL database server master-slave configuration

A B is two unitsMySQLServer, both binary logs are turned on,DatabaseVersion MySQL 5.5
one, server parameters
[A Server 192.168.1.100]
Server-id = 1
BINLOG-DO-DB = Test
binlog-ignore-db = MySQL
REPLICATE-DO-DB = Test
replicate-ignore-db = MySQL
Sync-binlog = 1
[B Server 192.168.1.101]
Server-id = 2
BINLOG-DO-DB = Test
binlog-ignore-db = MySQL
REPLICATE-DO-DB = Test
replicate-ignore-db = MySQL
Sync-binlog = 1
Restart A, B's database server
Second, the operation procedure
# A B Server stops syncing
STOP SLAVE;
# A B Server empties the master log
RESET MASTER;
# A B server empties slave log
RESET SLAVE;
# A Server Authorized Sync Account (we will synchronize the database files once, so authorization is 192.168.1%)
GRANT REPLICATION SLAVE on * * to ' master_slave ' @ ' 192.168.1% ' identified by ' 123456 ';
FLUSH privileges;
# A Server Lock table (the MySQL process cannot be terminated in the lock table state, otherwise it will fail)
FLUSH TABLES with READ lock; (lock table operation when database is running on line, data not written)
# If you use SSH, you need to re-open and copy the database file.
Tar-cvf/tmp/mysql-data.tar/www/mysql
TAR-XVF/TMP/MYSQL-DATA.TAR-C/
# view A Server host status (record binary start file, location)
SHOW MASTER STATUS;

# B Server Lock table (the MySQL process cannot be terminated in the lock table state, otherwise it will fail)
FLUSH TABLES with READ LOCK;
# Modify the B-server configuration (modify the host State to a server)
Change MASTER to master_host= ' 192.168.1.100 ', master_user= ' master_slave ', master_password= ' 123456 ', master_log_file= ' binlog.000001 ', master_log_pos=106;
Master_log_file corresponds to the file;master_log_pos in the a server Show master status corresponding to the position in the a server Show master status

If all of the two parameters indicated by the arrows are yes, the synchronization is successful.
# Turn on the B server synchronization process
START SLAVE;
# view B Server synchronization status is normal
SHOW SLAVE STATUS;

-------------------------------------------
Master-Slave configuration is complete, the following are the main from
------------------------------------------
The following are primary master configurations, which are rarely used and may encounter conflicts.
# View B server host (record binary start file, location)
SHOW MASTER STATUS;
# Modify the A server configuration (modified to host State of B server)
Change MASTER to master_host= ' 192.168.1.101 ', master_user= ' master_slave ', master_password= ' [email protected]# ', Master_log_file= ' binlog.000001 ', master_log_pos=106;
# Turn on the A server synchronization process
START SLAVE;
# View A B Server synchronization status to determine if it is successful
SHOW SLAVE STATUS; SHOW MASTER STATUS;
# Unlock A B Server
UNLOCK TABLES;
# Data test to create TABLE insert data test on a B server, respectively
DROP TABLE IF EXISTS ' test ';
CREATE TABLE ' Test ' (
' id ' int (one) not NULL auto_increment,
' Value ' varchar (+) DEFAULT ' 0 ',
PRIMARY KEY (' id ')
) Engine=myisam auto_increment=2 DEFAULT Charset=utf8;
INSERT into ' Test ' VALUES (' 1 ', ' Hello ');
Attention:
1. The contents of the Master.info in the database directory will override some of the options specified in the command line or MY.CNF, and change the configuration to remove the Master.info
2. The master configuration in MY.CNF will be canceled after MySQL 6.0, and the official recommendation is to use the dynamic change master
3. If you specify only ignore-db and do not specify DO-DB. The newly created database will also be synchronized.
4. Be sure to stop the firewall of the primary database server, or the primary server cannot be accessed remotely from the server.
Configure instances for each other synchronously:
1. A B Mutual master from synchronous test, unsynchronized MySQL:
Set in two database configurations: Binlog-do-db=test, Binlog-ignore-db=mysql,replicate-do-db=test,replicate-ignore-db=mysql
2. A B Mutual master from only sync test, do not synchronize other databases, the newly created will not be synchronized
Set in two database configurations: Binlog-do-db=test,replicate-do-db=test
3. A B Mutual Master never synchronizes MySQL, synchronization of other databases, such as the creation of a new database will also be synchronized
Set in two database configurations: Binlog-ignore-db=mysql,replicate-ignore-db=mysql
4. A B Mutual master from synchronizing all databases, including the newly created database
These four items are not set in two database configurations

MySQL database server master-slave configuration