A bash script for dumping and restoring MySQL databases with one command.
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.
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.
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
$ 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 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