Import large database in remote server
If you owned slow internet connection, it would be headache if you have large database to be imported in a remote server. Either you import the database through MySQL GUI or phpMyAdmin, the execution time is mostly will exceed the maximum execution time allowed, resulting in an script timeout problem. It would be same if you manually paste the queries and execute it in remote server. The server simply doesn't have enough time to capture your bulk records of "INSERT".
So is there another alternative?
Actually, it is. You can upload your sql file to your remote server, and then use the PHP script to parse the sql file and then run the query line by line.
Fortunately, there is an open source code which ease our job to write our own script. You may visit "BigDump", and download its source code, bigdump.php, which is zipped as archive sized at only 10KB zip archive.
Before using BigDump, you need to upload your sql file into your remote server. Be sure not to upload your file to somewhere people may access it, such as your /public_html folder. Normally, you would upload the file at the parent folder of /public_html, such as, /home/<username>/
Bigdump also allow import the records from CSV file. You may refer the following configuration source code, foundat the beginning of bigdump.php.
// Database configuration $db_server = 'localhost'; $db_name = ''; $db_username = ''; $db_password = ''; // Other settings (optional) $filename = ''; // Specify the dump filename to suppress the file selection dialog $csv_insert_table = ''; // Destination table for CSV files $csv_preempty_table = false; // true: delete all entries from table specified in $csv_insert_table before processing $ajax = true; // AJAX mode: import will be done without refreshing the website $linespersession = 3000; // Lines to be executed per one import session $delaypersession = 0; // You can specify a sleep time in milliseconds after each session // Works only if JavaScript is activated. Use to reduce server overrun // Allowed comment delimiters: lines starting with these strings will be dropped by BigDump $comment[]='#'; // Standard comment lines are dropped by default $comment[]='-- '; // $comment[]='---'; // Uncomment this line if using proprietary dump created by outdated mysqldump // $comment[]='CREATE DATABASE'; // Uncomment this line if your dump contains create database queries in order to ignore them $comment[]='/*!'; // Or add your own string to leave out other proprietary things // Connection character set should be the same as the dump file character set (utf8, latin1, cp1251, koi8r etc.) // See http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html for the full list $db_connection_charset = '';
Like