MySQL5.6 Database Master-Slave (Master/slave) synchronous installation and configuration detailed

Installation Environment

6.55.6.27主机A：192.168.1.1 （Master）主机B：192.168.1.2 （Slave）

The version of the database highlighted here is because MySQL is installed differently before and after 5.6.
I was in the configuration of the time, also encountered this pit, here in advance, I hope you do not take the pit.

Note: Here is a CentOS installation of MySQL article, lead here, the process of pro-test, we hope to help: http://blog.csdn.net/xlgen157387/article/details/49964557

Basic Environment Configuration

First, to ensure that the firewall on the 3306 port Open, (open mode, please refer to: [http://blog.csdn.net/xlgen157387/article/details/49964557]), if only to learn the master and slave configuration of the database, You can use service iptables stop commands to turn off the firewall directly.

You can then ping between the two machines to make sure that the same can be done between the two computers.

Configuration of Master

In the Linux environment, the location of the configuration file for MySQL is /etc/my.cnf , under which the configuration of master is specified as follows:

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

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

BINLOG-IGNORE-DB: Indicates the Ignore database when synchronizing
BINLOG-DO-DB: Specify the database that needs to be synchronized

The complete configuration is as follows:

1. Then restart MySQL:service mysqld restart

2, enter the MySQL: Enter [[email protected]_221_4_centos ~]# mysql -u root -p the MySQL password entered.

3, give the right account from the library, allow users to read the log on the main library, give 192.168.1.2 that is slave machine has file permissions, only give slave machine has file permissions also not, but also to give it replication slave permissions can.

In the master database command line, enter:

ONTO‘root‘@‘192.168.1.2‘BY‘mysql password‘ONTO‘root‘@‘192.168.1.2‘BY‘mysql password‘;>FLUSH PRIVILEGES

This is still used by the root user as a synchronization user, you can set your own.

4. Restart MySQL, log in to MySQL, display the main library information

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

Here the File, Position is to be used when configuring Salve, binlog_do_db represents the database to be synchronized, binlog_ignore_db represents the Ignore of the database, these are configured at the time of the specified.

In addition: If you perform this step always for Empty set(0.00 sec) , that indicates that the front my.cnf is not configured to.

Configuration of the Slave

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

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

2, it can be seen here, in the version after MySQL5.6 is not specified:

master-host=192.168.1.1#Master的主机IPmaster-user=rootmaster-password=#Master的MySQL密码

3, this is also a lot of online search configuration process, they also specify the version of the database, but did not say that the new version of the configuration this way is not applicable.

4, if you in the MySQL5.6 and later version of the configuration from the library, set to the top of the content, that is, specify the Master-host, Master-user and other information, restart MySQL when the return of the wrong, error information 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]# 

At this point, view the database error information (database directory,/data/mysqldb/vm_128_194_centos.err), you can see:

 .- to- .  -: A:Geneva 13345[Note] Innodb:waiting forPurge toStart .- to- .  -: A:Geneva 13345[Note] InnoDB:5.6.StartedLogSequence Number 2850211 .- to- .  -: A:Geneva 13345[ERROR]/data/home/server/mysql-5.6./bin/mysqld:unknownvariable ' master-host=192.168.1.1 ' .- to- .  -: A:Geneva 13345[ERROR] Aborting

It can be seen that the master-host detected number is an unknown variable and therefore an error occurs.

5. The configuration in 5.6 and subsequent versions is as follows:

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

Go to slave MySQL console and execute:

stop slave;  #关闭Slavemysql> 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;  #开启Slave

To specify the master information here, Master_log_file is the file option when configuring Master, Master_log_pos is the position option for configuring Master, which corresponds.

You can then mysql> show slave status; view the configured information by:

Mysql>Show slave status\G*************************** 1.Row***************************Slave_io_state:waiting for Master toSend Event Master_host:192.167. 1. 1Master_user:root Master_port:3306Connect_retry: -Master_log_file:mysql-bin. 000004Read_master_log_pos:28125Relay_log_file:vm_128_194_centos-relay-bin. 000004Relay_log_pos:26111Relay_master_log_file:mysql-bin. 000004Slave_io_running:yes Slave_sql_running:yes replicate_do_db:ufind_db replicate_ignore_d B:mysql Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Igno Re_table:last_errno:0Last_error:skip_counter:0Exec_master_log_pos:28125Relay_log_space:26296Until_condition:NoneUntil_log_file:until_log_pos:0Master_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:0Last_io_error:last_sql_errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:2Master_uuid:8ac3066a-9680-11e5-a2ec-5254007529FD Master_info_file:/Data/mysqldb/master.Info sql_delay:0Sql_remaining_delay:NULLSlave_sql_running_state:slave has read AllRelayLog; Waiting for the slave I/OThread  toUpdate it master_retry_count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Maste R_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position:01Rowinch Set(0.00SEC) Error:no Query Specifiedmysql> 

As you can see, it has been configured successfully.

Add slave from library that need to be synchronized

For a variety of reasons, when testing the test library, here I follow the above way, modify Master's my.cnf configuration file, add a synchronized database test, restart MySQL, do master: the show master status following:

Accordingly, to modify slave information from the library in my.cnf, restart replicate-do-db=test MySQL, according to the show master status above, perform the following from the library in Slave:

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 toSend Event Master_host:192.168. 1. 1Master_user:root Master_port:3306Connect_retry: -Master_log_file:mysql-bin. 000005Read_master_log_pos:1422Relay_log_file:vm_128_194_centos-relay-bin. 000004Relay_log_pos:283Relay_master_log_file:mysql-bin. 000005Slave_io_running:yes Slave_sql_running:yes replicate_do_db:ufind_db,test replicate_ign Ore_db:mysql Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild _ignore_table:last_errno:0Last_error:skip_counter:0Exec_master_log_pos:1422Relay_log_space:468Until_condition:NoneUntil_log_file:until_log_pos:0Master_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:0Last_io_error:last_sql_errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:2Master_uuid:8ac3066a-9680-11e5-a2ec-5254007529FD Master_info_file:/Data/mysqldb/master.Info sql_delay:0Sql_remaining_delay:NULLSlave_sql_running_state:slave has read AllRelayLog; Waiting for the slave I/OThread  toUpdate it master_retry_count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Maste R_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position:01Rowinch Set(0.00SEC) Error:no Query Specifiedmysql> 

A new test has been added.

The real test

To add a database table to the main library, user, observe the changes from the library as follows:

When creating a database:

When new data is added:

When you delete the master database table:

Configuration process, so far, hope to help you, if you have questions welcome message.

MySQL5.6 Database Master-Slave (Master/slave) synchronization installation and configuration detailed