MySQL master-slave replication architecture and implementation

MySQL master-slave replication architecture and implementation of MySQL master-slave replication configuration

• Master node:

  • Start binary Log

  • Set a globally unique ID number (Server-id) for the current node

  • Create a user account with replication privileges (Replication Slave,replication client)

• From node:

  • Start the relay log

  • Set a globally unique ID number for the current node

  • Connect to the primary server with a user account with replication permissions and start the replication thread

Concrete Steps Master Node (192.168.182.130)

• Edit the configuration file/etc/my.cnf, add the following:

  [mysqld]log_bin=1server_id=1innodb_file_per_table=on

• Create a user account with copy permissions

  MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘repluser‘@‘192.168.182.132‘ IDENTIFIED BY ‘replpass‘;MariaDB [(none)]> FLUSH PRIVILEGES;

From node (192.168.182.132)

• Edit the configuration file/etc/my.cnf, add the following:

  [mysqld]relay_log=relay_logrelay_log_index=relay_log.indexserver_id=7skip_name_resolve=1

• Connect to the primary server using a user account with replication privileges

  MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST=‘192.168.182.130‘,MASTER_USER=‘repluser‘,MASTER_PASSWORD=‘replpass‘,MASTER_LOG_FILE=‘mysql-bin.000075‘,MASTER_LOG_POS=245;

• Start the replication thread

  MariaDB [(none)]> START SLAVE;

• View the status of slave

  MariaDB [(None)]> SHOW SLAVE status\g*************************** 1. Row *************************** slave_io_state:waiting for master to send event master_host:192.1 68.182.130 master_user:repluser master_port:3306 connect_retry:60 master_    log_file:mysql-bin.000075 read_master_log_pos:245 relay_log_file:1.000002 relay_log_pos:529 relay_master_log_file:mysql-bin.000075 Slave_io_running:yes # #io线程启动了 Slave_sql_running:yes # # sq L Thread started the 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:245 relay_log_space:809 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_B Ehind_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:1

Issues to be aware of in MySQL master-slave replication

• Restrict read-only from the server

  • Restrict read-only from the server, set Read_only=on from the server, want to be permanently valid, write in the configuration file, but this limit is not valid for users with super privileges

    MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘read_only‘;+---------------+-------+| Variable_name | Value |+---------------+-------+| read_only     | OFF   |+---------------+-------+MariaDB [(none)]> SET GLOBAL  read_only=1;

  • If you want to restrict all users from being able to write, start a connection plus read lock and do not exit the connection

    MariaDB [(none)]> FLUSH TABLE WITH READ LOCK;

• How to ensure the transaction security of master-slave replication?

  • Enable parameters on the master node

    sync_binlog=

  • If you're using the InnoDB storage engine,

    innodb_flush_log_at_trx_commitinnodb_support_xa

  • On the slave node, the following parameters indicate whether the replication thread starts automatically when starting from the server, and on means that it does not start automatically, using a manual start

    skip_slave_start=on

• Two files on a slave node

  • The Master.info file holds information about the slave connection to master, such as the IP address of the primary server, the user, password, port, and the current synchronized binary log file and location.

  • The Relay-log.info file holds the binary log files and locations that the current slave node has copied, and where the copied content is saved to which trunk log

    [[email protected] data]# cat relay-log.info./1.000002693mysql-bin.000075409

