MySQL5.6 Replication Master-slave copy (read-write separation) configuration full version _mysql

MySQL5.6 master-slave copy (read-write separation) tutorial

1, MySQL5.6 start the master-slave replication has two ways:

Based on log (binlog);
Based on the Gtid (global transaction identifier).

Note that the Gtid method does not support temporary tables! So if your business system uses temporary tables, don't think about it, at least the current version of MySQL5.6.12 's Gtid replication doesn't support temporary tables.

So this tutorial is mainly to tell you how to use the log (Binlog) way to master from the copy!

2, MySQL officially provided by the MySQL replication tutorial:

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

The first step: Prepare for work

Home server: 192.168.1.100
From 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 two: Install MySQL database software on the primary server and from the server

Install method, see http://www.jb51.net/article/82542.htm

MySQL database software installation completed, do not rush to do the MySQL start operation. It is recommended that the MySQL initialization generated/usr/my.cnf
(If the installation is compiled from the source file, the path should be in/usr/local/mysql/mysql.cnf) removed, and then the optimized MySQL
The configuration file my.cnf is placed under/etc.
Step Three: Modify the configuration file for the primary database/usr/my.cnf

Copy Code code as follows:

[Mysqld]

Server-id=1
Log-bin=mysqlmaster-bin.log
Sync_binlog=1

innodb_buffer_pool_size=512m
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 Fourth: Modify the/USR/MY.CNF from the database configuration file

Copy Code code as follows:

server-id=2
Log-bin=mysqlslave-bin.log
Sync_binlog=1
innodb_buffer_pool_size=512m
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 Fifth: Restart the primary database and from the database by executing the following command on the primary database and from the database server, respectively

Copy Code code as follows:

[root@master ~]# service MySQL restart
[Root@slave ~]# service MySQL restart

Step Sixth: Create accounts for master and slave replication on the primary database

Copy Code code as follows:

[Root@master ~]# Mysql-uroot-p
Mysql> GRANT REPLICATION SLAVE on *.* to ' repl ' @ ' 192.168.1.101 ' identified by ' 111111 ';
Query OK, 0 rows Affected (0.00 sec)

Note: The IP address in the above command is the IP address from the database server.

Seventh Step: Primary Database Lock table (prohibit inserting data to get binary log coordinates of the primary database)
Mysql> flush tables with read lock;
Query OK, 0 rows Affected (0.00 sec)

Step Eighth: View the status of the primary database (and record the values of the file field and the Position field, which are useful when configuring from the server)

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 nineth: 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 section of the above command is a shared directory that can be accessed both by the primary database server and from the database server.
If you do not have such a shared directory, you can place all.sql in any other directory, and then use the SCP command to copy to a directory remotely from the database server
The execution time of this command varies according to the amount of data will be different, if the data volume of the primary database is very large, it may take a long time, then in this case, it is best to do this at night when there is no business, otherwise the seventh step of the lock table operation will have a great impact on the business system
Tenth step: Unlock the main database lock table operation
[Root@master ~]# mysql-uroot-p (This command executes on the primary database server)
mysql> unlock tables;
Query OK, 0 rows Affected (0.00 sec)
Step 11th: In the snapshot files created from the seventh step of importing from the database server to the database
[Root@slave ~]# mysql-uroot-p-h127.0.0.1-p3306 </mnt/windows/all.sql
Step 12th: Set up the primary database server from the database server to synchronize from the database server
[Root@slave ~]# Mysql-uroot-p
mysql> Change Master to Master_host = ' 192.168.1.100 ', master_user= ' repl ', master_password= ' 111111 ', master_log_file = ' mysqlmaster-bin.000004 ', master_log_pos=327;
Note: The red part of the value, is found in the eighth step, there is no mistake here
Step 13th: Start copying threads from the database
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
Step 14th: Query replication thread State from the database

Copy Code code 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 two are yes, the master-slave replication configuration is successful.
You can start testing your configuration for success by creating a new table in the primary database's test database, inserting a few data, and then checking to see if it's synchronized from the database.
Note: When there are a large number of queries from the database, you can temporarily shut down the replication thread from the database, and so on, and then open, so that data will not be lost.

Attached: An optimized master database configuration file and from the data profile contents are as follows:

Copy Code code as follows:

# for advice on I-Change settings
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[Client]
port=3306
Socket=/usr/local/mysql/mysql.sock
Default-character-set=utf8

[Mysqld]
Sync_binlog=1
Server-id=1
port=3306
Socket=/usr/local/mysql/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_connections=490
max_connect_errors=2
Character_set_server=utf8
Skip-external-locking
Key_buffer_size = 128M
Max_allowed_packet = 5M
Table_open_cache = 512
Sort_buffer_size = 2M
Read_buffer_size = 2M
Read_rnd_buffer_size = 8M
Myisam_sort_buffer_size = 64M
Thread_cache_size = 8
Query_cache_size = 32M
# 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:128m:autoextend
innodb_buffer_pool_size=2g
innodb_log_file_size=10m
Innodb_log_buffer_size=8m
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 configuration file from the database is as follows:

Copy Code code as follows:

# for advice on I-Change settings
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[Client]
port=3306
Socket=/usr/local/mysql/mysql.sock
Default-character-set=utf8

[Mysqld]
Sync_binlog=1
server-id=2
port=3306
Socket=/usr/local/mysql/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_connections=490
max_connect_errors=2
Character_set_server=utf8
Skip-external-locking
Key_buffer_size = 128M
Max_allowed_packet = 5M
Table_open_cache = 512
Sort_buffer_size = 2M
Read_buffer_size = 2M
Read_rnd_buffer_size = 8M
Myisam_sort_buffer_size = 64M
Thread_cache_size = 8
Query_cache_size = 32M
# 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:128m:autoextend
innodb_buffer_pool_size=2g
innodb_log_file_size=10m
Innodb_log_buffer_size=8m
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 = 16M

[MySQL]
No-auto-rehash

[Myisamchk]
Key_buffer_size = 256K
Sort_buffer_size = 256K
Read_buffer = 256K
Write_buffer = 256K

[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 = 16M

[MySQL]
No-auto-rehash

[Myisamchk]
Key_buffer_size = 256K
Sort_buffer_size = 256K
Read_buffer = 256K
Write_buffer = 256K

[Mysqlhotcopy]
Interactive-timeout