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.