MySQL database introduction: detailed explanation of database backup operation

1. Single database backup
2. Compressed backup
3. Multi-database backup
4. Single table backup

This article uses an example to explain the operation of backing up the database in the MySQL database. Share with you for your reference, the details are as follows:

Continuing from the last time: Getting started with MySQL database multi-instance configuration

When it comes to data, everyone will be very nervous. There are many types of data, but in general, data is very important. Therefore, daily data backup work has become the focus of the focus of operation and maintenance work.................

First, let's take a look at the data in the database

mysql> select * from test;
+-----+------+
| id | name |
+-----+------+
| 1 | 1 |
| 11 | text |
| 21 | abc |
| 9 | bcd |
| 111 | 1 |
| 441 | text |
| 41 | abc |
| 999 | bcd |
+-----+------+
8 rows in set (0.00 sec)

1. Single database backup

[root@centos6 ~]# mysqldump -uroot -p test >/download/testbak_$(date +%F).sql
Enter password: 

[root@centos6 ~]# ll /download/
total 2
-rw-r--r--. 1 root root 1888 Dec 12 20:34 testbak_2016-12-12.sql

Let's take a look at what this backup file contains.

[root@centos6 ~]# egrep -v "^--|\*|^$" 

/download/testbak_2016-12-12.sql

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (

 `id` int(4) NOT NULL,

 `name` char(20) NOT NULL

)ENGINE=MyISAM DEFAULT CHARSET=latin1;

LOCK TABLES `test` WRITE;

