MySQL使用mysqldump實現(xiàn)數(shù)據(jù)完全備份

更新時間：2023年07月24日 11:24:45 作者：牽著我的豬去看海

mysqldump是MySQL自帶的備份工具，可方便實現(xiàn)對MySQL的備份，也可以將指定的庫、表導出為SQL腳本，下面小編就來教大家如何使用mysqldump實現(xiàn)數(shù)據(jù)完全備份吧

mysqldump備份與恢復

MySQL自帶的備份工具，可方便實現(xiàn)對MySQL的備份

可以將指定的庫、表導出為SQL腳本

使用命令mysql導入備份的數(shù)據(jù)

mysqldump -u root -p --all-databses > all-data-$(date +%F).sql ###備份所有數(shù)據(jù)庫
mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###備份auth和mysql庫
mysqldump -u root -p auth > auth-$(data +%F).sql ###備份auth數(shù)據(jù)庫
mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###備份mysql的user表
mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###備份mysql庫user表的結構

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myadm              |
| mysql              |
| performance_schema |
| student            |
| sys                |
| tom                |
+--------------------+
7 rows in set (0.00 sec)
[root@server3 opt]# mysqldump -u root -p tom > /opt/tom.sql
Enter password: 
[root@server3 opt]# ls
tom.sql   # 導出的備份文件

對所有庫進行完全備份

[root@server3 opt]# mysqldump -uroot -pabc123 --all-databases > /backup/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump備份數(shù)據(jù)表

musqldump可針對庫內特定的表進行備份

使用mysqldump備份表的操作

mysqldump -u 用戶名 -p 【密碼】【選項】選項庫名表名 > /備份路徑/備份文件名

示例：

mysql> use tom;
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
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| chengji       |
+---------------+
1 row in set (0.00 sec)
mysql> select * from chengji;
+----------+-------+
| name     | point |
+----------+-------+
| xiaowang |    77 |
| xiaoli   |    75 |
+----------+-------+
2 rows in set (0.00 sec)
#復制tom表 name字段 張三內容 生成一張新表pp
mysql> create table pp as select * from chengji where name='xiaowang';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| chengji       |
| pp            |
+---------------+
2 rows in set (0.00 sec)
#新生成表
mysql> select *from pp;
+----------+-------+
| name     | point |
+----------+-------+
| xiaowang |    77 |
+----------+-------+
1 row in set (0.00 sec)

[root@server3 ~]# mysql -u root -p123123 chengji pp > /opt/pp.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@server3 ~]# ls /opt
pp.sql

恢復數(shù)據(jù)庫

1、使用mysqldump導出的腳本，可使用導入的方法：

1）source命令【作用于mysql模式下】2）mysql命令【作用于于linux模式下】

2、使用source恢復數(shù)據(jù)庫的步驟

登錄到mysql數(shù)據(jù)庫

執(zhí)行source備份sql腳本的路徑

source恢復的示例

MYSQL[(none)]> source /backup/all-data.sql

模擬刪除表

mysql> use tom;
Database changed
#刪除表
mysql> drop table chengji;
Query OK, 0 rows affected (0.02 sec)
mysql> drop table pp;
Query OK, 0 rows affected (0.01 sec)

進行恢復

mysql> use tom;
Database changed
mysql> use tom;
Database changed
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| tom           |
+---------------+
1 row in set (0.00 sec)
mysql> select * from tom;
+----+----------+----------+
| id | name     | address  |
+----+----------+----------+
|  1 | zhangsan | hangzhou |
+----+----------+----------+
1 row in set (0.00 sec)
mysql> insert into tom (name,address) values('lisi','wuxi');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tom;
+----+----------+----------+
| id | name     | address  |
+----+----------+----------+
|  1 | zhangsan | hangzhou |
|  2 | lisi     | wuxi     |
+----+----------+----------+
2 rows in set (0.00 sec)
mysql> create table pp as select * from tom where name='zhangsan';
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| pp            |
| tom           |
+---------------+
2 rows in set (0.00 sec)
mysql> select * from pp;
+----+----------+----------+
| id | name     | address  |
+----+----------+----------+
|  1 | zhangsan | hangzhou |
+----+----------+----------+
1 row in set (0.00 sec)
mysql> Ctrl-C -- exit!
Aborted
[root@server3 opt]# mysqldump -u root -p tom pp > /opt/pp.sql
Enter password: 
[root@server3 opt]# ls /opt/
all.sql  opt.sql  pp.sql  rh  tom.tom
[root@server3 opt]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.36-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop table tom;
ERROR 1046 (3D000): No database selected
mysql> use tom;
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
mysql> drop table tom;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table pp;
Query OK, 0 rows affected (0.00 sec)
```java
mysql> show tables;
Empty set (0.00 sec)
#恢復
mysql> source /opt/all.sql;
..省略內容
mysql> use tom;
Database changed
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| tom           |
+---------------+
1 row in set (0.00 sec)
mysql> source /opt/pp.sql;
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| pp            |
| tom           |
+---------------+
2 rows in set (0.00 sec)
mysql> select * from pp;
+----+----------+----------+
| id | name     | address  |
+----+----------+----------+
|  1 | zhangsan | hangzhou |
+----+----------+----------+
1 row in set (0.00 sec)

已經(jīng)恢復這邊我們是恢復所有數(shù)據(jù)庫

也可以單獨的對標進行備份恢復

到此這篇關于MySQL使用mysqldump實現(xiàn)數(shù)據(jù)完全備份的文章就介紹到這了,更多相關MySQL mysqldump數(shù)據(jù)備份內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: