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

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.

Host A:192.168. 0.179 (Master)

Host B:192.168. 0.143 (Slave)

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=1binlog-ignore-db=information_schemabinlog-ignore-db=clusterbinlog-ignore-db=mysqlbinlog-do-db=testdb 

The Server-id here is used to identify a unique database, which is set to 1.

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

1. Then restart MySQL:service mysqld restart

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

3, given from the library rights account, allow users to read the log on the main library, give 192.168.0.143 that is slave machine has file permissions, give slave machine replication slave permissions.

In the master database command line, enter:

> GRANT REPLICATION SLAVE on * * to ' syncuser ' @' 192.168.0.143 ' identified by ' 123456 ';

> FLUSH Privileges

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

Mysql> Show master status;

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.

Configuration of the Slave

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

server_id = 2
Log_bin = Mysql-bin

Replicate-do-db = TestDB

Log-slave-updates
Slave-skip-errors = All
Slave-net-timeout = 60

not specified in the version after MySQL5.6,

master-host=192.168.0.179 #Master的主机IP
Master-user=syncuser
master-password=123456 #Master的MySQL密码

After mysql5.6 these configurations are configured on the MySQL command line. Restart MySQL below to enter MySQL:

mysql> stop Slave;
mysql> Change Master to master_host= ' 192.168.0.179 ', master_user= ' syncuser ', master_password= ' 123456 ', Master_log_ File= ' mysql-bin.000001 ', master_log_pos=1686;

mysql> start slave;

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

Slave_io_running:yes
Slave_sql_running:yes

These two indicate a normal synchronization

Configuration process, so far, for later use, but also convenient for everyone to check.

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