Importing Large Files in Mysql using Source Command
Never tried to upload or import a large file to Mysql? Well, I recently faced the dilemma of importing a large WordPress database, over a 100 MB in size and some serious difficulty.
Mysql is initially set up to process 1 MB through import, so i increased this via shell to 120 MB but it still did not work
So, I played around in shell and found out the solution. Here it is the solution using XAMPP or WAMPP as the server
1. Create an empty database in phpMyadmin. Give it the same name as the sql file you are trying to upload. You could also create a database in shell by typing: mysql: CREATE DATABASE databasename;
2. Move your sql file to the bin file in your Xampp Mysql directory
3. Log on into shell: mysql -u root -p. It will then ask you for your root password. Type it in
4. Let Mysql know which file you intend to use
- mysql> USE database;. If you fail to use the USE command you can end up with this error: “error 1046 (3D0000): no database selected. If your database is large this error can freeze the screen
5. Dump the sql data file with this code: SOURCE c:/xampp/mysql/bin/file.sql
If your file is really big it could take over 30 minutes, be patience and your sql database will be imported
If Your Hosting Company is Godaddy
In the event you are using a hosting company like Godaddy or 1and1 or bluehost, upload the sql file to your cgi-bin or bin directory via ftp, through dreamweaver or any other ftp program.
Then create a database name in phpMyadmin for your new uploaded sql file
Type in mysql> USE new database name in shell. If you get an error, type is: SHOW DATABASES to ensure that you have the right name
Then enter this in shell: mysql> SOURCE /home/yourwebsitehome/anyotherhomefile/cgi_bin/file.sql;
Your sql file should load into the new database
Changing the Size of File Mysql Can Upload
Another less used method is to change the max_allowed_packet for mysql files. Go to your phpMyAdmin and click on variables and in the list of variables you will see max_allowed_packet set to 1 or 2M.
To change the max_allowed_packet size log on into shell and type in: mysql> nano /etc/my.cnf. Change the max_allowed_packet to about 100M. You may also want to increase the “max upload file” in the php.ini file.
So with these methods you should know how to import large sql data files into mysql myadmin