Detailed explanation of various MySQL replication methods 01/18 Update SLTechnology News&Howtos

Detailed explanation of various MySQL replication methods

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

This article mainly introduces a variety of MySQL replication methods to explain in detail, the things involved, learned from the theoretical knowledge, there are many books, literature for your reference, from the perspective of practical significance, accumulated years of practical experience can be shared with you.

MySQL replication mainly sends write operations to the master node and read operations to the slave node, and each node has related data sets. The slave service specially starts a thread to act as a client, requests the MySQL master node to read the events in the binary log file through the MySQL protocol, and then the master node will check the events in its own binary log and send them to the slave node. After receiving it, the slave node will first save it in its own relay log, and each event read will be saved in the relay.

Thread name used in replication:

Slave node:

Icano Thread: requests binary events from the primary node and saves them in the relay log

SQL Thread: read binary log events from the relay log and replay locally

Primary node:

Dump Thread: start a dump Thread for each slave node's Thread O Thread to send binary log events to it

The function of replication:

1. Realize the purpose of data distribution

2. The load balancing effect that can mainly complete the write operation

3. It can achieve the effect of backup (when the primary CVM is down, the standby node can act as the primary node)

4. High availability and failover can be achieved

5. Mysql can be upgraded and tested on the slave node.

MySQL replication: master-slave replication, master-master replication, semi-synchronous replication, filter replication, SSL replication

1. Achieve master-slave replication:

Prepare the environment

Virtual machine 1:Master node (primary node)

IP:192.168.1.108

Virtual machine 2:Slave node (slave node)

IP:192.168.1.109

1. Deploy Master nodes

(1) install mariadb service

[root@node0 ~] # yum install mariadb-server-y

(2) Edit its configuration file

[root@node0 ~] # vim / etc/my.cnf # # mariadb configuration file path [mysqld] log_bin=mysql-bin # # enable binary logging function server-id=1 # # define unique Server-idinnodb_file_per_table = ONskip_name_resolve = ON # # prohibit unpacking hostname [root@node0 ~] # systemctl start mariadb.service # # start mariadb service MariaDB [(none)] > show global variables like "server_id" # # check its server-idserver_id 1 MariaDB [(none)] > show global variables like "log_bin"; # # check whether its binary log is enabled log_bin ON MariaDB [(none)] > show master status; # # View the binary log file mysql-bin.000002 245 in use | MariaDB [(none)] > grant replication slave,replication client on *. * to 'repluser'@'192.168.%.%' identified by' replpass' # # create a user with replication permission MariaDB [(none)] > flush privileges

2. Deploy Slave nodes

(1) install mariadb service

[root@node1 ~] # yum install mariadb-server-y

(2) Edit its configuration file

[root@node1 ~] # vim / etc/ my.cnf [mysqld] relay-log=relay-log # # enable relay log server-id=2 # # define a unique Server-id This Server-id must not be the same as the Server-id on Master innodb_file_per_table = ONskip_name_resolve = ON [root@node1 ~] # systemctl start mariadb.serviceMariaDB [(none)] > show global variables like "relay_log"; # # check whether relay_log relay-log MariaDB [(none)] > show global variables like "server_id" is enabled for relay logs; # # check its server-iserver_id 2

(3) use a user account with replication permission to connect to the main server and start the replication thread

MariaDB [(none)] > change master to master_host='192.168.1.108',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000002',master_log_pos=245;## master_host: indicates the IP address of the primary node # # master_user, master_password: indicates the user account with replication permission # # master_log_file: indicates the binary log being used by the primary node # # master_log_pos: specify positionMariaDB [(none)] > start slave; # # start the replication thread MariaDB [(none)] > show slave status\ G; # # View the status information of the replication thread Slave_IO_Running: YesSlave_SQL_Running: Yes

3. Test whether the master-slave server can achieve the replication function.

(1) View the database once on the Master node

(2) check the database once on Slave

(3) create a database named "MaGeRepo" on the Master node and view it on the Slave node

