EC2で利用しているMYSQLのバージョンが低いので、
バグ対応とサイトレスポンス向上の為に5.5.25aにアップグレード。

mysql> select@@version;
+———–+
| @@version |
+———–+
| 5.5.20 |
+———–+
1 row in set (0.00 sec)

[ec2-user@ip-xx-xxx-xx-xxx src]$ uname -a
Linux ip-xx-xxx-xx-xxx x.x.xx-x.x.x.amzn1.x86_64 #1 SMP Thu Mar 22 08:00:08 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
[ec2-user@ip-xx-xxx-xx-xxx src]$

MySQL 5.5.25a

D.1.2. Changes in MySQL 5.5.25a (2012-07-05)
Note

Due to MSI restrictions, the MSI packages of MySQL 5.5.25a will treat the version as 5.5.26 internally;
for example, as displayed by the Installation Wizard. MySQL itself reports the version as 5.5.25a;
for example, if you check the value of the VERSION() SQL function or the version system variable.

Bugs Fixed
A regression bug in the optimizer could cause excessive disk usage for UPDATE statements. (Bug #65745, Bug #14248833)

MySQL 5.5.25

D.1.3. Changes in MySQL 5.5.25 (2012-05-30)
Note

MySQL 5.5.25 is superseded by MySQL 5.5.25a due to a regression

    bug that can cause excessive disk usage

(for details, see Bug #65745). Current users of 5.5.25: Monitor disk usage and upgrade to 5.5.25a
as soon as that is made available. Users contemplating upgrades to 5.5.25: Defer and upgrade to 5.5.25a
instead when that is made available.

Functionality Added or Changed

Important Change: Replication: The SHOW BINARY LOGS statement (and its equivalent SHOW MASTER LOGS)
may now be executed by a user with the REPLICATION CLIENT privilege. (Formerly, the SUPER privilege
was necessary to use either form of this statement.)
The –safe-mode server option now is deprecated and will be removed in MySQL 5.6.

Bugs Fixed

Performance: InnoDB:

    Improved the algorithm related to adaptive flushing.

This fix increases the rate of flushing in cases where compression is used and the
data set is larger than the buffer pool, leading to eviction. (Bug #13990648, Bug #65061)

InnoDB: In a transaction using the REPEATABLE READ isolation level, an UPDATE or DELETE
statement for an InnoDB table could sometimes overlook rows recently committed by other
transactions. As explained in Section 14.3.9.2, “Consistent Nonlocking Reads”,
DML statements within a REPEATABLE READ transaction apply to rows committed by
other transactions, even if a query could not see those rows. (Bug #14007649, Bug #65111)

InnoDB:

    The Innodb_buffer_pool_pages_flushed status variable was incorrectly set to twice the value it should be.

Its value should never exceed the value of Innodb_pages_written. (Bug #14000361, Bug #65030)

InnoDB: The error handling and message was improved for attempting to create a foreign key with
a column referencing itself. The message suggested a potential problem with the data dictionary,
when no such problem existed. (Bug #12902967)

InnoDB:

    The CHECK TABLE statement could fail for a large InnoDB table due to a timeout value of 2 hours.

For typical storage devices, the issue could occur for tables that exceeded approximately 200 or 350 GB,
depending on I/O speed. The fix relaxes the locking performed on the table being checked, which makes the
timeout less likely. It also makes InnoDB recognize the syntax CHECK TABLE QUICK, which avoids the
possibility of the timeout entirely. (Bug #11758510, Bug #50723)

Replication: It was theoretically possible for concurrent execution of more than one instance of
SHOW BINLOG EVENTS to crash the MySQL Server. (Bug #13979418)

Replication: Statements using AUTO_INCREMENT, LAST_INSERT_ID(), RAND(),
or user variables could be applied in the wrong context on the slave when
using statement-based replication and replication filtering server options
(see Section 16.2.3, “How Servers Evaluate Replication Filtering Rules”). (Bug #11761686, Bug #54201)

References: See also Bug #11754117, Bug #45670, Bug #11746146, Bug #23894.

Replication: An INSERT into a table that has a composite primary key that includes an
AUTO_INCREMENT column that is not the first column of this composite key is not safe for
statement-based binary logging or replication. Such statements are now marked as unsafe
and fail with an error when using the STATEMENT binary logging format.
For more information, see Section 16.1.2.3, “Determination of Safe and Unsafe Statements in Binary Logging”,
as well as Section 16.4.1.1, “Replication and AUTO_INCREMENT”. Note

Tables using the InnoDB storage engine are not affected by this issue,
since InnoDB does not allow the creation of a composite key that includes an AUTO_INCREMENT column,
where this column is not the first column in the key.
(Bug #11754117, Bug #45670)
References: See also Bug #11761686, Bug #54201, Bug #11746146, Bug #23894.

SHOW TABLES was very slow unless the required information was already in the disk cache. (Bug #60961, Bug #12427262)

Download(最新のMYSQLをダウンロード)
http://dev.mysql.com/downloads/mysql/5.5.html#downloads

[root@ip-xx-xxx-xx-xxx src]# ls -l
total 181992
-rw-rw-r– 1 ec2-user ec2-user 186355822 Jul 7 08:07 mysql-5.5.25a-linux2.6-x86_64.tar.gz
[root@ip-xx-xxx-xx-xxx src]# tar zxvf mysql-5.5.25a-linux2.6-x86_64.tar.gz
mysql-5.5.25a-linux2.6-x86_64/docs/mysql.info
mysql-5.5.25a-linux2.6-x86_64/docs/INFO_SRC
mysql-5.5.25a-linux2.6-x86_64/docs/INFO_BIN
mysql-5.5.25a-linux2.6-x86_64/docs/ChangeLog
mysql-5.5.25a-linux2.6-x86_64/COPYING
mysql-5.5.25a-linux2.6-x86_64/README
mysql-5.5.25a-linux2.6-x86_64/INSTALL-BINARY
mysql-5.5.25a-linux2.6-x86_64/bin/myisam_ftdump
mysql-5.5.25a-linux2.6-x86_64/bin/myisamchk
mysql-5.5.25a-linux2.6-x86_64/bin/myisamlog
[省略]
mysql-5.5.25a-linux2.6-x86_64/man/man1/resolve_stack_dump.1
mysql-5.5.25a-linux2.6-x86_64/man/man1/mysqlman.1
mysql-5.5.25a-linux2.6-x86_64/man/man1/resolveip.1
mysql-5.5.25a-linux2.6-x86_64/man/man1/mysql-stress-test.pl.1
mysql-5.5.25a-linux2.6-x86_64/man/man8/mysqld.8
[root@ip-xx-xxx-xx-xxx src]#

[root@ip-xx-xxx-xx-xxx src]# mv mysql-5.5.25a-linux2.6-x86_64 /usr/local/
[root@ip-xx-xxx-xx-xxx src]# cd /usr/local/
[root@ip-xx-xxx-xx-xxx local]# ls -l
total 44
drwxr-xr-x 2 root root 4096 May 27 18:00 bin
drwxr-xr-x 2 root root 4096 Jan 6 18:40 etc
drwxr-xr-x 2 root root 4096 Jan 6 18:40 games
drwxr-xr-x 3 root root 4096 May 27 18:00 include
drwxr-xr-x 2 root root 4096 May 27 08:12 lib
drwxr-xr-x 3 root root 4096 Mar 25 02:06 lib64
drwxr-xr-x 2 root root 4096 Jan 6 18:40 libexec
drwxr-xr-x 13 root root 4096 Jul 7 09:19 mysql-5.5.25a-linux2.6-x86_64
drwxr-xr-x 2 root root 4096 Jan 6 18:40 sbin
drwxr-xr-x 6 root root 4096 Mar 25 02:06 share
drwxr-xr-x 2 root root 4096 Jul 7 09:29 src
[root@ip-xx-xxx-xx-xxx local]#

シンボリックリンク作成
[root@ip-xx-xxx-xx-xxx local]# ln -s mysql-5.5.25a-linux2.6-x86_64/ mysql
[root@ip-xx-xxx-xx-xxx local]# ls -l
total 44
drwxr-xr-x 2 root root 4096 May 27 18:00 bin
drwxr-xr-x 2 root root 4096 Jan 6 18:40 etc
drwxr-xr-x 2 root root 4096 Jan 6 18:40 games
drwxr-xr-x 3 root root 4096 May 27 18:00 include
drwxr-xr-x 2 root root 4096 May 27 08:12 lib
drwxr-xr-x 3 root root 4096 Mar 25 02:06 lib64
drwxr-xr-x 2 root root 4096 Jan 6 18:40 libexec
lrwxrwxrwx 1 root root 30 Jul 7 09:30 mysql -> mysql-5.5.25a-linux2.6-x86_64/
drwxr-xr-x 13 root root 4096 Jul 7 09:19 mysql-5.5.25a-linux2.6-x86_64
drwxr-xr-x 2 root root 4096 Jan 6 18:40 sbin
drwxr-xr-x 6 root root 4096 Mar 25 02:06 share
drwxr-xr-x 2 root root 4096 Jul 7 09:29 src
[root@ip-xx-xxx-xx-xxx local]#

既存MYSQLを停止
[root@ip-xx-xxx-xx-xxx local]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]
[root@ip-xx-xxx-xx-xxx local]#

古いMYSQLのデータの場所を確認
[root@ip-xx-xxx-xx-xxx local]# cat /etc/my.cnf | grep data
datadir=/var/lib/mysql
[root@ip-xx-xxx-xx-xxx local]#

データベースデータを新しいMYSQLのデータフォルダーにコピー
[root@ip-xx-xxx-xx-xxx data]# cp -rp /var/lib/mysql/* /usr/local/mysql/data
[root@ip-xx-xxx-xx-xxx data]# ls -l
total 36892
-rw-rw—- 1 mysql mysql 27262976 Jul 7 09:33 ibdata1
-rw-rw—- 1 mysql mysql 5242880 Jul 7 09:33 ib_logfile0
-rw-rw—- 1 mysql mysql 5242880 Jun 7 02:13 ib_logfile1
-rw-rw—- 1 mysql root 11922 Mar 31 15:37 ip-xx-xxx-xx-xxx.err
drwx—— 2 mysql mysql 4096 Mar 31 15:54 mysql
drwx—— 2 mysql mysql 4096 Mar 31 15:54 performance_schema
drwx—— 2 mysql mysql 4096 Mar 31 15:14 test
drwx—— 2 mysql mysql 4096 Jun 15 16:55 WP01
[root@ip-xx-xxx-xx-xxx data]#

MYSQL定義ファイルをバックアップ
[root@ip-xx-xxx-xx-xxx support-files]# mv /etc/my.cnf /etc/my.cnf.20120707
[root@ip-xx-xxx-xx-xxx support-files]#

新しくMYSQL定義ファイルをコピー
[root@ip-xx-xxx-xx-xxx support-files]# cp -p my-small.cnf /etc/my.cnf
[root@ip-xx-xxx-xx-xxx support-files]#

データフォルダー、Socketなどの場所を編集
[root@ip-xx-xxx-xx-xxx support-files]# vi /etc/my.cnf

新しいMYSQLのデータフォルダーなどに書き込めるようにオーナー変更
[root@ip-xx-xxx-xx-xxx local]# chown -R mysql:mysql mysql-5.5.25a-linux2.6-x86_64/
[root@ip-xx-xxx-xx-xxx local]#

新しいMYSQLを起動
[root@ip-xx-xxx-xx-xxx local]# /etc/init.d/mysqld start
Starting mysqld: [ OK ]
[root@ip-xx-xxx-xx-xxx local]#

システムデータベースのアップグレード
[root@ip-xx-xxx-xx-xxx data]# /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=/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_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
[root@ip-xx-xxx-xx-xxx data]#

サービス起動ファイルの変更
※通常は、そのままにしてあるが前回はRPMでインストールして今回はTARから展開して利用しているので
 SocketやPIDを変更するのが手間なので、そのままコピー

[root@ip-xx-xxx-xx-xxx local]# cd support-files/
[root@ip-xx-xxx-xx-xxx support-files]# pwd
/usr/local/mysql/support-files
[root@ip-xx-xxx-xx-xxx support-files]# cp -p mysql.server /etc/init.d/mysqld

mysql> select @@version;
+———–+
| @@version |
+———–+
| 5.5.25a |
+———–+
1 row in set (0.00 sec)

mysql> select version();
+———–+
| version() |
+———–+
| 5.5.25a |
+———–+
1 row in set (0.00 sec)

mysql>

MYSQL5.5.25a

アップグレードでinnodb_buffer_pool_sizeが多くなっていたので変更。

mysql> show variables like ‘%buffer%’;
+——————————+———–+
| Variable_name | Value |
+——————————+———–+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 16384 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 2048 |
| preload_buffer_size | 32768 |
| read_buffer_size | 262144 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 65536 |
| sql_buffer_result | OFF |
+——————————+———–+
14 rows in set (0.00 sec)

mysql> show variables like ‘%buffer%’;
+——————————+———-+
| Variable_name | Value |
+——————————+———-+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 16777216 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 16384 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 2048 |
| preload_buffer_size | 32768 |
| read_buffer_size | 262144 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 65536 |
| sql_buffer_result | OFF |
+——————————+———-+
14 rows in set (0.00 sec)

mysql>

今回は久々にRPMでインストールしてあったMYSQLでアップグレードしたので、
/etc/init.d/mysqlの入れ替え、/etc/my.cnf、/etc/php.iniで少し時間かけてしまった。

PHP Socketの場所指定
MySQLに接続するときにエラー発生

パフォーマンスがいまいちなので、Wordpress用にQuery Cacheだけ設定しておいた。

mysql> SHOW VARIABLES LIKE ‘%query%’;
+——————————+————————————————-+
| Variable_name | Value |
+——————————+————————————————-+
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 10.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/ip-xx-xxx-xx-xxx-slow.log |
+——————————+————————————————-+

Comments are closed.

Post Navigation