$11 数据的导入导出和备份恢复相关操作

本节记录数据的导入导出和备份恢复相关操作

数据导出

1. SELECT ... FROM table INTO OUTFILE file

   (root@localhost) [book]> select * from student
       -> into outfile '~/mysqlBackup/studentbk.txt'
       -> fields terminated by ','         字段间隔符
       -> optionally enclosed by '"'       字符串包裹符
       -> lines terminated by '?';         行间隔符
   Query OK, 3 rows affected (0.01 sec)

数据导入

1. LOAD DATA INFILE file INTO TABLE table

   (root@localhost) [book]> load data infile '~/mysqlBackup/studentbk.txt'
       -> into table student
       -> fields terminated by ','
       -> optionally enclosed by '"'
       -> lines terminated by '?';
   Query OK, 3 rows affected (0.01 sec)
   Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

---

数据备份

1. mysqldump -u user -p possword --all-database > file

   $ mysqldump -u root --all-databases>./alldata.sql

2. mysqldump -u user -p possword --tab=directory database

   $ mysqldump -u root --tab=. book
   $ ls
   aaa.sql          book_cp.txt      borrow_copy.txt  student.sql      t.txt
   aaa.txt          book_view.sql    g.sql            student.txt      test_copy.sql
   book.sql         borrow.sql       g.txt            student_copy.sql test_copy.txt
   book.txt         borrow.txt       o.sql            student_copy.txt test_create.sql
   book_cp.sql      borrow_copy.sql  o.txt            t.sql            test_create.txt

数据恢复

1. mysql -u user -p password database < file

   $ mysql -u root book < ./alldata.sql

2. mysqlimport -u user -p password database file file必须写全路径,用来恢复数据,不能表结构

   $ mysqlimport -u root book ~/mysqlBackup/book_cp.txt
   book.book_cp: Records: 4  Deleted: 0  Skipped: 0  Warnings: 0