It takes long time since upgrade mysql last time,
so this time upgrade MySql from 5.6.14 to 5.6.16.

Changes in MySQL 5.6.16 (2014-01-31)
There is many InnoDB and Replication related bug fixes.

Download latest version of MySQL with wget.

 [root@HOME002 src]# wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.16-linux-glibc2.5-i686.tar.gz
--11:58:56--  http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.16-linux-glibc2.5-i686.tar.gz
dev.mysql.com をDNSに問いあわせています... 137.254.60.11
dev.mysql.com|137.254.60.11|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Found
場所: http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.16-linux-glibc2.5-i686.tar.gz [続く]
--11:58:57--  http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.16-linux-glibc2.5-i686.tar.gz
cdn.mysql.com をDNSに問いあわせています... 23.51.56.96
cdn.mysql.com|23.51.56.96|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 296884387 (283M) [application/x-tar-gz]
Saving to: `mysql-5.6.16-linux-glibc2.5-i686.tar.gz'
100%[=======================================================================>] 296,884,387 54.4K/s   in 96m 1s
13:35:00 (50.3 KB/s) - `mysql-5.6.16-linux-glibc2.5-i686.tar.gz' を保存しました [296884387/296884387]

[root@HOME002 src]#

Extract and move to self defined location.

[root@HOME002 src]# tar zxvf mysql-5.6.16-linux-glibc2.5-i686.tar.gz
mysql-5.6.16-linux-glibc2.5-i686/bin/innochecksum
mysql-5.6.16-linux-glibc2.5-i686/bin/myisam_ftdump
mysql-5.6.16-linux-glibc2.5-i686/bin/mysql_waitpid
mysql-5.6.16-linux-glibc2.5-i686/bin/mysqld_safe
mysql-5.6.16-linux-glibc2.5-i686/bin/perror
mysql-5.6.16-linux-glibc2.5-i686/bin/resolveip
mysql-5.6.16-linux-glibc2.5-i686/bin/msql2mysql

省略...............

mysql-5.6.16-linux-glibc2.5-i686/support-files/my-default.cnf
mysql-5.6.16-linux-glibc2.5-i686/support-files/mysql.server
mysql-5.6.16-linux-glibc2.5-i686/support-files/binary-configure
mysql-5.6.16-linux-glibc2.5-i686/support-files/mysqld_multi.server
mysql-5.6.16-linux-glibc2.5-i686/support-files/magic
mysql-5.6.16-linux-glibc2.5-i686/support-files/mysql-log-rotate
[root@HOME002 src]#

[root@HOME002 src]# ls -l
合計 583004
-rw-r--r--  1 root root 299497357  9月 11 11:14 mysql-5.6.14-linux-glibc2.5-i686.tar.gz
drwxr-xr-x 13 root root      4096  3月  1 13:39 mysql-5.6.16-linux-glibc2.5-i686
-rw-r--r--  1 root root 296884387  1月 15 19:59 mysql-5.6.16-linux-glibc2.5-i686.tar.gz
[root@HOME002 src]# mv  mysql-5.6.16-linux-glibc2.5-i686 /usr/local/
[root@HOME002 src]# cd /usr/local/
[root@HOME002 local]# ls -l
合計 124
drwxr-xr-x  2 root  root  4096  1月  8  2013 bin
drwxr-xr-x  2 root  root  4096  3月 30  2007 etc
drwxr-xr-x  2 root  root  4096  3月 30  2007 games
drwxr-xr-x  2 root  root  4096  1月  7  2013 include
drwxr-xr-x  3 root  root  4096  1月  7  2013 lib
drwxr-xr-x  2 root  root  4096  3月 30  2007 libexec
drwxr-xr-x  3 root  root  4096  1月  7  2013 man
lrwxrwxrwx  1 root  root    33 11月  9 10:12 mysql -> mysql-5.6.14-linux-glibc2.5-i686/
drwxr-xr-x 13 mysql mysql 4096  4月 28  2013 mysql-5.6.11-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096  6月 22  2013 mysql-5.6.12-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096  9月 14 07:48 mysql-5.6.13-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096 11月  9 10:06 mysql-5.6.14-linux-glibc2.5-i686
drwxr-xr-x 13 root  root  4096  3月  1 13:39 mysql-5.6.16-linux-glibc2.5-i686
drwxr-xr-x  2 root  root  4096  3月 30  2007 sbin
drwxr-xr-x  5 root  root  4096  1月  7  2013 share
drwxr-xr-x  2 root  root  4096  3月  1 13:40 src
[root@HOME002 local]#

Replace Symbolic link and data between old and new version.

[root@HOME002 local]# /etc/init.d/mysql stop
Shutting down MySQL...                                     [  OK  ]
[root@HOME002 local]# rm mysql
rm: remove シンボリックリンク `mysql'? y
[root@HOME002 local]#

[root@HOME002 local]# ln -s mysql-5.6.16-linux-glibc2.5-i686/ mysql
[root@HOME002 local]# ls -l
合計 124
drwxr-xr-x  2 root  root  4096  1月  8  2013 bin
drwxr-xr-x  2 root  root  4096  3月 30  2007 etc
drwxr-xr-x  2 root  root  4096  3月 30  2007 games
drwxr-xr-x  2 root  root  4096  1月  7  2013 include
drwxr-xr-x  3 root  root  4096  1月  7  2013 lib
drwxr-xr-x  2 root  root  4096  3月 30  2007 libexec
drwxr-xr-x  3 root  root  4096  1月  7  2013 man
lrwxrwxrwx  1 root  root    33  3月  1 13:43 mysql -> mysql-5.6.16-linux-glibc2.5-i686/
drwxr-xr-x 13 mysql mysql 4096  4月 28  2013 mysql-5.6.11-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096  6月 22  2013 mysql-5.6.12-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096  9月 14 07:48 mysql-5.6.13-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096 11月  9 10:06 mysql-5.6.14-linux-glibc2.5-i686
drwxr-xr-x 13 root  root  4096  3月  1 13:39 mysql-5.6.16-linux-glibc2.5-i686
drwxr-xr-x  2 root  root  4096  3月 30  2007 sbin
drwxr-xr-x  5 root  root  4096  1月  7  2013 share
drwxr-xr-x  2 root  root  4096  3月  1 13:40 src
[root@HOME002 local]#

[root@HOME002 mysql-5.6.14-linux-glibc2.5-i686]# cp -rp data/* /usr/local/mysql/data/
cp: `/usr/local/mysql/data/test/db.opt' を上書きしてもよろしいですか(yes/no)? y
[root@HOME002 mysql-5.6.14-linux-glibc2.5-i686]#

[root@HOME002 local]# chown -R mysql:mysql mysql-5.6.16-linux-glibc2.5-i686/
[root@HOME002 local]# chmod -R 755 mysql-5.6.16-linux-glibc2.5-i686/
[root@HOME002 local]#

[root@HOME002 local]# /etc/init.d/mysql start
Starting MySQL.......                                      [  OK  ]
[root@HOME002 local]#

[root@HOME002 local]#  /usr/local/mysql/bin/mysql --version
/usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.6.16, for linux-glibc2.5 (i686) using  EditLine wrapper
[root@HOME002 local]#

Update system DB in MySQL.


[root@HOME002 bin]# ./mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.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.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            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=3306' '--socket=/tmp/mysql.sock'
test.TABLE001                                      OK
test.timestamp_chk                                 OK
test.timestamp_chk2                                OK
OK
[root@HOME002 bin]#


[root@HOME002 bin]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.6.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.16-log |
+------------+
1 row in set (0.00 sec)

mysql>

