I have to set up a backup strategy. I choosed innobackupex to do so running on Debian 6 Squeeze. There are two servers, The production server and a backup server (that should work if the production server crashes). There is no replication, I use rsync to transfer the backups. I have a php script that looks in a conf file to know when it have to do backups.
My question is: How the skip the mysql database or the users table with innobackupex ?
On the master : I do the following commands :
innobackupex --user=root --password=xxx --no-timestamp /opt/backups/full/ rsync -avz --progress -e 'ssh -i -p 1000 ' /opt/backups/full/ user@xxx:/home/backups/full/
this works fine
On the backup server, I have just to prepare and restore the files :
innobackupex --apply-log --redo-only --user=xxx --password=xxx /home/backups/full/ innobackupex --copy-back /home/backups/full --user=root --password=xxx
Everything is alright but on the backup server, the root user's password changed and even the debian-sys-maint password.
The root users password becomes the one from the master.
I did a script to correct this.
The debian-sys-maint password is written in clear in the /etc/mysql/debian.cnf file so a extract it but in php (I use a pdo object) I can't change this password so I can't restart the mysql server.
Sometimes I can't retrieve the password of root user it's not master servers root password.
Sometimes I can stop/start mysql with /etc/init.d/mysql stop/start, sometimes with service mysql stop/start it this doesn't works I try with : mysqladmin -u root -p shutdown (if I could change the password)
If really I can't stop mysql I do killall mysql ( I know it's wrong)
and then I change the root password :
/usr/bin/mysqld_safe --skip-grant-tables & mysql --user=root mysql
Do someone had a problem similar to my ? How the skip the mysql database with innobackupex ?
The innobackupex tool is part of Percona XtraBackup. I work for Percona and I have developed training on Percona XtraBackup.
There are options for innobackupex to back up specific databases by name or by a regular expression. @YaK gives one option, or you can see other options here: http://www.percona.com/doc/percona-xtrabackup/innobackupex/partial_backups_innobackupex.html
However, --copy-back assumes you're restoring a full backup to an empty datadir. I.e. if the destination directory is not empty, --copy-back will give an error and refuse to overwrite files.
If you are trying to restore InnoDB tables to an instance where you already have a mysql database, you'll have to do the restore manually. This can be as simple as using mv of the files into your existing datadir (with the MySQL server shut down of course). Also remember to use chown mysql:mysql on the files before you start mysqld.
PS: You don't need to use --redo-only before you restore. That option is for doing incremental backups, and even then you'd skip that option before you do the final restore.