MySQL backup database table in Linux (multiple tables)

MySQL Backup database table

To back up a table operation for a database independently:

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 |
+---------------------------+
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 Database
[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 (+) not NULL DEFAULT ',
' Host ' char (+) not NULL DEFAULT ',
' Db ' char (+) not NULL DEFAULT ',
' Username ' char (+) not NULL DEFAULT ',
' Password ' char (+) not NULL DEFAULT ',
' Port ' int (4) not NULL DEFAULT ' 0 ',
' Socket ' char (+) not NULL DEFAULT ',
' Wrapper ' char (+) not NULL DEFAULT ',
' Owner ' char (+) 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 database backup multiple data tables

[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 (+) not NULL DEFAULT ',
' Host ' char (+) not NULL DEFAULT ',
' Db ' char (+) not NULL DEFAULT ',
########################### #省略 ############################
' Owner ' char (+) 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 (COLLATE utf8_bin not NULL DEFAULT '),
' User ' char (COLLATE utf8_bin not NULL DEFAULT '),
' Password ' char (a) CHARACTER SET latin1 COLLATE latin1_bin not NULL DEFAULT ',
########################### #省略 ############################
' Max_user_connections ' int (one) unsigned not NULL DEFAULT ' 0 ',
' Plugin ' char (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 ~]#