Since I didn`t use this environment for while, so I need to fix replication.
Before fix replication confirm current master log position.


mysql> show master status;;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000035 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Start to fix replication at slave side.

mysql> use test
Database changed
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: home001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000034
          Read_Master_Log_Pos: 433
               Relay_Log_File: relay-bin.000004
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000034
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,performance_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 433
              Relay_Log_Space: 120
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 2516bf0a-943a-11e2-8417-00123fd6c9fa
             Master_Info_File: /usr/local/mysql-5.6.16-linux-glibc2.5-i686/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 140301 13:47:09
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.08 sec)

mysql> CHANGE MASTER TO MASTER_HOST = 'home001', master_port=3306, MASTER_LOG_FILE = 'mysql-bin.000035', MASTER_LOG_POS = 120;
Query OK, 0 rows affected (0.16 sec)

mysql> start slave;
Query OK, 0 rows affected (0.07 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: home001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000035
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000035
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,performance_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 450
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 2516bf0a-943a-11e2-8417-00123fd6c9fa
             Master_Info_File: /usr/local/mysql-5.6.16-linux-glibc2.5-i686/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

mysql>

Insert data in Master DB.

mysql> use test;
Database changed
mysql> insert into TABLE001(title,comment) values('after-upgrade','Master MYSQL is upgraded to 5.6.16.');
Query OK, 1 row affected (0.38 sec)

mysql> select * from TABLE001 order by id desc limit 0,1;
+----+---------------+-------------------------------------+
| id | title         | comment                             |
+----+---------------+-------------------------------------+
| 25 | after-upgrade | Master MYSQL is upgraded to 5.6.16. |
+----+---------------+-------------------------------------+
1 row in set (0.00 sec)

mysql>

Confirm data slave side and finish replication recovery.

mysql> select * from TABLE001 order by id desc limit 0,1;
+----+---------------+-------------------------------------+
| id | title         | comment                             |
+----+---------------+-------------------------------------+
| 24 | after-upgrade | Master MYSQL is upgraded to 5.6.14. |
+----+---------------+-------------------------------------+
1 row in set (0.08 sec)

------- Insert data at master DB.

mysql> select * from TABLE001 order by id desc limit 0,1;
+----+---------------+-------------------------------------+
| id | title         | comment                             |
+----+---------------+-------------------------------------+
| 25 | after-upgrade | Master MYSQL is upgraded to 5.6.16. |
+----+---------------+-------------------------------------+
1 row in set (0.00 sec)

mysql>


バージョン確認

[root@HOME002 src]# /usr/local/mysql/bin/mysql --version
/usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.6.13, for linux-glibc2.5 (i686) using  EditLine wrapper
[root@HOME002 src]#

ダウンロード

[root@HOME002 src]# wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.14-linux-glibc2.5-i686.tar.gz/from/http://cdn.mysql.com/
--09:30:09--  http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.14-linux-glibc2.5-i686.tar.gz/from/http://cdn.mysql.com/
dev.mysql.com をDNSに問いあわせています... 137.254.60.11
dev.mysql.com|137.254.60.11|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Found
場所: http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.14-linux-glibc2.5-i686.tar.gz [続く]
--09:30:10--  http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.14-linux-glibc2.5-i686.tar.gz
cdn.mysql.com をDNSに問いあわせています... 23.209.216.96
cdn.mysql.com|23.209.216.96|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 299497357 (286M) [application/x-tar-gz]
Saving to: `mysql-5.6.14-linux-glibc2.5-i686.tar.gz'

100%[=====================================================================================================>] 299,497,357 1.12M/s   in 4m 43s

09:34:53 (1.01 MB/s) - `mysql-5.6.14-linux-glibc2.5-i686.tar.gz' を保存しました [299497357/299497357]

[root@HOME002 src]#

■64bit版
———————————————————————————-
http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.14-linux-glibc2.5-x86_64.tar.gz/from/http://cdn.mysql.com/
http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.14-linux-glibc2.5-x86_64.tar.gz
———————————————————————————-

展開

[root@HOME002 src]# tar xzvf mysql-5.6.14-linux-glibc2.5-i686.tar.gz
mysql-5.6.14-linux-glibc2.5-i686/bin/innochecksum
mysql-5.6.14-linux-glibc2.5-i686/bin/myisam_ftdump
mysql-5.6.14-linux-glibc2.5-i686/bin/mysql_waitpid
mysql-5.6.14-linux-glibc2.5-i686/bin/mysqld_safe
mysql-5.6.14-linux-glibc2.5-i686/bin/perror
mysql-5.6.14-linux-glibc2.5-i686/bin/resolveip
mysql-5.6.14-linux-glibc2.5-i686/bin/msql2mysql
mysql-5.6.14-linux-glibc2.5-i686/bin/mysqlbinlog

省略...............

mysql-5.6.14-linux-glibc2.5-i686/support-files/mysqld_multi.server
mysql-5.6.14-linux-glibc2.5-i686/support-files/magic
mysql-5.6.14-linux-glibc2.5-i686/support-files/mysql-log-rotate
[root@HOME002 src]#

展開したファイルの移動

[root@HOME002 src]# ls -l
合計 585176
-rw-r--r--  1 root root 299105519  7月 12 20:17 mysql-5.6.13-linux-glibc2.5-i686.tar.gz
drwxr-xr-x 13 root root      4096 11月  9 10:06 mysql-5.6.14-linux-glibc2.5-i686
-rw-r--r--  1 root root 299497357  9月 11 11:14 mysql-5.6.14-linux-glibc2.5-i686.tar.gz
[root@HOME002 src]# mv mysql-5.6.14-linux-glibc2.5-i686 /usr/local/
[root@HOME002 src]#
[root@HOME002 local]# ls -l
合計 116
drwxr-xr-x  2 root  root  4096  1月  8  2013 bin
drwxr-xr-x  2 root  root  4096  3月 30  2007 etc
drwxr-xr-x  2 root  root  4096  3月 30  2007 games
drwxr-xr-x  2 root  root  4096  1月  7  2013 include
drwxr-xr-x  3 root  root  4096  1月  7  2013 lib
drwxr-xr-x  2 root  root  4096  3月 30  2007 libexec
drwxr-xr-x  3 root  root  4096  1月  7  2013 man
lrwxrwxrwx  1 root  root    33  9月 14 07:57 mysql -> mysql-5.6.13-linux-glibc2.5-i686/
drwxr-xr-x 13 mysql mysql 4096  4月 28  2013 mysql-5.6.11-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096  6月 22 10:09 mysql-5.6.12-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096  9月 14 07:48 mysql-5.6.13-linux-glibc2.5-i686
drwxr-xr-x 13 root  root  4096 11月  9 10:06 mysql-5.6.14-linux-glibc2.5-i686
drwxr-xr-x  2 root  root  4096  3月 30  2007 sbin
drwxr-xr-x  5 root  root  4096  1月  7  2013 share
drwxr-xr-x  2 root  root  4096 11月  9 10:06 src
[root@HOME002 local]#

データコピー前にDBを停止とシンボリックリンク削除
データコピー後に再開

[root@HOME002 local]# /etc/init.d/mysql stop
Shutting down MySQL...                                     [  OK  ]
[root@HOME002 local]# rm mysql
rm: remove シンボリックリンク `mysql'? y
[root@HOME002 local]#

