Percona MySQL Upgrade from 5.5.40 to 5.6.21

Steps to do upgrade percona mysql

1. Download the mysql percona 5.5.40 binary installation

[root@vm129-79 my_percona]# ls -ltr

total 170240

-rw-r--r--.  1 mysql mysql  65263318 Oct  6 13:37 Percona-Server-5.5.40-rel36.1-707.Linux.x86_64.tar.gz

-rw-r--r--.  1 mysql mysql 109043478 Nov 20 05:05 Percona-Server-5.6.21-rel70.1-698.Linux.x86_64.tar.gz

lrwxrwxrwx.  1 mysql mysql        64 Dec 25 06:43 mysql5.5.40 -> /mysql/my_percona/Percona-Server-5.5.40-rel36.1-707.Linux.x86_64

lrwxrwxrwx.  1 mysql mysql        64 Dec 25 06:49 mysql5.6.21 -> /mysql/my_percona/Percona-Server-5.6.21-rel70.1-698.Linux.x86_64

drwxr-xr-x. 13 mysql mysql      4096 Dec 25 08:01 Percona-Server-5.6.21-rel70.1-698.Linux.x86_64

drwxr-xr-x. 13 mysql mysql      4096 Dec 25 08:01 Percona-Server-5.5.40-rel36.1-707.Linux.x86_64

2. Extract the binary installation

tar -xvf Percona-Server-5.5.40-rel36.1-707.Linux.x86_64.tar.gz

drwxr-xr-x. 13 mysql mysql      4096 Dec 25 08:01 Percona-Server-5.5.40-rel36.1-707.Linux.x86_64

3. Do the softlink

lrwxrwxrwx.  1 mysql mysql        64 Dec 25 06:43 mysql5.5.40 -> /mysql/my_percona/Percona-Server-5.5.40-rel36.1-707.Linux.x86_64

4. Start the mysql server.

<<end of mysql 5.5.40  installation>>

5. d/w the percona 5.6 version mysql

6. Extract the binary

tar -xvf Percona-Server-5.6.21-rel70.1-698.Linux.x86_64.tar.gz

7. Do the softlink

ln -s /mysql/my_percona/Percona-Server-5.6.21-rel70.1-698.Linux.x86_64 /mysql/my_percona/mysql5.6.21 

lrwxrwxrwx.  1 mysql mysql        64 Dec 25 06:49 mysql5.6.21 -> /mysql/my_percona/Percona-Server-5.6.21-rel70.1-698.Linux.x86_64

8. Upgrade the mysql with the new d/w'ed file

a. Take a backup of my.cnf files

b. Move the my.cnf(which already placed in old basedir) to new basedir

9. Shutdown the mysql server

10. Start the mysql service from the new basedir(version 5.6.21)

[root@vm129-79 mysql5.6.21]# /mysql/my_percona/mysql5.6.21/bin/mysqld_safe --basedir=/mysql/my_percona/mysql5.6.21 --datadir=/mysql/my_percona/mysql5.5.40/data/ --defaults-file=/mysql/my_percona/mysql5.6.21/my.cnf --user=mysql

141225 08:03:31 mysqld_safe Logging to '/mysql/data/mysql-error.log'.

141225 08:03:31 mysqld_safe Starting mysqld daemon with databases from /mysql/my_percona/mysql5.5.40/data/

-- Need to provide the path from ./bin/mysqld_safe

[root@vm129-79 my_percona]# cd mysql5.6.21/

[root@vm129-79 mysql5.6.21]# ./bin/mysqld_safe --defaults-file=/mysql/my_percona/mysql5.6.21/my.cnf --basedir=/mysql/my_percona/mysql5.6.21 --datadir=/mysql/my_percona/mysql5.5.40/data/ --user=mysql &

[1] 8894

[root@vm129-79 mysql5.6.21]# 141225 08:17:30 mysqld_safe Logging to '/mysql/my_percona/mysql5.5.40/data//vm129-79.dlab..com.err'.

141225 08:17:30 mysqld_safe Starting mysqld daemon with databases from /mysql/my_percona/mysql5.5.40/data/

FATAL Error, because need to provide the socket file for this instance

[root@vm129-79 mysql5.6.21]# 

