MySQL5.6 master-slave Replication (read/write splitting) configuration full version

MySQL5.6 master-slave Replication (read/write splitting) configuration full version

MySQL5.6 master-slave replication (read/write splitting) Tutorial

1. There are two ways to start master-slave replication from MySQL5.6:

Log-based (binlog );

Based on GTID (global transaction identifier ).

Note that the GTID mode does not support temporary tables! Therefore, if your business system needs to use temporary tables, do not consider this method. At least the latest version of MySQL5.6.12 does not support temporary table replication.

This tutorial mainly describes how to use the binlog method to replicate data!

2. MySQL Replication tutorials officially provided by MySQL:

Http://dev.mysql.com/doc/refman/5.6/en/replication.html

Step 1: Prepare

Master server: 192.168.1.100

Slave server: 192.168.1.101

MySQL software version:

MySQL-server-advanced-5.6.18-1.el6.x86_64.rpm

MySQL-cient-advanced-5.6.18-1.el6.x86_64.rpm

Step 2: Install MySQL database software on the master server and slave server

For the installation method, see

Full version of MySQL5.6 cluster configuration under RedHat6.5

After installing the MySQL database software, do not rush to start mysql. We recommend that you initialize/usr/my. cnf generated by mysql.

(When compiling and installing from the source file, the path should be in/usr/local/mysql. cnf), and then the optimized mysql

Put the configuration file my. cnf under/etc.

Step 3: modify the configuration file/usr/my. cnf of the primary database

[Mysqld]

 

Server-id = 1

Log-bin = mysqlmaster-bin.log

Sync_binlog = 1

 

Innodb_buffer_pool_size = 512 M

Innodb_flush_log_at_trx_commit = 1

 

SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

 

Lower_case_table_names = 1

Log_bin_trust_function_creators = 1

 

Step 4: Modify the slave Database Configuration File/usr/my. cnf

 

Server-id = 2

Log-bin = mysqlslave-bin.log

Sync_binlog = 1

Innodb_buffer_pool_size = 512 M

Innodb_flush_log_at_trx_commit = 1

 

SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

Lower_case_table_names = 1

Log_bin_trust_function_creators = 1

 

Step 5: run the following commands on the master database and slave database server to restart the master database and slave database respectively.

[Root @ master ~] # Service mysql restart

[Root @ slave ~] # Service mysql restart

 

Step 6: create an account for master-slave replication on the master database

[Root @ master ~] # Mysql-uroot-p

Mysql> grant replication slave on *. * TO 'repl' @ '192. 168.1.101 'identified BY '123 ';

Query OK, 0 rows affected (0.00 sec)

 

Note: The IP address in the preceding command is the IP address of the slave database server.

 

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

-------------------------------------- Split line --------------------------------------

Step 7: master database lock table (disable data insertion to obtain the binary log coordinates of the master database)

Mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

 

Step 8: view the status of the primary database (and record the values of the File field and Position field, which are used when configuring slave servers)

Mysql> show master status;

+ ------------------------ + ---------- + -------------- + ------------------ + ------------------- +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+ ------------------------ + ---------- + -------------- + ------------------ + ------------------- +

| Mysqlmaster-bin.000004 | 327 |

+ ------------------------ + ---------- + -------------- + ------------------ + ------------------- +

1 row in set (0.00 sec)

Step 9: Create a snapshot file for the primary database

[Root @ master ~] # Cd/usr/bin/

#./Mysqldump-uroot-p-h127.0.0.1-P3306 -- all-databases -- triggers -- routines -- events>/mnt/windows/all. SQL

The red part in the preceding command is a shared directory, which can be simultaneously accessed by the master database server and slave database server.

If you do not have such a shared directory, you can store all. SQL in any other directory, and then use the scp command to copy it to a directory on the remote slave database server.

The execution time of this command varies depending on the amount of data. If the data volume of the primary database is large, it may take a long time, in this case, it is best to perform this operation when there is no business at night, otherwise the lock table operation in Step 7 will have a great impact on the business system.

Step 10: Unlock the master database lock table

[Root @ master ~] # Mysql-uroot-p (this command is executed on the master database server)

Mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

Step 2: import the snapshot File Created in Step 7 from the database server to the slave Database

[Root @ slave ~] # Mysql-uroot-p-h127.0.0.1-P3306 </mnt/windows/all. SQL

Step 2: Set the master database server to synchronize data from the slave Database Server

[Root @ slave ~] # Mysql-uroot-p

Mysql> change master to master_host = '192. 168.1.100 ', master_user = 'repl', master_password = '000000', master_log_file = 'mysqlmaster-bin.000004', master_log_pos = 192;

Note: the value of the red part is found in Step 8 and cannot be mistaken here.

Step 2: Start the slave Database Replication thread

Mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

Step 2: query the replication thread status of the slave Database

Mysql> show slave status \ G

* *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.100

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqlmaster-bin.000004

Read_Master_Log_Pos: 327

Relay_Log_File: slave-relay-bin.000002

Relay_Log_Pos: 289

Relay_Master_Log_File: mysqlmaster-bin.000004

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: 327

Relay_Log_Space: 462

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: 2e5e1b22-f0a9-11e3-bbac-000c297799e0

Master_Info_File:/var/lib/mysql/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)

 

If both Slave_IO_Running and Slave_ SQL _Running are yes, the master-slave replication configuration is successful.

Next, you can test whether the configuration is successful. First, create a new table in the test database of the primary database, insert a few pieces of data, and then check whether the data has been synchronized from the database.

Note: When there are a large number of queries in the slave database, you can temporarily close the replication thread of the slave database. When the query volume falls down, the data will not be lost.

For more details, please continue to read the highlights on the next page:

1 2 Next Page