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

I do not know if you have any understanding of the previous articles on various methods of backing up MySQL databases, but today I am here to give you a brief introduction. If you are interested, let's take a look at the text. I believe you will gain something after reading the various methods of backing up the MySQL database.

View the information of the database

Mysql > SHOW DATABASES; # to view the current database, our database is employees

+-+

| | Database |

+-+

| | information_schema |

| | employees |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > USE employees

Database changed

Mysql > SHOW TABLES; # View the tables in the current library

+-+

| | Tables_in_employees |

+-+

| | departments |

| | dept_emp |

| | dept_manager |

| | employees |

| | salaries |

| | titles |

+-+

6 rows in set (0.00 sec)

Mysql > SELECT COUNT (*) FROM employees; # due to space, let's just look at the number of lines in employees, which is 300024.

+-+

| | COUNT (*) |

+-+

| | 300024 |

+-+

1 row in set (0.05sec)

Impose a read lock on the database

Mysql > FLUSH TABLES WITH READ LOCK; # imposes read locks on all tables

Query OK, 0 rows affected (0.00 sec)

Backup data files

[root@node1 ~] # mkdir / backup # create a folder to store backup database files

[root@node1 ~] # cp-a / var/lib/mysql/* / backup # copy source data files with reserved permissions

[root@node1 ~] # ls / backup # View the files in the directory

Employees ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock test

Simulated data loss and recovery

[root@node1 ~] # rm-rf / var/lib/mysql/* # Delete all files in the database

[root@node1 ~] # service mysqld restart # restart MySQL. If it is compiled and installed, it should not be started. If rpm is installed, the database will be reinitialized.

Mysql > SHOW DATABASES; # because we are installed by rpm, connect to MySQL to check, and find that the data is missing!

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | test |

+-+

3 rows in set (0.00 sec)

[root@node1] # rm-rf / var/lib/mysql/* # this step may not be done

[root@node1 ~] # cp-a / backup/* / var/lib/mysql/ # copy the backed up data files back

[root@node1 ~] # service mysqld restart # restart MySQL

# reconnect the data and view

Mysql > SHOW DATABASES; # database has been restored

+-+

| | Database |

+-+

| | information_schema |

| | employees |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > USE employees

The number of rows in the mysql > SELECT COUNT (*) FROM employees; # table has not changed.

+-+

| | COUNT (*) |

+-+

| | 300024 |

+-+

1 row in set (0.06 sec)

# # complete

Replicate BINARY LOG backups 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

# basic syntax format

Shell > mysqldump [options] db_name [tbl_name...] Manual CRATE DATABASES is required for recovery

Shell > mysqldump [options]-- databases db_name... Recovery does not require manual creation of the database

Shell > mysqldump [options]-- all-databases recovery does not require manual database creation

Other options:

-E,-- events: backup event scheduler

-R,-- routines: backup stored procedures and stored functions

-- triggers: trigger for backing up tables;-- skip-triggers

-- master-date [= value]

1: recorded as CHANGE MASTER TO statements, statements are not commented

2: CHANGE MASTER TO statements recorded as comments

Based on binary restore, you can only restore in full library.

-- flush-logs: log scrolling

Perform log scrolling after locking the table

View the information of the database

Mysql > SHOW DATABASES; # to view the current database, our database is employees

+-+

| | Database |

+-+

| | information_schema |

| | employees |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > USE employees

Database changed

Mysql > SHOW TABLES; # View the tables in the current library

+-+

| | Tables_in_employees |

+-+

| | departments |

| | dept_emp |

| | dept_manager |

| | employees |

| | salaries |

| | titles |

+-+

6 rows in set (0.00 sec)

Mysql > SELECT COUNT (*) FROM employees; # due to space, let's just look at the number of lines in employees, which is 300024.

+-+

| | COUNT (*) |

+-+

| | 300024 |

+-+

1 row in set (0.05sec)

Back up the database using mysqldump

[root@node1 ~] # mysql-e 'SHOW MASTER STATUS' # View the status of the current binary file and record the number of position

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000003 | 106 | |

