Principle of master-slave replication

Binlog and relay logs

  • Bin the log:

    Bin log records all data changes and can be used for local data recovery and master/slave synchronization

  • Relay log:

    • The primary Mysql node writes the binlog to the local Mysql node. The secondary Mysql node requests the incremental binlog. The primary Mysql node synchronizes the binlog to the secondary Mysql node

    • A separate process from the node copies the binlog to the local relaylog

    • The slave node periodically reruns the relay log

Three modes of binlog

The statement level model

Every SQL that modifies data is logged in the master’s bin-log. When the slave is replicated, the SQL process parses the same SQL as the original SQL executed by the master and executes it again

  • Advantages:

    The advantages of statement level are that row level does not need to record the changes of each row, reducing the amount of bin-log logs, saving I/O, and improving performance. Because he only needs to record the details of the statement being executed on the master, and information about the context in which the statement was executed

  • Disadvantages:

    Since it is a recorded execution statement, in order for the statement to be executed correctly on the slave side, it must also record some information about the execution of each statement, namely the context information, to ensure that all statements executed on the slave side get the same result as those executed on the master side. In addition, due to the rapid development of mysql, a lot of new functions are added, which makes the replication of mysql encounter no small challenge. The more complex the content involved in natural replication, the more likely bugs will appear. At the statement level, there are many cases of mysql replication problems, mainly caused by the use of certain functions or functions when modifying data. For example, sleep() cannot be copied correctly in some versions

Rowlevel mode

The modified data of each row is recorded in logs, and the same data is modified on the slave side

  • Advantages:

    Bin-log does not record context-specific information about the SQL statement being executed, but only which record was modified and to what extent. So the contents of the row level log clearly record the details of each row. And there are no specific cases where stored procedures, or functions, or trigger calls and triggers cannot be copied correctly

  • Disadvantages:

    At row level, all statements executed are logged as changes per row. This can result in a large amount of log content, such as an UPDATE statement like this: Update product set owner_member_id=’d’where owner_member_id=’a’; It is the change in each record that is updated by the statement, thus recording the number of events in which many records are updated. Naturally, the volume of bin-log logs is large

Mixed mode

In mixed mode, mysql will choose between statement and row according to the specific SQL statement it executes. Statement level is the same as before and only records statements executed. Not all changes are recorded in row level mode. For example, when a table structure changes, the statement mode is recorded in statement mode. If the SQL statement is an update or delete statement, the row level mode is optimized. All row changes will still be logged

Open the binlog

Modify the my.cnf file

Add it under the [mysqld] section

Sync_binlog =1 # database to be backed up binlog-do-db=hello # database not to be backed up binlog-ignore-db=mysql # Start binary log-bin=mysql-bin # server ID server-id=132Copy the code

Sync_binlog parameters

0: the storage engine does not flush binlogs to disk, but the file system of the operating system controls cache flushing

1: Each time a transaction is committed, the storage engine calls the sync operation of the file system to refresh the cache. This method is the safest but has low performance

N: When the submitted log group is equal to n, the storage engine invokes sync of the file system to refresh the cache

If sync_binlog=0 or sync_binlog is greater than 1, the transaction is committed but has not yet been synchronized to disk. Therefore, it is possible that the server has committed to not synchronizing some transactions to the binary log during a power failure or operating system crash. Therefore it is not possible to perform routine recovery of these transactions and they will lose binary logs

Adjust the binlog mode

  • View the log mode of binlog

    show variables like 'binlog_format';

  • Adjust the log mode of binlog

    The three formats of binlog are STATEMENT, ROW, and MIXED

    set binlog_format=STATEMENT;

View bin logs and relay logs

Mysqlbin.000005 = mysqlbin.000005 = mysql.bin.000005 = mysql.bin.000005 = mysql.bin.000005 = mysql.bin.000005 = mysql.bin.000005 Mysqlbinlog mysql-bin.000005 Converts binary files into readable SQL statements

mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.000058 >binlog

Use the command to view the binlog

Binary logs show master logs to see a list of all binary logs

The show binlog events command displays log events in a list

Format of the show binlog events command

show binlog events [IN 'log_name'] [FROM pos] [LIMIT[offset,]row_count];

Description:

  • IN ‘log_name’ : specifies the name of the binlog file to query (if omitted, the first binlog file is specified by default)

  • FROM pos: Specifies the pos start point (if omitted, the first POS point in the entire file)

  • LIMIT [offset] : offset (default: 0)

  • Row_count: Total number of rows queried (if omitted, all rows are displayed)

Switch the binlog file

flush logs;

A new log file is generated

Restarting the mysql service also generates new log files

Binlog-based master/slave replication

Disable the firewalls on the master and slave machines

Systemctl stop iptables (the iptables service needs to be installed) systemctl stop firewalld (default) systemctl disable firewalld.serviceCopy the code

Master server configuration

Check whether the binlog command is enabled

show variables like 'log_bin%';

