Install MySQL 5.6 on Ubuntu 12.04 (Using *.deb)

Install MySQL 5.6 on Ubuntu 12.04 LTS 

by Shi, September 26 2013

This article introduces how to install the lastest version MySql 5.6 on ubuntu 12.04 using *.deb debian packages, since the installer will install the mysql in /opt/mysql, and not the standard directory /usr/local/mysql, the installer does not set up anything so you have to set up manually. It's very very cumbersome, because you may experience all kinds of errors if some steps are missed or inappropriately operated. I have been struggling in this for quite a long time so that's why I come out and write down the processes of how I succeed finally.

Note: This tutorial is oriented to those who want to install MySQL 5.6 on Ubuntu using Debian packages, specifically for x86_64bit machine, and majorly for fresh installation (instructions on upgrade from old version to 5.6 are given accompany) there are some other tutorials using binary or other ways to install you can find on Google, but I won't bother that.


Part I: Install mysql 5.6 in ubuntu 12.04

Step 1.

Disabling AppArmor

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

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

Actually, you can do step 1 as the last step if you like, I have tested, it doesn't matter you do it now or do it later.

Step 2.

Download & Install MySQL 5.6 deb package from Official Site

Two ways:
(1) You can get MySQL installers from https://dev.mysql.com/downloads/mysql/#downloads
then choose Debian Linux. the package is about 283MB.

(2) Or, you can  get MySQL 5.6.x using command line

sudo wget -O mysql-5.6.deb https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.14-debian6.0-x86_64.deb/from/http://cdn.mysql.com/

Then install Mysql 5.6.x

sudo dpkg -i  mysql-5.6.14-debian6.0-x86_64.deb

(you can delete the package after you installed, use:  rm mysql-5.6.14-debian6.0-x86_64.deb  )

then you need to install dependency libaio1 before you proceed

sudo apt-get install libaio1

========================================================================

Note: If there is an old mysql version running on your system already then you need to uninstall it before you proceed to install the new version, and you want to back up the old verison first:

(a) Backup old version of MySQL Data
You will need this only if you are upgrading…

service mysql stop
cp -pr /var/lib/mysql/ /var/lib/mysql.old

At this point your previous MySQL is stopped and you have mysql’s data directory and mysqldump output in ~/backup directory. If all goes well you will not need backups!

(b) Remove previous version of MySQL Packages

sudo apt-get remove mysql-server-5.5 mysql-server-core-5.5 mysql-client-5.5 mysql-client-core-5.5 mysql-common
sudo apt-get remove --purge mysql-server-5.5 mysql-server-core-5.5 mysql-client-5.5 mysql-client-core-5.5 mysql-common
sudo dpkg --purge mysql-server-5.5 mysql-server-core-5.5 mysql-client-5.5 mysql-client-core-5.5 mysql-commonsudo apt-get autoremove
sudo apt-get autoclean
deluser mysql
sudo updatedb

Sometimes after above cmds,using "locate mysql" still lists many file related to mysql, that is because the system database is not updated.

Delete all the database

sudo find /var/lib/mysql/ -type f -delete

Delte all the mysql lib

sudo rm -r /opt/mysql

========================================================================

Step 3.  

Setup MySQL 5.6 Startup Script

Move your my.cnf from /etc/mysql/my.cnf which is the Ubuntu default location to /etc/my.cnf which is the one mysql uses for the start scripts. 

sudo mv /etc/mysql/my.cnf /etc/my.cnf

