MySQL master-slave replication architecture and implementation

Source: Internet
Author: User
Tags flush unique id

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.