Backup a MySQL Database from the Terminal

This guide shows how to save a MySQL database using a terminal, with a simple syntax command. And faster than using phpMyAdmin, for sure.
Just replace the db name, username and password and paste the code into your terminal.
While this is a handy how-to, probably you’ll want to perform frequent db exports and we’ll cover that later in the series, in the cron job tutorial.
For backing up your web or blog files, refer to Part 10: Backup Sites & Blogs from the Command Line.
Setup Unmanaged VPS: The Ubuntu-Nginx Guide
Take your virtual private server from zero to hero
.. from blank box to cute-as server ..
with this easy-to-follow copy/paste guide.
22+ parts with video, here’s the index.
Dumping (that’s exporting, not erasing) the data from the MySQL database is the same procedure whether backing up locally or remotely except that, remotely, of course you have first to access the server, as we covered in the VPS Bible, Set Up the Command Line Interface (CLI) using PuTTY.
Once logged into the terminal, don’t bother further logging into the MySQL shell.
Just change directory to where you want to backup to, maybe creating a new folder (mkdir /path/to/new). So goto something like:-
And backup in there, using this syntax:-
You’ll be prompted for your db password and, once entered, the backup is created, super-fast, and you’re done.
“mysqldump” Syntax Broken Down
- sudo gives Super User permissions, so you can perform the action
- mysqldump is the command. We’re dumping a db into a backup folder
- databasename is the name of the db
- -u tells mysqldump we’re about to give the user details, after which we give the user’s name, with no space in between
- -p tells mysqldump we’ll be authenticating with a password
- > says the backup is to go in the direction of the file to create
- some_db_backup_name.sql is the name of the backup file to be created
So, if I’ve got a database called “work”, my username is “guv” and my password is “abc”, I could use something like this below. (The one possible variable is the name of the backup file, which I can call whatever I like) ..
Prompted, after entering the db password, I’m done.
Setup Unmanaged VPS: The Ubuntu-Nginx Guide
Take your virtual private server from zero to hero
with this easy-to-follow copy/paste guide.
“My local PC runs Windows” Show me for Linux
“My local PC runs Linux” Show me for Windows
22+ parts with video, here’s the index ..
-
Setup Unmanaged VPS: The Ubuntu-Nginx Guide
Introducing vpsBible’s how-to guide for setting up a super-charged unmanaged VPS featuring Ubuntu and Nginx.
-
VPS (Virtual Private Server) vs Shared vs Dedicated
Before getting stuck in, let’s compare the web host types in detail and weigh up the differences between managed & unmanaged VPS.
-
Set Up a VPS Linux Distribution
Opening a VPS hosting account, we’ll choose, configure & launch a Linux server ‘distribution’.
-
Set Up Command Line Interface (CLI) using PuTTY
Now we’ve got a server to play with, we’ll connect to it, using the ’ssh’ protocol for a super-secure link.
-
Create a Linux User & Set Permissions
Initially you’ve logged into your remote Linux machine as ‘root’. We’ll add you as a user now, with root or SuperUser permissions.
-
Encrypt Data with OpenSSH & Auto-Login with PuTTY
Now we can use ‘authentication keys’ to shore up our connection while simplifying login to an automated, password-free yet secure process.
-
Harden the Secure Shell (SSH) & Create a Firewall
The final security step: galvanizing the OpenSSH protocol and setting up an ‘iptables’ firewall. Now the server is rock ruddy solid.
-
Edit bashrc for User-Friendly Linux, plus System Updates
We use the terminal a whole lot so let’s cut ourselves a break and create some command shortcuts, then update the server.
-
PHP5, MySQL and Xcache (for Platforms like WordPress)
We have our foundation, let’s lay some bricks: PHP for server-side web applications, Xcache to speed things up & MySQL for databases.
-
Add a Domain Zone to Your VPS
To make your server aware of yourDomain.com you’ll need a few ‘DNS’ records. Damn this is simple I’m practically asleep!
-
Prepare Linux Server for Email with Postfix
In order to send and receive email from our web applications we’ll install some software and tweak the ‘RDNS’ record.
-
Nginx (better than Apache) Web Server
OK I woke up .. let’s install this supersonic web server, tweaking its file structure & adding default configuration files.
-
Setup FileZilla for Secure FTP (SFTP)
There’s FTP, and then there’s ‘Secure FTP’. We’ll set up FileZilla using SFTP – and those authentication keys – for secure file transfers.
-
Serve Multiple Sites & Blogs with Virtual Hosts
We want a website folder structure, some permissions, some site-specific configuration files and, hey, we’ll hook up ‘FastCGI’.
-
Adding Sub-Domains with Nginx
Need a sub-domain? Here’s how to add www.sub.domain.com with an ‘A record’ and a configuration file. (I’ll allow you to skip this bit.)
-
Integrate A CMS Like WordPress, WP MU, Drupal etc
For those of you wanting to use a specific content framework, such as a CMS, blogging or forum software, pop in here and we’ll sort that out.
-
Configure phpMyAdmin for Nginx to Maintain MySQL
Rather than have to crunch databases via the command line, we can make life easier by adding this splendid tool to manage them.
-
Google Apps for Domain-Specific Email
GA’s free Standard Edition for catchall webmail – POP-ping or IMAP-ped to your desktop client – saves precious server resources for your webs.
-
Moving Day! How to Move Your Blog or Site
Nearly there. Here’s the web site or blog migration guide, relocating to your VPS with minimal or no downtime.
-
Appendix 1a: Nginx Control Panel
You’re migrating shared-to-VPS and want a GUI like cPanel? No you don’t, not after you read this! What’s more, you don’t need one either.
-
Appendix 1b: Nginx Control Panel Workarounds
For every control panel module there’s an equivalent terminal command. They’re all linked from here so, hey, no excuses!
-
Appendix 2: Add Web Sites Nginx Cheatsheet
Once you’ve set up your first site, adding more gets easier. Especially when you can refer to this. (Let’s face it, I’m just too good to you.)





















Got a Question? Want to Comment?
This site has no comments system. We've got a better system!
Please use the link at the top of the post and goto this topic's forum thread:-
For those posts ported from Guvnr.com (pre-Feb/2010), archive comments remain.
Handy info from these comments is in the process of being ported to the forum and attributed to the original author.
If you think this idea sucks, let me know.
Thank you.