Shulou(Shulou.com)06/01 Report--

I. the principle of mysql replication

The master info O thread thread from the library will read the master info file to get the user,password port information of the master library, and then get the location where the last binary log of the master library was obtained, such as 3640, which is 00003. After receiving the request from the slave library, the master library will verify the validity of the user name and password, and then ask the master database if you have a newer binary log than the last 00003 file 640s. Then the master library will check its own binglog log. If it is found that it is newer than 31080, if it has reached the location 31080, the master database will truncate the binglog log from file 00003 to 1080, and return it to the dump O thread thread of the slave library. After the slave database, it will first store it in the tcp/ip cache (tcp/ip cached), and then the slave library will immediately send an ack to the master database. After receiving the ack, the master library thinks that this process is complete and can do something else. At this time, the slave library will update the mast info information, update the binglog location information to 1080, next time start looking down from 1080, and then write the tcp/ip cached log to the relaylog. Finally, the sql thread thread will read the relay-log.info and obtain the location information of the last execution of the binglog log. For example, when it is found that the last time and the execution to 640, sql will read the relaylog binary log from the 640. when the execution is finished, the relay-log.info file will be updated to record the location of the last execution. finally, relaylog will automatically clean up the executed binary log so that the replication is completed.

Threads and files in replication

2.1, main library

Dump (IO) thread: the thread that sends binary logs to the main library during replication

2.2, slave library

IO thread: a thread that requests binary logs from the main library and accepts binary logs

SQL thread: a binary thread that executes the request

2.3, main library

Binlog file: binary log of the main library

2.4, slave library

Relaylog: relay log, which stores the requested binary log

Master.info:

1. Important parameters of connecting slave library to master library (user,passwd,ip,port)

2. The location of the binary log of the main database obtained last time

Relay-log.info

Stores the location of relaylog logs that have been executed by the SQL thread from the library

Second, mysql master-slave building

Master-slave replication premise

1. More than two MySQL instances (can be multiple physical machines or mysql instances)

2. The main database needs to open the binary log.

3. In order to provide users related to replication, the main library needs to use a special permission of replication slave.

4. The slave database needs to append the data different from the master database. In general, it is considered that the database should be backed up and restored to the slave database.

5. The slave library should automatically obtain the binary log from the master database from the point in time after recovery and start to automatically synchronize the master database data. We need to tell the slave library where to copy the binary log to learn.

Master-slave replication and construction practice

1. Environmental preparation

Two MySQL instances

3307:master

3308:slave

2. Open the main library binlog and relay-log from the slave library (generated under the data directory by default)

Vim / data/3307/my.cnf

Log-bin=/data/3307/mysql-bin

Binlog_format=row

3. Server-id is different.

[root@db02 data] # cat / data/3307/my.cnf | grep server-id

Server-id=3307

[root@db02 data] # cat / data/3308/my.cnf | grep server-id

Server-id=3308

4. Disable automatic domain name resolution in the database (all node instances are added)

Skip-name-resolve

5. Start multiple instances

Mysqld_safe-- defaults-file=/data/3307/my.cnf &

Mysqld_safe-- defaults-file=/data/3308/my.cnf &

6. Create a copy account and connect to the main library

Mysql-S / data/3307/mysql.sock

Grant replication slave on. To repl@'10.0.0.%' identified by '123'

7. Append data from the library

(1) situations where no additional addition is required

In the new environment built by master and slave at the same time, there is no need to back up the master database data and restore it to the slave database, directly from the beginning of the first binlog (mysql-bin.000001).

(2) if the master database has been working for a long time, we generally need to back up the data of the master database, restore it to the slave database, and then copy the database automatically from the point of time of backup.

Mysqldump-S / data/3307/mysql.sock-A-R-triggers-- master-data=2-- single-transaction > / tmp/full.sql

Sed-n '22p' / tmp/full.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=325

Restore to the slave library:

Mysql-S / data/3308/mysql.sock

Mysql > set sql_log_bin=0

Mysql > source / tmp/full.sql

8. Open the master library from the library

Mysql-S / data/3308/mysql.sock

Help change master to

CHANGE MASTER TO

MASTER_HOST='10.0.0.52'

MASTER_USER='repl'

MASTER_PASSWORD='123'

MASTER_PORT=3307

MASTER_LOG_FILE='mysql-bin.000002'

MASTER_LOG_POS=325

Turn on master and slave (start IO/SOL thread)

Start slave

9. Check the master-slave status