(reverse-i-search)`up': /mysql/my_percona/mysql5.6.21/bin/mysql_^Cgrade -uroot --datadir=/mysql/my_percona/mysql5.5.40/data/

[root@vm129-79 mysql5.6.21]# ./bin/mysql_upgrade -uroot -p -P 3307

Enter password: 

Looking for 'mysql' as: ./bin/mysql

Looking for 'mysqlcheck' as: ./bin/mysqlcheck

FATAL ERROR: Upgrade failed

Socket file provided and the upgrade has been completed.

[root@vm129-79 mysql5.6.21]# ./bin/mysql_upgrade -uroot -p -P 3307 -S /tmp/pmysql.sock

Enter password: 

Looking for 'mysql' as: ./bin/mysql

Looking for 'mysqlcheck' as: ./bin/mysqlcheck

Running 'mysqlcheck' with connection arguments: '--port=3307' '--socket=/tmp/pmysql.sock' 

Warning: Using a password on the command line interface can be insecure.

Running 'mysqlcheck' with connection arguments: '--port=3307' '--socket=/tmp/pmysql.sock' 

Warning: Using a password on the command line interface can be insecure.

mysql.columns_priv                                 OK

mysql.db                                           OK

mysql.event                                        OK

mysql.func                                         OK

mysql.general_log                                  OK

mysql.help_category                                OK

mysql.help_keyword                                 OK

mysql.help_relation                                OK

mysql.help_topic                                   OK

mysql.host                                         OK

mysql.ndb_binlog_index                             OK

mysql.plugin                                       OK

mysql.proc                                         OK

mysql.procs_priv                                   OK

mysql.proxies_priv                                 OK

mysql.servers                                      OK

mysql.slow_log                                     OK

mysql.tables_priv                                  OK

mysql.time_zone                                    OK

mysql.time_zone_leap_second                        OK

mysql.time_zone_name                               OK

mysql.time_zone_transition                         OK

mysql.time_zone_transition_type                    OK

mysql.user                                         OK

Running 'mysql_fix_privilege_tables'...

Warning: Using a password on the command line interface can be insecure.

Running 'mysqlcheck' with connection arguments: '--port=3307' '--socket=/tmp/pmysql.sock' 

Warning: Using a password on the command line interface can be insecure.

Running 'mysqlcheck' with connection arguments: '--port=3307' '--socket=/tmp/pmysql.sock' 

Warning: Using a password on the command line interface can be insecure.

OK

Check the error file for more information

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select version();

+-------------+

| version()   |

+-------------+

| 5.6.21-70.1 |

+-------------+

1 row in set (0.00 sec)

mysql> \s

--------------

/mysql/my_percona/mysql5.6.21/bin/mysql  Ver 14.14 Distrib 5.6.21-70.1, for Linux (x86_64) using  EditLine wrapper

Connection id: 11

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.6.21-70.1 Percona Server (GPL), Release 70.1, Revision 698

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db     characterset: latin1

Client characterset: utf8

Conn.  characterset: utf8

UNIX socket: /tmp/pmysql.sock

Uptime: 35 min 46 sec

Threads: 1  Questions: 828  Slow queries: 0  Opens: 383  Flush tables: 1  Open tables: 25  Queries per second avg: 0.385

--------------

[root@vm129-79 rc5.d]# ps -ef|grep mysqld

root      6454     1  0 01:58 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --datadir=/mysql/data/ --pid-file=/mysql/data//vm129-79.dlab..com.pid

mysql     6831  6454  0 01:58 ?        00:00:17 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/mysql/data/ --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mysql/data/mysql-error.log --pid-file=/mysql/data//vm129-79.dlab..com.pid

root      8894  7031  0 08:17 pts/1    00:00:00 /bin/sh ./bin/mysqld_safe --defaults-file=/mysql/my_percona/mysql5.6.21/my.cnf --basedir=/mysql/my_percona/mysql5.6.21 --datadir=/mysql/my_percona/mysql5.5.40/data/ --user=mysql

mysql     9144  8894  0 08:17 pts/1    00:00:02 /mysql/my_percona/mysql5.6.21/bin/mysqld --defaults-file=/mysql/my_percona/mysql5.6.21/my.cnf --basedir=/mysql/my_percona/mysql5.6.21 --datadir=/mysql/my_percona/mysql5.5.40/data/ --plugin-dir=/mysql/my_percona/mysql5.6.21/lib/mysql/plugin --user=mysql --log-error=/mysql/my_percona/mysql5.5.40/data//vm129-79.dlab.com.err --pid-file=/mysql/my_percona/mysql5.5.40/data//vm129-79.dlab.com.pid --socket=/tmp/pmysql.sock --port=3307

root      9275  7031  0 08:56 pts/1    00:00:00 grep mysqld