MySQL database backup stuck on 'checking permissions'

Question

I'm trying to backup a database on local machine. But the file is only 30MB after hours. Then I thought the hard drive corrupted or something that I tried this on another table and it worked fine.

Even MySQL workbench crashes when I connect to the database. So I used a python connector to print out the size of the databases. Sizes are in MB.

[('mysql', Decimal('18.5')),
 ('information_schema', Decimal('0.0')),
 ('performance_schema', Decimal('0.0')),
 ('sys', Decimal('0.0')),
 ('stocks', Decimal('2875.5')),
 ('test', Decimal('1183.9'))]

The stocks one is the one that got stucked on the checking permissions state. When I export the test it took only 40 mins. Then I went back again to try to export the stocks but still no luck.

I used show processlist to find out what happened

+----+-----------------+----------------+--------+---------+--------+------------------------+------------------------------------------------------------------------------------------------------+
| Id | User            | Host           | db     | Command | Time   | State                  | Info                                                                                                 |
+----+-----------------+----------------+--------+---------+--------+------------------------+------------------------------------------------------------------------------------------------------+
|  5 | event_scheduler | localhost      | NULL   | Daemon  | 209821 | Waiting on empty queue | NULL                                                                                                 |
| 77 | root            | localhost:3753 | stocks | Sleep   |   1479 |                        | NULL                                                                                                 |
| 84 | root            | localhost:4289 | stocks | Query   |      0 | init                   | show processlist                                                                                     |
| 86 | root            | localhost:4612 | NULL   | Query   |    442 | checking permissions   | SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SC |
+----+-----------------+----------------+--------+---------+--------+------------------------+------------------------------------------------------------------------------------------------------+

Well at least when I was trying to backup the test it doesn't stuck there.

I used the following command to backup the database

mysqldump -u root -p stocks > stocks.db

When I kill the process the error message was this

mysqldump: Error: 'Lost connection to MySQL server during query' when trying to dump tablespaces
mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'ndbinfo\_version'': MySQL server has gone away (2006)

The test has about 4k tables and the stocks has 20k tables. Is that what's causing this issue? Any idea what happened? Or is there any other ways I can backup the database?

Answer 1

Rate Per Second = RPS

Suggestions to consider for your my.ini [mysqld] section

innodb_buffer_pool_size=1G  # from 8M for a much better experience
innodb_log_buffer_size=16M  # from 1M to support ~ 30 mn in RAM before write to media
open_files_limit=60000  # from 6209 to better support your opened_* high volume
innodb_open_files=48000  # from 300 to reduce opened_tables RPhr of 24,310
table_open_cache=48000  # from 2000 should always be matched to innodb_open_files
table_definition_cache=6000  # from 1400 to support opened_table_definitions RPhr of 2,152
innodb_io_capacity=1000  # from 200 to use more of your available SSD io capacity

These are just the beginning of tuning for your serious workload. View profile, Network profile for additional suggestions and free downloadable Utility Scripts to assist with performance tuning.