Install MYSQL 5.6 in ubuntu 12.04/14.04 and set password and remote access

Part 1 : The steps to install mysql 5.6 in ubuntu 12.04

Note: it is also possible that, when you install mysql, there is a pop-up window to ask you to enter the password for root, and you/'d better remember that in case there will be some extra work to set password in the Part 2.

1. check the system OS version
uname -i

2.If there is an old mysql version running on your system and you need to uninstall it, and if you also install a .deb version, you need to use the "dpkg" one to uninstall:
sudo apt-get remove mysql-server mysql-server-5.5 mysql-server-core-5.5
sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo dpkg --pure mysql-server mysql-client mysql-common
sudo 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##
sudo find /var/lib/mysql/ -type f -delete ##delete all the database## 
sudo rm -r /opt/mysql ##delte all the mysql lib##

3. download mysql 5.6 package
Then based on the OS version result in 1, you can download the corresponding mysql package in the mysql official website http://dev.mysql.com/downloads/mysql/.

I use the 32bit version, you can also use the wget in the ubuntu terminal to download the package:
wget http://www.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.12-debian6.0-i686.deb/from/http://cdn.mysql.com/ -O mysql-5.6.12-debian6.0-i686.deb

4. Install the .deb file
dpkg -i mysql-5.6.12-debian6.0-i686.deb

5. unique and modify my.cnf
Defaultly, the my.cnf is located in /etc/mysql/my.cnf, here we need to move it:
mv /etc/mysql/my.cnf /etc/my.cnf
Change the parameters in my.cnf, you can also select the files' paths  that you needed:
basedir = /opt/mysql/server-5.6
lc-message-dir = /opt/mysql/sever-5.6/share

6. locate startscript
cp /opt/mysql/server-5.6/support-files/mysql.server /etc/init.d/mysql.server

**If you need  mysql to autostart when the system start up:
update-rc.d mysql.server defaults

7. Install libaio1
As libaio1 is probably not installed:
apt-get install libaio1

8. Create user and Change owner of the files
groupadd mysql
useradd -r -g mysql mysql
chown -R mysql /opt/mysql/server-5.6
chgrp -R mysql /opt/mysql/server-5.6

9 update mysql information schema info and remove confusing my.cnf:
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 as discussed in 2. 
sudo /opt/mysql/server-5.6/scripts/mysql_install_db --user=mysql --datadir=/var/lib/mysql
rm /opt/mysql/server-5.6/my.cnf

10. start mysqld
sudo service mysql.server start
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

11. Now you can use "mysql" to enter your mysql client, but will get many rights/access issues, we need to set the password for user, this is discussed in Part 2 blelow.
shell>mysql

Then the installation process is finished. We will set the password for the root user.

Part 2: The steps to set password for root. 
If there is no pop-up window for the root password, and if you forget it, then you can set/reset it as below.

Detail explanation can be found in mysql official website.
1. Stop mysqld:
sudo service mysql.server stop (or sudo service mysql stop)

2. Start mysqld with necessary options
shell>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.

3. Connect to the mysql-client
shell>mysql

4. set password:
mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass')WHERE User='root';
mysql> FLUSH PRIVILEGES;

5. Stop mysql-client and start it again without specific options:
sudo service mysql.server stop (or sudo service mysql stop)
sudo service mysql.server start (or sudo service mysql start)

6. Connect to the mysql-client using root with password:
shell>mysql -u root --password=abc --port=3306 --pager="less -SFX"
or
shell>mysql -u root -p'abc' --port=3306 --pager="less -SFX"
Pay attention to the password format if you use "-p" instead of "--password" option. You can change the port number in my.cnf.

If more user need to be added, follow the guild here. 

Part 3: Set database for remote access:

By default, the mysql server can only be accessed by localhost/127.0.0.1/hostname(need add host name in file /etc/hosts), you can not access it using the IP. This means you can only access it like below:
>mysql -h localhost/127.0.0.1 -u username --password=abc --port=3306
or 
>mysql -u username --password=abc --port=3306

But you can not act as a remote access like below:
>mysql -h homename/hostIP -u username --password=abc --port=3306


And if you want have remote access to the databse, using the IP/hostname you need to follow steps as below:

1. Add hostname in  file /etc/hosts
Change line:
127.0.0.1 localhost
to 
127.0.0.1 localhost hostname

2. Comment the line which limits the access only to local in file /etc/mysql/my.cnf.
Change line:

bind-address           = 127.0.0.1
skip-networking
to 
#skip-networking
#bind-address           = 127.0.0.1

3. After change my.cnf, the mysql server need be restated. 

>sudo service mysql restart

4. Creat user/root as below in mysql client:

mysql> Create user 'root'@'%' identified by 'password';

mysql> grant all privileges on *.* to 'root'@'%';

mysql> Create user 'username'@'%' identified by 'password';

mysql> grant all privileges on *.* to 'username'@'%';

Then the database should have remote access now.

Part 4: Some 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.
shell>/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...
shell>

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)

3. Remote access setting
If the database server needs to be accessed by other remote servers, then needs 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:

shell>sudo apt-get install php5-mysql

reference:

uninstall mysql: 
http://debbase.wordpress.com/2012/07/10/how-to-completely-remove-mysql-installation-in-ubuntu-12-04-lts-precise-pangolin-64bit/
install 5.6
http://www.peterchen.net/2013/02/20/en-how-to-install-mysql-5-6-on-ubuntu-12-04-precise/
http://www.4tm.biz/blog/2013/mysql-5-6-on-ubuntu-12-04-lts.html
set password for root:
http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html#resetting-permissions-generic