1

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?

11
  • Additional information request. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; F) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, for server workload tuning analysis to provide suggestions. Jan 28 at 23:27
  • 1
    @WilsonHauck It's not a server. I'm running on my laptop with 256GB SSD, 8GB RAM, 2 cores 4 threads. Show global status : pastebin.com/KzVm7DjW show global variables : pastebin.com/FbLuVppc Jan 29 at 0:43
  • Your laptop is capable of filling the role of a server. Not a problem. Any chance you could provide D) and F)? For a Windows version of MySQLTuner this URL will take you to a source. github.com/pmachapman/mysqltuner Your analysis is in process and would be more complete if you could provide D) and F). Thanks for posting B) and C). Jan 29 at 7:13
  • 1
    @WilsonHauck I couldn't log in to database with MySQLTuner because it says the password encryption method is not supported. The show full processlist returns this pastebin.com/mFpxDhSN Jan 31 at 1:08
  • 1
    @WilsonHauck I haven't try that yet. I'm working on an export to csv since I think this issue might take a deep venture to fix. I will first try to get the mysqltuner to work then try to modify the .ini file. Thanks for helping. Jan 31 at 11:38
0

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.

3
  • I just tried this settings. No luck with backing up tho. As for my daily tasks (contains mostly select) it seems that my CPU has already bottlenecked the entire process. It improves my 2.7s/iter loop to 2.5s/iter. Then the CPU has already maxed out with only 5% usage on ram and 1% on disk. Thanks for the suggestions tho. Feb 2 at 8:08
  • Would you flexibility to use Skype Talk with me for 5 minutes? SkypeID is wlhauck at aol.com Feb 2 at 13:28
  • Yes but maybe a few days later. Got a lot of things to work on lately. Will add you when I'm done with the works. Feb 4 at 3:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.