+-+

[root@node1 ~] # mysqldump-- all-databases-- lock-all-tables > backup.sql # backup the database to the backup.sql file

Mysql > CREATE DATABASE TEST1; # create a database

Query OK, 1 row affected (0.00 sec)

Mysql > SHOW MASTER STATUS; # write down the current position

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000003 | 191 | |

+-+

1 row in set (0.00 sec)

[root@node1 ~] # cp / var/lib/mysql/mysql-bin.000003 / root # backup binaries

[root@node1 ~] # service mysqld stop # stop MySQL

[root@node1 ~] # rm-rf / var/lib/mysql/* # Delete all data files

[root@node1 ~] # service mysqld start # starts MySQL. If it is compiled and installed, it should not be started (need to be reinitialized). If rpm is installed, the database will be reinitialized.

Mysql > SHOW DATABASES; # check the database, data is missing!

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | test |

+-+

3 rows in set (0.00 sec)

Mysql > SET sql_log_bin=OFF; # close the binary log for the time being

Query OK, 0 rows affected (0.00 sec)

Mysql > source backup.sql # recovery time, depending on the database time

Mysql > SET sql_log_bin=ON; turn on binary log

Mysql > SHOW DATABASES; # database recovery, but missing TEST1

+-+

| | Database |

+-+

| | information_schema |

| | employees |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

[root@node1 ~] # mysqlbinlog-- start-position=106-- stop-position=191 mysql-bin.000003 | mysql employees # recover data through binary log increments

Mysql > SHOW DATABASES; # now TEST1 appears!

+-+

| | Database |

+-+

| | information_schema |

| | TEST1 |

| | employees |

| | mysql |

| | test |

+-+

5 rows in set (0.00 sec)

# complete

Back up data using lvm2 Snapshot

Let's review the knowledge of lvm2-snapshot before we do the experiment.

LVM snapshot is simply to save the metadata of all files in the source partition of the snapshot at a point in time. If the source file does not change, then the corresponding file accessing the snapshot volume points directly to the source file of the source partition. If the source file changes, the corresponding file in the snapshot volume will not change. Snapshot volumes are mainly used for auxiliary backup files. Here is only a brief introduction, click to view the detailed introduction

Deploy the lvm environment

Add a hard disk; here we directly implement the hot plug of the SCSI hard disk. First, add a hard disk to the virtual machine without rebooting.

[root@node1 ~] # ls / dev/sd* # only have the following hard drives, but if we don't reboot, we can let the system recognize the newly added hard drives.

/ dev/sda / dev/sda1 / dev/sda2

[root@node1 ~] # echo'--- > / sys/class/scsi_host/host0/scan

[root@node1 ~] # echo'--- > / sys/class/scsi_host/host1/scan

[root@node1 ~] # echo'--- > / sys/class/scsi_host/host2/scan

[root@node1] # ls / dev/sd* # look! Sdb identified it.

/ dev/sda / dev/sda1 / dev/sda2 / dev/sdb

[root@node1 ~] # fdisk / dev/sdb # partition

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel

Building a new DOS disklabel with disk identifier 0xd353d192.

Changes will remain in memory only, until you decide to write them.

After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w (rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to

Switch off the mode (command 'c') and change display units to

Sectors (command'u').

Command (m for help): n

Command action

E extended

P primary partition (1-4)

P

Partition number (1-4): 1

First cylinder (1-2610, default 1):

Using default value 1

Last cylinder, + cylinders or + size {KMagne Mpeng} (1-2610, default 2610): + 15G

Command (m for help): t

Selected partition 1

Hex code (type L to list codes): 8e

Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): W

The 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] # partx-a / dev/sdb

BLKPG: Device or resource busy

Error adding partition 1

# # creating logical Volume

[root@node1 ~] # pvcreate / dev/sdb1

Physical volume "/ dev/sdb1" successfully created

[root@node1 ~] # vgcreate myvg / dev/sdb1

Volume group "myvg" successfully created

