MySQL Backup and recovery (note i)

MySQL physical backup and logical backup

MySQL Physical backup:
MySQL physical backup refers to a directory file that copies the entire database data (default in the/data/db_data directory)
Characteristics:
Simple, fast, but not suitable for memory storage engine (data is not saved to the hard disk), mainly for the MyISAM storage engine, can be implemented through file backup tools such as Scp,cp,tar, or MySQL comes with mysqlhotcopy
Disadvantages:
The storage engine corresponding to InnoDB requires MySQL Enterprise Edition Backup Utility Mysqlbackup

MySQL Logical backup:
To save data by getting the database structure and content
Advantages:
Can be used for any storage engine, online backup, MySQL comes with tool mysqldump
Disadvantages:
Slow (get data and convert to specified format), large volume,

MySQL on-line backup and offline backup

MySQL on-line backup
No need to shut down the MySQL server, the appropriate lock table can effectively guarantee the integrity of the backup

Offline Backup:
Need to shut down MySQL server, usually occurs in from library to prevent, the main library is working properly

Remote Backup and local backup
Remote Backup tool: Mysqldump
Local Backup tool: Mysqldump,mysqlhotcopy

To complete the backup and incremental backup:
Full backup: Back up all the databases
Incremental backup: A backup of the database variables in a point-in-time (relying on MySQL binary log) requires the--log-bin to be turned on (a new binary file is generated each time the restart
MySQL Client side: Flush logs (Linux command line mysqladmin flush-logs) command can manually generate a new one)


Backup policy:

shell> mysqldump-uroot-p--all-databases > Backup_date.sql (lock table)
If the database is all InnoDB engines, you can add--single-transaction to ensure that the transaction can also be backed up, if you need to create a new binary log as follows:
Mysqldump-uroot-p--single-transaction--flush-logs--master-data=2--all-databases > Backup_date.sql
To reduce space waste you can delete the useless binary log file by following this:
Mysqldump-uroot-p--single-transaction--flush-logs--master-data=2--all-databases--delete-master-logs > Backup_ Date.sql
When there is a master-slave operation needs to be careful, probably from the server has not been updated.

Recover by executing the following statement:
Mysql-uroot-p < Backup_date.sql
or binary log recovery
Mysqlbinlog mysql-bin.000007 mysql-bin.000008 | Mysql-uroot-p
Mysqlbinlog can also set the starting and ending time and location, please refer to the MySQL manual or man mysqlbinlog

Mysqldump Instructions for use
mysqldump [arguments] > file_name
Back up all the databases using--all-databases such as
Mysqldump--all-databases > Dump.sql
Specify the database
Mysqldump--databases db1 DB2 ... >dump.sql (create--databases with USE database statement will not be generated if no databases file is added)

mysql> CREATE DATABASE IF not EXISTS db1;
mysql> use DB1;
Mysql> Source Dump.sql

Mysqldump also has a--tab use method, which can be referenced in the manual

If your storage engine is InnoDB you can consider the parameters
--events (Event)
--routines (Stored procedures and functions)
--triggers (trigger, default included)
You can also specify to skip through the following parameters
--skipevents,--skip-routines, or--skip-triggers

Mysqldump can also backup the table structure separately from the data as follows:
--no-data Backing up data (table structure only)
--no-create-info backup table structure (back up data only)
shell> mysqldump--no-data Test > Dump-defs.sql
shell> mysqldump--no-create-info Test > Dump-data.sql

This article is from the "Linux Learning path" blog, so be sure to keep this source http://raystudylinux.blog.51cto.com/9065684/1767027

MySQL Backup and recovery (note i)