Linux : mysql数据导出导入

最新推荐文章于 2022-04-18 20:52:43 发布

大鹏blog

最新推荐文章于 2022-04-18 20:52:43 发布

阅读量445

点赞数

分类专栏： go语言编程

本文链接：https://blog.csdn.net/textdemo123/article/details/103121109

版权

go语言编程专栏收录该内容

55 篇文章

订阅专栏

背景：
有时候数据库迁移，会涉及到mysql数据的导入导出，我们可以使用mysqldump 将数据导出成sql文本，之后导入新数据库执行即可。
操作：
1.旧数据导出
mysqldump -u root -p 123456 > abc.sql

root@1e19afd2305d:/data# mysqldump  service     -u  root -p  > abc.sql ;
Enter password: 
root@1e19afd2305d:/data# ls
abc.sql
root@1e19afd2305d:/data# cat abc.sql 
-- MySQL dump 10.13  Distrib 5.6.41, for Linux (x86_64)
--
-- Host: localhost    Database: service
-- ------------------------------------------------------
-- Server version	5.6.41

/*!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 `service_info`
--

DROP TABLE IF EXISTS `service_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `service_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `url` varchar(128) DEFAULT NULL,
  `username` varchar(32) DEFAULT NULL,
  `password` varchar(128) DEFAULT NULL,
  `description` text NOT NULL,
  `type` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=69 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `service_info`
--

LOCK TABLES `service_info` WRITE;
/*!40000 ALTER TABLE `service_info` DISABLE KEYS */;
INSERT INTO `service_info` VALUES (22,'cfss_gateway_service','http://172.168.200.39:9999/','chenpengf','123456','cfss  gate  service',1),(23,'k8s_caas_portal ','http://172.168.200.48:4200/','admin','admin','k8s caas portal service',1),(25,'k8s-acrm','http://172.168.200.45:32079/acrm/','admin','000000','k8s acrm  service',1),(26,'nexus','http://172.168.200.37:8081','admin','admin123','jar,rpm,yarn... repository ',0),(27,'k8s  id service','http://172.168.200.45:32515/','admin','000000','cfss id service',1),(38,'kubeapps','http://172.168.200.45:30080','','','Application store',1),(41,'harbor_37','http://172.168.200.37:10000','admin','Harbor12345','docker image  repository ',0),(42,'harbor_50','http://172.168.200.50','admin','Harbor12345','docker image  repository ',1),(43,'istio-kiali','http://172.168.200.45:30201','admin','admin','show istio service',1),(44,'istio-grafana','http://172.168.200.45:30292','admin','admin123','istio service monitoring',1),(45,'istio-jaeger','http://172.168.200.45:30280/jaeger/search',' ','','istio service trace',1),(46,'k8s-kibana','http://172.168.200.45:30205/app/kibana',' ','','logging service',1),(47,'k8s-ccrm','http://172.168.200.45:32238/ccrm',' admin','000000','cfss ccrm service',1),(48,'k8s-dcms','http://172.168.200.45:32251/dcms',' admin','000000','cfss dcsm service',1),(49,'k8s-ischd','http://172.168.200.45:31522/ ',' admin','000000','cfss ischd service',1),(50,'k8s-breeze','http://172.168.200.48:88/#/','',' ','offline  deploy  kubernetes',1),(51,'zentao','http://172.168.200.37:9998/','admin','admin123','issue manage service',0),(52,'swarm-sonarqube','http://172.168.200.38:9988/','admin','admin','code  quality',0),(53,'Gitlab','http://172.168.200.37:9999',' ','','code registry',0),(54,'mesos-dcos','http://172.168.200.72',' ','','mesos  dcos',2),(55,'mesos-marathon','http://172.168.200.72:8080',' ','','mesos  marathon',2),(56,'mesos','http://172.168.200.72:5050',' ','','mesos',2),(57,'mesos-marathon-lb','http://172.168.200.78:9090/haproxy?stats',' ','','mesos  marathon  lb',2),(58,'mesos-es','http://172.168.200.121:10109/_cat/indices?v',' ','','mesos  elastic search service',2),(59,'mesos-kibanna','http://172.168.200.121:10110',' ','','mesos  looging service',2),(60,'mesos-granfana','http://172.168.200.121:10108  ',' ','','mesos  monitoring service',2),(61,'mesos-altermanager','http://172.168.200.121:10107',' ','','mesos  monitoring service',2),(62,'mesos-prometheus','http://172.168.200.121:10106',' ','','mesos  monitoring service',2),(63,'mesos-Jenkins','http://172.168.200.121:10103',' ','','mesos  jenkins service',2),(64,'mesos-konga','http://172.168.200.121:10111',' cfss','admincfss','mesos  konga service',2),(65,'mesos-acrm','http://172.168.200.121:10301/acrm',' admin','000000','mesos  acrm service',2),(66,'mesos-ccrm','http://172.168.200.121:10301/ccrm',' admin','000000','mesos  ccrm service',2),(67,'mesos-dcms','http://172.168.200.121:10301/dcms',' admin','000000','mesos  dcms service',2),(68,'mesos-ocrm','http://172.168.200.121:10301/ocrm',' admin','000000','mesos  ocrm service',2);
/*!40000 ALTER TABLE `service_info` 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 2019-11-18  5:48:25

2.新数据库，数据的导入
mysql -u root -p -D service<abc.sql
-D 是指定数据库

root@8738406079ce:/data# mysql -u root  -p  -D service<abc.sql 
Enter password: 
root@8738406079ce:/data# 
root@8738406079ce:/data# 
root@8738406079ce:/data# 
root@8738406079ce:/data# 
root@8738406079ce:/data# 
root@8738406079ce:/data# mysql -u root  -p                     
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 95029
Server version: 5.6.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> show databases ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| service            |
| wayne              |
+--------------------+
5 rows in set (0.01 sec)

mysql> use service ;
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_service |
+-------------------+
| service_info      |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from  service_info
    -> ;
+----+----------------------+---------------------------------------------+-----------+-------------+-------------------------------+------+
| id | name                 | url                                         | username  | password    | description                   | type |

34 rows in set (0.00 sec)

我们看到数据表已经导入