MySQL的备份与还原

最新推荐文章于 2025-12-26 14:31:53 发布

原创最新推荐文章于 2025-12-26 14:31:53 发布 · 171 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #mariadb #运维 #database

数据库专栏收录该内容

10 篇文章

订阅专栏

1.备份数据库school到/backup目录

[root@:masterI backup]# mysqldump -u root -pRoot=12345. school > /backup/school.sql
[root@:masterI backup]# ll
-rw-r--r--. 1 root root 3495 Jul 20 17:43 school.sql

2.备份MySQL数据库为待删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库

[root@:masterI backup]# mysqldump -u root -pRoot=12345. --add-drop-database  school > /backup/school_1.sql
[root@:masterI backup]# ll
-rw-r--r--. 1 root root 3495 Jul 20 17:53 school_1.sql

3.直接将MySQL数据库压缩备份

[root@:masterI backup]# mysqldump -u root -pRoot=12345.  school | gzip > /backup/db_school.sql.gz
[root@:masterI backup]# ll
-rw-r--r--. 1 root root 1180 Jul 20 17:56 db_school.sql.gz

4.备份MySQL数据库某个(些)表。此例备份student表

[root@:masterI backup]# mysqldump -u root -pRoot=12345.  school student > /backup/table_sch_student.sql
[root@:masterI backup]# ll
-rw-r--r--. 1 root root 2518 Jul 20 17:57 table_sch_student.sql

5.同时备份多个MySQL数据库（其他数据库素材另外准备）

[root@:masterI backup]# mysqldump -u root -pRoot=12345.  -B school test1 > /backup/db_school_test1.sql
[root@:masterI backup]# ll
-rw-r--r--. 1 root root 7399 Jul 20 18:03 db_school_test1.sql

6.仅仅备份数据库结构

[root@:masterI backup]# mysqldump -u root -pRoot=12345. -d school > /backup/structure_test1.sql
[root@:masterI backup]# ll
-rw-r--r--. 1 root root 2500 Jul 20 18:08 structure_test1.sql

7.备份服务器上所有数据库

[root@:masterI backup]# mysqldump -u root -pRoot=12345. -A > /backup/allDatabases.sql
[root@:masterI backup]# ll
-rw-r--r--. 1 root root 925234 Jul 20 18:10 allDatabases.sql

8.还原MySQL数据库

mysql> drop database school;
Query OK, 2 rows affected (0.01 sec)

mysql> create database school;
Query OK, 1 row affected (0.00 sec)
mysql> use school
Database changed
mysql> show tables;
Empty set (0.00 sec)

[root@:masterI backup]#  mysql -uroot -pRoot=12345. school  < /backup/school.sql

mysql> show tables from school;
+------------------+
| Tables_in_school |
+------------------+
| score            |
| student          |
+------------------+
2 rows in set (0.00 sec)

9.还原压缩的MySQL数据库

mysql> create database school;
Query OK, 1 row affected (0.00 sec)
mysql> use school
Database changed
mysql> show tables;
Empty set (0.00 sec)

[root@:masterI backup]# zcat db_school.sql.gz | mysql -uroot -pRoot=12345. school 

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| score            |
| student          |
+------------------+
2 rows in set (0.00 sec)

10.使用xtrabackup 备份数据库

[root@:masterI backup]# innobackupex --user=root --password=Root=12345. --no-timestamp /backup/full_`date +%F `

[root@:masterI full_2023-07-22]# ll /backup/full_2023-07-22/
total 12336
-rw-r-----. 1 root root      487 Jul 22 13:06 backup-my.cnf
drwxr-x---. 2 root root      247 Jul 22 13:06 booksDB
drwxr-x---. 2 root root      220 Jul 22 13:06 day4
drwxr-x---. 2 root root      154 Jul 22 13:06 day5
drwxr-x---. 2 root root       20 Jul 22 13:06 day6
drwxr-x---. 2 root root      128 Jul 22 13:06 haxi
-rw-r-----. 1 root root      722 Jul 22 13:06 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Jul 22 13:06 ibdata1
drwxr-x---. 2 root root       56 Jul 22 13:06 Market
drwxr-x---. 2 root root     4096 Jul 22 13:06 mysql
drwxr-x---. 2 root root     8192 Jul 22 13:06 performance_schema
drwxr-x---. 2 root root       92 Jul 22 13:06 school
drwxr-x---. 2 root root     8192 Jul 22 13:06 sys
drwxr-x---. 2 root root       56 Jul 22 13:06 Team
drwxr-x---. 2 root root       20 Jul 22 13:06 test
drwxr-x---. 2 root root      233 Jul 22 13:06 test1
-rw-r-----. 1 root root      135 Jul 22 13:06 xtrabackup_checkpoints
-rw-r-----. 1 root root      449 Jul 22 13:06 xtrabackup_info
-rw-r-----. 1 root root     2560 Jul 22 13:06 xtrabackup_logfile

11.在另外的数据库服务器上还原xtrabackup 备份

[root@:masterI backup]# scp full_2023-07-22.tar 192.168.127.161:/backup/

[root@dbNode1 backup]# mkdir full_2023-07-22
[root@dbNode1 backup]# tar xvf full_2023-07-22.tar -C /backup/

[root@dbNode1 mysql]# innobackupex --apply-back /backup/full_2023-07-22/
[root@dbNode1 mysql]# systemctl stop mysqld
[root@dbNode1 mysql]# innobackupex --copy-back /backup/full_2023-07-22/
  ...done
230722 01:23:55 completed OK!

[root@dbNode1 mysql]# chown -R mysql:mysql /var/lib/mysql/
[root@dbNode1 mysql]# systemctl start mysqld

12.使用mydumper备份数据库

[root@:masterI ~]# mydumper -u root -p 'Root=12345.' -B school -o /backup/pop/
[root@:masterI ~]# ll /backup/pop/
total 24
-rw-r--r--. 1 root root 140 Jul 20 17:31 metadata
-rw-r--r--. 1 root root 120 Jul 20 17:31 school-schema-create.sql
-rw-r--r--. 1 root root   0 Jul 20 17:31 school-schema-triggers.sql
-rw-r--r--. 1 root root 352 Jul 20 17:31 school.score.00000.sql
-rw-r--r--. 1 root root 423 Jul 20 17:31 school.score-schema.sql
-rw-r--r--. 1 root root 503 Jul 20 17:31 school.student.00000.sql
-rw-r--r--. 1 root root 550 Jul 20 17:31 school.student-schema.sql

13.使用mydumper恢复数据库

mysql> drop database school;
Query OK, 2 rows affected (0.01 sec)

[root@:masterI backup]# myloader -u root -p Root=12345. -d /backup/pop/ -o -B school

mysql> show tables from school;
+------------------+
| Tables_in_school |
+------------------+
| score            |
| student          |
+------------------+
2 rows in set (0.00 sec)