• Monitoring and maintenance of replication:

  • Cleanup log: Use the purge command to make sure the data has been backed up before cleaning up

  • Replication Monitoring

    MariaDB [(None)]> SHOW BINARY logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+|       mysql-bin.000001 | 483 | |       mysql-bin.000002 | 264 | |       mysql-bin.000003 | 264 | |       mysql-bin.000004 | 264 | |       mysql-bin.000005 | 990 | |       mysql-bin.000006 | 514 | |       mysql-bin.000007 | 264 | |       mysql-bin.000008 | 245 | |       mysql-bin.000009 | 245 |+------------------+-----------+mariadb [(none)]> SHOW BINLOG events;+------------------+-----+------------ -+-----------+-------------+----------------------------------------------------------------+| Log_name | Pos | Event_type | server_id | End_log_pos | Info |+------------------+-----+-------------+-----------+------ -------+----------------------------------------------------------------+|   mysql-bin.000001 | 4 |         Format_desc |         1 | 245 | Server Ver:5.5.45-mariadb-log, Binlog ver:4                 || mysql-bin.000001 | 245 |         Query |         1 | 315 | BEGIN | | mysql-bin.000001 | 315 |         Intvar |         1 | 343 | insert_id=9 | | mysql-bin.000001 | 343 |         Query |         1 | 456 | Use ' S_sc_c '; INSERT into S (sname,sdept) VALUES (' Xiao ', ' MA ') | | mysql-bin.000001 | 456 |         Xid |         1 | 483 | COMMIT/* xid=43 */|+------------------+-----+-------------+-----------+------ -------+----------------------------------------------------------------+mariadb [(none)]> SHOW MASTER status;+ ------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+|      mysql-bin.000009 |              245 |                  | |+------------------+----------+--------------+------------------+mariadb [(none)]> SHOW processlist;+----+-------------+-----------+----- -+---------+------+-----------------------------------------------------------------------------+-------------- ----+----------+| Id | User | Host | db | Command | Time | State | Info | Progress |+----+-------------+-----------+------+---------+------+----------------------------------------------  -------------------------------+------------------+----------+| 5 | Root | localhost | NULL |    Query | 0 | NULL |    SHOW Processlist |  0.000 | | 6 |           System user | | NULL | Connect | 3850 | Waiting for Master to send event |    NULL |  0.000 | | 7 |           System user | | NULL | Connect | 3806 | Slave have read all relay log; Waiting for the SLave I/O thread to update it |    NULL | 0.000 |+----+-------------+-----------+------+---------+------+------------------------------------------------- ----------------------------+------------------+----------+mariadb [(none)]> SHOW SLAVE status\g

  • Whether the server is lagging behind with the primary server, salve from the server Seconds_behind_master can be viewed

    MariaDB [(None)]> SHOW SLAVE status-> \g*************************** 1. Row *************************** slave_io_state:waiting for master to send event master_host:192.168.182.1 Master_user:repluser master_port:3306 connect_retry:60 master_log_file:mysql-bin.0000 read_master_log_pos:334 relay_log_file:1.000006 relay_log_pos:618relay_master_log_file:mysql-bin.0000 Slave_io_running:yes Slave_sql_running:yes Replicate_do_db:mydb Replicate_ignore_db:replicate_do_tab le:replicate_ignore_table:replicate_wild_do_table:replicate_wild_ignore_table:last_errno:0 Last_Erro r:skip_counter:0 exec_master_log_pos:334 relay_log_space:1182 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:0 #从服务器落后与主服务器多长时间Master_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:1

  • Determine if the master-slave node data is consistent, using some tools, such as Percona-tools

  • If the data is inconsistent, remove all data from the node, import the data backup of the master node to the slave node, and re-copy

• There is a parameter on the master node, set to 1 indicates that every time the dump event to the slave node, the local master.info information to be immediately synchronized to disk, so that the master.info from the node to be updated in time,

  MariaDB [(none)]> SET GLOBAL sync_master_info=1;MariaDB [(none)]> SHOW  GLOBAL VARIABLES LIKE ‘sync_master_info‘;+------------------+-------+| Variable_name    | Value |+------------------+-------+| sync_master_info | 1     |+------------------+-------+

• There are parameters about Relay_log on the slave node, such as Sync_relay_log_info, which means that each write is immediately synchronized to the disk file, as well as the Sync_relay_log

Dual Master Model

• Problems encountered:

  • Data inconsistency, we may need to leave the server with high data reliability and re-create a Slave server based on the retained MySQL server.

  • For auto-growing fields, if a master node is a one-of-a-kind, ..., and the other master node is also a "...", then the problem arises when merging, so we can make a node's autogrow field use an even ID, and the other master node uses an odd ID. There's no problem when you merge.

  • Settings for odd ID

    auto_increment_offset=1 #表示从1开始auto_increment_increment=2 #表示一次增长2个

  • Settings for even IDs

    auto_increment_offset=2auto_increment_increment=2

Configuration steps

• Each node uses a unique server_id

• Both start binary log and relay log

• Create a user account with copy permissions

• Defines the numeric range for the autogrow ID field as odd-even

Specific steps

• One of the nodes is:

  • Editing a configuration file

      log_bin=1server_id=1innodb_file_per_table=onrelay_log=
      relay-logrelay_log_index=relay-log.indexauto_increment_offset=1auto_increment_increment=2  

  • Create user account

      MariaDB [(none)]> GRANT REPLICATION slave,replication CLIENT on *. * to ' repluser ' @ ' 192.168.182.132 ' identified by ' replpass ';
      MariaDB [(None)]> FLUSH privileges;  

  • connect to the primary server with a user account with replication permissions and turn on the slave thread

      MariaDB [(none)]> change MASTER to master_host= ' 192.168.182.132 ', Master_user= ' Repluser ', master_password= ' Replpass ', master_log_file= ' mysql-bin.000006 ', MASTER_LOG_POS=245;
     MariaDB [(None)]> START SLAVE;  

• Actions on the other master node:

  • Editing a configuration file

    [mysqld]bin_log=1relay_log=1relay_log_index=relay-log.indexserver_id=7auto_increment_offset=2auto_increment_increment=2

  • Create a user account with copy permissions

    MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘repluser‘@‘192.168.182.130‘ IDENTIFIED BY ‘replpass‘;MariaDB [(none)]> FLUSH PRIVILEGES;

  • Connect to the master node and enable the slave thread using a user account with copy permissions

    MariaDB [(none)]>  CHANGE MASTER TO MASTER_HOST=‘192.168.182.130‘,MASTER_USER=‘repluser‘,MASTER_PASSWORD=‘replpass‘,MASTER_LOG_FILE=‘mysql-bin.000075‘,MASTER_LOG_POS=665;MariaDB [(none)]> START SLAVE;

