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 |
+----------------+