多重复制架构下的MySQL升级实践：5.1-5.5

升级前准备

1.下载mysql-5.5.42、mysql-5.6.23到/usr/local目录下

cd /usr/local
wget http://mysql.com/Downloads/MySQL-5.5/mysql-5.5.42-linux2.6-x86_64.tar.gz
wget http://mysql.com/Downloads/MySQL-5.6/mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz

2.解压mysql-5.5.42程序到/usr/local/目录下

tar zcf /usr/local/mysql-5.5.42-linux2.6-x86_64.tar.gz .

3.获取root权限

升级SLAVE到MySQL 5.5.42

1.确认当前待升级的 db server为只读状态，如果不是，则设置为只读

show global variables like 'read_only';
SET global read_only=true;

2.确认slave禁用了程序启动后自动开启复制，如果没有禁用则修改mysql配置文件禁止mysql自动开启复制

grep 'skip-slave-start' /etc/mysql/my.cnf

2.关闭slave复制线程，并记录复制点

STOP SLAVE;
SHOW SLAVE STATUS \G

3.关闭mysql slave server

/etc/init.d/mysql stop

4.替换MySQL程序为新的版本

cd /usr/local/
mv mysql mysql-5.1
mv mysql-5.5.42 mysql
mv /etc/init.d/mysql /etc/init.d/mysql-5.1
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

6.启动MySQL服务并执行升级程序

/etc/init.d/mysql start
/usr/local/mysql/bin/mysql_upgrade --skip-write-binlog -udbadmin -p

7.重启MySQL服务

/etc/init.d/mysql stop
/etc/init.d/mysql start

8.验证MySQL版本及可用性

SELECT @@version;

USE test;

-- 创建测试表
CREATE TABLE test.mysql_upgrade_test (is_upgrade_success enum('true','false') NOT NULL DEFAULT 'true') ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- 验证insert操作
INSERT INTO test.mysql_upgrade_test (is_upgrade_success) VALUES ('false');
-- 验证update操作
UPDATE test.mysql_upgrade_test SET is_upgrade_success='true';
-- 验证 select 操作
SELECT * FROM test.mysql_upgrade_test;
-- 验证 delete 操作
DELETE FROM test.mysql_upgrade_test;
-- 删除测试表
DROP TABLE test.mysql_upgrade_test;

9.启动slave复制

START SLAVE;

10.观察复制是否正常

SHOW SLAVE STATUS \G

升级MASTER到MySQL 5.5.42

1.确认vt web db slave是否为只读，如果不是，则将其设为只读

SHOW GLOBAL VARIABLES LIKE 'read_only';
SET GLOBAL read_only = on;

2.停止keepalived应用程序，使应用切换到slave上。

/etc/init.d/keepalived stop

3.确认master禁用了自动开启复制。如果没有禁用，则修改mysql配置文件禁止mysql自动开启复制

grep 'skip-slave-start' /etc/mysql/my.cnf

4.关闭复制线程，并记录复制点

STOP SLAVE;
SHOW SLAVE STATUS \G

5.关闭mysql slave server

/etc/init.d/mysql stop

6.替换MySQL程序为新的版本

cd /usr/local/
mv mysql mysql-5.1
mv mysql-5.5.42 mysql
mv /etc/init.d/mysql /etc/init.d/mysql-5.1
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

6.启动MySQL服务并执行升级程序（注意：升级master的时候要加上--skip-write-binlog参数，避免升级操作通过binlog同步到slave上）

/etc/init.d/mysql start
/usr/local/mysql/bin/mysql_upgrade --skip-write-binlog -udbadmin -p

7.重启MySQL服务

/etc/init.d/mysql stop
/etc/init.d/mysql start

8.验证MySQL版本及可用性

SELECT @@VERSION;

USE test;

-- 创建测试表
CREATE TABLE test.mysql_upgrade_test (is_upgrade_success enum('true','false') NOT NULL DEFAULT 'true') ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- 验证insert操作
INSERT INTO test.mysql_upgrade_test (is_upgrade_success) VALUES ('false');
-- 验证update操作
UPDATE test.mysql_upgrade_test SET is_upgrade_success='true';
-- 验证 select 操作
SELECT * FROM test.mysql_upgrade_test;
-- 验证 delete 操作
DELETE FROM test.mysql_upgrade_test;
-- 删除测试表
DROP TABLE test.mysql_upgrade_test;

9.启动slave复制

START SLAVE;

10.观察复制是否正常

SHOW SLAVE STATUS \G

13.相关命令

异常情况处理

1). 采取先slave后master的升级方式。master升级异常，在规定时间内无法修复，可以切换到slave提供服务。

2).最近在升级一个带分区表的库时，其中有两张表在升级过程中由于分区采用KEY分区方式，升级时脚本报错，如下：

