CentOS 8 配置主从复制数据库（mariadb10.3 ）

---

一、数据库安装与使用

1、安装服务
[root@server ~]# dnf module -y install mariadb:10.3

2、创建新的文件设置默认字符串
[root@server ~]# vim /etc/my.cnf.d/charset.cnf
[mysqld]
character-set-server = utf8mb4
[client]
default-character-set = utf8mb4

3、启动服务
[root@server ~]# systemctl enable --now mariadb

4、防火墙允许数据库服务通过
[root@server ~]# firewall-cmd --add-service=mysql --permanent
[root@server ~]# firewall-cmd --reload

5、数据库的初始设置
[root@server ~]# mysql_secure_installation

6、连接数据库
[root@server ~]# mysql -u root -pP@ssw0rd
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.3.17-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.

7、显示用户列表
MariaDB [(none)]> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *8232A1298A49F710DBEE0B330C42EEC825D4190A |
| root | server | *8232A1298A49F710DBEE0B330C42EEC825D4190A |
| root | 127.0.0.1 | *8232A1298A49F710DBEE0B330C42EEC825D4190A |
| root | ::1 | *8232A1298A49F710DBEE0B330C42EEC825D4190A |
+------+-----------+-------------------------------------------+
4 rows in set (0.000 sec)

8、显示数据库列表
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)

9、创建测试数据库
MariaDB [(none)]> create database test_database;
Query OK, 1 row affected (0.000 sec)

10、在测试数据库上创建测试表
MariaDB [(none)]> create table test_database.test_table (id int, name varchar(50), address varchar(50), primary key (id));
Query OK, 0 rows affected (0.002 sec)

11、插入数据到测试表
MariaDB [(none)]> insert into test_database.test_table(id, name, address) values("001", "CentOS", "Hiroshima");
Query OK, 1 row affected (0.001 sec)

12、显示测试表
MariaDB [(none)]> select * from test_database.test_table;
+----+--------+-----------+
| id | name | address |
+----+--------+-----------+
| 1 | CentOS | Hiroshima |
+----+--------+-----------+
1 row in set (0.000 sec)

13、删除测试数据库
MariaDB [(none)]> drop database test_database;
Query OK, 1 row affected (0.002 sec)
MariaDB [(none)]> exit
Bye

二、数据库备份与恢复

1、安装服务（一般mariadb backup工具都是随mariadb server一起安装的）
[root@server ~]# dnf -y install mariadb-backup

2、运行备份
例如：在[/home/mariadb_backup]下获取备份
[root@server ~]# mkdir /home/mariadb_backup
[root@server ~]# mariabackup --backup --target-dir /home/mariadb_backup -u root -p P@ssw0rd
[root@server ~]# ll /home/mariadb_backup/
total 12332
-rw-r-----. 1 root root 16384 Sep 22 19:53 aria_log.00000001
-rw-r-----. 1 root root 52 Sep 22 19:53 aria_log_control
-rw-r-----. 1 root root 324 Sep 22 19:53 backup-my.cnf
-rw-r-----. 1 root root 972 Sep 22 19:53 ib_buffer_pool
-rw-r-----. 1 root root 2560 Sep 22 19:53 ib_logfile0
-rw-r-----. 1 root root 12582912 Sep 22 19:53 ibdata1
drwx------. 2 root root 4096 Sep 22 19:53 mysql
drwx------. 2 root root 20 Sep 22 19:53 performance_schema
-rw-r-----. 1 root root 77 Sep 22 19:53 xtrabackup_checkpoints
-rw-r-----. 1 root root 446 Sep 22 19:53 xtrabackup_info

3、打包备份的文件
[root@server home]# tar -cf mariadb_backup.tar mariadb_backup

4、传输文件到目标服务器
[root@server home]# scp mariadb_backup.tar [root@192.168.199.102](mailto:root@192.168.199.102):/root/mariadb_backup.tar

5、开始在目标服务器上恢复数据，停止数据库并删除现有数据
[root@client ~]# systemctl stop mariadb
[root@client ~]# rm -rf /var/lib/mysql/*

6、解压传输过来的备份数据
[root@client ~]# ls
mariadb_backup.tar
[root@client ~]# tar -xvf mariadb_backup.tar

7、在恢复任务之前运行准备任务
[root@client ~]# mariabackup --prepare --target-dir /root/mariadb_backup

8、开始恢复数据
[root@client ~]# mariabackup --copy-back --target-dir /root/mariadb_backup
[root@client ~]# chown -R mysql. /var/lib/mysql
[root@client ~]# systemctl start mariadb

三、数据库主从复制

1、更改设置并创建用于在主服务器数据库上进行复制的用户
[root@server ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin=mysql-bin #获取二进制日志
server-id=101 #定义服务器
[root@server ~]# systemctl restart mariadb
[root@server ~]# mysql -u root -pP@ssw0rd

2、创建用户
MariaDB [(none)]> grant replication slave on *.* to repl_user@'%' identified by 'password';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> exit

3、从属数据库服务器设置
[root@client ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin=mysql-bin
server-id=102
read_only=1
report-host=client
[root@client ~]# systemctl restart mariadb

4、获取主服务器上的转储数据，获取数据后将其传输到从主机
[root@server /]# mkdir /home/mariadb_backup
[root@server /]# mariabackup --backup --target-dir /home/mariadb_backup -u root -p P@ssw0rd
[root@server home]# tar -cf mariadb_backup.tar mariadb_backup
[root@server home]# scp /home/mariadb_backup.tar [root@192.168.199.102](mailto:root@192.168.199.102):/root/

5、停止从属数据库并删除现有数据
[root@client ~]# systemctl stop mariadb
[root@client ~]# rm -rf /var/lib/mysql/*

6、解压传输过来的备份数据
[root@client ~]# tar -xvf mariadb_backup.tar

7、运行准备任务
[root@client ~]# mariabackup --prepare --target-dir /root/mariadb_backup

8、运行恢复数据任务
[root@client ~]# mariabackup --copy-back --target-dir /root/mariadb_backup
[root@client ~]# chown -R mysql. /var/lib/mysql
[root@client ~]# systemctl start mariadb

9、确认主日志的值
[root@client ~]# cat /root/mariadb_backup/xtrabackup_binlog_info
mysql-bin.000001 642 0-101-2

10、数据库设置主服务器信息
[root@client ~]# mysql -u root -pP@ssw0rd
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-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)]> change master to
 -> master_host='192.168.199.101',
 -> master_user='repl_user',
 -> master_password='password',
 -> master_log_file='mysql-bin.000001',
 -> master_log_pos=642;
Query OK, 0 rows affected (0.002 sec)

11、开始复制数据
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)

12、显示状态
MariaDB [(none)]> show slave status\G *********************** 1. row *********************** Slave_IO_State: Connecting to master
 Master_Host: 192.168.199.101
 Master_User: repl_user
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000001
 Read_Master_Log_Pos: 642
 Relay_Log_File: mariadb-relay-bin.000001
 Relay_Log_Pos: 4
 Relay_Master_Log_File: mysql-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: 642
 Relay_Log_Space: 256
 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: NULL
 Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error: 
Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
 Master_Server_Id: 0
 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)]> exit
Bye

13、测试主从复制数据库
1.在主服务器上创建一个demo的数据库
[root@server /]# mysql -uroot -pP@ssw0rd
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.3.17-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)]> create database demo;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| demo |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)

2.从属服务器上查看
[root@client ~]# mysql -u root -pP@ssw0rd
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.3.17-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 databases;
+--------------------+
| Database |
+--------------------+
| demo |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)