"Backing up single database-Multiple parameters" syntax: Mysqldump-u username-P database name > backup file name"View the character set of a database"Mysql> Show variables like ' character_set% '; +--------------------------+--------------------------------------- ----+| variable_name | value |+--------------------------+-------------------------------------------+| character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | Character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | Utf8 | | character_set_system | utf8 | | character_sets_dir | /application/mysql-5.5.32/share/charsets/|+--------------------------+ ————————————————————— + "View the character set of the library corresponding to the database"Mysql> Show CREATE Database oldboy\g*************************** 1. Row *************************** database:oldboycreate database:create Database ' Oldboy '/*!40100 DEFAULT CHARACTER Set UTF8 */1 row in Set (0.00 sec)
"View the structure of a database table"Mysql> Show CREATE TABLE student\g*************************** 1. Row *************************** table:studentcreate table:create Table ' student ' (' ID ' int (one-by-one) DEFAULT NULL, ' n Ame ' varchar (255) default NULL) Engine=innodb default charset=utf81 row in Set (0.00 sec)"BACKUP DATABASE command"
[Email protected] ~]# mysqldump-uroot-p ' oldboy123 ' oldboy >/opt/mysql_bak.sql[[email protected] ~]# ll/opt/-rw-r-- R--. 1 root root 3221 Feb 21:02 mysql_bak.sqlBackup DATABASE Command "Check that the backed up database is correct" "Back up the database and check that the backed up database is correct""Can not operate, just show normal when checking"[Email protected] ~]# mysqldump-uroot-p ' oldboy123 ' Oldboy >/opt/mysql_bak.sql [[email protected] ~]# egrep-v "#|\*| --|^$ "/opt/mysql_bak.sql DROP TABLE IF EXISTS ' student '; CREATE TABLE ' student ' (' ID ' int (one) default null, ' name ' varchar (255) default null) ENGINE=INNODB default Charset=utf8 ; LOCK TABLES ' student ' write;insert into ' student ' VALUES (3, ' Test 003 '), (1, ' Test 001 '), (2, ' Test 002 '), (4, ' Test 004 '), (5, ' Test 005 '); UNLOCK TABLES; "Specify Character Set backup (export) when backing up" "This will not be garbled when checking the exported database, the specified character set is the character set of the database table""Restore the backed up data while checking the recovered data"[Email protected]/]# mysql-uroot-poldboy123 Oldboy </opt/mysql_bak.sql [[email protected]/]# mysql-uroot-poldbo Y123-e "SELECT * from Oldboy.student", +------+-----------+| ID | Name |+------+-----------+| 3 | Test 003 | | 1 | Test 001 | | 2 | Test 002 | | 4 | Test 004 | | 5 | Test 005 |+------+-----------+"plus-b backup Database"[Email protected] ~]# mysqldump-uroot-poldboy123-b oldboy >/opt/mysql_bak_b.sql [[email protected] ~]# egrep-v "#| \*|--|^$ "/opt/mysql_bak_b.sql use ' Oldboy ';D rop TABLE IF EXISTS ' student '; CREATE TABLE ' student ' (' ID ' int (one) default null, ' name ' varchar (255) default null) ENGINE=INNODB default Charset=utf8 ; LOCK TABLES ' student ' write;insert into ' student ' VALUES (3, ' Test 003 '), (1, ' Test 001 '), (2, ' Test 002 '), (4, ' Test 004 '), (5, ' Test 005 '); UNLOCK TABLES;The "plus B" parameter is added to the command to create the database and connect to the database ""and use plus B for data recovery""Specify compression commands to compress backup MySQL data"[Email protected] ~]#mysqldump-uroot-poldboy123-b oldboy|gzip >/opt/mysql_bak_b.sql.gz[Email protected] ~]# ll/opt/-rw-r--r--. 1 root root 2058 Feb 22:48 mysql_bak_b.sql-rw-r--r--. 1 root root 771 Feb 16 22:57mysql_bak_b.sql.gz "Process focus of database Backup" 1. When exporting data, use the-B parameter 2 to compress the backed-up data with gziphow the 5.1.3 mysqldump worksThe process of backing up data with the mysqldump command is actually the process of outputting or generating a backed-up file directly from the MySQL library in the form of a logical SQL statement-B Parameter Description: "Steps for multi-Library backup"
"1. View Database"[Email protected] opt]# mysql-uroot-poldboy123-e "show databases;" +--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Oldboy | | Performance_schema | | t_a | | Test |+ —————————— +"2. Filter unwanted Information"[Email protected] opt]# mysql-uroot-poldboy123-e "show databases;" | Grep-evi "Database|infor|perfor" Mysqloldboyt_atest[[email protected]"3. Replace the statement to be executed"[Email protected] opt]# mysql-uroot-poldboy123-e "show databases;" | Grep-evi "database|infor|perfor" |sed ' s#^ #mysqldump-uroot-poldboy123-b #g ' Mysqldump-uroot-poldboy123-b mysqlmysqld Ump-uroot-poldboy123-b oldboymysqldump-uroot-poldboy123-b t_amysqldump-uroot-poldboy123-b Test
"4. Replace with the specified backup path"[Email protected] opt]# mysql-uroot-poldboy123-e "show databases;" | Grep-evi "database|infor|perfor" |sed-r ' s#^ ([a-z].*$) #mysqldump-uroot-poldboy123-b \1 >/opt/\1#g ' mysqldump- Uroot-poldboy123-b MySQL >/opt/mysqlmysqldump-uroot-poldboy123-b oldboy >/opt/oldboymysqldump-uroot-poldboy1 23-b t_a >/opt/t_amysqldump-uroot-poldboy123-b Test >/opt/test"5. Replace with the path of the specified backup, compress the backup"[Email protected] opt]# mysql-uroot-poldboy123-e "show databases;" | Grep-evi "database|infor|perfor" |sed-r ' s#^ ([a-z].*$) #mysqldump-uroot-poldboy123-b \1|gzip >/opt/\1.sql.gz#g ' Mysqldump-uroot-poldboy123-b mysql|gzip >/opt/mysql.sql.gzmysqldump-uroot-poldboy123-b oldboy|gzip >/opt/ Oldboy.sql.gzmysqldump-uroot-poldboy123-b t_a|gzip >/opt/t_a.sql.gzmysqldump-uroot-poldboy123-b test|gzip >/ Opt/test.sql.gz"6. Execute command "[Email protected] opt]# mysql-uroot-poldboy123-e "show databases;" | Grep-evi "database|infor|perfor" |sed-r ' s#^ ([a-z].*$) #mysqldump-uroot-poldboy123-b \1|gzip >/opt/\1.sql.gz#g ' | bash--warning:skipping The data of table mysql.event. Specify the--events option explicitly."7. Check the results of the backup"[Email protected] opt]# ll/opt/total 160-rw-r--r--. 1 root root 144306 Feb 23:23 mysql.sql.gz-rw-r--r--. 1 root root 769 Feb 23:23 oldboy.sql.gzdrwxr-xr-x. 2 root root 4096 Mar rh-rw-r--r--. 1 root root 748 Feb 23:23 t_a.sql.gz-rw-r--r--. 1 root root 508 Feb 23:23 test.sql.gz"7. Ignore warning Messages"[Email protected] opt]# mysql-uroot-poldboy123-e "show databases;" | Grep-evi "database|infor|perfor" |sed-r ' s#^ ([a-z].*$) #mysqldump-uroot-poldboy123--events-b \1|gzip >/opt/bak/\ 1.sql.gz#g ' |bash[[email protected] opt]# "Backing up a single table" [[email protected] mysql]# mysqldump-uroot-poldboy123--compactOldboy student/*!40101 Set @saved_cs_client = @ @character_set_client */;/*!40101 Set character_set_client = UTF8 */; CREATE TABLE ' student ' (' ID ' int (one) default null, ' name ' varchar (255) default null) ENGINE=INNODB default Charset=utf8 ;/*!40101 SET character_set_client = @saved_cs_client */;insert into ' Student ' VALUES (3, ' Test 003 '), (1, ' Test 001 '), (2, ' Test 002 '), (4, ' Test 004 '), (5, ' Test 005 ');"Backing up the database's table structure (without data)" [[email protected] mysql]# mysqldump-uroot-poldboy123--compact-b-D oldboy CREATE DATABASE/*! 32312 IF not exists*/' Oldboy '/*!40100 DEFAULT CHARACTER set UTF8 */; use ' Oldboy ';/*!40101 set @saved_cs_client &nbs p; = @ @character_set_client */;/*!40101 Set character_set_client = UTF8 */; CREATE TABLE ' student ' ( ' id ' int (one) default null, ' name ' varchar (255) default NULL) engine= InnoDB DEFAULT charset=utf8;/*!40101 SET character_set_client = @saved_cs_client */; [[email protected] mysql]# "Backing up the structure of a table in a library" [[email protected] mysql]# mysqldump-uroot- poldboy123--compact-d oldboy student/*!40101 SET @saved_cs_client = @ @character_set_ Client */;/*!40101 SET character_set_client = UTF8 */; CREATE TABLE ' student ' ( ' id ' int (one) default null, ' name ' varchar (255) default NULL) engine= InnoDB DEFAULT charset=utf8;/*!40101 SET character_set_client = @Saved_cs_client */; " -t separate data and structure everywhere" "Direct backup Data" "-t parameter" [[email protected] MySQL] # mysqldump-uroot-poldboy123--compact-t Oldboy studentinsert into ' Student ' VALUES (3, ' Test 003 '), (1, ' Test 001 '), (2, ' Test 002 ') ), (4, ' Test 004 '), (5, ' Test 005 '); "-A" entire backup database-including the available and table [[[email protected] mysql]# mysqldump-uroot-poldboy123-a-B- -events|gzip >/opt/a.sql.gz[[email protected] mysql]# ll/opt/total 308-rw-r--r--. 1 root root 144664 Feb 01:45 a.sql.gzdrwxr-xr-x. 2 root root 4096 Feb 23:25 bak open log logging in my.cnf Log-bin=mysql-binWill have all of the MySQLchange the record down"Path to MySQL log" [[email protected] data]# PWD/APPLICATION/MYSQL/DATALL-RW-RW----. 1 mysql mysql 541 Feb 18:33 mysql-bin.000001-rw-rw----. 1 mysql mysql 11815 Feb 22:24 mysql-bin.000002-rw-rw----. 1 mysql mysql 3557 Feb 22:51 mysql-bin.000003-rw-rw----. 1 MySQL MySQL 1851 Feb 01:31 mysql-bin.000004-rw-rw----. 1 mysql mysql 22:51 mysql-bin.index "-F refresh Binlog" [[email protected] data]# Mysqldump-uroot-poldboy 123-a-b-f--events|gzip >/opt/a.sql.gz "--master-data=1 automatically records the current location of Binlog when backing up" [[email protected] data]# my sqldump-uroot-poldboy123--master-data=1--compact OldboyChange MASTER to master_log_file= ' mysql-bin.000008 ', master_log_pos=107;/*!40101 SET @saved_cs_client = @ @character_set_client */; "View Binlog Records" mysqlbinlog "Summary: MySQL statement summary finishing" " -t separate data and structure everywhere "" -t Direct backup Data "" -a Back up all libraries " " -b Specify multiple libraries Add Repository statement and use statement Backup library "" -d Back up only tables " -d oldboy Student" -compact Remove annotations, suitable for debug output, production not. Streamline content General testing use "" --events Ignore warning messages "" -f Refresh Binlog Log  , that is, create an incremented new log file Cut (refresh) binlog "" --master-data=1 Add Binlog log file names and corresponding location points automatically record Binlog's current location " -x -- when backing up lock-all-tables Lock Table "" -e Execute SQL statements outside the database " " Production scenario Backup MyISAM backup command mysqldump-uroot-poldboy123-a-b-master-data=2-x--events|gzip >/opt/all.sql.gz innodb Backup command: Recommended use of Mysqldump-uroot-poldboy123-a-b-master-data=2-x-events-single-transaction|gzip >/opt/all.sql.gz "Professional DB backup operation" 5.2 Recovery Database Practice "restore command Source" mysql> source/opt/mysql.b.sqlquery OK, 1 row affected (0.00 sec)
"Unzip the backup compressed file" Gzip-d msyql.bak.sql.gz will back up all the libraries, restore to the database [[email protected] data]# for dbname in ' LS *.sql|sed ' s#_bak.sql# #g ';d o mysql-uroot-poldboy123 < ${dbname}_bak.sql;done "MySQL system some Commands" " View the user and number of the current linked database "[[email protected] data]# mysql-uroot-poldboy123-e" show processlist; +----+------+-----------+------+---------+-------+-------+------------------+| Id | User | host | db | Command | time | State | info |+----+------+-----------+-- ----+---------+-------+-------+------------------+| 3 | Root | localhost | NULL | sleep | 16551 | | null | | 58 | Root | localhost | NULL | query | 0 | null | Show Processlist |&Nbsp;
Full 1.2.3 MySQL database backup and restore command combat
