MySQL replication and mysql Replication

MySQL replication and mysql Replication

Overview

This article mainly introduces the building process of mysql master-slave and some concepts involved in the middle, and hopes to provide the most comprehensive overview of the knowledge of mysql master-slave. mysql has been installed in the environment, the Installation Method of mysql is not described here.

Test environment:

MASTER: mysql (5.6.21), linux: redhat 6.0, ip: 192.168.1.6

From: mysql (5.6.21), linux: redhat 6.0, ip: 192.168.1.7

Principles and concepts master-slave replication principles

1) when a transaction is committed, the master database uses the change as an event record (Events) to Binlog.

2) the master database pushes the events in the binary file to the Relay-bin log file of the slave database, and the slave database performs the change operation based on the events in the Relay log.

Thread

Binlog Dump thread: This thread runs on the master database. After the master and SLAVE databases are configured, start slave runs to START replication, and a Binlog Dump thread is generated on the master database, the main function of this thread is to read the Binlog events of the master database and send them to the slave database (the I/O thread of the slave database ).

I/O thread: This thread runs on the slave database, the I/O thread is used to send data to the master database and write the change events sent from the master database to the slave database relay logs.

SQL thread: This thread runs on the slave database. The main function of this thread is to read the change events in the relay log and update the slave database.

 

This figure comes from the mysql database development book.

 

Step master database

The master database is running, and the configuration file of the master database has been configured.

1. Configure my. cnf

 

server-id=6log-bin=/var/lib/mysql/mysql-binmax_binlog_size = 100Msync_binlog=0binlog-format=MIXEDbinlog-ignore-db=testreplicate-ignore-db=test

The server-id must be unique. The current IP host is set by default.

Log-bin enables binlog and configures the path. It is disabled by default.

Max_binlog_size sets the maximum value of the binlog file. The maximum value is 100 mb. When this value is reached, a new binlog file is automatically generated. Of course, the generated environment is set to be larger than this one.

Sync_binlog: Configure whether to refresh the binlog to the disk for each transaction commit. The default value 0 indicates that the binlog is not refreshed every time and is controlled by the file system, if it is set to 1, binlog will be refreshed to the disk each time the transaction is committed by default. The advantage is that when the system suddenly goes down, the system damage will be less, because binlog also has cache, the default transaction commit is to write the cache first, so when the system suddenly goes down, the records in the cache may be lost. However, if the disk is written for each transaction commit, the performance will be affected, semi-synchronous replication can be used to solve the problem of binlog cache data loss caused by sudden system down.

