How to Backup and Restore a MySQL/MariaDB Database

Learn how to back up and restore a MySQL/MariaDB database from the command line, step by step.
This is an excerpt from my practical Linux Web Hosting course -- $10 when you use this link: www.udemy.com/...
In this video, you'll learn how to back up and restore a MySQL database on the Linux command line, and how to schedule backups to run automatically, using a cronjob.
Check out my Free Linux Sysadmin Course Playlist: • The Linux Basics Cours...
Official Site & e-mail list: tutorialinux.com/
Twitter: / tutorialinux
Facebook: / tutorialinux
Patreon: / tutorialinux
Podcast: www.kernelpanic...

[@HallucinVIII]

Thank you for your great videos.
Every time I see the term drop tables all I can think of is little Bobby Tables from xkcd.
I imagine that I'm not the only one.

[@user-pw2cn9pf8x]

Very helpful. Thank you.

[@acidrazor]

I would love more tutorials for production/real world scenarios for n00b DBAs or someone setting out to build their own cluster etc.

[@tutoriaLinux]

Jeff, the guy who does the kernel panic podcast with me, REALLY knows MySQL...maybe I can convince him to do a few videos with me, setting up a highly available production cluster and talking about all the stuff that's important, all the gotchas, and all the hard-earned lessons. And how to get replication working again after your slave_exec_log_position gets all screwed up and you have evil parts of the binlog that have to be skipped before MySQL will work again. In production. ermagerd flashbacks

[@mattsnow7803]

Really enjoy watching your videos. It's really informative and easy to follow. Keep it coming!

[@runnerwannabe90]

Thanks for you videos.
But in this instance I disagree with this approach (i.e. the use of mysqldump), I would like to propose the use of Percona's Xtrabckup or the tool Mylvmbackup.
These should be the de facto standard, tools for MySQL backups backups and restoring.
Sure for your example, the a small database and my proposal is overkill, but if one of viewers is on the road to managing larger databases, least they can hit the ground running or learning and doing proper DBA tools / methodologies.
HTH

[@tutoriaLinux]

Thanks, that's a valuable tip. I actually wasn't familiar with Xtrabackup -- the mechanism I saw used for large-ish databases (2-5TB) was streaming replication to other database nodes (hot standby, business intelligence, a 'backup' node where we actually run mysqldump, etc.). We also had frequent disk snapshots on the DB instances, but of course that's primarily useful as a last resort. Thanks again for the tip; it helps to hear from someone who's a better DBA than me!

[@user-yy2bk2pg1l]

9:34 I got BAGUETTE from this one xD

[@xavierl3367]

Great guide, and excuse me if this has been mentioned before but how about a guide on tuning mysql. Oh and tuning mysql to run along side apache.

[@uchitesting]

What about crontab -e
Didn't have to deal with that for a while but if I'm correct this is how crontab should be edited.
Anyway thanks for the video. It showed very useful. I'm working in a project where many people are involved and shit happened so here I am to quickly know how to backup my stuff.

[@s.nikolic497]

great video ... thank you

[@ko95]

how do you keep database and web app files in sync? dump the sql to a version controlled git folder?

[@Dipesh155]

How to migrate mysql user, host and password from old to new server?

[@Linuxbabeofficial]

--add-drop-table is on by default. You don't need to type it :)

[@uchitesting]

just to be sure and on the safe side. Tomorrow they decide it is no more the default. Your script is still behaving as expected.
Let me wonder why would there be an option then.

[@rogernevez5187]

This method is also very fast for large databases (10Mi rows)?

[@JohnSmith-zl8rz]

why not just this? mysqldump -u username -p database_name > source_dump.sql why the --add-drop-table , I think by default drop table is add it.

[@samrybkin9184]

What your best games on linux?

[@southbayphotos]

Thanks for the information on backups and restores. But seriously, it's called "My-S-Q-L", not "My-Seequil": en.wikipedia.org/wiki/MySQL

[@jordannash4420]

Hi I'm stuck at 6:47 and get a permission denied statement when I execute the mysqldump. I'm doing this on Ubuntu 18.04.2 LTS. Anybody run into the same problem whilst following all the steps? Were you able to solve it?

[@jordannash4420]

Ok I solved it. Answer in this link. askubuntu.com/questions/1146220/permission-denied-after-trying-to-create-mysqldump-even-after-creating-my-cnf?noredirect=1#comment1905374_1146220

[@baniputri96]

Hi Jordan, /home//backups/db

[@ankitaaarya]

hey

[@flyingzeppo]

Very helpful. Thank you.