MARIADB master-slave replication, master copy, semi-synchronous replication configuration detailed _mariadb

Master and Slave server time to synchronize, the database version is best consistent, so as to avoid the function of processing, log read, log parsing and other anomalies.

The following three master-slave replication settings are independent.

Notice the impact of firewalls and selinux.

1, simple master-slave replication implementation

(1) configuration of the primary server

1) Installation Mariadb-server

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


2) Edit/etc/my.cnf file

[root@localhost ~]# vim /etc/my.cnf


Add the following at the end of the [mysqld] section

Skip_name_resolve = On
Innodb_file_per_table = On
Server-id = 1 (ID number cannot follow server)
Log-bin = master-log (custom binary log file name)

3 Authorize a host that can replicate local database information

[Root@localhost ~]# systemctl 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 status information for the master server, to be used from the server)
*************************** 1. Row *************
   file:master-log.000003 (binary log file in use)
  position:497 (location)
 binlog_do_db: 
binlog_ ignore_db:

(2) from the server configuration

1) Installation Mariadb-server

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

2) Edit/etc/my.cnf file

[root@localhost ~]# vim /etc/my.cnf

Add the following at the end of the [mysqld] section

Skip_name_resolve = On
Innodb_file_per_table = On
Server-id = 2 (the ID number cannot be the same as the primary server)
Relay-log = slave-log (custom binary log file name)

3 Set the location from which primary server to start the synchronization

[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 replication)
mariadb [(none)]> show Slave status\g (see the status from the server, which is shown in the section below)
 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

(3) test

1 to import a prepared database at the primary server

[root@localhost ~]# mysql < hellodb.sql


2 to see if sync from the server

MARIADB [(None)]> show databases;
+--------------------+
| Database   |
+--------------------+
| information_schema |
| Hellodb   | (database synchronized)
| mysql |    |
performance_schema
| | +--------------------+
mariadb [(none)]> use Hellodb;
MARIADB [hellodb]> Show tables; (The tables of the HELLODB database are also synchronized)
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes   |
| coc    |
| courses   |
| scores   |
| Students | |
Teachers   |
| TOC    |
+-------------------+

2, the realization of dual-master replication

(1) Server 1 configuration

1) Installation Mariadb-server

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

2) Edit/etc/my.cnf file

[root@localhost ~]# vim /etc/my.cnf

Add the following at the end of the [mysqld] section

Skip_name_resolve = On
Innodb_file_per_table = On
Server-id = 1 (ID number cannot follow server)
Log-bin = Master-log (custom primary server's binary log file name)
Relay-log = slave-log (custom binary log file name from server)
Auto_increment_offset = 1
Auto_increment_increment = 2

3 Master Status viewed on server 2

MARIADB [(None)]> Show Master Status\g
*************************** 1. Row ***************************
   File : master-log.000003
  position:422
 binlog_do_db: 
binlog_ignore_db:

4 start MARIADB Server and make the following configuration

[Root@localhost ~]# systemctl start Mariadb.service

[root@localhost ~]# mysql mariadb

 [(none)]> Grant Replication Slave,replication Client on *.* to ' repluser ' @ ' 10.1.51.% ' identified by ' replpasswd ';

 MARIADB [(none)]> change master to master_host= ' 10.1.51.50 ', master_user= ' repluser ', master_password= ' replpasswd ', Master_log_file= ' master-log.000003 ', master_log_pos=422;

 MARIADB [(None)]> start slave;

 MARIADB [(None)]> show SLAVE status\g (partial content only)
  master_host:10.1.51.50 master_user:repluser
  port:3306
  connect_retry:60
  master_log_file:master-log.000003
  read_master_log_pos:422
  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:2

(2) server 2 configuration

1) Installation Mariadb-server

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

2) Edit/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 View master status in server 1

MARIADB [(None)]> Show Master Status\g
1. Row ***************************
file:master-log.000003
position:245
binlog_do_db:
binlog_ignore_db:

4) Start MARIADB Server and configure

[Root@localhost ~]# systemctl start Mariadb.service