[root@node1] # lvcreate-n mydata-L 5G myvg

Logical volume "mydata" created.

[root@node1 ~] # mkfs.ext4 / dev/mapper/myvg-mydata # format

[root@node1 ~] # mkdir / lvm_data

[root@node1 ~] # mount / dev/mapper/myvg-mydata / lvm_data # Mount to / lvm_data

[root@node1 ~] # vim / etc/my.cnf # modify the datadir of the mysql configuration file as follows

Datadir=/lvm_data

[root@node1 ~] # service mysqld restart # restart MySQL

# re-import employees database # skip #

View the information of the database

Mysql > SHOW DATABASES; # to view the current database, our database is employees

+-+

| | Database |

+-+

| | information_schema |

| | employees |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > USE employees

Database changed

Mysql > SHOW TABLES; # View the tables in the current library

+-+

| | Tables_in_employees |

+-+

| | departments |

| | dept_emp |

| | dept_manager |

| | employees |

| | salaries |

| | titles |

+-+

6 rows in set (0.00 sec)

Mysql > SELECT COUNT (*) FROM employees; # due to space, let's just look at the number of lines in employees, which is 300024.

+-+

| | COUNT (*) |

+-+

| | 300024 |

+-+

1 row in set (0.05sec)

Create a snapshot volume and back up

Mysql > FLUSH TABLES WITH READ LOCK; # Lock all tables

Query OK, 0 rows affected (0.00 sec)

[root@node1 lvm_data] # lvcreate-L 1G-n mydata-snap-p r-s / dev/mapper/myvg-mydata # create a snapshot volume

Logical volume "mydata-snap" created.

Mysql > UNLOCK TABLES; # unlock all tables

Query OK, 0 rows affected (0.00 sec)

[root@node1 lvm_data] # mkdir / lvm_snap # create a folder

[root@node1 lvm_data] # mount / dev/myvg/mydata-snap / lvm_snap/ # Mount snap

Mount: block device / dev/mapper/myvg-mydata--snap is write-protected, mounting read-only

[root@node1 lvm_data] # cd / lvm_snap/

[root@node1 lvm_snap] # ls

Employees ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.index test

[root@node1 lvm_snap] # tar cf / tmp/mysqlback.tar * # package the file to / tmp/mysqlback.tar

[root@node1 ~] # umount / lvm_snap/ # Uninstall snap

[root@node1 ~] # lvremove myvg mydata-snap # Delete snap

Recover data

[root@node1 lvm_snap] # rm-rf / lvm_data/*

[root@node1 ~] # service mysqld start # starts MySQL. If it is compiled and installed, it should not be started (need to be reinitialized). If rpm is installed, the database will be reinitialized.

Mysql > SHOW DATABASES; # check the database, data is missing!

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | test |

+-+

3 rows in set (0.00 sec)

[root@node1 ~] # cd / lvm_data/

[root@node1 lvm_data] # rm-rf * # Delete all files

[root@node1 lvm_data] # tar xf / tmp/mysqlback.tar # extract the backup database to this folder

[root@node1 lvm_data] # ls # View the current file

Employees ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.index test

Mysql > SHOW DATABASES; # data is restored

+-+

| | Database |

+-+

| | information_schema |

| | employees |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

# # complete

Backing up with Xtrabackup

In order to demonstrate better, we use the version of mariadb-5.5 this time, using xtrabackup to use InnoDB can make the most of it, and each table of InnoDB must use a separate tablespace, we need to add innodb_file_per_table = ON to the configuration file to open it.

Download and install xtrabackup

We install it here through wget percona's official rpm package.

[root@node1 ~] # wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/6/x86_64/percona-xtrabackup-2.3.4-1.el6.x86_64.rpm

[root@node1 ~] # yum localinstall percona-xtrabackup-2.3.4-1.el6.x86_64.rpm # requires EPEL source

Xtrabackup introduction

Xtrabackup is a mysql database backup tool provided by percona. According to officials, it is the only open source tool in the world that can provide hot backup for innodb and xtradb databases. Features:

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

