When you want to backup your mysql databases, you usually do mysqldump ... --all-databases or mysqldump ... --databases mysql ... but you end up with the whole mysql table which is a pain to insert back when you need it because it can mess up the root password or the debian-sys-maint user...

If you wish to just backup all the users and privileges other than root and debian-sys-maint, you can use this command:

mysqldump -nt -uroot -p -w"User NOT LIKE 'root' AND User NOT LIKE 'debian%'" mysql user db > users_privs.sql

Here's an explanation of each of the options:

• -nt: Do not add "drop table" and "create table".
• -uroot -p: Connect as root and ask for a password
• -w...: Add a "WHERE" condition to each query. We exclude everything related to root and debian-sys-maint.
• mysql user db: Dump the user and db tables from the mysql database.
• > users_privs.sql: Store the sql dump into the users_privs.sql file.

A new version of your operating system just got released and you want to have a fresh new install, or you want to migrate all of your data to another machine. There are so much stuff to backup that you don't even know where to start?
I will try to list the most common stuff (on a web-server) to backup or copy somewhere when you want to do a server migration.

Read the rest of this entry »

I recently scheduled some backup tasks on my VPS using backup-manager which is a neat program for this job. Everything is fine for that.
Then I wanted to set up a cron on my home computer to download the backup archives everyday. This wasn't as easy as it sounded at first.
You might even get those errors:

Permission denied, please try again.
Permission denied, please try again.
Permission denied (publickey,password).
rsync: connection unexpectedly closed (0 bytes received so far) [receiver]
rsync error: unexplained error (code 255) at io.c(635) [receiver=3.0.3]

I need to connect to my VPS using a SSH key that is protected by a passphrase (password). This ssh key is added to my ssh agent (at login time).

The main problem is that cron is run using a restricted environment, meaning it doesn't give environment variables like SSH_AUTH_SOCK.
This variable is needed by the ssh client in order to communicate with the ssh agent that will provide the information on the ssh key.

Here's a workaround for it. It's a quite secure way to fix this.
Read the rest of this entry »