MySQL full backup + Incremental backup Note Summary

Backup Basics

Cold (cold Backup): Need to shut down the MySQL service, read and write requests are not allowed in the state;

Win Bei (warm backup): Service online, but only support read requests, do not allow write requests;

Hot backup: The business is not affected at the same time as the backup.

This type of backup depends on the needs of the business, not the Backup tool

MyISAM does not support hot-standby, InnoDB supports hot-standby, but requires specialized tools

Full backups: Full backup, back up all character sets.

Incremental Backup: Incremental backup data that has changed since the last full or incremental backup, cannot be used alone, and with full backups, the frequency of backups depends on how often the data is updated.

Differential backup: Differential backup data that has changed since the last full backup.

Recommended recovery strategy:

Full + increment + binary log

Full + diff + binary log

Full backup

MyISAM:

Mysql-uroot-p ' 123456 '-a-b-f–flush-privileges–master-data=2–x-–events | gzip >/opt/x_$ (Date +%f). sql.gz

InnoDB:

mysqldump-uroot-p123456 --single-transaction -a-b-F--events | gzip >/server/backup/x_$ (Date +%f). sql.gz

--single-transaction MySim directly selects the- x (--lock-all-tables) parameter lock table, InnoDB Select this parameter to ensure the consistency of the backup. The equivalent of setting an isolation level, repeatable READ, to ensure that no other session has submitted data when the session dump is made.

--master-data[=#] automatically find the location of Binlog this causes the binary log position Andfilename

Such as:--master-data=1 can not refresh the Binlog, do an incremental backup when it is useful

--master-data=2 will add a note, find some

- e,--events Dump events.

-A,--all-databases Dump Export all data, general plus-B common +--events all

- b,--databases specifies multiple library name backups visually, plus the-b parameter is required to increase the command to create a database and connect to the database, and production environment backup is mandatory.

- f,--flush-logs refresh, cut Binlog

Supplementary: Appeal two kinds of backup if the database has stored procedures and triggers, there are two additional parameters:

--triggers–routines--hex-blob, the general company does not have these three parameters.

Trigger stored procedure If you have a BLOB field in your library, and you don't add this parameter, your blog will be missing a large piece of data

Sub-Library Backup

Mysql-uroot-p ' 123456 '-e "show databases;" | Grep-evi "Database|infor|perf" | Sed-r ' s#^ ([a-z].*$) #mysqldump-uroot-p ' 123456 '--events-b \1 | gzip >/tmp/logs/\1.sql.gz#g ' | Bash

- T,--no-create-info If you want to export only table data

- D,--no-data backs up only the table structure no row information.

Error

Mysqlbinlog:unknownvariable ' Default-character-setutf8 ' solution

Mysqlbinlog --no-defaults ./mysql-bin.000007

MySQL full-volume export encountered the following alarm: The default is not to back up the event table, only add--events will not warn

Warning:skipping The data of table mysql.event. Specify the--events option explicitly

Workaround:

--events--ignore-table=mysql.event

ERROR 1046 (3d000) at line 22:no database selected

Modify. sql to precede 22 lines with the use library name;

Recovery

First restore full standby

mysql-uroot-p123456 </server/backup/x.sql

Multi-Library File recovery

For name in ' LS *.sql| Sed ' s#.sql# #g '; Do mysql-uroot-p123456 < ${name}.sql; Done

And then

mysqladmin-uroot-p123456 flush-log//cutting log

Summarize all the Binlog, put the wrong delete ,

The rest turns into SQL statements

CP mysql-bin.000016/server/backup/

Mysqlbinlog--no-defaults-d user mysql-bin.000016 > Bin.sql

mysql-uroot-p123456 < Bin.sql

Execution | Mysql

Reply according to Binlog location and time

Mysqlbinlog--start-postion=107--stop-position=1000-d Library name binary file

Mysqlbinlog--start-datetime= ' 2013-09-10 00:00:00 '--stop-datetime= ' 2013-09-10 01:01:01 '-d library name binary file

Egrep-v "#|\*|--|^$" filter to view backup content

Rsync with timed tasks

rsync-avz/data/3306/mysql-bin.000* [Email Protected]::backup--password-file=/etc/rsync.passsword

One master more from, one from do backup

Vim. MY.CNF//Setup Login

[Client]

User=root

Host=localhost

password=123456

Mysql> Show variables like "character_set%"; View character Sets

[Client]//setting Character Set

Default-character-set=utf8

[Mysqld]

Character-set-server=utf8//5.5

Default-character-set=utf8//5.1

[MySQL]

Default-character-set=utf8

Turn on Bin-log, there is a full backup, and all incremental Binlog file backups

Mysql> Show full processlist; Connection conditions

Mysql> Show variables like "%log_bin%";

+---------------------------------+-------+

| variable_name | Value |

+---------------------------------+-------+

| Log_bin | OFF |

| log_bin_trust_function_creators | OFF |

| Sql_log_bin | On |

+---------------------------------+-------+

Mysql> Flush table with read lock; Read lock

mysql> unlock tables; Unlock

replicate_wild_ignore_table=mysql.%//Can be added with wildcard characters

--skip-name-resolve option to start mysqld to disable DNS host name lookups

Slave-skip-errors = 1032,1062//excludes retention of 1032,1062.

Lower_case_table_names=1//make MySQL case insensitive! Use caution, will affect the original table name

[Mysqld]

Read-only//Read-only, root not restricted

Master never synchronizes

Sql=no, you can skip the error with the following command

When slave is in the stop state, execute set global sql_slave_skip_counter=n to skip the command n=1

Slave open from library record Binlog cascade synchronization as database backup

Log-bin =/data/3307/mysql-bin

Log-slave-updates

Expire_logs_days = 7//reserved for 7 days

This article is from the "what-all" blog, please be sure to keep this source http://hequan.blog.51cto.com/5701886/1775333

MySQL full backup + Incremental backup Note Summary