linux中MYSQL備份資料庫表(多個表)

MYSQL備份資料庫表

獨立備份某個資料庫的某個表操作：

mysql> use mysql
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
28 rows in set (0.00 sec)
[root@Mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' -B mysql |gzip >/backup/mysql-$(date +%F).sql.gz    #備份mysql資料庫
[root@Mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' mysql user|gzip >/backup/user-tables-$(date +%F).sql.gz   #壓縮格式備份user表
[root@Mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' mysql servers >/backup/servers-tables-$(date +%F).sql       
[root@Mysql ~]# egrep -v "^$|^--|＼*" /backup/servers-tables-2014-06-05.sql
DROP TABLE IF EXISTS `servers`;
CREATE TABLE `servers` (
  `Server_name` char(64) NOT NULL DEFAULT '',
  `Host` char(64) NOT NULL DEFAULT '',
  `Db` char(64) NOT NULL DEFAULT '',
  `Username` char(64) NOT NULL DEFAULT '',
  `Password` char(64) NOT NULL DEFAULT '',
  `Port` int(4) NOT NULL DEFAULT '0',
  `Socket` char(64) NOT NULL DEFAULT '',
  `Wrapper` char(64) NOT NULL DEFAULT '',
  `Owner` char(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`Server_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table';
LOCK TABLES `servers` WRITE;
UNLOCK TABLES;
[root@Mysql ~]#

MYSQLDatabase Backup多個資料表

[root@Mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' mysql user >/backup/user-tables-$(date +%F).sql
[root@Mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' mysql servers user>/backup/servers-user-tables-$(date +%F).sql
[root@Mysql ~]# egrep -v "^$|^--|＼*" /backup/servers-user-tables-2014-06-05.sql 
DROP TABLE IF EXISTS `servers`;
CREATE TABLE `servers` (
  `Server_name` char(64) NOT NULL DEFAULT '',
  `Host` char(64) NOT NULL DEFAULT '',
  `Db` char(64) NOT NULL DEFAULT '',
############################省略############################
  `Owner` char(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`Server_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table';
LOCK TABLES `servers` WRITE;
UNLOCK TABLES;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
############################省略############################
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin DEFAULT '',
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';
LOCK TABLES `user` WRITE;
UNLOCK TABLES;
[root@Mysql ~]#