Percona XtraBackup 2.4 to populate a secondary server. Centos 7 MySQL 5.7.43 to Ubuntu 22 MySQL 5.7.43

MySQL & MariaDB Percona XtraBackup
1

Aim: Populate a new Ubuntu 22 Mysql 5.7.43 server with a Percona XtraBackup 2.4 of a CentOS7 MySQL 5.7.43 server with no downtime.

I run through the steps to make a full backup, transfer it, prepare it, and restore it, but the mysql service won’t restart and logs no errors.

Prior to the backup the service starts fine.
I’ve confirmed the md5 of the backup.

Here is the process I used:

Install percona XtraBackup on Linode
sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm sudo yum install percona-xtrabackup-24

Create a hot backup (sudo due to core mysql file only being root accessible)
sudo xtrabackup --backup --no-timestamp --user=root --password='password' --target-dir=/home/user/backup

Own it to the current user
sudo chown -R user:group ~/backup sudo chmod -R u+rw ~/backup

Compress the backup
tar -cpzvf backup.tar.gz -C ~/backup .

Upload compressed backup to s3
aws s3 cp backup.tar.gz s3://s3Address/Backup.tar.gz

Login to private-ec2

Setup MySQL
Start service
sudo service mysql start

Initialise the EC2 db
sudo mysql_secure_installation

Download the backup from S3
aws s3 cp s3://eu-w2-jmtest-s3-01/backup.tar.gz ~/backup/backup.tar.gz

Uncompress backup
tar -xpzvf ~/backup/backup.tar.gz -C ~/backup

Prepare backup
xtrabackup --prepare --target-dir=/home/user/backup

Install Percona XtraBackup on private-ec2
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb sudo apt-get update sudo apt-get install percona-release sudo apt-get update sudo apt-get install percona-xtrabackup-24

Stop the MySQL service
sudo service mysql stop

Empty /var/lib/mysql
sudo rm -rf /var/lib/mysql/

sudo mkdir /var/lib/mysql
sudo chown -R mysql:mysql /var/lib/mysql`

Restore the backup (again with root filepaths)
sudo xtrabackup --copy-back --target-dir=/home/user/backup

Adjust permissions
sudo chown -R mysql:mysql /var/lib/mysql sudo chmod -R 700 /var/lib/mysql sudo chmod -R 600 /var/lib/mysql

Restart the mysql service [HERE IS WHER IT BLOWS UP]
sudo service mysql start

Login to mysql to check the restore worked:
mysql -u root -p

2

Hi @jaye welcome to Percona Community,

Would you like to share the journalctl -xe, error log, my.cnf? If you want me to guess wildly, firewall?

Thanks,
K

1 Like
3

Hi @jaye

I think your issue is related to the privileges you’re using for the datadir.
Keep in mind you’re removing the execute from the folders in your datadir.

To validate this, I did a quick test on a local environment:

MySQL was running, and this is the privileges in the datadir:

$ sudo ls -ld /var/lib/mysql
drwxr-x--x. 12 mysql mysql 4096 Jun 30 22:39 /var/lib/mysql

I stopped my process, changed to 600 (I didn’t use the -R, otherwise it would be harder to rollback):

$ sudo chmod 600 /var/lib/mysql
$ sudo ls -ld /var/lib/mysql
drw-------. 12 mysql mysql 4096 Jun 30 22:41 /var/lib/mysql

Tried to start my process again:

$ sudo systemctl start mysql
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

And indeed, nothing was printed in my error log either.

Lastly, I changed back to 700:

$ sudo chmod 700 /var/lib/mysql
$ sudo ls -ld /var/lib/mysql
drwx------. 12 mysql mysql 4096 Jun 30 22:43 /var/lib/mysql

And MySQL was able to start:

$ sudo systemctl start mysql
$ sudo systemctl status mysql
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2023-06-30 22:50:16 UTC; 1s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1599 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 1576 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 1602 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─1602 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jun 30 22:50:15 localhost.localdomain systemd[1]: Starting MySQL Server...
Jun 30 22:50:16 localhost.localdomain systemd[1]: Started MySQL Server.

This is the command you used that caused this (Last command from Adjust permissions):

sudo chmod -R 600 /var/lib/mysql

To fix your privileges, you should can try it like this:

Change all the directories to 700
sudo find /var/lib/mysql -type d -exec chmod 700 {} \;

Change all the files to 600
sudo find /var/lib/mysql -type f -exec chmod 600 {} \;

I hope this helps you.
Best,
Mauricio.

2 Likes
4

Thank you so much! That fixed it!