linux中MYSQL备份数据库表(多个表)

在linux中备份数据表或数据库都可以使用mysqldump命令来实现了,下面我们一起来看看linux中MYSQL备份数据库表(多个表)的例子吧.

MYSQL备份数据库表,独立备份某个数据库的某个表操作:

1. mysql>usemysql
2. mysql>showtables;
3. +---------------------------+
4. |Tables_in_mysql|
5. +---------------------------+
6. |columns_priv|
7. |db|
8. |event|
9. |func|
10. |general_log|
11. |help_category|
12. |help_keyword|
13. |help_relation|
14. |help_topic|
15. |innodb_index_stats|
16. |innodb_table_stats|
17. |ndb_binlog_index|
18. |plugin|
19. |proc|
20. |procs_priv|
21. |proxies_priv|
22. |servers|
23. |slave_master_info|
24. |slave_relay_log_info|
25. |slave_worker_info|
26. |slow_log|
27. |tables_priv|
28. |time_zone|
29. |time_zone_leap_second|
30. |time_zone_name|
31. |time_zone_transition|
32. |time_zone_transition_type|
33. |user|
34. +---------------------------+
35. 28rowsinset(0.00sec)
36. [root@Mysql~]#/usr/local/mysql/bin/mysqldump-uroot-p'admin'-Bmysql|gzip>/backup/mysql-$(date+%F).sql.gz#备份mysql数据库
37. [root@Mysql~]#/usr/local/mysql/bin/mysqldump-uroot-p'admin'mysqluser|gzip>/backup/user-tables-$(date+%F).sql.gz#压缩格式备份user表
38. [root@Mysql~]#/usr/local/mysql/bin/mysqldump-uroot-p'admin'mysqlservers>/backup/servers-tables-$(date+%F).sql
39. [root@Mysql~]#egrep-v"^$|^--|＼*"/backup/servers-tables-2014-06-05.sql
40. DROPTABLEIFEXISTS`servers`;
41. CREATETABLE`servers`(
42. `Server_name`char(64)NOTNULLDEFAULT'',
43. `Host`char(64)NOTNULLDEFAULT'',
44. `Db`char(64)NOTNULLDEFAULT'',
45. `Username`char(64)NOTNULLDEFAULT'',
46. `Password`char(64)NOTNULLDEFAULT'',
47. `Port`int(4)NOTNULLDEFAULT'0',
48. `Socket`char(64)NOTNULLDEFAULT'',
49. `Wrapper`char(64)NOTNULLDEFAULT'',
50. `Owner`char(64)NOTNULLDEFAULT'',
51. PRIMARYKEY(`Server_name`)
52. )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='MySQLForeignServerstable';--phpfensi.com
53. LOCKTABLES`servers`WRITE;
54. UNLOCKTABLES;
55. [root@Mysql~]#

MYSQL数据库备份多个数据表:

1. [root@Mysql~]#/usr/local/mysql/bin/mysqldump-uroot-p'admin'mysqluser>/backup/user-tables-$(date+%F).sql
2. [root@Mysql~]#/usr/local/mysql/bin/mysqldump-uroot-p'admin'mysqlserversuser>/backup/servers-user-tables-$(date+%F).sql
3. [root@Mysql~]#egrep-v"^$|^--|＼*"/backup/servers-user-tables-2014-06-05.sql
4. DROPTABLEIFEXISTS`servers`;
5. CREATETABLE`servers`(
6. `Server_name`char(64)NOTNULLDEFAULT'',
7. `Host`char(64)NOTNULLDEFAULT'',
8. `Db`char(64)NOTNULLDEFAULT'',
9. ############################省略############################
10. `Owner`char(64)NOTNULLDEFAULT'',
11. PRIMARYKEY(`Server_name`)
12. )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='MySQLForeignServerstable';
13. LOCKTABLES`servers`WRITE;
14. UNLOCKTABLES;
15. DROPTABLEIFEXISTS`user`;
16. CREATETABLE`user`(
17. `Host`char(60)COLLATEutf8_binNOTNULLDEFAULT'',
18. `User`char(16)COLLATEutf8_binNOTNULLDEFAULT'',
19. `Password`char(41)CHARACTERSETlatin1COLLATElatin1_binNOTNULLDEFAULT'',
20. ############################省略############################
21. `max_user_connections`int(11)unsignedNOTNULLDEFAULT'0',
22. `plugin`char(64)COLLATEutf8_binDEFAULT'',
23. `authentication_string`textCOLLATEutf8_bin,
24. `password_expired`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',
25. PRIMARYKEY(`Host`,`User`)
26. )ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT='Usersandglobalprivileges';
27. LOCKTABLES`user`WRITE;
28. UNLOCKTABLES;
29. [root@Mysql~]#