MySQL Backup recovery operation

"Backed-up Commands"

1) backing Up the database

Mysqldump-uroot-pwsyht-b Library name >/opt/test.sql #加-B for information on adding use DB and CREATE database db

2) View the database

Egrep-v "#|\*|--|^$"/opt/test.sql #查看test. SQL Library Contents

3) Recovering the database

Mysql-uroot-pwsyht </opt/test.sql #test. SQL has been built, so no database is specified for recovery

4) Compress backup

Mysqldump-uroot-pwsyht-b Library Name | gzip >/opt/test.sql.gzip #压缩备份减少占用内存空间, compression efficiency nearly 3 times times

5) backing up multiple libraries

Mysqldump-upeter-pwsyht123-b Test Allow | Gzip> test.sql.gz #备份test库和allow库, if you have multiple libraries, separate them with spaces.

6) Get the first half of the database

Mysql-uroot-pwsyht123-e "show databases;" | Egrep-vi "Cheu|mysql|perfor|infor|database" | Sed ' s#^ #mysqldump-uroot-pwsyht123-b #g ' | Sed ' s#$# > test.sql#g ' | Bash

Sub-Library Backup video

Http://edu.51cto.com/lesson/id-16611.html

Cat fenku.sh

For dbname in ' mysql-uroot-pwsyht123-e "show databases;" | Egrep-vi "Cheu|mysql|perfor|infor|database"

Do

Mysqldump-uroot-p ' wsyht123 '-B $dbname | gzip >/mnt/$dbname. sql.gz

Done

7) backing up a single table

MYSQLDUMP-UROOT-PWSYHT123 Library Name Table name > Backup filename. sql

8) View the content to be backed up

mysqldump-uroot-pwsyht123--compact Test T1 #--compact Remove comments, suitable for mode, production environment without

9) Back up the table structure only

mysqldump-uroot-pwsyht123-d Library Name Table name > backup file name. SQL #-d only back up the table structure

10) Back up table data only

MYSQLDUMP-UROOT-PWSYHT123-T Library Name Table name > backup file name. SQL #-t back up data only

11) View Help

Mysqldump--help > A.log

12) Back up all tables in all libraries

Mysqldump-uroot-pwsyht123-a-B--events > backup file name. SQL #-a back up all the tables in the library,-B back up the statement that created the library

13) Refresh Binlog parameters

Mysqldump-uroot-pwsyht123-a-b-f--events > backup file name. sql #-f Refresh Binlog parameters

--master-data increase the Binlog log file name and the corresponding location point

mysqldump-uroot-pwsyht123--master-data=2--compact > backup file name. sql

"Other ways to use"

MyISAM engine

Mysqldump-uroot-pwsyht123-a-b-f--master-data=2-x--events > A.sql #-x means lock table

InnoDB engine

Mysqldump-uroot-pwsyht123-a-b-f--master-data=2--events--single-transaction > All.sql

InnoDB Backup command: Recommended use

"Restored Commands"

Mysql> Source/mnt/mysql_bak_b.sql

mysql-uroot-pwsyht123 </opt/mysql_bak_b.sql

Backup by Script

# ls

# Wsyht_gbk_bak.sql

# ls *.sql | Sed ' s#_bak.sql# #g '

Wsyht_gbk

For dbname in ' ls *.sql | Sed ' s#_bak.sql# #g ';d o mysql-uroot-pwsyht123 < ${dbname}_bak.sql;done

"Modify Parameters Online"

Mysql-uroot-pwsyht123-e "Set names Latinl;use wsyht;select *from student;"

To view the number of connections connected to MySQL

Mysql-uroot-pwsyht123-e "show full processlist;"

Check to see if there is a log-bin log open

Mysql-uroot-pwsyht123-e "show variables;" | grep log_bin

View MySQL Status

Mysql-uroot-pwsyht123-e "Show global status;" | grep sel

Mysql-uroot-pwsyht123-e "show global status Like '%insert% ';"

Mysql-uroot-pwsyht123-e "Show global status;" | Less status

Implemented via Echo (this is more common)

echo "show database;" | Mysql-uroot-pwsyht123-s/data/3308/mysql.sock

With cat implementations, this usage is not much

Cat | Mysql-uroot-pwsyht123-s/data/3308/mysql.sock <<eof

Show database;

Eof

Through the MYSQL-E parameter implementation

Mysql-uroot-pwsyht123-s/data/3308/mysql.sock-e "show databases;"

"Change database parameter does not restart in effect"

mysql> set global key_buffer_size=1024*1024*32; #设置索引缓存大小为32兆

Mysql> Show variables like "key_buffer%"; #查看设置的变量

"Little tricks."

Database BULK Insert Data shell script implementation

http://oldboy.blog.51cto.com/2561410/597511

Do not log in database boring MySQL command summary

http://oldboy.blog.51cto.com/2561410/632608

Hope to communicate with the technology can contact me by the following ways

My OPS group 517751492

My QQ1934844044.

This article is from the "Wsyht blog" blog, make sure to keep this source http://wsyht2015.blog.51cto.com/9014030/1719492

MySQL Backup recovery operation