This article mainly introduces the concepts and methods of master-slave replication, master-slave replication and semi-synchronous replication of mariadb.
Reference resources http://www.jb51.net/article/97786.htm
The time of master and slave servers should be synchronized, and the database version should be consistent in order to avoid abnormal function processing, log reading, log parsing and so on.
The following three master-slave replication settings are independent.
Note the impact of firewalls and selinux.
1. Implementation of simple master-slave replication
1.1 Server 1 Operation
1) Install mariadb-server
[root@localhost ~]# yum -y install mariadb-server
2) Editing/etc/my.cnf file
Add the following at the end of the [mysqld] paragraph
[root@localhost ~]# vim /etc/my.cnf skip_name_resolve = ON innodb_file_per_table = ON server-id = 1 (id No. cannot be the same as the slave server) log-bin = master-log (Custom binary log file name)
3) Authorize hosts that can replicate local database information
[root@localhost ~]# System CTL start mariadb. service (start mariadb server) [root@localhost ~]# mysql MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'10.1.51.%' identified by 'replpasswd'; MariaDB [(none)]> flush privileges; MariaDB [(none)]> show master status\G (View the status information of the primary server and use it from the slave server) *************************** 1. row *************************** File: master-log.000003 (Binary log files in use) Position: 497 (Location) Binlog_Do_DB: Binlog_Ignore_DB:
1.2 Configuration of slave server
1) Install mariadb-server
[root@localhost ~]# yum -y install mariadb-server
2) Editing/etc/my.cnf file
Add the following at the end of the [mysqld] paragraph
[root@localhost ~]# vim /etc/my.cnf skip_name_resolve = ON innodb_file_per_table = ON server-id = 2 (id No. cannot be the same as the primary server) relay-log = slave-log (Custom binary log file name)
3) Setting the location from which primary server to synchronize
[root@localhost ~]# systemctl start mariadb.service [root@localhost ~]# mysql MariaDB [(none)]> change master to master_host='10.1.51.60',master_user='repluser',master_password='replpasswd',master_log_file='master-log.000003',master_log_pos=497; MariaDB [(none)]> start slave; (Start the replication function) MariaDB [(none)]> show slave status\G (Look at the status of the slave server, and here's part of it. Master_Host: 10.1.51.60 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-log.000003 Read_Master_Log_Pos: 497 Relay_Log_File: slave-log.000002 Relay_Log_Pos: 530 Relay_Master_Log_File: master-log.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Master_Server_Id: 1
1.3 Test
1) Import pre-prepared databases into the primary server
[root@localhost ~]# mysql < hellodb.sql
2) See Synchronization from Server
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb |(The database has been synchronized) | mysql | | performance_schema | | test | +--------------------+ MariaDB [(none)]> use hellodb; MariaDB [hellodb]> show tables; (hellodb The tables in the database are also synchronized. +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-------------------+
2. Implementation of Dual Master Replication
2.1 Installation of MariaDB
Operation of Server 1:
1) Install mariadb-server
[root@localhost ~]# yum -y install mariadb-server
2) Editing/etc/my.cnf file
[root@localhost ~]# vim /etc/my.cnf Add the following at the end of the [mysqld] paragraph skip_name_resolve = ON innodb_file_per_table = ON server-id = 1 (id number cannot be the same as slave server) log-bin = master-log (custom primary server binary log file name) relay-log = slave-log (custom slave server binary log file name) auto_increment_offset = 1 auto_increment_increment = 2
3) Start up services
[root@localhost ~]# systemctl start mariadb.service [root@localhost ~]#systemctl enable mariadb.service
Operation of Server 2:
1) Install mariadb-server
[root@localhost ~]# yum -y install mariadb-server
2) Editing/etc/my.cnf file
[root@localhost ~]# vim /etc/my.cnf skip_name_resolve = ON innodb_file_per_table = ON server-id = 2 relay-log = slave-log lob-bin = master-log auto_increment_offset = 2 auto_increment_increment = 2
3) Start up services
[root@localhost ~]# systemctl start mariadb.service [root@localhost ~]#systemctl enable mariadb.service
2.2 Configuration of Dual Master Replication
1) master Status Viewed on Server 2
Description: Record data, which will be used when configuring on Server 2.
MariaDB [(none)]> show master status\G *************************** 1. row *************************** File: master-log.000003 Position: 521 Binlog_Do_DB: Binlog_Ignore_DB:
2) Server 1 is configured as follows
Description: The following configurations are the IP of Server 2 and master data found on Server 2.
[root@localhost ~]# mysql
MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.1.%' identified by 'replpasswd';
MariaDB [(none)]> change master to master_host='192.168.1.188',master_user='repluser',master_password='replpasswd',master_log_file='master-log.000003',master_log_pos=521;
MariaDB [(none)]> start slave;
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.188
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 521
Relay_Log_File: slave-log.000002
Relay_Log_Pos: 806
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 521
Relay_Log_Space: 1094
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: 2
1 row in set (0.00 sec)
3) View master status in server 1
MariaDB [(none)]> show master status\G *************************** 1. row *************************** File: master-log.000003 Position: 795 Binlog_Do_DB: Binlog_Ignore_DB:
4) Configuration on Server 2 as follows
Description: The following configurations are the IP of Server 1 and the master information found in Server 1.
[root@localhost ~]# mysql
MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.1.%' identified by 'replpasswd';
MariaDB [(none)]> change master to master_host='192.168.1.187',master_user='repluser',master_password='replpasswd',master_log_file='master-log.000003',master_log_pos=795;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.187
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 795
Relay_Log_File: slave-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 795
Relay_Log_Space: 818
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
1 row in set (0.00 sec)
2.3 Test
1) Create mydb database on any server
MariaDB [(none)]> create database mydb;
2) View on another server
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | test | +--------------------+
3. Implementation of semi-synchronous replication
3.1 Configuration on the primary server
1) Install mariadb-server
[root@localhost ~]# yum -y install mariadb-server
2) Editing/etc/my.cnf
[root@localhost ~]# vim /etc/my.cnf
skip_name_resolve = ON
innodb_file_per_table = ON
server-id = 1
log-bin = master-log
3) Authorize hosts that can replicate local database information
[root@localhost ~]# System CTL start mariadb. service (start mariadb server) [root@localhost ~]# mysql MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'10.1.51.%' identified by 'replpasswd'; MariaDB [(none)]> flush privileges; MariaDB [(none)]> show master status\G (View the status information of the primary server and use it from the slave server) *************************** 1. row *************************** File: master-log.000003 (Binary log files in use) Position: 245 (Location) Binlog_Do_DB: Binlog_Ignore_DB:
4) Install the rpl semi sync_master plug-in and enable it
[root@localhost ~]# mysql MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so'; MariaDB [(none)]> set global rpl_semi_sync_master_enabled = ON; Supplement: MariaDB [(none)]> show plugins; (check whether the plug-in is activated) MariaDB [(none)]> show global variables like'rpl_semi%'; (You can see if the installed plug-in is enabled) MariaDB [(none)]> show global status like'% semi%'; (See the number of slave servers, which is 0 at this time)
3.2 Configuration of slave server
1) Install mariadb-server
[root@localhost ~]# yum -y install mariadb-server
2) Editing/etc/my.cnf file
Add the following at the end of the [mysqld] paragraph [root@localhost ~]# vim /etc/my.cnf skip_name_resolve = ON innodb_file_per_table = ON server-id = 2 (id number cannot be the same as primary server) relay-log = slave-log (custom binary log file name)
3) Setting the location from which primary server to synchronize
[root@localhost ~]# systemctl start mariadb.service [root@localhost ~]# mysql MariaDB [(none)]> change master to master_host='10.1.51.60',master_user='repluser',master_password='replpasswd',master_log_file='master-log.000003',master_log_pos=245;
4) Install the rpl semi sync_slave plug-in and enable it
[root@localhost ~]# mysql MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; MariaDB [(none)]> set global rpl_semi_sync_slave_enabled = ON; MariaDB [(none)]> start slave;
After completing the above configuration, you can view the related information of semi-synchronous replication on the main server. The commands are as follows:
MariaDB [(none)]> show global status like '%semi%';
Rpl_semi_sync_master_clients 1 (one from the server)
3.3 Test
The test depends on the actual situation of the individual.
1) Import the prepared database hellodb.sql on the primary server
MariaDB [hellodb]> source /root/hellodb.sql;
2) View the status of semi-synchronous replication on the primary server
MariaDB [hellodb]> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-log.000003 | 8102 | | | +-------------------+----------+--------------+------------------+ MariaDB [hellodb]> show global status like '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 1684 | | Rpl_semi_sync_master_net_wait_time | 60630 | | Rpl_semi_sync_master_net_waits | 36 | | Rpl_semi_sync_master_no_times | 1 | | Rpl_semi_sync_master_no_tx | 1 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 1884 | | Rpl_semi_sync_master_tx_wait_time | 65965 | | Rpl_semi_sync_master_tx_waits | 35 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 35 | +--------------------------------------------+-------+
3) See Synchronization from the Server
MariaDB [(none)]> show databases; MariaDB [(none)]> use hellodb; MariaDB [hellodb]> select * from students;
4 Supplement:
Based on the above semi-synchronous replication configuration replication filter, replication filtering is best set from the server, as follows
4.1 Configuration of slave server
1) Close mariadb server
[root@localhost ~]# systemctl stop mariadb.service
2) Editing/etc/my.cnf file
[root@localhost ~]# vim /etc/my.cnf skip_name_resolve = ON innodb_file_per_table = ON server-id = 2 relay-log = slave-log replicate-do-db = mydb (Copy only mydb Content of the database)
Plus: The commonly used filtering options are as follows
Replicate_Do_DB= Replicate_Ignore_DB= Replicate_Do_Table= Replicate_Ignore_Table= Replicate_Wild_Do_Table= Replicate_Wild_Ignore_Table=
3) Restart mariadb server
[root@localhost ~]# systemctl start mariadb.service
4) After restarting mariadb server, the semi-synchronous replication function will be shut down, so it will be restarted.
MariaDB [(none)]> show global variables like '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ MariaDB [(none)]> set global rpl_semi_sync_slave_enabled = ON; MariaDB [(none)]> stop slave;(Close the replication function from the server before restarting) MariaDB [(none)]> start slave;
4.2 Test
1) Create a new table semitable from the hellodb database on the primary server
MariaDB [hellodb]> create table semitable (id int);
2) See if the hellodb database has semitable from the server
MariaDB [(none)]> use hellodb MariaDB [hellodb]> show tables;(No) +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-------------------+
3) Create a mydb database on the primary server and create a tbl1 table for it
MariaDB [hellodb]> create database mydb;
4) Check whether the mydb database has tbl1 tables from the server
MariaDB [hellodb]> use mydb; MariaDB [mydb]> show tables; (You can see) +----------------+ | Tables_in_mydb | +----------------+ | tbl1 | +----------------+