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

This article mainly explains the "three ways to explain mysql backup", the content of the explanation is simple and clear, easy to learn and understand, now please follow the editor's ideas slowly in depth, together to study and learn the "three ways to explain mysql backup" bar!

First, the purpose of backup

Disaster recovery: recover and restore corrupted data

Requirements change: due to changes in requirements, you need to restore the data to before the change.

Testing: testing whether new features are available

II. Issues to be considered in backup

How long can you tolerate data loss?

How long will it take to recover the data?

Is it necessary to provide continuous service during recovery?

The recovered object is the whole library, multiple tables, or a single library, a single table.

Third, the type of backup

1. According to whether the database is offline or not

Cold backup (cold backup): mysql service needs to be turned off, and read and write requests are not allowed.

Warm backup: the service is online, but only read requests are supported, but write requests are not allowed

Hot backup (hot backup): while backing up, the business will not be affected.

Note:

1. This type of backup depends on the needs of the business, not the backup tool

2. MyISAM does not support hot backup, while InnoDB supports hot backup, but special tools are required.

2. According to the scope of the data set to be backed up

Full backup: full backup, backup all character sets.

Incremental backup: data that has changed since incremental backup's last full backup or incremental backup cannot be used alone. With the help of full backup, the frequency of backup depends on how often the data is updated.

Differential backup: data that has changed since differential backup's last full backup.

Recommended recovery strategy:

Full + incremental + binary log

Complete + difference + binary log

3. Based on backup data or files

Physical backup: backing up data files directly

Advantages:

Backup and restore operations are relatively simple and can span mysql versions

Fast recovery, belonging to file system level

Recommendations:

Don't assume that backups must be available, test

Mysql > check tables; check if the table is available

Logical backup: data and code in backup table

Advantages:

Recovery is simple,

The result of the backup is an ASCII file, which can be edited

Has nothing to do with storage engine

Can be backed up and restored over the network

Disadvantages:

Mysql server processes are required for backup or restore

Backup results take up more space

Floating point numbers may lose precision

After restore, the microcosm needs to be rebuilt

Four: backup objects

1. Data

2. Configuration file

3. Code: stored procedures, stored functions, triggers

4. Configuration files related to os

5. Copy related configurations

6. Binary log

5. Realization of backup and recovery

1. Using select into outfile to backup and restore data.

1.1 back up the data that needs to be backed up

The copy code is as follows:

Mysql > use hellodb; / / Open the hellodb library

Mysql > select * from students; to view the properties of students

Mysql > select * from students where Age > 30 into outfile'/ tmp/stud.txt'; / / back up the information of students over 30.

Note:

The directory path of the backup must give access to the user mysql that is currently running the mysql server

After the backup is completed, you need to copy the backed up files from the tmp directory, or you will lose the purpose of the backup.

Go back to the tmp directory to view the files you just backed up.

[root@www ~] # cd / tmp

[root@www tmp] # cat stud.txt

3Xie Yanke53M216

4Ding Dian32M44

6Shi Qing46M5\ N

13Tian Boguang33M2\ N

25Sun Dasheng100M\ N\ N

[root@www tmp] #

You'll find it's a text file. So you can't import the database directly. Need to restore using load data infile

Go back to the mysql server, delete users older than 30, and the simulated data is corrupted

Mysql > delete from students where Age > 30

Mysql > load data infile'/ tmp/stud.txt' into table students

2. Use mysqldump tools to back up and restore data.

Mysqldump is often used for warm-up, so we first need to apply a read lock to the data we want to back up.

2.1 manner in which read locks are imposed:

1. Add options directly during backup

-- lock-all-tables imposes read locks on all tables of the database to be backed up

-- lock-table only imposes a read lock on a single table, even if the entire database is backed up, it only imposes a read lock on a table when we back it up, so it is suitable for backing up a single table.

2. Write commands on the server side

Mysql > flush tables with read lock; applies a lock, which means that all tables located in memory are synchronized to disk, and then a read lock is imposed.

Mysql > flush tables with read lock; release read lock

