This article teaches you how to back up and restore MySQL databases. 02/24 Update SLTechnology News&Howtos

This article teaches you how to back up and restore MySQL databases.

2025-02-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

The following mainly brings you backup and recovery of MySQL database, I hope these contents can bring you practical use, which is also the main purpose of my article on backup and recovery of MySQL database. All right, don't talk too much nonsense, let's just read the following.

1. The significance of backing up data

Netizens often ask, what exactly is the work of operation and maintenance, and what do you do? In fact, the operation and maintenance work is simply summarized in two things: the first is to protect the company's data, and the second is that the website provides services 24 hours a day.

Which is more important for data and websites that provide services 24 hours a day?

It depends on the business and the company.

For example: banking, financial industry, data is the most important, can not be lost, the impact of downtime may not be so great.

Another example: Baidu search, Tencent qq chat records lost tens of thousands of data, it is nothing.

As far as the data is concerned, the core of the data is the database core data, of course, including other data, the following is the main explanation of the database data.

For the vast majority of enterprises, time zone data on time zone business opportunities, time zone products, time zone customers, company closure …... Therefore, the backup and recovery of the database is very important.

There are two ways to back up:

1. Logical backup: export data in the form of SQL statements, slow, cross-platform, cross-version, cross-software.

two。 Physical backup, direct backup of data files, cp tar, fast, not easy to cross-platform, cross-version. Cross-software, common method: linux backup tool (cp tar).

3. Xtrabackup

two。 Database backup

2.1 backing up a single database exercises using multiple parameters

The mysql database comes with an easy-to-use backup command, mysqldump, which is basically used as follows:

Command syntax: mysqldump-u user name-p password database name > backup file name

Here are five sample directories:

(1) backup name is oldboy database

(2) solve the problem of garbled backup files

(3) use the above backup data for recovery test

(4) the difference between using-B parameter and not applying-B parameter when backing up and the two backup methods.

(5) optimize the use of-compact parameter so that the backup file does not output comments, optimize the space.

(6) specify the compression command to compress the MySQL data of the backup

The example operation is as follows:

(1) example 1: back up the library named oldboy

1. View oldboy data before backup

two。 Execute backup commands on the command line

[root@db01 /] # mysqldump-uroot-poldboy123-S / data/3306/mysql.sock oldboy > / opt/oldboy_$ (date +% F) .sql [root@db01 /] # ll / opt/oldboy_2016-08-28.sql RW Aug RWLV-1 rootroot 2700 Aug 28 23:25 / opt/oldboy_2016-08-28.sql

3. After backup, check the backed-up data.

[root@db01 /] # egrep-v'# |\ * | ^ $'/ opt/oldboy_2016-08-28.sql DROP TABLE IF EXISTS`student`; CREATE TABLE `student` (`id` int (4) NOT NULL AUTO_INCREMENT,--INSERT INTO `test`VALUES (3grammatical Kaka'), (5grammatical Kaka'), (1mementoldboy'), (2mementoldboys'), (4memedzuma'); UNLOCK TABLES

Tip: based on the results above, we see the table structure statements that have been backed up and the sql statements that are inserted for data consolidation.

(2) example 2: if garbled code occurs, specify character set parameters to solve the backup garbled problem.

If the backup database backup file is garbled, use the following parameters to solve the Chinese garbled. The procedure is as follows

1. View the character set settings of the backup database client and server

two。 Specify the corresponding character set backup, here-- default-character_set=utf8 (specify character set parameters)

Mysqldump-uroot-poldboy123-S / data/3306/mysql.sock-oldboy > / opt/oldboy_$ (date +% F). Sql

Execution result:

[root@db01 opt] # mysqldump-uroot-poldboy123-S/data/3306/mysql.sock oldboy > / opt/oldboy_$ (date +% F). Sql [root@db01 opt] # egrep-v'# |\ * |-- | ^ $'/ opt/oldboy_2016-08-28.sql

Tip: if there is garbled, use the specified payment and view, it will not be garbled, in fact, even if the exported Chinese garbled does not matter, importing the database is still normal.

(3) example 2: data recovery test using the above backed-up files

[root@db01 opt] # mysql-uroot-poldboy123-S/data/3306/mysql.sock oldboy

< /opt/oldboy_2016-08-28.sql[root@db01 opt]#mysql-uroot -poldboy123 -S /data/3306/mysql.sock -e "select * fromoldboy.test;"+--------+-----------+| id | name |+--------+-----------+| 3 | hehe || 5 | kaka || 1 | oldboy || 2 | oldgirl || 4 | zuma | +--------+-----------+ （4）范例4：备份时加-B参数以及使用备份文件恢复数据的两种方法 1. 查看不加-B参数备份文件和加-B备份文件区别不加-B参数备份命令： mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy>

