mariadb 怎么开启备份 mariadb主备

本文演示环境是在三台服务器上部署。三台服务器为内部网络，在搭建K8S时，已经测试打通，所以网络测试步骤直接省略，默认可以互相ping通。
安装环境为Centos7.9,安装数据库为mariadb 10.6.5，本次搭建大同小异，以master节点为例，其他节点不同之处会标注。

mariadb安装

确认本机环境

[root@node1 ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)

1、首先在 RHEL/CentOS 和 Fedora 操作系统中添加 MariaDB 的 YUM 配置文件 MariaDB.repo 文件。

vi /etc/yum.repos.d/MariaDB.repo

在Centos7中输入

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.6/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

使用Yum命令安装mariadb数据库

# yum install MariaDB-server MariaDB-client -y

安装之后修改主节点主机/etc/my.cnf配置文件

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
server_id = 131    #  一组主从组里的每个id必须是唯一值。推荐用ip位数
log-bin= mysql-bin # 二进制日志，后面指定存放位置。如果只是指定名字，默认存放在/var/lib/mysql下 如果修改的话需要在修改之后赋mysql账号文件夹权限
lower_case_table_names=1 # 不区分大小写
binlog-format=ROW    # 二进制日志文件格式
log-slave-updates=True    # slave更新是否记入日志
sync-master-info=1    # 值为1确保信息不会丢失
slave-parallel-threads=3 #同时启动多少个复制线程，最多与要复制的数据库数量相等即可
binlog-checksum=CRC32    # 效验码
master-verify-checksum=1    # 启动主服务器效验
slave-sql-verify-checksum=1   # 启动从服务器效验
bind-address = 172.30.30.131    # 监听本机网卡ip
expire_logs_days = 5 # 配置定时清理
binlog-ignore-db=mysql # 设置不要复制的数据库
binlog-ignore-db=infomation_schema
binlog-do-db=blade # 设置需要复制的数据库

从节点则修改/etc/my.cnf为:

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
server_id=132
log-bin= mysql-bin #log-bin是二进制文件
relay_log = relay-bin    # 中继日志, 后面指定存放位置。如果只是指定名字，默认存放在/var/lib/mysql下
lower_case_table_names=1

MariaDB 包安装完毕后，立即启动数据库服务守护进程，并可以通过下面的操作设置，在操作系统重启后自动启动服务。

systemctl  start  mariadb //启动mariadb数据库
systemctl enable mariadb   //开机启动mariadb
systemctl  status mariadb //查看mariadb数据库状态

现在可以通过以下操作进行安全配置：设置 MariaDB 的 root 账户密码，禁用 root 远程登录，删除测试数据库以及测试帐号，最后需要使用下面的命令重新加载权限。

//注意,其他教程种有可能让输入的mysql_secure_installation，注意二者有区别
[root@node1 ~] mariadb-secure-installation

[root@master ~]# mariadb-secure-installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] y
Enabled successfully!
Reloading privilege tables..
 ... Success!


You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] 
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@master ~]#

此处设置根据自己需求设置即可。（不懂的话随便百度下mysql初始化设置有更精细的教程，此处不多解释）

初始化之后，root用户已经被设置成只可本地登录，我们还需要一个平时使用的账号进行管理。

本地输入命令 
mysql -uroot -p
进入mariadb页面后
 CREATE USER 'admin'@'%' IDENTIFIED BY 'Abc_123456';  创建账户
 GRANT all privileges ON *.* TO 'admin'@'%'; 授予权限
 FLUSH PRIVILEGES;   刷新权限

这样就设置好了远程账户，我们可以登录测试一下。

mariadb集群搭建

以下操作在master节点中使用，使用命令登录mariadb

mysql -uroot -p
查看二进制文件名(mysql-bin.000001)和位置(1039)：
MariaDB [mysql]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     1039 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
#通过二进制日志跟偏移位置查看此时的GTID值
MariaDB [(none)]> SELECT BINLOG_GTID_POS('mysql-bin.000001', 1039);
+-------------------------------------------+
| BINLOG_GTID_POS('mysql-bin.000001', 1039) |
+-------------------------------------------+
| 0-131-5                                   |
+-------------------------------------------+
1 row in set (0.000 sec)

进入slave节点

mysql -uroot -p
MariaDB [mysql]> SET GLOBAL gtid_slave_pos='0-131-5';
Query OK, 0 rows affected, 1 warning (0.004 sec)
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.30.30.131', MASTER_USER='admin', MASTER_PASSWORD='Abc_123456',  MASTER_USE_GTID=slave_pos;
Query OK, 0 rows affected (0.024 sec)
MariaDB [mysql]> start slave;
Query OK, 0 rows affected (0.003 sec)
MariaDB [mysql]> show slave status\G
1 row in set (0.000 sec)

至此mariadb主从复制搭建主从复制搭建就完成了

如果GTID值为空,也可以直接在slave节点上,插入日志位置

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.30.30.131', MASTER_USER='admin', MASTER_PASSWORD='Abc_123456', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=328;

Query OK, 0 rows affected (0.006 sec)