host-a:/usr/local# /usr/local/mysql/bin/mysql_upgrade -udbadmin -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=/tmp/mysql.sock'
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
xtaisan.video_slideshowInfo OK
xtaisan.video_trackingMeta
error : KEY () partitioning changed, please run:
ALTER TABLE `xtaisan`.`video_trackingMeta` PARTITION BY RANGE (video_id)
SUBPARTITION BY KEY /*!50531 ALGORITHM = 1 */ (video_id)
SUBPARTITIONS 4
(PARTITION p_1 VALUES LESS THAN (2400000000) ENGINE = InnoDB,
PARTITION p_2 VALUES LESS THAN (2500000000) ENGINE = InnoDB,
PARTITION p_3 VALUES LESS THAN (2600000000) ENGINE = InnoDB,
PARTITION p_4 VALUES LESS THAN (2700000000) ENGINE = InnoDB,
PARTITION p_5 VALUES LESS THAN (2800000000) ENGINE = InnoDB,
PARTITION p_6 VALUES LESS THAN (2900000000) ENGINE = InnoDB,
PARTITION p_7 VALUES LESS THAN (3000000000) ENGINE = InnoDB,
PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
status : Operation failed
xtaisan.video_trackingMetaFinished
error : KEY () partitioning changed, please run:
ALTER TABLE `xtaisan`.`video_trackingMetaFinished` PARTITION BY RANGE (video_id)
SUBPARTITION BY KEY /*!50531 ALGORITHM = 1 */ (video_id)
SUBPARTITIONS 4
(PARTITION p_1 VALUES LESS THAN (2400000000) ENGINE = InnoDB,
PARTITION p_2 VALUES LESS THAN (2500000000) ENGINE = InnoDB,
PARTITION p_3 VALUES LESS THAN (2600000000) ENGINE = InnoDB,
PARTITION p_4 VALUES LESS THAN (2700000000) ENGINE = InnoDB,
PARTITION p_5 VALUES LESS THAN (2800000000) ENGINE = InnoDB,
PARTITION p_6 VALUES LESS THAN (2900000000) ENGINE = InnoDB,
PARTITION p_7 VALUES LESS THAN (3000000000) ENGINE = InnoDB,
PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
status : Operation failed
xtaisan.video_trackingMetaNewVDNA OK
......
xvstool.websites OK
Running 'mysql_fix_privilege_tables'...
OK
root@EQX-148:/usr/local#

解决办法：

dbadmin@(none) 04:32:13>ALTER TABLE `xtaisan`.`video_trackingMeta` PARTITION BY RANGE (video_id)
-> SUBPARTITION BY KEY /*!50531 ALGORITHM = 1 */ (video_id)
-> SUBPARTITIONS 4
-> (PARTITION p_1 VALUES LESS THAN (2400000000) ENGINE = InnoDB,
-> PARTITION p_2 VALUES LESS THAN (2500000000) ENGINE = InnoDB,
-> PARTITION p_3 VALUES LESS THAN (2600000000) ENGINE = InnoDB,
-> PARTITION p_4 VALUES LESS THAN (2700000000) ENGINE = InnoDB,
-> PARTITION p_5 VALUES LESS THAN (2800000000) ENGINE = InnoDB,
-> PARTITION p_6 VALUES LESS THAN (2900000000) ENGINE = InnoDB,
-> PARTITION p_7 VALUES LESS THAN (3000000000) ENGINE = InnoDB,
-> PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 19386611 rows affected (22 min 49.71 sec)
Records: 19386611 Duplicates: 0 Warnings: 0

特别说明：

a) mysql从5.5开始，key分区方式使用的哈希函数算法发生了改变，如果分区表中使用了key分区，则必须在升级到5.5后需手动执行ALTER TABLE ... PARTITION BY ALGORITHM=1 [LINEAR] KEY，以兼容mysql5.1。ALGORITHM=1表示使用5.1的哈希算法，ALGORITHM=2表示使用的是新的哈希算法，从mysql5.5开始，如果使用key分区，在没有明确指明哈希算法时，默认使用新哈希算法，即ALGORITHM=2。

b) 如果需要从mysql5.5或之后的版本降级为mysql5.1，则需要使用旧的哈希算法（即ALGORITHM=1）重建分区，否则不兼容。

3）mysql启动时报错：usr/local/mysql/bin/mysqld: error while loading shared libraries:libaio.so.1: cannot open shared object file: No such file or directory

原因：系统缺少共享库libaio.so.1

解决方法：把缺少的库安装上

apt-get install libaio-dev

升级后续工作

1.升级备份工具、监控程序等

附:

MySQL版本下载链接MySQL5.1http://downloads.mysql.com/archives/get/file/mysql-5.1.57-linux-i686-glibc23.tar.gz

MySQL5.5http://mysql.com/Downloads/MySQL-5.5/mysql-5.5.42-linux2.6-x86_64.tar.gzhttp://cdn.mysql.com/Downloads/MySQL-5.5/mysql-5.5.42-linux2.6-i686.tar.gz

MySQL5.6http://mysql.com/Downloads/MySQL-5.6/mysql-5.6.23-linux-glibc2.5-i686.tar.gzhttp://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz

MySQL5.0以后的各历史归档版本http://downloads.mysql.com/archives/community/

升级前准备

升级SLAVE到MySQL 5.5.42

升级MASTER到MySQL 5.5.42

异常情况处理

升级后续工作

更多相关文章

随机推荐