/ opt/oldboy_$ (date +% F) .sql

Add-B parameter backup command:

Mysqldump-uroot-poldboy123-S / data/3306/mysql.sock oldboy > / opt/oldboy_B_$ (date +% F). Sql

Use the vimdiff command to compare the difference between a non-B parameter profile and a plus-B parameter profile

[root@db01 /] # cd / opt/ [root@db01 opt] # vimdiff oldboy_2016-08-28.sql oldboy_B_2016-08-28.sql-Current Database: `oldboy`

# conclusion:

Intuitively, the function of adding the-B parameter is to add the statement to create the database core to connect to the database. We can directly restore the backup files using-B, while the backup files that are not applicable to-B need to create a library first. The difference is the following two statements:

CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `oldboy`USE `oldboy`

The first kind of recovery data (backup data without-B parameter skin care data)

Note: now we use the backup file without the-B parameter to restore the database, which has been compared with the backup file with the-B parameter. The backup file with the-B parameter has more files than those without the-B parameter to create the oldboy database core and enter the oldbou database, that is to say, the backup file with the-B parameter saves us the two steps of creating the database and entering the database. Just import the backup file into the database. You don't need to specify that library yet, because it specifies the oldboy database.

The second type of backup file recovery data (backup file recovery using the plus-B parameter)

# Note: the configuration files backed up with-B parameter and those backed up without-B parameter have been compared and explained above, adding that the-B parameter will automatically build the database and restore the data when restoring the data, and if you do not add-B, you have to build the database manually first, so this is the advantage of adding-B parameter. If you can see for details that the data of one database is copied to another database, you can not add the-B parameter. At that time, you can import the backup file directly into the library.

Tip:-B is not only the application of one-click recovery database data for automatically building use libraries, but also can specify multiple libraries.

(5) example 5: optimize backup file size to reduce output comments (debug debugging)

Use the-compact parameter of mysqldump to optimize the backup result:

[root@db01 opt] # mysqldump-uroot-poldboy123-S/data/3306/mysql.sock-B oldboy-

Backup with-- compact parameter

Mysqldump-uroot-poldboy123-S / data/3306/mysql.sock-B oldboy-- compact > / opt/oldboy_$ (date +% F). Sql egrep-v'# |\ * |-- | ^ $'/ opt/oldboy_2016-08-28.sql

-- compact parameter description:

Note:-- compact this parameter is not used in the production environment and can be used for debugging.

(6) example 6: specify the compression command to compress the backed-up MySQL data

Use the gzip command to compress the database data when backing up to save disk space

Mysqldump-uroot-poldboy123-S / data/3306/mysql.sock-B oldboy | > / opt/oldboy_$ (date +% F) .sql. [root@db01 opt] # ll / opt/total 24Mustang-1root root 2700 Aug28 23:25 oldboy_2016-08-28.sql-28.sql-Aug 29 01:55 oldboy_2016-08-29.sql.gzl.gzMelr-1root root 2843 Aug28 23:54 oldboy_B_2016-08-

Summary of the above examples:

1. Backup data uses the-B parameter, which will add statements to build database and use library in the backup data.

two。 Backup data uses the-B parameter so that multiple library names can be followed directly.

3. Backup data use gzip to compress the backup data, which can save disk space.

4. When debug, you can use-compact to reduce output, but not for production.

5. Specify character set backup-default-character-set= character set (generally not applicable)

2.1 how mysqldump works

The process of backing up data using the mysqldump command is actually the process of directly outputting the data from the mysql library in the form of logical sql statements or generating backup files.

Can you give an example and make use of the principle of restoring backup files?

The backup data is filtered out with comments, which are all SQL statements. The result is as follows:

[root@db01 opt] # egrep-v'# |\ * | ^ $'/ opt/oldboy_2016-08-28.sqlDROP TABLE IF EXISTS `student`; CREATE TABLE `student` (`id`int (4) NOT NULL AUTO_INCREMENT, `name`char (20) NOT NULL, `age`tinyint (2) NOT NULL DEFAULT '0students, `dept`varchar (16) DEFAULT NULL, PRIMARY KEY (`id`), KEY`index _ name_ dept` (`name`, `dept`), KEY`index _ dept` (`name`, `dept`) ENGINE=InnoDB DEFAULT CHARSET=utf8;LOCK TABLES `student` DROP TABLE IF EXISTS `test`; CREATE TABLE `test` (`id` int (4) NOT NULL AUTO_INCREMENT, `name`char (20) NOT NULL, PRIMARY KEY (`id`), KEY`index _ name` (`name`) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULTCHARSET=utf8;LOCK TABLES `test` WRITE;INSERT INTO `test`test` VALUES (3Makehe`), (5Makekaka'), (1penceoldboy'), (2Zuma'), (4Zuma'). Database backup practice 3.1 backing up multiple libraries

