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:
Best Answer
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