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:
