How To Backup Whole MySQL Database
Update 01-03-2021: Updated article for UTF-8 encoding support
I uses Linux for everyday use, Linux Mint to be exact. It's free and just works out-of-the-box without the mess of installing different driver for different hardware. Well, at least this what my case, as some of us might find Linux is hard, cumbersome, and errorenous.
Everytime I feel my computer run slower than it's supposed to be, I go to my favorite distribution website and download their most recent Cinnamon release. Write the image to a flashdisk and boot it to the installer. After a few clicks, I have a new and fresh operating system.
Now, that's just the summary. In real-life scenario, we will have many data like office documents, precious family images, and so on. For a web developer like me, what matter most are my project files and databases. Thus, we'll need to backup them in a safe place.
Backup MySQL Databases from Command Line
In this article, I will share about backing up my MySQL databases. MySQL provides an utility to do this task called
mysqldump. The syntax of the command is:
mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]
[uname]is database username
[pass]is database password
[dbname]is database name
[backupfile.sql]is the database dump file name
That's useful if we want to back up single database. To backup whole database, here's the command:
mysqldump --opt -u [uname] -p[pass] --all-databases > [backupfile.sql]
Backup MySQL Databases with Compression
Suppose we're a really busy web developer. We will have many MySQL databases which some of them may have million rows. So, what happened if we have large database but we want to save our precious disk space? The answer is using compression. Using compression will help save space but will need more time to finish.
mysqldump --opt -u [uname] -p[pass] --all-databases | gzip -9 > [backupfile.sql.gz]
They're the most common commands I use when I need to backup my MySQL database. I will continue with MySQL database restoration on another article.