How to import a MySQL database that is too large for phpMyAdmin

[MarkSelux DF Member Posts:15 Threads:4 Reputation 0]

It often happens that the backup of a MySQL database is large and exceeds the limit that the hosting has imposed on the maximum size of the import in phpMyAdmin.

It happened to me recently, with a backup. How to do then?

We can solve in three ways, one easy and the other 2 a bit more challenging.


1. Import with MySQL from the shell

It is certainly the most practical and fast method, the one I prefer, but it is not the easiest.


Requires SSH access to the server, which offers few hosting services and a minimum familiarity with the terminal.

The command to use is this, in its simplest syntax:

Code:

mysql -h localhost -u nomeuser -p namedatabase <nomebackup.sql

Obviously the data relative to the MySQL server, to the name of the database and the related user, must be replaced, and pressing Enter it is necessary to insert the password, always relative to the database user.


2. Split the MySQL file into several parts



This is the simplest method.


If you do not have access to the server via SSH, or if the terminal is a bit scary, you can use a simple application to split the database into several parts and then import them individually with phpMyAdmin.

The Windows application is called SQL Dump Splitter, unfortunately for Mac OS XI did not find anything like it.

Once opened, a screen similar to the following will appear:


     The SQL Dump Splitter screen when it is open

Step 1
Select the sql file by clicking on "browse".

Step 2
Set the maximum size that must have each piece that will be created.

Step 3
Select any destination folder.

Step4
Check the "skip comments" box to ignore comments in the file. Then click on "Run": a folder will be created containing all the fragments.

Here it is: your dump has been divided correctly. Search for the destination folder containing the various files.
The first fragment to be imported into your database is the one that contains the word "structure" in the name: it loads the structure of all the tables in the database.
Then, in turn, the fragments are imported in numerical order: each file, in fact, will have the same name with an increasing numeric suffix.


3. Import a large mysql database with BigDump


BigDump is a PHP script available on the network, it must be loaded and configured with database and MySQL server data.

The easy-to-use script becomes very useful in case you need to transfer your site (or more) to a new provider, and you are dealing with databases of a certain size. Unfortunately, the PHPMyAdmin import feature has a limit of around 50 MB, so alternative solutions are needed. For those who have a good knowledge of Linux systems and command lines, it can also do so through SSH (Secure Shell).


Now let's see a simple guide on how to use BigDump.


Requirements

To use BigDump and proceed without problems to import a MySQL database it is necessary to:

• BigDump
  
  
• Download the affected MySQL database on your PC
  
  
• An FTP client
  
  
• Create a new (destination) database on the new Provider
  
  
• Minimum knowledge of php, mysql, phpmyadmin and http
  

Import the MySQL database

Open the "bigdump.php" file with a text editor (notepad) and enter the database of the destination database: ie database name, database user name and password. If the database character set is different from the default set, change it. The same for table prefixes. I refer of course to this piece of code:

Code:

$ db_server = 'localhost';
$ db_name = '';
$ db_username = '';
$ db_password = '';

After that:

• Connect via FTP to the server, where you want to import the database
  
  
• Create a "dump" folder in the server root
  
  
• Upload the "bigdump.php" file inside the folder
  
  
• Now also upload the MySQL database, initially downloaded to your PC, in the "dump" folder
  
  
• Start the import script now, by going to the web address "http://www.yoursite.com/dump/bigdump.php"
  
  
• You will see a screen similar to this one, where clicking on "Start Import", the import will be started automatically (in our example, we have imported the database "db-tech.sql"
  

After the database is imported, a green text "Congratulations..etc" will be displayed.

Note: do not touch anything until the import is complete. When finished, delete the bigdump file and the dump folder from the server.

For any information and / or problem, leave a comment. Nice day!

[EVX Administrator Posts:598 Threads:64 Reputation 128]

helpful nice share

[MarkSelux DF Member Posts:15 Threads:4 Reputation 0]

@Ebza I thank you, I am new in this forum and I must say that it is very nice, I will post many guides