MySQL5.6 Replication master slave copy (read and write separate configuration full version

2020-12-21 18:12:44
OfStack

MySQL 5.6 Master-slave replication (read-write separation) tutorial

1. MySQL 5.6 There are two ways to start master-slave replication:

Log-based (binlog);
GTID (Global transaction identifier) based.

One thing to note: GTID does not support temporary tables! So if your business system is using temporary tables, don't consider this option. At least GTID replication in the latest version of MySQL5.6.12 does not support temporary tables.

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

2. MySQL Replication course 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 the MySQL database software on the master and slave servers

Installation methods, see https: / / www ofstack. com article / 82542. htm

After installing the MySQL database software, don't rush to do the mysql boot operation. It is recommended to initialize mysql to /usr/ my.cnf
(if path from the source file to compile installation, should be in the/usr local/mysql/mysql cnf) to delete, and then the optimization of good mysql
The configuration file my.cnf is placed under /etc.
Step 3: Modify the configuration file for the primary database /usr/ ES72en.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

Step 4: Modify from database configuration file /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

Step 5: Restart the master and slave databases on the master and slave database servers by executing the following command



[root@master ~]# service mysql restart

[root@slave ~]# service mysql restart

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



[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.

Step 7: Master database lock table (forbid inserting data to get the base 2 log coordinates of the master database)
mysql > flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

Step 8: View the state of the primary database (and record the values of the File and Position fields, which you will use when configuring the slave server)

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 of the command above is a shared directory that can be accessed by both the primary and secondary database servers.
If you do not have such a shared directory, you can put ES138en.sql in any other directory and then copy it to a directory on the remote from the database server using the scp command
The execution time of this command varies depending on the amount of data. If the amount of data in the primary database is large and may take a long time, then in this case, it is better to do this at night when there is no business, otherwise the locktable operation in Step 7 will have a significant impact on the business system
Step 10: Unlock the lock table operation for the primary database
[ES144en@master ~]# ES146en-ES147en-ES148en (this command is executed on the primary database server)
mysql > unlock tables;
Query OK, 0 rows affected (0.00 sec)
Step 101: Import the snapshot file created in Step 7 from the database server into the slave database
[root@slave ~]# mysql -uroot -p -h127.0.0.1 -P3306 < /mnt/windows/all.sql
Step 102: Set up the master database server on the slave database server to synchronize with the slave 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 values in red are found in step 8, so make no mistake here
Step 103: Start the replication thread from the database
mysql > start slave;
Query OK, 0 rows affected (0.01 sec)
Step 104: Query the status of the replicated thread 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 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 Slave_IO_Running and Slave_SQL_Running are both yes, the master-slave replication configuration is successful.
The next step is to test if the configuration is successful by creating a new table in the test database in the primary database, inserting several pieces of data, and then going to the slave database to see if it is synchronized.
Note: When there are a large number of queries from the database, you can temporarily close the replication thread from the database, wait until the query volume drops, and then open it again, so that no data is lost.

Attachment: 1 optimized master database configuration file and slave data configuration file contents are 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/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

1 optimized configuration file from the database 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/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