From time to time, you’ll need to get a backup of your MySQL database and install it on a different machine – as I needed when reinstalling my Windows 7. I wanted to do something simple, as the last time. But now the database sql file is 11 Mb as opposed to merely 2 MB last time.
But I thought that this would be a breeze for the MySQL interface that I use and enjoy – HeidiSQL. It’s free, easy to use and user friendly. I personally don’t like too much the phpMyAdmin interface from the Vertrigo webserver installation that I use for local development.
After starting the import, I was happy – the HeidiSQL app runned so fast for the first 5000 statements … but wait! Starting with the 7000, it became very slow – probably no memory flush for queries ?
After waiting an hour and half, it got to around 7500 statements. Eager to start, I looked for a faster solution – the command line for importing large files. You’ll find out that in MySQL bin folder there is the core of the server : mysql.exe. You can call it with parameters, instructing it what to do.
After reading some help, I realised that what I looked for was :
mysql -u username -p database_name < textfilewithsqlstatments.sql
So you’ll have to provide the username for connecting to database – be careful to have enough rights to create tables, insert records and so on. Next, the database in which you want to import the content of the textfilewithsqlstatements. For Linux users, be careful to also check for any case sensitive issues that might appear.
Also be very careful with the less than sign. Less than means import, while greater than means export!
After pressing return key, you’ll be prompted for the password associated with the username provided. Then the import will start and everything is imported in MySQL, assuming that you didn’t misspelled or did some errors, case in which you might want to read again this post.