Restoring changes password in backup server

  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    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: ​http://www.percona.com/doc/percona-x...obackupex.html
    Scott Nemes