/ PHP

Setup MySQL with PHPMyAdmin on Ubuntu

MySQL is the world’s most popular free and open source database server. It will help us to store and retrieve data in an efficient way for our web applications. As the name suggests, it supports relational database structures and can serve SQL queries. Most web applications today use this as their primary form of data storage and retrieval system. So let’s install and configure MySQL in our VPS server running ubuntu.

Installing MySQL

Now lets install MySQL server using the following command:

sudo apt-get install mysql-server

You will be prompted to set a password for the MySQL root user. Choose a strong password and remember it.

In the default installation process, MySQL creates some test users with known default passwords. This is good for learning purposes, but is insecure while using them in a live server. So it’s recommended that you run mysql_secure_installation in order to help secure MySQL. While running mysql_secure_installation, you will be presented with the opportunity to change the MySQL root password, remove anonymous user accounts, disable root logins outside of localhost, and remove test databases. It is recommended that you answer yes to these options except changing the root password, as you’ve already selected a strong one I guess. If you are prompted to reload the privilege tables, select yes for that one also. Run the following command to execute the program:

sudo mysql_secure_installation

Now you can use the command line mysql client to login and do queries. To login as root user for MySQL, use the following command:

mysql -u root -p

It’ll ask you to enter the root password you’ve configured while installing MySQL. You’ll see the mysql prompt after successful login. With this command line tool, you can run MySQL queries for your server. But this might be a bit inconvenient to you and if you ever wondered if there is any good web based GUI tool to manage the MySQL server databases and table structures, then you might like to have PHPMyAdmin in your server.

Setup PHPMyAdmin

Download the latest phpmyadmin package. There are multilingual packages in different file types. I prefer English language with .zip format. So let’s download it and upload it to our server using FileZilla or similar sftp enabled ftp clients. Now after uploading the file successfully, let’s unzip this file using the following commands in your ssh terminal:

sudo apt-get install unzip
cd YOUR_DIRECTORY
sudo unzip phpMyAdmin-4.3.7-english.zip

After unzipping let’s delete the zip file and rename the extracted folder into a shorter prettier name:

sudo rm phpMyAdmin-4.3.7-english.zip
sudo mv phpMyAdmin-4.3.7-english phpmyadmin

Now let’s create a config directory for phpmyadmin and apply necessary permissions:

cd phpmyadmin
sudo mkdir config
sudo chmod o+rw config

Now let’s copy the sample configuration file to the directory we’ve just created and set necessary write permissions to it for now:

cp config.sample.inc.php config/config.inc.php
chmod o+w config/config.inc.php

Now access the phpmyadmin directory from web using your browser.

Please note that the directory for phpmyadmin must be configured to be served using php in your server configuration files. For example if you use nginx, the directory or it's parent directory must be configured to be served as php application in nginx configuration files.

You should see the setup page something like the following:

Setup wizard PHPMyAdmin

Now Click the New Server Button which will display the following wizard:

PHPMyAdmin Setup Server

Now fill out the necessary information for all of the tabs. Leave the fields to default values for the setting you don’t understand. If you want to find the meaning of a setting, then you can click the info icons beside each configuration.

Now click the save button to save the configurations you’ve given here. Now go on with the other links (features, navigation frames etc.) in the setup page and set your preferences and click save. The setup wizard will automatically save this settings to the config file we have in the config folder. You might see the following warning messages.

  • Use SSL – You should use SSL connections if your web server supports it
  • PHP extension to use – You should use mysqli for performance reasons
  • Blowfish secret – You didn’t have blowfish secret set and have enabled cookie authentication, so a key was automatically generated for you. It is used to encrypt cookies; you don’t need to remember it.

You can ignore them for now as you’ve already placed it inside a password protected directory and already using ssl for that directory. Once the file has been saved, it must be moved out of the config/ directory and the permissions must be reset as a security measure:

sudo mv config/config.inc.php .
sudo chmod o-rw config.inc.php
sudo chown www-data config.inc.php
sudo rm -rf config

Don’t miss the final dot(.) in the first command which represents current directory and the full command is to move the config.inc.php file to the current directory which is the phpmyadmin directory.

Now let's access the phpmyadmin directory from web and you should see the login screen. Use your mysql username password to login and voila, you should be able to see all the databases and do administration and other stuffs.