MySQL5.6 Replication Master-slave replication (read/write separation) configuration full version

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

1. There are two ways to start a master-slave copy of MySQL5.6:

Log based (binlog);

Based on the Gtid (global transaction identifier).

Note: The Gtid method does not support temporary tables! So if your business system is going to use a temporary table, don't think about it.

At least the current version of MySQL5.6.12 's Gtid replication is still not supported for temporary tables.

So the main tutorial is to tell you how to copy from the log (Binlog) way!

2. mysql official MySQL replication tutorial:

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

The first step: getting Ready for work

Primary 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 master server and slave server

installation method, see

MySQL5.6.18 for Rhel/oralcelinux 6.5 installation

After the MySQL database software installation is complete, do not rush to do the MySQL boot operation. It is recommended that MySQL initialize the generated/usr/my.cnf

(if it was compiled from source, the path should be in/usr/local/mysql/mysql.cnf), and then the optimized MySQL

The configuration file my.cnf in/etc.

Step Three: Modify the configuration file of the primary database/usr/my.cnf

[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

Fourth Step: Modify the configuration file from the database/usr/my.cnf

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

Fifth step: Restart the primary and the database from the main database and from the database server by executing the following command separately

[[Email protected] ~]# service MySQL restart

[[Email protected] ~]# service MySQL restart

Sixth step: Create an account for master-slave replication on the primary database

[Email protected] ~]# 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 (no re-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)

Eighth step: View the status of the primary database (and record the value of the file field and the Position field, which is 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)

Nineth Step: Create a Snapshot file for the primary database

[Email protected] ~]# cd/usr/bin/

#./mysqldump-uroot-p-h127.0.0.1-p3306--all-databases--triggers--routines--events >>/mnt/windows/all.sql

The red part 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 remote from the database server

The execution time of this command varies depending on the amount of data, and if the data volume of the primary database is large and may take a long time, in this case it is best to

Do this when there is no business at night, otherwise the locking table operation in the seventh step will have a great impact on the business system.

Tenth step: Unlock the lock table operation of the primary database

[[email protected] ~]# mysql-uroot-p (This command executes on the primary database server)

mysql> unlock tables;

Query OK, 0 rows Affected (0.00 sec)

11th Step: Import the snapshot file created from the database server to the seventh step from the database

[Email protected] ~]# Mysql-uroot-p-h127.0.0.1-p3306 </mnt/windows/all.sql

12th Step: Set the primary database server from the database server to synchronize from the database server

[Email protected] ~]# 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

13th step: Start the replication thread from the database

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

14th Step: Querying the replication thread state from the 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 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)

If both slave_io_running and slave_sql_running two are yes, the master-slave replication configuration is successful.

The following can start testing the configuration is successful, first in the main database test database to create a new table, and then insert a few data, and then to see from the database is synchronized.

Note: When there are a large number of queries from the database, you can temporarily shut down the replication thread from the database, and then the query volume is lowered and opened, so there is no loss of data.

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

# for advice The 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

A well-optimized configuration file from the database is as follows:

# for advice The 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