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

Preface

Why do I need to back up my data?

Backup type of data

How MySQL backs up data

Issues to be considered in backup

Design an appropriate backup strategy

Use cp for backup

Replicate BINARY LOG backups using mysqldump+

Back up data using lvm2 Snapshot

Backing up with Xtrabackup

Preface

Try to think about what is most important in a production environment. If the hardware of our server is broken, it can be repaired or replaced, and the software problem can be repaired or reinstalled, but what if the data is gone? This is probably the most terrifying thing. I think nothing is more important than data in a production environment. So how can we ensure that the data is not lost or can be quickly recovered after it is lost? As long as you finish reading this article, you should be able to have some understanding of the implementation of data backup and recovery in MySQL.

Why do I need to back up my data?

In fact, the preface also roughly explains why the data should be backed up, but we should still have a specific understanding of why the data should be backed up.

In the production environment, our database may encounter a variety of accidents, resulting in data loss, which can be divided into the following categories.

Hardware failure

Software failure

natural disaster

Hacker attack

Misoperation (the largest proportion)

Therefore, in order to recover data after data loss, we need to back up data regularly. The strategy of backing up data needs to be customized according to different application scenarios, and there are roughly several reference values. We can customize the data backup strategy in a specific environment according to these values.

Be able to tolerate how much data is lost

How long will it take to recover the data

Which data needs to be recovered?

Backup type of data

The backup types of data are mainly divided into the following groups according to their own characteristics.

Full backup

Partial backup

Full backup refers to backing up the entire dataset (that is, the entire database), and partial backup refers to backing up part of the dataset (for example, backing up only one table)

Some backups are divided into the following two types

Incremental backup

Differential backup

Incremental backup refers to backing up data that has changed since the last backup (incremental or complete). Features: saving space and restoring hassle

Differential backup refers to the characteristics of data that have changed since the last full backup: wasted space and easier to restore than incremental backup

Schematic diagram

How MySQL backs up data

There are several ways for us to back up data in MySQl

Hot backup

Warm backup

Cold backup

Hot backup means that when the database is backed up, the read and write operations of the database are not affected.

Warm backup means that when the database is backed up, the database read operation can be performed, but the write operation cannot be performed.

Cold backup means that when the database is backed up, the database cannot be read or written, that is, the database will be offline.

Different backup methods in MySQL should also consider whether the storage engine supports it or not.

MyISAM

Hot standby ×

Warm standby √

Cold standby √

InnoDB

Hot standby √

Warm standby √

Cold standby √

After considering the running state of the database when the data is backed up, we also need to consider the backup mode of the data in the MySQL database.

Physical backup is generally achieved by directly packaging and copying data files of the database through commands such as tar,cp.

Logical backup generally means exporting data from the database and saving the backup through specific tools (logical backup loses data precision)

Physical backup

Logical backup

Issues to be considered in backup

There are a few more issues we need to consider before customizing the backup strategy

What do we need to back up?

In general, the data we need to back up can be divided into the following categories

data

Binary log, InnoDB transaction log

Code (stored procedures, stored functions, triggers, event schedulers)

Server profile

Backup tool

Here we list several commonly used backup tools

Mysqldump: logical backup tool for all storage engines, supporting warm backup, full backup, partial backup, hot backup for InnoDB storage engine

Cp, tar and other archive replication tools: physical backup tool, suitable for all storage engines, cold backup, full backup, partial backup

Lvm2 snapshot: almost hot standby, backup with file system management tools

Mysqlhotcopy: a tool that doesn't live up to its name, almost cold standby, and only supports MyISAM storage engine.

Xtrabackup: a very powerful InnoDB/XtraDB hot backup tool that supports full backup and incremental backup, provided by percona

Design an appropriate backup strategy

According to different scenarios, we should make different backup strategies to back up the database. In general, there are three backup strategies.

Copy database files directly with cp,tar

Mysqldump+ replication BIN LOGS

Lvm2 Snapshot + replication BIN LOGS

Xtrabackup

The above solutions are aimed at different scenarios.

If the amount of data is small, you can use the first method to copy the database file directly.

If the amount of data is OK, you can use the second way, first use mysqldump to make a full backup of the database, and then back up BINARY LOG regularly to achieve the effect of incremental backup.

If the amount of data is mediocre and does not unduly affect the operation of the business, you can use the third way to back up the data files using the snapshot of lvm2, and then back up BINARY LOG regularly to achieve the effect of incremental backup.

If the amount of data is large and does not unduly affect the operation of the business, you can use the fourth way, after using xtrabackup for full backup, use xtrabackup for incremental backup or differential backup regularly.

Using cp for backup in practical drills