1. Check how many libraries there are currently

Method 2: using command splicing to realize sub-library backup.

[root@db01 ~] # mkdir / server/backup [root@db01 ~] # [root@db01 ~] # ls / server/backup/ 3ooo_2016-08-29.sql.gz oldboy_2016-08-29.sql.gz oldboy_gbk_2016-08-29.sql.gz3.3 backup single table and sub-table backup

Single-label backup command syntax: mysqldump-u user name-p password database name table name > backup file name

(1) back up a single table: the following backup test table of oldboy database as an example

1. Check which tables are in the oldboy database before backup

two。 Start backing up individual table (test) tables

[root@db01 ~] # [root@db01 ~] # egrep-v'# |\ * |-- | ^ $'/ opt/tables.sql DROP TABLE IF EXISTS `student` CREATE TABLE `student` (`id` int (4) NOT NULL AUTO_INCREMENT, `name`char (20) NOT NULL, `age`tinyint (2) NOT NULL DEFAULT '0percent, `dept`varchar (16) DEFAULT NULL, PRIMARY KEY (`id`), KEY`index _ name` (`name`), KEY`index _ name_ dept` (`name`, `dept`), KEY`index _ 123` (`name`, `dept`) ENGINE=InnoDB DEFAULT CHARSET=utf8;LOCK TABLES `student`

(2) A command to back up the table:

Database sub-table backup is actually a backup, but when one command is troublesome, you can use one command to back up multiple database tables, which is more concise. Use one command to back up student and test tables as follows:

[root@db01] # mysql-uroot-poldboy123-S/data/3306/mysql.sock-e "show tables from oldboy "# / opt/student_tables.sqlmysqldump-uroot-poldboy123-S/data/3306/mysql.sock oldboy test > / opt/test_tables.sql [root@db01 ~] # [root@db01 ~] # ll-rt / opt/ | tail-2-rw-r--r-- 1root root 2010 Aug 29 12:18 student_tables.sql-rw-r--r-- 1root root 1941 Aug 29 12:18 test_tables.sql

Disadvantages of split-table backup

1. By a complete full, in doing a sub-library sub-table backup.

two。 Script batch recovery of SQL files

Interview questions: how to restore a single library or table when multiple libraries or tables are available together?

Answer:

a. The third-party test library, import it into the library, then back up what you need, and finally restore it to the official library.

b. Single table: grep table name bak.sql > table name .sql

Single library: loop filter all tables in the library, grep table name bak.ssql > table name .sql (multiple table data)

c. Achieve sub-database sub-table backup.

3.4 back up multiple tables

The following backs up multiple tables of the oldboy database

1. To view the tables in the oldboy database, the command is as follows:

Mysql-uroot-poldboy123-S / data/3306/mysql.sock-e "showtables from oldboy;"

two。 Back up the student and test tables in the oldboy database with the following command

Mysqldump-uroot-poldboy123-S / data/3306/mysql.sock > / opt/tables.sql

Note: when backing up multiple tables, you can specify multiple tables behind the oldboy database without adding-B parameters!

3.5 backup database table structure (no data)

Command syntax: mysqldump-u user-p password database name table name-d > backup file name

Backup the table structure in the database by using the value of mysqldump-d parameter

(1) back up the structure of all tables in the oldboy library:

Output and check before backup

[root@db01 3306] # mysqldump-uroot-poldboy123-S/data/3306/mysql.sock oldboytest-d-compact, / *! 40101 SET @ saved_cs_client = @ @ character_set_client * /; / *! 40101 SET character_set_client = utf8 * /; CREATE TABLE `test` (`id`index (4) NOT NULL AUTO_INCREMENT, `name`char (20) NOT NULL, PRIMARY KEY (`id`), KEY`index _ name` (`name`) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULTCHARSET=utf8 / * 40101 SET character_set_client = @ saved_cs_client * /

2.. When the above output is correct, output it to the backup file for backup (backup all table results), the command is as follows

Mysqldump-uroot-poldboy123-S/data/3306/mysql.sock oldboy-d > / opt/bak_oldboy.sql3.6 back up the data in the table

Command syntax: mysqldump-u user-p password database name table name-t > backup file name

1. Output before backing up the test table in the oldboy library to see if the data is desired

[root@db01 3306] # mysqldump-uroot-poldboy123-S/data/3306/mysql.sock oldboy test-t-compactINSERT INTO `test`Test` VALUES (3 recordings hehe`), (5 recordings kaka`), (1 recordings oldboy'), (2 recordings oldboys'), (4 recordings Zuma')

