Centos8.1中MariaDB-10.3.17数据库备份和还原

以下方法前景必须是一台用yum源安装的mysql数据库没操作过的崭新的服务器

*查看是否启用二进制日志：

show master logs;
查看主库的日志文件,以及position信息，常用于备份数据库时使用
show binary logs;显示二进制日志

MariaDB [mysql]> show master logs;
ERROR 1381 (HY000): You are not using binary logging
MariaDB [mysql]> show binary logs;
ERROR 1381 (HY000): You are not using binary logging
MariaDB [mysql]> 

*创建一个日志文件专门存放二进制日志:

mkdir /data/logbin

*修改配置文件:

vim /etc/my.cnf.d/mariadb-server.cnf 

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld/mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
log-bin=/data/logbin/mysql-bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid

"/etc/my.cnf.d/mariadb-server.cnf" 56L, 1489C    



:wq

*设置权限所有者和所属组:

chown mysql.mysql /data/logbin/

[root@CentOS8 data]# chown mysql.mysql /data/logbin/
[root@CentOS8 data]# ll /data/
total 8
drwxr-xr-x 2 mysql mysql    6 Feb 25 10:41 logbin

*重新启动mysql服务:

systemctl restart mariadb

[root@CentOS8 data]# systemctl restart mariadb

*查看文件夹是否生成二进制文件:

ll /data/logbin/  #(上面是二进制文件，下面是索引)

[root@CentOS8 data]# ll /data/logbin/
total 8
-rw-rw---- 1 mysql mysql 328 Feb 25 10:46 mysql-bin.000001
-rw-rw---- 1 mysql mysql  30 Feb 25 10:46 mysql-bin.index

*再次查看二进制文件是否启动:

show master logs; --(在centos8.11默认大小328)|
show master status;--(position默认大小)

MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+
1 row in set (0.000 sec)

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

*刷新日志(也是创建日志):

flush logs;

MariaDB [(none)]> flush logs;  
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> show master logs;
+------------------