【www.bbyears.com--linux】
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 ~]#
MYSQL数据库备份多个数据表
[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 ~]#