Problems that should be paid attention to in the master-slave replication architecture:

1. Parameters on the Master node

If sync_binlog=ONsync_master_info=ON uses the InnoDB storage engine, the following two items should also be enabled: innodb_flush_log_at_tx_commit = ON # # flush log innodb _ support_xa = ON # # whether to let innodb support distributed transactions

2. Parameters on the Slave node

Skip_slave_start = OFF # # whether to automatically start the transaction thread sync_relay_log = ON sync_relay_log_info = ON

Second, realize master master replication

Prepare the environment

Virtual machine 1:Master node (primary node)

IP:192.168.1.108

Virtual machine 2:Master node (primary node)

IP:192.168.1.109

1. Deploy virtual machine 1

(1) install mariadb service

[root@node0 ~] # yum install mariadb-server-y

(2) Edit its configuration file

[root@node0 ~] # vim / etc/my.cnflog_bin=mysql-binrelay-log=relay-log # # enable relay log server-id=1innodb_file_per_table = ON [root@node0 ~] # systemctl start mariadb.serviceMariaDB [(none)] > show global variables like "% log%"; relay_log relay-log log_bin ON MariaDB [(none)] > show master status Mysql-bin.000003 245 MariaDB [(none)] > grant replication slave,replication client on *. * to 'repluser'@'192.168.%.%' identified by' replpass'

2. Deploy virtual machine 2

(1) install mariadb service

[root@node1 ~] # yum install mariadb-server-y

(2) Edit configuration file

[root@node1 ~] # vim / etc/ my.cnf [mysqld] relay-log=relay-loglog_bin=mysql-bin # # enable binary log server-id=2innodb_file_per_table = ONskip_name_resolve = ON [root@node1 ~] # systemctl start mariadb.serviceMariaDB [(none)] > show master status Mysql-bin.000003 245 MariaDB [(none)] > grant replication slave,replication client on *. * to 'repluser'@'192.168.%.%' identified by' replpass'; # # create a user account with replication permission

3. The two nodes connect to each other's server using the users created by both sides, and start the replication thread

(1) Connect virtual machine 1 to virtual machine 2

MariaDB [(none)] > change master to master_host='192.168.1.109',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000003',master_log_pos=245;MariaDB [(none)] > flush privileges;MariaDB [(none)] > start slave

(2) connect virtual machine 2 to virtual machine 1

MariaDB [(none)] > change master to master_host='192.168.1.108',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000003',master_log_pos=245;MariaDB [(none)] > flush privileges;MariaDB [(none)] > start slave

4. Test the master master replication model

(1) View the database of virtual machine 1 and virtual machine 2 respectively

(2) add "LweimRepo" database on virtual machine 1 and "WzxRepo" database on virtual machine 2

MariaDB [(none)] > create database LweimRepo; # # virtual machine 1Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > flush privileges;MariaDB [(none)] > create database WzxRepo; # # virtual machine 2Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > flush privileges

(3) View the database of the two nodes again.

Third, to achieve semi-synchronous replication: when a master node has multiple slave nodes, only one slave node is required to copy and respond to the master node, and then the master node responds to the client

Prepare the environment

Virtual machine 1:Master node (primary node)

IP:192.168.1.108

Virtual machine 2:Slave node (slave node)

IP:192.168.1.109

1. Install the plug-in "semisync_master.so" to the master node

MariaDB [(none)] > install plugin rpl_semi_sync_master soname 'semisync_master.so';MariaDB [(none)] > show global variables like "% semi%" +-- +-+ | Variable_name Value +-- +-+ | rpl_semi_sync_master_enabled OFF # # whether the master node of semi-synchronous replication is enabled It needs to be set to ON | rpl_semi_sync_master_timeout 10000 # # waiting time for slave response (in milliseconds). The default is 10s | rpl_semi_sync_master_trace_level 32 # # tracking node 32. The default value is fine. Change is not recommended | rpl_semi_sync_master_wait_no_slave ON # # whether to wait for +-- +-- + MariaDB [(none)] > set global rpl_semi_sync_master_enabled=1 when there is no slave node # # change "rpl_semi_sync_master_enabled" to "ON"

