Restoring changes password in backup server

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 ?

After finishing and fixing my scripts, I’ll share them.

Take a look at the --databases option for innobackupex, i.e. --databases=“mydatabase”. As long as the “mysql” system tables are in MyISAM format, that flag should do the trick to exclude the system tables.

See: ​