MySQL backup-incremental backup level data recovery

Source: Internet
Author: User
Tags compact mysql backup

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.