But for the InnoDB storage engine, although you can also request a read lock, it does not mean that all its data has been synchronized to disk, so when faced with InnoDB, we have to use mysql > show engine innodb status; to see that all the data of InnoDB has been synchronized to disk before backing up.

2.2 backup strategy:

Full backup + incremental backup + binary log

Demonstrate the process of backup

2.3 make a full backup of the database first:

The copy code is as follows:

[root@www] # mysqldump-uroot-- single-transaction-- master-data=2-- databases hellodb > / backup/hellodb_ `date +% F`.sql

-- single-transaction: hot standby InnoDB tables can be implemented based on this option; therefore, it is not necessary to use-- lock-all-tables at the same time.

-- master-data=2 records the location of the binary log at that time of backup and comments it out. 1 is uncommented.

-- databases hellodb specifies the database to back up

Then go back to the mysql server.

2.4 go back to the mysql server to update the data

The copy code is as follows:

Mysql > create table tb1 (id int); create table

Mysql > insert into tb1 values (1), (2), (3); insert data, here is only a demonstration, randomly insert a few data

2.5 check the location recorded in the full backup file first:

The copy code is as follows:

[root@www backup] # cat hellodb_2013-09-08.sql | less

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=15684; records the location of the binary log

2.6 when returning to the server side:

The copy code is as follows:

Mysql > show master status; shows the location of the binary log at this time

The incremental part is from the location recorded in the backup file to where we are at this time.

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000004 | 15982 | |

+-+

2.7 make incremental backups

The copy code is as follows:

[root@www backup] # mysqlbinlog-start-position=15694-stop-position=15982

/ mydata/data/mysql-bin.000013 > / backup/hellodb_ `date + $qualified% H`.sql

2.8 go back to the server

The copy code is as follows:

Mysql > insert into tb1 values (4), (5); insert some values

Mysql > drop database hellodb; delete the hellodb library

2.9 Export the binary log this time:

The copy code is as follows:

[root@www backup] # mysqlbinlog-- start-position=15982 / mydata/data/mysql-bin.000013 to view the location of the binary log during the delete operation

[root@www backup] # mysqlbinlog-- start-position=15982-- stop-position=16176 / mydata/data/mysql-bin.000013 > / tmp/hellodb.sql / / Export binary log

2.10 take mysql offline first

Back to the server side:

The copy code is as follows:

Mysql > set sql_log_bin=0; turn off binary log

Mysql > flush logs; scroll down the log

2.11 simulate database corruption

The copy code is as follows:

Mysql > drop database hellodb

2.12 start recovering data:

The copy code is as follows:

[root@www] # mysql

< /backup/hellodb_2013-09-08.sql //导入完全备份文件 [root@www ]# mysql < /backup/hellodb_2013-09-08_05.sql //导入增量备份文件 [root@www ]# mysql< hellodb.sql //导入二进制文件 验证完成，显示结果为我们预想的那样 注： 1、真正在生产环境中，我们应该导出的是整个mysql服务器中的数据，而不是单个库，因此应该使用--all-databases 2、在导出二进制日志的时候，可以直接复制文件即可，但是要注意的是，备份之前滚动下日志。 3、利用lvm快照实现几乎热备的数据备份与恢复 3.1策略： 完全备份+二进制日志； 3.2准备： 注：事务日志必须跟数据文件在同一个LV上； 3.3创建lvm Lvm的创建这里就不多说了，想了解话点击http://www.jb51.net/LINUXjishu/105937.html 3.4 修改mysql主配置文件存放目录内的文件的权限与属主属组，并初始化mysql 复制代码 代码如下: [root@www ~]# mkdir /mydata/data //创建数据目录 [root@www ~]# chown mysql:mysql /mydata/data //改属组属主 [root@www ~]# [root@www ~]# cd /usr/local/mysql/ //必须站在此目录下 [root@www mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data //初始化mysql 3.5修改配置文件： 复制代码 代码如下: vim /etc/my.cof datadir=/mydata/data 添加数据目录 sync_binlog = 1 开启此功能 3.6 启动服务 复制代码 代码如下: [root@www mysql]# service mysqld start mysql>