2. Install "semisync_slave.so" on the Slave node

MariaDB [(none)] > install plugin rpl_semi_sync_slave soname 'semisync_slave.so';MariaDB [(none)] > set global rpl_semi_sync_slave_enabled=1; # # slave node with semi-synchronous replication enabled MariaDB [(none)] > show global variables like "% semi%" +-- +-+ | Variable_name Value +-- +-+ | rpl_semi_sync_slave_enabled ON # # whether semi-synchronous replication is enabled Slav node | rpl_semi_sync_slave_trace_level 32 +-- +-+

3. Realize master-slave replication

4. Check whether the slave node is added on the Master node.

5. Create the database "GunDuZi" on the Master node and view it on the Slave node

Fourth, implement filter replication: let the replication database or the table specified in the replication database be specified from the node

Prepare the environment

Virtual machine 1:Master node (primary node)

IP:192.168.1.108

Virtual machine 2:Slave node (slave node)

IP:192.168.1.109

1. Achieve master-slave replication

2. Specify "wtcdb" as the database that the Slave node needs to replicate.

MariaDB [wxpp] > set global replicate_do_db=wtcdb;MariaDB [wxpp] > show global variables like "replicate%" +-- +-+ | Variable_name Value +-- +-+ | replicate_annotate_row_events OFF | Replicate_do_db wtcdb # # copy only those databases | replicate_do_table # # copy only those tables in those databases | replicate_events_marked_for_skip replicate | databases ignored by replicate_ignore_db # # (databases that are not replicated) | tables ignored by replicate_ignore_table # # (tables that are not replicated) | replicate_wild_do_table # # can be specified using wildcards Database to be copied | replicate_wild_ignore_table +-+-+

3. View the database on the Slave node

4. Create "lweimdb", "wzxdb" and "wtcdb" databases on the Master node

MariaDB [(none)] > create database lweimdb;Query OK, 1 row affected (0.01 sec) MariaDB [(none)] > create database wzxdb;Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > create database wtcdb;Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > flush privileges;Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > show databases +-+ | Database +-+ | information_schema | lweimdb | mysql | performance_schema | test | wtcdb | wxpp | wzxdb +-+

5. Check the database on the Slave node again to see if only the "wtcdb" database has been copied

Fifth, achieve SSL replication

Prepare the environment

Virtual machine 1:Master node (primary node)

IP:192.168.1.108

Virtual machine 2:Slave node (slave node)

IP:192.168.1.109

Virtual machine 3:CA mechanism (sign master-slave node certificate)

IP:192.168.1.110

1. Deploy virtual machine 3

(1) create CA certificate key

[root@localhost CA] # (umask 077 / OpenSSL genrsa-out. / private/cakey.pem 1024)

(2) let CA sign the certificate by itself

[root@localhost CA] # openssl req-new-x509-key. / private/cakey.pem-out cacert.pem-days 365 [root@localhost CA] # touch serial index.txt # # create the required file [root@localhost CA] # echo 01 > serial # # generate the certificate serial number

2. The Master node creates the certificate and asks the CA institution to sign it.

[root@node0 ssl] # (umask 077 Openssl genrsa-out master.key 1024) # # Master node generates the key [root@node0 ssl] # openssl req-new-key master.key-out master.csr-days 365# generate the certificate that needs to be signed [root@localhost CA] # openssl ca-in / tmp/master.csr-out master.crt-days # # Let CA sign the certificate [root@node0 ssl] # chmod 600 * # # change the permission to 600 [root@node0 ssl] # chown mysql.mysql- R ssl/* # # change the file attribute to mysql [root@node0 ssl] # lltotal 12murrw1 mysql mysql 1046 Jun 9 20:49 cacert.pem-rw- 1 mysql mysql 3202 Jun 9 20:43 master.crt-rw- 1 mysql mysql 887 Jun 9 20:35 master.key