INSERT INTO `test` VALUES (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd');

UNLOCK TABLES;

From the above file content, we can see that the actual process of this backup is to back up the SQL statements for creating databases, building tables, and inserting data. It can also be said that the SQL statements are exported.

-B parameter

[root@centos6 ~]# mysqldump -uroot -p -B test >/download/testbak_$(date +%F)_b.sql

Enter password: 

[root@centos6 ~]# egrep -v "^--|^$" /download/testbak_2016-12-12_b.sql  

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `test`;

DROP TABLE IF EXISTS `test`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `test` (

 `id` int(4) NOT NULL,

 `name` char(20) NOT NULL

)ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES `test` WRITE;

/*!40000 ALTER TABLE `test` DISABLE KEYS */;

INSERT INTO `test` VALUES (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd');

/*!40000 ALTER TABLE `test` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

The function of the -B parameter is clear at a glance. When our database is lost, we can directly use this backup file to restore it without having to rebuild the database and table and then perform data recovery operations.

2. Compressed backup

Sometimes, the database data is large and may need to be compressed for backup to save backup time and disk space.

[root@centos6 ~]# mysqldump -uroot -p -B test|gzip >/download/testbak_$(date +%F).sql.gz
Enter password: 

[root@centos6 ~]# ll /download/testbak_2016-12-12.sql.gz
-rw-r--r--. 1 root root 753 Dec 12 20:49 /download/testbak_2016-12-12.sql.gz

[root@centos6 ~]# ll /download/
total 14
-rw-r--r--. 1 root root 2027 Dec 12 20:41 testbak_2016-12-12_b.sql
-rw-r--r--. 1 root root 1888 Dec 12 20:34 testbak_2016-12-12.sql
-rw-r--r--. 1 root root 753 Dec 12 20:49 testbak_2016-12-12.sql.gz

You can also see the compressed effect

3. Multi-database backup

[root@centos6 ~]# mysqldump -uroot -p -B test mysql|gzip >/download/testbak_$(date +%F).sql01.gz
Enter password: 
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

[root@centos6 ~]# ll /download/testbak_2016-12-12.sql01.gz 
-rw-r--r--. 1 root root 152696 Dec 12 20:52 /download/testbak_2016-12-12.sql01.gz

There is a warning message here, which can be ignored or parameters can be added during backup. The backup statement is as follows

[root@centos6 ~]# mysqldump -uroot -p -B --events test mysql|gzip >/download/testbak_$(date +%F).sql02.gz
Enter password: 
[root@centos6 ~]# ll /download/testbak_2016-12-12.sql02.gz                 
-rw-r--r--. 1 root root 152749 Dec 12 20:54 /download/testbak_2016-12-12.sql02.gz

This way there will be no warning message.

However, this method of backing up multiple databases together will cause a problem. If only one of the databases has a problem, it is difficult to restore the single database. Therefore, this backup method is not commonly used and does not meet actual needs. Therefore, multiple single database backup operations are required when backing up multiple databases.

[root@centos6 ~]# mysqldump -uroot -p -B test|gzip >/download/testbackup_$(date +%F).sql.gz         
Enter password: 

[root@centos6 ~]# mysqldump -uroot -p -B --events mysql|gzip >/download/mysqlbak_$(date +%F).sql.gz      
Enter password: 

[root@centos6 ~]# ll /download/
total 80
-rw-r--r--. 1 root root 152608 Dec 12 20:58 mysqlbak_2016-12-12.sql.gz
-rw-r--r--. 1 root root 754 Dec 12 20:58 testbackup_2016-12-12.sql.gz
-rw-r--r--. 1 root root 2027 Dec 12 20:41 testbak_2016-12-12_b.sql
-rw-r--r--. 1 root root 1888 Dec 12 20:34 testbak_2016-12-12.sql
-rw-r--r--. 1 root root 152696 Dec 12 20:52 testbak_2016-12-12.sql01.gz
-rw-r--r--. 1 root root 152749 Dec 12 20:54 testbak_2016-12-12.sql02.gz
-rw-r--r--. 1 root root 753 Dec 12 20:49 testbak_2016-12-12.sql.gz

4. Single table backup

The purpose of backup in separate databases is to facilitate operation when restoring the database, but it also faces the problem that if a table in a database is damaged, but the whole database cannot be restored, the backup of separate databases and tables is often used in actual production. In this way, the data is also backed up and it is easy to operate during recovery.

[root@centos6 ~]# mysqldump -uroot -p -B test test >/download/test_testbak_$(date +%F).sql   

Enter password: 

[root@centos6 ~]# egrep -v "#|^$|\*" /download/test_testbak_2016-12-12.sql

-- MySQL dump 10.13 Distrib 5.5.52, for linux2.6 (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
--Server version 5.5.53-log
--
-- Current Database: `test`
--
USE `test`;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
 `id` int(4) NOT NULL,
 `name` char(20) NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `test`
--
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd');
UNLOCK TABLES;
--
-- Current Database: `test`
--
USE `test`;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
 `id` int(4) NOT NULL,
 `name` char(20) NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `test`
--
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd');
UNLOCK TABLES;
-- Dump completed on 2016-12-12 21:13:16

Therefore, the backup of sharded tables is the same as the backup of sharded libraries. You only need to perform multiple single-table backup operations. However, some friends will definitely ask questions. If there are thousands or tens of thousands of tables in a library, how long will it take to prepare such a backup? ? ? ? For backups of large amounts of data, you can use professional backup tools. For backups of small amounts of data or tables, you can write the backup operation into a script and include it in a scheduled task. You only need to check whether the backup is successful.

Share a simple backup script in the actual production environment for reference only.

[root@centos6 scripts]# vi bak.sh 
#!/bin/sh
##########################################
#this scripts created by root of mingongge
#created on 2016-11-11
#######################################

ip=`grep 'IPADDR' /etc/sysconfig/network-scripts/ifcfg-eth0|awk -F "=" '{print $2}'`
#Define server IP variable BAKDIR=/backup 
#Define backup path [ ! -d $BAKDIR/${ip} ] && mkdir -p $BAKDIR/${ip}
 #If the path does not exist, create one. This is for convenience when there are many servers. DB_PWD="mingongge"
DB_USER="root"
MYSQL="/application/mysql/bin/mysql"
MYSQL_DUMP="/application/mysql/bin/mysqldump"
DATA=`date +%F`

####back data of test's databses####

DB_NAME=`$MYSQL -u$DB_USER -p$DB_PWD -e "show databases;"|sed '1,5d'`
 #Define database variable for name in $DB_NAME
#For loop statement takes library name do
 $MYSQL_DUMP -u$DB_USER -p$DB_PWD -B ${name} |gzip >$BAKDIR/${ip}/${name}_$DATA.sql.gz 
 #Full database backup [ ! -d $BAKDIR/${ip}/${name} ] && mkdir -p $BAKDIR/${ip}/${name}
#Judge this path to distinguish which database's backup file for tablename in `$MYSQL -u$DB_USER -p$DB_PWD -e "show tables from ${name};"|sed '1d'`
#For loop statement takes table name do
  $MYSQL_DUMP -u$DB_USER -p$DB_PWD ${name} ${tablename} |gzip >$BAKDIR/${ip}/${name}/${tablename}_$DATA.sql.gz
#Table backup done
done

The results of the execution are as follows

[root@ranzhioa ~]# tree /backup/
/backup/
10.1xx.1xx.1xx #Server IP
 xxxxxxx #Actually the database name is cash_balance_2016-12-15.sql.gz
cash_depositor_2016-12-15.sql.gz
cash_trade_2016-12-15.sql.gz
crm_customer_2016-12-15.sql.gz
crm_delivery_2016-12-15.sql.gz
crm_order_2016-12-15.sql.gz
crm_orderAction_2016-12-15.sql.gz
crm_orderField_2016-12-15.sql.gz
crm_plan_2016-12-15.sql.gz

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:

Detailed explanation of real-time backup knowledge points of MySQL database
Detailed explanation of three ways to backup mysql
Several ways to backup MySql database
Summary of various implementation methods of mysql database backup
MySQL learning database backup detailed explanation

<<: Understanding and application scenarios of enumeration types in TypeScript

>>: The vue project realizes drawing a watermark in a certain area

MySQL database introduction: detailed explanation of database backup operation

Table of contents

1. Single database backup

2. Compressed backup

3. Multi-database backup

4. Single table backup

Share a simple backup script in the actual production environment for reference only.

jQuery realizes the sliding effect of drop-down menu

vue front-end HbuliderEslint real-time verification automatic repair settings

Detailed explanation of Truncate usage in MySQL

MySQL5.6.31 winx64.zip installation and configuration tutorial

Example code of vue icon selector

How to solve the mysql error 1033 Incorrect information in file: 'xxx.frm'

Summary of common functions of PostgreSQL regular expressions

In-depth explanation of binlog in MySQL 8.0

js array entries() Get iteration method

Top 10 Time-Saving Tips to Shorten Web App Development (Graphical Tutorial)

Recommend

CSS3 realizes draggable Rubik's Cube 3D effect

Solve the problem of resetting the Mysql root user account password

How to safely shut down a MySQL instance

Implementing parameter jump function in Vue project

Tutorial on how to install and configure the unzipped version of MySql under Windows 10

Prometheus monitors MySQL using grafana display

How to implement Nginx reverse proxy for multiple servers

The process of JDK installation and configuration of environment variables under WIN10 (detailed version)

Introduction to document.activeELement focus element in JavaScript

MySQL 8.0.25 installation and configuration tutorial under Linux

Let's talk in detail about the props attributes of components in Vue

How to create a new user in CentOS and enable key login

18 Nginx proxy cache configuration tips that operators must know (which ones do you know?)

HTML head tag detailed introduction

Example code for implementing raindrop animation effect with CSS