[root@HOME002 mysql-5.6.13-linux-glibc2.5-i686]# cp -rp data/* /usr/local/mysql-5.6.14-linux-glibc2.5-i686/data/
cp: `/usr/local/mysql-5.6.14-linux-glibc2.5-i686/data/test/db.opt' を上書きしてもよろしいですか(yes/no)? y
[root@HOME002 mysql-5.6.13-linux-glibc2.5-i686]#

[root@HOME002 local]# chown -R mysql:mysql mysql-5.6.14-linux-glibc2.5-i686/
[root@HOME002 local]# chmod -R 755 mysql-5.6.14-linux-glibc2.5-i686/
[root@HOME002 local]# ln -s mysql-5.6.14-linux-glibc2.5-i686/ mysql
[root@HOME002 local]#

[root@HOME002 local]# ls -l
合計 116
drwxr-xr-x  2 root  root  4096  1月  8  2013 bin
drwxr-xr-x  2 root  root  4096  3月 30  2007 etc
drwxr-xr-x  2 root  root  4096  3月 30  2007 games
drwxr-xr-x  2 root  root  4096  1月  7  2013 include
drwxr-xr-x  3 root  root  4096  1月  7  2013 lib
drwxr-xr-x  2 root  root  4096  3月 30  2007 libexec
drwxr-xr-x  3 root  root  4096  1月  7  2013 man
lrwxrwxrwx  1 root  root    33 11月  9 10:12 mysql -> mysql-5.6.14-linux-glibc2.5-i686/
drwxr-xr-x 13 mysql mysql 4096  4月 28  2013 mysql-5.6.11-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096  6月 22 10:09 mysql-5.6.12-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096  9月 14 07:48 mysql-5.6.13-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096 11月  9 10:06 mysql-5.6.14-linux-glibc2.5-i686
drwxr-xr-x  2 root  root  4096  3月 30  2007 sbin
drwxr-xr-x  5 root  root  4096  1月  7  2013 share
drwxr-xr-x  2 root  root  4096 11月  9 10:06 src
[root@HOME002 local]#


[root@HOME002 local]# /etc/init.d/mysql start
Starting MySQL......                                       [  OK  ]
[root@HOME002 local]#

バージョン確認

[root@HOME002 local]#  /usr/local/mysql/bin/mysql --version
/usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.6.14, for linux-glibc2.5 (i686) using  EditLine wrapper
[root@HOME002 local]#

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.14-log |
+------------+
1 row in set (0.00 sec)

mysql> use test
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| TABLE001       |
| timestamp_chk  |
| timestamp_chk2 |
+----------------+
3 rows in set (0.00 sec)

mysql>

■ 暫くDBを立ち上げていなかったので、ログが削除されていてレプリケーションがエラーになっていたのでレプリケーション修正。


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: home001
                  Master_User: slave_user
省略

Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
   




mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000034 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>



mysql> stop slave;
Query OK, 0 rows affected (0.08 sec)

mysql> CHANGE MASTER TO MASTER_HOST = 'home001', master_port=3306, MASTER_LOG_FILE = 'mysql-bin.000034', MASTER_LOG_POS = 120;
Query OK, 0 rows affected (0.17 sec)

mysql> start slave;
Query OK, 0 rows affected (0.06 sec)

mysql>


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: home001
                  Master_User: slave_user
省略

Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:


mysql>  select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME001.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql> use test;
Database changed
mysql> insert into TABLE001(title,comment) values('after-upgrade','Master MYSQL is upgraded to 5.6.14.');
Query OK, 1 row affected (0.38 sec)


mysql> select * from TABLE001 order by id desc limit 0,1;
+----+---------------+-------------------------------------+
| id | title         | comment                             |
+----+---------------+-------------------------------------+
| 24 | after-upgrade | Master MYSQL is upgraded to 5.6.14. |
+----+---------------+-------------------------------------+
1 row in set (0.00 sec)

mysql>




mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME002.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from TABLE001 order by id desc limit 0,1;
+----+---------------+-------------------------------------+
| id | title         | comment                             |
+----+---------------+-------------------------------------+
| 24 | after-upgrade | Master MYSQL is upgraded to 5.6.14. |
+----+---------------+-------------------------------------+
1 row in set (0.00 sec)

mysql>

MYSQL5.6.11
MYSQL5.6,10が2月にリリースされてから2ヶ月ですが、
機能追加、BugFixが含まれた5.6.11がリリースされました。

リリースノートは以下のページで確認出来ます。
http://dev.mysql.com/doc/relnotes/mysql/5.6/en/

Changes in MySQL 5.6.12 (Not yet released)
Changes in MySQL 5.6.11 (2013-04-18)
Changes in MySQL 5.6.10 (2013-02-05, General Availability)

5.6.11で追加・変更された機能
Changes in MySQL 5.6.11 (2013-04-18)
http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-11.html
There is many changes in Replication,Bugs Fixed and Performance.

先ずは既存のLinuxバージョンと32bit or 64bitか確認。
[root@HOME002 ~]# uname -m -i
i686 i386
[root@HOME002 ~]#

32bitなので以下のcompile済みtarをダウンロード
mysql-5.6.11-linux-glibc2.5-i686.tar.gz

[root@HOME002 src]# wget http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.11-linux-glibc2.5-i686.tar.gz
--15:52:30--  http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.11-linux-glibc2.5-i686.tar.gz
cdn.mysql.com をDNSに問いあわせています... 117.104.139.58, 117.104.139.49
cdn.mysql.com|117.104.139.58|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 296649804 (283M) [application/x-tar-gz]
Saving to: `mysql-5.6.11-linux-glibc2.5-i686.tar.gz'

344K/100%[======================================================>] 296,649,804  282K/s   in 14m 36s

16:07:07 (331 KB/s) - `mysql-5.6.11-linux-glibc2.5-i686.tar.gz' を保存しました [296649804/296649804]

[root@HOME002 src]#


[root@HOME002 src]# ls -lh
合計 566M
-rw-r--r-- 1 root root 282M  2月 16 15:03 mysql-5.6.10-linux-glibc2.5-i686.tar.gz
-rw-r--r-- 1 root root 283M  4月  8 21:54 mysql-5.6.11-linux-glibc2.5-i686.tar.gz
[root@HOME002 src]#

展開して/usr/localに移動

[root@HOME002 src]# tar zxvf mysql-5.6.11-linux-glibc2.5-i686.tar.gz
mysql-5.6.11-linux-glibc2.5-i686/COPYING
mysql-5.6.11-linux-glibc2.5-i686/bin/mysqlaccess.conf
mysql-5.6.11-linux-glibc2.5-i686/bin/resolveip
mysql-5.6.11-linux-glibc2.5-i686/bin/mysqld_multi
mysql-5.6.11-linux-glibc2.5-i686/bin/mysqlshow
mysql-5.6.11-linux-glibc2.5-i686/bin/mysqlaccess
mysql-5.6.11-linux-glibc2.5-i686/bin/resolve_stack_dump
mysql-5.6.11-linux-glibc2.5-i686/bin/mysqladmin
mysql-5.6.11-linux-glibc2.5-i686/bin/mysqld
 [省略...]
mysql-5.6.11-linux-glibc2.5-i686/mysql-test/lib/My/SysInfo.pm
mysql-5.6.11-linux-glibc2.5-i686/mysql-test/lib/My/Options.pm
mysql-5.6.11-linux-glibc2.5-i686/mysql-test/lib/My/SafeProcess.pm
mysql-5.6.11-linux-glibc2.5-i686/mysql-test/lib/My/Memcache.pm
mysql-5.6.11-linux-glibc2.5-i686/mysql-test/lib/My/File/Path.pm
mysql-5.6.11-linux-glibc2.5-i686/mysql-test/lib/My/Exec.pm
mysql-5.6.11-linux-glibc2.5-i686/mysql-test/lib/My/ConfigFactory.pm
mysql-5.6.11-linux-glibc2.5-i686/mysql-test/lib/My/CoreDump.pm
[root@HOME002 src]#


[root@HOME002 src]# mv mysql-5.6.11-linux-glibc2.5-i686 /usr/local/
[root@HOME002 src]#

[root@HOME002 local]# pwd
/usr/local
[root@HOME002 local]# ls -l
合計 108
drwxr-xr-x  2 root  root  4096  1月  8 14:59 bin
drwxr-xr-x  2 root  root  4096  3月 30  2007 etc
drwxr-xr-x  2 root  root  4096  3月 30  2007 games
drwxr-xr-x  2 root  root  4096  1月  7 19:58 include
drwxr-xr-x  3 root  root  4096  1月  7 19:58 lib
drwxr-xr-x  2 root  root  4096  3月 30  2007 libexec
drwxr-xr-x  3 root  root  4096  1月  7 19:58 man
lrwxrwxrwx  1 root  root    33  2月 16 15:56 mysql -> mysql-5.6.10-linux-glibc2.5-i686/
drwxrwxr-x 13 mysql mysql 4096  1月 25 15:14 mysql-5.5.29-linux2.6-i686
drwxrwxr-x 13 mysql mysql 4096  2月 16 15:51 mysql-5.6.10-linux-glibc2.5-i686
drwxr-xr-x 13 root  root  4096  4月 28 16:11 mysql-5.6.11-linux-glibc2.5-i686
drwxr-xr-x  2 root  root  4096  3月 30  2007 sbin
drwxr-xr-x  5 root  root  4096  1月  7 19:58 share
drwxr-xr-x  2 root  root  4096  4月 28 16:13 src
[root@HOME002 local]#


準備が出来たので既存MYSQLを停止して、
データコピーとシンボリックリンク入れ替えを行います。

[root@HOME002 ~]# /etc/init.d/mysql stop
Shutting down MySQL...                                     [  OK  ]
[root@HOME002 ~]# 

[root@HOME002 local]# rm mysql
rm: remove シンボリックリンク `mysql'? y
[root@HOME002 local]# ln -s mysql-5.6.11-linux-glibc2.5-i686 mysql
[root@HOME002 local]# ls -l
合計 108
drwxr-xr-x  2 root  root  4096  1月  8 14:59 bin
drwxr-xr-x  2 root  root  4096  3月 30  2007 etc
drwxr-xr-x  2 root  root  4096  3月 30  2007 games
drwxr-xr-x  2 root  root  4096  1月  7 19:58 include
drwxr-xr-x  3 root  root  4096  1月  7 19:58 lib
drwxr-xr-x  2 root  root  4096  3月 30  2007 libexec
drwxr-xr-x  3 root  root  4096  1月  7 19:58 man
lrwxrwxrwx  1 root  root    32  4月 28 16:15 mysql -> mysql-5.6.11-linux-glibc2.5-i686
drwxrwxr-x 13 mysql mysql 4096  1月 25 15:14 mysql-5.5.29-linux2.6-i686
drwxrwxr-x 13 mysql mysql 4096  2月 16 15:51 mysql-5.6.10-linux-glibc2.5-i686
drwxr-xr-x 13 root  root  4096  4月 28 16:11 mysql-5.6.11-linux-glibc2.5-i686
drwxr-xr-x  2 root  root  4096  3月 30  2007 sbin
drwxr-xr-x  5 root  root  4096  1月  7 19:58 share
drwxr-xr-x  2 root  root  4096  4月 28 16:13 src
[root@HOME002 local]#

