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