Summary of application of database backup mysqldump

[[Email protected] ~]# mysqldump -uroot -p123456 xxx > /opt/xxx.sql     #备份数据库xxx [[email protected] ~]# egrep -v  "#|\*|--|^$"  /opt/ xxx.sql drop table if exists  ' test '; create table  ' Test '   (   ' id '  int (4)  NOT NULL AUTO_INCREMENT,    ' name '  char  NOT NULL,  PRIMARY KEY  (' id ')  engine=innodb  AUTO_INCREMENT=7 DEFAULT CHARSET=latin1; lock tables  ' test '  WRITE;INSERT INTO  ' Test '  VALUES  (1, ' Martin '), (2, ' Martin ') ), (3, ' Tom '), (4, ' Marry '), (5, ' Jacky '), (6, ' advertised riveting '); unlock tables; [[email protected] ~]# mysqldump -uroot -p123456 xxx -- default-character-set=latin1 > /opt/xxx1.sql      #指定字符集导出数据库 [[Email  protected] ~]# egrep -v  "#|\*|--|^$"  /opt/xxx1.sql                                            DROP TABLE IF EXISTS  ' test '; create table  ' Test '   (   ' id '  int (4)  NOT NULL AUTO_INCREMENT,    ' name '  char  NOT NULL,  PRIMARY KEY  (' id ')  engine=innodb  AUTO_INCREMENT=7 DEFAULT CHARSET=latin1; lock tables  ' test '  WRITE;INSERT INTO  ' Test '  VALUES  (1, ' Martin '), (2, ' Martin ') ), (3, ' Tom '), (4, ' Marry '), (5, ' Jacky '), (6, ' horse ');    can see garbled no longer exists, the default is to build a table latin1unlock tables; [[email protected] ~]# mysql -uroot -p123456 -e  "use xxx;drop  table test; "     Delete Table Test[[email protected] ~]# mysql -uroot -p123456 -e  "use xxx;show tables;"    can see that the test table is gone [[Email protected] ~]# [[email protected] ~]# [[email  protected] ~]# mysql -uroot -p123456 xxx < /opt/xxx1.sql     back to database [[Email protected] ~]# [[email protected] ~]# mysql -uroot  -p123456 -e  "use xxx;show tables;"     Check the test table again with the +---------------+| tables_in_xxx |+---------------+| test           |+---------------+[[email protected] ~]#  [[email protected] ~]# mysqldump -uroot -p123456 -b xxx -- default-character-set=latin1 > /opt/xxx1_b.sql     -B  Options Export [[email  Protected] ~]# cd /opt/[[email protected] opt]# diff xxx1.sql xxx1_ b.sql    contrast without &NBSp;-b options    Add  -b options when the difference 18a19,26> -- current database:  ' xxx ' > -- > > CREATE DATABASE /*!32312 IF NOT EXISTS*/  ' xxx '  /*!40100  DEFAULT CHARACTER SET latin1 */;> > USE  ' xxx ';> > &NBSP;--51C59&LT;&NBSP;--&NBSP;DUMP&NBSP;COMPLETED&NBSP;ON&NBSP;2016-08-13&NBSP;23:07:34---> --  Dump completed on 2016-08-13 23:17:33 Description: Intuitive look   Add  -B  parameter is added when exporting the database   Create the database and connect to the database command, that is, the following two statements create database /*!32312 if not exists*/  ' xxx '  /*! 40100 default character set latin1 */; use  ' xxx '; [[email protected] opt]# mysql -uroot -p123456 -e  "drop database  xxx;show databases; "        Delete Database xxx                +--------------------+| database           |+-- ------------------+| information_schema | |  mysql              | |  performance_schema | |  test               |+-------- ------------+[[email protected] opt]# mysql -uroot -p123456  < /opt /xxx1_b.sql         because this is the data that was added to the  -B  option, you don't need to specify the database again                                           [[email protected] opt]# mysql -uroot -p123456 -e  "show  databases; "         can see the numberThe library has recovered            +--------------------+| database            |+--------------------+| information_ schema | |  mysql              | |  performance_schema | |  test               | |  xxx                |+--- -----------------+[[email protected] opt]# mysqldump -uroot -p123456 -b xxx  --default-character-set=latin1|gzip > /opt/xxx1_B.sql.gz    use gzip to compress and then export [[ Email protected] opt]# ll-rw-r--r--  1 root root 2069 aug 13  23:17 xxx1_b.sql-rw-r--r--  1 root root  805 aug 13 23:33 xxx1_b.sql.gz    can see the size of the exported data after compression   a lot of small summary: 1, the export data with-B parameter 2, Using gzip data compression for backup mysqldump  the process of backing up data with the mysqldump command is essentially outputting the data from the MySQL library in the form of a logical SQL statement to back up multiple libraries [[email  Protected] opt]# mysqldump -uroot -p123456 -b test zabbix | gzip  > /opt/mul.sql.gz[[email protected] opt]# lltotal 4900-rw-r--r--   The 1 root root 5012554 aug 15 07:52 mul.sql.gz-b  parameter is key, which means connecting multiple libraries and adding   Information on use db;  and  create database db [[email protected] opt]# mysql - uroot -p123456 -e  "show databases;" | grep -evi  "Database|info|perf" martinmartin_gbkmartin_utf8mysqltestzabbix[[email protected]  opt]# cat mysql.sh    #备份数据库多个库的脚本 #!/bin/bashfor dbname in  ' MySQL  -uroot -p123456 -e  "show databases;" | grep -evi  "Database|info|peRF "' do    mysqldump  -uroot -p123456  --events -b ${ dbname}|gzip > /opt/${dbname}.sql.gzdone [[email protected] opt]# sh  Mysql.sh [[email protected] opt]# ll -htotal 5.1m-rw-r--r-- 1 root  root  549 Aug 15 08:34 martin_gbk.sql.gz-rw-r--r-- 1 root  root  845 aug 15 08:34 martin.sql.gz-rw-r--r-- 1 root root   547 Aug 15 08:34 martin_utf8.sql.gz-rw-r--r-- 1 root root   199 aug 15 08:34 mysql.sh-rw-r--r-- 1 root root 149k aug 15  08:34 mysql.sql.gz-rw-r--r-- 1 root root  545 aug 15 08:34  test.sql.gz-rw-r--r-- 1 root root 4.9M Aug 15 08:34  zabbix.sql.gzmysql> use  martin;database changedmysql> show tables;   +------------------+|  tables_in_martin |+------------------+| student           | |  student1         |+------------------+2 rows in  set  (0.00&NBSP;SEC) backing up a single table [[email protected] opt]# mysqldump -uroot -p123456  martin student > one.sql        martin refers to the name of the library   student refers to the table name     can not add  -b parameters at this time     because the  -b parameter will be identified later is the library name back up multiple tables [[ email protected] opt]# mysqldump -uroot -p123456 martin student  Student1 > two.sqlmartin refers to the library name   student student1  refers to two table names [[email protected ] alertscripts]# mysqldump -uroot -p123456 -d martin student1  Back up the structure of the student1   table only  martin Representative Database [[email protected] ~]# mysqldump -uroot -p123456 -a  -b --events|gzip > /opt/all.sql.gz   -a represents all Databases [[email protected]  MYSQL]#&NBSP;LL-RW-RW----&NBSP;1&NBSP;MYSQL&NBSP;MYSQL&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;236&NBSP;AUG&NBSP;11 &NBSP;23:14&NBSP;MYSQL-BIN.000001-RW-RW---- 1 mysql mysql       488&NBSP;AUG&NBSP;12&NBSP;00:51&NBSP;MYSQL-BIN.000002-RW-RW---- 1 mysql mysql    &NBSP;&NBSP;&NBSP;722&NBSP;AUG&NBSP;12&NBSP;23:46&NBSP;MYSQL-BIN.000003-RW-RW---- 1 mysql mysql &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;229&NBSP;AUG&NBSP;13&NBSP;01:06&NBSP;MYSQL-BIN.000004-RW-RW---- 1  mysql mysql      244 Aug 17 14:41  MYSQL-BIN.000005-RW-RW---- 1 mysql mysql       95 aug  17 14:41 mysql-bin.Index[[email protected] mysql]# mysqldump -uroot -p123456 -a -b -f  --events|gzip > /opt/all.sql.gz   -F  will refresh bin-log[[email protected] &NBSP;MYSQL]#&NBSP;LL-RW-RW---- 1 mysql mysql      236 aug &NBSP;11&NBSP;23:14&NBSP;MYSQL-BIN.000001-RW-RW---- 1 mysql mysql      &NBSP;488&NBSP;AUG&NBSP;12&NBSP;00:51&NBSP;MYSQL-BIN.000002-RW-RW---- 1 mysql mysql   &NBSP;&NBSP;&NBSP;&NBSP;722&NBSP;AUG&NBSP;12&NBSP;23:46&NBSP;MYSQL-BIN.000003-RW-RW---- 1 mysql &NBSP;MYSQL&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;229&NBSP;AUG&NBSP;13&NBSP;01:06&NBSP;MYSQL-BIN.000004-RW-RW----  1 mysql mysql      287 Aug 17 14:50  MYSQL-BIN.000005-RW-RW---- 1 mysql mysql      149 Aug  17 14:50 mysql-bin.000006-RW-RW----&NBSP;1&NBSP;MYSQL&NBSP;MYSQL&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;149&NBSP;AUG&NBSP;17&NBSP;14:50 &NBSP;MYSQL-BIN.000007-RW-RW---- 1 mysql mysql      106 aug &NBSP;17&NBSP;14:50&NBSP;MYSQL-BIN.000008-RW-RW---- 1 mysql mysql       152 aug 17 14:50 mysql-bin.index[[email protected] mysql]# mysqldump  -uroot -p123456 --master-data=1 --compact martin        #--master-data=1  This parameter will find Bin-log position change master to master_log_file= ' mysql-bin.000008 ',  MASTER_LOG_POS=106;/*!40101 SET  @saved_cs_client      = @@ character_set_client */;/*!40101 set character_set_client = utf8 */; [[email protected] mysql]# mysql -uroot -p123456 -e  "show master  Status\g; "     *************************** 1. row ***************************             File: mysql-bin.000008         position: 106[[email protected] mysql]# mysqldump -uroot -p123456  --master-data=2 --compact martin       #--master-data=2   This parameter will find the Bin-log position, but the statement is commented, actually does not execute--change master to master_log_file= ' mysql-bin.000008 ',  master_log_pos=106;/*!40101 set  @saved_cs_client      = @ @character_set _client */;/*!40101 set character_set_client = utf8 */;