[root@localhost ~]# mysql mariadb

 [(none)]> Grant Replication Slave,replication Client on *.* to ' repluser ' @ ' 10.1.51.% ' identified by ' replpasswd ';

 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;

 MARIADB [(None)]> start slave;

 MARIADB [(none)]> show slave status\g (partial content only) 
  master_host:10.1.51.60 master_user:repluser
  port:3306
  connect_retry:60
  master_log_file:master-log.000003
  read_master_log_pos:422
  Relay_ log_file:slave-log.000003
  relay_log_pos:530
  relay_master_log_file:master-log.000003
  Slave_IO_ Running:yes
  Slave_sql_running:yes
  master_server_id:1

(3) test

1 Create a 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
| | +--------------------+

3. Implementation of semi-synchronous replication

(1) configuration on the primary server

1) Installation Mariadb-server

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

2) Edit/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 a host that can replicate local database information

[Root@localhost ~]# systemctl 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 status information for the master server, to be used from the server)
*************************** 1. Row *************
   file:master-log.000003 (binary log file in use)
  position:245 (location)
 binlog_do_db: 
binlog_ ignore_db:

4 Install RPL Semi Sync_master plugin, and enable

[root@localhost ~]# MySQL

mariadb [(none)]> Install plugin rpl_semi_sync_master soname ' semisync_master.so '; C13/>MARIADB [(None)]> set global rpl_semi_sync_master_enabled = on;

Add:

MARIADB [(None)]> show plugins (to see if the plug-in is active)
MARIADB [(None)]> show global variables like ' rpl_semi% '; (to see if the installed Plug-ins are enabled)
MARIADB [(None)]> show global status like '%semi% '; (you can see the number from the server, 0 at this time)

(2) from the server configuration

1) Installation Mariadb-server

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


2) Edit/etc/my.cnf file

[root@localhost ~]# vim /etc/my.cnf

Add the following at the end of the [mysqld] section

Skip_name_resolve = On
Innodb_file_per_table = On
Server-id = 2 (the ID number cannot be the same as the primary server)
Relay-log = slave-log (custom binary log file name)

3 Set the location from which primary server to start the synchronization

[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 RPL Semi Sync_slave plugin and enable

[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 you complete the configuration above, you can view information about semi-synchronous replication on the primary server as follows:

MARIADB [(None)]> show global status like '%semi% ';
 Rpl_semi_sync_master_clients 1 (one from server)

(3) test

The test is based on personal circumstances.
1 to import a prepared database on the primary server Hellodb.sql

MariaDB [hellodb]> source /root/hellodb.sql;

2 View the status of the 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 to see if sync from the server

MARIADB [(None)]> show databases;
MARIADB [(None)]> use Hellodb;
MARIADB [hellodb]> SELECT * from students;

Supplemental: Replication filtering is best set up from the server based on the above semi-synchronous replication configuration, as follows

(1) from the server configuration

1) Close MARIADB server

[root@localhost ~]# systemctl stop mariadb.service

2) Edit/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 the contents of the MyDB database)

Add: Common 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 the MARIADB server, the semi-synchronous replication feature will be turned off, so reboot

MARIADB [(None)]> show global variables like '%semi% ';
+---------------------------------+-------+
| Variable_name     | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled  | Off |
| Rpl_semi_sync_slave_trace_level | |
+---------------------------------+-------+

mariadb [(None)]> set global rpl_semi_sync_slave_enabled = on;
MARIADB [(none)]> stop slave (you need to turn off restart from server replication first)
mariadb [(none)]> start slave;

(2) test

1 The HELLODB database on the primary server creates a new table semitable

MariaDB [hellodb]> create table semitable (id int);

2 to see if the Hellodb database is semitable from the server

MARIADB [(None)]> use Hellodb
mariadb [hellodb]> show tables; (not)
+-------------------+
| 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 whether there is a tbl1 table for viewing the MyDB database from the server

MARIADB [hellodb]> use mydb;
MARIADB [mydb]> Show tables; (can view)
+----------------+
| Tables_in_mydb |
+----------------+
| tbl1   |
+----------------+