MySQL 数据库导出命令 mysqldump 简明用法

Kevin 域名 1,626 次浏览 103条评论

之前用 LNMP 一键包搭建环境时,习惯通过 phpMyAdmin 面板备份数据库。偶尔这样操作还行,如果需要经常备份就麻烦了。还是使用 shell 命令操作要好些,可以写个脚本自动完成。

MySQL 和其衍生版(如 MariaDB)包含了一个实用工具 mysqldump,可以用它转储数据库至标准输出,然后输出到文件保存,并在需要时用备份恢复数据库。下面就来介绍 mysqldump 的具体用法。

导出单个数据库

这是 mysqldump 导出数据库的基本语法。

mysqldump -u user_name -p db_name > dump-$(date +"%Y%m%d").sql

解释其中的各项参数:

  • -u 用于指定数据库用户名,通常是 root,需要具备对目标数据库的读写权限。
  • -p 指定数据库账号密码。基于安全考量,一般不指定密码,运行命令后以交互方式输入。若要指定密码,在 -p 后输入密码(注意是紧挨着没有空格)。
  • db_name 备份数据库名称,这种写法不能同时备份多个数据库。如果用空格写了多个名称,则会被视为备份数据库下的表(第一名称是数据库,之后名称表示该数据库下的表名)。
  • > dump-$(date +"%Y%m%d").sql 将数据库导出到文件,这里文件名加了时间戳。

导出多个数据库

导出多个数据库用 --databases 参数指定,语法如下。

mysqldump -u user_name -p --databases db1 db2 db3 > dump-$(date +"%Y%m%d").sql

当带了 --databases 和下面介绍的 --all-databases 参数,在转储输出前会写入 CREATE DATABASE 和 USE 语句。这样在恢复数据时,如果没有同名数据库,它会先创建数据库。并在恢复各个数据库时,通过 USE 语句设置默认数据库,以便将数据导入到同一数据库中。

导出所有数据库

备份所有数据库使用 --all-databases 参数。

mysqldump -u user_name -p --all-databases > dump-$(date +"%Y%m%d").sql

其它常用参数

上面提到过,因为安全原因,不建议在 -p 后面指定密码。但有的场景需要自动输入,那应该怎样操作比较好呢?这时可用选项文件存储密码凭据。

具体做法是先在用户目录下创建一个.my.cnf文件,内容格式如下。

[client]
user=root
password=password_here

接着设置文件权限,仅允许属主具读写权限。这步是必须的,MySQL 会忽略未设置权限的选项文件。

chmod 600 /root/.my.cnf

之后在命令里用 --defaults-extra-file 参数指定选项文件(绝对路径),这样就不用手动输入密码了。

mysqldump --defaults-extra-file=/root/.my.cnf --user=root db_name > dump-$(date +"%Y%m%d").sql

可能你留意到了,为什么上面命令中仍指定了用户名?这是一个安全做法,防止配置选项文件里的用户名被替换,这个不是必须。事实上如果选项文件用了.my.cnf文件名,并且在用户目录下,--defaults-extra-file 参数也可以省略掉,MySQL 程序会自动读取这个约定用户选项文件。

另外介绍几个实用参数,在机器内存较小或要备份大型数据库时很有用。

mysqldump -u user_name -p --single-transaction --quick --lock-tables=false db_name > dump-$(date +"%Y%m%d").sql
  • --single-transaction 参数表示将备份会话的隔离级别设置为 REPEATABLE READ,以确保不会看到其它会话已经提交的数据。目的是为确保备份数据的一致性,它只对支持事务的表有效(只有 InnoDB 表支持事务)。在导出大表时与 --quick 结合使用。
  • --quick 参数表示逐行转储表,避免将表存储在内存中可能带来的潜在问题。
  • --lock-tables=false 参数表示不要为备份会话锁定表。

添加定时备份数据库任务

在掌握了数据库导出命令使用后,要定时备份数据库只需添加相应的 cron 任务。执行crontab -e添加计划任务,内容如下。

0 0 1 * * /usr/bin/mysqldump --defaults-extra-file=/root/.my.cnf --user=root db_name > /root/dump-$(date +"%Y%m%d").sql

这将每月备份一次数据库,文件存储在 /root 目录下。

用备份文件恢复数据库

导入恢复单个数据库:

mysql -u user_name -p db_name < dump-backup.sql

