MySQL database Master-Slave (Master/Slave) Synchronization installation and configuration details

Install environment OS: CentOS6.5 database version: MySQL5.6.27 host A: 192.168.1.1 (Master) host B: 192.168.1.2 (Slave) The database version emphasized here, the reason is that the installation method before and after MySQL 5.6 is different. I encountered this problem during configuration. Here

Installation environment operating system: CentOS 6.5 database version: MySQL 5.6. 27 host A: 192.168. 1.1 (Master) host B: 192.168. 1.2 (Slave) indicates the database version, because the installation method before and after MySQL 5.6 is different. I encountered this problem during configuration. Here

Installation environment

Operating System: CentOS 6.5 database version: MySQL 5.6.27 host A: 192.168.1.1 (Master) host B: 192.168.1.2 (Slave)

The database version mentioned here is because MySQL is installed differently before and after MySQL 5.6.
I also encountered this problem during configuration. I hope you will not be confused here.

Note: Here is an CentOS installation of MySQL articles, which leads to the process of testing, hope to help everyone: http://blog.csdn.net/xlgen157387/article/details/49964557

Basic Environment Configuration

First, make sure that the firewall is enabled for port 3306. For more information, see [http://blog.csdn.net/xlgen157387/article/details/49964557].service iptables stopCommand to directly disable the firewall.

Then, ping the two machines to ensure that the two machines are the same.

Master Configuration

In Linux, the location of the MySQL configuration file is/etc/my.cnfIn this file, the configuration of the specified Master is as follows:

log-bin=mysql-binserver-id=2binlog-ignore-db=information_schemabinlog-ignore-db=clusterbinlog-ignore-db=mysqlbinlog-do-db=ufind_db 

Here the server-id is used to identify a unique database. Here it is set to 2. When setting the slave database, you need to set it to another value.

Binlog-ignore-db: indicates the ignore database during synchronization.
Binlog-do-db: Specifies the database to be synchronized

The complete configuration is as follows:

1. Restart mysql:service mysqld restart

2. Enter mysql:[root@VM_221_4_centos ~]# mysql -u root -pPress enter and enter the mysql password.

3. Grant the Slave database permission account to allow the user to read logs in the master database, and grant the File Permission to 192.168.1.2, that is, the Server Load balancer machine, but only grant the Server Load balancer machine the File Permission, you must also grant the replication slave permission to it.

In the Master database command line, enter:

 >GRANT FILE ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY 'mysql password'; >GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY 'mysql password';>FLUSH PRIVILEGES

Here, the root user is used for synchronization. You can set it by yourself.

4. Restart mysql and log on to mysql. The master database information is displayed.

mysql> show master status;

mysql> show master status;+------------------+----------+--------------+----------------------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                 | Executed_Gtid_Set |+------------------+----------+--------------+----------------------------------+-------------------+| mysql-bin.000004 |    28125 | ufind_db     | information_schema,cluster,mysql |                   |+------------------+----------+--------------+----------------------------------+-------------------+1 row in set (0.00 sec)mysql> 

Here, File and Position are used when configuring Salve. Binlog_Do_DB indicates the database to be synchronized, and Binlog_Ignore_DB indicates the Ignore database. These are all specified during configuration.

In addition, if this step is alwaysEmpty set(0.00 sec), It indicates that my. cnf is not configured correctly.

Slave Configuration

1. First, modify the configuration file of the slave Database:/etc/my. cnf:

log-bin=mysql-binserver-id=3binlog-ignore-db=information_schemabinlog-ignore-db=clusterbinlog-ignore-db=mysqlreplicate-do-db=ufind_dbreplicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allslave-net-timeout=60

2. We can see that MySQL and later versions are not specified:

Master-host = 192.168.1.1 # Master's host IPmaster-user = rootmaster-password = mysql password # Master's MySQL password

3. This is also the configuration process for many searches on the Internet. They also specify the database version, but it is not said that the new version configuration is not applicable.

4. If the slave database is configured in MySQL5.6 or later, the above content is set, that is, the master-host and master-user information is specified, an error is returned when MySQL is restarted. The error message is as follows:

[root@VM_128_194_centos bin]# service mysqld restartShutting down MySQL... SUCCESS! Starting MySQL... ERROR! The server quit without updating PID file (/data/mysqldb/VM_128_194_centos.pid).[root@VM_128_194_centos bin]# 

In this case, view the database error message (Database directory,/data/mysqldb/VM_128_194_centos.err). You can see:

2016-05-06 13:12:04 13345 [Note] InnoDB: Waiting for purge to start2016-05-06 13:12:04 13345 [Note] InnoDB: 5.6.27 started; log sequence number 28502112016-05-06 13:12:04 13345 [ERROR] /data/home/server/mysql-5.6.27/bin/mysqld: unknown variable 'master-host=192.168.1.1'2016-05-06 13:12:04 13345 [ERROR] Aborting

We can see thatmaster-hostThe number of detected variables is an unknown variable, so an error occurs.

5. configuration in 5.6 and later versions is as follows:

After modifying the/etc/my. cnf file, restart MySQL (service mysqld restart)

Log on to the Slave mysql console and run the following command:

Mysql> stop slave; # disable Slavemysql> change master to master_host = '2017. 168.1.1 ', master_user = 'root', master_password = '000000', master_log_file = 'mysql-bin.000004', master_log_pos = 123456; mysql> start slave; # enable Slave

Specify the Master information here. master_log_file is the File option when configuring the Master. master_log_pos is the Position option for configuring the Master.

Then you can usemysql> show slave status;View the configuration information:

mysql> show slave status \G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.167.1.1                  Master_User: root                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000004          Read_Master_Log_Pos: 28125               Relay_Log_File: VM_128_194_centos-relay-bin.000004                Relay_Log_Pos: 26111        Relay_Master_Log_File: mysql-bin.000004             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB: ufind_db          Replicate_Ignore_DB: mysql           Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 28125              Relay_Log_Space: 26296              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 2                  Master_UUID: 8ac3066a-9680-11e5-a2ec-5254007529fd             Master_Info_File: /data/mysqldb/master.info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it           Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 01 row in set (0.00 sec)ERROR: No query specifiedmysql> 

You can see that the configuration is successful.

Add Slave database Slave to be synchronized

For various reasons, the test database is used during the test. Here I modify the my. in the cnf configuration file, add the synchronized database test, restart MySQL, and run the Master command:show master statusAs follows:

Correspondingly, the Slave database information to be modified is added in my. cnfreplicate-do-db=testRestart Mysql and execute the following content in the Slave database according to the above show master status:

>stop slave>change master to master_host='192.168.1.1',master_user='root',master_password='123456',master_log_file='mysql-bin.000005', master_log_pos=120;>start slave

Then use: show slave status;

mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.1.1                  Master_User: root                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000005          Read_Master_Log_Pos: 1422               Relay_Log_File: VM_128_194_centos-relay-bin.000004                Relay_Log_Pos: 283        Relay_Master_Log_File: mysql-bin.000005             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB: ufind_db,test          Replicate_Ignore_DB: mysql           Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 1422              Relay_Log_Space: 468              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 2                  Master_UUID: 8ac3066a-9680-11e5-a2ec-5254007529fd             Master_Info_File: /data/mysqldb/master.info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it           Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 01 row in set (0.00 sec)ERROR: No query specifiedmysql> 

Test has been added.

Real Testing

Add the database table and user to the master database. Observe the slave database changes as follows:

When creating a database:

When adding data:

When deleting a Master database table:

So far, I hope to help you with the configuration process. If you have any questions, please leave a message.