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?
show full processlist
returns this pastebin.com/mFpxDhSN