MySQL5.6 Best Practices for master-slave replication

MySQL5.6 configuration of Master-slave replicationEnvironmentoperating system: centos-6.6-x86_64MySQL version: Mysql-5.6.26.tar.gzMaster node ip:192.168.31.57 host name: edu-mysql-01ip:192.168.31.59 host name from node: edu-mysql-02  MySQL Master Copy Official documenthttp://dev.mysql.com/doc/refman/5.6/en/replication.htm L the principle of MySQL master-slave replication (also known as A/b replication)

1. Master records data changes into binary logs (binary log), which is the file specified by the configuration file Log-bin, which is called the binary log event (binary logs events);
2. Slave reads the binary log events in Master through the I/O thread and writes to its trunk log (relay log);
3. Slave redo the events in the relay log, the event information in the trunk log is executed locally one at a time, the data is stored locally, and the change is reflected to its own data (data replay).

points to note for master-slave configuration

1. The master-Slave server operating system version and the number of bits consistent, master and slave server hostname not consistent.
2. The version of Master and Slave database should be consistent;
3. The data in the Master and Slave databases should be consistent;
4. Master to open the binary log, master and Slave server_id must be unique within the LAN;
5. Both Master and Slave CREATE database mbank, table testuser;

  Master Configuration1, Master (192.168.31.57) and Slave (192.168.31.59) Note: Both database server SELinux are disable (permanently off SELinux, please modify the/etc/selinux/ Config, change SELINUX to Disabled)2, modify the Master configuration file/etc/my.cnf [[email protected] ~]# vi/etc/my.cnf# # Add the following configuration items in [mysqld]# Set SERVER_ID, generally set to IPserver_id=57# Replication filtering: Database to be backed up, output BinlogBinlog-do-db=mbank# Copy Filter: Database not required for backup, no output (MySQL library is not normally synchronized)Binlog-ignore-db=mysql# Open the binary log function, you can take it, it's better to have meaningLog-bin=edu-mysql-bin# Memory allocated for each session, used to store the cache of binary logs during the transactionbinlog_cache_size=1m# master-slave copy format (mixed,statement,row, default format is statement)binlog_format=mixed# Number of days the binary log is automatically deleted/expired. The default value is 0, which means that it is not automatically deleted. expire_logs_days=7 # Skip all errors encountered in master/slave replication or specify types of errors to avoid slave-side replication . # such as: 1062 error refers to some primary key duplication, 1032 error is due to master and slave database data inconsistencyslave_skip_errors=1062# If you need to synchronize functions or stored procedureslog_bin_trust_function_creators=true(if you want to know more detailed analysis of the above parameters, you can directly Baidu parameter name)  2.1 Replication filtering allows you to replicate only a subset of the data in the server, and there are two types of replication filtering:

1. Filter the events in the binary log on Master;
2. Filters the events in the trunk log on the Slave.

  2.2 MySQL replication type for binary log (Binlog)

1. Statement-based replication: The SQL statement executed on Master executes the same statement on Slave. MySQL uses statement-based replication by default and is more efficient. Row-based replication is automatically selected as soon as it is found that it cannot be copied accurately.
2. Row-based replication: Copy the changed content to Slave instead of executing the command on the Slave. Support Starts from MySQL5.0.
3. Mixed-type replication: statement-based replication is used by default, and row-based replication occurs when a statement-based, inaccurate copy is found.

