[MySQL] MySQL Database setup recommendation for best results.

If you are installing a third party script that will connect to mySQL, your configuration script, or web based installer will ask you for certain variables regarding mySQL.

Typically, the first step is to create your database.

Login to your control panel and click on the database manager.

1. Create your database.
2. Create your DB user and password next.
3. Finally add the user you just made to the empty database you just created.

Then when you run the installation script, the fields should look as follows:

db host = localhost
db name = username_whatever
db user = username_whatever
db pass = whatever

Please note that the password is the only field that does not require the "username_" in front of it.

"username_" is your cPanel username. You will see the database name and any users connected to that database listed in the database manager after you have completed steps 1-3 above.

Finish your installer or save the configuration file and run your script according to the readme file that was included with the script.

***DO NOT TRY TO MAKE YOUR DATABASES IN PHPMYADMIN.***
You will get permission errors. You need to make these in the cPanel database manager as outlined above.

I'm trying to import a MySQL database that's 26MB gzipped or about 120MB unzipped. I ran an import of the gzipped version through phpMyAdmin, but it seems to have timed out somewhere at 20% of the file.

Any suggestions on how to accomplish the import?

Thanks,
Omar

Hello, there are two ways:
first is to increase max_execution_time and session.gc_maxlifetime settings in your php.ini file if you are going to perform such operations via phpMyAdmin in future;
second is to perform restoration via command line interface. To do it, you need to upload your archive via ftp or file manager, unzip it using the command

Code:

---

unzip <archive name>

---

and restore it

Code:

---

mysql dbname < dbdump.sql

---

Please, note, that these operations should be performed logged as root user.

Quote:

---

Originally Posted by Alexander

you need to upload your archive via ftp or file manager

---

Forgive my ignorance, but where exactly should I upload the gzipped file?

You can upload it in any folder on your VPS, logged as root user you can unzip it and restore without any problems. Please, note, that during the restoration the extracted dump must be in your current folder. Also, the db should exist.

I must be doing something wrong... When I go to open the Shell from Virtuozzo, the Java window pops open, but won't let me do anything, and it says disconnected. It doesn't accept any input.

You need to connect via putty to your VPS to perform these operations. If you faild to do it, please, update the ticket and provide us with the path to ziped dump and the db name, we'll do it for you.

Improvement, but not there yet. I like to try these things myself so I've got it to start importing using PuTTY and the command line you provided.

It starts working, but it timed out again. This time at around 48%.

Is there something else I need to do to make it work before I give up and ask the pros in Tech support to do it for me?

Could you please paste the exact error message you get while importing your SQL file into a database ?

The only error message was a window that popped up in PuTTY, something about the connection being closed. I'll try it again and copy the error message this time.

I looked at the DB in phpMyAdmin and could see that it had stopped growing.

Give me a few minutes and I'll post the results of the next attempt here.

Should I keep PuTTY's connection alive by doing something like dir from time to time?

Ok, the popup window says:

PuTTY Fatal Error
Network error: Software caused connection abort.
I started the import at 10:58 and it timed out at 11:08, so it seems to run for 10 minutes, then quits.

I'll try another import, this time I've used connect_timeout=9999 before using the mysql command. Not sure if this is the proper syntax, but I didn't get a bad command warning.

Still no luck. Same error after 10 minutes. Any suggestions?

It was not the SQL error message. Putty SSH client gave the timout error message. I would recommend you to create a screen and run the command in screen. You can create a screen using the following command:

Code:

---

screen -S SCREEN_NAME

---

where SCREEN_NAME is a name of your screen

Then run the command to import your SQL file:

Code:

---

mysql DB_NAME < DB_NAME.sql

---

Close the screen with help of Ctrl+A, then Ctrl+D. When the command is run in screen it will never be killed even if you close the SSH window.

You can open a screen again with help of the following command:

Code:

---

screen -r SCREEN_NAME

---

Ok, I have it going again. We'll see in about 10 minutes or so.

Didn't work. Same message after 10 minutes again.

Well, I take that back - it's still going. Much slower than the first few tries, but still growing the DB. I will report in a little while.

It's done! But it took forever.
2.5 hours for 130MB.
Is there a reason why the performance was so slow?

Call your ISP and ask if they require keepalive connections for SSH.

I had issues using Adelphia cable when I subscribed to their service and I had to specifically enable keepalive in putty.

In my putty version it is located at Connection > Seconds Between Keepalives.

Basically your SSH client will send a packet to the server and back. I don't know why some ISP require this, it is a waste of time and packets, but try it out and it might be your fix.

Matt, Sergey's SCREEN solution did the trick. The DB is in and working.

Any thoughts on the very long import time it required?

It is a bad ISP, or a bad computer.

130 MB should never take that long.

Have you tried another ISp or another computer? I suggest you download a large file like that SQL and test it from:

Different ISP
Different Computer
Different Computer on Different ISP
Same Computer on Different ISP
Same Computer on Same ISP

Doing those tests will tell you where your problem is.

No, this was a file already uploaded to my vps and already unzipped. All I had to do was issue the mysql command.

It was all done on the glowhost vps (cpvps118-vern). Bad computer?

Not sure why it took so long, perhaps because of MySQL or perhaps because of the VPS enviroment.

Maybe we can get a DBA to post here and give us a normal import time for a DB of that size.

For what it's worth, during the first few import tries (which timed out after 10 minutes) the rate of import was approximately 50MB/10min or 5MB/min. The successful import was 130MB/2.5hr or 0.9MB/min.

What was 130MB? The final data or the sql file?
Also may be the format of the sql file
I think possibly what could be an issue is that the redirect method reads the whole file into mem and depending on how mem is arranged, it could have been hitting the swap file pretty heavily. That would slow it down. I'll bet the disk IO was pretty high too with all that reading and writing. Theres no real answer on how long these things should take. I assume this was a one shot deal anyway? If not, I can take a look next time.

Yes John-Marc, it was a one shot deal and it's finished.

The file size of the database.sql was ~130MB.

Not sure what you mean by final data vs. sql file.

Perhaps there was something else going on at the same time, maybe the nightly backup, because the first few attempts had a better rate of import than the one that ultimately succeeded.

Omar