Semi-synchronous replication

• Need to use a plug-in, if it is RPM package installed plug-in path in/usrl/lib64/mysql/plugin, I use the binary format installed plug-in path is/usr/local/mysql/lib/plugin

• Operations on the master node

  • Editing a configuration file

    [mysqld]log_bin=1server_id=1innodb_file_per_table=onplugin_dir=/usr/local/mysql/lib/plugin

  • Add a user account with copy permission

    MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘repluser‘@‘192.168.182.132‘ IDENTIFIED BY ‘replpass‘;MariaDB [(none)]> FLUSH PRIVILEGES;

  • Installing plugins

    MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so‘;

  • See which plugins are available

    MariaDB [(none)]> SHOW PLUGINS;

  • View semi-Sync related variables

    MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%semi%‘;+------------------------------------+-------+| Variable_name                      | Value |+------------------------------------+-------+| rpl_semi_sync_master_enabled       | OFF   || rpl_semi_sync_master_timeout       | 10000 || rpl_semi_sync_master_trace_level   | 32    || rpl_semi_sync_master_wait_no_slave | ON    |+------------------------------------+-------+

  • Rpl_semi_sync_master_enabled: Disables the primary node for semi-synchronous replication for off

  • Rpl_semi_sync_master_timeout: Indicates the timeout to wait for a response from the server, the default is 10s, if more than this time from the server did not answer to the primary server, then demoted to run asynchronously, no longer wait

  • Rpl_semi_sync_master_trace_level: Indicates the trace level

  • Rpl_semi_sync_master_wait_no_slave: Indicates whether to wait when there is no slave node, on for wait

  • Enable semi-sync

    MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;

• Actions from the node:

  • Editing a configuration file

    [mysqld]relay_log=1relay_log_index=relay-log.indexserver_id=7skip_name_resolve=1plugin_dir=/usr/local/mysql/lib/plugin/

  • Connect to the master node using a user account with copy permissions

    MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST=‘192.168.182.130‘,MASTER_USER=‘rpluser‘,MASTER_PASSWORD=‘replpass‘,MASTER_LOG_FILE=‘mysql-bin.000078‘,MASTER_LOG_POS=245;

  • Installing plugins

    MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so‘;

  • View semi-Sync related variables

    MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%semi%‘;+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| rpl_semi_sync_slave_enabled     | OFF   || rpl_semi_sync_slave_trace_level | 32    |+---------------------------------+-------+

  • Enable semi-sync

    MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;

  • Enable slave threads

    MariaDB [(none)]> START SLAVE;

• The above operation can complete a semi-synchronous replication, you can view the relevant state variables on the master node to see if there is a semi-synchronous slave node

  MariaDB [(None)]> SHOW GLOBAL STATUS like '%semi% '; +--------------------------------------------+-------+| variable_name | Value |+--------------------------------------------+-------+| rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | On | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 |+--------------------------------------------+-------+# rpl_semi_sync_master_clients is 0 for no, 1 for 

Copy Filter

• To copy only the specified database from the node, or specify a table for the specified database

• There are two ways to achieve this:

  • The primary server only records events in the binary log that are related to a specific database (a specific table), but may cause point-in-time restores to be impossible (after all, binary logging events are incomplete), and it is not recommended to use

    binlog_do_db  #指定只记录哪些数据库的相关修改操作到二进制日志文件中，可以指定一个列表，使用逗号隔开binlog_ignore_db  #指定只忽略哪些不记录，其他都记录，可以指定一个列表，使用逗号隔开

  • When you sql_thread an event in the replay trunk log from the server, only the events associated with a particular database (specific table) are read and applied to the local, but the network and disk IO are wasted

    replicate_do_db=   #该处指定的数据库的相关事件都要进行复制replicate_ignore_db=replicate_do_table=replicate_ignore_table=replicate_wild_do_table=  #在指定表的时候可以使用通配符replcate_wild_ignore_table=

  • Example: Copying only related events for a mydb database

    #在从服务器上进行的操作MariaDB [(none)]> SET GLOBAL replicate_do_db=‘MYDB‘;MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%replicate%‘;+----------------------------------+-----------+| Variable_name                    | Value     |+----------------------------------+-----------+| replicate_annotate_row_events    | OFF       || replicate_do_db                  | MYDB      || replicate_do_table               |           || replicate_events_marked_for_skip | replicate || replicate_ignore_db              |           || replicate_ignore_table           |           || replicate_wild_do_table          |           || replicate_wild_ignore_table      |           |+----------------------------------+-----------+MariaDB [(none)]> SHOW SLAVE STATUS\G;

MySQL master-slave replication architecture and implementation