A B for two MySQL server, both open binary log, database version MySQL 5.5, server parameters [a server 192.168.1.100]server-id = 1binlog-do-db = Testbinlog-ignore-db = mysqlreplicate-do-db = testreplicate-ignore-db = MySQL Sync-binlog = 1 [B Server 192.168.1.101]server-id = 2binlog-do-db = Te stbinlog-ignore-db = mysqlreplicate-do-db = testreplicate-ignore-db = Mysqlsync-binlog = 1 Restart A, B's database server two, operation steps # A B server stops the same Step Stop SLAVE; # A B server empties 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 (cannot terminate the MySQL process in the Lock table state, otherwise it will fail) FLUSH TABLES with READ lock; (when the database is running on the line, the lock table operation, the data is not written) # If you use SSH, you need to re-open, copy the database file. Tar-cvf/tmp/mysql-data.tar/www/mysqltar-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 B server configuration (modified to host State of 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; # See if the B server synchronization status is normal show SLAVE status;-------------------------------------------master/slave configuration is complete, The following are the main master configurations that are mainly from the------------------------------------------below, and are rarely used and may encounter conflicts. # View B Server host (record binary start file, location) SHOW MASTER status; # Modify A Server configuration (modify host status for B server) change MASTER to master_host= ' 19 2.168.1.101 ', master_user= ' master_slave ', master_password= ' [email protected]# ', master_log_file= ' binlog.000001 ', master_log_pos=106; # open a server synchronization process start slave; # View a B server synchronization status, determine if the show SLAVE status successfully; SHOW MASTER status; # unlock a b server unlock tables; # data test created on a B server, respectivelyTable Insert Data Test drop table IF EXISTS ' test '; CREATE TABLE ' test ' ( ' id ' int (one) not NULL auto_increment, ' value ' varchar () DEFAULT ' 0 ', PRIMARY KE Y (' id ')) engine=myisam auto_increment=2 DEFAULT charset=utf8;insert into ' Test ' VALUES (' 1 ', ' Hello '); Note: 1. The contents of the Master.info in the database directory override some of the options specified in the command line or MY.CNF, and change the configuration to remove Master.info2. The master configuration in MY.CNF will be canceled after MySQL 6.0, and the official recommended dynamic change MASTER3. 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. Mutual Synchronization Configuration example: 1. A B Mutual master from synchronous test, out of sync MySQL: Two database configurations are set: Binlog-do-db=test, Binlog-ignore-db=mysql,replicate-do-db=test, Replicate-ignore-db=mysql 2. A B Mutual master from only synchronous test, do not synchronize other databases, the newly created will not synchronize two database configurations are set: Binlog-do-db=test,replicate-do-db=test 3. A B Mutual is never synchronized MySQL, synchronization of other databases, such as the creation of a new database will also synchronize two database configuration are set: Binlog-ignore-db=mysql,replicate-ignore-db=mysql 4. A B Mutual primary from synchronizing all databases, including the newly created database two database configurations do not set the above four Share to:
MySQL database server master-slave configuration
