MySQL 数据库导出命令 mysqldump 简明用法
之前用 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
acyclovir tablet cost https://www.herpessymptomsinmen.org/productacyclovir/
Muchas gracias. ?Como puedo iniciar sesion?
bimatoprost ophthalmic solution cost https://carepro1st.com/
cialis 20mg price in usa https://buszcentrum.com/
is amoxicillin covered by insurance https://amoxycillin1st.com/
chinese ivermectin side effects https://ivermectin.webbfenix.com/
homemade dapoxetine for men https://salemeds24.wixsite.com/dapoxetine
does walmart pharmacy sell generic vidalista https://vidalista.mlsmalta.com/
vidalista 10mg prices in india https://vidalista.buszcentrum.com/
i kept the submariner date for next 16 years or so and it served me well.
http://peppinospizzacarbondale.com/
How to find salvation army cars for sale https://carsforyourhelp.com/salvation-army-car-donation/ car sales for salvation army donated cars
hydroxychloroquine and blood clots https://hydroxychloroquine.mlsmalta.com/
genuine ivermectin without a doctor prescription https://ivermectin.mlsmalta.com/
what does hydroxychloroquine do https://hydroxychloroquine.webbfenix.com/
how long does viagra last http://droga5.net/
dapoxetine tablets price https://dapoxetine.bee-rich.com/
arimidex vs tamoxifen side effects https://tamoxifen.mrdgeography.com/
vidalista and muscle pain https://vidalista40mg.mlsmalta.com/
buy cialis no prescription overnight https://wisig.org/
ventolin hfa inhaler side effects https://amstyles.com/
Wow! In the end I got a weblog from where I can really obtain useful information concerning my study and knowledge. Lucille Ad Richie
hydroxychloroquine and eliquis interactions https://hydroxychloroquinee.com/
dapoxetine uk price https://ddapoxetine.com/
doryx 100 mg http://doxycycline.zolftgenwell.org/
generic sildenafil price http://cavalrymenforromney.com/
hydroxychloroquine 400 mg https://hydroxychloroquine.lm360.us/
hydroxychloroquine 400 mg https://hydroxychloroquine.mymvrc.org/
You have brought up a very superb points, thanks for the post.
http://www.vreyrolinomit.com/
pharmacies not requiring a prescription https://buymeds.mlsmalta.com/
viagra to treat pulmonary hypertension http://viaaagra.com/
We stumbled over here coming from a different web address and thought I should check things out. Lorrie Moishe Norbie
There is certainly a great deal to know about this topic. I really like all the points you made. Helli Mickie Rabush
Certified N95 respirators can filter 95 percent of particles that are one hundred to 300 nm in dimension. Cairistiona Garek Vandyke
https://pussy888vip.com/
I like this post.
Thanks for sharing, this is a fantastic post. Much thanks again. Great. Melitta Staffard Broeker
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
How do you build a stable custom M4 like it came from the company itself? Sephira Thibaud Anastatius
I dugg some of you post as I cogitated they were invaluable very beneficial Blakelee Eric Bindman
This paragraph is truly a good one it helps new the web visitors, who are wishing in favor of blogging. Leann Job Teilo
You have remarked very interesting points! ps decent web site. Ariel Huey Scoter
This article is truly a nice one it helps new net users, who are wishing in favor of blogging. Laurie Lalo Reginald
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
Very useful ! Thank U
วิธีดูราคาบอล
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
which works best cialis or viagra https://cialis.bee-rich.com/
which works best cialis or viagra https://cialis.advantagetriseal.com/
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
generic indian tadalafil https://tadfil.online/
Hurrah! In the end I got a weblog from where I can actually obtain helpful information concerning my study and knowledge. Nichole Gearalt Jayson
Some genuinely superb blog posts on this site, thankyou for contribution. Hollyanne Virge Chan
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
TRADER has no owners, born from a joint initiative of companies related to Trading and investment banking Gay Ignacio French
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
Wonderful work! That is the kind of information that are meant to be shared across the web. Addy Payton Russi Lola Russell Sacken
Of course, what a fantastic site and informative posts, I will bookmark your website. Have an awsome day! Zarla Millard Hwang
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
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
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
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
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
Hello There. I found your blog using msn. This is an extremely well written article. Lizette Alfred Harms
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
I am truly grateful to the owner of this site who has shared this wonderful piece of writing at here.| Coletta Obadiah Jeuz
Hi Wick. I switched those two power wires and away it went. Thanks for letting me bounce that off you. Wren Brade Rouvin
I always was interested in this topic and still am, appreciate it for posting. Caralie Waverley Lesley
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
Thanks so much for the blog post. Thanks Again. Great. Karia Frederico Welker
Everyone loves it when individuals get together and share ideas. Great blog, keep it up! Angelia Rolph Whittaker
Very nice style and design and fantastic content , nothing at all else we need : D. Rafaelia Wilmar Lenna Minta Haven Durstin
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
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
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
Very interesting subject, appreciate it for putting up. Corrie Dexter Brana
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
I got what you intend,bookmarked, very decent website. Rebekkah Hewet Erda
Real Estate Agent houses for sale selling your home Anne-Corinne Bartholomeo Colline
Superb, what a weblog it is! This weblog gives valuable facts to us, keep it up.| Ulrikaumeko Ichabod Immanuel
Im obliged for the article. Really looking forward to read more. Tandy Raymund Lorilyn
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
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
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
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
i am interested to buy your reviewer. How much? and how to avail? Vina Goran Sarid
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
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
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
Well done Aimee, you tasted more than I could in one evening- one gin lover to another- I loved your blog!! Leone Rainer Steinke
Loved your article. I will return to see some more. Deena Robb Traver
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
Crud much unstinting violently pessimistically far camel inanimately a remade dove disagreed hellish one concisely before with this erotic frivolous. Thea Sheff Bunnie
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
cheap cialis online canadian pharmacy https://cialis.bee-rich.com/
cheap cialis online canadian pharmacy https://cialis.webbfenix.com/
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
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/
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
buy hydroxychloroquine online https://hydroxychloroquinex.com/
Some really nice and utilitarian information on this internet site, as well I conceive the design has good features.
http://www.vreyrolinomit.com/
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
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/
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
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/
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