MySQL5.5 master-slave synchronization configuration and Problems

I installed some articles on the Internet to configure the MySQL master-slave synchronization mechanism, but encountered an exception when I restarted from MySQL. I do not know the master_hostusrsbinmysqld: unknownvariablemaster_host10.0.2.160. I used to use MySQL5.5, most of the configurations are based on versions earlier than Mysql 5.5.

Install some articles on the Internet to configure the master-slave synchronization mechanism of MySQL. However, an exception occurs when you restart MySQL, saying that you do not know the master_host/usr/sbin/mysqld parameter: unknown variable master_host = 10.0.2.160 I used MySQL5.5, and most of the configurations are based on versions earlier than 5.5. Mysql versions do not start from 5.1.7.

Install some articles on the Internet to configure the master-slave synchronization mechanism of MySQL. However, an exception occurs when you restart MySQL, saying that you do not know the master_host parameter.

/Usr/sbin/mysqld: unknown variable 'master _ host = 10.0.2.160 ′

I used MySQL5.5, and most of the configurations were based on versions earlier than 5.5. Mysql versions do not support parameters similar to "master-host" since 5.1.7. View http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html for details

Configure master-slave synchronization for MySQL Replication

A B is two MySQL servers, both of which enable binary logs. The database version is MySQL 5.5.

Edit the configuration file on the two servers. Add the following configuration to [mysqld]

I. MySQL database migration
First, if you want to synchronize the existing MySQL DATA, we 'd better first synchronize the data to the slave database through full synchronization.
For the synchronization method, refer:
1.
2. database migration under MySQL
Ii. Server Configuration

[A master server 10.0.2.160]

server-id = 1binlog-ignore-db = mysqlbinlog-ignore-db =?information_schemasync-binlog = 1

[B slave server 10.0.2.151]

server-id = 2replicate-ignore-db = mysqlreplicate-ignore-db =?information_schema

Restart the MySQL service.

Use commands?SHOW MASTER STATUSView the status of the primary service:

mysql> SHOW MASTER STATUS;+---------------------+----------+--------------+--------------------------+| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB         |+---------------------+----------+--------------+--------------------------+| mysql-binlog.000001 | 525      |              | mysql,information_schema |+---------------------+----------+--------------+--------------------------+1 row in set (0.00 sec)

Master server A authorized synchronization account:

GRANT REPLICATION SLAVE ON *.* TO 'root'@'10.0.2.151' IDENTIFIED BY 'password';FLUSH PRIVILEGES;

Slave? Start to enable replication:

CHANGE MASTER TO MASTER_HOST='10.0.2.160',MASTER_USER='root', MASTER_PASSWORD='password',MASTER_LOG_FILE='?mysql-binlog.000001',MASTER_LOG_POS=106;

Stop slave synchronization: mysql>slave?start

Stop slave synchronization: mysql> slave stop

Use show slave status on the slave server to view the status of slave Synchronization

Mysql master-slave Synchronization

Slave_IO_Running: Yes
Slave_ SQL _Running: Yes

If yes, the synchronization is successful.

Unfortunately, in my first experiment, all the above parameters are "No ".

Solution: the master and slave synchronization locations of MySQL are inconsistent.
1. Stop the Slave service first:

mysql> slave stop

2. Check the host status on the master server:
Record the value corresponding to File and Position.

mysql> SHOW MASTER STATUS;+---------------------+----------+--------------+--------------------------+| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB         |+---------------------+----------+--------------+--------------------------+| mysql-binlog.000002 |      958 |              | mysql,information_schema |+---------------------+----------+--------------+--------------------------+1 row in set (0.00 sec)

3. Execute manual synchronization on the slave server:

CHANGE MASTER TO MASTER_HOST='10.0.2.160',MASTER_USER='root', MASTER_PASSWORD='password',MASTER_LOG_FILE='?mysql-binlog.000002',MASTER_LOG_POS=958;

Start slave synchronization: slave start

View the slave status again and find:

Slave_IO_Running: YesSlave_SQL_Running: Yes

If yes, the synchronization is successful.

We know that the data replication mechanism is completely based on the incremental SQL statements executed on the master to be propagated to the secondary server and run successfully. This requires:Before running this mechanism, the data in the primary and secondary databases is consistent. When running this mechanism, the secondary database prohibits write operations from other SQL statements (non-master-distributed SQL statements.However, inconsistency may still occur during running, which may cause the communication to fail. Therefore, if a master-slave problem occurs, you must first solve the synchronization location problem and repair the lost data.

MySQL replication configuration dual-host mutual master-slave

Edit the configuration file on the two servers. Add the following configuration to [mysqld]

I. Server Parameters

[Server A 192.168.1.100]

server-id = 1binlog-do-db = testbinlog-ignore-db = mysqlreplicate-do-db = testreplicate-ignore-db = mysqlsync-binlog = 1

[Server B 192.168.1.101]

server-id = 2binlog-do-db = testbinlog-ignore-db = mysqlreplicate-do-db = testreplicate-ignore-db = mysqlsync-binlog = 1

Ii. Procedure

# Server a B stops Synchronization

STOP SLAVE;

# Server a B clears MASTER logs

RESET 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 'master_slave123!@#';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;

# If you are using SSH, You need to restart and copy the database files.

tar -cvf /tmp/mysql-data.tar /www/mysqltar -xvf /tmp/mysql-data.tar -C /

# View the status of server A host (recording the binary start file and location)

SHOW MASTER STATUS;

# Server B lock table (the mysql process cannot be terminated in the lock table state, otherwise it will fail)

FLUSH TABLES WITH READ LOCK;

# Modify server B configuration (to the host status of server)

CHANGE MASTER TO MASTER_HOST='192.168.1.100',MASTER_USER='master_slave', MASTER_PASSWORD='master_slave123!@#',MASTER_LOG_FILE='binlog.000001',MASTER_LOG_POS=106;

# Enable server B synchronization process

START SLAVE;

# Check whether the synchronization status of server B is normal

SHOW SLAVE STATUS;

# View server B Host (record the binary start file and location)

SHOW MASTER STATUS;

# Modify the configuration of server A to the host status of server B)

CHANGE MASTER TO MASTER_HOST='192.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 separately and determine whether the synchronization is successful.

SHOW SLAVE STATUS;SHOW MASTER STATUS;

# Unlock server A B

UNLOCK TABLES;

# Data test: Create A table on server a B to insert data test

DROP TABLE IF EXISTS `test`;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 master.info content in the database directory will overwrite some of the options specified in the command line or my. cnf. To change the configuration, You need to delete master.info.

2. The master configuration in my. cnf will be canceled after MySQL 6.0. We recommend using dynamic change master.

3. If only ignore-db is specified, do-db is not specified. The newly created database is also synchronized.

Mutual synchronization configuration instances:

1. a B synchronizes the master and slave databases to each other and does not synchronize mysql:

Binlog-do-db = test, binlog-ignore-db = mysql, 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. Newly created databases are not synchronized.

Binlog-do-db = test, replicate-do-db = test

3. Master A and Master B never synchronize mysql and other databases. For example, the new database will also be synchronized.

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 preceding four items are not set in both database configurations.

Refer:
Official Website: http://dev.mysql.com/doc/refman/5.5/en/replication-howto-mysqldump.html
Version configuration earlier than 5.17: http://www.drupal001.com/2012/03/mysql-master-slave-troubles/
MySQL replication configuration dual-host mutual master-slave: http://www.ncq8.com/2010/10/250.html

Original article address: MySQL5.5 master-slave synchronization configuration and problems. Thank you for sharing it with me.