Menu
I have a 400 MB large SQL backup file. I'm trying to import that file into MySQL database using WAMP → import, but the import was unsuccessful due to many reasons such as upload file size is too large and so on. So, I've gone ahead and made a few changes to the configuration of PHP and MySQL settings under WAMP. Changes made are
Under WAMP->MySQL->my.ini max_allowed_packet = 800M read_buffer_size = 2014K Under WAMP->PHP->PHP.ini max_input_time = 20000 memory_limit = 128M Is this the right way to import such a large SQL file using WAMP → import?
If so, did I make the right changes to the configuration files? What are the maximum values allowed for the above variable?
724
The easy way to import a SQL file without having to install any tools to run mysql -u USER -p DATABASE_NAME < PATH/TO/FILE.sql in the command line (or terminal). Use the official MySQL Workbench to import SQL files. Use a popular web-based database manager called phpMyAdmin. Let us walk through these methods step-by-step in this guide – Read on!
Now type a command in cmd to show all the files included in the database by typing show database. After creating and selecting the database, Import the sql file using this command. You have successfully completed the process to upload a large database in MySQL.
Then place the large sql file that you are trying to import into the new upload directory. Now when you go onto the db import page within phpmyadmin console you will notice a drop down present that wasn’t there before – it contains all of the sql files in the upload directory that you have just created.
Now we will learn to upload data in Xampp using CMD. Now type a command in cmd to show all the files included in the database by typing show database. After creating and selecting the database, Import the sql file using this command. You have successfully completed the process to upload a large database in MySQL.
You can import large files this command line way:
mysql -h yourhostname -u username -p databasename < yoursqlfile.sql Since you state (in a clarification comment to another person's answer) that you are using MySQL Workbench, you could try using the "sql script" option there. This will avoid the need to use the commandline (although I agree with the other answer that it's good to climb up on that horse and learn to ride).
In MySQL Workbench, go to File menu, then select "open script". This is probably going to take a long time and might even crash the app since it's not made to open things that are as big as what you describe.
The better way is to use the commandline. Make sure you have MySQL client installed on your machine. This means the actual MySQL (not Workbench GUI or PhpMyAdmin or anything like that). Here is a link describing the command-line tool. Once you have that downloaded and installed, open a terminal window on your machine, and you have two choices for slurping data from your file system (such as in a backup file) up into your target database. One is to use 'source' command and the other is to use the < redirection operator.
Option 1: from the directory where your backup file is:
$mysql -u username -p -h hostname databasename < backupfile.sql Option 2: from the directory where your backup file is:
$mysql -u username -p -h hostname [enter your password] > use databasename; > source backupfile.sql Obviously, both of these options require that you have a backup file that is SQL.
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
