MySQL 5.6 Master-Slave synchronization configuration case sharing
This article environment
Main Library: Redhat 6.5 x64 192.168.1.180 mysql-5.6.35
Standby Library: Redhat 6.5 x64 192.168.1.181 mysql-5.6.35
One, the general configuration method one
1. mysql Master server configuration
Add the following configuration:
# VI/ETC/MY.CNF
[Mysqld]
Log-bin = Master-bin
Log-bin-index = Master-bin.index
Binlog_format = Mixed
Server-id = 1
Restart MySQL after modifying my.cnf
#/etc/init.d/mysqld restart
[Email protected] bin]#/etc/init.d/mysqld restart
Shutting down MySQL. [OK]
Starting MySQL. [OK]
[Email protected] bin]# mysql-uroot-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 1
Server Version:5.6.35-log MySQL Community Server (GPL)
Copyright (c), Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.
Mysql> Show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 | 2290 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)
Mysql>
2. master server Configuration Synchronous replication account
Mysql>grant replication Slave on * * to ' repl ' @ ' percent ' identified by ' 123456 ';
--Refresh Permissions
Mysql>flush privileges;
3. mysql from the server configuration
Description: Default as long as the Server-id is not the same.
--Add the following configuration:
# VI/ETC/MY.CNF
[Mysqld]
Log-bin = Slave-bin
Binlog_format = Mixed
Server-id = 11
Relay-log = Slave-relay-bin
Relay-log-index = Slave-relay-bin.index
Configuration description: Do not sync MySQL library can realize the master-slave library has different account permissions, after testing, mysql5.6.29 only in the configuration from the library is valid.
Other parameters:
Binlog-do-db = MyDB synchronizes only one database
#replicate-ignore-db = mysql ignores MySQL library, this parameter has many unexpected synchronization problems, or is not used.
replicate_wild_ignore_table = mysql.% Ignore MySQL library
The final content from the library my.cnf is as follows:
###
Log-bin = Slave-bin
Binlog_format = Mixed
Server-id = 11
Relay-log = Slave-relay-bin
Relay-log-index = Slave-relay-bin.index
Binlog-do-db = MyDB
Replicate_wild_ignore_table = mysql.%
--After restarting from the library MySQL can be effective
#/etc/init.d/mysqld Restart
[Email protected] ~]#/etc/init.d/mysqld restart
Shutting down MySQL. [OK]
Starting MySQL. [OK]
4. Test examples
CREATE DATABASE MyDB;
CREATE TABLE User (
ID varchar () not NULL,
Username varchar () not NULL,
Password char (+) not NULL,
PRIMARY KEY (ID)
) ;
INSERT into User VALUES (' 1 ', ' koumm ', ' 123456 ');
INSERT into User VALUES (' 2 ', ' Zhangsan ', ' 123456 ');
INSERT into User VALUES (' 3 ', ' Lisi ', ' 123456 ');
INSERT into User VALUES (' 4 ', ' Li2si ', ' 123456 ');
INSERT into User VALUES (' 5 ', ' abc ', ' 123456 ');
INSERT into User VALUES (' 6 ', ' Tom ', ' 123456 ');
INSERT into User VALUES (' 7 ', ' JK ', ' 123456 ');
INSERT into User VALUES (' 8 ', ' xb ', ' 123456 ');
5. Normal master-slave configuration process
(1) Main Library lock table
Mysql> flush tables with read lock;
Query OK, 0 rows Affected (0.00 sec)
Mysql> Show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 | 2290 | | | |
+-------------------+----------+--------------+------------------+-------------------+
Mysql> show master logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 420 |
| master-bin.000002 | 2290 |
+-------------------+-----------+
2 rows in Set (0.00 sec)
(2) Main Library backup
[Email protected] ~]# Mysqldump-uroot-p-B mydb >/mysql/backu/mydb.sql
Description: The-b parameter has a build database statement.
(3) Main library unlocking the lock table function
mysql> unlock tables;
Query OK, 0 rows Affected (0.00 sec)
Mysql>
6. Import the database from the library, enter the password and return
[Email protected] ~]# mysql-uroot-p </mysql/backup/mydb.sql
Enter Password:
7. Configuring the synchronization process from the library
(1) Configure synchronization, manually perform synchronization parameters, the configuration will be written to the Master.info file.
Mysql> Change MASTER to
Master_host= ' 192.168.1.180 ',
master_port=3306,
Master_user= ' Repl ',
-master_password= ' 123456 ',
Master_log_file= ' master-bin.000002 ',
master_log_pos=2290;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
(2) Start the synchronization process
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
Mysql> show Slave status\g;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.1.180
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:master-bin.000002
read_master_log_pos:2290
relay_log_file:slave-relay-bin.000002
relay_log_pos:284
relay_master_log_file:master-bin.000002
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:mysql.%
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:2290
relay_log_space:457
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:1
Master_uuid:7baedb0e-5ea0-11e8-8692-000c292185b2
Master_info_file:/usr/local/mysql/data/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave have 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
#查看如下两个参数为YES, instructions are working correctly from the library.
Slave_io_running:yes
Slave_sql_running:yes
(3) test synchronization situation
# Main Library Inserts a record
Mysql> Use MyDB
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a
Database changed
Mysql> Show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| user |
+----------------+
1 row in Set (0.00 sec)
Mysql> select * from user;
+----+----------+----------+
| ID | Username | password |
+----+----------+----------+
| 1 | Koumm | 123456 |
| 2 | Zhangsan | 123456 |
| 3 | Lisi | 123456 |
| 4 | Li2si | 123456 |
| 5 | ABC | 123456 |
| 6 | Tom | 123456 |
| 7 | JK | 123456 |
| 8 | XB | 123456 |
+----+----------+----------+
8 rows in Set (0.00 sec)
mysql> INSERT into user values (' 9 ', ' Liang ', ' 654321 ');
Query OK, 1 row affected (0.01 sec)
Mysql> commit;
Query OK, 0 rows Affected (0.00 sec)
Mysql> select * from user;
+----+----------+----------+
| ID | Username | password |
+----+----------+----------+
| 1 | Koumm | 123456 |
| 2 | Zhangsan | 123456 |
| 3 | Lisi | 123456 |
| 4 | Li2si | 123456 |
| 5 | ABC | 123456 |
| 6 | Tom | 123456 |
| 7 | JK | 123456 |
| 8 | XB | 123456 |
| 9 | Liang | 654321 |
+----+----------+----------+
9 Rows in Set (0.00 sec)
# Query synchronization from Library
Mysql> Use MyDB
Database changed
Mysql> select * from user;
+----+----------+----------+
| ID | Username | password |
+----+----------+----------+
| 1 | Koumm | 123456 |
| 2 | Zhangsan | 123456 |
| 3 | Lisi | 123456 |
| 4 | Li2si | 123456 |
| 5 | ABC | 123456 |
| 6 | Tom | 123456 |
| 7 | JK | 123456 |
| 8 | XB | 123456 |
| 9 | Liang | 654321 |
+----+----------+----------+
9 Rows in Set (0.00 sec)
Third, master-slave synchronization Simple Management
1. Stop MySQL Sync
STOP SLAVE Io_thread; #停止IO进程
STOP SLAVE Sql_thread; #停止SQL进程
STOP SLAVE; #停止IO和SQL进程
2. Start MySQL Sync
START SLAVE Io_thread; #启动IO进程
START SLAVE Sql_thread; #启动SQL进程
START SLAVE; #启动IO和SQL进程
3. Reset MySQL Sync
RESET SLAVE;
Clears the master-slave synchronization parameter, it deletes the master.info and Relay-log.info files, and all the relay logs, and starts a new relay log.
Applies again from the library once again configured.
4. View MySQL sync status
SHOW SLAVE STATUS;
5. Temporarily skip MySQL sync errors
Make sure that the data is consistent and that the temporary skip this error operation is as follows, possibly multiple times.
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;
# VI/ETC/MY.CNF
[Mysqld]
#slave-skip-errors=1062,1053,1146 #跳过指定error No type error
#slave-skip-errors=all #跳过所有错误
Original reference: Https://www.linuxidc.com/Linux/2016-04/130729.htm
Other relevant information reference:
Implement master-Slave synchronization between two MySQL databases http://www.linuxidc.com/Linux/2016-02/128100.htm
MySQL master-slave synchronization in Linux environment--Add new from library http://www.linuxidc.com/Linux/2015-08/122448.htm
Build MySQL master-slave synchronization http://www.linuxidc.com/Linux/2015-08/121806.htm by Xtrabackup to achieve non-stop lock table
MySQL master-slave synchronization configuration record http://www.linuxidc.com/Linux/2015-07/119939.htm
MySQL Database master-Slave synchronization configuration under Linux http://www.linuxidc.com/Linux/2016-03/129138.htm
MySQL 5.6.35 Master-Slave synchronization configuration case
