0

We've encountered several times now a scenario that causes our MySQL database to become corrupted when we attempt to restore a mysqldump file. We've used the same procedures (via batch scripts) for several years without problem, and only recently started encountering the issue in the last few months.

We're running MySQL Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu 16.04.2 LTS) and are restoring two MyISAM tables with ~350K records (combined).

We use the following cmd script to backup the tables from our internal and restore to the remote database server:

"%mysql_path%\mysqldump.exe" --user=root --password="%PWD%" --host=%source% ourdbname --tables table1 table2 | "%mysql_path%\mysql.exe" -uroot --password="%PWD%" --host=%dbtarget% -C ourdbname

Where the %% variables are supplied as part of the larger batch script. Basically we're piping the output of the dump to the mysql.exe to restore on the remote server. The script is run on a Windows 10 machine, but both source and destination databases are on Linux.

The restore part is where the database gets corrupted, which causes the DB server CPU usage to spike to 100% per core. The only way we have found to fix this issue is to kill the mysql process, delete the .frm, .MYD, .MYI files then start mysql and restore the tables locally (scp'd the mysqldump file to the server and restore).

We've found that the database only gets corrupted when we have active traffic on the website. So, we could take the site down (separate server running Java connecting via JDBC), run the script and then bring the site back online and it works 100% of the time. Obviously this is not ideal, but is the only way we can do it now.

Any ideas what might be causing this? Suggestions on how to work around it without having to bring the front-end server down?

Improve this question
5
  • dba.stackexchange.com/questions/19532/…
    – user9517
    Dec 12, 2017 at 7:12
  • I doubt anything is genuinely getting "corrupted." With MyISAM, all locks are table level locks, so you probably have an avalanche of lock waits as the web server starts piling on new queries. You haven't mentioned SHOE PROCESSLIST or anything from the error log.
    – Michael - sqlbot
    Dec 13, 2017 at 1:11
  • You really should be copying these tables over with different names, and then doing atomic renames.
    – Michael - sqlbot
    Dec 13, 2017 at 1:18
  • When the corruption occurs all CPUs are pegged 100% and I cannot show processlist. If I restart mysql it just immediately jumps to 100% again. Only after deleting files can I start mysql without CPU maxing out. I like the idea of copying tables over with a different name, can I rename the tables during the mysqldump?
    – Abram
    Dec 13, 2017 at 2:38
  • Apparently you can't rename during mysqldump, but I'm altering my scripts to create staging tables and will dump, then restore/rename those on the destination server.
    – Abram
    Dec 13, 2017 at 2:52

1 Answer 1

Reset to default
2

It seems that you are using MyISAM as the database engine for your site. I recommend using InnoDB, it is better in so many ways.

However, the main problem here is that your web application is most likely writing to the database at the same time as your backup is being written to the database. Depending on the actual table schema, this can cause all kinds of bad effects. Using MyISAM only makes it works, since there is no row-level locking in MyISAM if I remember correctly.

So, using InnoDB could help with this, but then again, it might not. The only safe option could be to simply stop the front-end server. Another option is to make an API for the remote application which would safely write the incoming data to the database.

Improve this answer
2
  • Thanks for the response. The web application only reads from those tables, never writes. It also largely never writes to any tables (except for admins that log in to manage user accounts). I will switch to InnoDB and see if that helps any (was under impression that MyISAM was better in this case).
    – Abram
    Dec 12, 2017 at 18:13
  • 1
    "your web application is most likely writing to the database at the same time as your backup is being written to the database" ...or even reading. Neither of these would cause corruption, but both would cause locks. MyISAM tables only have table-level locks, and reads are locking, too. They don't block other reads but they will block writes.
    – Michael - sqlbot
    Dec 13, 2017 at 1:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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