MySQL和Mariadb的实现步骤和方法完全一致，本文仅以Mariadb为例完成。
1.1 实现Mariadb主从复制
1.1.1 架构图及环境说明
两台服务器
1 主节点：
MariaDB-Master
CentOS 8.4
IP: 192.168.250.18/24
10.3.28-MariaDB
2 从节点：
MariaDB-Slave
CentOS 8.4
IP: 192.168.250.28/24
10.3.28-MariaDB
1.1.2 配置主节点数据库
# 修改主机名、同步时间，验证IP地址和操作系统版本等信息。
[root@CentOS84 ]#hostname
MariaDB-Master
[root@CentOS84 ]#hostname -I
192.168.250.18
[root@CentOS84 ]#systemctl enable --now chronyd.service
[root@CentOS84 ]#date
Tue Feb 22 18:41:53 CST 2022
[root@CentOS84 ]#uname -a
Linux CentOS84 4.18.0-305.3.1.el8.x86_64 #1 SMP Tue Jun 1 16:14:33 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux
[root@CentOS84 ]#
# 安装数据库，查看数据库配置文件
[root@CentOS84 ]#dnf -y install mariadb-server
[root@CentOS84 ]#rpm -ql mariadb
[root@CentOS84 ]#ll /etc/my.cnf.d/mariadb-server.cnf
-rw-r--r-- 1 root root 1458 Apr 19 2021 /etc/my.cnf.d/mariadb-server.cnf
[root@CentOS84 ]#cp /etc/my.cnf.d/mariadb-server.cnf /etc/my.cnf.d/mariadb-server.cnf.bak
[root@CentOS84 ]#ll /etc/my.cnf.d/
total 24
-rw-r--r-- 1 root root 41 Apr 19 2021 auth_gssapi.cnf
-rw-r--r-- 1 root root 295 Dec 18 2020 client.cnf
-rw-r--r-- 1 root root 763 Feb 19 2021 enable_encryption.preset
-rw-r--r-- 1 root root 1458 Apr 19 2021 mariadb-server.cnf
-rw-r--r-- 1 root root 1458 Feb 22 18:50 mariadb-server.cnf.bak
-rw-r--r-- 1 root root 232 Feb 19 2021 mysql-clients.cnf
[root@CentOS84 ]#
# 修改配置文件
[root@CentOS84 ]#vim /etc/my.cnf.d/mariadb-server.cnf
# 在[mysqld]段内增加两行，定义主节点ID和开启二进制
[mysqld]
server-id=18
log-bin
#查看3306端口是否被占用，确保空出3306默认数据库用的TCP端口
[root@CentOS84 ]#ss -tln
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
# 启动数据库并查看服务启动状况
[root@CentOS84 ]#systemctl restart mariadb
[root@CentOS84 ]#systemctl status mariadb
● mariadb.service - MariaDB 10.3 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Active: active (running) since Tue 2022-02-22 19:19:47 CST; 16s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 4341 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)
Process: 4204 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mariadb.service (code=exited, status=0/SUCCESS)
Process: 4180 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
Main PID: 4309 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 31 (limit: 23544)
Memory: 85.4M
CGroup: /system.slice/mariadb.service
└─4309 /usr/libexec/mysqld --basedir=/usr
Feb 22 19:19:46 MariaDB-Master mysql-prepare-db-dir[4204]: See the MariaDB Knowledgebase at http://mariadb.com/kb or the
Feb 22 19:19:46 MariaDB-Master mysql-prepare-db-dir[4204]: MySQL manual for more instructions.
Feb 22 19:19:46 MariaDB-Master mysql-prepare-db-dir[4204]: Please report any problems at http://mariadb.org/jira
Feb 22 19:19:46 MariaDB-Master mysql-prepare-db-dir[4204]: The latest information about MariaDB is available at http://mariadb.o>
Feb 22 19:19:46 MariaDB-Master mysql-prepare-db-dir[4204]: You can find additional information about the MySQL part at:
Feb 22 19:19:46 MariaDB-Master mysql-prepare-db-dir[4204]: http://dev.mysql.com
Feb 22 19:19:46 MariaDB-Master mysql-prepare-db-dir[4204]: Consider joining MariaDB''s strong and vibrant community:
Feb 22 19:19:46 MariaDB-Master mysql-prepare-db-dir[4204]: https://mariadb.org/get-involved/
Feb 22 19:19:46 MariaDB-Master mysqld[4309]: 2022-02-22 19:19:46 0 [Note] /usr/libexec/mysqld (mysqld 10.3.28-MariaDB-log) start>
Feb 22 19:19:47 MariaDB-Master systemd[1]: Started MariaDB 10.3 database server.
# 验证数据库3306端口是否监听
[root@CentOS84 ]#ss -tln
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
[root@CentOS84 ]#
# 进入数据库，查看开始进行复制二进制日志的文件和位置
[root@CentOS84 ]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28259 |
| mariadb-bin.000002 | 344 |
+--------------------+-----------+
2 rows in set (0.000 sec)
MariaDB [(none)]>
# 创建复制账号并授权
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.250.%' identified by 'shone';
Query OK, 0 rows affected (0.001 sec)
# 查看并验证账号信息
MariaDB [(none)]> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+-----------------------------------+
| query |
+-----------------------------------+
| User: 'root'@'127.0.0.1'; |
| User: 'repluser'@'192.168.250.%'; |
| User: 'root'@'::1'; |
| User: 'root'@'localhost'; |
| User: 'root'@'mariadb-master'; |
+-----------------------------------+
6 rows in set (0.001 sec)
# 默认数据库信息
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.002 sec)
MariaDB [(none)]>
1.1.3 配置从节点数据库
# 修改主机名、同步时间，验证IP地址和操作系统版本等信息。
[root@CentOS84 ]#hostname MariaDB-Slave
[root@CentOS84 ]#hostname
MariaDB-Slave
[root@CentOS84 ]#hostname -I
192.168.250.28
[root@CentOS84 ]#systemctl enable --now chronyd.service
Created symlink /etc/systemd/system/multi-user.target.wants/chronyd.service → /usr/lib/systemd/system/chronyd.service.
[root@CentOS84 ]#
# 安装数据库，并修改数据库配置文件
[root@CentOS84 ]#dnf -y install mariadb-server
[root@CentOS84 ]#vim /etc/my.cnf.d/mariadb-server.cnf
# 在[mysqld]字段内增加一行，定义主节点ID
[mysqld]
server-id=28
# 启动数据库并查看服务启动状况
[root@CentOS84 ]#systemctl restart mariadb
[root@CentOS84 ]#systemctl status mariadb
● mariadb.service - MariaDB 10.3 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Active: active (running) since Tue 2022-02-22 20:09:47 CST; 8s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 38461 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)
Process: 38324 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mariadb.service (code=exited, status=0/SUCCESS)
Process: 38300 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
Main PID: 38429 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 30 (limit: 23544)
Memory: 83.3M
CGroup: /system.slice/mariadb.service
└─38429 /usr/libexec/mysqld --basedir=/usr
Feb 22 20:09:47 MariaDB-Slave mysql-prepare-db-dir[38324]: See the MariaDB Knowledgebase at http://mariadb.com/kb or the
Feb 22 20:09:47 MariaDB-Slave mysql-prepare-db-dir[38324]: MySQL manual for more instructions.
Feb 22 20:09:47 MariaDB-Slave mysql-prepare-db-dir[38324]: Please report any problems at http://mariadb.org/jira
Feb 22 20:09:47 MariaDB-Slave mysql-prepare-db-dir[38324]: The latest information about MariaDB is available at http://mariadb.o>
Feb 22 20:09:47 MariaDB-Slave mysql-prepare-db-dir[38324]: You can find additional information about the MySQL part at:
Feb 22 20:09:47 MariaDB-Slave mysql-prepare-db-dir[38324]: http://dev.mysql.com
Feb 22 20:09:47 MariaDB-Slave mysql-prepare-db-dir[38324]: Consider joining MariaDB''s strong and vibrant community:
Feb 22 20:09:47 MariaDB-Slave mysql-prepare-db-dir[38324]: https://mariadb.org/get-involved/
Feb 22 20:09:47 MariaDB-Slave mysqld[38429]: 2022-02-22 20:09:47 0 [Note] /usr/libexec/mysqld (mysqld 10.3.28-MariaDB) starting >
Feb 22 20:09:47 MariaDB-Slave systemd[1]: Started MariaDB 10.3 database server.
# 验证数据库3306端口是否监听
[root@CentOS84 ]#ss -tln
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
[root@CentOS84 ]#
# 进入数据库，查看主从复制的命令格式
[root@CentOS84 ]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> help change master to
Name: 'CHANGE MASTER TO'
Description:
Syntax:
CHANGE MASTER TO option [, option] ...
option:
MASTER_BIND = 'interface_name'
| MASTER_HOST = 'host_name'
| MASTER_USER = 'user_name'
| MASTER_PASSWORD = 'password'
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = interval
| MASTER_HEARTBEAT_PERIOD = interval
| MASTER_LOG_FILE = 'master_log_name'
| MASTER_LOG_POS = master_log_pos
| RELAY_LOG_FILE = 'relay_log_name'
| RELAY_LOG_POS = relay_log_pos
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| IGNORE_SERVER_IDS = (server_id_list)
server_id_list:
[server_id [, server_id] ... ]
CHANGE MASTER TO changes the parameters that the slave server uses for
connecting to the master server, for reading the master binary log, and
reading the slave relay log. It also updates the contents of the
master.info and relay-log.info files. To use CHANGE MASTER TO, the
slave replication threads must be stopped (use STOP SLAVE if
necessary).
Options not specified retain their value, except as indicated in the
following discussion. Thus, in most cases, there is no need to specify
options that do not change. For example, if the password to connect to
your MySQL master has changed, you just need to issue these statements
to tell the slave about the new password:
STOP SLAVE; -- if replication was running
CHANGE MASTER TO MASTER_PASSWORD='new3cret';
START SLAVE; -- if you want to restart replication
MASTER_HOST, MASTER_USER, MASTER_PASSWORD, and MASTER_PORT provide
information to the slave about how to connect to its master:
o MASTER_HOST and MASTER_PORT are the host name (or IP address) of the
master host and its TCP/IP port.
*Note*: Replication cannot use Unix socket files. You must be able to
connect to the master MySQL server using TCP/IP.
If you specify the MASTER_HOST or MASTER_PORT option, the slave
assumes that the master server is different from before (even if the
option value is the same as its current value.) In this case, the old
values for the master binary log file name and position are
considered no longer applicable, so if you do not specify
MASTER_LOG_FILE and MASTER_LOG_POS in the statement,
MASTER_LOG_FILE='' and MASTER_LOG_POS=4 are silently appended to it.
Setting MASTER_HOST='' (that is, setting its value explicitly to an
empty string) is not the same as not setting MASTER_HOST at all.
Beginning with MySQL 5.5, trying to set MASTER_HOST to an empty
string fails with an error. Previously, setting MASTER_HOST to an
empty string caused START SLAVE subsequently to fail. (Bug #28796)
o MASTER_USER and MASTER_PASSWORD are the user name and password of the
account to use for connecting to the master.
In MySQL 5.5.20 and later, MASTER_USER cannot be made empty; setting
MASTER_USER = '' or leaving it unset when setting a value for for
MASTER_PASSWORD causes an error (Bug #13427949).
Currently, a password used for a replication slave account is
effectively limited to 32 characters in length; the password can be
longer, but any excess characters are truncated. This is not due to
any limit imposed by the MySQL Server generally, but rather is an
issue specific to MySQL Replication. (For more information, see Bug
#43439.)
The text of a running CHANGE MASTER TO statement, including values
for MASTER_USER and MASTER_PASSWORD, can be seen in the output of a
concurrent SHOW PROCESSLIST statement.
The MASTER_SSL_xxx options provide information about using SSL for the
connection. They correspond to the --ssl-xxx options described in
https://mariadb.com/kb/en/ssl-server-system-variables/, and
http://dev.mysql.com/doc/refman/5.5/en/replication-solutions-ssl.html.
These options can be changed even on slaves that are compiled without
SSL support. They are saved to the master.info file, but are ignored if
the slave does not have SSL support enabled.
MASTER_CONNECT_RETRY specifies how many seconds to wait between connect
retries. The default is 60. The number of reconnection attempts is
limited by the --master-retry-count server option; for more
information, see
https://mariadb.com/kb/en/replication-and-binary-log-server-system-variables/.
MASTER_HEARTBEAT_PERIOD sets the interval in seconds between
replication heartbeats. Whenever the master''s binary log is updated
with an event, the waiting period for the next heartbeat is reset.
interval is a decimal value having the range 0 to 4294967 seconds and a
resolution in milliseconds; the smallest nonzero value is 0.001.
Heartbeats are sent by the master only if there are no unsent events in
the binary log file for a period longer than interval.
Setting interval to 0 disables heartbeats altogether. The default value
for interval is equal to the value of slave_net_timeout divided by 2.
Setting @@global.slave_net_timeout to a value less than that of the
current heartbeat interval results in a warning being issued. The
effect of issuing RESET SLAVE on the heartbeat interval is to reset it
to the default value.
MASTER_LOG_FILE and MASTER_LOG_POS are the coordinates at which the
slave I/O thread should begin reading from the master the next time the
thread starts. RELAY_LOG_FILE and RELAY_LOG_POS are the coordinates at
which the slave SQL thread should begin reading from the relay log the
next time the thread starts. If you specify either of MASTER_LOG_FILE
or MASTER_LOG_POS, you cannot specify RELAY_LOG_FILE or RELAY_LOG_POS.
If neither of MASTER_LOG_FILE or MASTER_LOG_POS is specified, the slave
uses the last coordinates of the slave SQL thread before CHANGE MASTER
TO was issued. This ensures that there is no discontinuity in
replication, even if the slave SQL thread was late compared to the
slave I/O thread, when you merely want to change, say, the password to
use.
CHANGE MASTER TO deletes all relay log files and starts a new one,
unless you specify RELAY_LOG_FILE or RELAY_LOG_POS. In that case, relay
log files are kept; the relay_log_purge global variable is set silently
to 0.
Prior to MySQL 5.5, RELAY_LOG_FILE required an absolute path. In MySQL
5.5, the path can be relative, in which case the path is assumed to be
relative to the slave''s data directory. (Bug #12190)
IGNORE_SERVER_IDS was added in MySQL 5.5. This option takes a
comma-separated list of 0 or more server IDs. Events originating from
the corresponding servers are ignored, with the exception of log
rotation and deletion events, which are still recorded in the relay
log.
In circular replication, the originating server normally acts as the
terminator of its own events, so that they are not applied more than
once. Thus, this option is useful in circular replication when one of
the servers in the circle is removed. Suppose that you have a circular
replication setup with 4 servers, having server IDs 1, 2, 3, and 4, and
server 3 fails. When bridging the gap by starting replication from
server 2 to server 4, you can include IGNORE_SERVER_IDS = (3) in the
CHANGE MASTER TO statement that you issue on server 4 to tell it to use
server 2 as its master instead of server 3. Doing so causes it to
ignore and not to propagate any statements that originated with the
server that is no longer in use.
If a CHANGE MASTER TO statement is issued without any IGNORE_SERVER_IDS
option, any existing list is preserved; RESET SLAVE also has no effect
on the server ID list. To clear the list of ignored servers, it is
necessary to use the option with an empty list:
CHANGE MASTER TO IGNORE_SERVER_IDS = ();
If IGNORE_SERVER_IDS contains the server''s own ID and the server was
started with the --replicate-same-server-id option enabled, an error
results.
Also beginning with MySQL 5.5, the master.info file and the output of
SHOW SLAVE STATUS are extended to provide the list of servers that are
currently ignored. For more information, see
https://mariadb.com/kb/en/show-slave-status/, and
[HELP SHOW SLAVE STATUS].
Beginning with MySQL 5.5.5, invoking CHANGE MASTER TO causes the
previous values for MASTER_HOST, MASTER_PORT, MASTER_LOG_FILE, and
MASTER_LOG_POS to be written to the error log, along with other
information about the slave''s state prior to execution.
CHANGE MASTER TO is useful for setting up a slave when you have the
snapshot of the master and have recorded the master binary log
coordinates corresponding to the time of the snapshot. After loading
the snapshot into the slave to synchronize it to the slave, you can run
CHANGE MASTER TO MASTER_LOG_FILE='log_name', MASTER_LOG_POS=log_pos on
the slave to specify the coordinates at which the slave should begin
reading the master binary log.
The following example changes the master server the slave uses and
establishes the master binary log coordinates from which the slave
begins reading. This is used when you want to set up the slave to
replicate the master:
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
The next example shows an operation that is less frequently employed.
It is used when the slave has relay log files that you want it to
execute again for some reason. To do this, the master need not be
reachable. You need only use CHANGE MASTER TO and start the SQL thread
(START SLAVE SQL_THREAD):
CHANGE MASTER TO
RELAY_LOG_FILE='slave-relay-bin.006',
RELAY_LOG_POS=4025;
URL: https://mariadb.com/kb/en/change-master-to/
MariaDB [(none)]>
# 根据上面模板，配置好本次实验的从数据库，其中 MASTER_LOG_FILE和MASTER_LOG_POS两个字段的值是从主服务器二进制日志查看命令内复制
MariaDB [(none)]>
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.250.18',
-> MASTER_USER='repluser', MASTER_PASSWORD='shone', MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=344;
Query OK, 0 rows affected (0.039 sec)
# 启动从数据库
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]>
1.1.4 验证数据库主从复制
# 在从数据库查看主从复制的状态
MariaDB [(none)]> show slave status\G
*************************<strong> 1. row </strong>*************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.250.18
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000003
Read_Master_Log_Pos: 344
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 645
Relay_Master_Log_File: mariadb-bin.000003
Slave_IO_Running: Yes #此行和下一行必须都市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: 344
Relay_Log_Space: 1654
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: 18
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 2
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
MariaDB [(none)]>
# 在主数据库内创建新数据库summer,在从数据库验证正确复制
MariaDB [(none)]> create database summer;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| summer |
+--------------------+
4 rows in set (0.001 sec)
MariaDB [(none)]>
# 下面是从数据库查看的信息
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| summer |
+--------------------+
4 rows in set (0.002 sec)
1.2 实现Mariadb主主复制
1.2.1 架构图及环境说明
主主复制的两个节点，都可以更新数据，并且互为主从。容易产生数据不一致的问题，生产中要慎用。
两台服务器
1 第一主节点：
MariaDB-Master1
CentOS 8.4
IP: 192.168.250.18/24
10.3.28-MariaDB
2 第二主节点：
MariaDB-Master2
CentOS 8.4
IP: 192.168.250.28/24
10.3.28-MariaDB
主主复制的配置步骤：
(1) 各节点使用各自唯一server_id
(2) 都启动binary log和relay log
(3) 创建拥有复制权限的用户账号
(4) 定义自动增长id字段的数值范围各为奇偶 auto_increment_offset=1 auto_increment_offset=2
(5) 双主均要把对方指定为主节点，并启动复制线程
1.2.2 配置数据库主节点1
# 配置好两台优化好的CentOS虚拟机，安装mariadb-server
[root@CentOS84 ]#dnf -y install mariadb-server
[root@CentOS84 ]#vim /etc/my.cnf.d/mariadb-server.cnf
# 在[mysqld]段内增加四行，定义节点ID、开启二进制、开始点和增长幅度
[mysqld]
server-id=18
log-bin
auto_increment_offset=1
auto_increment_increment=2
# 启动数据库服务
[root@CentOS84 ]#systemctl start mariadb
# 查看二进制日志信息并配置数据库复制账号
[root@CentOS84 ]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28259 |
| mariadb-bin.000002 | 764 |
| mariadb-bin.000003 | 526 |
| mariadb-bin.000004 | 412 |
| mariadb-bin.000005 | 344 |
+--------------------+-----------+
5 rows in set (0.000 sec)
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.250.%' identified by 'shone';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]>
1.2.3 配置数据库主节点2
# 在数据库主节点2上安装mariadb-server
[root@CentOS84 ]#dnf -y install mariadb-server
[root@CentOS84 ]#vim /etc/my.cnf.d/mariadb-server.cnf
# 在[mysqld]段内增加四行，定义节点ID、开启二进制、开始点（注意区别于数据库主节点1）和增长幅度
[mysqld]
server-id=28
log-bin
auto_increment_offset=2
auto_increment_increment=2
# 启动数据库服务
[root@CentOS84 ]#systemctl start mariadb
# 配置数据库复制
[root@CentOS84 ]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.28-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> STOP SLAVE；
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.250.18',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='shone',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000005',
-> MASTER_LOG_POS=344;
Query OK, 0 rows affected (0.004 sec)
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.001 sec)
# 查看并记录二进制日志信息，将在回到数据库主节点1再次配置需要用到 mariadb-bin.000001和330这两个值
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 330 |
+--------------------+-----------+
1 row in set (0.001 sec)
# 查看IP18到IP28复制状态
MariaDB [(none)]> show slave status\G
*************************<strong> 1. row </strong>*************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.250.18
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000005
Read_Master_Log_Pos: 550
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 763
Relay_Master_Log_File: mariadb-bin.000005
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: 550
Relay_Log_Space: 1074
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: 18
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 2
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.001 sec)
1.2.4 再配置数据库主节点1
# 再回到数据库主节点1继续完成后续配置
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.250.28',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='shone',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000001',
-> MASTER_LOG_POS=330;
Query OK, 0 rows affected (0.004 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
# 查看IP28到IP18复制状态
MariaDB [(none)]> show slave status\G
*************************<strong> 1. row </strong>*************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.250.28
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 330
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: mariadb-bin.000001
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: 330
Relay_Log_Space: 868
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: 28
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
MariaDB [(none)]>
# 至此，两台都为主MariaDB数据库的复制（主主复制）配置全部完成
1.2.5 验证数据库主主复制
# 登入数据库主节点1 （IP:192.168.250.18）
[root@CentOS84 ]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.3.28-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# 在数据库主节点1创建一个shonedb1数据库
MariaDB [(none)]> create database shonedb1;
Query OK, 1 row affected (0.002 sec)
MariaDB [(none)]> use shonedb1;
Database changed
# 在数据库主节点1上，shonedb1数据库内创建表
MariaDB [shonedb1]> create table t1(id int auto_increment primary key,name char(10));
Query OK, 0 rows affected (0.041 sec)
# 在数据库主节点1上，shonedb1数据库的t1表内插入一对键值
MariaDB [shonedb1]> insert t1 (name) values('user1');
Query OK, 1 row affected (0.002 sec)
# 随即切换到数据库主节点2上，也在shonedb1数据库的t1表内插入一对键值
[root@CentOS84 ]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.28-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use shonedb1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [shonedb1]> insert t1 (name) values('user2');
Query OK, 1 row affected (0.002 sec)
# 再切换回数据库主节点1的数据库内，查看创建的表
MariaDB [shonedb1]> show tables;
+--------------------+
| Tables_in_shonedb1 |
+--------------------+
| t1 |
+--------------------+
1 row in set (0.001 sec)
# 再通过SSH终端软件，同时向数据库主节点1和数据库主节点2，同时发送了下面命令
MariaDB [shonedb1]> insert t1 (name) values('userX');
Query OK, 1 row affected (0.002 sec)
# 经过上面的操作，我们查看到如下的t1表内的键值信息，可以看到无论分别向数据库主节点1或数据库主节点2，还是同时向数据库主节点1和2发送向表内插入键值的命令，均会被执行，可以看到有两个ID的都为nameX的键值对。
MariaDB [shonedb1]> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | userX |
| 4 | userX |
+----+-------+
4 rows in set (0.001 sec)
# 在数据库主节点1上又创建一个shonedb2数据库
MariaDB [shonedb1]> create database shonedb2;
Query OK, 1 row affected (0.001 sec)
# 在数据库主节点1上查看数据库
MariaDB [shonedb1]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shonedb1 |
| shonedb2 |
| summer |
+--------------------+
6 rows in set (0.001 sec)
# 登入数据库主节点2 （IP:192.168.250.28） 查看到的表和数据库信息
MariaDB [shonedb1]> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | userX |
| 4 | userX |
+----+-------+
4 rows in set (0.001 sec)
# 在数据库主节点2上创建的数据库也将被复制到数据库主节点1
MariaDB [shonedb1]> create database shonedb2;
Query OK, 1 row affected (0.001 sec)
# 在数据库主节点2查看数据库
MariaDB [shonedb1]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shonedb1 |
| shonedb2 |
| summer |
+--------------------+
6 rows in set (0.001 sec)
MariaDB [shonedb1]>
下图是利用SSH终端软件SecureCRT向两个数据库主节点的表内同时发送插入userX键值的演示截图
