0

After i followed the steps mentioned on the DigitalOcean https://www.digitalocean.com/community/tutorials/how-to-create-hot-backups-of-mysql-databases-with-percona-xtrabackup-on-ubuntu-14-04 ... i coudldn't achive the expected result.

I am using a Server that has its binary-log option enabled

log_bin = /var/log/mysql/mariadb-bin

log_bin_index = /var/log/mysql/mariadb-bin.index

i do the following to backup my database using innobackupex command as follows

innobackupex --user=$username  --password=$password --no-timestamp /data/backups/new_backup

then i do apply-log to apply the copied transaction

innobackupex --apply-log /data/backups/new_backup

But once i:

1- stop my mysql server

2- move my /var/lib/mysql/* to /tmp/mysql/

3- restore (plus changing ownership) my backup using sudo innobackupex --copy-back /data/backups/new_backup

i couldn't start mysql service again because the /var/lib/mysql/ now doesn't have the binary log mysql-bin.xxxxxx.

here is what my /var/lib/mysql/ directory looks like

user@pc1:~$ ll /var/lib/mysql/
total 1205264
drwxr-xr-x  6 mysql mysql      4096 Jan 13 01:19 ./
drwxr-xr-x 45 root  root       4096 Oct 10 14:13 ../
drwx------  2 mysql mysql     73728 Jan  8 05:02 1bd3e0294da19198/
-rw-rw----  1 mysql mysql     16384 Jan  8 05:09 aria_log.00000001
-rw-rw----  1 mysql mysql        52 Jan  8 05:09 aria_log_control
-rw-r--r--  1 root  root          0 Oct 10 14:10 debian-10.0.flag
-rw-rw----  1 mysql mysql  79691776 Feb 11 06:22 ibdata1
-rw-rw----  1 mysql mysql 536870912 Feb 11 06:22 ib_logfile0
-rw-rw----  1 mysql mysql 536870912 Oct 10 14:14 ib_logfile1
-rw-rw----  1 mysql mysql         0 Oct 10 14:10 multi-master.info
drwxr-xr-x  2 mysql root       4096 Oct 10 14:10 mysql/
-rw-rw----  1 mysql mysql  45143857 Jan  8 04:54 mysql-bin.000002
-rw-rw----  1 mysql mysql       728 Jan  8 04:55 mysql-bin.000003
-rw-rw----  1 mysql mysql  35315670 Jan  8 05:08 mysql-bin.000004
-rw-rw----  1 mysql mysql      2513 Jan  8 05:09 mysql-bin.000005
-rw-rw----  1 mysql mysql     32133 Jan 12 23:43 mysql-bin.000006
-rw-rw----  1 mysql mysql     46855 Feb 11 06:22 mysql-bin.000007
-rw-rw----  1 mysql mysql         5 Jan 13 01:19 mysql.pid
-rw-rw----  1 mysql mysql      1961 Jan 13 01:19 mysql-slow.log
-rw-------  1 root  root         15 Oct 10 14:10 mysql_upgrade_info
drwx------  2 mysql mysql      4096 Oct 10 14:10 performance_schema/

and here is it after restoration

user@pc1:~$ ll /var/lib/mysql/
total 1147036
drwxr-x--- 6 mysql mysql      4096 Feb 11 06:33 ./
drwxr-xr-x 3 mysql mysql      4096 Feb 11 06:31 ../
drwxr-x--- 2 mysql mysql     65536 Feb 11 06:33 1bd3e0294da19198/
-rw-r----- 1 mysql mysql       419 Feb 11 06:33 backup-my.cnf
-rw-r----- 1 mysql mysql  79691776 Feb 11 06:33 ibdata1
-rw-r----- 1 mysql mysql 536870912 Feb 11 06:33 ib_logfile0
-rw-r----- 1 mysql mysql 536870912 Feb 11 06:33 ib_logfile1
-rw-r----- 1 mysql mysql  12582912 Feb 11 06:33 ibtmp1
drwxr-x--- 2 mysql mysql      4096 Feb 11 06:33 mysql/
drwxr-x--- 2 mysql mysql      4096 Feb 11 06:33 performance_schema/
-rw-r----- 1 mysql mysql        33 Feb 11 06:33 xtrabackup_binlog_info
-rw-rw-r-- 1 mysql mysql        38 Feb 11 06:33 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql       115 Feb 11 06:33 xtrabackup_checkpoints
-rw-r----- 1 mysql mysql       552 Feb 11 06:33 xtrabackup_info
-rw-r----- 1 mysql mysql   8388608 Feb 11 06:33 xtrabackup_logfile

Currently i am not doing replication using Master-Slave but i want the binary logs, my case only worked when i copy mysql-bin.xxxxx from the /tmp/mysql/ to the /var/lib/mysql/ for mysql service to be able to start !, Or when i disable the log-option and do manually some stuffs like changing /var/log/mysql/mariadb-bin.index to point to mysql-bin.000001 which i think it is complicated and not what xtrabackup is built for.

Do i have to disable binary-log option ?

Do i have to rsync the binary-log files beside the database?

How could i reconstruct the binary-log files from the restored version ?

Update [solved]

Thanks to hmkael and Michael Coburn

Michael Coburn mentioned that: since i was using MariaDB i had to use MariaDB Backup instead of Percona XtraBackup, xtrabackup and or its replication would not copy binary logs.

more information in the link below:

https://www.percona.com/forums/questions-discussions/percona-xtrabackup/50864-i-failed-how-to-setup-a-slave-for-replication-please-help

0

If your backup is from a server with binary logging enabled, xtrabackup will create a file named xtrabackup_binlog_info in the target directory. This file contains the binary log file name and position of the exact point in the binary log to which the prepared backup corresponds.

You have to copy this binary log back to the original folder before start your MySQL server

  • Thanks a lot @hmkael ... i already mentioned that "my case only worked when i copy mysql-bin.xxxxx from the /tmp/mysql/ to the /var/lib/mysql/ for mysql service to be able to start ".... However, what if the master server was damaged (no more binary logs), i was planning to rsync the backup directory but in this case there is no more binary logs to be retrieved (xtrabackup did point to the binary log index but didn't copy the binary log files)... should i also rsync these binary logs too ? – ibrasec Feb 14 '18 at 19:49
  • unfortunately xtrabackup won't copy the binary log back, you have to copy back only the binary log mentioned in xtrabackup_binlog_info from your /tmp/mysql/ to /var/lib/mysql/ in order for mysql service to be able to start, not all mysql-bin.xxxxx – Mikael HOUNDEGNON Feb 14 '18 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.