Set session sql_log_bin=0; closes the binary log

Mysql > source / backup/all_db_2013-09-08.sql reads backup files

3.7 go back to the mysql server:

The copy code is as follows:

Mysql > FLUSH TABLES WITH READ LOCK; request read lock

Note: do not quit, start another terminal:

Mysql > SHOW MASTER STATUS; to view the location of the binary file

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000004 | 107 |

+-+

1 row in set (0.00 sec)

Mysql > FLUSH LOGS; is recommended to scroll down the log. This will be very convenient when backing up the log.

3.8 Export binaries and create a directory to store them separately

The copy code is as follows:

[root@www ~] # mkdir / backup/limian

[root@www ~] # mysql-e 'show master status;' > / backup/limian/binlog.txt

[root@www ~] #

3.9 create a snapshot of the volume on which the data resides:

The copy code is as follows:

[root@www] # lvcreate-L 100m-s-pr-n mysql_snap / dev/myvg/mydata

Go back to the server and release the read lock

The copy code is as follows:

Mysql > UNLOCK TABLES

[root@www ~] # mount / dev/myvg/mysql_snap / mnt/data

[root@www data] # cp * / backup/limian/

[root@www data] # lvremove / dev/myvg/mylv_snap

3.10 update the data of the database and delete the first data file of the data directory to simulate database corruption

The copy code is as follows:

Mysql > create table limiantb (id int,name CHAR (10))

Mysql > insert into limiantb values (1)

[root@www data] # mysqlbinlog-- start-position=187 mysql-bin.000003 > / backup/limian/binlog.sql

[root@www backup] # cd / mydata/data/

[root@www data] # rm-rf *

[root@www] # cp-a / backup/limian/* / mydata/data/

[root@www data] # chown mysql:mysql *

3.11 test

Start the service

The copy code is as follows:

[root@www data] # service mysqld start

[root@www data] # mysql login test

Mysql > SHOW DATABASES

Mysql > SET sql_log_bin=0

Mysql > source/backup/limian/binlog.sql; # binary recovery

Mysql > SHOW TABLES; # to view the recovery results

Mysql > SET sql_log_bin=1; # Open binary log

Note: this method realizes the backup of data files close to hot backup, and the data files placed in lvm can flexibly change the size of lvm according to the size of the data, and the backup method is also very simple.

4. Backup and restore based on Xtrabackup

Official site: www.percona.com

Advantages:

1. Perform a full backup quickly and reliably

2. Transactions will not be affected in the process of backup

3. It supports data flow, network transmission and compression, so it can effectively save disk resources and network bandwidth.

4. The availability of verification data can be automatically backed up.

Install Xtrabackup

The copy code is as follows:

[root@www] # rpm-ivh percona-xtrabackup-2.1.4-656.rhel6.i686.rpm

The latest version of the software is available from http://www.percona.com/software/percona-xtrabackup/

Note: when backing up the database, we should have permissions, but it should be noted that the user when backing up the database should be given minimum permissions to ensure security.

4.1 premise:

You should make sure that you are using a single table and a table space, otherwise the backup and recovery of a single table is not supported.

Add to the mysqld section in the configuration file

Innodb_file_per_table = 1

4.2 backup strategy

Full backup + incremental backup + binary log

4.3 prepare a directory for storing backup data

The copy code is as follows:

[root@www ~] # makdir / innobackup

4.4 make a full backup:

The copy code is as follows:

[root@www] # innobackupex-- user=root-- password=mypass / innobackup/

Note:

1. As long as the last line shows innobackupex: completed OK, it means that your backup is correct.

2. Another thing to note is that after each backup, a directory named after the current point in time is automatically created under the data directory to store the backup data, so let's see what we have.

[root@www 2013-09-12 11-03-04] # ls

Backup-my.cnf ibdata1 performance_schema xtrabackup_binary xtrabackup_checkpoints

Hellodb mysql test xtrabackup_binlog_info xtrabackup_logfile