Binlog-format: binary log record method. There are three methods: row (record the change operation of each row, advantage: high compatibility with replication, disadvantage: a large number of log records, it has a great impact on IO and is not easy to use for analysis. STATEMENT (the SQL STATEMENT used to record operations, which is also the default format. Advantage: the log volume is small, which is easy to use for analysis, i/O has a small impact. Disadvantages: replication errors may occur, for example, some functions that are sometimes used. MIXED (MIXED with the above two formats, uses the STATEMENT record by default, when there is an uncertain function, the row record is used, for example, curret_user (), now)

Binlog-ignore-db: The binlog of the specified database is not recorded. If multiple databases are specified, multiple rows can be repeated in the configuration file. If binglog-do-db is configured for a long time, only the specified binlog of the database is not recorded in other databases.

Replicate-ignore-db: do not copy the binlog of the specified database

2. Create a copy user

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.7' IDENTIFIED BY 'repl';

Run the command on the master database and grant the replication slave permission of the user repl to the 192.168.1.7 server.

3. Refresh the table and set the database read-only

FLUSH TABLES WITH READ LOCK;

The current master database can only read but cannot update

4. Record the binary file name and offset of the master database

SHOW MASTER STATUS;

The purpose of logging the log name and the cheap volume is to use

 5. Back up the master database

To stop the master database service, you need to back up the master database to the slave database.

service mysql stop

There are many backup methods: 1. If the master database cannot stop the service online, you can use hot backup tools such as dump, ibbackup, and xtrabackup to back up the database and restore it to the slave database.

2. If the master database allows to stop the service, you can directly copy all files in the data directory of the master database cp to the slave database path, and you can use the xftp tool for convenience.

After completing this step, you can restart the master database service.

Slave Database

Here, the service from the database is stopped.

1. Configure my. cnf

log-bin=/var/lib/mysql/mysql-binserver-id=7

max_binlog_size = 100Msync_binlog=0

binlog-format=MIXEDbinlog-ignore-db=testreplicate-ignore-db=test

Note that the server-id cannot be the same as the master

2. Use -- skip-slave-start to start the slave Database Service

The purpose of using -- skip-slave-start to start the replication process from the server immediately is to facilitate subsequent configuration operations.

mysqld_safe --skip-slave-start &

3. log on to mysql

mysql -uroot -p123456

Log on to mysql and perform the following operations:

change master to  master_host = '192.168.1.6',  master_user = 'repl',  master_password = 'repl',  master_log_file = 'mysql-bin.000046',  master_log_pos = 211991;

Start the slave thread

start slave

4. Check

Execute on slave Database

show processlist\G;

Indicates that the master has been connected.

 

Test

Run

# Create a repltest database on the master database and create a test table under the database to insert data create database repltest; use repltestcreate table test (id int); insert into test () values (1 ), (2); # test the creation of the norepl table in the existing test database and the insertion of data use testcreate table norepl (id int); insert into norepl () values (1 );

Run in slave Database

select * from repltest.test;

select * from test.norepl;

Results All data operations except the test database will not be copied to the slave database. This is also in line with the previous settings.

 

Supplement

 Slave server startup parameters

-Read_only: This option allows the slave server to only allow updates from the slave server thread or users with SUPER permissions. The slave server does not accept updates from customers. -Replicate_do_db = db_name indicates that the slave server only copies statements with the default database (selected by USE) as db_name. To specify multiple databases, you must use this option multiple times. Each database is used once. Note that the statement-replicate_do_table = db_name.tbl_name of cross-database is not copied, which indicates that the slave server thread only copies the specified table. To specify multiple tables, use this option multiple times. Each table is used once. Compared with-replicate-do-db, cross-database update is allowed. -Replicate_ignore_db = db_name indicates that the slave server does not copy the statement where the default database (selected by USE) is db_name. To ignore multiple databases, you must use this option multiple times and use it once for each database. -Replicate-ignore-table = db_name.tbl_name tells the slave server thread not to copy or update any statements of the specified table (even if the statement may update other tables ). To ignore multiple tables, use this option multiple times. -Replicate_wild_do_table = db_name.tbl_name tells the slave server thread to restrict the replication and update of the table to match the specified database and table name mode statement. The pattern can contain wildcards '%' and '_', which have the same meaning as the LIKE pattern matching operator. To specify multiple tables, use this option multiple times. Each table is used once. This option can be updated across databases. -Replicate_wild_ignore_table = db_name.tbl_name indicates that the slave server thread does not copy a table statement that matches the given wildcard pattern. To ignore multiple tables, use this option multiple times. This option can be updated across databases. -Replicate_rewrite_db = from_name-> to_name indicates that if the slave server uses the default database (selected by USE) as the from_name on the master server, it is translated as to_name. Only the host name or IP address reported to the master server during Server registration is affected. This value appears in the output of show slave hosts on the master server. If you do not want the slave server to register on the master server, do not set this value. -Report_port = slave_port: Specifies the TCP/IP Port Number of the slave server, which is reported to the master server during Server registration. -Skip_slave_start indicates that the slave server does not start the slave server thread when the server is started. Use the start slave statement to START the thread later. -Slave_skip_errors = [err_code1, err_code2 ,... | All] Generally, when an error occurs, the replication stops. This gives you a chance to manually solve the inconsistency problem in the data. This option tells the SQL thread of the slave server to continue copying when the statement returns an error in any option value

 

Replication thread status

You can use show processlist \ G to view the status of the replication thread. Common thread statuses include: (1) Master Server Binlog Dump thread Has sent all binlog to slave; the waiting for binlog to be updated thread has read all major updates from the binary log and sent them to the slave server. The thread is currently idle, waiting for a new event in the binary log caused by a new update on the master server. (2) The Waiting for master to send event thread in the I/O thread status of the slave server has been connected to the master server and is Waiting for the arrival of the binary log event. If the master server is idle, it will take a long time. Timeout occurs if the waiting duration is slave_read_timeout seconds. In this case, the thread considers the connection to be interrupted and attempts to reconnect. (3) the Reading event from the SQL thread status of the server from the relay log thread has read an event from the relay log and can process the event. Has read all relay log; waiting for the slave I/O thread to update it thread Has processed all events in the relay log file, it is waiting for the I/O thread to write new events to the relay log.

 

File

In the database path from the library, three files are generated: master.info, relay-log.info, relay-bin

Master.info: used to record the position of the binglog currently read from the I/O thread of the database to the master database.

Relay-log.info: used to record the position of the relay-bin currently read from the SQL thread of the database.

Relay-bin: relay log. The relay log record format is the same as the binary log of the master database, however, the relay log will delete the content in the relay log after the SQL thread executes the event in the current relay log.

 

Slave Database Replication status

You can use show slave status \ G to view the slave Database

*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.1.6                  Master_User: repl                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000047          Read_Master_Log_Pos: 763952               Relay_Log_File: localhost-relay-bin.000003                Relay_Log_Pos: 764115        Relay_Master_Log_File: mysql-bin.000047             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: 763952              Relay_Log_Space: 764455              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: 0Master_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: 6                  Master_UUID: d58e2793-8534-11e5-b224-000c2908cc04             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

 

Slave_IO_State: the thread has been connected to the master server and is waiting for the binary log event to arrive at Master_Host: master server ipMaster_User: Connection user Master_Port: master server port Connect_Retry: when you re-establish a master-slave connection, if the connection fails to be established, retry after the interval, the default value is 60 s. Master_Log_File: the name of the binary log file of the master server that the I/O thread is reading. Read_Master_Log_Pos: the position where the I/O thread has read in the binary log of the master server. Relay_Log_File: name of the relay log file currently being read and executed by the SQL thread Relay_Log_Pos: the location where the SQL thread has read and executed in the current relay log. Relay_Master_Log_File: the binary file Slave_IO_Running of the master server executed by the SQL thread: whether the I/O thread runs and successfully connects to the master server. Slave_ SQL _Running: whether the SQL thread is running. Replicate_Do_DB: indicates the database to be copied. Replicate_Ignore_DB must be configured in the configuration file: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: replicate_Wild_Ignore_Table: do not copy the statement Last_Errno: Error Code Last_Error: error message Skip_Counter: SQL _SLAVE_SKIP_COUNTER value Exec_Master_Log_Pos: Relay_Log_Space: Relay Log File Size Until_Condition: value specified in the UNTIL clause of the START SLAVE statement Until_Log_File: Used to indicate Indicates the log file name Until_Log_Pos: Location Value Master_SSL_Allowed: If SSL connection to the master server is allowed, the value is Yes or else: The following are some SSL connection information Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: master_SSL_Key: Seconds_Behind_Master: this field is an indicator of the number of slave servers falling behind (this status is a very important performance indicator, normally 0, if the I/O thread of the slave server cannot connect to the master server, null is displayed.) Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: The latest IO thread error code. 2003 indicates that the I/o thread cannot connect to the master server last_io: the latest IO thread error message (for example, error reconnecting to ma) Ster 'repl @ 192.168.1.6: 3306 '-retry-time: 60 retries: 3) Last_ SQL _Errno: latest SQL thread error code Last_ SQL _Error: latest SQL thread error message Replicate_Ignore_Server_Ids: Master_Server_Id: master server IDMaster_UUID: master server UUID value Master_Info_File: From the master.info file path SQL _Delay: positive number indicates that slave has delayed SQL _Remaining_Delay: an integer indicates the delay time Slave_ SQL _Running_State: SQL thread running status (the SQL thread has processed all the events in the relay log file and is waiting for the I/O thread to write new events to the relay log .) Master_Retry_Count: 86400Master_Bind: latest: Latest I/O thread error time: the time when the most recent SQL thread reports an error Master_SSL_Crl: Master_SSL_Crlpath: Failed: Executed_Gtid_Set: Auto_Position: 0

Note the following important statuses: The statuses before Slave_ SQL _Running are added with pending, Last_IO_Error, Last_ SQL _Errno, Last_ SQL _Error, SQL _Delay, SQL _Remaining_Delay, closing, closing, and closing.

Summary

In an enterprise environment, master-slave replication is the most basic and common form, and configuration management is relatively simple. The above is about one master node and one slave node. On this basis, you only need to add a slave server to achieve a master node and multiple slaves structure. However, this structure has high requirements on the reliability of the master server, in this case, master-master replication is available. You will spend some time writing an article about Master-master replication later.

 

 

 

Note: Author: pursuer. chen Blog: http://www.cnblogs.com/chenmh All essays on this site are original. You are welcome to repost them. However, you must indicate the source of the article and clearly provide the link at the beginning of the article. Otherwise, you will be held accountable. Welcome to discussion

 

--- Restore content end ---