MySQL5.6的In-Place升级实战:MySQL5.6.20升至MySQL5.6.22
注:本MySQL安装了sphinx2.1.9插件
(1)进入安装文件目录,解压缩:
[root@localhost ~]# cd /install_files/ [root@localhost install_files]# tar -xvf mysql-5.6.22.tar.gz
(2)拷贝sphinx的MySQL相关文件,并编译安装MySQL:
[root@localhost install_files]# cp -r ./sphinx-2.1.9-release/mysqlse/ ./mysql-5.6.22/storage/sphinx [root@localhost install_files]# cd mysql-5.6.22 [root@localhost mysql-5.6.22]# cmake . -DCMAKE_INSTALL_PREFIX=/opt/mysql [root@localhost mysql-5.6.22]# make [root@localhost mysql-5.6.22]# make install
(3)直接执行mysql_upgrade命令升级会失败
[root@localhost mysql-5.6.22]# mysql_upgrade -uroot -p Enter password: Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck FATAL ERROR: Upgrade failed
(4)以忽略授权的方式来启动MySQL(本案例中数据文件位置为/opt/mysql_data/,注意修改)
[root@localhost mysql-5.6.22]# mysqld --datadir=/opt/mysql_data/ --user=mysql --skip-grant-tables & [1] 2952 [root@localhost mysql-5.6.22]# 2015-01-11 20:35:06 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release. 2015-01-11 20:35:06 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2015-01-11 20:35:06 2952 [Warning] You need to use --log-bin to make --binlog-format work. 2015-01-11 20:35:06 2952 [Note] Plugin 'FEDERATED' is disabled. 2015-01-11 20:35:06 2952 [Note] InnoDB: Using atomics to ref count buffer pool pages 2015-01-11 20:35:06 2952 [Note] InnoDB: The InnoDB memory heap is disabled 2015-01-11 20:35:06 2952 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2015-01-11 20:35:06 2952 [Note] InnoDB: Memory barrier is not used 2015-01-11 20:35:06 2952 [Note] InnoDB: Compressed tables use zlib 1.2.3 2015-01-11 20:35:06 2952 [Note] InnoDB: Using Linux native AIO 2015-01-11 20:35:06 2952 [Note] InnoDB: Not using CPU crc32 instructions 2015-01-11 20:35:06 2952 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2015-01-11 20:35:06 2952 [Note] InnoDB: Completed initialization of buffer pool 2015-01-11 20:35:06 2952 [Note] InnoDB: Highest supported file format is Barracuda. 2015-01-11 20:35:06 2952 [Note] InnoDB: 128 rollback segment(s) are active. 2015-01-11 20:35:06 2952 [Note] InnoDB: Waiting for purge to start 2015-01-11 20:35:06 2952 [Note] InnoDB: 5.6.22 started; log sequence number 16928437501 2015-01-11 20:35:06 2952 [Note] Server hostname (bind-address): '*'; port: 3306 2015-01-11 20:35:06 2952 [Note] IPv6 is available. 2015-01-11 20:35:06 2952 [Note] - '::' resolves to '::'; 2015-01-11 20:35:06 2952 [Note] Server socket created on IP: '::'. 2015-01-11 20:35:06 2952 [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=localhost-relay-bin' to avoid this problem. 2015-01-11 20:35:06 2952 [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. 2015-01-11 20:35:06 2952 [Note] Slave I/O thread: connected to master 'manager_slave@192.168.12.175:3306',replication started in log 'logbin.000053' at position 34074053 2015-01-11 20:35:06 2952 [Note] mysqld: ready for connections. Version: '5.6.22-log' socket: '/opt/mysql/mysql.sock' port: 3306 Source distribution 2015-01-11 20:35:06 2952 [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 2015-01-11 20:35:06 2952 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0 2015-01-11 20:35:06 2952 [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.1.54-CoreSeek-log), even though it is on the slave (version: 5.6.22-log), Error_code: 1193 2015-01-11 20:35:06 2952 [Note] Slave SQL thread initialized, starting replication in log 'logbin.000053' at position 34074053, relay log './localhost-relay-bin.000119' position: 850
(5)关闭mysql
[root@localhost mysql-5.6.22]# mysqladmin shutdown 2015-01-11 20:39:32 2952 [Note] mysqld: Normal shutdown 2015-01-11 20:39:32 2952 [Note] Giving 2 client threads a chance to die gracefully 2015-01-11 20:39:32 2952 [Note] Shutting down slave threads 2015-01-11 20:39:32 2952 [Note] Error reading relay log event: slave SQL thread was killed 2015-01-11 20:39:32 2952 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 2015-01-11 20:39:32 2952 [Note] Slave I/O thread killed while reading event 2015-01-11 20:39:32 2952 [Note] Slave I/O thread exiting, read up to log 'logbin.000053', position 34074199 2015-01-11 20:39:32 2952 [Note] Forcefully disconnecting 0 remaining clients 2015-01-11 20:39:32 2952 [Note] Binlog end 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'partition' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_SYS_FIELDS' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_SYS_INDEXES' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_SYS_TABLES' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_FT_CONFIG' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_FT_DELETED' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_METRICS' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_CMPMEM' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_CMP_RESET' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_CMP' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_LOCK_WAITS' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_LOCKS' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'INNODB_TRX' 2015-01-11 20:39:32 2952 [Note] Shutting down plugin 'InnoDB' 2015-01-11 20:39:32 2952 [Note] InnoDB: FTS optimize thread exiting. 2015-01-11 20:39:32 2952 [Note] InnoDB: Starting shutdown... 2015-01-11 20:39:33 2952 [Note] InnoDB: Shutdown completed; log sequence number 16928437511 2015-01-11 20:39:33 2952 [Note] Shutting down plugin 'BLACKHOLE' 2015-01-11 20:39:33 2952 [Note] Shutting down plugin 'ARCHIVE' 2015-01-11 20:39:33 2952 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA' 2015-01-11 20:39:33 2952 [Note] Shutting down plugin 'MRG_MYISAM' 2015-01-11 20:39:33 2952 [Note] Shutting down plugin 'CSV' 2015-01-11 20:39:33 2952 [Note] Shutting down plugin 'MyISAM' 2015-01-11 20:39:33 2952 [Note] Shutting down plugin 'MEMORY' 2015-01-11 20:39:33 2952 [Note] Shutting down plugin 'sha256_password' 2015-01-11 20:39:33 2952 [Note] Shutting down plugin 'mysql_old_password' 2015-01-11 20:39:33 2952 [Note] Shutting down plugin 'mysql_native_password' 2015-01-11 20:39:33 2952 [Note] Shutting down plugin 'binlog' 2015-01-11 20:39:33 2952 [Note] mysqld: Shutdown complete [1]+ Done mysqld --datadir=/opt/mysql_data/ --user=mysql --skip-grant-tables
(6)再次执行mysql_upgrade命令升级,成功
注:如果出现Repairing tables,只要处理结果是OK就没问题
[root@localhost bin]# mysql_upgrade -uroot -p …… xyz.abc01 OK Repairing tables hn.sphinx Error : Unknown storage engine 'SPHINX' error : Corrupt OK
(7)关闭mysql:注意访问权限
[root@localhost bin]# mysqladmin -h127.0.0.1 shutdown -uroot -p Enter password: 2015-01-11 20:52:35 4201 [Note] mysqld: Normal shutdown 2015-01-11 20:52:35 4201 [Note] Giving 2 client threads a chance to die gracefully 2015-01-11 20:52:35 4201 [Note] Shutting down slave threads [root@localhost bin]# 2015-01-11 20:52:35 4201 [Note] Error reading relay log event: slave SQL thread was killed 2015-01-11 20:52:35 4201 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 2015-01-11 20:52:35 4201 [Note] Slave I/O thread killed while reading event 2015-01-11 20:52:35 4201 [Note] Slave I/O thread exiting, read up to log 'logbin.000053', position 34074199 2015-01-11 20:52:35 4201 [Note] Forcefully disconnecting 0 remaining clients 2015-01-11 20:52:35 4201 [Note] Binlog end 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'partition' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_SYS_FIELDS' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_SYS_INDEXES' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_SYS_TABLES' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_FT_CONFIG' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_FT_DELETED' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_METRICS' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_CMPMEM' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_CMP_RESET' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_CMP' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_LOCK_WAITS' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_LOCKS' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'INNODB_TRX' 2015-01-11 20:52:35 4201 [Note] Shutting down plugin 'InnoDB' 2015-01-11 20:52:35 4201 [Note] InnoDB: FTS optimize thread exiting. 2015-01-11 20:52:35 4201 [Note] InnoDB: Starting shutdown... 2015-01-11 20:52:36 4201 [Note] InnoDB: Shutdown completed; log sequence number 16928437621 2015-01-11 20:52:36 4201 [Note] Shutting down plugin 'BLACKHOLE' 2015-01-11 20:52:36 4201 [Note] Shutting down plugin 'ARCHIVE' 2015-01-11 20:52:36 4201 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA' 2015-01-11 20:52:36 4201 [Note] Shutting down plugin 'MRG_MYISAM' 2015-01-11 20:52:36 4201 [Note] Shutting down plugin 'CSV' 2015-01-11 20:52:36 4201 [Note] Shutting down plugin 'MyISAM' 2015-01-11 20:52:36 4201 [Note] Shutting down plugin 'MEMORY' 2015-01-11 20:52:36 4201 [Note] Shutting down plugin 'sha256_password' 2015-01-11 20:52:36 4201 [Note] Shutting down plugin 'mysql_old_password' 2015-01-11 20:52:36 4201 [Note] Shutting down plugin 'mysql_native_password' 2015-01-11 20:52:36 4201 [Note] Shutting down plugin 'binlog' 2015-01-11 20:52:36 4201 [Note] mysqld: Shutdown complete
(8)正常启动mysql
[root@localhost bin]# service mysqld start Starting MySQL.. [确定]
(9)确认mysql的版本
[root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23 Server version: 5.6.22-log Source distribution 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>