[root@www 2013-09-12 11-03-04] #

Xtrabackup_checkpoints: backup type, backup status, and LSN (log serial number) scope information

Xtrabackup_binlog_info: the location of the binary log files currently in use by the mysql server and the binary log events up to the moment the backup is made.

Xtrabackup_logfile: non-text file, xtrabackup's own log file

Xtrabackup_binlog_pos_innodb: the current position of the binary log file and the binary log file for the InnoDB or XtraDB table.

Backup-my.cnf: the configuration of mysqld in the data file during backup

4.5 go back to the mysql server to update the data

The copy code is as follows:

Mysql > use hellodb

Mysql > delete from students where StuID > = 24

4.6 incremental backup

The copy code is as follows:

Innobackupex-- user=root-- password=mypass-- incremental / innobackup/--incremental-basedir=/innobackup/2013-09-12 11-03-04 /

-- incremental specifies the backup type

-- incremental-basedir= specifies which backup this incremental backup is based on, here is the full backup file, so that the data from the incremental backup can be merged into the full backup

4.7 second increment

Modify the data first.

The copy code is as follows:

Mysql > insert into students (Name,Age,Gender,ClassID,TeacherID) values ('tom',33,'M',2,4)

Innobackupex-- user=root-- password=mypass-- incremental / innobackup/-- incremental-basedir=/innobackup/2013-09-12 11-37-01 /

You only need to change the last directory to the data directory of the first incremental backup.

4.8 Last change to the data but no incremental backup

The copy code is as follows:

Mysql > delete from coc where id=14

4.9 back up the binary log files (because the last modification did not make an incremental backup, you have to rely on the binary log for point-in-time recovery)

The copy code is as follows:

[root@www data] # cp mysql-bin.000003 / tmp/

4.10 simulate database crash

The copy code is as follows:

[root@www data] # service mysqld stop

[root@www data] # rm-rf *

Preparation before recovery

4.11 data synchronization for full backup

The copy code is as follows:

[root@www] # innobackupex-- apply-log-- redo-only / innobackup/2013-09-12 11-03-04 /

4.12 data synchronization for the first increment

The copy code is as follows:

Innobackupex-apply-log-redo-only / innobackup/2013-09-12 11-03-04 /-incremental-basedir=/innobackup/2013-09-12 11-37-01 /

4.13 data synchronization for the second increment

The copy code is as follows:

Innobackupex-apply-log-redo-only / innobackup/2013-09-12 11-03-04 /-incremental-basedir=/innobackup/2013-09-12 11-45-53 /

-- the meaning of apply-log is to undo transactions that are not commit at the time of backup, and those that have been commit but are still in the transaction log are applied to the database.

Note:

For xtrabackup, it is based on transaction log and data file backup. The backup data may contain transactions that have not yet been committed or transactions that have been committed but have not been synchronized to the database file. It should also be preprocessed to synchronize committed transactions to data files, and uncommitted transactions should be rolled back. Therefore, the database that is backed up cannot be restored immediately.

The process of preprocessing:

First of all, for the full backup file, only the committed transaction is synchronized to the data file, it should be noted that when there is an increment, you cannot roll back the transaction, otherwise your incremental backup will have no effect.

Then merge the first incremental backup into the full backup file

And so on, merge the last few increments into the files after the previous merge, so that we can do point-in-time recovery as long as we have a full backup + binary log.

4.14 data recovery

The copy code is as follows:

[root@www ~] # service mysqld stop

[root@www data] # rm-rf * simulate database crash

[root@www] # innobackupex-- copy-back / innobackup/2013-09-12 11-03-04 /

-- copy-back database recovery followed by the location of the backup directory

4.15 Detection:

The copy code is as follows:

[root@www ~] # cd / mydata/data/

[root@www data] # chown mysql:mysql *

[root@www data] # service mysqld start

The test results are normal.

Thank you for your reading, the above is the content of "three ways to explain mysql backup", after the study of this article, I believe you have a deeper understanding of the three ways of mysql backup to explain this problem, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.