Key parameter description of mysqldump

1,-B specify more than one library, will increase the database statement and use statement

2 、--Compact Remove annotations for debug output production environment no

3.-A Back up all libraries

4,-F Refresh binlog log

5 、--master-data=1 Add binglog log file name and corresponding location point

6,-X lock table

7,-l read-only lock table

8.-D backs up the table structure only

9.-T only backs up data

、--single-transaction for InnoDB transactional database backup

Production Scenario MyISAM Backup: mysqldump -uroot -p123456 -a -b --master-data=1 -x --events| Gzip > /opt/all.sql.gz Production Scenario InnoDB Backup: mysqldump -uroot -p123456 -a -b -- Recovery of master-data=1 --events --single-transaction|gzip > /opt/all.sql.gz Database mysql>  drop database xxx; query ok, 1 row affected  (0.07 sec) mysql> show databases;    +--------------------+| database            |+--------------------+| information_schema | |  mysql              | |  performance_schema | |  test               |+-------- ------------+4 rows in set  (0.00 sec) mysql> system ls /optrh   xxx1_B.sql  xxx1_B.sql.gz  xxx1.sql  xxx.sqlmysql> source /opt/xxx1_B.sql                             #用source命令恢复数据库mysql > show databases;        +--------------------+| database            |+--------------------+| information_schema | |  mysql              | |  performance_schema | |  test               | |  xxx                |+--- -----------------+5 rows in set  (0.00 SEC)

This article from "Thick tak" blog, declined reprint!

Summary of application of database backup mysqldump