MySQL Backup and recovery multi-Library backup

Instance:
Backup:
Mysqldump-u root-p ' 123456 ' Kaliboy>/opt/kaliboy.sql

Specify a character Set backup:
Mysqldump-u root-p ' 123456 '--default-character-set=utf8 kaliboy>/opt/kaliboy.sql

Backup parameters:
-e: Can be executed after MySQL command; separate
-B: The function of the parameter is to increase the command to create the database and connect the database
-D: Backing up the table structure
-T: Backing up data
-a-b--events backing up all databases
-F: Refresh the Binlog log file
--master-data=1 (=2): Add binlog log file name and corresponding location point
-X,--lock-all-tables Lock table
-L,--lock-tables read-only lock table
--compact: Remove Comments
--single-transaction: For InnoDB Transactional database backup

Mysqldump-u root-p ' 123456 '-B kaliboy>/opt/kaliboy_bak.sql
Restore the database:
Mysql-uroot-p ' 123456 ' </opt/kaliboy_bak.sql

To view the contents of a backup:
Egrep-v "#|\*|--|^$"/opt/kaliboy.sql
To view the character set:
Mysql-uroot-p ' 123456 '-e "show variables like '%character% '"

MYQSL Multi-Library backup:
Mysqldump-uroot-p ' 123456 '-B (library name) Kaliboy (library name) Classsinfo (library name) Kaliboy_gbk|gzip >/opt/mysql_bak.sql.gz
MySQL Sub-Library backup:
Mysql-uroot-p ' 123456 '-e "show databases;" | Grep-evi "database|info|perfor" |sed-r ' s#^ ([a-z].*$) #mysqldump-uroot-p ' 123456 '--events-b \1|gzip >/opt/\1. Sql.gz#g ' |bash

#!/bin/bash
For dbname in ' mysql-uroot-p ' 123456 '-e "show databases;" | Grep-evi "Database|infor|perfor"
Do
Mysqldump-uroot-p "123456"--events-b $dbname |gzip >/opt/bak${dbname}_bak.sql.gz
Done

To back up a single table and multiple tables:
Syntax: mysqldump-u user name-P database Name table name Table name > backup file name
Mysqldump-u root-p ClassInfo Score>/opt/table1.sql

Sub-database Sub-table backup:

#！ /bin/bash
User=root
passwd=123456
Sock=/data/3306/mysql.sock
login= "Mysql-u$user-p$passwd-s $SOCK"
Databases= ' $LOGIN-e "show DATABASES; | Sed "1d" |egrep-v "^.*sch|mysql" '
dump= "Mysqldump-u$user-p$passwd-s $SOCK"
For database in $DATABASES
Do
[!-d/back/$database] && mkdir-p/back/$database
Table= ' $LOGIN-e "show tables from $database;" | Sed ' 1d '
For table in $TABLE
Do
$dump $database $TABLE |gzip >/back/$database/${database}_${table}_$ (date +%f). sql.gz
Done

Back up only the table structure and back up data only:

Parameter-d Backup table structure
Parameter-T back up data

Instance:
Mysql-u root-p ' 123456 '--compact-d classinfo score>/opt/tabled.sql
Mysql-u root-p ' 123456 '--compact-t classinfo score>/opt/tablet.sql

Back Up all databases:
Mysqldump-u root-p ' 123456 '-a-b--events|gzip >/opt/all.sql.gz

The difference between MyISAM and InnoDB backup:
MyISAM:
Mysqldump-uroot-p ' 123456 '-a-b--master=2-x|gzip >/opt/all.sql.gz

Mysqldump-uroot-p ' 123456 '-a-b--master=2-x--triqqers--routines--events--hex-blob|gzip >/opt/all.sql.gz

InnoDB
Mysqldump-uroot-p ' 123456 '-a-b--master=2--singe-transaction|gzip >/opt/all.sql.gz

Mysqldump-uroot-p ' 123456 '-a-b--master=2--singe-transaction--triqqers--routines--events--hex-blob|gzip >/opt/ All.sql.gz

Recover Database Combat:

Recovering a database with the source command
Enter MySQL database console, mysql-u root-p after login
Mysql>use database name
Then use the command Source command, followed by the script file, you can view the file path with system ls
Mysql>source/opt/all.sql.gz

Batch Recovery Script instance:

#!/bin/bash
Gzip-d/opt/*.gz

For dbname in ' ls *.sql|sed ' s#_bak.sql# #g ';
Do
Mysql-u root-p ' 123456 ' <${dbname}_bak.sql;
Done

Mysql--shou Command Summary:

Show status; View database state information for the current session
Show global status; View entire database health status information
Show full processlist; To view the SQL statement being executed
Show variables; View database parameter information

---------------------------------------------
Mysqlbinlog---Binlog log to resolve MySQL
Used to record MySQL internal additions and deletions and other updates to the MySQL database content records.

Mysqlbinlog Parameters:
-D intercepts the binlog of the specified library
Intercept by Location:
Mysqlbinlog mysqlbin.000046--start-position=365--stop-position=456-r/opt/pos.sql
Intercept by Time:
Mysqlbinlog mysql-bin.000046--start-datetime= ' 2016-10-15 15:14:15 '--stop-datetime= ' 2016-10-15 15:16:17 '-R time.sql

MySQL Backup and recovery multi-Library backup