Using MySQL

From SuperbHosting.net Support Wiki

Setting up your database

If you would like to use a MySQL database, you must first create a MySQL database by using your myCP® control panel. After logging into the MySQL area, click on create database.

Database and login name naming scheme

The database naming scheme is automated in myCP®. The database name is based on one of two pieces of information: your primary domain name or your FTP login name. If your hostname begins with 'sh,' your database will be named based on your FTP login name. If your hostname begins with 'no' or 'nt,' your database will be named based on your FTP login name. When you create the new database, it should be clear what naming convention is being used.

When Database name is based on Domain Name:
If your domain name is abc.com, the first database you setup is called abc_com_1. If you request a second DB, it will be called abc_com_2, and so on. Because we use dedicated MySQL servers and we need to know which client owns the database, there can be no exceptions regarding the naming scheme. If you have 2 different accounts, abc.com and def.com, the databases for abc.com must be abc_com_1, abc_com_2, etc, and the databases for def.com must be def_com_1, def_com_2, and so on. The login name is the same as the database name. If you have a long domain name like, abcdefghijklmnop.com, the login and database name for the database abcdefghijklmnop_com_1 is going to be abcdefghijklmn_1. The names are truncated in 14th character, and then we add the "_1", "_2", "_3", etc, to the end.

When Database name is based on FTP Login Name:
If your FTP login name is shb8_134, the first database you setup will be called shb8_134_1. If you request a second database, it will be called shb8_134_2, and so on. Because we use dedicated MySQL servers and we need to know which client owns the database, there can be no exceptions regarding the naming scheme.

The user has full control over the database. It can create, modify and drop tables, add and drop indexes, insert, modify and delete records from a table, etc. The maximum length of the login and database name is 16 characters long.

Logging into your database

You can login to the database in myCP® with phpMyAdmin to create your tables, and do all of your MySQL work. You may also login with any MySQL client, or with a PHP, Perl, or ASP script on your site. To login you need four pieces of information:

1. The database hostname/server, which you can determine by clicking on phpMyAdmin. You will see text in the login dialogue indicating the name of your server, for example: MySQL1.dca1.superb.net.
2. The database name, which you can determine by looking at the entry under "database" in the main MySQL screen in myCPreg;.
3. The database username, which is the same as the database name.
4. The database password. If you forget the password you used to create the database, you can change it as specified below.

Database size

Each MySQL database is 20MB in size. Depending on the number of databases you have ordered, the maximum disk space for the database is the number of databases x 20MB (i.e. 5 DBs = maximum diskspace allowed would be 100MBs).

Changing the MySQL user password and user permissions

You may want change the password and the user permissions for security reasons. The user created for your database is initially set-up with full permissions for the newly created database.

1. Click the link Users for the database you want.
2. Change the Permissions you would like for that User, and if you don't want to change the password, just leave both password fields blank.
3. If you would like to change the password, but not the permissions, just type in the Password (in both fields) and don't change any of the permissions.
4. Click on Submit.

NOTE: If you remove all privileges, the user is going to be removed from MySQL (this is how the GRANT statement is supposed to work). If you are not sure why you should change the user permission, don't do it. You can make your scripts stop working. (For more information on MySQL database permissions, see: http://www.MySQL.com/doc/P/r/Privilege_system.html).

DB Dump Tool

This tool can be extremely useful. Using the DB Dump Tool, you can transfer structure and data from any MySQL server onto our server or from our server to another.

1. Click in the link Dump for the database you want.
2. Type in the server name, Db name, login and password of the server you want to transfer the data from (your old hosting company, for example).
3. Type in the server name, Db name, login and password of the server you want to tranfer the data to (it defaults to our server and your database and login name)
4. Click on Submit Query button

Stats

This gives you the stats on your MySQL databases. Just for informational purposes.

phpMyAdmin Suite

This is the most used suite for MySQL Administration. This is an open source, third-party application.

1. Click in the link PhpMyAdmin for the database in question.
2. You will be prompted to enter the database login and password.
3. After logging in, you can begin working with phpMyAdmin Suite.

NOTE: A very nice feature of phpMyAdmin is the ability to dump your database into a plain text file in your own computer (this is great for local backups). Creating and editing tables is very simple and fast.