Connecting to a DB Instance Running the MySQL Database

The following steps will allow you to create and manage a MySQL instance within your new web space.

Before starting these steps, you will need the following information:
Server account credentials (server name, username, password)
The port number that ITServices has reserved for your MySQL instance
Knowledge of server connectivity via a Secure Shell (SSH) and prior MySQL experience is required to fully understand, implement, and test a new MySQL instance.

Create the MySQL Instance :

1) Login to the new server using SSH. Which of these servers you login to will depend on the type of account you have. If you are unsure which server to create your MySQL instance on please contact ITServices.
2) The directory that will hold your database(s) should be called www-data and should be located in your server account document root. Create the www-data directory and a temporary directory (i.e. “tmp”) under it that will hold your database(s). To do this, issue the following commands:

a) umask 077
b) cd ;
c) mkdir -p www-data/tmp
d) chmod 711 www-data www-data/tmp

3) Install the MySQL instance by issuing the following command:

Note: When issuing this command and other commands in these steps, replace PORT_NUMBER_HERE with your reserved port number

mysql_install_db --port=PORT_NUMBER_HERE --socket="$HOME/www-data/tmp/mysql.sock" --ldata="$HOME/www-data"

4) Start the database instance by issuing the following command:

mysqld_safe --port=PORT_NUMBER_HERE --socket="$HOME/www-data/tmp/mysql.sock" --datadir="$HOME/www-data" &

5) When creating your new MySQL instance, it will default to not having a password for the root user. Not having a password is not secure, therefore, the first task after starting the instance is to create the root user password:

Note: When issuing this command, replace YOUR_NEW_PASSWORD with a desired password.

mysqladmin --port=PORT_NUMBER_HERE --socket="$HOME/www-data/tmp/mysql.sock" -u root password 'YOUR_NEW_PASSWORD'

If you wish to change this password in the future, issue the following command (it will prompt you for the current password before changing it to the new password):

 mysqladmin --port=PORT_NUMBER_HERE --socket="$HOME/www-data/tmp/mysql.sock" -u root -p password 'YOUR_NEW_PASSWORD'

Recommendation: The database root account should only be used for administering the database, and should never be used to access the database from your application web pages. We recommend creating additional database users that only have necessary privileges to be used in web applications. This will increase the security of your database.
After going through the steps above, you will have an installed and running instance of MySQL.

Additional Commands :

Below are some additional commands which are common:

To login to the MySQL instance:

 mysql --socket="$HOME/www-data/tmp/mysql.sock" -u root -p

To stop the MySQL service:

 kill `cat "$HOME/www-data/uni.pid"`

To show the threads running against the MySQL instance:

 SHOW PROCESSLIST;

To find out the status of the MySQL instance:

 mysqladmin --port=PORT_NUMBER_HERE --socket="$HOME/www-data/tmp/mysql.sock" ping