How to use mysqldump
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!
