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

This article mainly introduces how to use mysqldump, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Create tables and import data

[root@node1] # mysql-u test-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 6

Server version: 5.7.11 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql >

Mysql >

Mysql >

Mysql >

Mysql > use testdb

Database changed

Mysql >

Mysql >

Mysql > create table test1 (id int (10), name varchar (10))

Query OK, 0 rows affected (0.03 sec)

Mysql >

Mysql >

Mysql >

Mysql >

Mysql >

Mysql > insert into test1 values (1)

Query OK, 1 row affected (0.02 sec)

Mysql > insert into test1 values (2)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into test1 values (2)

Query OK, 1 row affected (0.00 sec)

Mysql >

Mysql > select * from test1

+-+ +

| | id | name |

+-+ +

| | 1 | jack |

| | 2 | mike |

| | 2 | joe |

+-+ +

3 rows in set (0.00 sec)

Export the table to the file

[root@node1 testdb] # mysqldump-u test-p testdb test1 > test1.sql

Enter password:

[root@node1 testdb] # ls

Db.opt test1.dmp test1.frm test1.ibd test1.sql

[root@node1 testdb] # ls-l

Total 124

-rw-r- 1 mysql mysql 65 Feb 18 15:55 db.opt

-rw-r--r-- 1 root root 1846 Feb 23 09:37 test1.dmp

-rw-r- 1 mysql mysql 8586 Feb 23 09:35 test1.frm

-rw-r- 1 mysql mysql 98304 Feb 23 09:36 test1.ibd

-rw-r--r-- 1 root root 1846 Feb 23 09:38 test1.sql

[root@node1 testdb] # pwd

/ var/lib/mysql/testdb

[root@node1 testdb] #

Delete tables in the database

Mysql > drop table test1

Query OK, 0 rows affected (0.00 sec)

Mysql >

Mysql >

Mysql >

Mysql > show tables

->

Empty set (0.00 sec)

Import the table into the database

Mysql > source / var/lib/mysql/testdb/test1.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

5. View tables and data normally

Mysql > show tables

->

+-+

| | Tables_in_testdb |

+-+

| | test1 |

+-+

1 row in set (0.00 sec)

Mysql > select * from test1

+-+ +

| | id | name |

+-+ +

| | 1 | jack |

| | 2 | mike |

| | 2 | joe |

+-+ +

3 rows in set (0.00 sec)

Mysql >

Sixth, use the master data parameter to get the offset of the log, so that the database can be restored when the database is migrated.

[root@node1 backup] # mysqldump-u root-p-master-data=2-- databases testdb > test2.sql

Enter password:

[root@node1 backup] # ll

Total 4

-rw-r--r-- 1 root root 2053 Apr 22 05:28 test2.sql

[root@node1 backup] # more test2.sql

-- MySQL dump 10.13 Distrib 5.7.11, for Linux (i686)

--

-- Host: localhost Database: testdb

-

-- Server version 5.7.11-log

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

--

-- Position to start replication or point-in-time recovery from

--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=2198

--

-- Current Database: `testdb`

--

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

USE `testdb`

--

-- Table structure for table `t`

--

DROP TABLE IF EXISTS `t`

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

/ *! 40101 SET character_set_client = utf8 * /

CREATE TABLE `t` (

`id`int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/ * 40101 SET character_set_client = @ saved_cs_client * /

--

-- Dumping data for table `t`

--

LOCK TABLES `t` WRITE

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

INSERT INTO `t` VALUES (1), (2)

/ *! 40000 ALTER TABLE `t` 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 2016-04-22 5:28:41

[root@node1 backup] #

Thank you for reading this article carefully. I hope the article "how to use mysqldump" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.