[MySQL] MySQL Database setup recommendation for best results.

[Matt]

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.

[omarfilip]

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

[Alexander]

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

unzip <archive name>

and restore it

mysql dbname < dbdump.sql

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

[omarfilip]

you need to upload your archive via ftp or file manager

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

[Alexander]

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.

[omarfilip]

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.

[Alexander]

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.

[omarfilip]

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?

[sergey]

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

[omarfilip]

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.