MySQL database server master-slave configuration, mysql master-slave Configuration

MySQL database server master-slave configuration, mysql master-slave Configuration
A B is two MySQL servers with binary logs enabled, database Version MySQL 5.5 I. 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 = testbinlog-ignore-db = mysqlreplicate-do-db = testreplicate-ignore-db = mysqlsync-binlog = 1 restart, b's Database Server 2. Operation Procedure # a B's server stops synchronizing STOP SLAVE; # server a B clears the MASTER log RES Et master; # server a B clears the SLAVE log RESET SLAVE; # server A authorizes the synchronization account (We will synchronize the database file once, so the authorization is 192.168.1%) GRANT REPLICATION SLAVE ON *. * TO 'master _ slave '@' 192. 168.1% 'identified BY '123456'; flush privileges; # server a lock table (the mysql process cannot be terminated in the LOCK table state; otherwise, it will fail) flush tables with read lock; (When a database is running online, the table is locked and data is not written.) # If you use SSH, You need to enable it again to copy the database files. Tar-cvf/tmp/mysql-data.tar/www/mysqltar-xvf/tmp/mysql-data.tar-C/# view server A host STATUS (record binary start file, location) show master status;

# Server B lock table (mysql process cannot be terminated in the LOCK table state, otherwise it will fail) flush tables with read lock; # modify server B configuration (change to the host State of server) change master to MASTER_HOST = '2017. 168.1.100 ', MASTER_USER = 'master _ slave', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'binlog. 000001 ', MASTER_LOG_POS = 106; MASTER_LOG_FILE corresponds to the File in the show master status of server A; If MASTER_LOG_POS corresponds to the two parameters indicated by the Position arrow in the show master status of server A, if yes, the synchronization is successful. # Enable server B synchronization process start slave; # Check whether the synchronization STATUS of server B is normal SHOW SLAVE STATUS; SLAVE master-SLAVE configuration is complete. The following is the master-SLAVE configuration under, it is rarely used and may encounter conflicts. # View the server B Host (record the binary start file, location) show master status; # modify the configuration of server A (change to the host STATUS of server B) CHANGE MASTER TO MASTER_HOST = '20180101. 168.1.101 ', MASTER_USER = 'master _ slave', MASTER_PASSWORD = 'master _ slave123! @ # ', MASTER_LOG_FILE = 'binlog. 000001 ', MASTER_LOG_POS = 106; # enable server A synchronization process start slave; # Check the synchronization STATUS of server a B, and determine whether the SHOW SLAVE STATUS and SHOW MASTER STATUS are successful; # UNLOCK the unlock tables of server a B; # create A TABLE on server A to insert data to test the DROP TABLE IF EXISTS 'test' operation '; create table 'test' ('id' int (11) not null AUTO_INCREMENT, 'value' varchar (100) DEFAULT '0', primary key ('id ')) ENGINE = MyISAM AUTO_INCREMENT = 2 default charset = utf8; INSERT INTO 'test' VALUES ('1', 'Hello'); Note: 1. the contents of master.info in the database directory will overwrite the command line or my. some of the options specified in cnf must be deleted to change the configuration. info2. my. the master configuration in cnf will be canceled after MySQL 6.0. It is recommended that you use dynamic CHANGE MASTER3. if you only specify ignore-db, do-db is not specified. The newly created database is also synchronized. 4. stop the firewall of the master database server. Otherwise, the slave server cannot remotely access the master database server. Synchronization configuration instances: 1. a B is used for master-slave synchronization with each other. If mysql is not synchronized, binlog-do-db = test and binlog-ignore-db = mysql are set in both database configurations, replicate-do-db = test, replicate-ignore-db = mysql 2. a B synchronizes the master and slave databases only to test, and does not synchronize other databases. The newly created database is not synchronized. Both database configurations are set to binlog-do-db = test, replicate-do-db = test 3. A and B are mutually dominated by never synchronizing mysql, and synchronizing other databases. For example, the newly created database will also be synchronized with both database configurations with binlog-ignore-db = mysql, replicate-ignore-db = mysql 4. a B synchronizes the master and slave databases to each other, including the newly created database. The above four items are not set in both database configurations: