Hello to everyone,
I’m trying to take a backup with Xtrabackup, but MySQL Accesses are cut off during backup, MySQL continues to run when the backup is stopped or the backup is finished. I tried all the parameters on the Percona site, but no results. I did everything up to the IO checks, but I couldn’t find a solution. Has anyone had experience with this?
The MySQL version and an example of the command I have implemented are as follows.
mysql Ver 8.0.27-18 for Linux on x86_64 (Percona Server (GPL), Release ‘18’, Revision '24801e21b45
xtrabackup -u root -p --backup --datadir=/var/lib/mysql/ --target-dir=/dbbackup/ --parallel=4 --throttle=200 --use-memory=1G --safe-slave-backup --rebuild_threads=8
I also tried changing the parameters in the command I mentioned above, but there was no solution. Since there are too many websites on my servers, I need to take a backup without interrupting access. Since my MySQL databases are Innodb, I need to take a full and incremental backup.
I don’t understand this part. How is access cut off during the backup if you are running locally, and using the MySQL root account?
When I start the backup process, after a certain period of time, access to mysql is gone and sites do not open. The MySQL server is not responding, but the sites open as soon as I stop the backup process.
Hmm. Are you certain all tables are InnoDB? It’s possible that the backup process is competing against lots of other DML and metadata locks are blocking the system.
You can try the following flags to kill long running queries which might be interfering with the backup process:
--backup-lock-timeout=20 --backup-lock-retry-count=8 --ftwrl-wait-timeout=20
Thank you for the quick notification. I’ll try it as soon as possible and post a video response if the problem persists.
220419 09:49:50 Executing UNLOCK TABLES
220419 09:49:50 All tables unlocked
220419 09:49:50  Copying ib_buffer_pool to /dbbackup/full/ib_buffer_pool
220419 09:49:50  …done
220419 09:49:50 Backup created in directory ‘/dbbackup/full/’
220419 09:49:50  Writing /dbbackup/full/backup-my.cnf
220419 09:49:50  …done
220419 09:49:50  Writing /dbbackup/full/xtrabackup_info
220419 09:49:50  …done
xtrabackup: Transaction log of lsn (332567999181) to (332567999607) was copied.
220419 09:49:51 completed OK!
Hello again @matthewb , @daniil.bazhenov
I tried the backup process, I took a backup without any problems, I was taking it before, but the SQL accesses were gone in the backup process and the access was gone in the same way. The commands you sent did not help in interrupting the access, but when the backup process is finished, the issue that caught my attention is the unlocking of the tables. Is there any way to prevent this? Maybe it’s going out of access because it’s locked the table.
The table locks are there to prevent any DDLs from executing while the backup is taking place, and to synchronize the backup with the binary log. If you remove these locks, then you will have an inconsistent backup.
You need to start another backup, and in another terminal, run SHOW PROCESSLIST when you see access going away from the application. We need to see the processlist while the application is hung.
To double check all tables are InnoDB execute this query:
SELECT COUNT(*) AS Tables_Count,
CONCAT(ROUND(SUM(data_length) / ( 1024 * 1024 * 1024 ), 2), ‘G’) Data_Size,
CONCAT(ROUND(SUM(index_length) / ( 1024 * 1024 * 1024 ), 2), ‘G’) Index_Size,
CONCAT(SUM(ROUND(( data_length + index_length ) /
( 1024 * 1024 * 1024 ), 2)), ‘G’) Total_Size, engine AS Storage_Engine
WHERE TABLE_SCHEMA NOT IN (‘mysql’, ‘information_schema’, ‘performance_schema’, ‘sys’)
GROUP BY engine;
If not all tables are InnoDB then some locking will happen on the non transactional tables.
Also, check if the server itself is restarted with
show global status like ‘%uptime%’;
If uptime counter get reset everytime maybe the problem is not xtrabackup but the server crashing or restarting , causing disconnects to existing connections.
Last, what version of mysql/percona server and xtrabackup are you running?
| Tables_Count | Data_Size | Index_Size | Total_Size | Storage_Engine |
| 38194 | 9.02G | 3.96G | 9.94G | InnoDB |
1 row in set (7.77 sec)
There is no myisam table among the tables.
There is no interruption or restart of MySQL or server services.
MySQL Version: mysql Ver 8.0.27-18 for Linux on x86_64 (Percona Server (GPL), Release ‘18’, Revision ‘24801e21b45’)
Xtrabackup Version: xtrabackup version 8.0.27-19 based on MySQL server 8.0.27 Linux (x86_64) (revision id: 50dbc8dadda)
As you can see in this screenshot, it seems to be locked, which is a serious problem for me and means that I cannot take SQL backups on 150 servers. How we can solve this problem.
Your screenshot proves exactly what I said above. You are attempting to run DDL operations (CREATE TABLE, ALTER TABLE, etc) while you are also trying to take backups. It will be impossible to take consistent backups while DDL operations are happening.
Think about this like waiting in line at the coffee shop. There are 5 queries in line ahead of you. You (the backup) now get in line and you have to wait for everything in front of you to finish. At the same time, you must prevent anything else from going forward in order to create consistency. There’s a query behind you that wants to alter a table, but it cannot do that because you have said “everyone else needs to wait for me to finish and I have to wait for those before me to finish before I can start”
Does that make sense? What you are seeing is 100% normal and expected.
If you cannot stop the DDLs during the backup process then you have 2 options: 1) create inconsistent backups, or 2) take backups from a replica
Thank you for your quick return, I will be sharing the result here after making the necessary checks.