Using MySQL

MySQL is the world's most popular open source database.

Setting up a MySQL Database

The Account Control Center allows you to setup and configure MySQL databases for your account.

First, log into the pairLite Account Control Center. To create a brand new database, click "Create a New Database" under the Database Administration section.

The "Add Database" page has three options:

• Database Name - all pairLite databases have names using the form "USERNAME_something," where "USERNAME" is your pairLite username, and "something" is a name of your choosing up to 20 characters. Your username is automatically added to the beginning of the database name you entered. For example, if you entered a database name "test123," your database name would be "USERNAME_test123."
• Access Level - all databases have two available access levels. With "Local Only" access, the database can only be accessed from servers within the pairLite network. With "Local/Remote" access, the database can also be accessed from outside the network. Remote access is available only for debugging purposes and not for general use.
• Optimization Period - all databases are optimized using MySQL's OPTIMIZE TABLE query on a recurring basis. If you will be frequently inserting new data into your database, choose weekly or bi-weekly. If your data will be mostly static, choose the monthly option.

Maintaining a database with the Account Control Center

The Account Control Center's main Database Administration page lists all the databases currently created in your account. Click on the name of a database to view detailed database information.

From this screen, you can change your database settings listed below:

• Optimize Now - initiate an immediate optimization of your database (this feature can only be used once per day)
• Create Backup - this will require you to provide the database's full-access password and will backup the database to a directory in your account
• Change A Password - allows you to change the any database user password
• Purge Data - allows you to delete all information from the database. Use this feature with extreme caution
• Delete Database - allows you to delete a database if no longer needed

Connections can be made to your MySQL database while you are connected to your account via SSH will be made with the MySQL command line utility. Below is a sample connection to a database using it:

username@server% mysql -hlocalhost -uusername -p username_dbname
Enter password: <enter password here>
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 3.23.54

Type 'help;' or '\h' for help. Type '\c' to clear the buffer

mysql>

Several command-line options are required -- -h signifies hostname, -u username, -p password, and the last argument should be the name of your database. You can specify your password on the command line if you'd like, but it is recommended for security reasons that you do not. MySQL will prompt for your password, as shown above. (It will not be displayed on the screen as you type it.)

Once connected via the MySQL monitor, you can use it to issue SQL commands to your database to create, populate, and delete tables. Some examples:

Creating a table:

mysql> create table test (
-> field1 int,
-> field2 char(10),
-> field3 char(10)
-> );
Query OK, 0 rows affected (0.02 sec)

Inserting data:

mysql> insert into test
-> values(1,"name","place");
Query OK, 1 row affected (0.00 sec)

mysql> insert into test
-> values(2,"noun","word");
Query OK, 1 row affected (0.00 sec)

Selecting data:

mysql> select * from test;
+--------+---------+--------+
| field1 | field2  | field33|
+--------+---------+--------+
|      1 | name    | place  |
|      2 | noun    | word   |
+--------+---------+--------+
2 rows in set (0.00 sec)

When you are finished interacting with your data, the "exit" command exits the MySQL utility:

mysql> exit
Bye