导入恢复单个数据表:

mysql -u user_name -p db_name < dump-table-backup.sql

导入单个数据库或数据表时,目标数据库必须是已存在。否则需要先创建数据库,创建语法如下。

mysqladmin -u user_name -p create db_name

当导入使用 --databases 或 --all-databases 参数导出的数据库文件,则不需要指定目标数据库,它会自动创建数据库。

mysql -u user_name -p < dump-full-backup.sql

评论列表

  1. #1

    acyclovir tablet cost https://www.herpessymptomsinmen.org/productacyclovir/

  2. #2

    Muchas gracias. ?Como puedo iniciar sesion?

  3. #3

    bimatoprost ophthalmic solution cost https://carepro1st.com/

  4. #4

    cialis 20mg price in usa https://buszcentrum.com/

  5. #5

    is amoxicillin covered by insurance https://amoxycillin1st.com/

  6. #6

    chinese ivermectin side effects https://ivermectin.webbfenix.com/

  7. #7

    homemade dapoxetine for men https://salemeds24.wixsite.com/dapoxetine

  8. #8

    does walmart pharmacy sell generic vidalista https://vidalista.mlsmalta.com/

  9. #9

    vidalista 10mg prices in india https://vidalista.buszcentrum.com/

  10. #10

    i kept the submariner date for next 16 years or so and it served me well.

    http://peppinospizzacarbondale.com/

  11. #11

    How to find salvation army cars for sale https://carsforyourhelp.com/salvation-army-car-donation/ car sales for salvation army donated cars

  12. #12

    hydroxychloroquine and blood clots https://hydroxychloroquine.mlsmalta.com/

  13. #13

    genuine ivermectin without a doctor prescription https://ivermectin.mlsmalta.com/

  14. #14

    what does hydroxychloroquine do https://hydroxychloroquine.webbfenix.com/

  15. #15

    how long does viagra last http://droga5.net/

  16. #16

    dapoxetine tablets price https://dapoxetine.bee-rich.com/

  17. #17

    arimidex vs tamoxifen side effects https://tamoxifen.mrdgeography.com/

  18. #18

    vidalista and muscle pain https://vidalista40mg.mlsmalta.com/

  19. #19

    buy cialis no prescription overnight https://wisig.org/

  20. #20

    ventolin hfa inhaler side effects https://amstyles.com/

  21. #21

    Wow! In the end I got a weblog from where I can really obtain useful information concerning my study and knowledge. Lucille Ad Richie

  22. #22

    hydroxychloroquine and eliquis interactions https://hydroxychloroquinee.com/

  23. #23

    dapoxetine uk price https://ddapoxetine.com/

  24. #24

    doryx 100 mg http://doxycycline.zolftgenwell.org/

  25. #25

    generic sildenafil price http://cavalrymenforromney.com/

  26. #26

    hydroxychloroquine 400 mg https://hydroxychloroquine.lm360.us/

  27. #27

    hydroxychloroquine 400 mg https://hydroxychloroquine.mymvrc.org/

  28. #28

    You have brought up a very superb points, thanks for the post.

    http://www.vreyrolinomit.com/

  29. #29

    pharmacies not requiring a prescription https://buymeds.mlsmalta.com/

  30. #30

    viagra to treat pulmonary hypertension http://viaaagra.com/

  31. #31

    We stumbled over here coming from a different web address and thought I should check things out. Lorrie Moishe Norbie

  32. #32

    There is certainly a great deal to know about this topic. I really like all the points you made. Helli Mickie Rabush

  33. #33

    Certified N95 respirators can filter 95 percent of particles that are one hundred to 300 nm in dimension. Cairistiona Garek Vandyke

  34. #34

    https://pussy888vip.com/
    I like this post.

  35. #35

    Thanks for sharing, this is a fantastic post. Much thanks again. Great. Melitta Staffard Broeker

  36. #36

    I not to mention my pals were actually going through the good tactics found on the website and so before long developed an awful suspicion I had not thanked the web blog owner for those techniques. The women happened to be passionate to learn all of them and have in effect in fact been tapping into those things. Appreciation for being very thoughtful and then for deciding upon this kind of helpful information most people are really desperate to know about. Our own sincere regret for not expressing appreciation to sooner. Carina Neel Tatum

  37. #37

    How do you build a stable custom M4 like it came from the company itself? Sephira Thibaud Anastatius

  38. #38

    I dugg some of you post as I cogitated they were invaluable very beneficial Blakelee Eric Bindman

  39. #39

    This paragraph is truly a good one it helps new the web visitors, who are wishing in favor of blogging. Leann Job Teilo

  40. #40

    You have remarked very interesting points! ps decent web site. Ariel Huey Scoter

  41. #41

    This article is truly a nice one it helps new net users, who are wishing in favor of blogging. Laurie Lalo Reginald

  42. #42

    Hence, we hire every one of them on the basis of their qualifications and abilities. Each of our writers are extremely qualified, experienced and educated and deliver the best assignment help possible Wandie Van Adala

  43. #43

    Very useful ! Thank U
    วิธีดูราคาบอล

  44. #44

    I have recently started a site, the info you offer on this web site has helped me greatly. Thanks for all of your time & work. Stormy Jeremy Isabeau

  45. #45

    which works best cialis or viagra https://cialis.bee-rich.com/

  46. #46

    which works best cialis or viagra https://cialis.advantagetriseal.com/

  47. #47

    My brother recommended I would possibly like this website. He was once totally right. This post truly made my day. You can not consider simply how a lot time I had spent for this info! Thanks!| Lorinda Shaughn Martina

  48. #48

    generic indian tadalafil https://tadfil.online/

  49. #49

    Hurrah! In the end I got a weblog from where I can actually obtain helpful information concerning my study and knowledge. Nichole Gearalt Jayson

  50. #50

    Some genuinely superb blog posts on this site, thankyou for contribution. Hollyanne Virge Chan

  51. #51

    I together with my friends have already been reading through the best techniques found on your web site and unexpectedly got a terrible feeling I never expressed respect to the blog owner for those techniques. All the men are already totally joyful to learn them and have now clearly been having fun with them. Thanks for really being very thoughtful and for obtaining this sort of extraordinary things most people are really desperate to discover. My personal honest apologies for not expressing gratitude to earlier. Fulvia Eward Milena

  52. #52

    TRADER has no owners, born from a joint initiative of companies related to Trading and investment banking Gay Ignacio French

  53. #53

    Good day! This is kind of off topic but I need
    some help from an established blog. Is it very difficult to set up your
    own blog? I’m not very techincal but I can figure things
    out pretty quick. I’m thinking about setting up my
    own but I’m not sure where to start. Do you have any
    ideas or suggestions? Cheers http://antiibioticsland.com/Zithromax.htm

  54. #54

    Wonderful work! That is the kind of information that are meant to be shared across the web. Addy Payton Russi Lola Russell Sacken

  55. #55

    Of course, what a fantastic site and informative posts, I will bookmark your website. Have an awsome day! Zarla Millard Hwang

  56. #56

    So up for this challenge I have used Animoto to make a few videos showing pictures of my work but this is a great challenge to get me in front of the camera. I look forward to watching all the great videos submitted by everyone. Filia Errick Gereld

  57. #57

    Status Liker, Photo Auto Liker, Autoliker Facebook, Autoliker, auto like, Autoliker, Facebook Autoliker, Photo Liker, autoliker, facebook auto liker, Fb Autoliker, Facebook Liker, Working Auto Liker, Auto Liker, Status Auto Liker, auto liker, Facebook Auto Liker, Auto Like, autolike, Increase Facebook Likes Darrelle Ulrich Tana

  58. #58

    The following time I learn a weblog, I hope that it doesnt disappoint me as much as this one. I mean, I do know it was my choice to read, however I truly thought youd have one thing fascinating to say. All I hear is a bunch of whining about something that you could repair for those who werent too busy on the lookout for attention. Lyndsay Chaim Derick

  59. #59

    Howdy! This blog post could not be written any better! Looking at this article reminds me of my previous roommate! He constantly kept preaching about this. I will send this post to him. Pretty sure he will have a good read. Thanks for sharing! Sissy Perceval Felty

  60. #60

    You need to be a part of a contest for one of the finest blogs online. I will highly recommend this site! Dorothea Arron Olivier

  61. #61

    Hello There. I found your blog using msn. This is an extremely well written article. Lizette Alfred Harms

  62. #62

    I was more than happy to find this website. I wanted to thank you for your time due to this wonderful read!! I definitely enjoyed every part of it and I have you bookmarked to look at new information on your web site. Tomasine Gail Huskamp

  63. #63

    I am truly grateful to the owner of this site who has shared this wonderful piece of writing at here.| Coletta Obadiah Jeuz

  64. #64

    Hi Wick. I switched those two power wires and away it went. Thanks for letting me bounce that off you. Wren Brade Rouvin

  65. #65

    I always was interested in this topic and still am, appreciate it for posting. Caralie Waverley Lesley

  66. #66

    This is my first time go to see at here and i am actually impressed to read all at one place. Nerty William Osric Stacey Hurleigh Winwaloe

  67. #67

    Thanks so much for the blog post. Thanks Again. Great. Karia Frederico Welker

  68. #68

    Everyone loves it when individuals get together and share ideas. Great blog, keep it up! Angelia Rolph Whittaker

  69. #69

    Very nice style and design and fantastic content , nothing at all else we need : D. Rafaelia Wilmar Lenna Minta Haven Durstin

  70. #70

    Aw, this was a really nice post. Finding the time and actual effort to make a great article?but what can I say?I hesitate a lot and never seem to get nearly anything done. Fiann Nappie Gagne

  71. #71

    For hottest information you have to visit web and on world-wide-web I found this web site as a finest web page for hottest updates.| Juliane Thibaut Sapphera

  72. #72

    At vero eos et accusamus et iusto odio dignissimos ducimus qui blanditiis praesentium voluptatum deleniti atque corrupti quos dolores et quas molestias excepturi sint occaecati cupiditate non providen Brier Richmound Hax

  73. #73

    Very interesting subject, appreciate it for putting up. Corrie Dexter Brana

  74. #74

    Good day! I simply would like to give you a huge thumbs up for the great info you have got here on this post. I will be coming back to your blog for more soon. Shane Xavier Hyacinth

  75. #75

    I got what you intend,bookmarked, very decent website. Rebekkah Hewet Erda

  76. #76

    Real Estate Agent houses for sale selling your home Anne-Corinne Bartholomeo Colline

  77. #77

    Superb, what a weblog it is! This weblog gives valuable facts to us, keep it up.| Ulrikaumeko Ichabod Immanuel

  78. #78

    Im obliged for the article. Really looking forward to read more. Tandy Raymund Lorilyn

  79. #79

    Once you have a credit card on file upgrades come about automatically when you add adequate users to move to the next plan level. Amalita Adolpho Hafler

  80. #80

    When I originally left a comment I seem to have clicked on the -Notify me when new comments are added- checkbox and now each time a comment is added I receive four emails with the same comment. There has to be a way you can remove me from that service? Many thanks!| Ava Jamey Dalston

  81. #81

    Suggestions And Guidance To Help With Making Travel Anxiety-Free Whether it is to find a more affordable method to travel or possibly a greater place to stay, the tips below is likely to make your vacation more pleasant. Abandon your main valuable items in your own home. Tend not to anticipate an flight will make you are traveling. If you actually need earphones, a quilt along with a cushion being cozy when soaring, a light blanket and comfy headphones. You should also think about taking some snacks with you. Develop a checklist for your vacation! Make travel preparations way prior to your holiday. In spite of the diverse techniques to commit your hard earned dollars on your own trips, a variety of facts you can pay for whenever you traveling, the less expensive they may be. You may prevent excessive expenditures in the course of vacation by steering clear of eleventh hour costs. When traveling with a small youngster, you need to take along things that can keep him curious and pleased during the trip. Bring lots of games and game titles you are aware the little one truly appreciates. You should buy a new playthings to get his consideration. You might be taken care of in the case of a cancelled airline flight that was incurred on the charge card. Before leaving, its smart to execute some study. A motorcycle is great way to continue on a road trip. It is definitely a lots of fun. You can find a great deal of journey suggestions on the market that pleasant trip and household pets websites now. Such as attention locations. It is possible to take your dog alongside as long as you just need to be sure they are made it possible for. Sign up to signals on travel price watcher. This will likely inform you of your price ranges of spots you to definitely any cost alter. If the selling price for plane tickets or accommodations meets your anticipations, the internet site comes to an end you an email notify. This helps to keep you avoid the irritation of looking into the purchase price daily. If you are traveling over a cruise liner, chat with other guests during dishes. Several ships seat you will definitely get set in a big kitchen table with individuals you have never ever achieved prior to. Have got a good frame of mind and speak because of the individuals you satisfy. You are traveling with they, and you simply may find out some information about the ship by doing this. We hope these tips have fired up your want to traveling. Now, the truth is all of that is waiting for you. Think about your choices. Once you are able to program for your trip, there are actually your time and efforts to get more pleasant and exciting. Valencia Shermie Lesslie

  82. #82

    I have read some excellent stuff here. Certainly price bookmarking for revisiting. I wonder how so much attempt you place to make this kind of fantastic informative site.| Cristen Falkner Schulz

  83. #83

    i am interested to buy your reviewer. How much? and how to avail? Vina Goran Sarid

  84. #84

    Way cool! Some extremely valid points! I appreciate you writing this write-up plus the rest of the website is really good. Yovonnda Moise Furie

  85. #85

    Thanks for a marvelous posting! I certainly enjoyed reading it, you are a great author. I will always bookmark your blog and may come back sometime soon. I want to encourage you to continue your great posts, have a nice evening! Angeline Haley Roseanne

  86. #86

    After checking out a few of the articles on your blog, I truly like your way of blogging. I added it to my bookmark site list and will be checking back soon. Take a look at my website too and let me know how you feel. Shawna Leighton Wilonah

  87. #87

    Well done Aimee, you tasted more than I could in one evening- one gin lover to another- I loved your blog!! Leone Rainer Steinke

  88. #88

    Loved your article. I will return to see some more. Deena Robb Traver

  89. #89

    Awesome post. I am a normal visitor of your web site and appreciate you taking the time to maintain the excellent site. I will be a regular visitor for a long time. Elspeth Ric Yurt

  90. #90

    Crud much unstinting violently pessimistically far camel inanimately a remade dove disagreed hellish one concisely before with this erotic frivolous. Thea Sheff Bunnie

  91. #91

    You can definitely visit your expertise within the work you write.
    The planet hopes for more passionate writers such as you who aren’t afraid to express the direction they believe.
    Constantly follow your heart.

    Also visit my blog; UshaISpeller

  92. #92

    cheap cialis online canadian pharmacy https://cialis.bee-rich.com/

  93. #93

    cheap cialis online canadian pharmacy https://cialis.webbfenix.com/

  94. #94

    Greetings! Very useful advice within this post!

    It is the little changes which will make the biggest changes.

    Thanks for sharing! http://herreramedical.org/chloroquine

  95. #95

    Hi there! This post couldn’t be written any better! Reading through this post reminds me of my previous room mate! He always kept talking about this. I will forward this article to him. Pretty sure he will have a good read. Thank you for sharing!

    http://www.zortilonrel.com/

  96. #96

    At this moment I am going away to do my breakfast, later than having my breakfast coming yet again to read more news. http://antiibioticsland.com/Cephalexin.htm

  97. #97

    buy hydroxychloroquine online https://hydroxychloroquinex.com/

  98. #98

    Some really nice and utilitarian information on this internet site, as well I conceive the design has good features.

    http://www.vreyrolinomit.com/

  99. #99

    I have been browsing online more than 3 hours today, yet I never found any
    interesting article like yours. It’s pretty worth enough for me.
    In my view, if all web owners and bloggers made good content as you did, the net will be a lot more
    useful than ever before. https://cialis.studiowestinc.com/tadalafil

  100. #100

    My brother recommended I might like this website. He was totally right. This post actually made my day. You cann’t imagine just how much time I had spent for this info! Thanks!

    http://www.zortilonrel.com/

  101. #101

    Have you ever heard of second life (sl for short). It is basically a game where you can do anything you want. SL is literally my second life (pun intended lol). If you would like to see more you can see these second life articles and blogs

    http://www.feedage.com/feeds/1772091/angela-a-thomas

  102. #102

    Have you ever heard of second life (sl for short). It is essentially a online game where you can do anything you want. SL is literally my second life (pun intended lol). If you want to see more you can see these Second Life authors and blogs

    edugamesresearch.com/blog/category/game-writing/page/13/

  103. #103

    Hey There. I discovered your blog using msn. That is a really well written article. I will make sure to bookmark it and return to read more of your useful info. Thank you for the post. I will definitely comeback.

    https://www.youtube.com/watch?v=zFfqK4-tynU

发表评论

电邮地址用于 Gravatar 头像显示,不会被公开可见。