Log_bin OFF indicates that the function is disabled

Master server configuration

  • Modify the my.cnf file

    Add it under the [mysqld] section

    Sync_binlog =1 # database to be backed up binlog-do-db=hello # database not to be backed up binlog-ignore-db=mysql # Start binary log-bin=mysql-bin # server ID server-id=132Copy the code

  • Restarting the mysql service

    systemctl restart mysqld

  • The host grants backup permission to the slave machine

    Note: Log in to the MySQL command client first

    GRANT REPLICATION SLAVE ON *.* TO 'MySQL '@' MySQL' IDENTIFIED by 'MySQL ';

Matters needing attention:

Generally, the root account is not used. % indicates that all clients can be connected. As long as the account and password are correct, the client IP address can be used instead

  • Refresh the permissions

    FLUSH PRIVILEGES;

  • Example Query the master status

    show master status;

Slave server configuration

  • Modify the my.conf file

    [mysqld] 
server-id=133
Copy the code

  • To configure the slave server

    Change master to master_host='192.168.68.132', master_port=3306, master_user='root', master_password='root', master_log_file='mysql-bin.000002', master_log_pos=1190, MASTER_AUTO_POSITION=0;Copy the code

    Note:

    Do not break in the middle of the statement, master_port is the mysql server port number (without quotes), master_user is the database account that performs the synchronization operation, “410” without single quotation marks (410 is the value of position seen in show master status, and mysql-bin.000001 is the value of file)

  • Enable replication from the secondary server

    start slave;

  • Check the status of the secondary server replication function

    Show slave status \G;

    Note: Slave_IO and Slave_SQL processes must be in YES state, otherwise they are in error state.

After the setup is successful, insert data into the host machine to see if there is any data in the slave machine

Gtid-based master/slave replication

GTID introduction

Gtids are Global Transaction Identifiers, transaction-based replication. With GTID, each transaction can be identified and tracked because it is committed on the original server and applied by any slave server; This means that there is no need to reference log files or locations in those files using GTID when starting a new slave or failing to a new master server, which greatly simplifies these tasks. Since gTID-based replication is entirely transaction-based, you only need to determine whether the master and slave levels are consistent; As long as all transactions committed on the master host are also committed to the slave, consistency between the two is guaranteed

GTID = server_uuid:transaction_id

The server_uuid is from auto. CNF

Relationship between GTID and Binlog

Structure of GTID in binlog

GTID event structure

Previous_gtid_log_event:

  • Previous_gtid_log_event is present in every binlog header

  • Each time binlog rotate is stored in the binlog header

  • Previous-gtids only stores all binlogs executed on the machine in the binlog, not including manually setting the gtid_purged value

  • In other words, if you manually set global gtid_purged=xx; Xx is not logged in Previous_gtid_log_event

Configure the GTID primary/secondary replication

  • Example Modify the my. CNF file on the master and slave servers

    # Enable the GTID mode (mandatory) gtid_mode=ON # Enforce the consistency of GTID (mandatory) invincible-gtid -consistency=trueCopy the code

  • Restart the mysql

    systemctl restart mysqld

  • Execute change Master from the server

    Change master to master_host='192.168.68.132', master_port=3306, master_user='root', master_password='root', master_auto_position = 1;Copy the code

  • Open the synchronization

    START SLAVE;

Causes and solutions of master/slave synchronization delay

The reason for the delay in master/slave synchronization

A server opens N links to the client to connect, so there will be large concurrent update operations, but there is only one thread from the server to read the binlog, when a certain SQL execution on the secondary server for a long time or because of a certain SQL lock table will result in a large backlog of SQL on the primary server. Not synchronized to the slave server. This leads to master-slave inconsistencies, known as master-slave delays

Solution to master/slave synchronization delay

In fact, there is no one way to beat the master/slave synchronization delay, because all SQL must be executed on the slave server, but if the master server is constantly updated and constantly written, then the latency will be increased. Of course we can do some mitigation measures

  • The master server is responsible for the update operation and has higher security requirements than the slave server, so some Settings can be changed, such as SYNc_binlog =1, innodb_flush_LOG_at_trx_COMMIT =1, while the slave server does not need such high data security. You can also set sync_binlog to 0 or turn binlog off. Innodb_flushlog, innodb_flush_log_at_trx_COMMIT can also be set to 0 to improve the efficiency of SQL execution. Another is to use better hardware than the master library as slaves

  • When a slave server is used as a backup, instead of providing queries, its load is down, and the execution of SQL in the relay log is naturally more efficient

  • Add slave server, this purpose is also to spread the read stress, thus reducing server load

Methods for determining master-slave delay

You can run the show slave status command to view the status of the secondary server. For example, you can view the value of Seconds_Behind_Master to determine whether the master/slave delay occurs

The values are as follows:

  • NULL: indicates that either io_thread or SQL_thread fails. That is, the Running status of the thread is No, not Yes.
  • 0: The desired value is zero, indicating that the master/slave replication status is normal