MySQL

Prerequisites

  • You have followed the steps in Initial Setup. In particular the section on setting the hostname of your server.

Installing MySQL

Begin by issuing the following command in your terminal:

sudo apt-get install mysql-server

You will be prompted to set a password for the MySQL root user. Choose a strong password and keep it in a safe place for future reference.

hardy-01-mysql-root-password.png

The MySQL server package will be installed on your server, along with dependencies and client libraries.

After installing MySQL, it's recommended that you run mysql_secure_installation, a program that helps secure MySQL. mysql_secure_installation gives you the option to disable root logins from outside localhost, remove anonymous user accounts, and the option to remove the test database. Additionally, it allows you to set your root password. Run the following command to execute the program:

sudo mysql_secure_installation

After running mysql_secure_installation, MySQL is more secure and ready to be configured.

Configuring MySQL

Tuning

The steps in this section require changes to the configuration file /etc/mysql/my.cnf. However, I prefer to add additional config files to the /etc/mysql/conf.d directory. Files in this directory get included after the main my.cnf file is read, which allows for a tidy way to override the default values that come pre-installed.

By default, MySQL makes some assumptions about your server environment with respect to memory. To configure MySQL more conservatively for a lower memory VPS, you'll need to change of the memory related configuration options.

Create a new file called /etc/mysql/conf.d/mysqld_performance_tuning.cnf to hold our changes.

[mysqld]
key_buffer = 16K
max_allowed_packet = 1M
thread_stack = 64K
table_cache = 4
sort_buffer = 64K
net_buffer_length = 2K

MySQL uses 10MBs of RAM for InnoDB tables. Remove support for InnoDB tables if you do not use them. To remove support for InnoDB add the following to the [mysqld]] block.

skip-innodb

These settings are only suggested values for a low memory environment; please feel free to tune them to appropriate values for your server.

Moving the Data Directory

You can use the following command to list the current databases in mysql

mysqlshow -u root -p

If you have added another disk that you wish to use for storing all MySQL databases then you can migrate the default data dir to this disk.

Shutdown the MySQL database, if it is running:

sudo /etc/init.d/mysql stop

Either open the main configuration file (/etc/mysql/my.cnf or add a new file /etc/mysql/conf.d/mysqld_data_dir.cnf.

The property you need to change is named datadir. This property is usually located in the [mysqld_safe] section in the configuration file.

[mysqld]
datadir      = /mnt/data/mysql

Create the directory you specified in the datadir property:

mkdir -p /mnt/data/mysql/

Check to make sure the user assigned to execute mysql has read/write privileges on this directory. You may need to modify the directory settings using chown and chmod. It's easy to refer to the original datadir for the ownership and rights. You may have to su to root to do this.

Move the databases listed in the first step to the new data directory:

mv /var/lib/mysql/ /mnt/data/mysql/

Note: You can use the copy (cp) command instead if you prefer. Remember to remove the original files once the migration is complete and tested.

After all the databases have been migrated to the new data directory, you should start MySQL:

sudo /etc/init.d/mysql start

The database files will now be managed under the new data directory. If you encounter any problems during startup, you check the hostname.err file located in the data directory.

You can check all of the configuation changes by veiwing the server variables:

mysqladmin -u root -p variables

Remote Access

I have outlined two approaches to this. The first being my new chosen method which is to connect over an SSH tunnel. This method avoids the need to open up the firewall for remote MySQL connections and allows all MySQL users to be tied to localhost connections only.

The second method is the more traditional approach that has a specific user that can be accessed from a remote network location.

Access of SSH

The easiest way to do this is to use a client app that supports SSH tunnelling. I am currently using Navicat for Mac. When creating the connection you enter localhost or 127.0.0.1 as the hostname and the root username and password. You can then access the SSH settings and enter the hostname of your server and the username and password of a SSH privileged user.

Here are some screen shots from Navicat (v7.1.7):

navicat-connection-properties-general.pngnavicat-connection-properties-ssh.png

News Flash
After years of using Navicat I have switched to the Sequel Pro which is free and now supports all the features I require from a MySQL client tool.

Access with remote user account

MySQL will bind to localhost (127.0.0.1) by default. Allowing unrestricted access to MySQL on a public IP is generally not advised, but you may change the address it listens on by modifying the bind-address parameter. If you decide to bind MySQL to your public IP, you should implement firewall rules that only allow connections from specific IP addresses.

To change the binding add a custom override conf file to the /etc/mysql/conf.d folder.

sudo nano /etc/mysql/conf.d/mysqld_access.cnf

Paste in the following lines:

[mysqld]
# Allow MySQL to listen on all address for connections
# i.e. allow all network connections
bind-address        = 0.0.0.0

Now to open the firewall up you need to first edit the existing test rules. Temporarily give yourself root access to facilitate this:

sudo -i

Firstly lets make the changes to our test rule set:

nano /etc/iptables.test.rules

Paste in the following lines to open up firewall to standard mysql requests on port 3306.

# Allow MySQL connections
-A INPUT -p tcp --dport 3306 -j ACCEPT

Then lets apply those rules to our server.

sudo iptables-restore < /etc/iptables.test.rules

Let's see if the changes are there:

sudo iptables -L

To save our rules permanently (so that they take place when the server reboots) issue a:

sudo iptables-save > /etc/iptables.up.rules

You can also create a MySQL user that has limited privileges that you can use to connect remotely. To do this we can add a user from the

mysql -u root -p
mysql> USE mysql;
mysql> CREATE USER 'remote'@'%' IDENTIFIED BY 'password here';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,RELOAD,ALTER,SHOW DATABASES,LOCK TABLES,CREATE USER ON *.* TO 'remote'@'%';
mysql> FLUSH PRIVILEGES;
mysql> QUIT:

NOTE: For a complete list of grant options see the the MySQL GRANT Command Reference page.

Using MySQL

The standard tool for interacting with MySQL is the mysql client program. To get started, issue the following command at your prompt:

mysql -u root -p

You will be prompted to enter the root MySQL user's password. Enter the password you assigned when you installed MySQL, and you'll be presented with the MySQL monitor display:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 5.0.75-0ubuntu10 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

If you've forgotten your root password, use the package reconfiguration tool to change that password:

sudo dpkg-reconfigure mysql-server-5.0
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License