MySQL 5.6 master-slave synchronization configuration case

MySQL 5.6 master-slave synchronization configuration case

MySQL 5.6 master-slave synchronization configuration case sharing, I hope to help you.

Environment
Master Database: CentOS6.5 x64 192.168.0.65 mysql-5.6.29
Slave Database: CentOS6.5 x64 192.168.0.66 mysql-5.6.29

I. Conventional configuration method 1

1. mysql master server configuration

# Vi/etc/my. cnf

[Mysqld]
Log-bin = master-bin
Log-bin-index = master-bin.index
Binlog_format = mixed
Server-id = 1

# Service mysqld restart

Mysql> show master status;
+ ------------------- + ---------- + -------------- + -------------------- + ------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ ------------------- + ---------- + -------------- + -------------------- + ------------------- +
| Master-bin.000001 | 353 |
+ ------------------- + ---------- + -------------- + -------------------- + ------------------- +
1 row in set (0.00 sec)
Mysql>

2. configure a synchronization replication account on the master server

Grant replication slave on *. * to 'repl' @ '%' identified by '123 ';
Flush privileges;

3. mysql slave server configuration

Note: by default, server-IDs are not the same.

# Vi/etc/my. cnf

[Mysqld]
Log-bin = mysql-bin
Binlog_format = mixed
Server-id = 11
Relay-log = slave-relay-bin
Relay-log-index = slave-relay-bin.index

Configuration Description: If the mysql database is not synchronized, the master database and slave database can have different account permissions. After testing, the configuration of mysql5.6.29 is only valid in the slave database.

Other parameters:
Binlog-do-db = mydb only synchronizes one database
# Replicate-ignore-db = mysql ignores the mysql database. This parameter causes many unexpected synchronization problems and is still not used.
Replicate_wild_ignore_table = mysql. % ignore mysql database

# Service mysqld restart

4. test example

Create database 'mydb ';

Create table 'user '(
'Id' varchar (20) not null,
'Username' varchar (20) not null,
'Password' char (32) not null,
Primary key ('id ')
);

Insert into user VALUES ('1', 'koumm', '123 ');
Insert into user VALUES ('2', 'hangsan ', '123 ');
Insert into user VALUES ('3', 'lisi', '20140901 ');
Insert into user VALUES ('4', 'lib2si', '123 ');
Insert into user VALUES ('5', 'abc', '123 ');
Insert into user VALUES ('6', 'Tom ', '123 ');
Insert into user VALUES ('7', 'jk ', '123 ');
Insert into user VALUES ('8', 'xb ', '123 ');

5. Normal Master/Slave configuration process (1) master database 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.000001 | 353 |
+ ------------------- + ---------- + -------------- + -------------------- + ------------------- +
1 row in set (0.00 sec)

Mysql> show master logs;
+ ------------------- + ----------- +
| Log_name | File_size |
+ ------------------- + ----------- +
| Master-bin.000001 | 353 |
+ ------------------- + ----------- +
1 row in set (0.00 sec)

Mysql>

(2) master database backup

[Root @ master ~] # Mysqldump-uroot-p-B mydb> mydb. SQL
Note: The-B parameter has a database creation statement.

(3) unlock the lock table of the master database

Mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Mysql>

6. Import the database from the database

# Mysql-uroot-padmin <mydb. SQL

7. Configure synchronization from the database (1) Configure synchronization and manually execute synchronization parameters. This configuration will be written to the master.info file.

Mysql>

CHANGE MASTER
MASTER_HOST = '1970. 168.0.65 ',
MASTER_PORT = 3306,
MASTER_USER = 'repl ',
MASTER_PASSWORD = '000000 ',
MASTER_LOG_FILE = 'master-bin.000001 ',
MASTER_LOG_POS = 353;

(2) Start the slave 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.0.65
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 353
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: master-bin.000001
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:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 353
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: c8bb22a1-024e-11e6-a1e8-000c29225fa0
Master_Info_File:/usr/local/mysql/data/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>

# Check that the following two parameters are YES, indicating that the slave database runs normally.
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes

(3) test Synchronization

# Insert a record to the master database

Mysql> use mydb;
Database changed
Mysql> select * from stu;
+ ---- + ---------- +
| Id | username | password |
+ ---- + ---------- +
| 1 | koumm | 123456 |
| 2 | zhangsan | 123456 |
| 3 | lisi | 123456 |
| 3 | wangwu | 123456 |
| 5 | zhaoliu | 123456 |
| 6 | zhouqi| 123456 |
+ ---- + ---------- +
6 rows in set (0.00 sec)