3. Configure the configuration file of the Master node and create a user with replication permissions

[root@node0 ~] # vim / etc/my.cnfssl # # enable the sll function ssl_ca=/var/lib/mysql/ssl/cacert.pem # # specify the CA institution certificate path ssl_cert=/var/lib/mysql/ssl/master.crt # # indicate the certificate path of the Master node ssl_key=/var/lib/mysql/ssl/master.key # # indicate the secret key path of the Master node [root@node0 ~] # systemctl start mariadb.serviceMariaDB [ (none)] > show global variables like "% ssl%" # # check whether ssl+-+--+ is enabled | Variable_name | Value +-- -+ | have_openssl | YES | have_ssl | YES | ssl_ca | / var/lib/mysql/ssl/cacert.pem | ssl_capath | ssl_cert | / var/lib/mysql/ssl/master.crt | ssl_cipher | | ssl_key | / var/lib/mysql/ssl/master.key +-+-+ MariaDB [(none)] > grant replication slave | Replication client on *. * to 'repluser'@'192.168.%.%' identified by' replpass' require ssl

4. Send the certificate on the Master node to Slave, modify its configuration file, connect to the Master node and start the replication thread

[root@node0 ~] # scp ssl/* root@192.168.1.109:/var/lib/mysql/ # # make sure that the file sent is "mysql" root@192.168.1.109's password: cacert.pem 1046 1.0KB/s 00:00 master.crt 3206 3.1KB/s 00:00 master.key 100 887 0.9KB/s 00 : 00 [root@node1 ~] # vim / etc/my.cnf ssl ssl_ca=/var/lib/mysql/cacert.pem ssl_cert=/var/lib/mysql/master.crtssl_key=/var/lib/mysql/ master.key [root @ node1 ~] # systemctl start mariadb.serviceMariaDB [(none)] > show global variables like'% ssl%' # # the function of SSL on the slave node has been enabled +-+-+ | Variable_name | Value +-+-- -+ | have_openssl | YES | have_ssl | YES | ssl_ca | / var/lib/mysql/cacert.pem | ssl_capath | | ssl_cert | / var/lib/mysql/master.crt | ssl_cipher | | ssl_key | | / var/lib/mysql/master.key +-+-+ MariaDB [(none)] > change master to master_host='192.168.1.108' | Master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000003',master_log_pos=245,master_ssl=1,master_ssl_ca='/var/lib/mysql/cacert.pem',master_ssl_cert='/var/lib/mysql/master.crt',master_ssl_key='/var/lib/mysql/master.key' # # master_ssl: enable ssl function # # master_ssl_ca: specify CA certificate path # # master_ssl_cert: indicate Master node certificate path # # master_ssl_key: indicate Master node key path MariaDB [(none)] > start slave; # # start replication thread MariaDB [(none)] > show slave status\ G # # View replication thread related information Slave_IO_Running: YesSlave_SQL_Running: YesMaster_SSL_Allowed: Yes # # make sure that all three items are "YES" Master_SSL_CA_File: / var/lib/mysql/cacert.pemMaster_SSL_Cert: / var/lib/mysql/master.crtMaster_SSL_Key: / var/lib/mysql/master.key

5. View the database on the Master node and the Slave node respectively

6. Create "LweimRepo", "HjRepo" and "WzxRepo" on the Master node, and then check it on the Slave node

MariaDB [(none)] > create database LweimRepo;Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > create database HjRepo;Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > create database WzxRepo;Query OK, 1 row affected (0.04 sec) MariaDB [(none)] > show databases # # View +-+ | Database +-+ | information_schema | HjRepo | LweimRepo | WzxRepo | mysql | performance_schema | test +-+ on the Slave node

Summary of the question:

1. When a Slave node connects to a Master node, be sure to specify the binary log and pos currently used by the Master node.

2. When using SSL replication, make sure that the key, the permissions of the certificate file and belonging to the master group

3. When generating certificates, the country, province and company names of the three virtual machines must be the same.

Read the above introduction of a variety of MySQL replication methods in detail, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.