MySQL

MySQL is one of the most common database applications in use for web hosting. If your site needs a database, this is what you need.

Installing MySQL

For installation instructions, please click here.

Using MySQL Database

While we provide installation support for MySQL to the point of verifying that it is running properly, we are unable to troubleshoot problems with MySQL. If you become aware of a problem with MySQL through their mailing lists or otherwise, please let us know and we will work with you to resolve it. MySQL has a free online manual for your use at http://www.mysql.com/doc/en/index.html.

If you have questions about MySQL and its use, we recommend you use their mailing lists, which are at http://www.mysql.com/documentation/lists.html.

We want to make sure that your experience with us is as good as it possibly can be. With that in mind, we hope you understand that we also have limitations in resources and knowledge that prevent us from being able to support third party software (as complex as this) as well as you may need it to be. This is why we ask you first use the above references for troubleshooting any problems you may have; if you are still having problems, contact us for help in fixing the problem.

MySQL Database Backup & Restore

If your Web site is using application relying on MySQL, or if you have created personal databases using MySQL, you should backup each database to avoid data loss.

Backing Up MySQL Databases

Below are two different methods of backing up your data:

Using PhpMyAdmin to backup your databases

If you haven't already installed PhpMyAdmin, please follow the below steps:

• Log into your Site Manager (Control Panel).
• Click Site Applications from the top navigation within your Site Manager (Control Panel).
• From the Database section of your Site Applications, click on the install icon ( ) beside PhpMyAdmin.
• A wizard (multi-stepped window of instructions) will open. Proceed with the installation as you would with any other application.

Once PhpMyAdmin is installed, access PhpMyAdmin and follow these steps to export data for each database you have:

• Select the database from the pull-down menu in the left-hand frame
• Click on the Export tab in the main frame of the page
• Modify Export Format and Compression as desired (default values are acceptable)
• Select the checkbox Save as file. Do not modify the File Name Template (it will be saved by default as the name of the database)
• Repeat steps for each database.

NOTE: All data must be backed-up, or it will be lost with the uninstall of MySQL.

Using the Command Line to backup your databases

The most common way of backing up a database using the command line can be done using the program mysqldump. The following syntax will help create a backup of your database/databases.

shell#> mysqldump --opt db_name > backup-file.sql

If you don't name any tables or use the --databases or --all-databases option, entire databases will be dumped.

Please consult the MySQL documentation at the links below.

• http://dev.mysql.com/doc/mysql/en/backup.html
• http://dev.mysql.com/doc/mysql/en/mysqldump.html

Restoring MySQL Databases

Below are two different methods of backing up your data:

Using PhpMyAdmin to Restore your databases

If you haven't already installed PhpMyAdmin, please follow the below steps:

• Log into your Site Manager (Control Panel).
• Click Site Applications from the top navigation within your Site Manager (Control Panel).
• From the Database section of your Site Applications, click on the install icon ( ) beside PhpMyAdmin.
• A wizard (multi-stepped window of instructions) will open. Proceed with the installation as you would with any other application.

Once PhpMyAdmin is installed, access PhpMyAdmin and follow these steps to import data for each database you previously backed-up:

• From the PhpMyAdmin Homepage, create a new database with the same name as the previously backed up database. If you are restoring the PhpBB database (and you've already re-installed PhpBB), you can simply select phpbb from the pull-down menu in the left hand navigation
• Once the desired database is selected, choose the SQL tab in the main frame of the page
• Browse for the location of the file that you previously backed-up
• Select the compression type, or leave it as auto-detect
• Press Go! You will receive confirmation of successful import, and the associated tables will be listed under the database name in the left-hand frame. If you restored the PhpBB database, verify that the forums are accessible, and the data is accurate
• Repeat these steps for each database you wish to restore

Using the Command Line to restore your databases

You can read the dump file back into the server like this:

shell#> mysql db_name < backup-file.sql

or like this:

shell#> mysql -e "source /path-to-backup/backup-file.sql" db_name

Please consult the MySQL documentation from the link below.

• http://dev.mysql.com/doc/mysql/en/mysqldump.html

MySQL FAQ

1. What are the telnet (SSH) commands to access MySQL?
Use the following outline to connect and begin querying the MySQL server from a Telnet.

NOTE: By default you cannot connect to your databases remotely due to security concerns (you can only connect from localhost). If you need to connect remotely (only available with dedicated IP accounts), you will need to create (or modify) a user and either leave the 'host' column blank or enter in your own IP address (or host name obtained from reverse DNS lookup)

1. Connect to the MySQL Server servername:> mysql -u USERNAME -p
2. Enter Password: PASSWORD
3. Connect to the Database mysql> use DATABASENAME
4. Receiving Help mysql> help

2. How do I connect to MySQL through PHP?
http://us.php.net/mysql.

3. How do I connect to MySQL using the MySQL Perl Module?
Use the following outline to connect and begin querying the MySQL server from a Perl script.

NOTE: By default you cannot connect to your databases remotely due to security concerns (you can only connect from localhost). If you need to connect remotely (only available with dedicated IP accounts), you will need to create (or modify) a user and either leave the 'host' column blank or enter in your own IP address (or host name obtained from reverse DNS lookup)

1. Declarations. You must require the MySQL package for your script to function properly. Do this by including the following line in your code: use Mysql;
2. Connect To The Database. Somewhere near the beginning of your script, you need to make your initial connection to the database server. Using the following form, substitute your database, username, and password for the examples to connect successfully.

Mysql->connect('localhost','DATABASENAME','USERNAME','USERPASSWORD');

1. Execute a Query. You are now ready to begin querying the database server. Most problems that you may incur will generally occur due to invalid permission settings for the specified user.

4. Can I use ODBC with MySQL?
At this time, we do not support ODBC.

5. Can I use JDBC with MySQL?
At this time, we do not support JDBC.

6. Can I remotely connect to my MySQL database?
You can choose in your site manager whether or not you would like to have MySQL listen for connections other than local (enable "TCP/IP Connection:" in your MySQL configuration in the Site Applications). It is set to off by default due to security concerns.