CentOS7 + mysql5.6 configure Master/Slave, centos7mysql5.6

CentOS7 + mysql5.6 configure Master/Slave, centos7mysql5.6

I. installation environment

OS: CentOS-7-x86_64-DVD-1611.iso database version: mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz database address: 192.168.2.1 (master) 192.168.2.2 (slave)

 

The Installation Method for MySQL before and after MySQL 5.6 is different.

Self-Organized mysql installation, master-slave configuration based on this installation: http://www.cnblogs.com/cypress/p/8608496.html

First, ensure that port 3306 is available or disable the firewall. The two machines can ping each other.

Ii. Master Configuration

1. Modify the MySQL configuration file

[root@localhost ~]# vim /etc/my.cnf 

File Content

 

[Mysqld] # enable the binary log-bin = mysql-bin # identify the unique id (required). Generally, the last ip address server-id = 2 # the database is not synchronized, you can set multiple binlog-ignore-db = information_schemabinlog-ignore-db = clusterbinlog-ignore-db = mysql # specify the database to be synchronized (matching with slave ), you can set multiple binlog-do-db = test

 

　　

 

2. Restart MySQL

service mysqld restart

 

3. Enter mysql settings to allow obtaining logs from the master database. Note: the root user configuration is used here.

[root@localhost ~]# mysql -u root -p

 

# GRANT permissions TO the slave database mysql> grant file on *. * TO 'root' @ '192. 168.2.2 'identified BY 'root password ';
Mysql> grant replication slave on *. * TO 'root' @ '192. 168.2.2 'identified BY 'root password ';
Mysql> flush privileges;

 

4. Restart MySQL, log on to MySQL, and view the master database information.

mysql> show master status;

Display content

+------------------+----------+--------------+----------------------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                 | Executed_Gtid_Set |+------------------+----------+--------------+----------------------------------+-------------------+| mysql-bin.000006 |    120 | ufind_db     | information_schema,cluster,mysql |                   |+------------------+----------+--------------+----------------------------------+-------------------+1 row in set (0.00 sec)mysql> 

Note: If this step is alwaysEmpty set(0.00 sec), It indicates that my. cnf is not configured

 

Iii. Slave Configuration

1. Slave Database Configuration

 

# Enable binary log (optional)
Log-bin = mysql-binserver-id = 3binlog-ignore-db = information_schemabinlog-ignore-db = clusterbinlog-ignore-db = mysql
# Always replicate-do-db = testreplicate-ignore-db = mysqllog-slave-updatesslave-skip-errors = allslave-net-timeout = 60 with the master database configuration

 

4. Restart MySQL and log on to MySQL.

# Disable Slavemysql> stop slave;
# Set the connection master database information mysql> change master to master_host = '2017. 168.2.1 ', master_user = 'root', master_password = 'root password', master_log_file = 'mysql-bin.000006', master_log_pos = 120; # enable Slavemysql> start slave;

Note: The above master_log_file is the File Field When configuring the Master, and master_log_pos is the Position Field When configuring the Master. One-to-one correspondence

 

5. View information

mysql> show slave status \G;

　　

* *************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.1 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 120 Relay_Log_File: localhost-relay-bin.000006 Relay_Log_Pos: 520 rows: mysql-bin.000006 rows: Yes // display yes for success Slave_ SQL _Running: yes // If yes is displayed, it indicates that the master Replicate_Do_DB: test Replicate_Ignore_DB: mysql is not started.
// The above information is in the configuration file: Response: Last_Errno: 0 Last_Error: Skip_Counter: 0 response: 357 Relay_Log_Space: 697 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 priority: No priority: Priority: Master_SSL_Cert: Priority: Master_SSL_Key: Seconds_Behind_Master: Priority: No priority: 0 Last_IO_Error: // If no, the following error message is displayed: 0 Last_ SQL _Error: replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: be0a41c0-2b40-11e8-b791-000c29267b6a Master_Info_File:/usr/local/mysql/data/master.info SQL _Delay: 0 SQL _Remaining_Delay: NULL Slave: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Usage: Master_SSL_Crl: Master_SSL_Crlpath: Usage: Executed_Gtid_Set: Auto_Position: 01 row in set (0.00 sec) ERROR: no query specified

NOTE: If Slave_IO_Running: No, the following error occurs:

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

The UUID of the master server is the same as that of the slave server.

[Root @ localhost ~] # Vim/usr/local/mysql/data/auto. cnf # This is the server-uuid for modifying auto. cnf in my installation path

　　The above master-slave MySQL is ready for use. You are welcome to raise more bugs.

Part of the content, the test method can refer to reprint address:Http://blog.csdn.net/u010870518/article/details/51331244