Show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

10. Introduction of master-slave important status information

Show slave status\ G

Slave_IO_Running: Yes (io thread status)

Slave_SQL_Running: Yes (sql thread status)

Last_IO_Errno: 0 (io thread exception status code)

Last_IO_Error: (io thread exception details)

Last_SQL_Errno: 0 (sql thread status code)

Last_SQL_Error: (sql thread exception details)

Third, the solution to common exceptions in master-slave replication IO thread failure

1. The main library cannot be connected.

Check user,password,port,IP, network, walls

Stop slave

Reset slave all; (empty configuration)

Chagen master to

Start slave

2. The binaries of the main library are missing or corrupted

Solution scheme

Stop slave

Reset slave all

Restore from a new backup

Change master to

Start slave

SQL thread failure

Execute relaylog log new and new events

1. When deleting or modifying an object, there is no such object.

2. When the object is created, the object already exists

3. Primary key conflict

Writing from the library will lead to the above problems

The handling method skips this error

Stop slave

Set global sql_slave_skip_counter=1

Start slave

/ etc/my.cnf

Slave-skip-errors=1032,1062,1007

However, the above operations are sometimes risky, and the safest way is to build new masters and slaves from scratch.

How to prevent it?

Change from library to read-only library

Set global read_only=1

Vim / etc/my.cnf

Read_only=1 (this parameter can only control ordinary users)

The master-slave delay is too long.

Show slave status\ G

Seconds_Behind_Master:0

The default master-slave replication is an asynchronous process

Main library reason

1. The binary log will not be recorded until the main library has made a modification operation.

2. The pressure on the main library is very great (big business, many things).

3. The large number of slave libraries causes the domp thread to be busy.

From the library reason:

1. Relay-log write is slow

2. The SQL thread is slow (there is a big difference between master and slave hardware)

Solution idea

Main library

1. Sync_binlog=1 (1 means that as long as the main database makes a commit, the binary log will be flushed to disk immediately. If it is equal to 0, it should be decided according to the system binlog)

2. Big things are divided into small things, and many things are separated.

3. Use multi-level master-slave, sub-library and sub-table architecture.

4. Put binlog on ssd or flash for high performance storage

Slave library

1. Put relay on ssd or flash

2. Try to choose the same hardware configuration as the main library

Master-slave replication other features semi-synchronous replication

1. The cause of occurrence

Ensure data consistency, security considerations

By default, the MySQL master-slave backup is backed up asynchronously. When the slave database requests the binary log from the master database, the slave database will store the requested binary log in the tcp/ip cache and return an ack to tell the master database that the binary log has been received. Imagine that when the slave library is suddenly powered off, the binary log stored in the tcp/ip cache will be lost. And the master library also thinks that the binary log is received normally from the library, and the data from the library will be lost. the semi-synchronous implementation method is that when the binary log is received from the library, it must be stored in the relaylog landing to the disk before the slave ack will be sent to the master library, thus ensuring the consistency of the data.

Semi-synchronous replication is a concept that appeared in 5.5. but the poor performance of semi-synchronous replication in 5.5is not recommended. Group commit group commit function appears after 5.6to improve the performance of open version synchronous replication. 5.7.Enhancement of semi-synchronous replication new features: after sync; implementation

Load plug-in

Lord:

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'

From:

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'

Check to see if the load is successful:

Show plugins

Start:

Lord:

SET GLOBAL rpl_semi_sync_master_enabled = 1

From:

SET GLOBAL rpl_semi_sync_slave_enabled = 1

Restart the IO thread on the slave library

STOP SLAVE IO_THREAD

START SLAVE IO_THREAD

Check to see if it is running

Lord:

Show status like 'Rpl_semi_sync_master_status'

From:

Show status like 'Rpl_semi_sync_slave_status'

Add:

Rpl_semi_sync_master_timeout | 10000

By default, after 10 seconds, there is no ack, and the master-slave relationship is automatically switched to normal replication.

If it is a 1-master and multi-slave semi-synchronous replication, as long as one landing relaylog returns ack, this semi-synchronization will be completed.

Delay from the library

To prevent the logical damage of the database, suppose that if a table is accidentally deleted in the master database, this event will also be recorded in the binary log and sent to the slave database. At this time, the slave database will not immediately perform this operation. For example, the slave database will not delete the table until 3 hours later, which gives us some buffer time. The delay slave database is the delay of the sql thread.

