MySQL master-slave replication, mysql master-slave

MySQL master-slave replication, mysql master-slave

Original article published on cu:

Reference:

This article involves the principle of MySQL master-slave replication and a simple verification of master-slave replication.

I. MySQL master-slave replication Principle 1. Master-slave replication architecture Diagram

Three threads in master-slave replication:

2. Master-slave replication process

General process: when a transaction is committed, the primary database uses the change as the event record (Events) to the binary file (binlog), and obtains the binary log (binlog) from the IO thread of the database from the primary database ), save it as a relay-log locally, and then execute the content in the relay log on the slave database through the SQL thread to make the slave database consistent with the master database.

The detailed process is as follows:

Ii. Verify the environment 1. Operating System

CentOS-6.7-x86_64

2. MySQL version

MySQL is 5.6.36: https://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.36.tar.gz

3. Topology

Iii. master database configuration 1. my. cnf Configuration

[Root @ master ~] # Vim/etc/my. cnf [mysqld] # The ID value uniquely identifies the master and slave servers in the cluster. The master_id must be a positive integer between 1 and-1, the slave_id value must be a positive integer between 2 and 232-1. server_id = 196 # enable binlog. Only after binlog is enabled can I/O be written to the Slave relay-log, which is the prerequisite for replication. log_bin =/mysql-bin # sets the maximum value of the binlog file. When this value is reached, a new binlog file is automatically generated. max_binlog_size = 1G # binlog occupies a large amount of disk space. You can set the binlog file expiration time, in days. # expire-logs-days = # configure whether to refresh the binlog to the disk for each transaction commit. The default value is 0, which is controlled by the file system. If it is set to 1, by default, binlog is refreshed to the disk each time a transaction is committed. # Cause A binlog also has a cache. When a transaction is committed, the cache is written first. If the system suddenly goes down, the records in the cache may be lost. However, writing to the disk for each transaction commit may affect the performance. # semi-synchronous replication can solve the binlog cache data loss caused by sudden system downtime. sync_binlog = 0 # There are three ways to record binary logs: row, STATEMENT and mixed # row. row-based replication records change operations for each row. Advantages: high compatibility with replication, disadvantages: large log records have a great impact on IO and are not easy to use for analysis. # STATEMENT, STATEMENT-based replication, and SQL statements used to record operations, is the default format. Advantages: the log volume is small, which is easy to use for analysis. The IO impact is small. Disadvantages: time may not be fully synchronized, resulting in deviation. the user who executes the statement may also be a different user; # mixed, in mixed mode, the STATEMENT record is used by default. When an uncertain function appears, the row record is used. binlog-format = mixed # write only changes to the specified database Binary File binlog. If multiple databases can be separated by commas (,), or multiple binlog-do-db options are used. # If no database exists in databases, enabling binlog-do-db will cause mysql to fail to start. # binglog-do-db = # do not write binary logs to changes to the specified database. If multiple databases can be separated by commas, or multiple binlog-ignore-db options are used. binlog-ignore-db = information_schema, mysql, cece_schema, test # specifies the database to be copied and synchronized. If multiple databases are separated by commas, you can also use multiple replicate-do-db options # replicate-do-db = # To specify databases that do not require replication and synchronization. If multiple databases are separated by commas, or use multiple replicate-ignore-db options. # replicate-ignore-db = # The configuration file takes effect after restart [root @ master ~] # Service mysqld restart

2. Create a copy user

# Authorize the host in the 10.11.4.0 network segment on the master database, and obtain the replication slave permission from the Database User repl [root @ master ~] # Mysql-uroot-pEnter password: mysql> grant replication slave on *. * TO 'repl' @ '10. 11.4.% 'identified by 'repl'; mysql> flush privileges;

3. Refresh the table and set read-only

# Lock the table to prohibit writing new data; # (executed as appropriate), set read-only to prevent the binlog file name and offset from being obtained, or from the master database to the slave database backup, the master database has changed. Do not forget to unlock it after the first synchronization is completed. [root @ master ~] # Mysql-uroot-pEnter password: mysql> flush tables with read lock;

4. Get the file name and offset of the master binlog.

# Get the binlog file name and offset. You can set the synchronization replication point for the slave database [root @ master ~] # Mysql-uroot-pEnter password: mysql> show master status;

5. Unlock read-only lock

# Unlock read-only locks [root @ master ~] # Mysql-uroot-pEnter password: mysql> unlock tables;

6. iptables

[root@master ~]# vim /etc/sysconfig/iptables-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT[root@master ~]# service iptables restart

Iv. Slave Database Configuration 1. my. cnf Configuration

# Copy the my. cnf file on the master database server to the slave Database Server [root @ master ~] # Scp/etc/my. cnf slave:/etc/Enter password: # modify the server id value to be different from that of the master database server, and keep the remaining configurations unchanged. # You can configure slave_skip_errors = [err_code1, err_code2 ,... | All] parameter; # During the replication process, the SQL statement in the binlog fails due to various reasons. By default, the slave database stops copying data and requires user intervention; # You can set slave-skip-errors to define the error number. If a defined error number is encountered during the replication process, you can skip it. If the slave database is used for backup, if this parameter is set, data inconsistency exists. We recommend that you do not use it. If the slave database shares the query pressure on the master database, you can use it. [Root @ slave ~] # Vim/etc/my. cnf [mysqld] # The ID value uniquely identifies the master and slave servers in the cluster. The master_id must be a positive integer between 1 and-1, the slave_id value must be a positive integer between 2 and 232-1. server_id = 198 # Use -- skip-slave-start to start the slave Database Replication thread, which is convenient for subsequent configuration operations [root @ slave ~] # Service mysqld stop [root @ slave ~] # Mysqld_safe -- skip-slave-start &

