MySQL databases

MySQL databases

This document provides information about MySQL databases. This includes creating databases, deleting databases, adding users to the database, changing database passwords, and customizing the database using scripts. The control panel makes it easy to manage the database for those people who do not have in-depth knowledge in this area.

Creating databases

To create a database, log in to the Control Panel, and then click the Account Manager icon, and then click the “Manage SQL” link.

In the main menu of the control panel, click the “MySQL Databases” icon, and then click “Create Database”.

Select the name of the database, you will be reminded what the database is for. For example, enter “chat” if the database is intended to be used with a chat script. The database name always precedes your username and underscore. If you select “chat” for the name, the real name of the database will be username_chat.

The database user name is the name for logging into the database. May be the same as the name of the database. In the above example we have chosen a different user name (usrdb). As with the database name, the database username is also preceded by your username and an underscore (in this case playtester_usrdb).

Once you have selected the database name and user name, enter the database password and click “Create” below.

Deleting databases

A list of all databases is present in the main menu of MySQL databases. To delete a database, check the box next to the name of the database you want to delete, and then click “Delete”. In the above example, we delete the database named playtester_db.

You can also restore, verify or optimize your database. Click the right button and Direct Admin will automatically do everything for you! You will also be prompted to make changes to the notification menu.

You can also upload/download MySQL backups from your computer. To download the backup you want, click the “Download” button; to download, click the “Download” button below.

Adding users to the database

There may be cases where you want to have more than one login and password assigned to a database. To add additional users to the database, follow these steps:

1. In the main menu of the database, click on the name of the database you want to change.
2. Click “Create New User” or “Add Existing User” on the right side of the screen.

• When creating a new user, enter your user name and password, and then click “Create”.
• If you are adding an existing user, select the desired user and click “Assign”.

You can see how many users each database has, in the main menu of MySQL databases.

Deleting users from the database

To delete database users, first click on the database name in the main MySQL database menu. In this example, we clicked on the database “playtester_db”, which has two users (see Figure above).

The playtester_db database has two users: playtester_db and playtester_tuth . To delete a database user, simply check the box next to his name and click “Delete”.

Changing database passwords

To change the database password, first click on the database name in the main MySQL database menu. The list of database users will appear (see Figure above). To change the password for a database user, click the “Change Password” link and enter a new password, then click “Change”. You don’t need to know the old password to make this change.

Setting up access to the database in the scripts

Most scripts need to know four things about your database: database name, database user name, password, and host name.

The database name always takes the form ControlPanelUsername_DatabaseName, where ControlPanelUsername is the login name you use to access the control panel. DatabaseName is the name you give the database from the MySQL database menu. For example, if you login to the control panel as “tut” and create a database named “host”, the true database name would be tut_host – you would want to enter that database name in your script configuration.

The database user name can be the same as the database name, or you can choose a different user name for the database. The database user name takes the form ControlPanelUsername_DatabaseUserName.

The database password is set when the database is created. The password must be different from the control panel password. The script must know the database password to change the database.

The host name tells the script where to access the database. This is usually referred to as “localhost” because the script and the database are located on the same server. If the script does not allow text entry in the hostname configuration, enter the IP address of your site.

Note: After creating the database, the control panel will take you to a screen that provides all this information. Simply take the values from this page and enter them into the configuration of your script.

Example: if you have a database named playtester_db and the user name in that database is playtester_tuth, your password is “tuthost”, and your access node is localhost, your php script for the database credentials will look like this

<?php
$servername = "localhost";
$username = "playtester_tuth";
$database = "playtester_db";
$password = "";
?>

Access to phpMyAdmin

To access phpMyAdmin, log in to the control panel and then click the “Tools” icon and then the “phpMyAdmin” link.

phpMyAdmin is a tool written in PHP that allows you to administer your MySQL databases. You can execute SQL statements, work with fields (add, edit, delete), work with tables (create, modify, delete), create additional databases and much more. phpMyAdmin is designed for experienced users.

You can find more information on how to use phpMyAdmin here.