Linux : mysql数据导出导入

75 阅读 0 评论 50 点赞

我是靠谱客的博主怡然白羊，最近开发中收集的这篇文章主要介绍Linux : mysql数据导出导入，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

背景：
有时候数据库迁移，会涉及到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)