MySQL5.1→5.6移行を試し中のまとめ
Solaris10 ZFS & MySQL5.1 で動かしてる環境を Linux & MySQL5.6 化しています。
データサイズが大きくZFS 側はファイルシステムをgzip圧縮で利用中なので、Disk容量的に5.6へ移行する際にもサイズの大きいテーブルは圧縮が必須で、ここはInnoDBの圧縮機能を利用しました。
時間的な猶予はわりとあったので、多少試行錯誤しつつなんとか 5.6 のスレーブを作るところまで完成したのでまとめておきます。
しかし、@sh2nd さんの記事に頼りきりでした。良質な記事ばかりでいつも助かっています。本当にありがとうございますとしか言いようがありません。
●InnoDB圧縮まわり
http://d.hatena.ne.jp/sh2/20090628
http://d.hatena.ne.jp/sh2/20090705
●5.6のパラメータまわり
http://d.hatena.ne.jp/sh2/20130806
■移行方針
- クライアントライブラリは変更せず使えればそれに越したことはない
- アプリケーション向けの挙動はなるべく変わらないようにしたい
- 文字コードは 5.1 時の utf8 維持
- データは引き継いだ環境を作っておいて、ホストのIPアドレス変更作業で短時間停止で入替を試みる
- 先日の"MySQL Casual Talks vol.5"や、"db tech showcase 東京 2013"で聞いた感じで、GTIDやPerformanceSchema 周りは一旦off
■構成遷移
0.元構成
Master(Solaris ZFS/5.1)
|
非同期レプリケーション
|
Slave(Solaris ZFS/5.1)
1.新ホストに 5.1 をコピー
Master(Solaris ZFS/5.1)
|
非同期レプリケーション
|
Slave(Solaris ZFS/5.1) -- stop slave & zfs snap した静止点をコピー --> Slave(Linux XFS/5.1)
2.新ホストの 5.1 を1回Masterと同期
Master(Solaris ZFS/5.1) ---------+
| |
非同期レプリケーション 非同期レプリケーション
| |
Slave(Solaris ZFS/5.1) Slave(Linux XFS/5.1)
3.新ホストの 5.1 を 5.5 へアップグレード
Master(Solaris ZFS/5.1) ---------+
| |
非同期レプリケーション stop slave & mysqlupgrade
| |
Slave(Solaris ZFS/5.1) Slave(Linux XFS/5.5)
4.新ホストの 5.5 を replicate-do-db で必要なDBに絞ってレプリケーション再開
Master(Solaris ZFS/5.1) ---------+
| |
非同期レプリケーション 非同期レプリケーション(replicate-do-dbでDB絞り)
| |
Slave(Solaris ZFS/5.1) Slave(Linux XFS/5.5)
※mysqlデータベース構造が同期されないように replicate-do-db でデータ移行が必要なDBに絞りました
5.新ホストの 5.5 で、ALTER TABLE ～ ROW_FORMAT=compressed KEY_BLOCK_SIZE=n で圧縮
Master(Solaris ZFS/5.1) ---------+
| |
非同期レプリケーション stop slave -> start slave io_thread -> ALTER TABLE
| |
Slave(Solaris ZFS/5.1) Slave(Linux XFS/5.5)
※ALTER 中はレプリケーションのSQLスレッドを停止、IOスレッドはバイナリログ確保のために稼動させ続けました
6.圧縮の終わった 5.5 でSQLスレッドを稼動させて同期させる
Master(Solaris ZFS/5.1) ---------+
| |
非同期レプリケーション 非同期レプリケーション(replicate-do-dbでDB絞り)
| |
Slave(Solaris ZFS/5.1) Slave(Linux XFS/5.5) ※InnoDBテーブル圧縮状態
※後続の 5.6 側レプリケーションに備えて、5.5 側では log_slave_updates=ON しておきます
7.Linux 5.5 を一旦停止して、5.6 向けにデータコピーして5.6へアップグレード
Master(Solaris ZFS/5.1) ---------+
| |
非同期レプリケーション 非同期レプリケーション(replicate-do-dbでDB絞り)
| |
Slave(Solaris ZFS/5.1) Slave(Linux XFS/5.5)
|
5.5 のコールドバックアップ状態のファイルをコピー & msyqlupgrade
|
Slave(Linux XFS/5.6)
8.Linux 5.6 側を 5.5 と非同期レプリケーションを張る
Master(Solaris ZFS/5.1) ---------+
| |
非同期レプリケーション 非同期レプリケーション(replicate-do-dbでDB絞り)
| |
Slave(Solaris ZFS/5.1) Slave(Linux XFS/5.5)
|
非同期レプリケーション(replicate-do-dbでDB絞り)
|
Slave(Linux XFS/5.5)
■5.6 の my.cnf サンプル
[mysqld_safe] basedir = /usr/local/mysql [mysqld] ## Base socket = /tmp/mysql56.sock port = 33306 user = mysql character_set_server = utf8 collation_server = utf8_general_ci default_time_zone = SYSTEM default_storage_engine = InnoDB innodb_file_per_table = 1 skip-character-set-client-handshake skip-name-resolve explicit_defaults_for_timestamp = OFF #sql_mode = TRADITIONAL #transaction_isolation = READ-COMMITTED ## File Location basedir = /usr/local/mysql datadir = /usr/local/mysql/data innodb_data_home_dir = /usr/local/mysql/data innodb_log_group_home_dir = /usr/local/mysql/data slave_load_tmpdir = /usr/local/mysql/mysqltmp tmpdir = /usr/local/mysql/mysqltmp innodb_data_file_path = ibdata1:1G:autoextend ## InnoDB #innodb_buffer_pool_dump_at_shutdown = ON #innodb_buffer_pool_load_at_startup = ON innodb_buffer_pool_size = 10240M #innodb_checksum_algorithm = crc32 innodb_file_format = Barracuda innodb_flush_method = O_DIRECT innodb_large_prefix = ON innodb_log_buffer_size = 8M innodb_log_file_size = 1G innodb_log_files_in_group = 3 #innodb_online_alter_log_max_size = 1G innodb_print_all_deadlocks = ON innodb_flush_log_at_trx_commit = 1 innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_io_capacity = 5000 innodb_thread_concurrency = 0 innodb_autoextend_increment = 256 skip-innodb-doublewrite ## PerformanceSchema performance_schema = OFF ## MyISAM key_buffer_size = 512M ## Memory max_heap_table_size = 256M tmp_table_size = 256M ## Binary Log binlog_format = MIXED log-bin = mysql-bin binlog_checksum = none #enforce_gtid_consistency = ON expire_logs_days = 2 #gtid_mode = ON log_slave_updates = ON master_info_repository = TABLE #master_verify_checksum = ON max_binlog_size = 1G relay_log_info_repository = TABLE relay_log_recovery = ON #report_host = <hostname> #report_port = 3306 server_id = 3 #slave_net_timeout = 30 sync_binlog = 1 ## Slow Query and General Log log_output = FILE log_queries_not_using_indexes = ON log_slow_slave_statements = ON log_throttle_queries_not_using_indexes = 60 long_query_time = 60.0 slow_query_log = ON #general_log = 1 ## Connection Management #log_warnings = 2 max_allowed_packet = 16M #max_connect_errors = 999999999 max_connections = 200 table_open_cache = 10000 table_open_cache_instances = 16 ## Memory Allocation per Connection read_buffer_size = 2M read_rnd_buffer_size = 2M sort_buffer_size = 2M ## Replication read_only replicate-do-db=hogehoge #plugin-load=rpl_semi_sync_master=semisync_master.so #plugin-load=rpl_semi_sync_slave=semisync_slave.so #//master settings #rpl_semi_sync_master_enabled=1 #rpl_semi_sync_master_timeout=5000 [client] port = 33306 socket = /tmp/mysql56.sock [mysqldump] quick max_allowed_packet = 16M
■5.6 のエラーログサンプル
2013-11-xx 16:33:12 34945 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='127.0.0.1', master_port= 3306, master_log_file='mysql-bin.000008', master_log_pos= 71975, master_bind=''. New state master_host='xxx.xxx.xxx.xxx', master_port= 3306, master_log_file='mysql-bin.000008', master_log_pos= 71975, master_bind=''. 2013-11-xx 16:33:24 34945 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 2013-11-xx 16:33:24 34945 [Note] Slave I/O thread: connected to master 'slave@xxx.xxx.xxx.xxx:3306',replication started in log 'mysql-bin.000008' at position 71975 2013-11-xx 16:33:24 34945 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000008' at position 71975, relay log './mysqlhost4-relay-bin.000001' position: 4 2013-11-xx 16:33:24 34945 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193 2013-11-xx 16:33:24 34945 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.34-log), even though it is on the slave (version: 5.6.14-log), Error_code: 1193 2013-11-xx 15:17:07 34945 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown 2013-11-xx 15:17:07 34945 [Note] Giving 2 client threads a chance to die gracefully 2013-11-xx 15:17:07 34945 [Note] Event Scheduler: Purging the queue. 0 events 2013-11-xx 15:17:07 34945 [Note] Shutting down slave threads 2013-11-xx 15:17:07 34945 [Note] Error reading relay log event: slave SQL thread was killed 2013-11-xx 15:17:08 34945 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 2013-11-xx 15:17:08 34945 [Note] Slave I/O thread killed while reading event 2013-11-xx 15:17:08 34945 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000015', position 45513016 2013-11-xx 15:17:08 34945 [Note] Forcefully disconnecting 0 remaining clients 2013-11-xx 15:17:08 34945 [Note] Binlog end 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'partition' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'ARCHIVE' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'BLACKHOLE' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_SYS_FIELDS' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_SYS_INDEXES' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_SYS_TABLES' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_FT_CONFIG' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_FT_DELETED' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_METRICS' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_CMPMEM' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_CMP_RESET' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_CMP' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_LOCK_WAITS' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_LOCKS' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'INNODB_TRX' 2013-11-xx 15:17:08 34945 [Note] Shutting down plugin 'InnoDB' 2013-11-xx 15:17:08 34945 [Note] InnoDB: FTS optimize thread exiting. 2013-11-xx 15:17:08 34945 [Note] InnoDB: Starting shutdown... 2013-11-xx 15:17:45 34945 [Note] InnoDB: Shutdown completed; log sequence number 4314504063076 2013-11-xx 15:17:45 34945 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA' 2013-11-xx 15:17:45 34945 [Note] Shutting down plugin 'CSV' 2013-11-xx 15:17:45 34945 [Note] Shutting down plugin 'MEMORY' 2013-11-xx 15:17:45 34945 [Note] Shutting down plugin 'MRG_MYISAM' 2013-11-xx 15:17:45 34945 [Note] Shutting down plugin 'MyISAM' 2013-11-xx 15:17:45 34945 [Note] Shutting down plugin 'sha256_password' 2013-11-xx 15:17:45 34945 [Note] Shutting down plugin 'mysql_old_password' 2013-11-xx 15:17:45 34945 [Note] Shutting down plugin 'mysql_native_password' 2013-11-xx 15:17:45 34945 [Note] Shutting down plugin 'binlog' 2013-11-xx 15:17:45 34945 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 131126 15:17:46 mysqld_safe mysqld from pid file /data/data/mysqlhost4.pid ended 131126 15:26:34 mysqld_safe Starting mysqld daemon with databases from /data/data 2013-11-xx 15:26:34 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2013-11-xx 15:26:34 4434 [Note] Plugin 'FEDERATED' is disabled. 2013-11-xx 15:26:34 4434 [Note] InnoDB: The InnoDB memory heap is disabled 2013-11-xx 15:26:34 4434 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2013-11-xx 15:26:34 4434 [Note] InnoDB: Compressed tables use zlib 1.2.3 2013-11-xx 15:26:34 4434 [Note] InnoDB: Using Linux native AIO 2013-11-xx 15:26:34 4434 [Note] InnoDB: Using CPU crc32 instructions 2013-11-xx 15:26:34 4434 [Note] InnoDB: Initializing buffer pool, size = 10.0G 2013-11-xx 15:26:35 4434 [Note] InnoDB: Completed initialization of buffer pool 2013-11-xx 15:26:35 4434 [Note] InnoDB: Highest supported file format is Barracuda. 2013-11-xx 15:26:36 4434 [Note] InnoDB: 128 rollback segment(s) are active. 2013-11-xx 15:26:36 4434 [Note] InnoDB: Waiting for purge to start 2013-11-xx 15:26:36 4434 [Note] InnoDB: 5.6.14 started; log sequence number 4314504063076 2013-11-xx 15:26:36 4434 [Note] Server hostname (bind-address): '*'; port: 33306 2013-11-xx 15:26:36 4434 [Note] IPv6 is not available. 2013-11-xx 15:26:36 4434 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 2013-11-xx 15:26:36 4434 [Note] Server socket created on IP: '0.0.0.0'. 2013-11-xx 15:26:36 4434 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqlhost4-relay-bin' to avoid this problem. 2013-11-xx 15:26:36 4434 [Warning] Recovery from master pos 45513016 and file mysql-bin.000015. 2013-11-xx 15:26:36 4434 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 2013-11-xx 15:26:36 4434 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000015' at position 45513016, relay log './mysqlhost4-relay-bin.000033' position: 4 2013-11-xx 15:26:36 4434 [Note] Slave I/O thread: connected to master 'slave@xxx.xxx.xxx.xxx:3306',replication started in log 'mysql-bin.000015' at position 45513016 2013-11-xx 15:26:36 4434 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193 2013-11-xx 15:26:36 4434 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.34-log), even though it is on the slave (version: 5.6.14-log), Error_code: 1193 2013-11-xx 15:26:36 4434 [Note] Event Scheduler: Loaded 0 events 2013-11-xx 15:26:36 4434 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.6.14-log' socket: '/tmp/mysql56.sock' port: 23306 MySQL Community Server (GPL)
※余談
エラーログ見るとわりとボロボロです。レプリケーション周りの警告はまだ調べてません。
ALTER での圧縮ですが、135G のファイルが 10時間弱、650G のファイルが3日ほどかかりました。
iostat/vmstat を見ているとIOは余裕があったので、サーバのCPU環境にかなり依存するようです。
5.6.7 からは圧縮について調整するパラメータが追加されています。
今回は 5.5 上で圧縮したため使えませんでしたが、5.6系であればこの辺を調整してもう少し速度や圧縮率の調整ができそうです。
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_compression_failure_threshold_pct
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_compression_level
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_compression_pad_pct_max