3. Start/Restart the Master database service, log in to the database, create a data synchronization user, and grant the appropriate permissions[[Email protected] ~]# service MySQL restartshutting down MySQL. [OK] Starting MySQL. [OK][email protected] ~]# mysql-uroot-pEnter Password:Welcome to the MySQL Monitor.  Commands End With; or \g. Your MySQL Connection ID is 1Server version:5.6.26-log Source distributionCopyright (c), Oracle and/or its affiliates. All rights Reser ved.Oracle is a registered trademark of the Oracle Corporation and/or its affiliates. Other names trademarks of their respective owners.Type ' help; '  or ' \h ' for help. Type ' \c ' to clear the current input stat ement.#创建数据同步用户, and grant the appropriate permissionsmysql> grant replication slave, replication Client on *. * to ' repl ' @ ' 192.168.31.59 ' identified by ' 1234 ";Query OK, 0 rows Affected (0.00 sec)# Refresh Authorization table informationmysql> flush Privileges;Query OK, 0 rows Affected (0.00 sec)# See what Users and IP rights are now available for MySQLmysql> Select User,host from Mysql.user;# Check the position number and make a note of the position number (this position and now log files are required from the machine)mysql> Show master status;Mysql-bin file name is modified after Master reboot (sequence number plus 1) 4, Next processing Slave (192.168.31.59), configuration files only need to modify one item, the rest of the configuration with commands to operate[email protected] ~]# vi/etc/my.cnf# # Add the following configuration items in [mysqld]# Set SERVER_ID, generally set to IPserver_id=59# Replication filtering: Database to be backed up, output BinlogBinlog-do-db=mbank#复制过滤: Databases that do not need to be backed up, not output (MySQL library is not generally synchronized)Binlog-ignore-db=mysql# Turn on the binary log for Slave to be used as the Master of other SlaveLog-bin=edu-mysql-slave1-bin# Memory allocated for each session, used to store the cache of binary logs during the transactionbinlog_cache_size = 1M# master-slave copy format (mixed,statement,row, default format is statement)binlog_format=mixed# Number of days the binary log is automatically deleted/expired. The default value is 0, which means that it is not automatically deleted. expire_logs_days=7# Skip all errors encountered in master-slave replication or specify types of errors to avoid slave-side replication interrupts. # such as: 1062 error refers to some primary key duplication, 1032 error is due to master and slave database data inconsistencyslave_skip_errors=1062# Relay_log Configuring the trunk logRelay_log=edu-mysql-relay-bin# log_slave_updates means slave writes the copy event into its own binary logLog_slave_updates=1# Prevent data changes (except for special threads)Read_only=1if Slave is a Master of another Slave, you must set the Bin_log. Here we turn on the binary log and explicitly name it (the default name is hostname, but if the H ostname changes there will be a problem). Relay_log configures the trunk log, log_slave_updates indicates that slave writes the replication event into its own binary log. when setting log_slave_updates, you can let slave play the Master of other slave. At this point, slave writes the events executed by the SQL thread to its own binary log (binary logs), and then its slave can get the events and execute it.  5. Restart the MySQL service after saving, restore the backup data[[Email protected] ~]# service MySQL restartshutting down MySQL. [OK] Starting MySQL. [OK]  6, Login Slave database, add relevant parameters# # #Master IP, port, sync user, password, position number, read which log file[email protected] ~]# mysql-uroot-pEnter Password:Welcome to the MySQL Monitor.  Commands End With; or \g. Your MySQL Connection ID is 3Server version:5.6.26-log Source distributionCopyright (c), Oracle and/or its affiliates. All rights Rese rved.Oracle is a registered trademark of the Oracle Corporation and/or its affiliates. Other names trademarks of their respective owners.Type ' help; '  or ' \h ' for help. Type ' \c ' to clear the current input stat ement.mysql> Change Master to master_host= ' 192.168.31.57 ', master_user= ' repl ', master_password= ' 123456 ', Master_po rt=3306, master_log_file= ' edu-mysql-bin.000001 ', master_log_pos=429, master_connect_retry=3 0;Query OK, 0 rows affected, 2 warnings (0.01 sec) explanation of the command executed above:master_host= ' 192.168.31.57 ' # # Master's IP address master_user= ' REPL ' # # users who are used to synchronize data (users authorized in master)master_password= ' 123456 ' # # Sync data user's password master_port=3306 # # master database service Port master_log_file= ' E du-mysql-bin.000001 ' # #指定 Slave read the copy data from which log file (you can see the log file name using show Master status on Master)master_log_pos=429 # # from which POSITION to start reading master_connect_retry=30 # #当重新建立主从连接时, if the connection establishment fails, how often after the interval is retried. The unit is seconds, the default setting is 60 seconds, and the synchronization delay tuning parameter. # # View master-Slave synchronization statusmysql> show slave status\g;you can see that slave_io_state is empty, slave_io_running and Slave_sql_runnin g are no, indicating that Slave has not started the copy process. # # Turn on master-slave synchronizationmysql> start slave;Query OK, 0 rows Affected (0.00 sec)# # See the Master-slave sync status againmysql> show slave status\g;mainly see the following two parameters, these two parameters if yes means master-slave synchronization is normalSlave_io_running:yes Slave_sql_running:yesby the master-slave synchronization state information can be seen, we configured the master-slave synchronization is normal. You can view the status of the master and slave threads. On master, you can see the slave Ithe connection created by the/O thread:master:mysql> show Processlist\g; 1.row is the connection to the I/O thread that handles slave. 2.row handles the MySQL client connection thread. 3.row is the thread that handles the local command line. slave:mysql> show processlist\g;1. Row is the I/O thread state. 2. Row is the SQL thread state. 3. Row is the thread that handles the local command line. 7, master-slave data replication synchronization test(1) synchronization test of change data on MBank library in Master;mysql> INSERT into ' testuser ' (' usercode ', ' username ') VALUES (' 1 ', ' sync Test 1 '), (' 2 ', ' Sync Test 2 ');when you are finished adding in Master, check to see if the data is synchronized in the login Slave.   8, in the test process, if you encounter synchronization error, you can reset the master-slave replication settings on the Slave (optional operation): (1) mysql> reset slave;(2) mysql> change master to master_host= ' 192.168.31.57 ', master_user= ' repl ',master_password= ' 123456 ', master_port=3306,master_log_file= ' edu-mysql-bin.00000x ', master_log_pos=xx, master_connect_retry=30;(at this point, master_log_file and Master_log_pos are going to use show maste in master. )r Status Command view)Note: If the Slave does not have read-only control, do not manually insert the data in the Slave, then the data will be inconsistent, the master and slave will be disconnected, you need to reconfigure.   9, the above is a one-way copy of the master-slave , is also used more, and the two-way master and slave is actually the master and Slave are turned on the log function, and then the master to perform authorized users(This is authorized as the slave server, which is the IP address here is the IP address of master), and then on the master of the Chang Master operation. The master/slave mechanism of LAN-based is usually able to meet the requirements of "real-time" backup. If the delay is large, you can troubleshoot from several factors:

1. Network latency;
2. Master load is too high;
3. Slave load is too high;

The general practice is to use multiple Slave to share the read request, and then configure a single Slave only as a backup, without any other operations, can be relatively maximum to achieve "real-time" requirements. I encountered in the deployment of the problem: because my slave server is the primary server replication, MySQL server_id, in sync with the error:Fatal error:the slave I/O thread stops because master and slave has equal mysq L server UUIDs; These uuids must be D Ifferent for replication to work.(1) First check:mysql> Show variables like ' server_id ';+ ————— + ——-+ | variable_name | Value |+ ————— + ——-+| server_id | 3 |+ ————— + ——-+master and slave cannot but the same. (2) if not, continue to troubleshoot. Enter the MySQL data directory, open the Auto.cnf file, which records the database UUID, each library's UUID should be different. [Auto]Server-uuid=6dcee5be-8cdb-11e2-9408-90e2ba2e2ea6solution, according to this 16 binary format, casually change, restart MySQL can.  

MySQL5.6 Best Practices for master-slave replication