two。 The above results start the backup correctly

Mysqldump-uroot-poldboy123-S/data/3306/mysql.sock oldboy test > / opt/bak_test.sql

Table structure and data separation backed up using the-- tab parameter

[root@db01 3306] # mysqldump-uroot-poldboy123-S / data/3306/mysql.sock oldboy test-- compact [root@db01 3306] # ls / tmp/a.txt b.txt

Note: after the above separation, the data table and the sql statement are backed up separately, one is the text and the other is the sql statement

3.7 Summary

-B back up multiple libraries (and add statements for create and use libraries).

-d value backs up the database table structure.

-t only backs up data (in the form of sql statements).

-T separates the database table and data into different files, the data is text, non-SQL statements.

3.8 parameters for refreshing binlog

What is binlog? Is a SQL statement that records database updates. Binary file

Mysqldump is used to regularly complete the data at a certain time, for example, bak.sql.gz at 00:00

Incremental backup, when data is written to the database, updated SQL statements are also written to the corresponding file. This file is called the binlog file.

For example, if you lose data at 10:00, you need to recover the data:

1. The bak.sql.gz data backed up at 00:00 is restored to the database, when the data is restored to 00:00.

2. From 00:00 to 10, the data will be recovered from binlog.

One parameter is required for the binglog file to take effect: log-bin

Personal understanding: we usually back up the database regularly instead of in real time. Suppose our database is backed up at 00:00 in the evening, and when my database malfunctions at 8 o'clock in the morning, how do we get back up the data between 00:00 and 8 o'clock in the morning? Then we have to go through the binlog file, and the binlog file will be cut, but we don't need to back up on the tile before 00:00, so we have to brush the binlog file and regenerate it when backing up, that is to say, the regenerated binlog is between 00 and 8:00, and we need to rely on the binlog in between to restore the data.

Backup refresh binlog is binlog regeneration requires the use of the-F parameter to generate a new binlog file

The following tests:

[root@db01 3306] # ll / data/3306/-rw-rw---- 1 mysqlmysql 2547 Aug 28 15:11 mysql-bin.000001-rw-rw---- 1 mysqlmysql 6282 Aug 29 01:15 mysql-bin.000002 [root@db01 3306] # mysqldump-uroot-poldboy123-S / data/3306/mysql.sock oldboy test > / opt/bak_test.sql [root@db01 3306 ] # ll / data/3306/-rw-rw---- 1 mysqlmysql 2547 Aug 28 15:11 mysql-bin.000001-rw-rw---- 1 mysqlmysql 6282 Aug 29 01:15 mysql-bin.000002-rw-rw---- 1 mysqlmysql 150 Aug 29 15:47 mysql-bin.000003

Binlog log slicing to determine the tipping point for full and incremental backups.

-F refresh the binlog log, generate a new file, and start incremental recovery from this file in the future

-- master-data parameter description:

-- master-data will add CHANGEMASTER statement, binlog file and location information to the backup file.

1. Back up the sample with the-master-data parameter, with a value of 1: executable CHANGEMASTER statement

Mysqldump-uroot-poldboy123-S/data/3306/mysql.sock oldboy test > / opt/bak_test.sql # / opt/alL__$ (date +% F). Sql.sql.gz

#-F may not be used, and there is some repetition with-master-data

Innodb engine enterprise production backup command (recommended)

Mysqldump-uroot-poldboy123-A-B-F-R--master-data=2-x-- events-| gzip > / opt/all.sql.gz

#-F may not be used, and there is some repetition with-master-data

Additional addition:

(1) mysqldump is the description of logical backup

Disadvantages: the efficiency is not very high.

Advantages: simple and convenient. Reliable, migrating.

It is suitable for scenarios where the quantity is not very large, and the data is less than 50 GB in the first 50 GB of packaging.

(2) if it exceeds 50g, the options are as follows:

1. Xtrabackup physical backup tool, complete and incremental.

two。 Physical backup scheme, stop the SQL thread from the library, package, cp.

(3) when will the backed-up data be used?

1. When restoring data to the test library.

two。 When the data is deleted artificially through the SQL statement.

3. When it comes to master and slave replication.

5. Practice of restoring database

5.1 Database recovery matters

Tip:

1. Data recovery is closely related to the character set. If the character set is incorrect, the recovered data is garbled.

2. The principle of mysql command and source command to restore the database is the process of re-executing the SQL statement of the file in the database.

5.2 restore the database by using the source command

Mysql > use database

Then use the source command, followed by a script file (such as the sql used here).

The file mysql > source oldboy_db.sql # is the system path, and the default is the system path before logging in to mysql.

The source practice is as follows:

5.3 restore with mysql command (standard)

Command syntax:

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.