[root@HOME002 local]# cp -rf mysql-5.6.10-linux-glibc2.5-i686/data/* /usr/local/mysql-5.6.11-linux-glibc2.5-i686/data/
[root@HOME002 local]#

[root@HOME002 local]# chown -R mysql:mysql mysql-5.6.11-linux-glibc2.5-i686/
[root@HOME002 local]# chmod -R 755 mysql-5.6.11-linux-glibc2.5-i686/

[root@HOME002 bin]# pwd
/usr/local/mysql/bin
[root@HOME002 bin]# /etc/init.d/mysql start
Starting MySQL......                                       [  OK  ]
[root@HOME002 bin]# 


MYSQLの起動までは問題無く完了したので、Upgradeスクリプトヲ実行してシステムDBをアップグレード。
コマンドラインでパスワードを利用するのはセキュリティ上良くないとのメッセージあり。※ 要調整

[root@HOME002 bin]# ./mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.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.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            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=3306' '--socket=/tmp/mysql.sock'   OK
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Warning: Using a password on the command line interface can be insecure.test.TABLE001      OK
[root@HOME002 bin]#

バージョン確認
version

レプリケーション確認(マスターでデータを入れてスレーブ側で確認)
repication

レプリケーション確認(Slave Status)
slavestatus

There are some error and warnings in error log even MYSQL is working fine,
so I need to check it later.


先日、スレーブをアップグレードしてみて特に問題無さそうでしたので、
マスターDBをアップグレードしてみました。

アップグレード前の状況
既存では5.5.29をマスターDBで利用している。


[root@HOME001 local]# ls -l
合計 48
drwxr-xr-x.  2 root  root  4096  1月 31 14:18 2013 bin
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 etc
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 games
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 include
drwxr-xr-x.  3 root  root  4096  1月 31 14:12 2013 lib
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 libexec
drwxr-xr-x.  3 root  root  4096 12月 30 16:07 2012 man
lrwxrwxrwx.  1 mysql mysql   27 12月 22 14:42 2012 mysql -> mysql-5.5.29-linux2.6-i686/
drwxrwxr-x. 13 mysql mysql 4096  1月 24 15:37 2013 mysql-5.5.29-linux2.6-i686
drwxr-xr-x.  4 mysql mysql 4096  1月 18 05:26 2013 percona-xtrabackup-2.0.5
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 sbin
drwxr-xr-x.  7 root  root  4096  1月  7 14:59 2013 share
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 src
drwxrwxrwt.  2 root  root    40  3月 24 12:22 2013 tmp
lrwxrwxrwx.  1 mysql mysql   24  1月 24 14:09 2013 xtrabackup -> percona-xtrabackup-2.0.5
[root@HOME001 local]#

MYSQL5.6をダウンロードして展開。


[root@HOME001 local]# ls -l
合計 52
drwxr-xr-x.  2 root  root  4096  1月 31 14:18 2013 bin
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 etc
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 games
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 include
drwxr-xr-x.  3 root  root  4096  1月 31 14:12 2013 lib
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 libexec
drwxr-xr-x.  3 root  root  4096 12月 30 16:07 2012 man
lrwxrwxrwx.  1 mysql mysql   27 12月 22 14:42 2012 mysql -> mysql-5.5.29-linux2.6-i686/
drwxrwxr-x. 13 mysql mysql 4096  1月 24 15:37 2013 mysql-5.5.29-linux2.6-i686
drwxr-xr-x. 13 root  root  4096  3月 24 13:09 2013 mysql-5.6.10-linux-glibc2.5-i686
drwxr-xr-x.  4 mysql mysql 4096  1月 18 05:26 2013 percona-xtrabackup-2.0.5
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 sbin
drwxr-xr-x.  7 root  root  4096  1月  7 14:59 2013 share
drwxr-xr-x.  2 root  root  4096  3月 24 13:10 2013 src
drwxrwxrwt.  2 root  root    40  3月 24 12:22 2013 tmp
lrwxrwxrwx.  1 mysql mysql   24  1月 24 14:09 2013 xtrabackup -> percona-xtrabackup-2.0.5
[root@HOME001 local]#

既存DBを停止してアップグレード作業開始

1) STOP MYSQL
2) Remove Symbolic Link
3) Copy Data from Old MySQL to New SQL
4) Set Authentication
6) Recreate Symbolic Link with MySQL5.6


[root@HOME001 local]# /etc/init.d/mysql stop
Shutting down MySQL...                                    [  OK  ]
[root@HOME001 local]# 

