mysqldump使用

此处介绍mysqldump的使用

[mysql@Mserver1 ~]$ mysqldump -usystem -p menagerie > /data/mysqldata/backup/menagerie_fulldbbak.sql

[mysql@Mserver1 backup]$ ll
总用量 4
-rw-rw-r--. 1 mysql mysql 3539 11月  5 10:20 menagerie_fulldbbak.sql

[mysql@Mserver1 ~]$ mysqldump -usystem -p --databases menagerie ttt > /data/mysqldata/backup/dbbak_menagerie_ttt.sql

[mysql@Mserver1 backup]$ ll
总用量 12
-rw-rw-r--. 1 mysql mysql 7540 11月  5 10:26 dbbak_menagerie_ttt.sql
-rw-rw-r--. 1 mysql mysql 3539 11月  5 10:20 menagerie_fulldbbak.sql

[mysql@Mserver1 ~]$ mysqldump -usystem -p --all-databases  > /data/mysqldata/backup/full_dbbak.sql

[mysql@Mserver1 backup]$ ll
总用量 652
-rw-rw-r--. 1 mysql mysql   7540 11月  5 10:26 dbbak_menagerie_ttt.sql
-rw-rw-r--. 1 mysql mysql 651774 11月  5 10:28 full_dbbak.sql
-rw-rw-r--. 1 mysql mysql   3539 11月  5 10:20 menagerie_fulldbbak.sql

[mysql@Mserver1 ~]$ mysqldump -usystem -p menagerie --tab=/data/mysqldata/backup

[mysql@Mserver1 backup]$ ll
总用量 668
-rw-rw-r--. 1 mysql mysql   7540 11月  5 10:26 dbbak_menagerie_ttt.sql
-rw-rw-r--. 1 mysql mysql   1405 11月  5 10:33 event.sql
-rw-rw-rw-. 1 mysql mysql    427 11月  5 10:33 event.txt
-rw-rw-r--. 1 mysql mysql 651774 11月  5 10:28 full_dbbak.sql
-rw-rw-r--. 1 mysql mysql   3539 11月  5 10:20 menagerie_fulldbbak.sql
-rw-rw-r--. 1 mysql mysql   1460 11月  5 10:33 pet.sql
-rw-rw-rw-. 1 mysql mysql    311 11月  5 10:33 pet.txt
[mysql@Mserver1 backup]$ 

[mysql@Mserver1 backup]$ more event.txt
Fluffy  1995-05-15      litter  4 kittens, 3 female, 1 male
Buffy   1993-06-23      litter  5 puppies, 2 female, 3 male
Buffy   1994-06-19      litter  3 puppies, 3 female
Chirpy  1999-03-21      vet     needed beak straightened
Slim    1997-08-03      vet     broken rib
Bowser  1991-10-12      kennel  \N
Fang    1991-10-12      kennel  \N
Fang    1998-08-28      birthday        Gave him a new chew toy
Claws   1998-03-17      birthday        Gave him a new flea collar
Whistler        1998-12-09      birthday        First birthday
[mysql@Mserver1 backup]$ 
[mysql@Mserver1 backup]$ 
[mysql@Mserver1 backup]$ more pet.txt
Fluffy  Harold  cat     f       1993-02-04      \N
Claws   Gwen    cat     m       1994-03-17      \N
Buffy   Harold  dog     f       1989-05-13      \N
Fang    Benny   dog     m       1990-08-27      \N
Bowser  Diane   dog     m       1979-08-31      1995-07-29
Chirpy  Gwen    bird    f       1998-09-11      \N
Whistler        Gwen    bird    \N      1997-12-09      \N
Slim    Benny   snake   m       1996-04-29      \N
Puffball        Diane   hamster f       1999-03-30      \N
[mysql@Mserver1 backup]$ 

[mysql@Mserver1 ~]$ mysqldump -usystem -p menagerie pet --tab=/data/mysqldata/backup --fields-terminated-by=',' --fields-enclosed-by='"'

[mysql@Mserver1 backup]$ more pet.txt 
"Fluffy","Harold","cat","f","1993-02-04",\N
"Claws","Gwen","cat","m","1994-03-17",\N
"Buffy","Harold","dog","f","1989-05-13",\N
"Fang","Benny","dog","m","1990-08-27",\N
"Bowser","Diane","dog","m","1979-08-31","1995-07-29"
"Chirpy","Gwen","bird","f","1998-09-11",\N
"Whistler","Gwen","bird",\N,"1997-12-09",\N
"Slim","Benny","snake","m","1996-04-29",\N
"Puffball","Diane","hamster","f","1999-03-30",\N
[mysql@Mserver1 backup]$ 

[mysql@Mserver1 backup]$ mysql -usystem -p ttt < pet.sql

(system@localhost)[ttt]>load data infile '/data/mysqldata/backup/pet.txt'
    -> into table pet
    -> fields terminated by ','
    -> enclosed by '"'
    -> ;
Query OK, 9 rows affected (0.01 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0


(system@localhost)[ttt]>select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)