![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
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$
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$
aws$ cat /usr/local/mysql/data/mysql_upgrade_info 5.5.28 aws$
