![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
先日、スレーブをアップグレードしてみて特に問題無さそうでしたので、
マスター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>
その他レプリケーションの状態など。
マスターアップグレード前に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]#