Mysql – Setting automatic backups of MySQL database including table lock etc – MySQL Workbench

backupMySQL

We've got a Virtual Server with full ssh and root access running LAMP on Ubuntu Server.

I've got a small computer in the office running ubuntu that isn't being used for anything.
I thought I should use it to make and store (a copy of) our MySQL database backups. However, how do I go about backing up our databases?

MySQL Workbench locks tables to avoid errors, amongst a host of other features, but does not have any planned automatic backup bits planned. MySQL Dumper doesn't specify that they can keep the database consistent.
Source for information from this paragraph:

I don't see the point of running a php script on my server and emailing the backups when I have SSH access. Since backups are so important, I figure there must be a standard (or awesome) way of doing it I haven't found yet!

So how do I go about backing up our data? And, preferably, our users, config, etc too!

Best Answer

If you have MySQL installed on the Ubuntu machine, you can do the following:

Suppose IP of Virtual is 10.1.2.30

You can the following on the Ubuntu machine

BACKUP_FILE=/root/MySQLData.sql
MYSQL_HOST=10.1.2.30
MYSQL_USER=whateverusername
MYSQL_PASS=whateverpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} --all-databases > ${BACKUP_FILE}

You could crontab this on the Ubuntu machine as well

If you are looking for consistent mysqldumps, you could

use --single-transaction option if all the data is InnoDB
if all data is MyISAM, run FLUSH TABLES WITH READ LOCK; on the database
if data is MyISAM and InnoDB mixed, run FLUSH TABLES WITH READ LOCK; and --single-transaction on the database (See my post https://dba.stackexchange.com/questions/8587/mysql-db-backup-excluding-specific-tables/8613#8613 for some ideas on scripting that)

Give it a Try !!!

Related Solutions

Ssh – why use ssh tunneling for thesql server

If you use SSH tunneling, then your SSH and MySQL passwords are both encrypted during communication -- as well as all data that passes through the tunnel. If you're not using an SSH tunnel and you're not using MySQL's SSL support, there is no encrypting and a malicious third party could use packet sniffing or other nefarious means to eavesdrop on your communications.

Also, if you're using SSH tunneling, then you will be alerted if the remote machine's SSH key changes. This will not happen with standard MySQL, meaning an attacker could use IP spoofing or a Man in the Middle attack to disrupt/interfere/eavesdrop on your communications.

For more information, see:

Ssh – Forwarding port 3306 on Mac OS X in order to connect to a remote MySQL Database

This should work:

ssh -L 3310:127.0.0.1:3306 user@server

The first port number is the local port to use (must not be in use already), the IP in between the colons is the IP to connect to, from the perspective of the host you are sshing into. The second port number is the port to connect to. If you are connecting to a server other than the one you are sshing into, then try the following:

ssh -L 3310:<remote-IP>:3306 user@server

Usually, when I want to do this more than one time, I create an entry in ~/.ssh/config like the following where sshhost.example.com is the server I am sshing to, the local port I want to open is 5910, the IP on the other side I want to connect to is 192.168.35.69, and the port I want to connect to there is 5900:

Host desk
    Hostname sshhost.example.com
    User jed
    Port 22
    LocalForward 5910 192.168.35.69:5900

Then from a command prompt I can just do

jed@jed-mbp:~$ ssh desk

and get a tunnel to my desktop at the office.

Good luck,

--jed

Best Answer

Related Solutions

Ssh – why use ssh tunneling for thesql server

Ssh – Forwarding port 3306 on Mac OS X in order to connect to a remote MySQL Database

Related Topic