note that the /etc/mysql/conf.d/* files will be included anyway.

To get mysql started you will need to copy the new startscript to /etc/init.d, use cp /opt/mysql/server-5.6/support-files/mysql.server /etc/init.d/mysql.server. 

As you probably want mysql to start automatically on system startup, use update-rc.d mysql.server defaults to create a standard runlevel configuration.

sudo cp /opt/mysql/server-5.6/support-files/mysql.server /etc/init.d/mysql.server
sudo update-rc.d -f mysql remove
sudo update-rc.d mysql.server defaults

(Note: MySQL 5.6 depends on libaio which we already installed)

Step 4.  

Update configuration & Environment settings

(a)Create user and set owner & group of the mysql files

groupadd mysql
useradd -r -G mysql mysqlchown -R mysql /opt/mysql/server-5.6/
chgrp -R mysql /opt/mysql/server-5.6/

(b) Update mysql config file

By default, for MySql 5.6, the my.cnf is located in /etc/mysql/my.cnf, here we need to move it to /etc/my.cnf:

Use vim or whatever editor to edit my.cnf:

sudo vim /etc/mysql/my.cnf
basedir = /opt/mysql/server-5.6
lc-messages-dir = /opt/mysql/server-5.6/share

You may also need to tweak few other settings.
For example: table_cache has been renamed to table_open_cache

(c) Update envirionment variable: $PATH

Add /opt/mysql/server-5.6/bin into system path and use command source /etc/environment to update immediately

vim /etc/environment
PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/opt/mysql/server-5.6/bin"
source /etc/environment

(d) confirm changes

which sql

It should show: /opt/mysql/server-5.6/bin/mysql

(e) Update mysql information schema to version 5.6

/opt/mysql/server-5.6/scripts/mysql_install_db --user=mysql --datadir=/var/lib/mysql

Caution: This will create my.cnf in /opt/mysql/server-5.6/ which sets SQL mode to strict. Your applications may have problems with this mode. For compatibility reasons you should DELETE this confusing my.cnf and only rely on your /etc/my.cnf. Delete it by  rm /opt/mysql/server-5.6/my.cnf. You won’t be able to overwrite this setting with your /etc/my.cnf and remove STRICT_TRANS_TABLES from your sql_mode. Read more about mysql_install_db here.

Also, pay attention that the "datadir" is the database storage path, if you need to re-install the mysql, generally it is better to delete all the database in this folder.

rm /opt/mysql/server-5.6/my.cnf

Step 5.  

Start mysql

service mysql.server start

Or, you can also start it using mysqld, which have no datadir setting:
sudo /opt/mysql/server-5.6/bin/mysqld

You can checking the difference of this two methods using :
ps aux | grep mysqld

Finally, we reach this point, and hopefully everything goes fine. If you encounter any errors please refer to the below Errors part, Now you can use "mysql" to enter your mysql client, but will got many rights/access issues, therefore, we need to set the password for user.

Part II: Set password for root

Note: You may check the official site for detailed explanations.

Step 1.  Stop mysqld:

sudo service mysql.server stop

Step 2. Start mysqld with necessary options:

sudo /opt/mysql/server-5.6/bin/mysqld --skip-grant-tables --skip-networking

If your mysql server is in a risk of remote client access, then it is better to use --skip-networking.

Step 3. Connect to the mysql-client:

mysql

Step 4. Set password:

UPDATE mysql.user SET Password=PAWWSORD('MyNewPasswd') WHERE user='root';
FLUSH PRIVILEGES

Step 5. Stop mysql-client and start it again without specific options:

sudo service mysql.server stop
sudo service mysql.server start

Step 6. Connet to the mysql-client using root with password:

mysql -u root --password=abc --port=3306 --pager="less -SFX"

OR

mysql -u root -p'abc' --port=3306 --pager="less -SFX"

Pay attention to the password format if you use "-p" not "--password" option. You can change the port number in my.cnf.

Part III:  Possible issues

1. After update mysql schema using mysql_install_db, you will find mysql suggests to use
mysql_secure_installation to help to set the password, I tried, then I got info as below and tried many methods suggested in the internet, but had no luck.
/opt/mysql/server-5.6/bin/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):
mysql: unknown option '--connect-expired-password'
Enter current password for root (enter for none):
mysql: unknown option '--connect-expired-password'
Enter current password for root (enter for none):
mysql: unknown option '--connect-expired-password'
Unable to connect to the server as root user, giving up.

Cleaning up...

2. If you forget to start the mysqld and run mysql, then you will get error like below:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

A possible Solution:

modify my.cnf file using sudo vim /etc/mysql/my.cnf, find the line bind-address = 127.0.0.1 and revised it to bind-address = localhost, then restart mysql using sudo /etc/init.d/mysql restart

3. Remote access setting

If the database server needs to be accessed by other remote servers, then you need to configure the setting in the my.cnf file.
Comment these lines in my.cnf:
skip-networking bind-address = 127.0.0.1

4. Fatal error: Call to undefined function mysql_connect()
On ubuntu, this means the php does not have the mysql module, or the module is not correctly connected:
sudo apt-get install php5-mysql

Reference:
1. http://dev.mysql.com/doc/refman/5.6/en/linux-installation.html
2. http://www.peterchen.net/2013/02/20/en-how-to-install-mysql-5-6-on-ubuntu-12-04-precise/
3. http://rtcamp.com/wordpress-nginx/tutorials/mysql/mysql-5-6-ubuntu-12-04/
4. http://geminihzh.blogspot.com/2013/07/install-mysql-56-in-ubuntu-1204-and-set.html