1
I am running a web application within a shared hosting environment which uses a MYSQL database which is about 3GB large.
For testing purposes I have setup a XAMPP environment on my local macOS machine. To copy the online DB to my local machine I used mysqldump
on the server and than directly imported the dump file to mysql
:
// Server
$ mysqldump -alv -h127.0.0.3 --default-character-set=utf8 -u dbUser -p'dbPass' --extended-insert dbName > dbDump.sql
// Local machine
$ mysql -h 127.0.0.3 -u dbUser -p'dbPass' dbName < dbDump.sql
The only optimization here is the use of extended-insert
. However the import takes about 10 hours!
The dumb file already includes commands to disable unique and foreign key checks to speed up the import:
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
I am not an SQL export and not sure if the /*!40014
commands are executed or not, so added SET FOREIGN_KEY_CHECKS=0;
manually to the file to make sure the checks are disabled. However this does not make any difference.
Why does it take so much time to import the data? Is there a better/faster way to do this?
The server is not the fastest (shared hosting...) but it takes just about 2 minutes to export/dump the data. That exporting (no syntax checks, no parsing, just writing...) is faster than importing (parsing, syntax check, etc.) is not surprising but 300 times faster (10 hours vs. 2 minutes)? This is a huge difference...
Isn't there any other solution that would be faster? Copy the binary DB file instead, for example? Anything would be better than using a text file as transfer medium.
This is not just about transferring the data to another machine for testing purposes. I also create daily backups of the database. If it would be necessary to restore the DB it would be pretty bad if the site is down for 10 hours...
1not sure if the /*!40014 commands are executed or not Executed if server version is 4.00.14 or higher. Read there. Is there a better/faster way to do this? There is a lot of backup methods. Read there. Pay attention to 'Making Delimited-Text File Backups' (DB structure must be dumped separately without data). – Akina – 2018-08-15T12:20:03.487