MySQL backup on Ubuntu | Debian
This guide is for Sys Admins–or people who have a bit of Sys Admin skills. While there is a plethora of database backup products for MySQL that exist, this post is not about those products.
We’ll use the built-in tools of MySQL for the backup process–it is in fact, a very simple process.
A simple setup
If your database is on a local workstation, you can login locally, just get a terminal (gnome-terminal, xterm, konsole, aterm .. it doesn’t really matter)
$ cd ~
$ mysqldump -u yourusername -p –all-databases > mysql.backup
“cd ~” simply changes directory to your home directory, that way we have all the necessary permissions to create a file mysql.backup
Replace yourusername with a username defined in MySQL, it’s not the same as your shell username at least not necessarily. So you gotta know this username, if an admin set it up for you, you need to ask your admin.
The “–all-databases” flag will dump all the databases in MySQL to the file mysql.backup, this may not be what you need. A more targeted usage of mysqldump is
$ cd ~
$ mysqldump -u yourusername -p NameOfDatabase > mysql.backup
Substitute the name of your database to NameOfDatabase above, that command will then dump all the contents, including the structure of the database into mysql.backup.
The dump file
The dump file (mysql.backup) actually contains the create table, insert into table instructions for your database. You can easily restore the contents of the database by simply executing the SQL script inside the dump file; for example
$ mysql -u YourUserName -p
mysql> create database restoreddb;
mysql> use restoredb;
mysql> .\mysql.backup;
.\ takes a source file argument, if you did this, it will read all the contents of mysql.backup and will try to execute it inside the restoredb database.
** The create database and use database command can actually be part of mysql.backup, but you can figure it out for yourself, I just don’t think it’s necessary to belabor that point in this guide.
A client server approach
MySQL is actually a client server database, which means that can run mysqldump and some other mysql tools on a workstation that is different from where the mysql server is installed. You don’t really need to perform the backup on the server box itself, use the client-server capabilities of MySQL. Here’s how;
On a workstation install the mysql-client tools. In Ubuntu and Debian;
$ sudo apt-get install mysql-client
Now you can use the mysql client command line from a workstation.
$ mysql -h IPorNameOfMySQLServer -u YourUserName -p
mysql> show databases;
mysql> use NameOfDatabase;
mysql> show tables;
mysql> quit
$ mysqldump -h IPorNameOfMySQLServer -u YourUserName -p –all-databases > mysql.backup
There’s a bit of MySQL tinkering, you can use these commands to peek a bit into your MySQL database, just so you know what the names of the databases are and what the names of the tables in the databases are.
The only thing different with the client-server approach on MySQL is the use “-h IpOfMySQLServer, the rest of the command behavior is the same. By the way, the mysql.backup file will be stored on the workstation where you executed the mysql client tools, not on the server–which is quite handy actually.
I can’t use mysql tools remotely, but it works if I use them on the server directly
This is a privileges problem most likely, not because you can use the mysql tools locally on the server, you will automatically be able to use them from a remote workstation. This is what you need to do on the server side (where MySQL server is installed) to ensure that you can use use mysql tools from a remote workstation.
YOu must know the password for the “root” user of MySQL, or use a mysql username that is equivalent to root.
$ mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON yourdbnamehere.* TO UserNameHere@’%’ IDENTIFIED BY ‘passwordhere’;
mysql>flush privileges;
The SQL command above is pretty easy to understand, it’s almost like conversational English. Here are some notes about the command.
- GRANT ALL PRIVILEGES – will grant all privileges like alter, create delete, drop etc, with the exception of GRANT and OPTION; but this pretty much makes the user a very powerful one
- yourdbnamehere.* – had you written *.* this will make the user very privileged on all the database instances defined inside the MySQL server. YourDBanamehere.* grants the user all privileges ONLY in a specific db, not all
- UserNameHere@’%’ – means that the user can login or use mysql tools from anywhere in the network; this is not good for production environment, use this only if you have too, inside a development environment. If don’t want the user be able to login remotely, but only locally, then use UserNameHere@’localhost’ instead
Using remote login
You could use SSH to login remotely to your database server, then login to mysql as if you’re local, then use SCP (secure copy) to copy mysql.backup from the server
local$ ssh shellUserName@ServerNameOrIPHere
server$ cd ~
server$ mysqldump -u root -p –all-databases > mysql.backup
server$ exit
local$ scp shellUserName@ServerNameOrIphere:/home/shellUserNameHere/mysql.backup .
shellUserName – is your Linux shell account, this is not your MySql username
If you want to show appreciation for my efforts dear reader, you could buy me a tall hazel nut Americano ($2) via PayPal. Thanks
Leave a comment