登录
首页 >  数据库 >  MySQL

总结mysql数据导库的常用操作有哪些?

来源:SegmentFault

时间:2023-01-22 08:11:01 440浏览 收藏

你在学习数据库相关的知识吗?本文《总结mysql数据导库的常用操作有哪些?》,主要介绍的内容就涉及到MySQL,如果你想提升自己的开发能力,就不要错过这篇文章,大家要知道编程理论基础和实战操作都是不可或缺的哦!

工作中经常遇到:一个数据库导入新的数据库实例中,或者一个数据库中的某些表导入新的数据库中,常用操作,总结一下。

部分数据表导入新库

  • 单表导入新库的sql为

  • 多表导入新库(将所有的stability_* 导入新库)
    多表的时候,只需选出需要的表即可。

# 拼接处所有的导出sql
mysql> select concat ('create table `dashboard`.',table_name,'(select * from `dashboard_stb`.',table_name,');') from information_schema.tables where table_name like "stability_%";
+--------------------------------------------------------------------------------------------------------+
| concat ('create table `dashboard`.',table_name,'(select * from `dashboard_stb`.',table_name,');')      |
+--------------------------------------------------------------------------------------------------------+
| create table `dashboard`.stability_capacity(select * from `dashboard_stb`.stability_capacity);         |
| create table `dashboard`.stability_dailymaxflow(select * from `dashboard_stb`.stability_dailymaxflow); |
| create table `dashboard`.stability_indextype(select * from `dashboard_stb`.stability_indextype);       |
| create table `dashboard`.stability_ktraceagent(select * from `dashboard_stb`.stability_ktraceagent);   
# 此处省略N行
+--------------------------------------------------------------------------------------------------------+

粘贴进去直接运行即可

mysql>  create table `dashboard`.stability_maccpu(select * from `dashboard_stb`.stability_maccpu);
Query OK, 138138 rows affected (2.16 sec)
Records: 138138  Duplicates: 0  Warnings: 0

mysql>  create table `dashboard`.stability_macmem(select * from `dashboard_stb`.stability_macmem);
Query OK, 138137 rows affected (2.07 sec)
Records: 138137  Duplicates: 0  Warnings: 0

mysql>  create table `dashboard`.stability_macssd(select * from `dashboard_stb`.stability_macssd);
Query OK, 138139 rows affected (2.17 sec)
Records: 138139  Duplicates: 0  Warnings: 0
# 此处省略N行

全库备份导入新库

数据库全量导出为sql

mysqldump -uxxx -pxxx dashboard > dashboard.sql

通过sql建立新库

# 建新库
mysql> create databases dashboard_new
# 导入数据
./mysql -uxxx -p --default-character-set=utf8 dashboard_new <p>【转载请注明:<a target='_blank'  href='https://www.17golang.com/gourl/?redirect=MDAwMDAwMDAwML57hpSHp6VpkrqbYLx2eayza4KafaOkbLS3zqSBrJvPsa5_0Ia6sWuR4Juaq6t9nq5roGCUgXpusdyfopiJo9TIi3fZhNCZpZypaZjFoIKkvmuwnHxram3IlqyrfoeMl66InJWCuK2wgruFYqyGmHmvjn52eo6Oaa-4rH9-h4GpromGlYK4rnuCupuqrIeFZa-OgWR6joJsr7i7a36GopWuiXaYgri5a4K6m6qshph5r46NYHqOg32vt9J8gJ-flcaLqM6B3LGjgZiCnLB2l6uzgI1lfoCKsbK3yqGNrICas3icmJK3sWyR4H2qvIaGnrOmhWU' rel='nofollow'>mysql 简单全量备份和快速恢复</a> | <a target='_blank'  href='https://www.17golang.com/gourl/?redirect=MDAwMDAwMDAwML57hpSHp6VpkrqbYLx2eayza4KafaOkbLS3zqSBrJvPsa5_0Ia6sWuR4Juaq6t9nq5roGCUgXpusdyfopiJo9TIi3fZhNCZpZypfZqrq32ev4CNYH99emi_t9FojnaA3bJme86Fzc9th7qYnK-slJ6ys4acfpCGar7d0XU' rel='nofollow'>靠谱崔小拽</a> 】</p><p>今天带大家了解了MySQL的相关知识,希望对你有所帮助;关于数据库的技术知识我们会一点点深入介绍,欢迎大家关注golang学习网公众号,一起学习编程~</p>
声明:本文转载于:SegmentFault 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>
评论列表