It will find a special node and configure it as a delay node to prevent logic damage as much as possible. In general, this node will be used for backup.

Configuration implementation

Delay of SQL_thread

Mysql > stop slave

Mysql > CHANGE MASTER TO MASTER_DELAY = 60

Mysql > start slave

Mysql > show slave status\ G

SQL_Delay: 300

Cancel the delay:

Mysql > stop slave

Mysql > CHANGE MASTER TO MASTER_DELAY = 0

Mysql > start slave

Copy filtering

The reason is that sometimes we don't want to copy all the data in the main database, we just want to copy a library or some tables in a library.

Control of the main library (not recommended):

Whitelist: records only the binary logs of the libraries listed in the whitelist

Binlog-do-db

Blacklist: do not record the binary log of the library listed in the blacklist

Binlog-ignore-db

Control from the library side:

Whitelist: execute only the relay logs of the libraries or tables listed in the whitelist

-- replicate-do-db=test (which library)

-- replicate-do-table=test.t1 (which table of which library)

-- replicate-wild-do-table=test.x* (fuzzy match)

Blacklist: relay logs that do not execute the libraries or tables listed in the blacklist

-- replicate-ignore-db

-- replicate-ignore-table

-- replicate-wild-ignore-table

Copy only the data from the world database

In the slave library configuration

Vi / etc/my.cnf

Replicate-do-db=world

Restart the slave library

Query: show slave status\ G

Replicate-do-db = world

A new master-slave replication mode-GTID replication

GTID is a new replication feature since 5.6. the previous replication mode is that when the master database changes in any way, the form of events will be recorded in the binlog log, each event generates a position number, and then gets these binary events from the library. GTID generates a global unique number for each complete thing separately, which simplifies the configuration of master-slave replication, and the implementation process is relatively simple to write.

Its official definition is as follows:

GTID = source_id: transaction_id

7E11FA47-31CA-19E1-9E56-C43AA21293967:29

The first half is UUID, and the second half is the number of things, which is stored under auto.cnf

Configuration implementation

1. Important parameters

Gtid-mode=on (whether to enable GTID)

Enforce-gtid-consistency=true (force the consistency of GTID)

Log-slave-updates=1 (whether slave updates are written to the log)

2. Planning

Main library: 10.0.0.51 Universe 24

From the library 1: 10.0.52 Universe 24

From library 2vl 10.0.53 Unix 24

3. Write to the configuration file

Main library:

Add the following configuration information

Db01:10.0.0.51/24

Vim / etc/my.cnf

[mysqld]

Basedir=/application/mysql

Datadir=/application/mysql/data

Socket=/tmp/mysql.sock

Log-error=/var/log/mysql.log

Log_bin=/data/binlog/mysql-bin

Binlog_format=row

Skip-name-resolve

Server-id=51

Gtid-mode=on

Enforce-gtid-consistency=true

Log-slave-updates=1

[client]

Socket=/tmp/mysql.sock

Slave1:

Db02:10.0.0.52/24

Vim / etc/my.cnf

[mysqld]

Basedir=/application/mysql

Datadir=/application/mysql/data

Socket=/tmp/mysql.sock

Log-error=/var/log/mysql.log

Log_bin=/data/binlog/mysql-bin

Binlog_format=row

Skip-name-resolve

Server-id=52

Gtid-mode=on

Enforce-gtid-consistency=true

Log-slave-updates=1

[client]

Socket=/tmp/mysql.sock

Slave2:

Db02:10.0.0.53/24

Vim / etc/my.cnf

[mysqld]

Basedir=/application/mysql

Datadir=/application/mysql/data

Socket=/tmp/mysql.sock

Log-error=/var/log/mysql.log

Log_bin=/data/binlog/mysql-bin

Binlog_format=row

Skip-name-resolve

Server-id=53

Gtid-mode=on

Enforce-gtid-consistency=true

Log-slave-updates=1

[client]

Socket=/tmp/mysql.sock

The three nodes initialize the data respectively:

/ application/mysql/scripts/mysql_install_db-user=mysql-basedir=/application/mysql-datadir=/application/mysql/data/

Start the mysql of the three nodes:

/ etc/init.d/mysqld start

Test startup:

Mysql-e "show variables like 'server_id'"

Master:51

Slave:52,53

51:

Grant replication slave on. To repl@'10.0.0.%' identified by '123'

52\ 53:

Change master to master_host='10.0.0.51',master_user='repl',master_password='123', MASTER_AUTO_POSITION=1

Start slave

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.