mysqldump 备份单张表和恢复

1、使用工具mysqldump 
步骤一：备份表
命令：mysqldump -uroot -p1qazxsw2 -S /var/lib/mysql/mysql.sock --tab=/soft/ huzhicheng user    ----huzhicheng为数据库名称，user 为我们备份的表名称
在/soft 目录下会有两个文件，一个后缀为.sql 一个为.txt .sql 为表结构.txt 存储的是数据
步骤二：恢复表使用命令先恢复表结构
这里我恢复到test库里面做测试，使用mysql 命令创建表使用的文件就是我们刚才备份的.sql 文件 命令行如下：
mysql -uroot -p1qazxsw2 -S /var/lib/mysql/mysql.sock test 完成后去test库查看表是否创建
[root@mysql mysql]# mysql -uroot -p1qazxsw2
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.6.22-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, 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> use test
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> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | NO   |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
| addr  | varchar(50) | YES  |     | NULL    |       |
| phone | varchar(12) | YES  |     | NULL    |       |
| age   | varchar(3)  | YES  |     | NULL    |       |
| id    | varchar(10) | NO   | PRI |         |       |
| id2   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

表已经存在
下面恢复数据
登入数据库：
[root@mysql mysql]# mysql -uroot -p1qazxsw2
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.6.22-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, 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> use test
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>load data infile '/soft/user.txt' into table user;
这样就完成了数据的恢复。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26764973/viewspace-1460511/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26764973/viewspace-1460511/