此处介绍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)