Mairadb数据库的备份和恢复

一、检查数据库状态 1.检查mariadb的服务状态

[root@mster-k8s ~]# systemctl status mariadb
● mariadb.service - MariaDB 10.6.7 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: active (running) since Tue 2022-04-19 11:25:41 CST; 6h ago
     Docs: man:mariadbd(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 101452 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 101428 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 101426 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 101440 (mariadbd)
   Status: "Taking your SQL requests now..."
    Tasks: 8
   Memory: 54.9M
   CGroup: /system.slice/mariadb.service
           └─101440 /usr/sbin/mariadbd

2.测试进入mariadb数据库

[root@mster-k8s ~]# mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 23
Server version: 10.6.7-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)]> 

二、配置二进制日志功能 1.修改/etc/my.conf

[root@mster-k8s ~]# vim /etc/my.cnf
[root@mster-k8s ~]# cat /etc/my.cnf
#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
log_bin=mysql-bin

2.重启服务

systemctl restart mariad

3.查看二进制日志文件

4.查看binlog开启状态

MariaDB [(none)]> show global variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.001 sec)

MariaDB [(none)]> 

三、备份数据库 1.备份全部的数据库

[root@mster-k8s mariadb]# mysqldump -uroot -p123 --all-databases > all_databases.sql
[root@mster-k8s mariadb]# ls
all_databases.sql

2.备份单个数据库

 mysqldump -uroot -p123 huawei > huawei.sql

3.备份一个数据库的表

 mysqldump -uroot -p123 huawei  student > huawei_student.sql

4.备份多个数据库

mysqldump -uroot -p123 --databases huawei mysql > huawei_mysql.sql

四、恢复数据库 1.将备份数据库的student表恢复到某个新数据库 ①当前数据库列表

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| huawei             |
| huaweinew          |
| information_schema |
| mysql              |
| performance_schema |
| redhat             |
| sys                |
+--------------------+
7 rows in set (0.000 sec)

②创建新数据库

MariaDB [(none)]> create database test_huawei;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| huawei             |
| huaweinew          |
| information_schema |
| mysql              |
| performance_schema |
| redhat             |
| sys                |
| test_huawei        |
+--------------------+
8 rows in set (0.000 sec)

③将备份的表恢复到新数据库

mysql -uroot -p123  test_huawei  use test_huawei;
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 [test_huawei]> show tables;
+-----------------------+
| Tables_in_test_huawei |
+-----------------------+
| student               |
+-----------------------+
1 row in set (0.000 sec)

MariaDB [test_huawei]> select * from student;
+----+--------+--------+------+-------+--------+-------+
| id | name   | gender | age  | class | course | grade |
+----+--------+--------+------+-------+--------+-------+
|  1 | 张三   |      0 |   18 |     3 | 语文   |    98 |
|  2 | 李四   |      0 |   17 |     3 | 数学   |    95 |
|  3 | 王五   |      1 |   16 |     2 | 物理   |    88 |
|  4 | 高峰   |      0 |   22 |     4 | 英语   |   100 |
|  5 | 陈林   |      1 |   15 |     5 | 化学   |    99 |
+----+--------+--------+------+-------+--------+-------+
5 rows in set (0.000 sec)

2.恢复数据库 ①.删除源数据库

MariaDB [(none)]> drop database huawei;
Query OK, 1 row affected (0.006 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| huaweinew          |
| information_schema |
| mysql              |
| performance_schema |
| redhat             |
| sys                |
| test_huawei        |
+--------------------+
7 rows in set (0.000 sec)

②创建空数据库

MariaDB [(none)]> create database huawei_reduction;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| huawei_reduction   |
| huaweinew          |
| information_schema |
| mysql              |
| performance_schema |
| redhat             |
| sys                |
| test_huawei        |
+--------------------+
8 rows in set (0.001 sec)

③恢复数据库

mysql -uroot -p123  huawei_reduction   show tables;
+----------------------------+
| Tables_in_huawei_reduction |
+----------------------------+
| student                    |
+----------------------------+
1 row in set (0.000 sec)

MariaDB [huawei_reduction]> select * from student;
+----+--------+--------+------+-------+--------+-------+
| id | name   | gender | age  | class | course | grade |
+----+--------+--------+------+-------+--------+-------+
|  1 | 张三   |      0 |   18 |     3 | 语文   |    98 |
|  2 | 李四   |      0 |   17 |     3 | 数学   |    95 |
|  3 | 王五   |      1 |   16 |     2 | 物理   |    88 |
|  4 | 高峰   |      0 |   22 |     4 | 英语   |   100 |
|  5 | 陈林   |      1 |   15 |     5 | 化学   |    99 |
+----+--------+--------+------+-------+--------+-------+
5 rows in set (0.000 sec)

MariaDB [huawei_reduction]>