MySQL5.6 Master-Slave synchronization installation and configuration (Master/Slave), mysql5.6slave

MySQL5.6 Master-Slave synchronization installation and configuration (Master/Slave), mysql5.6slave

MySQL5.6 Master-Slave synchronization installation and configuration (Master/Slave)
This article mainly introduces the master-slave synchronization installation and configuration of MySQL database, which has some reference value. If you are interested, please take a look.
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://www.bkjia.com/article/101778.htm

Basic Environment Configuration

First, ensure that the firewall is enabled for port 3306. To learn the master-slave configuration of the database, you can use the service iptables stop command to directly disable the firewall.

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

Master Configuration

In Linux, the MySQL configuration file is located in/etc/my. cnf. In this file, specify the Master configuration 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. Go to mysql: [root @ VM_221_4_centos ~] # Press enter from mysql-u root-p 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 the execution of this step is always Empty set (0.00 sec), it means that the previous my. cnf is not configured.

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

It can be seen that the number of detected master-host 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 view the configuration information through mysql> show slave status:

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 execute the Master: show master status as follows:

Correspondingly, You need to modify the Slave database information in my. add replicate-do-db = test to cnf, restart 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. We also hope that you can support the customer's home.