mysqldump Backup and Restore command usage

one. mysql backup command:
1. Backing up a single database
mysql> system mysqldump-uroot-pmybaby99 WordPress >/tmp/wordpress.sql;
mysql> system Ls-l/tmp/wordpress.sql;
-rw-r--r--1 root root 292760 Oct 18:43/tmp/wordpress.sql
2. Compress the backup:
[Email protected] mysql]# mysqldump-uroot-pmybaby99 wordpress|gzip >/server/bak/wordpress.sql.gz;
[Email protected] mysql]# ll-l/server/bak/wordpress.sql.gz
-rw-r--r--1 root root 77219 Oct 21:21/server/bak/wordpress.sql.gz #生产环境常用, high text compression rate
3. When exporting. Add the default character set. Easy to view SQL files. It is not garbled if no arguments are added, and are not garbled when imported into the database .
[Email protected] mysql]# mysqldump-uroot-pmybaby99--default-character-set=utf8 wordpress|gzip >/server/bak/ wordpress.sql.utf8.gz;
[Email protected] mysql]# ll/server/bak/
Total 452
-rw-r--r--1 root root 292760 Oct 21:34 wordpress.sql
-rw-r--r--1 root root 77219 Oct 21:20 wordpress.sql.gz
-rw-r--r--1 root root 77216 Oct 21:36 wordpress.sql.utf8.gz
[Email protected] mysql]# mysqldump-uroot-pmybaby99--DEFAULT-CHARACTER-SET=GBK wordpress|gzip >/server/bak/ wordpress.sql.gbk.gz; [Email protected] mysql]# ll/server/bak/
Total 532
-rw-r--r--1 root root 292760 Oct 21:34 wordpress.sql
-rw-r--r--1 root root 76938 Oct 21:37 wordpress.sql.gbk.gz
-rw-r--r--1 root root 77219 Oct 21:20 wordpress.sql.gz
-rw-r--r--1 root root 77216 Oct 21:36 wordpress.sql.utf8.gz
4. Backup multiple databases at the same time, commonly used in production environments
[[email protected] mysql]# mysql-uroot-pmybaby99-b wordpress test |gzip >/server/bak/word_test.sql.gz
[Email protected] mysql]# ll-l/server/bak/word_test.sql.gz
-rw-r--r--1 root root 3424 Oct 21:53/server/bak/word_test.sql.gz
5. Examples of e-parameters commonly used in scripts
[Email protected] mysql]# mysql-uroot-pmybaby99-e "show databases;"
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Test |
| WordPress |
+--------------------+
6. Export all libraries with the-a parameter
[Email protected] mysql]# mysqldump-uroot-pmybaby99-a |gzip >/server/bak/all.sql.gz
--warning:skipping The data of table mysql.event. Specify the--events option explicitly.
[Email protected] mysql]# ll/server/bak/all.sql.gz
-rw-r--r--1 root root 297734 Oct 21:59/server/bak/all.sql.gz
7. Backing up a single table
[[email protected] ~]# mysqldump-uroot-pmybaby99 wordpress wp_links >/server/bak/wordpress.table.sql
[Email protected] ~]# ll-l/server/bak/
Total 836
-rw-r--r--1 root root 297734 Oct 21:59 all.sql.gz
-rw-r--r--1 root root 292760 Oct 21:34 wordpress.sql
-rw-r--r--1 root root 76938 Oct 21:37 wordpress.sql.gbk.gz
-rw-r--r--1 root root 77219 Oct 21:20 wordpress.sql.gz
-rw-r--r--1 root root 77216 Oct 21:36 wordpress.sql.utf8.gz
-rw-r--r--1 root root 2479 Oct 14:21 wordpress.table.sql
-rw-r--r--1 root root 3424 Oct 21:53 word_test.sql.g
8. Backing up multiple tables
[[email protected] ~]# mysqldump-uroot-pmybaby99 wordpress wp_links wp_options >/server/bak/wordpress.tables.sql
[Email protected] ~]# ll-l/server/bak/
Total 1112
-rw-r--r--1 root root 297734 Oct 21:59 all.sql.gz
-rw-r--r--1 root root 292760 Oct 21:34 wordpress.sql
-rw-r--r--1 root root 76938 Oct 21:37 wordpress.sql.gbk.gz
-rw-r--r--1 root root 77219 Oct 21:20 wordpress.sql.gz
-rw-r--r--1 root root 77216 Oct 21:36 wordpress.sql.utf8.gz
-rw-r--r--1 root root 2479 Oct 14:21 wordpress.table.sql
-rw-r--r--1 root root 275194 Oct 14:23 wordpress.tables.sql
-rw-r--r--1 root root 3424 Oct 21:53 word_test.sql.gz
9. Back up the table structure only, using the-D parameter
[[email protected] ~]# mysqldump-uroot-pmybaby99-d wordpress wp_links wp_options >/server/bak/wordpress.tables.des C.sql
[Email protected] ~]# egrep-v "^$|\*|--"/server/bak/wordpress.tables.desc.sql
DROP TABLE IF EXISTS ' wp_links ';
CREATE TABLE ' Wp_links ' (
' link_id ' bigint () unsigned not NULL auto_increment,
' Link_url ' varchar (255) Not NULL DEFAULT ' ',
' link_name ' varchar (255) Not NULL DEFAULT ' ',
' link_image ' varchar (255) Not NULL DEFAULT ' ',
' link_target ' varchar (+) not NULL DEFAULT ' ',
' link_description ' varchar (255) Not NULL DEFAULT ' ',
' link_visible ' varchar (a) Not NULL DEFAULT ' Y ',
' Link_owner ' bigint () unsigned not NULL DEFAULT ' 1 ',
' link_rating ' int (one) not NULL DEFAULT ' 0 ',
' link_updated ' datetime not NULL DEFAULT ' 0000-00-00 00:00:00 ',
' Link_rel ' varchar (255) Not NULL DEFAULT ' ',
' Link_notes ' Mediumtext not NULL,
' Link_rss ' varchar (255) Not NULL DEFAULT ' ',
PRIMARY KEY (' link_id '),
KEY ' link_visible ' (' link_visible ')
) Engine=myisam DEFAULT Charset=utf8;
DROP TABLE IF EXISTS ' wp_options ';
CREATE TABLE ' wp_options ' (
' option_id ' bigint () unsigned not NULL auto_increment,
' option_name ' varchar (+) not NULL DEFAULT ' ',
' Option_value ' longtext not NULL,
' autoload ' varchar () not NULL DEFAULT ' yes ',
PRIMARY KEY (' option_id '),
UNIQUE KEY ' option_name ' (' option_name ')
) Engine=myisam auto_increment=148 DEFAULT Charset=utf8;
two. mysql Database restore:
1. Restore a single database (production environment standard usage), or you may not need to go to the console and restore directly to the command line. Downlink is a demo of logging into MySQL controller
mysql> System mysql-uroot-pmybaby99 Test </tmp/wordpress.sql;
Mysql> Show tables;
+-----------------------+
| Tables_in_test |
+-----------------------+
| Wp_commentmeta |
| wp_comments |
| Wp_links |
| wp_options |
| Wp_postmeta |
| wp_posts |
| Wp_term_relationships |
| wp_term_taxonomy |
| wp_terms |
| Wp_usermeta |
| Wp_users |
+-----------------------+
Rows in Set (0.01 sec
2. Restore the database with the source command, as long as you have access to MySQL console
Mysql> mysql> Source/server/bak/wordpress.sql
Query OK, 0 rows Affected (0.00 sec)
3. Restore the database. If you are backing up the database with the-b parameter. You can not specify a database
Mysql-uroot-pmybaby99 >/server/bak/xxx.sql
three. mysql with e-reference for non-interactive conversations
1. For example, view a table in the library
[[email protected] ~]# mysql-uroot-pmybaby99-e ' Set names utf8;use wordpress;select * from Wp_users; '
2. View MySQL status
[Email protected] ~]# mysql-uroot-pmybaby99-e "show processlist;"
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 24 | Root | localhost | NULL |    Query | 0 | NULL | Show Processlist |
+----+------+-----------+------+---------+------+-------+------------------+
3. View MySQL variables
[Email protected] ~]# mysql-uroot-pmybaby99-e "show variables;" | Tail-5
Version_comment Source Distribution
Version_compile_machine x86_64
Version_compile_os Unknown-linux-gnu
Wait_timeout 28800
Warning_count 0
4. View MySQL Global status
[[email protected] ~]# mysql-uroot-pmybaby99-e "show global status;" | Tail-5
Threads_connected 1
Threads_created 26
Threads_running 1
Uptime 3225
Uptime_since_flush_status 3225

This article is from "Spider-Man" blog, please be sure to keep this source http://txidc.blog.51cto.com/9138217/1568407

mysqldump Backup and Restore command usage