Shulou(Shulou.com)06/01 Report--

Master-slave synchronous installation and configuration of MySQL5.6 database (Master/Slave)

This article mainly introduces the MySQL5.6 database master-slave synchronous installation and configuration of the detailed explanation, has a certain reference value, interested can understand.

Installation environment

Operating system: CentOS 6.5

Database version: MySQL 5.6.27

Host ARV 192.168.1.1 (Master)

Host BRV 192.168.1.2 (Slave)

The database version emphasized here is due to the fact that MySQL was installed differently before and after 5. 6.

When I was in the configuration, I also encountered this pit. I explained it in advance here. I hope everyone will not dig the pit.

Note: here is an article on installing MySQL in CentOS, which leads to a test of the process. I hope it will be helpful to you: http://www.jb51.net/article/101778.htm

Basic environment configuration

First of all, to ensure that the firewall is open to port 3306, if only to learn the master-slave configuration of the database, you can use the service iptables stop command to turn off the firewall directly.

You can then ping between the two machines to ensure that the two machines are the same.

Configuration of Master

Under the Linux environment, the configuration file of MySQL is located in / etc/my.cnf. Specify the configuration of Master under this file as follows:

Log-bin=mysql-bin

Server-id=2

Binlog-ignore-db=information_schema

Binlog-ignore-db=cluster

Binlog-ignore-db=mysql

Binlog-do-db=ufind_db

The server-id here is used to identify the unique database, which is set to 2, which needs to be set to a different value when setting the slave library.

Binlog-ignore-db: indicates the database of ignore during synchronization

Binlog-do-db: specify the database that needs to be synchronized

The screenshot of the complete configuration is as follows:

1. Then restart mysql:

Service mysqld restart

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

3. Give slave database permission account, allow users to read logs on the master database, give 192.168.1.2, that is, Slave machine has File permission, only give Slave machine File permission, but also give it REPLICATION SLAVE permission.

On 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

The root user is still used here as the user used for synchronization, which can be set by yourself.

4. Restart mysql, log in to mysql, and display the information of the main library

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 represents the database to be synchronized, and Binlog_Ignore_DB represents the database of Ignore. These are specified during configuration.

In addition: if this step is always Empty set (0. 00 sec), then the previous my.cnf is not configured correctly.

Configuration of Slave

1. From the configuration of the library, modify the configuration file first: / etc/my.cnf is as follows:

Log-bin=mysql-bin

Server-id=3

Binlog-ignore-db=information_schema

Binlog-ignore-db=cluster

Binlog-ignore-db=mysql

Replicate-do-db=ufind_db

Replicate-ignore-db=mysql

Log-slave-updates

Slave-skip-errors=all

Slave-net-timeout=60

2. You can see that it is not specified in the version after MySQL5.6:

Host IP of master-host=192.168.1.1 # Master

Master-user=root

MySQL password for master-password=mysql password # Master

3. This is also the configuration process of many searches on the Internet, and they all specify the version of the database, but do not say that the configuration of the new version is not applicable.

4. If you set the content above when configuring the slave library in MySQL5.6 and later versions, that is, if you specify master-host, master-user and other information, an error will be returned when you restart MySQL. The error message is as follows:

[root@VM_128_194_centos bin] # service mysqld restart

Shutting 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] #

At this point, looking at the error message of the database (the 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 start

2016-05-06 13:12:04 13345 [Note] InnoDB: 5.6.27 started; log sequence number 2850211

2016-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

You can see that the number of master-host detected is an unknown variable, so an error occurs.

5. The configurations in 5.6 and subsequent versions are as follows:

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

Go to the Slave mysql console and execute:

Mysql > stop slave; # turn off Slave

Mysql > change master to master_host='192.168.1.1',master_user='root',master_password='123456',master_log_file='mysql-bin.000004', master_log_pos=28125

Mysql > start slave; # enable Slave

Specify the information of Master here. Master_log_file is the File option when configuring Master, and master_log_pos is configuring the Position option of Master, which is to be corresponding here.

You can then 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: 0

Master_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: 0

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql >

As you can see, the configuration has been successful.

Add a slave Slave that needs to be synchronized

For various reasons, the test library is used in the test. Here, I modify the my.cnf configuration file of Master, add a synchronized database test, restart MySQL, and execute Master as follows:

Accordingly, to modify the information of Slave slave library, add replicate-do-db=test in my.cnf, restart Mysql, and execute the following contents in Slave slave library 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: 0

Master_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: 0

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql >

Test has been added.

Real testing.

Add database tables to the main library, user

[this article is reproduced from Java Senior architect, original link: https://mp.weixin.qq.com/s/N7Q7nT5lgPwiT80huthyPw, reprint authorization, please contact the original author]

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.