MySQL data preparation and step clustering

MySQL Backup and recovery:
1, disaster recovery;
2, Audit;
3, testing;

Backup: The purpose is to restore, and to test the backup data;

Backup type:

Whether the database server is online depending on the backup:
Cold: Cold Backup
Win Bei: Warm backup
Hot standby: Hot backup

Depending on the data set backed up:
Full backup:
Partial backups: Partial backup

Depending on the interface at the time of backup (directly backing up data files or exporting data through a MySQL server):
Physical Backup: Direct copy (archive) data file backup method; physical backup
Logical backup: Put the data from the library and save it as a text file; logical backup
Mysqldump

Depending on whether you are backing up the entire data or just backing up the changed data:
Full backup:
Incremental backups: Incremental backup
Differential backups: Differential backup

Backup policy:
Select a Backup method
Select Backup Time
Considering the cost of recovery
Recovery duration

Backup cost:
Lock time
Backup duration
Backup load

Backup objects:
Data
Configuration file
Code: Stored procedures, stored functions, triggers
OS-related configuration files, such as crontab configuration plans and related scripts

configuration related to replication;
Binary log files

Backup tool:
Mysqldump: Logical Backup tool
InnoDB Hot Spares, MyISAM Win Bei, Aria Win Bei
Slow backup and recovery process
Mysqldumper: Multi-threaded mysqldump

It is difficult to achieve differential or incremental backups;

Lvm-snapshot:
Tools close to hot spare: because you want to request a global lock, then create a snapshot, and then release the global lock after the snapshot is created;
Use tools such as CP and tar for physical backup;
Faster backup and recovery;

It is difficult to achieve incremental backups, and the request global needs to wait for a period of time, especially on busy servers;

SELECT clause into OUTFILE '/path/to/somefile '
LOAD DATA INFILE '/path/from/somefile '
Part of the Backup tool, does not back up the relationship definition, only back up the data in the table;
Logical Backup tool, faster than mysqldump

Innobase: Commercial Backup tool, Innobackup
Xtrabackup: Open Source Backup tool provided by Percona
InnoDB hot standby, incremental backup;
MyISAM Win Bei, Delta not supported;
Physical backup, fast speed;

Mysqlhotcopy: Almost cold prepared

Mysqldump
mysqldump [Options] [db_name [Tbl_name ...]

Backing up a single library: mysqldump [options] Db_name
When recovering: If the target library does not exist, you need to manually create it beforehand

--all-databases: Backing Up all libraries

--databases db1 DB2 ...: Backing up a specified number of libraries

Note: Lock before backup

--lock-all-tables: Request lock All tables before backup, Win Bei for MyISAM, InnoDB, Aria

--single-transaction: InnoDB storage engine can be prepared for hot standby;

Backup code:
--events: Backing up event Scheduler code
--routines: Backing up stored procedures and storage functions
--triggers: Backup Trigger

Scroll logs when backing up:
--flush-logs: Scrolls the log before and after the backup, after the request to the lock;

Synchronization location Tag when copying:
--MASTER-DATA=[0|1|2]
0: Do not record
1: Record as change master statement
2: Change master statement recorded as comment

Using mysqldump Backup:
Request Lock:--lock-all-tables or use--singe-transaction for InnoDB hot standby;
Scrolling log:--flush-logs
Select the library to back up:--databases
Logging binary log files and location:--master-data=

Recovery:
Recommendation: Turn off the binary log and close other user connections;

Backup strategy: Based on mysqldump
Backup: mysqldump+ binary log file;
Sunday make a full backup: Rolling logs at the same time as backups
Monday to Saturday: backup binary log;
Recovery:
Full backup + events from each binary log file to the moment

The MySQL configuration file, as well as the MySQL-related OS profile, should be backed up directly after each modification;

Backup steps:
1, request global lock, and scroll log
Mysql> FLUSH TABLES with READ LOCK;
Mysql> FLUSH LOGS;

2, do binary log file and location tag (manual);
# mysql-e ' Show Master status ' >/path/to/somefile

3. Create a Snapshot volume
# lvcreate-l-s-n-P R/PATH/TO/SOME_LV

4. Release the global lock
Mysql> UNLOCK TABLES;

5. Mount the snapshot volume and back up
# CP

6. After the backup is complete, delete the snapshot volume

Recovery:
1, binary log to save good;
Extracts all events after the backup into a SQL script;
2, restore data, modify permissions and belong to the main group, etc., and start MySQL
3, do instant point restore

Mylvbackup:perl script, fast backup MySQL based on LVM

Xtrabackup:

Backup Note:
1. Put the data and backup on different disk devices; It is ideal to store the backup or offsite.
2, the backup data should be periodically to restore the test;
3, every disaster recovery should be done immediately after a full backup;
4, for different scale or level of data volume, to customize a good backup strategy;
5, the binary log should be with the data files on different disks, and periodically back up the binary log files;

Recovery from backup should follow the steps:
1, stop the MySQL server;
2, record the server configuration and file permissions;
3, the data from the backup to the MySQL data directory, its implementation depends on the tool;
4, change the configuration and file permissions;
5. Restart the server in restricted access mode, mysqld--skip-networking option to skip network function;
Method: Edit the MY.CNF configuration file and add the following:
Skip-networking
Socket=/tmp/mysql-recovery.sock
6. Load the logical backup (if any), then check and replay the binary log;
7, check the data that has been restored;
8, re-restart the server in full access mode;
Note the options that were previously added in the my.cnf, and restart;

SELECT clause into OUTFILE '
LOAD DATA INFILE ' into TABLE tb_name

This article is from the "Scattered People" blog, please be sure to keep this source http://zouqingyun.blog.51cto.com/782246/1690149

MySQL data preparation and step clustering