mysql – How to Fix 1000 MB Database File Not Uploading via phpMyAdmin

MySQLphpmyadmin

We have an e-commerce Magento site and a large database of 1000 MB for that site.

When we try to import the database through phpMyAdmin, it's not uploading the full databse, a lot of tables will be missing after uploading.

We use a GoDadddy cPanel and phpMyAdmin.

How can we upload the full 1000 MB database completely?

Also, is there any way to replace the old domain name to new domain name in the database?

thanks in advance

Best Answer

Try this

For Export:

mysqldump -u [user] -p [db_name] | gzip > [filename_to_compress.sql.gz]

For Import:

gunzip < [compressed_filename.sql.gz]  | mysql -u [user] -p[password] [databasename]

Note: There is no space between the keyword '-p' and your password.

Related Solutions

Mysql – How and where do I upload a thesql database

If you have downloaded the database as an SQL file then you can just import it using the command prompt and the mysql command.

Do it in these simple steps:

Make sure the SQL file is unpacked as a text file with SQL commands inside
Verify you're able to login to MySQL using the command prompt. You can simply type # mysql -p -u username database_name. You'll be asked for password and, if correct, you'll be accepted to your database.
If you haven't created any new database to unpack your backup file in you can do it now (inside the MySQL command prompt) by typing create database MyNewDatabase;
Now that you know you can login to the database you can "feed" the downloaded SQL file by changing the command in this way: # mysql -p -u username database_name < /tmp/download/db_backup.sql. Make sure you're not inside the MySQL command prompt but out in the normal command prompt when typing this command. You can simply type exit or quit to leave the MySQL command prompt.
The database will then be imported into your local database. If it's a big database running this last command might take quite some time.

Some things to think of:

If you have a new installation and haven't added any users to MySQL then root is the username to use. But it's always recommended to add a separate user account in MySQL that you can use from scripts ect.
In my examples above I specified a database name. But if your backup script starts with use someotherdb; then someotherdb will be used instead of the database name you specify in the mysql command. To get around this, either open up the SQL backup file and remove the first line with use, or, when you create your backup, untick the box saying Include using (or something similar).
This is just how to install the database. You talked about "web site" also and that's a different thing. However, you need to update your website scripts to point to the new local database that you've created now.

Thesqldump – select table in database only if it contains a specific string

What you need is a tool for controlling migrations and upload them to git a.k.a. a version control for MySQL. While you can deploy your own, this is a very well known problem and there are many tools available, some of them free and open source. I am not going to recommend you anything in particular, as they are different on the goals/features/integration with frameworks, but here it is a list of options:

Liquibase
Flyway
c5-db-migration
dbdeploy
MyBatis
Autopatch
Sqitch
online-migration

If you still want to deploy your own tool (not recommended), have a look at least at mysqldbcompare from mysqlutils and some of the percona-toolkit tools.

Best Answer

Related Solutions

Mysql – How and where do I upload a thesql database

Thesqldump – select table in database only if it contains a specific string

Related Question