[root@HOME001 local]# rm mysql
rm: remove シンボリックリンク `mysql'? y
[root@HOME001 local]#

[root@HOME001 local]# cp -rp /usr/local/mysql-5.5.29-linux2.6-i686/data/* /usr/local/mysql-5.6.10-linux-glibc2.5-i686/data/               
[root@HOME001 local]#

[root@HOME001 local]# chown -R mysql:mysql mysql-5.6.10-linux-glibc2.5-i686/
[root@HOME001 local]# chmod -R 755 mysql-5.6.10-linux-glibc2.5-i686/
[root@HOME001 local]#


[root@HOME001 local]# ln -s mysql-5.6.10-linux-glibc2.5-i686/ mysql
[root@HOME001 local]# ls -l
合計 52
drwxr-xr-x.  2 root  root  4096  1月 31 14:18 2013 bin
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 etc
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 games
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 include
drwxr-xr-x.  3 root  root  4096  1月 31 14:12 2013 lib
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 libexec
drwxr-xr-x.  3 root  root  4096 12月 30 16:07 2012 man
lrwxrwxrwx.  1 root  root    33  3月 24 13:18 2013 mysql -> mysql-5.6.10-linux-glibc2.5-i686/
drwxrwxr-x. 13 mysql mysql 4096  1月 24 15:37 2013 mysql-5.5.29-linux2.6-i686
drwxr-xr-x. 13 mysql mysql 4096  3月 24 13:09 2013 mysql-5.6.10-linux-glibc2.5-i686
drwxr-xr-x.  4 mysql mysql 4096  1月 18 05:26 2013 percona-xtrabackup-2.0.5
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 sbin
drwxr-xr-x.  7 root  root  4096  1月  7 14:59 2013 share
drwxr-xr-x.  2 root  root  4096  3月 24 13:10 2013 src
drwxrwxrwt.  2 root  root    40  3月 24 12:22 2013 tmp
lrwxrwxrwx.  1 mysql mysql   24  1月 24 14:09 2013 xtrabackup -> percona-xtrabackup-2.0.5
[root@HOME001 local]#

準備が出来たので、MYSQLを起動してシステムDBをアップグレードします。
この段階では、旧バージョンで利用していたmy.confは編集していません。


[root@HOME001 bin]# /etc/init.d/mysql start
Starting MySQL..                                           [  OK  ]
[root@HOME001 bin]# ./mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.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
test.TABLE001                                      OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
OK
[root@HOME001 bin]#

アップグレードは終わりましたが、念の為再起動してログを確認。
以下のオプションだけ後日確認。

explicit_defaults_for_timestamp
innodb_additional_mem_pool_size
innodb_use_sys_malloc

[root@HOME001 bin]# /etc/init.d/mysql restart
Shutting down MySQL...                                     [  OK  ]
Starting MySQL..                                           [  OK  ]
[root@HOME001 bin]#




130324 13:21:15 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2013-03-24 13:21:15 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-03-24 13:21:15 5061 [Note] Plugin 'FEDERATED' is disabled.
2013-03-24 13:21:15 b789e8e0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-03-24 13:21:15 5061 [Note] InnoDB: The InnoDB memory heap is disabled
2013-03-24 13:21:15 5061 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2013-03-24 13:21:15 5061 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-03-24 13:21:15 5061 [Note] InnoDB: CPU does not support crc32 instructions
2013-03-24 13:21:15 5061 [Note] InnoDB: Using Linux native AIO
2013-03-24 13:21:15 5061 [Note] InnoDB: Initializing buffer pool, size = 32.0M
2013-03-24 13:21:15 5061 [Note] InnoDB: Completed initialization of buffer pool
2013-03-24 13:21:15 5061 [Note] InnoDB: Highest supported file format is Barracuda.
2013-03-24 13:21:16 5061 [Note] InnoDB: 128 rollback segment(s) are active.
2013-03-24 13:21:16 5061 [Note] InnoDB: Waiting for purge to start
2013-03-24 13:21:16 5061 [Note] InnoDB: 1.2.10 started; log sequence number 1733578
2013-03-24 13:21:16 5061 [Note] Semi-sync replication initialized for transactions.
2013-03-24 13:21:16 5061 [Note] Semi-sync replication enabled on the master.
2013-03-24 13:21:16 5061 [Note] Server hostname (bind-address): '*'; port: 3306
2013-03-24 13:21:16 5061 [Note] IPv6 is available.
2013-03-24 13:21:16 5061 [Note]   - '::' resolves to '::';
2013-03-24 13:21:16 5061 [Note] Server socket created on IP: '::'.
2013-03-24 13:21:16 5061 [Note] Event Scheduler: Loaded 0 events
2013-03-24 13:21:16 5061 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.10-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
[root@HOME001 data]#


バージョンアップ結果確認


[root@HOME001 data]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.10-log |
+------------+
1 row in set (0.00 sec)

mysql>

MYSQL5610-master

その他レプリケーションの状態など。

マスターアップグレード前にSlaveを念の為、停止しておきました。
マスターアップグレード中なので書き込みも発生しないし、
スレーブは参照だけ出来ていれば良いかと。
但し、コマースサイトなどで書き込み処理も停止出来ない場合は、
他のスレーブをマスターにアップグレードする方法でも良いかと。
但し、スペックはますてーとして十分なシステムを選択する必要あり。


mysql> stop slave;
Query OK, 0 rows affected (0.08 sec)
mysql>

アップグレード後にスレーブ再開して問題無い事を確認済み。

mysql> start slave;
Query OK, 0 rows affected (0.21 sec)

mysql>


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: home001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000020
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-bin.000007
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000020
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,performance_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 609
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 2516bf0a-943a-11e2-8417-00123fd6c9fa
             Master_Info_File: /usr/local/mysql-5.6.10-linux-glibc2.5-i686/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

mysql>
 

マスターでデータをINSERTして確認


mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME001.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql>


mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000020 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>


mysql> insert into TABLE001(title,comment) values('after upgrade master','Upgrade to  5.6.10-log and Check Replication');
Query OK, 1 row affected (0.45 sec)

mysql>

スレーブ側にてデータ確認


mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME002.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql>

mysql> select * from TABLE001;
+----+----------------------+----------------------------------------------+
| id | title                | comment                                      |
+----+----------------------+----------------------------------------------+
|  1 | Before upgrade       | MYSQL 5.6 - 0                                |
|  2 | Before upgrade       | MYSQL 5.6 - 1                                |
|  3 | Before upgrade       | MYSQL 5.6 - 2                                |
|  4 | After Slave upgrade  | MYSQL 5.6 - 0                                |
|  5 | After Slave upgrade  | MYSQL 5.6 - 1                                |
|  6 | reset replication    | Execute Change Master                        |
|  7 | reset replication    | Execute Change Master2                       |
|  8 | After Change Master  | Log is deleted by purge in my.conf           |
|  9 | after upgrade master | Upgrade to  5.6.10-log and Check Replication |
+----+----------------------+----------------------------------------------+
9 rows in set (0.23 sec)

mysql>

Slaveも再起動してみてログを確認


[root@HOME002 data]# /etc/init.d/mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL...                                          [  OK  ]
[root@HOME002 data]#


[root@HOME002 data]# cat HOME002.localdomain.err
130324 14:22:23 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2013-03-24 14:22:24 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-03-24 14:22:24 13058 [Note] Plugin 'FEDERATED' is disabled.
2013-03-24 14:22:24 b7f596d0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-03-24 14:22:24 13058 [Note] InnoDB: The InnoDB memory heap is disabled
2013-03-24 14:22:24 13058 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2013-03-24 14:22:24 13058 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-03-24 14:22:24 13058 [Note] InnoDB: CPU does not support crc32 instructions
2013-03-24 14:22:24 13058 [Note] InnoDB: Using Linux native AIO
2013-03-24 14:22:24 13058 [Note] InnoDB: Initializing buffer pool, size = 32.0M
2013-03-24 14:22:24 13058 [Note] InnoDB: Completed initialization of buffer pool
2013-03-24 14:22:24 13058 [Note] InnoDB: Highest supported file format is Barracuda.
2013-03-24 14:22:25 13058 [Note] InnoDB: 128 rollback segment(s) are active.
2013-03-24 14:22:25 13058 [Note] InnoDB: Waiting for purge to start
2013-03-24 14:22:25 13058 [Note] InnoDB: 1.2.10 started; log sequence number 1734793
2013-03-24 14:22:26 13058 [Note] Server hostname (bind-address): '*'; port: 3306
2013-03-24 14:22:26 13058 [Note] IPv6 is available.
2013-03-24 14:22:26 13058 [Note]   - '::' resolves to '::';
2013-03-24 14:22:26 13058 [Note] Server socket created on IP: '::'.
2013-03-24 14:22:26 13058 [Note] Slave I/O thread: Start semi-sync replication to master 'slave_user@home001:3306' in log 'mysql-bin.000020' at position 449
2013-03-24 14:22:26 13058 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
2013-03-24 14:22:26 13058 [Note] Slave I/O thread: connected to master 'slave_user@home001:3306',replication started in log 'mysql-bin.000020' at position 449
2013-03-24 14:22:26 13058 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2013-03-24 14:22:27 13058 [Note] Event Scheduler: Loaded 0 events
2013-03-24 14:22:27 13058 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.10-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2013-03-24 14:22:27 13058 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000020' at position 449, relay log '/usr/local/mysql/data/relay-bin.000007' position: 612
[root@HOME002 data]#

参考サイト
2.11.1.1. Upgrading from MySQL 5.5 to 5.6


9月末にMYSQL5.5.28がリリースされたので、アップグレードしておく。
最近、アップグレードの頻度が少し高いような気がする。
プライベートのサイトは良いが、会社のサービスで運用する場合に
新機能を使わないのであれば、ある程度落ち着いたバージョンが良いかと。

D.1.2. Changes in MySQL 5.5.28 (2012-09-28)

Bugs Fixed

InnoDB: Certain information_schema tables originally introduced in MySQL 5.6
are now also available in MySQL 5.5 and MySQL 5.1 (Bug #13113026)

InnoDB: When a SELECT … FOR UPDATE, UPDATE, or other SQL statement
scanned rows in an InnoDB table using a < or <= operator in a WHERE clause, the next row after the affected range could also be locked. (Bug #11765218) Partitioning: When used with a table having multiple columns in its primary key, but partitioned by KEY using a column that was not part of the primary key as the partitioning column, a query using an aggregate function and DISTINCT such as SELECT SUM(DISTINCT pk_column_1) FROM table WHERE pk_column_2 = constant was not handled correctly. (Bug #14495351) Partitioning: For tables using PARTITION BY HASH or PARTITION BY KEY, when the partition pruning mechanism encountered a multi-range list or inequality using a column from the partitioning key, it continued with the next partitioning column and tried to use it for pruning, even if the previous column could not be used. (Bug #14342883) Partitioning: The buffer for the row currently read from each partition used for sorted reads was allocated on open and freed only when the partitioning handler was closed or destroyed. For SELECT statements on tables with many partitions and large rows, this could cause the server to use excessive amounts of memory.(Bug #13025132) Replication: On 64-bit Windows platforms, values greater than 4G for the max_binlog_cache_size and max_binlog_stmt_cache_size system variables were truncated to 4G.(Bug #13961678) Replication: In master-master replication with --log-slave-updates enabled, setting a user variable and then performing inserts using this variable caused the Exec_master_log_position column in the output of SHOW SLAVE STATUS not to be updated. (Bug #13596613) mysqlhotcopy failed for databases containing views. (Bug #62472, Bug #13006947, Bug #12992993) Adding a LIMIT clause to a query containing GROUP BY and ORDER BY could cause the optimizer to choose an incorrect index for processing the query, and return more rows than required. (Bug #54599, Bug #11762052) mysqlbinlog did not accept input on the standard input when the standard input was a pipe. (Bug #49336, Bug #11757312) その他、数件のBugFixあり

ファイルをダウンロード

aws$sudo mv mysql-5.5.28-linux2.6-x86_64.tar.gz /usr/local/src/
aws$

aws$ pwd
/usr/local/src
aws$ ls -lh
total 560M
-rw-r--r-- 1 root     root     6.9M Jan 30  2012 httpd-2.2.22.tar.gz
-rw-r--r-- 1 root     root     5.9M Aug 20 13:22 httpd-2.4.3.tar.gz
-rw-rw-r-- 1 ec2-user ec2-user 178M Jul  6 23:07 mysql-5.5.25a-linux2.6-x86_64.tar.gz
-rw-rw-r-- 1 ec2-user ec2-user 178M Aug 26 11:37 mysql-5.5.27-linux2.6-x86_64.tar.gz
-rw-rw-r-- 1 ec2-user ec2-user 179M Oct 13 13:05 mysql-5.5.28-linux2.6-x86_64.tar.gz
-rw-r--r-- 1 root     root      14M Aug 16 22:50 php-5.4.6.tar.gz
aws$

バックアップ

aws$ mysqldump --all-databases --single-transaction --flush-logs > /home/ec2-user/mysql_dump20121013.sql -p
Enter password:
aws$

Tarファイルの展開

aws$ tar zxvf mysql-5.5.28-linux2.6-x86_64.tar.gz
mysql-5.5.28-linux2.6-x86_64/docs/mysql.info
mysql-5.5.28-linux2.6-x86_64/docs/INFO_SRC
mysql-5.5.28-linux2.6-x86_64/docs/INFO_BIN
mysql-5.5.28-linux2.6-x86_64/docs/ChangeLog
mysql-5.5.28-linux2.6-x86_64/COPYING
mysql-5.5.28-linux2.6-x86_64/README
mysql-5.5.28-linux2.6-x86_64/INSTALL-BINARY
mysql-5.5.28-linux2.6-x86_64/bin/myisam_ftdump
mysql-5.5.28-linux2.6-x86_64/bin/myisamchk
mysql-5.5.28-linux2.6-x86_64/bin/myisamlog

[省略]

mysql-5.5.28-linux2.6-x86_64/man/man1/my_print_defaults.1
mysql-5.5.28-linux2.6-x86_64/man/man1/myisamlog.1
mysql-5.5.28-linux2.6-x86_64/man/man1/mysql_tzinfo_to_sql.1
mysql-5.5.28-linux2.6-x86_64/man/man1/mysqlslap.1
mysql-5.5.28-linux2.6-x86_64/man/man1/myisampack.1
mysql-5.5.28-linux2.6-x86_64/man/man8/mysqld.8

aws$ ls -lh
total 560M
-rw-r--r--  1 root     root     6.9M Jan 30  2012 httpd-2.2.22.tar.gz
-rw-r--r--  1 root     root     5.9M Aug 20 13:22 httpd-2.4.3.tar.gz
-rw-rw-r--  1 ec2-user ec2-user 178M Jul  6 23:07 mysql-5.5.25a-linux2.6-x86_64.tar.gz
-rw-rw-r--  1 ec2-user ec2-user 178M Aug 26 11:37 mysql-5.5.27-linux2.6-x86_64.tar.gz
drwxr-xr-x 13 root     root     4.0K Oct 13 13:46 mysql-5.5.28-linux2.6-x86_64
-rw-rw-r--  1 ec2-user ec2-user 179M Oct 13 13:05 mysql-5.5.28-linux2.6-x86_64.tar.gz
-rw-r--r--  1 root     root      14M Aug 16 22:50 php-5.4.6.tar.gz
aws$

MYSQLのバージョン切り替え作業

1)展開したファイルの移動
2)MYSQL停止
3)データフォルダーコピー
4)シンボリックリンク張り直し
5)権限設定
6)MYSQLサービス開始

aws$ mv mysql-5.5.28-linux2.6-x86_64 /usr/local/

aws$ /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!
aws$ cp -rp /usr/local/mysql/data/ /usr/local/mysql-5.5.28-linux2.6-x86_64
aws$ rm mysql
rm: remove symbolic link `mysql'? y
aws$ ln -s /usr/local/mysql-5.5.28-linux2.6-x86_64 mysql
aws$

