Tag Archives: MySQL

How to test MySQL database connection

Posted on by
Reply

You can test connectivity to a MySQL database using the below sample script:

        <?php
        $dbname = 'mysql_dbname';
        if (!mysql_connect('mysql_host',        'mysql_user', 'mysql_password')) {
            echo 'Could not connect to mysql'; exit;
        }
        $sql = "SHOW TABLES FROM $dbname";
        $result = mysql_query($sql);
        if (!$result) {
            echo "DB Error, could not list tables\n";
            echo 'MySQL Error: ' . mysql_error(); exit;
        }
        while ($row = mysql_fetch_row($result)) {
            echo "Table: {$row<span class="error">[0]</span>}\n";
        }
        mysql_free_result($result);
        ?>

The variables used in the script are:
mysql_dbname: the MySQL database name
mysql_host: this needs to be specified as localhost
mysql_user: the username associated with the above MySQL database
mysql_password: the password corresponding to the above user

This script, on a successful connection, will fetch and display the names of all the tables in the specified database.

How to backup/restore your MySQL database

Posted on by
Reply

If you run a database driven website, it is important to backup your databases, periodically. You can generate a full backup to do this, but you may decide it is unnecessary to backup everything. So, here is how to backup just your databases.

To backup the databases please do the following:

1. Login to your control panel and click Backups.
2. Under Download a MySQL Database Backup, click the name of the database.
3. Click Save As.
4. Select a destination for where you would like the back up to be saved, locally.

Once you have a backup, if you ever inadvertently lose your data, then you can restore the database easily and quickly.
To restore a database, please do the following:

1. Login to your control panel and click Backups.
2. Click the Browse button next to the option which says Restore a MySQL Database.
3. Select the .sql.gz file which you want restored.
4. Click the Open button and click the Upload button.

Your database driven website should be working again.

How to connect to the MySQL database

Posted on by
Reply

Configuration Settings:

Host = localhost (really, put localhost)
Database name = (cpanelusername_databasename)
Database username = (cpanelusername_databaseuser)
Database password = (******)

Note: When you move databases to our servers, the database name and username may change. This change must be updated in your script code. (The database name and username is clearly displayed in cPanel, in the MySQL area.)

On shared servers, the database user name cannot be changed to something without the cPanel user name in it.

It is possible for someone to use their cPanel username and password to connect to the database. However, we NEVER recommend doing this because the first time you change or reset your cPanel password, then your databases will stop working.

How to connect to the MySQL server remotely

Note: If connecting from your home computer, you need a MySQL client like Navicat, phpMyAdmin, or Dreamweaver.

Configuration Settings:

Host = (use the server IP address)
Database name = (cpanelusername_databasename)
Database username = (cpanelusername_databaseuser)
Database password = (******)
MySQL Connection Port = 3306
TCP or UDP, either is fine.

Also, before you can connect to MySQL from another computer, you must enable your computer as an Access Host. It may sound confusing, but cPanel makes this very simple.

  1. When using X3 cPanel theme, login to cPanel and click the Remote MySQL icon, under Databases.
  2. Type in the connecting IP address, and click the Add Host button.
  3. All done, try connecting remotely.

-or-

  1. When using X, login to cPanel and click the MySQL Databases icon, under Databases.
  2. Scroll all the way to the bottom and type in the connecting IP address where it says “Host (% wildcard is allowed):”, and click the Add Host button.
  3. All done, try connecting remotely.

How can I find out my computers IP address?

Simple, just go to http://www.whatismyip.com/

What if my IP constantly changes?

Okay, you have a dynamic IP address. Therefore, every time you go online, or every once in awhile, your IP changes. You have a few options here. The most secure, would be to update your IP in Remote MySQL every time your IP changes. However, you do have other options available. You could simply enter % (a percentage sign) in the Add Access Hosts text field. This will allow any IP to remotely connect to your databases. Another option: Lets say the first two parts of your IP never change. Then, you could enter 93.138.%.% , where the first two parts are your actual IP address numbers.

IMPORTANT UPDATE ! Since the 11.25 update, we’ve noticed a bug where you add an IP to the MySQL remote access list, only to find that the corresponding MySQL user that is created does not have any privileges.

In order to get the correct privileges added, you must go back to your MySQL Databases section in cPanel and re-add the user to each database (reassign the privileges). No need to remove the user first; just select the user and the database from the drop-downs and click “Add”.

We’ve notified cPanel about the issue.