mysqldump如何用
发布时间：2021-12-27 06:17:09 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍了mysqldump怎么用，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 一、创建表并导入数据 [root@node1 ~]# mysql -u test -p Enter password: Welcome to the MySQL mon
|
这篇文章主要介绍了mysqldump怎么用，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 一、创建表并导入数据 [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,'jack'); Query OK, 1 row affected (0.02 sec) mysql> insert into test1 values (2,'mike'); Query OK, 1 row affected (0.00 sec) mysql> insert into test1 values (2,'joe'); 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)  二、导出表到文件中 [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]# 三、删除数据库中的表 mysql> drop table test1; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> mysql> show tables -> ; Empty set (0.00 sec) 四、将表导入到数据库中 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) 六，使用master data参数可以得到日志的偏移位置，以便数据库迁移的时候恢复 [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]# 感谢你能够认真阅读完这篇文章，希望小编分享的“mysqldump怎么用”这篇文章对大家有帮助。 （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
