Mysql: Alternatives to Replication w/ Remote Dumps

If you are unable to get the proper permissions/access on your system because of a shared hosting environment then you may just want to dump your local database and send it to a remote server. This is a simple way to setup a daily copy.

1. Create user called ‘remote’ on the slave (destination for backup) that you can access from the master (the data that needs to be backed up)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to remote@'MASTERIPADDRESS' IDENTIFIED BY 'PASSHERE';
Query OK, 0 rows affected (0.07 sec)

mysql> flush privileges;

Note:
*REPLICATION CLIENT Gives the right to the user to ask where the slaves/masters are.
*REPLICATION SLAVE Needed for the replication slaves (to read binlogs from master).

IMPORTANT! – Make sure /etc/mysql/my.cnf bind-address is not 127.0.0.1 – if so comment. If change, you will need to restart mysql.

2. Verify you can connect from the master to the slave.

# mysql -h ipaddresstoslave -u remote -p

3. Create a script to run the commands.

#!/usr/bin/python
import os
import datetime
file='mysql-dump-all-%s.sql' % (datetime.date.today())
print "Creating backup file..."
os.system('mysqldump --all-databases -u jester_root -pREMOTEPASS > ~/'+file)
print "Dumping to titan..."
os.system('mysql -h IPTOSLAVE -u remote -pREMOTEPASS < ~/'+file)
print "Deleting local sql file..."
os.system('rm ~/'+file)

4. Add file to cron

Done!

Leave a Reply