Backup and restore of MySQL

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