2. Configure Synchronization

# Configure the parameters submitted from the slave database to the master database. If the parameters are incorrect, you can reconfigure them; # master-host, master-user, master-password, master-port, etc. can also be in my. specify in the cnf file; # "start slave" to start replication. [Root @ slave ~] # Mysql-uroot-pEnter password: mysql> change master to master_host = '10. 11.4.196 ', master_user = 'repl', master_password = 'repl', master_log_file = 'mysql-bin.000001', master_log_pos = 120; mysql> start slave;

3. Set slave database read-only

# The Master-slave replication framework is basically complete, but the slave database can also write data; # If a user writes data to the slave database and then synchronizes the data from the master database, this may cause data disorder and data corruption. Therefore, you need to set the slave database to read-only [root @ slave ~]. # Mysql-uroot-pEnter password: mysql> set global read_only = 1; mysql> show global variables like 'read % ';

5. Verification 1. View thread 1) master database

[root@master ~]# mysql -uroot -pEnter password:mysql> show processlist\G;

2) slave Database

[root@slave ~]# mysql -uroot -pEnter password:mysql> show processlist\G;

2. View slave database status

[root@slave ~]# mysql -uroot -pEnter password:mysql> show slave status\G;

# The significance of slave status indicators is as follows (Red-bold indicators are important):Slave_IO_State: the I/O thread has been connected to the master, waiting for the binary log event to arrive at Master_Host: master ipMaster_User: the user who connects to the master Master_Port: master port Connect_Retry: when the master-slave connection is established again, if the connection fails, the Retry Interval is 60 s by default. Master_Log_File: the master binary log file Read_Master_Log_Pos currently being read by the I/O thread: Relay_Log_File: the 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 master binary file Slave_IO_Running executed by the SQL thread: i/O whether the thread is running and successfully connected to masterSlave_ SQL _Running: whether the SQL thread is runningReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table, replicate_Wild_Ignore_Table: the statement that matches the specified database and table name mode does not need to be copied. It matches the given wildcard mode statement.Last_Errno: Error Code Last_Error: error messageSkip_Counter: SQL _SLAVE_SKIP_COUNTER value Exec_Master_Log_Pos: Relay_Log_Space: Relay Log File Size Until_Condition: The value Until_Log_File specified in the UNTIL clause of the START SLAVE statement: used to indicate 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; otherwise, the value is Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Key:Seconds_Behind_Master: an indicator of the number of slave nodes that slave lags behind. This status is an important performance indicator. Normally, it is 0./The O thread cannot connect to the master. The result is null.Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: The latest IO thread error code, of which 2003 represents IThe/o thread cannot connect to the master server Last_IO_Error: The latest IO thread error message (for example, error reconnecting to master 'repl @ 192.168.1.6: 100'-retry-time: 60 retries: 3)) Last_ SQL _Errno: The latest SQL thread error code Last_ SQL _Error: The latest SQL thread error messageReplicate_Ignore_Server_Ids: Master_Server_Id: master's server IDMaster_UUID: master's UUID value Master_Info_File: slave's master.info file pathSQL _Delay: positive number indicates that slave has delayed SQL _Remaining_Delay: integer indicates the delay time Slave_ SQL _Running_State: the SQL thread running status. The SQL thread has processed all the events in the relay log file and is waiting for I/O threads write new events to relay logsMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp: the most recent I/O thread error time Last_ SQL _Error_Timestamp: the time when the most recent SQL thread reports an errorMaster_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0

3. Three files in the slave Database

Three files are generated in the slave database path: master.info, relay-log.info, relay-bin

Master.info: records the master's ip address, account, password, and the position of the binglog currently read from the library's I/O thread to the master database.

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

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

4. view the synchronization status of the new data database. 1) Create a database and a table in the master database.

[root@master ~]# mysql -uroot -pEnter password:mysql> create database dbtest;mysql> use dbtest;mysql> create table tabtest(id int);mysql> insert into tabtest() values(1),(2);

2) view databases and tables from the database

[root@slave ~]# mysql -uroot -pEnter password:

(1) view the database

mysql> show databases;

(2) query a table

mysql> select * from dbtest.tabtest;

(3) slave status

mysql> show slave status\G;

6. Supplement (without verification): semi-synchronous Replication

MySQL supports semi-synchronous replication as a plug-in starting from MySQL5.5.

Refer:

Http://www.cnblogs.com/chenmh/p/5744227.html

Https://dev.mysql.com/doc/refman/5.6/en/replication-semisync-interface.html

1. Concept 1) Asynchronous replication)

By default, MySQL replication is asynchronous. After the transaction committed by the client is executed, the master database immediately returns the result to the client, regardless of whether the slave database has been received and processed. If the master database crashes, the committed transactions on the master database may not be uploaded to the master database. If the master database is promoted to the master database, the data on the master database may be incomplete.

2) full synchronization replication (Fully synchronous replication)

After a transaction is executed in the master database, all slave databases execute the transaction and return it to the client. Because it is necessary to wait for all slave databases to execute this transaction before returning, the performance of full synchronization replication will be severely affected.

3) Semisynchronous replication)

Between asynchronous replication and full synchronous replication, the master database does not return to the client immediately after the transaction committed by the client is executed.Wait for at least one slave database to receive and writeRelay logTo the client.. Compared with asynchronous replication, semi-synchronous replication improves data security and also causes a certain degree of delay. This delay is at least the round-trip time of a TCP/IP. So,We recommend that you use semi-synchronous replication in small transactions and Low-latency networks to achieve zero data loss when performance is low..

2. Notes