Backup and restore of MySQL
Backup: Copy
RAID1,RAID10: Ensure hardware damage without interruption of business;
DROP TABLE mydb.tb1;
Backup type:
Hot, warm, and cold backup (depending on whether the server is online)
Hot backup: Read, write not affected;
Warm backup: Only read operation can be performed;
Cold backup: Offline backup, read and write operations are aborted;
Physical and logical backups (based on direct CP or exporting files)
Physical Backup: Copy data files;
Logical backup: Export data to a text file;
Full backup, incremental backup, and differential backup;
Full backup: Back up all data;
Incremental backup: Backs up only data that has changed since the last full or incremental backup;
Differential backup: Backs up only data that has changed since the last full backup;
Online: Physical Full backup
Restores:
Backup what:
Data, configuration files, binary logs, transaction logs
Hot backup:
MyISAM: Warm Backup
Innodb:xtrabackup, mysqldump
MySQL--From:
Physical Backup: Fast speed
Logical backup: Slow speed, loss of floating-point precision, easy to use text processing tools directly to its processing, portable ability;
Backup strategy: Full + Delta; Full + diff
MySQL Backup tool:
Mysqldump: Logical Backup tool, MyISAM (warm), InnoDB (hot Backup)
Mysqlhotcopy: Physical Backup tool, warm backup
File System Tools:
CP: Cold Standby
LV: Snapshot function of logical volume, almost hot standby;
Mysql> FLUSH TABLES;
Mysql> LOCK TABLES
Create a snapshot: Release the lock and then copy the data
InnoDB:
Third set of tools:
Ibbackup: Business Tools
Xtrabackup: Open Source Tools
Mysqldump: Logical Backup
Mysqldump (full backup) + Binary log Lock table: Flush tables with read lock scrolling binary log: Flush logs
Full + increment:
Backing up a single database, or a specific table in a library
Mysqldump db_name [TB1] [TB2] does not contain a command to create a database, you must specify a database at restore time, or you can only back up a single table
--MASTER-DATA={0|1|2}
0: Do not log the binary log file and the location of the road;
1: Record location in Chnage MASTER to, can be used to start the server directly after recovery;
2: Record the position in the form of change MASTER to, but the default is to be commented;
--lock-all-tables: Lock All tables
--flush-logs: Execute log flush;
If the table type in the specified library is InnoDB, you can use--single-transaction to start the hot spare without using lock and flush;
To back up multiple libraries:
--all-databases: Back up all libraries, save the command to create the library
--databases db_name,db_name,...: Back up the specified library, save the command to create the library
--events: Backup Event Scheduler
--routines: Backup of stored procedures and functions
--triggers: Backup Trigger
The following backup preserves the creation of the database:
Mysqldump-uroot-p--lock-all-tables--flush-logs--all-databases--master-data=2 >/root/all.sql
Backup strategy: Weekly full + daily increment
Full backup: mysqldump
Incremental backup: Backing up binary log files (flush logs)
Manual emulation:
Mysqldump-uroot-p--master-data=2--flush-logs--all-databases--lock-all-tables >/root/alldatabases.sql
After the first Tianquan backup, add the delete data,
Make incremental backup the next day: Flush logs
Mysqlbinlog mysql-bin.000006 >/root/mon-increamental.sql
Add delete data, then the database data is all gone, but the binary files remain
To restore data:
(1) Start mysqld after initializing the library
(2) Restore full library Mysql-uroot-p < Alldatabases.sql
(3) Restore the first day incremental backup Mysql-uroot-p < Mon-increamental.sql
(4) Restore the day's data Mysqlbinlog mysql-bin.000007 | Mysql-uroot-p
Make a script, one is full, one is incremental
Backup and restore of MySQL