We are using a version of mysql-5.1 installed using yum, using a dataset of an employee database found on the network

View the information of the database

Mysql > SHOW DATABASES; # View the current database, our database is employees+set (USE employees; Database changedmysql > SHOW TABLES; # View the table in the current library + set (SELECT COUNT (*) FROM employees; # due to space, we only look at the number of rows of employees as 300024+set (FLUSH TABLES WITH READ LOCK; # applies a read lock Query OK on all tables, 0 rows affected (0.00 sec))

Backup data files

[root/* # this step can not be done [root@node1 ~] # cp-a / backup/* / var/lib/mysql/ # copy the backed up data file back [root@node1 ~] # service mysqld restart # restart MySQL# reconnect data and check mysql > SHOW DATABASES # Database restored +-+ | Database | +-+ | information_schema | | employees | | mysql | | test | +-+ 4 rows in set (0.00 sec) mysql > USE employees; mysql > SELECT COUNT (*) FROM employees # there is no change in the number of rows in the table +-+ | COUNT (*) | +-+ | 300024 | +-+ 1 row in set (0.06 sec) # # complete replication of BINARY LOG backup using mysqldump+

We are using a version of mysql-5.1 installed using yum, using a dataset of an employee database found on the network

We make a full backup through mysqldump, then modify the data in the table, and then restore the binary log through binary log. You need to add log_bin=on to the mysql configuration file to enable it.

Introduction to mysqldump command

Mysqldump is a client-side logical backup tool that can generate a SQL statement that reproduces the creation of the original database and table, supports all storage engines, and supports hot backup for InnoDB

Introduction of official documents

Shell > mysqldump [options] db_name [tbl_name...] To recover, you need to manually CRATE DATABASES shell > mysqldump [options] shell > mysqldump [options] SHOW DATABASES; # to view the current database. Our database is employees+set (USE employees; Database changedmysql > SHOW TABLES; # to view the table + set (SELECT COUNT (*) FROM employees) in the current database. # for space reasons, let's just look at the number of lines of employees as 300024+set (SHOW MASTER STATUS@node1 ~] @ node1 ~] or OSF disklabelBuilding a new DOS disklabel with disk identifier in memory only, until you decide to write them.After of course, the previous content wonto switch and change display units to sectors (command for help): nCommand action e extended p primary partition (default default value or + size {KMague M) G} (default for help): tSelected partition to list codes): of partition to for help): wThe partition table has been altered calling ioctl () to re-read partition table.Syncing disks.You have new mail in / var/spool/mail/root [root@node1 ~] BLKPG: Device or resource busyerror adding partition @ node1 ~] @ node1 ~] SHOW DATABASES # View the current database, our database is employees+set (USE employees; Database changedmysql > SHOW TABLES; # View the tables in the current database + set (SELECT COUNT (*) FROM employees) # for space reasons, let's just look at the number of lines of employees as 300024+set (@ node1 lvm_data] @ node1 lvm_data] @ node1 lvm_data] write-protected, mounting read-only [root@node1 lvm_data] @ node1 lvm_snap] index test [root@node1 lvm_snap] @ node1 ~] @ node1 ~]

The backup process is fast and reliable

The backup process does not interrupt the transaction that is in progress

Ability to save disk space and traffic based on functions such as compression

Automatic backup verification

Fast reduction speed

Extracted from Brother Ma's document

Full backup based on xtrabackup

We use xtrabackup's front-end configuration tool innobackupex to achieve a full backup of the database.

When using innobackupex backup, xtrabackup is called to back up all InnoDB tables, copying all files related to table structure definition (.frm), and MyISAM, MERGE, CSV, and ARCHIVE tables, as well as files related to triggers and database configuration file information, which are saved to a directory named by time.

Backup process [rootlog sequence number * can be restored without starting the database * [rootdata/* # delete data [root@node1 ~] # innobackupex R mysql.mysql / data/ [root@node1 ~] # ls / data/-l MariaDB [(none)] > SHOW DATABASES # data restore + Database | + TEST1 | | TEST2 | | employees | | mysql | | performance_schema | | test | + in set (0.00 sec) # there are still many powerful functions of xtrabackup that have not been described. If you are interested, please see the official document summary.

Backup method backup speed recovery convenience function is generally used for cp fast general, low flexibility very weak small data backup mysqldump slowly general, can ignore the difference of storage engine general small and medium-sized data backup lvm2 snapshot fast general, support almost hot backup, speed general small and medium-sized data backup xtrabackup faster and faster to achieve innodb hot backup, the storage engine requires powerful large-scale backup

In fact, we can also backup data through Master-Slave Replication.

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.