MYSQL SYMBOLIC LINK

aws$ chown -R mysql:mysql mysql-5.5.28-linux2.6-x86_64
aws$ ls -lh
total 60K
lrwxrwxrwx  1 root   root     23 Sep  8 22:47 apache2 -> /usr/local/httpd-2_2_22
drwxr-xr-x  2 root   root   4.0K Sep  9 01:06 bin
drwxr-xr-x  2 root   root   4.0K Jan  6  2012 etc
drwxr-xr-x  2 root   root   4.0K Jan  6  2012 games
drwxr-xr-x 14 apache apache 4.0K Sep  8 22:44 httpd-2_2_22
drwxr-xr-x  3 root   root   4.0K May 27 09:00 include
drwxr-xr-x  2 root   root   4.0K Sep  9 01:06 lib
drwxr-xr-x  2 root   root   4.0K Oct 13 12:52 lib64
drwxr-xr-x  2 root   root   4.0K Jan  6  2012 libexec
lrwxrwxrwx  1 root   root     39 Oct 13 13:55 mysql -> /usr/local/mysql-5.5.28-linux2.6-x86_64
drwxrwxr-x 13 mysql  mysql  4.0K Jul  7 04:44 mysql-5.5.25a-linux2.6-x86_64
drwxr-xr-x 14 mysql  mysql  4.0K Aug 26 12:09 mysql-5.5.27-linux2.6-x86_64
drwxr-xr-x 13 mysql  mysql  4.0K Oct 13 13:46 mysql-5.5.28-linux2.6-x86_64
drwxr-xr-x  7 root   root   4.0K Sep  9 00:54 php-5.4.6
drwxr-xr-x  2 root   root   4.0K Jan  6  2012 sbin
drwxr-xr-x  5 root   root   4.0K Oct 13 12:52 share
drwxr-xr-x  2 root   root   4.0K Oct 13 13:48 src
aws$

aws$ /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
aws$

MYSQL起動後にシステムテーブルをアップグレード

aws$ /usr/local/mysql/bin/mysql_upgrade -p
Enter password:
Looking for 'mysql' as: /usr/local/mysql/bin/mysql
Looking for 'mysqlcheck' as: /usr/local/mysql/bin/mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/usr/local/mysql/data/mysql.sock'
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/usr/local/mysql/data/mysql.sock'
WP01.wp01_commentmeta                              OK
WP01.wp01_comments                                 OK
WP01.wp01_geo_mashup_administrative_names          OK
WP01.wp01_geo_mashup_location_relationships        OK
WP01.wp01_geo_mashup_locations                     OK
WP01.wp01_links                                    OK
WP01.wp01_mappress_maps                            OK
WP01.wp01_mappress_posts                           OK
WP01.wp01_options                                  OK
WP01.wp01_postmeta                                 OK
WP01.wp01_posts                                    OK
WP01.wp01_posts_0929                               OK
WP01.wp01_searchmeter                              OK
WP01.wp01_searchmeter_recent                       OK
WP01.wp01_term_relationships                       OK
WP01.wp01_term_taxonomy                            OK
WP01.wp01_terms                                    OK
WP01.wp01_usermeta                                 OK
WP01.wp01_users                                    OK
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'...
OK
aws$

mysql upgrade

aws$ cat /usr/local/mysql/data/mysql_upgrade_info
5.5.28
aws$

mysql version

参考サイト:
D.1.2. Changes in MySQL 5.5.28 (2012-09-28)

Installing and Upgrading MySQL


MYSQL5.1.41がリリースされていたので、アップグレード
C.1.2. Changes in MySQL 5.1.41 (05 November 2009)

■InnoDB Plugin has been upgraded to version 1.0.5. This version is considered of
 Release Candidate (RC) quality.
■The InnoDB buffer pool is divided into two sublists: A new sublist containing blocks
 that are heavily used by queries, and an old sublist containing less-used blocks and from
 which candidates for eviction are taken.
 innodb_old_blocks_pct / innodb_old_blocks_time
■Important Change: Security Fix
Bug#32167, CVE-2008-2079 / Bug#47320, CVE-2009-4028 / Bug#46922
Partitioning: Bug#44059
Replication: Bug#48297 / Bug#48216 / Bug#47678 / Bug#47323 / Bug#47287/ Bug#46640
Bug#42829 / Bug#34582 / Bug#48370 / Bug#48295 / Bug#48293
その他多数……………………….

1) 現在のバージョン確認
original_20091221

http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.41-linux-i686-icc-glibc23.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/

2) 最新版のダウンロード
[root@colinux src]# wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.41-linux-i686-icc-glibc23.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/
–00:06:43– http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.41-linux-i686-icc-glibc23.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/

wget_5141

3) ダウンロードしたtarを解凍
[root@colinux src]# tar zxvf mysql-5.1.41-linux-i686-icc-glibc23.tar.gz
tar_zxvf

4) 展開したtarを自分が希望するパスへ移動 (これはオプション)
mv_mysql5141

