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

Xiaosheng blog: http://xsboke.blog.51cto.com

-Thank you for your reference. If you have any questions, you are welcome to communicate.

1. Mysqldump backup combined with binlog log recovery

Use mysqldump for full library backup and binlog log backup. When restoring, you can use binlog log to restore to any location or time before backup.

1. Brief introduction to Binlog

Mainly used for master-slave replication and recovery of databases, use the mysqlbinlog command to view binlog binary log files

Let's demonstrate how binlog is restored to a certain point in time through an example.

Restart the mysql service for the configuration to take effect

Verify that binlog is enabled successfully

Create databases and tables for testing and insert data

In order to facilitate the experiment, we put the records of misoperations in a new binlog log file.

Now it is found that deleting the zhangsan is a mistake, and now you need to restore the zhangsan

Check the binlog log file, restore the table to the point where the zhangsan is not deleted according to the location implementation, then skip the location where the zhangsan is deleted, and restore the tom

You can make an analysis diagram that can be used to analyze the area code specified at the time of recovery.

It can be analyzed from this figure that all the mysql-bin.00001 files can be restored.

Area 219-416 of Mysql-bin.00002 does not need to be restored.

Let's start the simulation.

First, export some binlog logs according to the requirements.

Simulated deletion of database

Restore through binlog Lo

Verification

2. Introduction to Mysqldump

Mysqldump is a tool used by mysql for backup and data transfer

Encapsulate the sql statement into a file that contains all the SQL commands needed to rebuild your database, such as CREATE DATABASE,CREATE TABLE,INSERT, etc., which can be used to achieve lightweight rapid migration or restore the database

Mysqldump exports datasheets into SQL script files, which can be used between different versions of MySQL

Mysqldump is used for backups with a small amount of data (about a few gigabytes). It is not recommended when the amount of data is large.

Exported objects: single table, multiple tables, single database, multiple databases, all databases

1) exampl

Export a specified database or single table

Export multiple databases

Export all

Fully back up the database and restart a new binlog

Import

2) Mysqldump backup scheme

Full library backup at 1 am on Sunday

Incremental backup every 4 hours from Monday to Saturday morning

Set up crontab tasks and execute backup scripts every day

First set up the cronta task

Edit mysqlfullbackup script

Edit mysqldailybackup.sh script content

II. Xtrabackup backup tool

1. Brief introduction to Xtrabackup

Xtrabackup belongs to physical backup, supports physical hot backup, open source, and has better performance than ibbackup.

Xtrabackup supports incremental backup, full backup, differential backup, and combination with binlog logs

Xtrabackup provides two command line tools:

Xtrabackup: tables that can be backed up by innodb and xtraDB storage engines

Innobackupex: storage engine that supports backing up innodb and myisam tables, written in Perl language, with a read lock executed during execution

two。 Matters needing attention

Because xtrabackup is a physical hot backup, transactions that have been committed but not written to the data file or not committed may occur during backup, so this problem needs to be solved during recovery. The committed data in the backed up data can be written to the data file using the-applog-log parameter, and all the uncommitted data can be rolled back to ensure the uniqueness of the data.

All files under data need to be deleted during data recovery.

After the restore, you need to set the master group of the recovery files before mysql can run normally.

3. Advantages of Xtrabakup

Backup is fast and can be used in larger databases to make up for some deficiencies of mysqldump.

Hot backup is supported, and the backup process does not interrupt the execution of transactions.

Support for compressed backups to save disk space and traffic

Automatic backup verification

Fast reduction speed

4. Xtrabackup installation

First download xtrabackup and install it

Wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/tarball/percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

Install related plug-ins

Install percona-toolkit

Wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpm

5. Xtrabackup+binkog

Xtrabackup+binlog can achieve point-in-time restore.

1) first create a backup directory

2) pay attention to enable binlog log

3) perform a full backup (using xtrabackup's "streaming" and "compressed backup" functions)

4) View backup

5) in a production environment, binlog logs are also backed up

You can use-start-position and-stop-position to skip the location of erroneous deletion to back up the binlog log, which is not demonstrated here

6) Xtrabackup reduction

The problem of data consistency needs to be solved before restore.

Start restore

Set up a master group for restored files

Finally, if you need to restore to a certain point in time, you can use the backed-up binlog log files to restore.

6. The backup user used in the production environment may not be root, so you need to pay attention to the permissions.

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.