MySQL Server: Installation & Management

The next fundamental piece of our web development environment is, of course, a database management system of choice: MySQL.

Installing MySQL server is an easy task, simply issue the following commands in a Terminal window:

> sudo apt-get install mysql-server

You will be asked to enter the password you wish to set for the mysql root user.
Once the install process has completed, verify that mysql is running using:

> sudo service mysql status

Easy! Once you have installed MySQL, we should activate it with this command:

> sudo mysql_install_db

Finish up by running the MySQL set up script:

> sudo /usr/bin/mysql_secure_installation

In order for other computers on your network to view the server you have created, you must first edit the “Bind Address”. Begin by opening up Terminal to edit the my.cnf file.

> sudo nano /etc/mysql/my.cnf

Change the line

bind-address = 127.0.0.1

Changing the 127.0.0.1 to your IP address.

> sudo apt-get install libapache2-mod-auth-mysql php5-mysql

Now just restart Apache and you are all set!

It is fundamental to protect access to you databases, setting a root password is essential to this point. In case the installer didn't ask you for a password, or you left it blank, you'll have to set it “manually”, here is how:
Note that the same procedure can be used to change the current root user's password.

mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('yourpassword');

Make sure to change yourpassword to a password of your choice.

This is for a VirtualBox system only
As was done for our Virtual Hosts files, we'll configure our VirtualBox VM so that all our database files will be stored inside a directory that does not reside inside our VM's but rather on our host's filesystem.

Please refer to the apache virtual host setup section for deeper details on this configuration, as it relies on the same principles.

Let's create this directory on the host's side (feel free to adapt this folder location to wherever you see fit):

> mkdir /home/<username>/Documents/webdev/mysql

Then, configure it as a VirtualBox shared folder:

> VBoxManage sharedfolder add "lamp-xd" --name "mysql-share" --hostpath "/home/<username>/Documents/webdev/mysql"

By default all MySQL related databases files are stored under /var/lib/mysql. What we'll do is first stop the mysql service, then create a copy of the existing data, mount our “host” shared folder with the correct access rights, replace the copied data inside the newly mounted directory and restart the mysql service.

Inside the VM:

> service mysql stop
> sudo cp -a /var/lib/mysql /var/lib/mysql.bak

> id mysql
uid=104(mysql) gid=108(mysql) groups=108(mysql)

> sudo nano /etc/fstab
ADD:
# automount mysqlshare as mysql user
mysql-share	/var/lib/mysql/	vboxsf	defaults,uid=104,gid=108	0	0

> sudo mount /var/lib/mysql
> cp -a /var/lib/mysql.bak/* /var/lib/mysql/

All should now be in place to have MySQL use our shared folder as physical database storage location.

> sudo service mysql start

Now, if all is running well, we can get rid of the backup files we initially created. In case of necessity, those files are, in fact, still present on the virtual machine's hard disk, unmounting the shared folder would reveal them.

> sudo rm -r /var/lib/mysql.bak

Even though your database files will, from now on, be stored on your host filesystem, you might find it easier to use an integrated tool to manage the different aspects of your MySQL configuration (i.e. mainly users and tables management). This could be done using PhpMyAdmin, that could be deployed inside a dedicated virtual host.

Although, my personal advise is to use the more versatile MySQL Workbench application. The primary reason being that this one will allow you to manage not only your local development databases, but also your remote production servers, all from the same interface. Using ssh tunneling, you won't even have to allow external MySQL connections on your production machines.