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
%% 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?
SHOE PROCESSLISTor anything from the error log.
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?