5) 新しく展開したフォルダーのパーミッションの変更
[root@colinux local]# chown -R mysql:mysql mysql-5.1.41-linux-i686-icc-glibc23/
[root@colinux local]# chmod -R 755 mysql-5.1.41-linux-i686-icc-glibc23/

6) 現在稼動している、MYSQLサービスを停止します。
mysql_5141_migration

7) シンボリックリンクの削除
[root@colinux local]# pwd
[root@colinux local]# rm mysql

8) アップグレード前のまで利用していた、MYSQLのdataフォルダーを新しいバージョンのMYSQLにコピー

[root@colinux local]# cp -rp mysql-5.1.40-linux-i686-icc-glibc23/data mysql-5.1.41-linux-i686-icc-glibc23/

9) シンボリックリンクの作成とサービスの再開
[root@colinux local]# ln -s mysql-5.1.41-linux-i686-icc-glibc23/ mysql

mysql5141_symbolic

[root@colinux local]# /etc/init.d/mysql.server start
Starting MySQL…. SUCCESS!
[root@colinux local]#

10) システムデータベースのアップグレード(information_schema / mysql )

[root@colinux local]# /usr/local/mysql/bin/mysql_upgrade -u root -p

mysql_5141_sqlupgrade

11) バージョンアップの確認

※ mysql_upgrade_infoファイルの確認
[root@colinux data]# cat /usr/local/mysql/data/mysql_upgrade_info
upgrade_info

※ エラーログの確認
mysql_5141_log_confirmation

※ information_schemaとmysqlスキーマの確認
select TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_TIME,UPDATE_TIME
from information_schema.tables where
TABLE_SCHEMA = 'information_schema' or TABLE_SCHEMA = 'mysql'
and CREATE_TIME is not NULL and UPDATE_TIME is not null
order by CREATE_TIME desc;

system_tables

※ バージョン確認 (mysql> select @@version;)
version_5141


MYSQL5.1.40がリリースされていたので、 恒例のアップグレードをしました。

ダウンロードサイト
Linux (non RPM, Intel C/C++ compiled, glibc-2.3) downloads
upgrade_0

ダウンロード
[root@colinux ~]# wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.40-linux-i686-icc-glibc23.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/

upgrade_1
[root@colinux ~]# ls -l mysql-5.1.40-linux-i686-icc-glibc23.tar.gz
-rw-r–r– 1 root root 123633020 2009-10-07 06:22 mysql-5.1.40-linux-i686-icc-glibc23.tar.gz
[root@colinux ~]#

解凍
[root@colinux src]# tar zxvf mysql-5.1.40-linux-i686-icc-glibc23.tar.gz

意図する場へ移動
[root@colinux src]# mv mysql-5.1.40-linux-i686-icc-glibc23 /usr/local/

オーナーを変更
[root@colinux local]# chown -R mysql:mysql mysql-5.1.40-linux-i686-icc-glibc23/

フォルダー権限の設定
[root@colinux local]# chmod -R 755 mysql-5.1.40-linux-i686-icc-glibc23/

upgrade_2

サービスを停止(ダウンタイム)

[root@colinux local]# /etc/init.d/mysql.server stop
Shutting down MySQL. SUCCESS!
[root@colinux local]#

シンボリックリンクの削除と再作成(ダウンタイム)
[root@colinux local]# rm mysql
[root@colinux local]# ln -s mysql-5.1.40-linux-i686-icc-glibc23/ mysql

upgrade_3

オリジナルデータのコピー(ダウンタイム)
※/dataフォルダーの移動は必須ではない。my.cnfで指定してある/dataフォルダーやログが分けてあれば不要。
[root@colinux mysql-5.1.39-linux-i686-icc-glibc23]# pwd
/usr/local/mysql-5.1.39-linux-i686-icc-glibc23
[root@colinux mysql-5.1.39-linux-i686-icc-glibc23]# cp -rp data/ /usr/local/mysql-5.1.40-linux-i686-icc-glibc23/
[root@colinux mysql-5.1.39-linux-i686-icc-glibc23]#

サービスの再開(ダウンタイム終了)
[root@colinux bin]# /etc/init.d/mysql.server start
Starting MySQL… SUCCESS!

Information_schema , mysqlデータベースのアップグレード
[root@colinux bin]# /usr/local/mysql/bin/mysql_upgrade -u root -p
Enter password:
Looking for ‘mysql’ as: /usr/local/mysql/bin/mysql
Looking for ‘mysqlcheck’ as: /usr/local/mysql/bin/mysqlcheck
Running ‘mysqlcheck’ with connection arguments: ‘–port=3306’ ‘–socket=/tmp/mysql.sock’
Running ‘mysqlcheck’ with connection arguments: ‘–port=3306’ ‘–socket=/tmp/mysql.sock’
DB01.TABLE000 OK
DB01.TABLE007 OK
DB01.TABLE008 OK
DB01.TABLE009 OK
DB01.TABLE100 OK
DB01.TABLE101 OK

[Hup…]

mysql.user OK
mysql.user_info OK
Running ‘mysql_fix_privilege_tables’..OK
[root@colinux bin]#

upgrade_4

アップグレードの確認

[root@colinux data]# cat /usr/local/mysql/data/mysql_upgrade_info
5.1.40
[root@colinux data]#

upgrade_5


[データベースのアップグレード]

ダウンタイムが2~3分程度(データサイズによる)で済むデータベースのマイナーアップグレードのメモ書きです。


①  mysql-5.1.32-linux-i686-icc-glibc23からmysql-5.1.34-linux-i686-icc-glibc23へのアップグレード

② 最新のMYSQLをダウンロードしてきて、/usr/localに展開する。
  http://dev.mysql.com/downloads/mysql/5.1.html
Linux (non RPM, Intel C/C++ compiled, glibc-2.3) downloads
Linux (x86) 5.1.36 113.3M
Linux (AMD64 / Intel EM64T) 5.1.36 116.3M
Linux (IA64) 5.1.36 139.6M

③ lsコマンドで確認した状態。mysqlはシンボリックリンクなので最新のバイナリーをシンボリックリンクの切り替えで更新する。

[root@MYSQL01 local]# ls -l /usr/local/mysql*
lrwxrwxrwx 1 root root 35 7月 14 14:42 /usr/local/mysql -> mysql-5.1.34-linux-i686-icc-glibc23

/usr/local/mysql-5.1.32-linux-i686-icc-glibc23:
合計 148
-rwxrwxr-x 1 root mysql 19071 2月 14 19:22 COPYING
-rwxrwxr-x 1 root mysql 5139 2月 14 19:22 EXCEPTIONS-CLIENT
-rwxrwxr-x 1 root mysql 8769 2月 14 19:22 INSTALL-BINARY
-rwxrwxr-x 1 root mysql 1410 2月 14 19:22 README
drwxrwxr-x 2 root mysql 4096 2月 14 19:22 bin
drwxrwxr-x 4 root mysql 4096 3月 18 10:57 data
drwxrwxr-x 2 root mysql 4096 2月 14 19:20 docs
drwxrwxr-x 2 root mysql 4096 2月 14 19:20 include
drwxrwxr-x 3 root mysql 4096 2月 14 19:21 lib
drwxrwxr-x 4 root mysql 4096 2月 14 19:21 man
drwxrwxr-x 9 root mysql 4096 2月 14 19:22 mysql-test
drwxrwxr-x 2 root mysql 4096 2月 14 19:22 scripts
drwxrwxr-x 27 root mysql 4096 2月 14 19:22 share
drwxrwxr-x 5 root mysql 4096 2月 14 19:22 sql-bench
drwxrwxr-x 2 root mysql 4096 2月 14 19:22 support-files

/usr/local/mysql-5.1.34-linux-i686-icc-glibc23:
合計 156
-rwxrwxr-x 1 mysql mysql 19071 4月 1 08:52 COPYING
-rwxrwxr-x 1 mysql mysql 5139 4月 1 08:52 EXCEPTIONS-CLIENT
-rwxrwxr-x 1 mysql mysql 8769 4月 1 08:52 INSTALL-BINARY
-rwxrwxr-x 1 mysql mysql 1410 4月 1 08:52 README
drwxrwxr-x 2 mysql mysql 4096 7月 14 14:38 bin
drwxrwxr-x 11 root mysql 4096 7月 14 14:42 data
drwxrwxr-x 4 mysql mysql 4096 7月 14 08:53 data_original
drwxrwxr-x 2 mysql mysql 4096 4月 1 08:51 docs
drwxrwxr-x 2 mysql mysql 4096 4月 1 08:51 include
drwxrwxr-x 3 mysql mysql 4096 4月 1 08:51 lib
drwxrwxr-x 4 mysql mysql 4096 4月 1 08:51 man
drwxrwxr-x 10 mysql mysql 4096 4月 1 08:52 mysql-test
drwxrwxr-x 2 mysql mysql 4096 4月 1 08:52 scripts
drwxrwxr-x 27 mysql mysql 4096 4月 1 08:52 share
drwxrwxr-x 5 mysql mysql 4096 4月 1 08:52 sql-bench
drwxrwxr-x 2 mysql mysql 4096 4月 1 08:52 support-files
[root@MYSQL01 local]#

