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

This article is about how to use mysqldump to back up and restore mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Mysqldump is a logical backup and recovery tool for mysql, which can back up only a table, a library, or an entire database. In order to ensure the consistency of the data, a write lock will be added during the backup, so it must be used when the system is idle.

Syntax:

[root@D2-LZY245 ~] # mysqldump

Usage: mysqldump [OPTIONS] database [tables]

OR mysqldump [OPTIONS]-- databases [OPTIONS] DB1 [DB2 DB3...]

OR mysqldump [OPTIONS]-- all-databases [OPTIONS]

For more options, use mysqldump-help

Back up the test1 library:

[root@D2-LZY245] # mysqldump-uroot-p123456 test1 > test1_bk.sql

View the generated backup file:

[root@D2-LZY245] # mysqldump-uroot-p123456 test1 > test1_bk.sql

Mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@D2-LZY245 ~] # cat test1_bk.sql

-- MySQL dump 10.13 Distrib 5.7.15, for linux-glibc2.5 (x86 / 64)

--

-- Host: localhost Database: test1

-

-- Server version 5.7.15

/ * 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 * /

--

-- Table structure for table `emp`

--

DROP TABLE IF EXISTS `emp`

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

/ *! 40101 SET character_set_client = utf8 * /

CREATE TABLE `emp` (

`employee_ id` int (10) DEFAULT NULL

`first_ name` varchar (50) DEFAULT NULL

`last_ name` varchar (50) DEFAULT NULL

`salary` decimal (10pm 2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/ * 40101 SET character_set_client = @ saved_cs_client * /

--

-- Dumping data for table `emp`

--

LOCK TABLES `emp` WRITE

/ *! 40000 ALTER TABLE `emp` DISABLE KEYS * /

INSERT INTO `emp` VALUES, (101pime), (101pime), (102pr), (102pl), (102pr), (103pas), (104pas), (105pas), (4800.00), (106m), (4800.00), (4800.00), (4800.00), (4800.00), (4800.00), (4800.00), (4800.00) 4200.00), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (109)

/ *! 40000 ALTER TABLE `emp` ENABLE KEYS * /

UNLOCK TABLES

--

-- Table structure for table `emp1`

--

DROP TABLE IF EXISTS `emp1`

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

/ *! 40101 SET character_set_client = utf8 * /

CREATE TABLE `emp1` (

`employee_ id` int (10) DEFAULT NULL

`first_ name` varchar (50) DEFAULT NULL

`last_ name` varchar (50) DEFAULT NULL

`salary` decimal (10pm 2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/ * 40101 SET character_set_client = @ saved_cs_client * /

--

-- Dumping data for table `emp1`

--

LOCK TABLES `emp1` WRITE

/ *! 40000 ALTER TABLE `emp1` DISABLE KEYS * /

INSERT INTO `emp1` VALUES (100penceSteventry pencils 24000.00), (101pyrrine Neenaforme pr é cors Kochhards pr é cor 17000.00), (102pr é minals de Haan',17000.00), (103pr Haan',17000.00 pr 9000.00), (104p pas pr é cr é es 6000.00), (105pm pas Davidres pr é cors 4800.00), (106J Valliaballakes pr é cs 4800.00), (107pr Dianaqu pr orentz' 4200.00), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (108), (109)

/ *! 40000 ALTER TABLE `emp1` 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 * /

-- Dump completed on 2017-05-26 18:32:51

Backup files are actually SQL scripts that first set parameters, delete tables if they exist, then create tables, lock tables, execute insert statements, and unlock them. Just execute the SQL script when you restore it. There are two ways to execute a script, one is to redirect, and the other is to use source.

Redirect method:

Mysql > create database test2

Query OK, 1 row affected (0.00 sec)

# create a database test2 and restore to test2.

[root@D2-LZY245] # mysql-uroot-p123456 test2

Use test2

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > show tables

+-+

| | Tables_in_test2 |

+-+

| | emp |

| | emp1 |

+-+

2 rows in set (0.00 sec)

Source mode:

Mysql > create database test3

Query OK, 1 row affected (0.00 sec)

Mysql > use test3

Database changed

Mysql > source / root/test1_bk.sql

Mysql > show tables

+-+

| | Tables_in_test3 |

+-+

| | emp |

| | emp1 |

+-+

2 rows in set (0.00 sec)

There is a difference between the two ways, using the redirect method, if there is an error, it will exit directly, and the subsequent statement will not be executed, while the source method will skip the error and continue to execute the subsequent statement.

Thank you for reading! This is the end of the article on "how to use mysqldump to back up and restore mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.