[root@node1 ~] # mkdir / extrabackup # create backup directory

[root@node1 ~] # innobackupex-- user=root / extrabackup/ # backup data

# prompt complete to indicate success *

[root@node1 ~] # ls / extrabackup/ # see backup directory

2016-04-27 07-30-48

[root@node1 ~] # innobackupex-- apply-log / extrabackup/2016-04-27upload 07-30-48 / # specify the directory of backup files

# in general, the end of the following three lines represents success *

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 369661462

160427 07:40:11 completed OK!

[root@node1] # cd / extrabackup/2016-04-277-30-48 /

[root@node1 2016-04-27007-30-48] # ls-hl # View backup files

Total 31M

-rw-r- 1 root root 386 Apr 27 07:30 backup-my.cnf

Drwx- 2 root root 4.0K Apr 27 07:30 employees

-rw-r- 1 root root 18m Apr 27 07:40 ibdata1

-rw-r--r-- 1 root root 5.0M Apr 27 07:40 ib_logfile0

-rw-r--r-- 1 root root 5.0M Apr 27 07:40 ib_logfile1

Drwx- 2 root root 4.0K Apr 27 07:30 mysql

Drwx- 2 root root 4.0K Apr 27 07:30 performance_schema

Drwx- 2 root root 4.0K Apr 27 07:30 test

-rw-r- 1 root root 27 Apr 27 07:30 xtrabackup_binlog_info

-rw-r--r-- 1 root root 29 Apr 27 07:40 xtrabackup_binlog_pos_innodb

-rw-r- 1 root root 117 Apr 27 07:40 xtrabackup_checkpoints

-rw-r- 1 root root 470 Apr 27 07:30 xtrabackup_info

-rw-r- 1 root root 2.0M Apr 27 07:40 xtrabackup_logfile

Recover data

[root@node1 ~] # rm-rf / data/* # Delete data files

* * you don't need to start the database to restore *

[root@node1] # innobackupex-- copy-back / extrabackup/2016-04-27073048 / # restore data and remember how to use it

# We are compiling and installing mariadb here, so we need to do something #

[root@node1 data] # killall mysqld

[root@node1] # chown-R mysql:mysql. / *

[root@node1 ~] # ll / data/ # data recovery

Total 28704

-rw-rw---- 1 mysql mysql 16384 Apr 27 07:43 aria_log.00000001

-rw-rw---- 1 mysql mysql 52 Apr 27 07:43 aria_log_control

-rw-rw---- 1 mysql mysql 18874368 Apr 27 07:43 ibdata1

-rw-rw---- 1 mysql mysql 5242880 Apr 27 07:43 ib_logfile0

-rw-rw---- 1 mysql mysql 5242880 Apr 27 07:43 ib_logfile1

-rw-rw---- 1 mysql mysql 264 Apr 27 07:43 mysql-bin.000001

-rw-rw---- 1 mysql mysql 19 Apr 27 07:43 mysql-bin.index

-rw-r- 1 mysql mysql 2166 Apr 27 07:43 node1.anyisalin.com.err

[root@node1 data] # service mysqld restart

MySQL server PID file could not be found! [FAILED]

Starting MySQL.. [OK]

MariaDB [(none)] > SHOW DATABASES; # View the database, and it has been restored

+-+

| | Database |

+-+

| | information_schema |

| | employees |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec

Incremental backup

# create two databases for testing #

MariaDB [(none)] > CREATE DATABASE TEST1

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)] > CREATE DATABASE TEST2

Query OK, 1 row affected (0.00 sec)

[root@node1] # innobackupex-- incremental / extrabackup/-- incremental-basedir=/extrabackup/2016-04-277-30-48 /

[root@node1] # ls / extrabackup/2016-04-270757-22 / # View backup files

Total 96

-rw-r- 1 root root 386 Apr 27 07:57 backup-my.cnf

Drwx- 2 root root 4096 Apr 27 07:57 employees

-rw-r- 1 root root 49152 Apr 27 07:57 ibdata1.delta

