MySQL backup-incremental backup level data recovery

Mysqldump-u User name-p password

Mysqldump-h 127.0.0.1-u root-p3003--events-p mysql >/tmp/mysql.bak.sql # # #因为mys Qldump default is not to back up the event table, only add the--events will be resolved plus

Egrep-v "#|\*|--|^$"/tmp/mysql.bak.sql ############### #查看导出的表信息

Mysql-h 127.0.0.1-uroot-p ' 123456 '-p3003-e "use Mysql;drop table db;" # #通过命令删除mysql下的db表.
Mysql-h 127.0.0.1-uroot-p ' 123456 '-p3003 mysql </tmp/mysql_b.sql # #通过备份还原mysql库的表

mysql-uroot-h10.10.0.37-p3320--default-character-set=utf8-p hive_new <./201806201512sql.sql can also specify a character set

# # #带参数大B的导出

Mysqldump-h127.0.0.1-uroot-poldboy-p3003-b Oldboy >/tmp/oldboy_b.sql # # # #这样就算删除了oldboy数据库 can also be restored.

# # #压缩方式倒出数据库

Mysqldump-h127.0.0.1-uroot-poldboy-p3003-b oldboy|gzip >/tmp/oldboy_b.sql.gzip

Mysqldump Backup principle

The process of backing up data with the MYSQLDUNMP command is essentially the process of inputting data directly into or generating a backup file from a MySQL library in the form of a logical SQL statement.

# # #mysql备份多个库

Mysqldump-h 127.0.0.1-uroot-poldboy-p3003-b oldboy TEST_GBK test_utf8|gzip >/opt/3gku.sql.gz # #备份多个库

Gunzip </opt/3gku.sql.gz |mysql-h 127.0.0.1-uroot-poldboy-p3003 # # #恢复库

Mysql-h127.0.0.1-uroot-poldboy-p3003-e "show databases;" |    Grep-evi "database|infor|perfor" |sed ' s#^ #mysqldump-H 127.0.0.1-uroot-poldboy-p3003-b #g ' |gzip >/tmp/haha.sql.gz # # #可以查出来库以及在库前面加上参数

# # #分库备份mysql-h127.0.0.1-uroot-poldboy-p3003-e "show databases;" | Grep-evi "database|infor|perfor" |sed-r ' s#^ ([a-z].*$) #mysqldump-H 127.0.0.1-uroot-poldboy-p3003-b \1|gzip >/opt /\1.sql.gz#g ' |bash

 #!/bin/bash  for  dbname in  ' mysql-h127. 0.0 . 1 -uroot-poldboy-p3003-e  " show databases;   " | grep -evi  "  Database|infor|perfor   " "  do  mysqldump -H  127.0 . 0.1 -uroot-poldboy-p3003--events-b $dbname | gzip  >/opt/${dbname}_bak.sql.gz  done  

Script Backup sub-Library

# # #备份mysql表结构

mysqldump-h127.0.0.1-uroot-poldboy-p3003--compact-d Oldboy >/opt/a.sql    # # #备份表结构mysqldump-h127.0.0.1-uroot -poldboy-p3003--compact-t Oldboy >/opt/a.sql         # # #备份数据

# # #mysql数据库全备, and Refresh Bin-log

Mysqldump-h127.0.0.1-uroot-p-p3003--events-a-b-f |gzip >/opt/b.sql.gz

-A backup all libraries  -B Specify multiple libraries, add build-up statements and use statements--compact uncomment, suitable for debug output, production without-f Refresh Binlog Log--master-data add binlog log file name level corresponding location point-r backup stored procedure such as-X lock table-L--lock-tables read-only lock table----only  back up table structure-T  only back up data
--single-transaction for innodb Things database backup

MyISAM Backup command

Mysqldump-h127.0.0.1-uroot-poldboy-a-B--master-data=2-x--events |gzip >/opt/all.sql.gz

InnoDB Backup command: Recommended use

Mysqldump-h 127.0.0.1-u root-poldboy-a-B--master-data=2--events--single-transaction|gzip >/opt/all.sql.gz

MySQL recovery method

1.source/opt/mysql_bak_b.sql # #需要进入数据库里面操作然后指定指定数据路径 Sysctem View Directory

2.mysql-h 127.0.0.1-uroot-poldboy-p3003 Oldboy </opt/mysql_bak.sql

Mysql-h 127.0.0.1-uroot-poldboy-p3003 </opt/mysql_bak_b.sql

# #查看链接数据库情况

Mysql-h 127.0.0.1-uroot-poldboy-p3003-e "show full processlist;" You can find the database link and the action command to troubleshoot slow queries

Mysql-h 127.0.0.1-uroot-poldboy-p3003-e "show global status;" View global Status

Mysql-h 127.0.0.1-uroot-poldboy-p3003-e "show global status;" | grep SEL # # #查看mysql计数

MySQL Backup-incremental backup-level data recovery