Published on Oct 1, 2011

Setup MySQL with PHPMyAdmin on Ubuntu 10.04

MySQL Relational Database Server

MySQL Relational Database Server

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 10.04.

Enable Repositories

To install MySQL server, you need to have the universe repository enabled in ubuntu Lucid 10.04. To do so, let’s modify (if needed) the apt repository file /etc/apt/sources.list and enable the universe lines by un-commenting them (remove the # at the beginning of each line). You might want to use vi to edit this file. Use the following command:

sudo vi /etc/apt/sources.list

An example file may look like the following after editing:

## main & restricted repositories
deb http://us.archive.ubuntu.com/ubuntu/ lucid main restricted
deb-src http://us.archive.ubuntu.com/ubuntu/ lucid main restricted
deb http://security.ubuntu.com/ubuntu lucid-security main restricted
deb-src http://security.ubuntu.com/ubuntu lucid-security main restricted

## universe repositories
deb http://us.archive.ubuntu.com/ubuntu/ lucid universe
deb-src http://us.archive.ubuntu.com/ubuntu/ lucid universe
deb http://us.archive.ubuntu.com/ubuntu/ lucid-updates universe
deb-src http://us.archive.ubuntu.com/ubuntu/ lucid-updates universe

deb http://security.ubuntu.com/ubuntu lucid-security universe
deb-src http://security.ubuntu.com/ubuntu lucid-security universe

Now save the file by pressing “ESC” then typing :x and then hitting enter. After that you need to update the package repositories by issuing the following commands:

sudo apt-get update
sudo apt-get upgrade

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

Configure MySQL

By default MySQL makes some assumptions for it’s configurations based on your server memory and creates a configuration file itself. But if you feel you are smart, then let’s configure it for our needs by editing the file /etc/mysql/my.cnf with vi:

sudo vi /etc/mysql/my.cnf

In this file, you’ll see almost everything is explained in the comments for the configurations. You may also like to know the meaning of these configuration settings explained here. Tune them for your system and web applications. You may like to set the following configuration values considering enhanced security and optimized for 512MB server memory:

skip-external-locking
bind-address = 127.0.0.1
key_buffer = 16M
max_allowed_packet = 1M
thread_stack = 192K
thread_cache_size = 50
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 128K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
wait_timeout = 60
myisam-recover = BACKUP
max_connections = 50
table_cache = 1024
thread_concurrency = 8
query_cache_limit = 1M
query_cache_size = 16M
skip-innodb

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

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 from here. There are multilingual packages in different file types. I prefer English language with .zip format. So let’s download it and upload it to the adminpanel directory we created inside webroot using FileZilla or similar sftp enabled ftp clients. This is recommended because as we’ve made this directory secured with password protection in the previous article and also made it mandatory to use https for accessing the contents of this directory. So let’s put phpmyadmin inside this directory to make it accessible to you only. Now as you’ve uploaded the file successfully, now let’s unzip this file using the following commands in your ssh terminal:

sudo apt-get install unzip
cd /var/www/example.com/public_html/adminpanel
sudo unzip phpMyAdmin-3.4.5-english.zip

Here you are installing the unzip program to unzip the file. In the 3rd line for the unzip command, use the filename of the file you’ve just uploaded instead of “phpMyAdmin-3.4.5-english.zip”. You may like to type first few letters of the file and then press TAB for the command line to autocomplete the filename for you.

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

sudo rm phpMyAdmin-3.4.5-english.zip
sudo mv phpMyAdmin-3.4.5-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 let’s go to your browser for starting up the setup process. As we’ve uploaded and extracted phpmyadmin to the adminpanel directory and our domain is example.com, so you need to access the url http://www.example.com/adminpanel/phpmyadmin/setup/ to start the setup process. But as you’ve not yet set the domain example.com to point to our vps ip address, so you need to access the url like this for now: http://your_ip_here/adminpanel/phpmyadmin/setup/ As the adminpanel directory is password protected, so you might be prompted for the password to access this url. The setup page will look like the following:

PHPMyAdmin Setup Wizard

PHPMyAdmin Setup Wizard

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

PhpMyAdmin Server Setup Wizard

PhpMyAdmin Server Setup Wizard

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. You can choose to review or edit the file with your favorite editor vi, if you prefer to set some advanced options which the setup script does not provide. Now you can access the phpmyadmin directory from your browser and login using MySQL root user. As per our configuration settings, the url you need to access will be like http://your_ip_address/adminpanel/phpmyadmin/ You may notice that the http:// is redirected to https:// automatically as we’ve configured to use https only for the adminpanel directory in Nginx configuration. After logging in you can do almost every mysql operation through this web interface.

So you’ve successfully installed and configured your database server and the web based management tool. Now let’s put some web applications that uses PHP and MySQL in your server and have fun with them. After that let’s configure our domain to point to the ip address of our vps, so we can just type in our domain name in the browser address bar instead of the ugly ip-address we’ve used so far.

Articles in this Step by Step VPS Setup Guide

Getting Started with VPS – The Beginners Tutorial
Introductory concepts on web servers and virtual private servers. Introducing you to the world wide web from a technical point of view.

Shared Hosting, VPS, Dedicated and Cloud Servers
Comparing the popular server hosting solutions. Basic understanding of shared, dedicated, vps and cloud servers.

Deploy a Linux VPS Server using Linode
Tutorial on deploying your linux distribution in your VPS server and setting up hostname and initial configurations

Setup NginX Web Server (Not Apache!) on Ubuntu 10.04
Analyzing why NginX web server is better for you instead of the mostly used Apache. Demonstration on how to setup and configure NginX on Ubuntu server.

Enable HTTPS (HTTP+SSL) in NginX Web Server
Tutorial on enabling secure http connections for NginX web server. Demonstrating how to self sign your SSL certificate for the VPS server.

NginX Password Protect Web Directory
Helping you to protect specific web directories with passwords for restricting access to administration section or important private files that need to be accessed through the web.

Setup PHP-FPM with APC on Ubuntu 10.04 for Faster Performance
Installing and Configuring PHP5 with Fast CGI Process Manager along with the excellent op-code cache solution APC for faster and optimized PHP backend.

Setup MySQL with PHPMyAdmin on Ubuntu 10.04
Setting up MySQL database server and configuring for better performance in low memory environment. Also setup a web based database management front-end named PHPMyAdmin.

Configure Domains and Subdomains in your VPS Running NginX
Tutorial on DNS setup for adding additional domains and subdomains. Also configuring NginX for handling each domains/subdomains.

Domain Emails for Free Using Google Apps
Setup domain specific email addresses without installing any email server in your VPS. Google Apps seems to be a better solution in this regard.

Leave a comment

Sponsored