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

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

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

Installation Method, see http://www.bkjia.com/article/82542.htm

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
Copy codeThe Code is as follows:
[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
 
Copy codeThe Code is as follows:
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.
Copy codeThe Code is as follows:
[Root @ master ~] # Service mysql restart
[Root @ slave ~] # Service mysql restart

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

Copy codeThe Code is as follows:
[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.

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

Copy codeThe Code is as follows:
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.

Appendix: an optimized master database configuration file and Slave Data configuration file are as follows:

Copy codeThe Code is as follows:
# For advice on how to change settings please see
# Http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[Client]
Port = 3306
Socket =/usr/local/mysql. sock
Default-character-set = utf8
 
[Mysqld]
Sync_binlog = 1
Server-id = 1
Port = 3306
Socket =/usr/local/mysql. sock
Pid-file =/home/mysql/temp/my3306.pid
User = mysql
Datadir =/home/mysql/data
Tmpdir =/home/mysql/temp/
Log-bin =/home/mysql/data/mysqlmaster-bin
Log-error =/home/mysql/logs/error. log
Slow_query_log_file =/home/mysql/logs/slow. log
Binlog_format = mixed
Slow_query_log
Long_query_time = 10
Wait_timeout = 31536000
Interactive_timeout = 31536000
Max_connections = 500
Max_user_connection= 490
Max_connect_errors = 2
Character_set_server = utf8
Skip-external-locking
Key_buffer_size = 128 M
Max_allowed_packet = 5 M
Table_open_cache = 512
Sort_buffer_size = 2 M
Read_buffer_size = 2 M
Read_rnd_buffer_size = 8 M
Myisam_sort_buffer_size = 64 M
Thread_cache_size = 8
Query_cache_size = 32 M
# Try number of CPU's * 2 for thread_concurrency
Thread_concurrency = 4
Binlog-ignore-db = mysql
Binlog-ignore-db = information_schema
Replicate_ignore_db = mysql
Replicate_ignore_db = information_schema
Expire-logs-days = 10
Skip-slave-start
Skip-name-resolve
Lower_case_table_names = 1
Log_bin_trust_function_creators = 1
 
# InnoDB
Innodb_data_home_dir =/home/mysql/data
Innodb_log_group_home_dir =/home/mysql/logs
Innodb_data_file_path = ibdata1: 128 M: autoextend
Innodb_buffer_pool_size = 2G
Innodb_log_file_size = 10 M
Innodb_log_buffer_size = 8 M
Innodb_lock_wait_timeout = 50
Innodb_file_per_table
Innodb_flush_log_at_trx_commit = 1
 
# SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES
 

 
 
An Optimized slave database configuration file is as follows:

Copy codeThe Code is as follows:
# For advice on how to change settings please see
# Http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[Client]
Port = 3306
Socket =/usr/local/mysql. sock
Default-character-set = utf8
 
[Mysqld]
Sync_binlog = 1
Server-id = 2
Port = 3306
Socket =/usr/local/mysql. sock
Pid-file =/home/mysql/temp/my3306.pid
User = mysql
Datadir =/home/mysql/data
Tmpdir =/home/mysql/temp/
Log-bin =/home/mysql/data/mysqlslave-bin
Log-error =/home/mysql/logs/error. log
Slow_query_log_file =/home/mysql/logs/slow. log
Binlog_format = mixed
Slow_query_log
Long_query_time = 10
Wait_timeout = 31536000
Interactive_timeout = 31536000
Max_connections = 500
Max_user_connection= 490
Max_connect_errors = 2
Character_set_server = utf8
Skip-external-locking
Key_buffer_size = 128 M
Max_allowed_packet = 5 M
Table_open_cache = 512
Sort_buffer_size = 2 M
Read_buffer_size = 2 M
Read_rnd_buffer_size = 8 M
Myisam_sort_buffer_size = 64 M
Thread_cache_size = 8
Query_cache_size = 32 M
# Try number of CPU's * 2 for thread_concurrency
Thread_concurrency = 4
Binlog-ignore-db = mysql
Binlog-ignore-db = information_schema
Replicate_ignore_db = mysql
Replicate_ignore_db = information_schema
Expire-logs-days = 10
# Skip-slave-start
Skip-name-resolve
Lower_case_table_names = 1
Log_bin_trust_function_creators = 1
 
# InnoDB
Innodb_data_home_dir =/home/mysql/data
Innodb_log_group_home_dir =/home/mysql/logs
Innodb_data_file_path = ibdata1: 128 M: autoextend
Innodb_buffer_pool_size = 2G
Innodb_log_file_size = 10 M
Innodb_log_buffer_size = 8 M
Innodb_lock_wait_timeout = 50
Innodb_file_per_table
Innodb_flush_log_at_trx_commit = 1
 
# SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES
SQL _mode = STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION, NO_AUTO_VALUE_ON_ZERO
 
[Mysqldump]
Quick
Max_allowed_packet = 16 M
 
[Mysql]
No-auto-rehash
 
[Myisamchk]
Key_buffer_size = 256 K
Sort_buffer_size = 256 K
Read_buffer = 256 K
Write_buffer = 256 K
 
[Mysqlhotcopy]
Interactive-timeout
 
SQL _mode = STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION, NO_AUTO_VALUE_ON_ZERO
 
[Mysqldump]
Quick
Max_allowed_packet = 16 M
 
[Mysql]
No-auto-rehash
 
[Myisamchk]
Key_buffer_size = 256 K
Sort_buffer_size = 256 K
Read_buffer = 256 K
Write_buffer = 256 K
 
[Mysqlhotcopy]
Interactive-timeout