Mysql>
Mysql> insert into stu VALUES ('7', 'Tom ', '123 ');
Query OK, 1 row affected (0.05 sec)

Mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Mysql>

# Querying synchronization from a database

Mysql> use mydb;
Database changed
Mysql> select * from stu;
+ ---- + ---------- +
| Id | username | password |
+ ---- + ---------- +
| 1 | koumm | 123456 |
| 2 | zhangsan | 123456 |
| 3 | lisi | 123456 |
| 3 | wangwu | 123456 |
| 5 | zhaoliu | 123456 |
| 6 | zhouqi| 123456 |
| 7 | tom | 123456 |
+ ---- + ---------- +
7 rows in set (0.00 sec)

Mysql>

Summary:

This article is only a process of configuring mysql master-slave. There are still many addresses that need to be considered and improved.
(1) Whether to directly synchronize permissions from the master database or to the slave database separately.
(2) mysql5.5 semi-automatic master-slave synchronous Replication
(3) mysql5.6 has master-slave Replication Based on GTID.

Ii. Quick slave database configuration method 2

1. master database backup (full database backup)

Mysqldump-uroot-p-A-B -- events -- master-data = 1> mydb. SQL

Full database backup records the database backup master-bin.000003 ', MASTER_LOG_POS = 583 position, in the configuration of slave database does not need to lock the table in the master database, record POS position, unlock and other configurations.

[Root @ localhost ~] # Egrep-v "# | \ * | -- | ^ $" mydb. SQL | head

Change master to MASTER_LOG_FILE = 'master-bin.000003 ', MASTER_LOG_POS = 583;
USE 'mydb ';
Drop table if exists 'user ';
Create table 'user '(
'Id' varchar (20) not null,
'Username' varchar (20) not null,
'Password' char (32) not null,
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
Lock tables 'user' WRITE;

2. Create a synchronized database from the database and import data

Mysql-uroot-padmin
Create database mydb;
Mysql-uroot-padmin mydb <mydb. SQL

3. Configure master-slave Synchronization

Mysql>
CHANGE MASTER
MASTER_HOST = '1970. 168.0.65 ',
MASTER_PORT = 3306,
MASTER_USER = 'repl ',
MASTER_PASSWORD = '000000 ';

Mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.65
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 583
Relay_Log_File: testdb-relay-bin.000004
Relay_Log_Pos: 747
Relay_Master_Log_File: master-bin.000003
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: 583
Relay_Log_Space: 2536
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: c8bb22a1-024e-11e6-a1e8-000c29225fa0
Master_Info_File:/usr/local/mysql-5.6.29-linux-glibc2.5-x86_64/data/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)

Iii. Simple Management of master-slave Synchronization

1. Stop MYSQL Synchronization

Stop slave IO_THREAD; # STOP the IO Process
Stop slave SQL _THREAD; # STOP the SQL Process
Stop slave; # stop io and SQL Processes

2. Start MYSQL Synchronization

Start slave IO_THREAD; # START the IO Process
Start slave SQL _THREAD; # START the SQL Process
Start slave; # start io and SQL Processes

3. Reset MYSQL Synchronization

Reset slave;
Clear the master-slave synchronization parameter, which deletes the master.info and relay-log.info files, along with all relay logs, and starts a new relay log.
It is applicable to re-configuring a slave database.

4. view the MYSQL synchronization status

Show slave status;

5. Temporarily skipping MYSQL synchronization errors

When data consistency is ensured, the following error is skipped temporarily, which may occur multiple times.

Stop slave;
Set global SQL _SLAVE_SKIP_COUNTER = 1;
Start slave;

# Vi/etc/my. cnf

[Mysqld]
# Slave-skip-errors = errors 1146, # skip errors of the specified error no type
# Slave-skip-errors = all # skip all errors

Here, a 1146 error is reported because binlog-do-db filtering is configured and the configuration BUG occurs. Use binlog-do-db with caution.

Implement master-slave synchronization between two MySQL Databases

MySQL master-slave synchronization in Linux -- Add a new slave Database

Use XtraBackup to build MySQL master-slave synchronization without stopping the table and locking it

MySQL master-slave synchronization configuration records

Master-slave synchronization configuration of MySQL Databases in Linux

This article permanently updates the link address: