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
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.
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:
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
FROM information_schema.TABLES
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?
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
