mysql备份数据库命令_linux中MYSQL备份数据库表(多个表)

更新时间:2019-10-02    来源:linux    手机版     字体:

【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 ~]#

本文来源:http://www.bbyears.com/caozuoxitong/70788.html