Xtrabackup first time replication failing on slave side

MySQL & MariaDB Percona XtraBackup
1

Hi All,

I’ve followed numerous guides to set up a simple master slave replication on 2 Ubuntu servers running Mysql v8. I have v8 of Xtrabackup too:

xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)

The process I did was basically:

ON MASTER:

sudo xtrabackup --backup --user=XYZ --password=ABC --target-dir=/backupfolderonmaster

sudo xtrabackup --prepare --target-dir=/backupfolderonmaster

rsync -avP /backupfolderonmaster/ USER@SLAVESERVERIP:/backupfolderonslave/

ON SLAVE:

sudo systemctl stop mysql

rm -rf /var/lib/mysql/*

sudo xtrabackup --copy-back --target-dir=/backupfolderonslave

THIS IS MY FIRST PROBLEM ==>

root@SLAVESERVER:~# sudo xtrabackup --copy-back --target-dir=/backupfolderonslave
*2024-03-26T00:30:56.407087-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --copy-back=1 --target-dir=/backupfolderonslave *
xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)
2024-03-26T00:30:56.407151-00:00 0 [ERROR] [MY-011825] [Xtrabackup] datadir must be specified.

So I try with:

sudo xtrabackup --copy-back --target-dir=/backupfolderonslave/ --datadir=/var/lib/mysql/

This copies files across and seems to be happy.

2024-03-26T00:00:50.418982-00:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

I then do this:

chown -R mysql:mysql /var/lib/mysql

but then MY SECOND PROBLEM ==>

root@SLAVESERVER:~# service mysql start
Job for mysql.service failed because the control process exited with error code.
See “systemctl status mysql.service” and “journalctl -xeu mysql.service” for details.

I then run the following:

root@SLAVESERVER:~# journalctl -xeu mysql.service
░░ A start job for unit mysql.service has begun execution.
*░░ *
░░ The job identifier is 45068.
Mar 26 00:50:24 SLAVESERVERIP mysql-systemd-start[2862328]: MySQL system database not found in /var/lib/mysql. Please run mysqld --initialize.
Mar 26 00:50:24 SLAVESERVERIP systemd[1]: mysql.service: Control process exited, code=exited, status=1/FAILURE

Finally I run:

root@SLAVESERVER:~# mysqld --initialize
root@SLAVESERVER:~# service mysql start

And MySQL starts on the SERVER but the server MySQL interface is asking me for my credentials.

“Warning! Webmin needs to know your MySQL administration login and password in order to manage your database. Please enter your administration username (usually root) and password below.”

Have I skipped a step or done something wrong? Just really confused why I’m being asked for MySQL details on the slave and what those details are. Is it something to do with the my.cnf file from the master?

Sorry for the potentially silly question but I have spent days trying to figure this out to no avail …

Thanks!

2

If you copied over a backup from source, you should not need to use this command.

Your rsync command could have copied it directly into the mysql datadir.

Did you copy the /etc/my.cnf from source over to replica?

Please look at mysql’s error log and not journalctl.

3

Thanks, I tried again from scratch and this time I made rsync send the files from the prepared Master backup to the Mysql datadir on the Slave (/var/lib/mysql) directly.

I also tried with copying the contents of /etc/mysql/my.cnf file from Master to Slave.

Still I have a problem in trying to start Mysql and when I look at the logs I see the suggestion of running " mysqld --initialize" on the Slave. When I look at Mysql logs I see nothing logged because I have stopped mysql before and no new messages are logged. I did however find this in syslog:

mysql-systemd-start[3589017]: MySQL system database not found in /var/lib/mysql. Please run mysqld --initialize.

I dont understand what could be going wrong? Is there another step I need to do? Is there a config file missing from Master to Slave? do I need to copy all the contents manually from the etc/mysql/ directory from master to slave too? … I’m lost at the moment so any more suggestions would be greatly appreciated.

4

Did you chown -R mysql:mysql /var/lib/mysql before you started? It’s very strange that you have data in /var/lib/mysql but “mysql” cannot see it. Please provide ls -la /var/lib/mysql/

5

Thanks again - yes I did change the permissions on the SLAVE with:

chown -R mysql:mysql /var/lib/mysql

This is what i did exactly:

  1. Creating a backup on the MASTER and storing it in a directory (/backupzpercona).
  2. Preparing the backup on the MASTER with xtrabackup --prepare.
  3. Stopping the MySQL service on the SLAVE.
    4)Transferring that backup using rsync to the /var/lib/mysql/ directory on the SLAVE.
  4. changed directory ownership on SLAVE with chown -R mysql:mysql /var/lib/mysql
  5. tried starting mysql server on SLAVE but that failed.

Here is the output of ls for the slave and master servers:

SLAVESERVER

root@SLAVESERVER:~# ls -la /var/lib/mysql/
total 1040488
drwxr-x— 2 mysql mysql 4096 Mar 26 23:13 ‘#innodb_redo’
drwxrwxrwx 11 mysql mysql 4096 Mar 26 23:13 .
drwxr-xr-x 70 root root 4096 Mar 26 23:13 …
-rw-r----- 1 mysql mysql 447 Mar 26 23:04 backup-my.cnf
drwxr-x— 2 mysql mysql 4096 Mar 26 23:04 dbthoruk
drwxr-x— 2 mysql mysql 4096 Mar 26 23:04 dbthoruk_b
drwxr-x— 2 mysql mysql 4096 Mar 26 23:04 dbthoruk_c
drwxr-x— 2 mysql mysql 4096 Mar 26 23:04 dbthoruk_r
drwxr-x— 2 mysql mysql 4096 Mar 26 23:04 dbthoruk_v
-rw-r----- 1 mysql mysql 13222 Mar 26 23:04 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 Mar 26 23:13 ibdata1
-rw-r----- 1 mysql mysql 12582912 Mar 26 23:13 ibtmp1
-rw-r----- 1 mysql mysql 157 Mar 26 23:04 mysql-bin.000248
-rw-r----- 1 mysql mysql 32 Mar 26 23:04 mysql-bin.index
-rw-r----- 1 mysql mysql 33554432 Mar 26 23:13 mysql.ibd
drwxr-x— 2 mysql mysql 4096 Mar 26 23:04 performance_schema
drwxr-x— 2 mysql mysql 12288 Mar 26 23:04 prod_dbthoruk
drwxr-x— 2 mysql mysql 4096 Mar 26 23:03 sys
-rw-r----- 1 mysql mysql 452984832 Mar 26 23:13 undo_001
-rw-r----- 1 mysql mysql 452984832 Mar 26 23:13 undo_002
-rw-r----- 1 mysql mysql 21 Mar 26 23:04 xtrabackup_binlog_info
-rw-r----- 1 mysql mysql 149 Mar 26 23:13 xtrabackup_checkpoints
-rw-r----- 1 mysql mysql 506 Mar 26 23:04 xtrabackup_info
-rw-r----- 1 mysql mysql 33554432 Mar 26 23:13 xtrabackup_logfile
-rw-r----- 1 mysql mysql 39 Mar 26 23:13 xtrabackup_tablespaces

MASTERSERVER (ls of prepared backup directory using xtrabackup)

root@MASTERSERVER:~# ls -la /backupzpercona
total 1040480
drwxr-x— 2 root root 4096 Mar 26 23:13 ‘#innodb_redo’
drwxrwxrwx 11 root root 4096 Mar 26 23:13 .
drwxr-xr-x 20 root root 4096 Mar 22 01:26 …
-rw-r----- 1 root root 447 Mar 26 23:04 backup-my.cnf
drwxr-x— 2 root root 4096 Mar 26 23:04 dbthoruk
drwxr-x— 2 root root 4096 Mar 26 23:04 dbthoruk_b
drwxr-x— 2 root root 4096 Mar 26 23:04 dbthoruk_c
drwxr-x— 2 root root 4096 Mar 26 23:04 dbthoruk_r
drwxr-x— 2 root root 4096 Mar 26 23:04 dbthoruk_v
-rw-r----- 1 root root 13222 Mar 26 23:04 ib_buffer_pool
-rw-r----- 1 root root 79691776 Mar 26 23:13 ibdata1
-rw-r----- 1 root root 12582912 Mar 26 23:13 ibtmp1
-rw-r----- 1 root root 157 Mar 26 23:04 mysql-bin.000248
-rw-r----- 1 root root 32 Mar 26 23:04 mysql-bin.index
-rw-r----- 1 root root 33554432 Mar 26 23:13 mysql.ibd
drwxr-x— 2 root root 4096 Mar 26 23:04 performance_schema
drwxr-x— 2 root root 4096 Mar 26 23:04 prod_dbthoruk
drwxr-x— 2 root root 4096 Mar 26 23:03 sys
-rw-r----- 1 root root 452984832 Mar 26 23:13 undo_001
-rw-r----- 1 root root 452984832 Mar 26 23:13 undo_002
-rw-r----- 1 root root 21 Mar 26 23:04 xtrabackup_binlog_info
-rw-r----- 1 root root 149 Mar 26 23:13 xtrabackup_checkpoints
-rw-r----- 1 root root 506 Mar 26 23:04 xtrabackup_info
-rw-r----- 1 root root 33554432 Mar 26 23:13 xtrabackup_logfile
-rw-r----- 1 root root 39 Mar 26 23:13 xtrabackup_tablespaces

MASTERSERVER

root@MASTERSERVER:~# ls -la /var/lib/mysql/
total 1022052
-rw-r----- 1 mysql mysql 196608 Mar 27 14:33 ‘#ib_16384_0.dblwr’
-rw-r----- 1 mysql mysql 8585216 Mar 27 14:33 ‘#ib_16384_1.dblwr’
drwxr-x— 2 mysql mysql 4096 Mar 27 14:33 ‘#innodb_redo’
drwxr-x— 2 mysql mysql 4096 Mar 18 01:33 ‘#innodb_temp’
drwx------ 12 mysql mysql 69632 Mar 21 01:50 .
drwxr-xr-x 62 root root 4096 Feb 29 22:52 …
-rw-r----- 1 mysql mysql 56 May 17 2022 auto.cnf
-rw------- 1 mysql mysql 1676 May 17 2022 ca-key.pem
-rw-r–r-- 1 mysql mysql 1112 May 17 2022 ca.pem
-rw-r–r-- 1 mysql mysql 1112 May 17 2022 client-cert.pem
-rw------- 1 mysql mysql 1680 May 17 2022 client-key.pem
drwxr-x— 2 mysql mysql 12288 Nov 28 23:04 dbthoruk
drwxr-x— 2 mysql mysql 4096 Jul 25 2023 dbthoruk_b
drwxr-x— 2 mysql mysql 4096 Aug 18 2023 dbthoruk_c
drwxr-x— 2 mysql mysql 4096 Aug 17 2023 dbthoruk_r
drwxr-x— 2 mysql mysql 4096 Aug 19 2023 dbthoruk_v
-rw-r–r-- 1 mysql mysql 0 Jun 1 2023 debian-5.7.flag
-rw-r----- 1 mysql mysql 13222 Mar 16 15:11 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 Mar 27 14:33 ibdata1
-rw-r----- 1 mysql mysql 12582912 Mar 18 01:23 ibtmp1
-rw-r----- 1 mysql mysql 7 May 20 2022 localhost.pid
-rw-r----- 1 mysql mysql 33554432 Mar 27 14:33 mysql.ibd
-rw-r----- 1 mysql mysql 6 Jun 1 2023 mysql_upgrade_info
-rw-r----- 1 mysql mysql 176 Mar 21 01:06 mysqld-auto.cnf
drwxr-x— 2 mysql mysql 4096 Jun 1 2023 performance_schema
-rw------- 1 mysql mysql 1680 May 17 2022 private_key.pem
drwxr-x— 2 mysql mysql 12288 Mar 25 15:36 prod_dbthoruk
-rw-r–r-- 1 mysql mysql 452 May 17 2022 public_key.pem
-rw-r–r-- 1 mysql mysql 1112 May 17 2022 server-cert.pem
-rw------- 1 mysql mysql 1676 May 17 2022 server-key.pem
drwxr-x— 2 mysql mysql 4096 May 17 2022 sys
-rw-r----- 1 mysql adm 5779781 Sep 4 2023 dbthoruk.log
-rw-r----- 1 mysql mysql 7 Mar 18 01:23 dbthoruk.pid
-rw-r----- 1 mysql mysql 452984832 Mar 27 14:33 undo_001
-rw-r----- 1 mysql mysql 452984832 Mar 27 14:33 undo_002

Another thought, could apparmour be affecting anything?

root@SLAVESERVER:~# ls /etc/apparmor.d/ | grep -E ‘mysql|xtrabackup’
usr.sbin.mysqld

6

You did not mention that you first erased the entire datadir before doing the rsync. I want to verify that you did do that.

What was the output of your prepare step? Did it say “completed OK” at the end?

apparmour

Yes, I would just disable it entirety. If mysql then starts, you know that’s the issue and you’ll need to find a guide on how to fix that.

7

Thanks again for the suggestions. I had a nightmare last night but in the end it pointed me to a potential culprit here - the datadir of Mysql appears to have been corrupt somehow (perhaps through purging binary logs or something along those lines). I found this out the hard way when a reboot of the Master server would not allow Mysql to restart so the same problem as I was seeing on the slave.

I will now try this process again but fingers crossed should be better …