Cheap VPS & Xen Server


Residential Proxy Network - Hourly & Monthly Packages

How To Install MySQL 5.6 On Ubuntu 12.10 (Including memcached Plugin)


According to What’s New in MySQL 5.6, MySQL 5.6 will bring some performance improvements over MySQL 5.5. If you want to test out MySQL 5.6, you have to install one of the development releases from the MySQL downloads page because there is no stable release yet. This tutorial explains how to install the MySQL 5.6.8rc1 (Linux Generic) development release on an Ubuntu 12.10 server.

This document comes without warranty of any kind! I want to say that this is not the only way of setting up such a system. There are many ways of achieving this goal but this is the way I take. I do not issue any guarantee that this will work for you!

 

1 Preliminary Note

In this tutorial I use the hostname server1.example.com with the IP address 192.168.0.100. These settings might differ for you, so you have to replace them where appropriate.

It is important that you haven’t installed any other MySQL version on your system before (not even the one from the Ubutu repositories) because if you did, this tutorial will not work anymore!

Because we must run all the steps from this tutorial with root privileges, we can either prepend all commands in this tutorial with the string sudo, or we become root right now by typing

sudo su

 

2 Disabling AppArmor

Your manually installed MySQL 5.6.8 might refuse to start if AppArmor is running, therefore we disable AppArmor:

/etc/init.d/apparmor stop
update-rc.d -f apparmor remove
apt-get remove apparmor apparmor-utils

 

3 Installing MySQL 5.6.8rc1 (Linux Generic)

First we create a user and group called mysql:

groupadd mysql
useradd -r -g mysql mysql

Next install the libaio1 package:

apt-get install libaio1

We will install MySQL in the /usr/local/mysql directory (with /usr/local/mysql/data being the data directory, i.e., the directory which will contain the databases). Therefore we download MySQL to the /usr/local directory now:

cd /usr/local
wget -O mysql-5.6.8-rc-linux2.6-x86_64.tar.gz http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.8-rc-linux2.6-x86_64.tar.gz/from/http://cdn.mysql.com/

Unpack MySQL, rename the directory to mysql, and make it owned by the user and group mysql:

tar xvfz mysql-5.6.8-rc-linux2.6-x86_64.tar.gz
mv mysql-5.6.8-rc-linux2.6-x86_64 mysql
cd mysql
chown -R mysql .
chgrp -R mysql .

Run

scripts/mysql_install_db –user=mysql

to create the necessary databases (like the mysql database):

Change some ownerships:

chown -R root .
chown -R mysql data

MySQL uses the my.cnf file inside /usr/local/mysql, so we don’t have to create one on our own. If you want to change MySQL settings, you can do it in that file.

The downloaded MySQL package comes with the init script mysql.server which we copy to /etc/init.d as follows:

cp support-files/mysql.server /etc/init.d/mysql.server

It is already executable, so we don’t have to change permissions.

We can now start MySQL 5.6 as follows:

/etc/init.d/mysql.server start

To make MySQL start automatically at boot time, run the following command:

update-rc.d mysql.server defaults

The MySQL commands (like mysql, mysql_secure_installation, etc.) are located in the /usr/local/mysql/bin directory which is not in the PATH which means we would have to call these commands with the full or relative path which is cumbersome. To avoid this, we create the following symlink so that we can call all MySQL commands without a path:

ln -s /usr/local/mysql/bin/* /usr/local/bin/

Finally, we should set a MySQL root password:

mysql_secure_installation

root@server1:/usr/local/mysql# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we’ll need the current
password for the root user.  If you’ve just installed MySQL, and
you haven’t set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): <– ENTER
OK, successfully used password, moving on…

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] <– ENTER
New password: <– yourrootsqlpassword
Re-enter new password: <– yourrootsqlpassword
Password updated successfully!
Reloading privilege tables..
… Success!

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] <– ENTER
 … Success!

Normally, root should only be allowed to connect from ‘localhost’.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] <– ENTER
 … Success!

By default, MySQL comes with a database named ‘test’ that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] <– ENTER
 – Dropping test database…
… Success!
– Removing privileges on test database…
… Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] <– ENTER
 … Success!

All done!  If you’ve completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

Cleaning up…
root@server1:/usr/local/mysql#

4 Enabling The memcached Plugin

MySQL 5.6 now provides a NoSQL interface via memcached (see What’s New in MySQL 5.6; this page explains how you can make use of it: http://schlueters.de/blog/archives/152-Not-only-SQL-memcache-and-MySQL-5.6.html). To use this feature, we must enable the memcached plugin – this will then automatically start a memcached daemon on port 11211, which means you don’t have to install memcached separately because it’s all included in the MySQL package.

First log into MySQL…

mysql -u root -p

… to find out about your MySQL version and all installed plugins:

mysql> select version();

mysql> select version();
+———–+
| version() |
+———–+
| 5.6.8-rc  |
+———–+
1 row in set (0.00 sec)

mysql>

mysql> show plugins;

If daemon_memcached is not listed, this means we must activate the memcached plugin:

mysql> show plugins;
+—————————-+———-+——————–+———+———+
| Name                       | Status   | Type               | Library | License |
+—————————-+———-+——————–+———+———+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| mysql_old_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INSERTED         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+—————————-+———-+——————–+———+———+
43 rows in set (0.00 sec)

mysql>

Make sure we have a test database:

mysql> SHOW DATABASES;

mysql> SHOW DATABASES;
+——————–+
| Database           |
+——————–+
| information_schema |
| mysql              |
| performance_schema |
+——————–+
3 rows in set (0.00 sec)

mysql>

If there’s no test database, create it as follows:

mysql> CREATE DATABASE test;

Leave the MySQL shell:

mysql> quit;

Make sure you are still in the /usr/local/mysql directory and run:

mysql -u root -p < share/innodb_memcached_config.sql

Then log into MySQL again…

mysql -u root -p

… and enable the memcached plugin as follows:

mysql> INSTALL PLUGIN daemon_memcached SONAME ‘libmemcached.so’;

Verify that is has been activated:

mysql> show plugins;

You should now see daemon_memcached listed:

mysql> show plugins;
+—————————-+———-+——————–+—————–+———+
| Name                       | Status   | Type               | Library         | License |
+—————————-+———-+——————–+—————–+———+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL            | GPL     |
| mysql_old_password         | ACTIVE   | AUTHENTICATION     | NULL            | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL            | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_FT_INSERTED         | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL            | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| daemon_memcached           | ACTIVE   | DAEMON             | libmemcached.so | GPL     |
+—————————-+———-+——————–+—————–+———+
44 rows in set (0.00 sec)

mysql>

Leave the MySQL shell:

mysql> quit;

Run

netstat -tap

You should see MySQL’s memcached daemon running on port 11211:

root@server1:~# netstat -tap
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 *:ssh                   *:*                     LISTEN      641/sshd
tcp        0      0 *:11211                 *:*                     LISTEN      1665/mysqld
tcp        0      0 server1.example.com:ssh 192.168.0.10:51889      ESTABLISHED 1758/1
tcp        0      0 server1.example.com:ssh 192.168.0.10:51771      ESTABLISHED 961/0
tcp6       0      0 [::]:ssh                [::]:*                  LISTEN      641/sshd
tcp6       0      0 [::]:mysql              [::]:*                  LISTEN      1665/mysqld
tcp6       0      0 [::]:11211              [::]:*                  LISTEN      1665/mysqld
root@server1:~#

 

  • MySQL: http://www.mysql.com/

Comments

comments