Mysqldump - -- Connecting to localhost

MySQL & MariaDB Percona Server for MySQL 8.0
1

I am using mysqldump to dump my databases, but I face a problem, the dump freeze for around 30-40 seconds on – Connecting to localhost…, then the process continue normally

I did not face this issue with my other server that still running MySQL 5.7

1 Like
2

When mysqldump freezes, what does the output say right before this happens?

1 Like
3

if you mean mysql error log file, nothing written there before start the dump, it just freeze for around 30 seconds then continue the dump

1 Like
4

Can you post the command that you use for mysqldump?
Also, when you say that the mysqldump is stuck in connecting to localhost for 30-40 seconds, is it possible for you to check the processlist of mysql in another session during that time?
That would let us know what is happening with the server when it is trying to authenticate.

1 Like
5

backup command I am using is

mysqldump --login-path=local --opt --set-gtid-purged=OFF -v DATABASE | gzip > DATABASE.gz

and I found this in the full processlist command output

| 314393 | root | localhost | NULL | Query | 13 | checking permissions | SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘UNDO LOG’ AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘DATAFILE’ AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (‘asp_tirehc_2160642’))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME | 12763 | 0 | 0 |

1 Like
6

It seems like you are hitting this bug,

https://bugs.mysql.com/bug.php?id=93875

So it seems like this problem occurs where the tables are more than your other instance where you don’t see this problem. The bug is that the query scans many rows when there are a lot of tables in the database. It is not indexed so the query performance is bad.
This was fixed in 8.0 by improving the query to not scan all the rows.
Other alternative would be to use mydumper which is more advanced version of mysqldump,

If you want to take a look here,

2 Likes
7

Thanks for your reply, but I am using this version of Percona
8.0.27-18 Percona Server (GPL), Release ‘18’, Revision ‘24801e21b45’

and the problem still exist,
I am discovering mydumper and it seems it is perfect

1 Like
8

Oh, I though you mentioned that you were on 5.7.

Can you check your mydumper version?
mysqldump --version

And yes, mydumper is a better option than mysqldump. It allows you to back up in multiple threads. Restore is faster as well with myloader.

1 Like