Light on Dark -:- User Guides

Manage MySQL using PHPMyAdmin

Phpmyadmin is a browser based manager for MySQL, which allows you to set up users with detailed permissions, create databases, and manage the data in a simple manner.

It is installed like most Ubuntu packages, using apt-get install. If you are doing an install, it is always worth updating the package list first to ensure you get the latest version:

sudo apt-get update

sudo apt-get install phpmyadmin

If the Apache server is not selected use up and down arrows and spacebar to select it, then use 'tab' to select OK and 'enter'.
Select Database

Let it configure the database - just click 'enter'
Configure Database

You will need to put in the Root user password you entered during installation
Root password

Phpmyadin sets up a user and password so it can access it's own database. You do not need this password, so you can leave it blank if you want.
Application password


And that's it installed. You may need to restart Apache, if the install did not do it:

sudo service apache2 restart

and then go to your browser and type in your domain name or IP, followed by /phpmyadmin, eg

Yours might not look like this, but the functionality is still the same.

Log in

Login as root with the password set up before.
Initial view

Create a user

We are now going to create a user for ResourceSpace, and give them a database.

Click ‘Privileges’

Note the three different 'root' users, differentiated by which server they can log in from. In reality, we only need one of these and we will delete the others later, along with setting restrictions on this user for additional security. We will make sure we create a new superuser first!

Click ‘Add a new user’
Add new user

Enter the user's name and password, and check the "create database" option underneath. If you are building a single machine installation (ie ResourceSpace and MySQL are running on the same server), set the host to 'local' – it will fill in 'localhost' for you. If you intend to connect to this database from another server, change it to 'any host', but ensure you have adequate security in place to restrict access through the firewall.
New user details

Because we are creating a user specifically to access the ResourceSpace database, we do not give it any global privileges.
Global Priveleges

Click ‘Create User’
User added

We can see the SQL commands which have been run to create the new user, and the user has appeared in the user list.

Click on the Databases tab, and check the database has been created

– it may not show in the left panel till you refresh your browser.
New database in list

Note the left panel is Case Sensitive – upper case comes before lower case, and underscore(_), hyphen (-), etc are sorted differently so both database and table lists appear in different order in the two panels.

We now need to change the collation for the database. Collation controls the way non-basic-alpha characters are translated - it dates back to the times when we used 8 bit (ascii) encoding for characters, and there were not enough combinations to encode all the non-english characters. Now we use 16 bit encoding (unicode), the problem has more or less disappeared, but the default for MySQL is 'Latin' 8bit encoding while ResourceSpace uses UTF (16 bit) encoding.

Before we create any tables in the ResourceSpace database, we want to change the default encoding so the tables are created as UTF-8. To do this, click on the ResourceSpace database in the left panel and select 'Operations'.
the operations screen

Depending on the version of phpMyAdmin you are using, you're screen might look quite a bit different to this, but the functionality should still be there. On the drop down for collation, select utf8_general_ci, then click on go.

If we come back later, after the tables have been created, we should see that they're all now created as UTF-8:
files created as UTF-8


Root User Security

Just before we install our application, we want to tighten up the security for the root user. Firstly we need to create a new 'super user', so create a new user as above but do not create a new database for them, and instead click select all privileges. I have called my superuser jbb, as you can see on the list below.

Now, log out of phpMyAdmin, and log back in using your new username and password. You do this to ensure you really do have adequate permissions before removing the root user, or you will lock yourself out of the database altogether.

Go back into privileges and find the user 'root' on host localhost, and click 'edit privileges'. Remove all global privileges and save the changes, and then delete any other root users in the list, so it looks like this:

Root user access reduced

Changing the file location

We have said that the default disk allocated to an AWS server is destroyed when the server terminates, and we obviously do not want this to happen to our data. To prevent it, we need to move our data files to a secondary volume which is not going to be deleted when the instance disappears.

If you have not already done so, follow the instructions on the 'adding disk space' guide to add an additional volume.

Stop MySQL:

sudo service mysql stop

Next we need to copy the files from their current location to the new volume.

sudo cp -R -p /var/lib/mysql /mnt/storage/

This copies the files, but does not change their ownership (that is what the - p option does). If you look at this folder you will see that its permissions are set to 700, so only the owner (the mysql user) can see what is inside. Fortunately, the root user can also see these files, which means we can access them using the 'sudo' command.

cd /mnt/storage

sudo ls mysql
ls mysql

Here we can see the files and folders, and we can see that there is a folder for each database I have created. The mysql database is where MySQL holds its internal data, and phpMyAdmin creates one when it is installed. You can see 2 ResourceSpace databases, because MySQL is case sensitive - the ResourceSpace database is the one I am going to use, the resourcespace one was created in error and I will delete it later. I also have a phpBB forum set up on here - which is down while I do these changes, so better press on.

some people say you should delete all the files in this folder, and just leave the subfolders (the actual database data), but I have not found this to be necessary. If you have problems accessing your data after this process, please tell us in the forums and I will update the instructions accordingly.

Next we need to tell MySQL to use the new files.

There are 2 places where the location is set, in the MySQL config file and in apparmor. Apparmor is a security system to stop malicious software from interfering with critical system files in later versions of Linux. If you update the config file without updating apparmor, MySQL will not restart, it will just appear to hang.

Firstly, the config file:

sudo nano /etc/mysql/my.cnf

use find (<ctrl>w) to find the line starting datadir and update it to:

datadir = /mnt/storage/mysql

then save and exit (<ctrl>x, y, enter)

Next, edit the apparmor file:

sudo nano /etc/apparmor.d/usr.sbin.mysqld

Find the lines

/var/lib/mysql/ r,
/var/lib/mysql/** rwk,

and replace with the following (or type in the # (comment) at the start of these lines and paste the others afterwards)

# /var/lib/mysql/ r,
# /var/lib/mysql/** rwk,
/mnt/storage/mysql/ r,
/mnt/storage/mysql/** rwk

Save and exit, then reload apparmor using

sudo service apparmor reload

and restart MySQL with

sudo service mysql restart

Deleting (Dropping) a Database

While we were looking at moving the data files, I pointed out a database which are being created by accident, with the wrong case. I am now going to delete that database, and then have a quick look in the data files to make sure it is gone, and to verify we are using the new location.

In phpMyAdmin, select localhost and go to the Databases tab. Put a check in the box beside the database you want to delete:
selected database to delete

and then click the 'Drop' link at the bottom:
Do you really want to destroy this database

Depending on which version of phpMyAdmin you are using, you might not get the pretty pink, but you will certainly be prompted to confirm before the database is deleted. click yes, and it's gone:
database deleted

Just quickly nip back to SSH and have another look at that folder:
MySQL folder contents

and we can see that the data store for this database has been deleted them the new folder location.

What next

We have increased our MySQL security by disabling the root user and creating a new superuser account. We have, however, reduced our MySQL security by installing an application which is accessible across the Internet. If this is a concern to yourself or your IT department, please get in touch and we can suggest some fixes.

We have also copied the data folders to a more permanent location, so there is much less risk of the data being lost if the server crashes.

just making sure we have a vertical scroll bar, otherwise it jitters sideways.