Shulou(Shulou.com)05/31 Report--

This article to share with you is about MySQL database backup types and commonly used backup tools which, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some gains, not much to say, follow Xiaobian to see it.

1 Database backup type

According to the database size backup, there are four types, which are applied to different occasions. The following is a brief introduction:

1.1 full backup

This is the way most people use it, and it backs up the entire database, including all database objects such as user tables, system tables, indexes, views, and stored procedures. But it takes more time and space, so it is generally recommended to do a full backup once a week.

1.2 Transaction Log Backup

The transaction log is a separate file that records changes to the database. Backup time only needs to copy the changes made to the database since the last backup, so it takes very little time. For database robustness, it is recommended to backup transaction logs hourly or more frequently.

1.3 differential backup

Also known as incremental backups. It is an alternative to backing up only a portion of the database, and instead of using the transaction log, it uses a new image of the entire database. It is smaller than the original full backup because it contains only the database that has changed since the last full backup. Its advantage is fast storage and recovery. It is recommended to make a differential backup once a day.

1.4 file backup

A database can consist of many files on a hard disk. If the database is too large to be backed up in one night, you can use file backups to back up a portion of the database nightly. This backup is not very common because databases are generally not large enough to require multiple file stores.

According to the status of the database, it can be divided into three types:

1. Cold backup, when the database is closed, can better ensure the integrity of the database.

2. Hot backup, where the database is running, relies on the database's [1] log file for backup.

3. Logical backup, using software to extract data from a database and write the results to a file.

2 Introduction to backup tools

MySQL is divided into logical backup and physical backup according to backup recovery method. Logical backup is the backup sql statement, in the recovery time to execute the backup sql statement to achieve database data reproduction, physical backup is the backup data file, the comparison image point is cp data file, but the real backup is naturally not cp so simple.

These two kinds of backups have their own advantages and disadvantages. Generally speaking, physical backup recovery speed is faster and takes up more space, while logical backup speed is slower and takes up less space.

Official address: dev.mysql.com/doc/refman/5.6/en/backup-and-recovery.html

2.1 mysqldump tool

Mysqldump is a backup tool that comes with mysql. The directory is under the bin directory: /usr/local/mysql/bin/mysqldump, which supports innodb-based hot backup. However, since it is a logical backup, the speed is not very fast, and it is suitable for scenarios where the backup data is relatively small. Mysqldump full backup + binary log enables point-in-time recovery.

For myisam storage engine tables, only warm backup can be used. At this time, to prevent data from being written, read lock should be added first. At this time, you can also enter the database to manually add read locks, but this is more troublesome. You can directly have a lock option in the mysqldump tool, that is, --lock-all-tables, for example, mysqldump --databases test --lock-all-tables --flush-logs > /tmp/backup_test_`date+%F-%H-%M`.sql.

If you are backing up a single table, add the table name directly after the library name test.

For innodb storage engine tables, you can hot backup without locking the database. Add an option to hot backup, --single-transaction, for example: mysqldump --databases test --single-transaction --flush-logs--master-data=2> /tmp/backup_test_`date +%F-%H-%M`.sql.

PS: Note that when recovering, remember to close the binary log:

mysql> set sql_log_bin=0;

Because this is based on logical backup mode, so the execution of sql will insert data, will be recorded in the binary log inside, because this matter recovery, so the inserted binary log basically has no meaning, can be closed off, shorten the recovery time.

2.2 LVM-based snapshot backup

In physical backup, there are physical backups based on file systems (snapshots of LVM), and you can also directly use commands such as tar to package backups of the entire database directory, but these can only be cold backups, and different storage engine backups are different. Myisam automatically backs up to the table level, while innodb can only back up the entire database without opening a separate table space.

Here's an introduction to using LVM's snapshot feature for backups To be safe, first place a read lock on the database

mysql>FLUSH TABLES WITH READ LOCK

Refresh the binary log for point-in-time recovery

mysql>FLUSH LOGS

Then create snapshot volumes

lvcreate –L 1G –s –n data-snap –p –r/dev/myvg/mydata

*** Enter database to release read lock

UNLOCK TABLES

Mount snapshot volumes for backup

mount –r /dev/myvg/data-snap /mnt/snap

Then package and backup the files under/mnt/snap

When restoring, close mysqld, then backup the binary log and restore the original backup file, and then restore to the error point through the binary log (do not forget to temporarily close the binary log when restoring the binary log)

Between 2010 and 2012, mysql database deployment in Amazon cloud environment, they provide LVM snapshot, quite convenient and fast, using lvm snapshot, in Amazon 10G network, recovery is also very fast.

2.3 tarpack backup

0, prepare *** data backup from library, temporarily lock all tables, open window 1

mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +++++ | File | Position | Binlog_Do_DB |Binlog_Ignore_DB | | mysql-bin.000003 | 194554 | | | 1 row in set (0.00 sec)

PS: This window cannot exit, you must keep until the tar package is finished.

--Open a new shell window and go to the data file directory tar package

Open Window 2

[root@myfstv_21_11 data]# tar -zcvf mysqla1.tar.gz mysqla tar -zcvf mysqla1.tar.gz mysqla compressed

Switch to Window 1 and execute the unlock command

mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) mysql> copy tar to another mysql repository server, overwrite the data directory, and restart the mysql database service.

2.4 xtrabackup tool provided by percona

Support innodb physical hot backup, full backup, incremental backup, and very fast speed, support innodb storage caused by data migration between different databases, support slave backup recovery backup recovery in replication mode, in order to make xtrabackup support more functional expansion, you can set up independent table space, open innodb_file_per_table function, after enabling, you can support separate table backup.

Support online hot standby and recovery

When large amounts of data, backup and recovery are faster.

Xtrabackup can perform full, incremental, and partial backups.

xtrabackup backup principle

xtraBackup is based on InnoDB's crash-recovery capability. It will copy innodb data file, because the table is not locked, copied data is inconsistent, in the recovery time to use crash-recovery, so that data recovery consistent.

InnoDB maintains a redo log, also known as a transaction log, which contains all changes to innodb data. When InnoDB starts, it checks the data file and transaction log first, and does two steps:

XtraBackup copies innodb data page by page during backup, and does not lock the table. At the same time, XtraBackup has another thread to monitor the transactions log and copy the changed log pages once the log changes. Why the rush to copy? Because the transactions log file is limited in size, when it is full, it will be written from scratch, so new data may overwrite old data.

During the prepare process, XtraBackup uses the copied transactionslog to crash recover the innovb data file that was backed up.

The above is the MySQL database backup types and commonly used backup tools, Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.