(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 < ./huawei_student.sql
④检查恢复的表
MariaDB [(none)]> 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 < ./huawei.sql
④查看数据库
MariaDB [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]>