④ 既存で動作しているmysqlのサービスを一時停止する。

[root@MYSQL01 local]# /etc/init.d/mysql stop
Shutting down MySQL. [ OK ]

⑤ /usr/local/mysql-5.1.32-linux-i686-icc-glibc23/dataをフォルダー毎
新しいMYSQLのデータフォルダーにコピーする。
※データフォルダーやログが別にある時はそちらもコピーしておく。(my.cnf確認)

⑥ データコピーが終わったら既存のMYSQLはサービス停止時間を最小限に抑える為に再開しておく。

[root@MYSQL01 local]# /etc/init.d/mysql start
Starting MySQL. [ OK ]
[root@MYSQL01 local]# ls -l

⑦ 新しいデータベース用に準備したオプションファイルを明示的に指定して、新しいMYSQLも起動する。
./bin/mysqld_safe --defaults-file=/etc/my_05.1.34.cnf &

-------------------my_05.1.34.cnf---------------
port = 3307
socket = /tmp/mysql5.1.34.sock
basedir=/usr/local/mysql-5.1.34-linux-i686-icc-glibc23
datadir=/usr/local/mysql-5.1.34-linux-i686-icc-glibc23/data
slow_query_log=/usr/local/mysql-5.1.34-linux-i686-icc-glibc23/data/slow_query.log
------------------------------------------------

※事前に新しいMYSQLのフォルダーオーナーをmysqlなどのユーザーに変更しておく。

⑧  ローカルソケットとportを指定して、MYSQLに接続する。
何も指定しないとDefaultのオプションファイルが読み込まれ3306に古いMYSQLに接続されてしまう。(/etc/my.cnf)

./mysql -u root -p -P 3307 --socket=/tmp/mysql5.1.34.sock

⑨ 接続出来る事が確認出来たら、以下のように新規導入したMYSQLのアップグレード状況を"mysql_upgrade"
にて確認。不足している、システムテーブルはこのスクリプトにて補完される。

---------------------------------------------------------------------------------------
[root@MYSQL01 bin]# ./mysql_upgrade -u root -p -P 3307 --socket=/tmp/mysql5.1.34.sock
Enter password:
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck'...
Running 'mysqlcheck'...
WINDOWS.TABLE004_Brokerage OK
WINDOWS.TABLE004_Channel OK
WINDOWS.TABLE004_ConfidenceTerm OK
WINDOWS.TABLE004_DTCompany OK
WINDOWS.TABLE004_DTForeignExchange OK
WINDOWS.TABLE004_DTForeignstock OK
WINDOWS.TABLE004_HandlingItem OK
WINDOWS.TABLE004_HowToOrder OK
WINDOWS.TABLE004_InfoService OK
WINDOWS.TABLE004_InitialPublicOffering OK
WINDOWS.TABLE004_SearchTerm OK
WINDOWS.TABLE004_SecuritiesCompany OK
WINDOWS.TABLE004_Support OK
WINDOWS.TABLE004_TabSort OK
WINDOWS.TABLE007DTOfBank OK
WINDOWS.TABLE007InfoOfBank OK
WINDOWS.TABLE007ItemDT OK
WINDOWS.TABLE005CommodityExchange OK
WINDOWS.TABLE005DTOfCompany OK
WINDOWS.TABLE005FinancialScale OK
WINDOWS.TABLE005GuaranteeMoney OK
WINDOWS.TABLE005HandlingCommodity OK
WINDOWS.TABLE005HowToOrder OK
WINDOWS.TABLE005InformationSupport OK
WINDOWS.TABLE005Item OK
WINDOWS.TABLE005Margin OK
WINDOWS.TABLE005MobileService OK
WINDOWS.TABLE005Others OK
WINDOWS.TABLE005SearchTerm OK
WINDOWS.TABLE005Sort OK
WINDOWS.T_TEST OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log
Error : You can't use locks with log tables.
status : 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.servers OK
mysql.slow_log
Error : You can't use locks with log tables.
status : 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
test.M01 OK
test.STUDY OK
test.STUDY2 OK
test.T01 OK
test.T_SiteResponse OK
test.company OK
test.innodb_monitor OK
Running 'mysql_fix_privilege_tables'...
OK
[root@MYSQL01 bin]#

⑩ 特にエラーが無ければ、古いMYSQLと入れ替える為にシンボリックリンクを切り替えて
MYSQLを停止 → シンボリックリンク切り替え → MYSQLを起動。

[root@MYSQL01 local]# /etc/init.d/mysql stop
Shutting down MySQL. [ OK ]
[root@MYSQL01 local]# ls -l

[root@MYSQL01 local]# ls -l
lrwxrwxrwx 1 root root 35 7月 14 14:42 mysql -> mysql-5.1.32-linux-i686-icc-glibc23
drwxrwxr-x 13 root mysql 4096 2月 14 19:22 mysql-5.1.32-linux-i686-icc-glibc23
drwxrwxr-x 14 mysql mysql 4096 7月 14 14:12 mysql-5.1.34-linux-i686-icc-glibc23

[root@MYSQL01 local]# rm mysql

[root@MYSQL01 local]# ln -s mysql-5.1.34-linux-i686-icc-glibc23 mysql

lrwxrwxrwx 1 root root 35 7月 14 14:42 mysql -> mysql-5.1.34-linux-i686-icc-glibc23
drwxrwxr-x 13 root mysql 4096 2月 14 19:22 mysql-5.1.32-linux-i686-icc-glibc23
drwxrwxr-x 14 mysql mysql 4096 7月 14 14:12 mysql-5.1.34-linux-i686-icc-glibc23

[root@MYSQL01 local]# /etc/init.d/mysql start
Starting MySQL. [ OK ]
[root@MYSQL01 local]# ls -l

⑪ MYSQLのオプションファイルは指定しなくても、オリジナル(/etc/my.cnf)が読み込まれport 3306にて接続
出来る。この時点でデータベース基本アップグレードは終了。

[root@MYSQL01 local]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.34-log MySQL Community Server (GPL)

⑫ 最終確認として、Version確認、アカウント確認、接続確認などを行う。
最終的にエラーログにエラーが書き込まれて無ければ無事終了。

mysql> select version();
+------------+
| version() |
+------------+
| 5.1.34-log |
+------------+
1 row in set (0.00 sec)

mysql> select user,host,password from mysql.user;
+-----------------+-------------------------+-------------------------------------------+
| user | host | password |
+-----------------+-------------------------+-------------------------------------------+
| root | localhost | *D5B72F886E3918D73A1AAAAD8F6C62EC6C4DFFFF |
| root | MYSQL01.local | *D5B72F886E3918D73A1AAAAD8F6C62EC6C4DFFFF |
| root | 127.0.0.1 | *D5B72F886E3918D73A1AAAAD8F6C62EC6C4DFFFF |
| my_user | 192.168.0.72 | *16320B8A6B1F40A0D6924311E03EEEE6379AAAA2 |
| my_user | 192.168.0.115 | *16320B8A6B1F40A0D6924311E03EEEE6379AAAA2 |
| my_user | PC08001.local | *16320B8A6B1F40A0D6924311E03EEEE6379AAAA2 |
| my_user | PC06013.local | *16320B8A6B1F40A0D6924311E03EEEE6379AAAA2 |
+-----------------+-------------------------+-------------------------------------------+
20 rows in set (0.00 sec)

mysql> show full processlist;
+----+-----------------+-----------+--------------------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+--------------------+---------+------+-------+-----------------------+
| 10 | root | localhost | information_schema | Query | 0 | NULL | show full processlist |
| 28 | my_user | localhost | TEST | Sleep | 5414 | | NULL |
+----+-----------------+-----------+--------------------+---------+------+-------+-----------------------+
2 rows in set (0.01 sec)

mysql>

mysql> status;
--------------
mysql Ver 14.14 Distrib 5.1.34, for pc-linux-gnu (i686) using readline 5.1

Connection id: 10
Current database: information_schema
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.34-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 3 hours 13 min 56 sec

Threads: 2 Questions: 8919 Slow queries: 936 Opens: 619 Flush tables: 1 Open tables: 64 Queries per second avg: 0.766
--------------

mysql> exit