Shulou(Shulou.com)05/31 Report--

What this article shares with you is some basic knowledge about the MySQL master and follower. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Principle of MySQL replication

1. When the main database is modified, the data changes will be recorded in binlog as event, and the sync_binlog parameter on the main library controls the binlog log to be flushed to disk.

two。 The master library pushes the events in the binlog to the slave library's relay log relay log, and then the slave library redoes the data change operation according to the relay log relay log. Master-slave synchronization is achieved through logical replication.

MySQL completes replication between master and slave databases through three ready-made:

1.binlog dump runs on the main database and is responsible for reading database events and sending them to I / O threads.

The 2.I/O thread runs on the slave library, and when the slave library start slave is created, the Icano thread is linked to the master to write the events of the master library to the relaylog

The 3.SQL thread runs on the slave library and is responsible for reading the relay log and updating the slave library.

4.Master.inf and relay-log.info is used to save the progress of replication.

Three replication methods, MIX,STATEMENT,ROW

1. Statement

Statement-based replication may lead to inconsistency between master and standby and small amount of data because of stored procedures or triggers.

2. Row

The disadvantage of row-level data replication is that the amount of data is large, but the data will not be affected by external conditions such as triggers.

3. Mix

Statement mode is used by default if the statement contains update time, user-related functions, etc.

Only Mysqlbinlog-vv logfilename-base64-output=DECODE-ROWS-start-pos=number can parse the contents of the row schema.

Dual-master replication architecture:

Master1 is used as the read-write library and master2 as the read-only library.

At the same time, both sides act as the slave of the other party.

When maintaining

1. Stop slave on master1 first. So as not to affect master1 when master2 does maintenance.

two。 Stop slave on master2. Start maintenance operations, such as creating an index.

3. Open slave on master2 to synchronize master2 with master1.

4. Transfer read and write operations to master2 to ensure that there are no write operations on master1

5. Open the slave of master1 and let it finish synchronously with salve2.

Set up mysql replication:

1. Set up an account for replication:

Create replac slave on *. * TO 'repl'@'192.168.7.200' identified by' 1234test`

two。 Modify my.cnf to enable binlog and set the value of server-id

Log-bin = xxxxx.log

Server-id=1

3. Lock all the watches, lock tables with read lock

4. Check the current log offset show master status

5. Back up or copy the data files to the slave library directly from the shutdown database.

6. Unlock tables

7. Modify the my.cnf server-id=2 of slave

8. Turn on salve. / bin/mysql_safe-skip-slave-start& using deferred replication

9. Make response settings for the database service

Mysql > change master to master_host='',master_user='',master_password='',master_logfile='',master_log_pos=''

10. Enter: start slave from the library

11. Show process\ G check the slave process

12. Show variables like'% sync_binlog%' 0 means that mysql does not control the refresh of binlog, but is controlled by the file system.

13. The last few points to note is that the MHA architecture can automatically extract missing log for catchup. You can also use the mysqlbinlog tool to extract the log that was not extracted from the primary database when it was down and transfer it to the standby database.

Semi-synchronous replication:

Before mysql 5.5, due to the state of asynchronous replication between the primary and standby libraries, if the primary library collapsed after commit, the binlog had not yet been transferred to the slave library. The number will be lost.

After 5. 5, mysql introduced semi-synchronous replication status. By default, mysql needs to ensure that any changes received by the relaylog of the standby library will not confirm that the commit has been successful. If the network is cut off, it will automatically switch back to semi-synchronous replication mode.

Installation:

1. First you need to confirm whether mysql can use this feature, select @ @ have_dynamic_loading = > YES

2 to find the master library / slave library plug-in semisync_master/slave.so under $MYSQL_HOME/lib/plugin/

3. Install the plug-in install plugin rpl_semi_sync_master/salve soname 'semisync_master/slave.so' on the main and standby libraries

4.select * from mysql.plugin

5. Open semi-synchronous set global rpl_semi_sync_master_enabled=1 by setting global parameters on the master and slave libraries

Set global rpl_semi_sync_master_timeout=3000

From the library

Set global rpl_semi_sync_slave=1

If you have already started asynchronous replication, you need to stop io_thread. Stop slave io_thread; start slave io_thread

Show status like'% semi_sync%'

Several results that need to be noted are:

Rpl_semi_syc_master_status ON stands for opening

Rpl_semi_syc_master_yes/no_tx indicates the number of transaction synchronized or unsynchronized

Rpl_semi_syc_master_timeout indicates how long to wait will determine the slave timeout.

If you reconnect after being disconnected from the library, it will automatically return to the semi-synchronous state.

Some useful parameters for Mysql replication:

1. Whether Log-slave-updates slave library needs to remember binlog is helpful for transferring slave library from library.

2. The default retry interval of Master-connect-retry is 60 seconds when the connection between the slave library and the master library is lost.

3. / bin/Mysqld_safe-read-only to prevent people other than superusers from modifying data from the library

Daily maintenance commands:

1. View the slave library status show slave status. One of the 2 states that I am more concerned about is SLAVE_IO_Running and Slave_SQL_Running.

two。 Sometimes the gap between the main library and the backup library is getting wider and wider, so you can use flush tables with read lock in your spare time.

Show master status looks at the position of binlog. Next use select master_pos_wait ('logiflename','log_pos'); this statement blocks until you synchronize from the library to that point. And then unlock the table.

3. Error in slave library:

A) if you need to skip some statements that cause replication to fail, you can use set global SQL_SLAVE_SKIP_COUNTER=N. Net 1 | 2. When the main library is not self-growing, use 1 self-growth, use 2.

B) Log event entry exceeded max_allowed_packet is usually due to long strings or blob cannot be transferred. At this point, you need to increase the size of this parameter on the main library.

C) if the self-growth variables conflict in the case of multi-master replication, you need to set auto_increment_increment=2,auto_increment_offset=1; on the dual master. (.) Auto_increment_increment=2,auto_increment_offset=0; (2, 4, 6, 8... (.)

Some simple architectures to improve the performance of slave libraries

1. Pass it to master2 master2 via master1 and start slave-log-update using the blackhole engine.

The other slave replicates only part of the database through replace-do-db. The disadvantage of this method is that once the master1 goes down, it will be troublesome to discuss the exception handling plan in advance.

2.mysql 5.6 is followed by multithreaded replication, which allows parallel updates from the library. The mysql of the Slave_parallel_works=2. Percona branch can also be solved by using Taobao Dingqi's mysql transfer to relay logs from the multithreaded application of the main library.

Http://dinglin.iteye.com/blog/188640.

Switch between master and slave:

1. First make sure that all slave libraries perform all updates in relay log. Stop slave io_thread is executed on each slave library. Then use show process\ G until you see state: has read all relay log and the flag updates are all done.

2. Stop slave on S1. Turn on log-bin mode. At the same time, close log-slave-updates.

3. Reset master.

4. Execute stop slave on S2 and then change master to master_host=S1. Start slave.

5. Delete master.info and relay-log.info on S1 or start as slave next time

6. After the main library is repaired, it can be reset to S1 according to the configuration method of S2.

These are some of the basic knowledge of the MySQL master and follower, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.