MySQL database backup

Some time ago because of misoperation deleted a table of a few data, embarrassed, just this week is free to toss the next data backup knowledge, now the MySQL data backup related practice and experience summarized as follows:

One. Use the mysqldump command to back up the database:

To back up the entire database, including the table structure and data, use the following

Mysqldump-h10.38.14.143-uroot-prootroot Weixin > Weixin.sql

Where Weixin is the database name, you can back up multiple databases at once (separated by spaces), saved in the current directory by default

This wexin.sql file consists mainly of four parts: database and operating system information, table structure, specific data (actually INSERT statement), lock table and release the table command statements

Add:

1. Read lock: I and other people can only read the database

Write Lock: Only I can do the additional deletion check, others can not do anything

2. Sometimes use the mysqldump command will prompt no permission to operate the database related prompts such as Mysqldump:got Error:1044:access denied for the user ' php ' @ ' 10.38.% ' to database ' Cms_f GW ' When using LOCK TABLES

This is because the MySQL database restricts the operation of the remote server to him, please communicate with the DBA for open access (mainly by executing a command similar to the following to authorize)

Grant all privileges on * * to ' root ' @ '% ' identified by ' 123456 ' with GRANT option;

Flush privileges;

3. If you forget some of the usage of MySQL command can be used more? Operation, equivalent to man help, this is useful, as follows

4. Mysqldump can also be used to back up the table structure only, use the following

Mysqldump-h10.38.14.143-uroot-prootroot--no-data--databases Weixin cjdaily >/tmp/table_structure.sql

Table_structure.sql in the following sections

5. You can make some extensions to write a backup database shell script according to the mysqldump command, as follows is a script written according to our own needs (the specific syntax of the shell script will not speak)

#!/bin/bash

#backup Database

If [$#-ge 1]; Then

[Email protected]

Else

databaselist= "cjdaily cms_rmrb Guangzhou pladaily"

Fi

For database in $databaselist

Do

if [!-e/home/pengyudi/backup_databases/$database]

Then

Mkdir-p/home/pengyudi/backup_databases/$database

Fi

mysqldump--opt-h10.38.10.5-uchen-pab#@c-123 $database |gzip >/home/pengyudi/backup_databases/$database/$ database$ (date + "%y-%m-%d%h:%m:%s"). sql.gz

If [$?-eq 0]

Then

echo "$ (date +"%y-%m-%d%h:%m:%s ") Database---$database---Backup successfully \ r \ n"

find/home/pengyudi/backup_databases/$database/-name $database \*-ctime +1-exec rm-rf {} \;

Else

echo "$ (date +"%y-%m-%d%h:%m:%s ") Database---$database---Backup unsuccessfully,please check out the reason\r\n"

Fi

Done

6. Back up all databases on a host mysqldump--opt-h10.38.10.5-uchenliangliagasd-pabcdeqasdadfa-1238--all-databases> test.dump

Two. Recovery of data

1. Switch to MySQL's working environment
Use test;

Source/tmp/table_structure.sql;

2. Do not switch to the MySQL environment

/USR/BIN/MYSQL-H192.168.4.47-UM_BBS_TEST_ADMIN-P3309-P89603D5A Test</tmp/table_structure.sql; (Note that specific databases are specified)

Three. Export data using Load Import data and select * * * * * from tabelname to outfile ' file_name '

Add

1. Note that the file to save the data to be quoted and the file path to save the data, pay special attention to some details, such as above, it is very easy to make mistakes

SELECT * from WEIXIN_DATA_RMRB into outfile ' weixin_outfile.sql ';

2. Tips for use:

When importing data with the load command, you can increase the import speed with the appropriate data for the table of the MyISAM storage engine

(1) You can quickly import large amounts of data in the following ways (disable keys and enable keys can be used to turn on or off updates for non-unique indexes, increasing import speed, but not for InnoDB tables)

ALTER TABLE WEIXIN_DATA_RMRB disable keys

Load data infile ' weixin_outfile.sql ' into table WEIXIN_DATA_RMRB

ALTER TABLE WEIXIN_DATA_RMRB enable keys

(2) Turn off uniqueness check to increase the speed of import

Increase import speed by executing set unique_checks=0 (Turn off uniqueness check) and set Unique_checks=1 before and after importing data

For InnoDB tables, you can use the following method to increase the import speed:

(1) You can increase the import speed by arranging the imported data in the order of the primary key, because the InnoDB table is saved in the primary key order

(2) Execute set autocommit=0 before import to turn off autocommit things, execute set autocommit=1 reply after import end automatically commit things

Four. Recovering data using a binary log

1. Turn on Binlog log

Modifying the/ETC/MY.CNF configuration file

Vim/etc/my.cnf

Add the following two lines below [mysqld] to open and set the save path for the Binlog log

Log-bin=/home/logs/mysql/mysql-bin #记得要修改权限, or MySQL will not start; Mysql-bin as part of the name of the log file (mysql-bin.000001)

Binlog_format=mixed

Log=/home/logs/mysql/mysql.log

Save exit

Chown Mysql.mysql/home/logs/mysql-r #修改目录权限

where Mysql-bin.index records which binary log files are generated

Additional log-related parameters:

Expire_logs_days = 7 #保留七天的日志

Slow-query-log = on #开启慢查询

Slow_query_log_file =/home/logs/mysql/slow.log #慢查询日志保存路径

Long_query_time = 1 #慢查询的时间, record more than one second

Log-queries-not-using-indexes = on #记录没用使用到索引的SQL语句

After modifying the configuration, restart Mysql:service mysqld restart

Go to the MySQL command interface to view binary log related information:

Show variables like "%bin%"

2. Commands related to the Binlog log

Flush logs: After execution, one more recent Binlog log file in the directory where the Binlog log is stored

Show Master Status View last bin Log

Reset master clears all Binlog logs

Operation command to recover a log file by Binlog log:

/usr/bin/mysqlbinlog--no-defaults-f/home/logs/mysql/mysql-bin.000008| Mysql-h10.38.14.143-uroot-prootroot

View a binlog log command:/usr/bin/mysqlbinlog--no-defaults-f/home/logs/mysql/mysql-bin.000008

Note: You can restore data to a point as needed, and the main parameters behind Mysqlbinlog are:

--stop-position= "120"

--start-positon= "20"

--stop-date= "2016-11-19 18:50:42"

--start-date= "2016-11-19 18:30:21"

Friendship Link:

Binlog detailed operation examples, please refer to the following blog:

Http://blog.chinaunix.net/uid-20494084-id-3753682.html

mysql5.7 Official Document Address http://dev.mysql.com/doc/refman/5.7/en/preface.html

MySQL database backup