mysqldump 备份单张表和恢复

rile

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 /user.sql      test 为恢复的库，user.sql 为备份表的表结构 
　　完成后去test库查看表是否创建
　　[iyunv@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>　　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    |       |

　　|>
　　|>　　+-------+-------------+------+-----+---------+-------+
　　8 rows in set (0.00 sec)
　　
表已经存在　　下面恢复数据
　　登入数据库：
　　[iyunv@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>　　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;
　　这样就完成了数据的恢复。