A bash script for dumping and restoring MySQL databases with one command.

Getting started

Client (backup) side

1. This tool should be configured on a backup server with a MySQL Client installed and running.

Ubuntu / Debian

$ apt-get install mysql-client

CentOS / RHEL

$ yum install mysql

2. We'll create a backup user on our MySQL server during the next step. Let's add its future details to the .my.cnf in our root dir.

$ sudo echo '[client]
> user=backup
> password=<password>' > /root/.my.cnf

This will prevent the MySQL Client from asking the server's user and password each time we connect.

Server (host) side

3. Add a backup user to your mysql server:

GRANT ALL PRIVILEGES ON *.* TO 'backup@<BACKUP_SERVER_IP>' IDENTIFIED BY '<password>';

Here, <password> is the one you defined on step 2.

4. So you can access your server remotely (backup --> host), comment out bind-address on the mysql server's configuration file:

$ sed -i 's|bind-address|#bind-address|' /etc/my.cnf

5. Reload the MySQL server:

$ service mysql reload

As far as the server is concerned, you're done.

Configuration

Create a .records file in your root directory. This is where getdb will find the target host's address.

Each host's entry on the .records file should have the following structure:

shorthand,hostname|IPADDRESS

e.g.

$ echo 'server1,server1.example.com|192.168.122.3' >> /root/.records

You can add several aliases for your host, seperating them with a comma:

$ echo 'server1,server1.example.com,sv1|192.168.122.3' >> /root/.records

Usage

Dumping

$ bash getdb dump <host> <options>

Your databases will be dumped on a /var/dbs/<server_hostname>/dump directory.

Dumping all databases from server1, which we added earlier to our .records file:

$ bash getdb dump server1 -A

Dumping a specific database from server1:

$ bash getdb dump server1 wordpress

Dumping several databases:

$ bash getdb dump server1 wordpress db2 db3

Restoring

Restoring is a work in progress. It's still buggy and needs lots of work.

$ bash getdb restore <host> <options>

Restoring the latest full dump:

$ bash getdb restore ifm -dp latest

Restoring the latest dump of a single database:

$ bash getdb restore ifm -n db_name -p latest

Restoring yesterday's latest dump:

$ bash getdb restore ifm -n db_name -p yesterday