-rw-r- 1 root root 44 Apr 27 07:57 ibdata1.meta

Drwx- 2 root root 4096 Apr 27 07:57 mysql

Drwx- 2 root root 4096 Apr 27 07:57 performance_schema

Drwx- 2 root root 4096 Apr 27 07:57 test

Drwx- 2 root root 4096 Apr 27 07:57 TEST1

Drwx- 2 root root 4096 Apr 27 07:57 TEST2

-rw-r- 1 root root 21 Apr 27 07:57 xtrabackup_binlog_info

-rw-r- 1 root root 123 Apr 27 07:57 xtrabackup_checkpoints

-rw-r- 1 root root 530 Apr 27 07:57 xtrabackup_info

-rw-r- 1 root root 2560 Apr 27 07:57 xtrabackup_logfile

BASEDIR refers to the directory where the full backup is located, and after the execution of this command, the innobackupex command creates a new time-named directory in the / extrabackup directory to hold all incremental backup data. In addition, when you perform an incremental backup again after an incremental backup, its-- incremental-basedir should point to the directory where the last incremental backup was located.

It is important to note that incremental backups can only be applied to InnoDB or XtraDB tables, and for MyISAM tables, incremental backups are actually performed as full backups.

Organize incremental backups

[root@node1] # innobackupex-- apply-log-- redo-only / extrabackup/2016-04-277-30-48 /

[root@node1] # innobackupex-- apply-log-- redo-only / extrabackup/2016-04-279 07-30-48 /-incremental-dir=/extrabackup/2016-04-27 7-5

7-22 /

Recover data

[root@node1 ~] # rm-rf / data/* # Delete data

[root@node1] # innobackupex-- copy-back / extrabackup/2016-04-270730-48 / # after collating incremental backups, you can restore them directly through full backups.

[root@node1] # chown-R mysql.mysql / data/

[root@node1 ~] # ls / data/-l

Total 28732

-rw-rw---- 1 mysql mysql 8192 Apr 27 08:05 aria_log.00000001

-rw-rw---- 1 mysql mysql 52 Apr 27 08:05 aria_log_control

Drwx- 2 mysql mysql 4096 Apr 27 08:05 employees

-rw-r- 1 mysql mysql 18874368 Apr 27 08:05 ibdata1

-rw-r- 1 mysql mysql 5242880 Apr 27 08:05 ib_logfile0

-rw-r- 1 mysql mysql 5242880 Apr 27 08:05 ib_logfile1

Drwx- 2 mysql mysql 4096 Apr 27 08:05 mysql

-rw-rw---- 1 mysql mysql 245 Apr 27 08:05 mysql-bin.000001

-rw-rw---- 1 mysql mysql 19 Apr 27 08:05 mysql-bin.index

-rw-r- 1 mysql mysql 1812 Apr 27 08:05 node1.anyisalin.com.err

-rw-rw---- 1 mysql mysql 5 Apr 27 08:05 node1.anyisalin.com.pid

Drwx- 2 mysql mysql 4096 Apr 27 08:05 performance_schema

Drwx- 2 mysql mysql 4096 Apr 27 08:05 test

Drwx- 2 mysql mysql 4096 Apr 27 08:05 TEST1

Drwx- 2 mysql mysql 4096 Apr 27 08:05 TEST2

-rw-r- 1 mysql mysql 29 Apr 27 08:05 xtrabackup_binlog_pos_innodb

-rw-r- 1 mysql mysql 530 Apr 27 08:05 xtrabackup_info

MariaDB [(none)] > SHOW DATABASES; # data restore

+-+

| | Database |

+-+

| | information_schema |

| | TEST1 |

| | TEST2 |

| | employees |

| | mysql |

| | performance_schema |

| | test |

+-+

7 rows in set (0.00 sec)

# there are still many powerful functions about xtrabackup that have not been described. If you are interested, you can read the official documents.

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

What do you think of this article after reading the introduction of various methods of backing up MySQL database? If you want to know more about it, you can continue to follow our industry information section.

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.