Shulou(Shulou.com)06/01 Report--

Rookie Learning Linux 078 Notes mysql backup mysqldump

Content overview

Detailed explanation of backup type and backup type

Backup tool

Backup and restore by mysqldump

Backup and restore of MySQL

Backups: copy

RAID1,RAID10 guarantees hardware damage without affecting business suspension

Backup Typ

Hot backup, warm backup, cold backup

Hot backup: read and write are not affected

Warm backup: only read operations can be performed

Cold backup: offline backup read and write operations are aborted

Physical backup, logical backup

Physical backup: copying data files directly

Logical backup: exporting data to a text file

Full backup, incremental backup, and differential backup

Full backup: backing up all data

Incremental backup: back up only the data that has changed since the last full backup or incremental backup

Differential backup: only data that has changed since the last full backup is backed up

The difference between increment and difference: increment is the difference between each backup and the previous backup.

The difference is compared with the first full backup, and there will be a different backup

(the difference is relatively wasteful of hard disk space and easy to manage)

Backups can be freely combined according to demand.

Online physical full backup

Online logical incremental backup.

Hot backup (with the highest technical complexity)

MyISAM lvm hot standby

InnoDB supports hot backup xtrabackup and mysqldump

Cold standby mysql-- > from

The speed of physical backup is fast and the portability is not very good

Slow logical backup will result in loss of floating-point precision

It is easy to use text processing tools to deal with it directly, and it has strong portability.

Backup strategy: full + increment; complete + difference

Frequency and variation of data variables

Bearable reduction time

Something that is backed up

Data, configuration files, binary logs, transaction logs

Restore testing, rehearsal, multi-person division of labor, restore emergency treatment plan

MySQL backup tool:

Mysqldump logical backup tool cold backup MyISAM (warm) InnoDB (hot)

Mysqlhostcopy physical backup tool warm backup

File system tool

Cp cold standby

Snapshot function of lv logical volumes, almost hot standby

Mysql > FLUSH TABLES

Mysql > LOCK TABLES

Create a snapshot: release the lock, and then copy the data

InnoDB:

The third set of tools:

Ibbackup (InnoDB) business tools

Xtrabackup open source tools

Mysqldump logical backup

Mysqldump (full backup) + binary log

Full + increment

Mysqldump DB_NAME [tb1] [tb2]

-- master-data= {0 | 1 | 2}

0 does not record binary log files and their event locations

1 record the location in CHANGE MASTER TO, which can be used to start the slave server directly after recovery

2 record the location in CHANGE MASTER TO, but it is annotated by default

-- lock-all-tables locks all tables

-- flush-logs execution log flush

If all the tables in the specified library are of InnoDB type, you can use-- single-transaction to start the hot backup

(do not use with-- lock-all-tables)

-- events

-- routines

-- triggers

Back up multiple libraries

-- all-databases backs up all libraries

-- databases DB_NAME1,DB_NAME2 backup specified library

(with this backup, you do not need to manually create a new database name for recovery)

Lab backup and restore

Backup strategy: weekly full + daily increment

Full backup: mysqldump

Incremental backup: backing up binary log files (flush logs)

1. Backup database

Full backup (backup files need to be tested here on other hard drives, so just put them in other directories)

# mysqldump-uroot-p-master-data=2-- flush-logs-- all-databases\

-- lock-all-tables > / root/all-dbs.sql

(this is an one-line command)

Mysql > PURGE BINARY LOGS TO 'mysql-bin.000007'; (deletion is not recommended, just for testing)

You can back it up and delete it.

Make an incremental backup (the next day)

Mysql > FLUSH LOGS

Back up the scrolled log (just copy it directly) the directory in front of it is the directory where the binary log is placed.

Cp / mydata/data/mysql-bin.000007 / root/binary- `date +% Ymure% mmury% d`.sql

Mysqlbinlog / mydata/data/mysql-bin.000007 > / root/monday-incremental.sql

(it is recommended to back up binary log files in the previous article)

Do the increment on the third day and simulate this day to delete the database accidentally.

(but as a result, do not delete the binary log, ha, it will be dead if it is gone.)

Copy the binary file first

Cp / mydata/data/mysql-bin.000008 > / root/mysql-bin.000008.sql

# rm-rf / mydata/data/* (simulating database corruption)

Restore database

Because I simulated too violently, I deleted the library file directly, so I need to reinitialize the database.

# killall mysqld

Initialize the database

#. / scripts/mysql_install_db-user=mysql-datadir=/mydata/data

Restore full backup

# mysql-uroot-p < / root/all-dbs.sql

Import incremental backup

# mysql-uroot-p < / root/monday-incremental.sql (using mysqlbinlog)

The last step is to import the last binary log into the database

# mysqlbinlog mysql-bin.000008.sql | mysql- uroot-p

(at this point, it will be instantly restored)

Although mysqldump can realize backup and restore, it will lose precision and is only suitable for small databases.

Final assignment

Script completes full backup

Script completes incremental backup

And add it to the task schedule to automatically perform the backup

(currently, the individual has not made it.)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.