SQL/PHP: How to upload big database to server when I have import file size limit? And then update...

Question

Hi, I'm creating locally a big database using MySQL and PHPmyAdmin. I'm constantly adding a lot of info to the database. I have right now more than 10MB of data and I want to export the database to the server but I have a 10MB file size limit in the Import section of PHPmyAdmin of my web host.

So, first question is how I can split the data or something like that to be able to import?

BUT, because I'm constantly adding new data locally, I also need to export the new data to the web host database.

So second question is: How to update the database if the new data added is in between all the 'old/already uploaded' data?

Answer 1

What I would do, in 3 steps:

Step 1: Export your db structure, without content. This is easy to manage on the exporting page of phpmyadmin. After that, I'd instert that into the new db.

Step 2: Add a new BOOL column in your local db in every table. The function of this is, to store if a data is new, or even not. Because of this set the default to true

Step 3: Create a php script witch connects to both databases. The script needs to get the data from your local database, and put it into the new one.

I would do this with following mysql methods http://dev.mysql.com/doc/refman/5.0/en/show-tables.html, http://dev.mysql.com/doc/refman/5.0/en/describe.html, select, update and insert

then you have to run your script everytime you want to sync your local pc with the server.

Answer 2

Don't use phpMyAdmin to import large files. You'll be way better off using the mysql CLI to import a dump of your DB. Importing is very easy, transfer the SQL file to the server and afterwards execute the following on the server (you can launch this command from a PHP script using shell_exec or system if needed) mysql --user=user --password=password database < database_dump.sql. Of course the database has to exist, and the user you provide should have the necessary privilege(s) to update the database.

As for syncing changes : that can be very difficult, and depends on a lot of factors. Are you the only party providing new information or are others adding new records as well? Are you going modify the table structure over time as well?

If you're the only one adding data, and the table structure doesn't vary then you could use a boolean flag or a timestamp to determine the records that need to be transferred. Based on that field you could create partial dumps with phpMyAdmin (by writing a SQL command and clicking Export at the bottom, making sure you only export the data) and import these as described above.

BTW You could also look into setting up a master-slave scenario with MySQL, where your data is transferred automatically to the other server (just another option, which might be better depending on your specific needs). For more information, refer to the Replication chapter in the MySQL manual.