5.5.x升级5.7.21步骤

5.5.x升级5.7.21步骤

环境准备

上传软件包

根据官方文档说明

Upgrading to the latest release is recommended before upgrading to the next version

当前版本5.5.46,需要上传三个包

1
2
3
mysql-5.5.59-linux-glibc2.12-x86_64.tar.gz
mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz
mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz

创建目录

1
mkdir /usr/local/{mysql-5.5.59,mysql-5.6.39,mysql-5.7.21}

解压

1
2
3
tar -zxvf mysql-5.5.59-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql-5.5.59/
tar -zxvf mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql-5.6.39/
tar -zxvf mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql-5.7.21/

设置环境变量

1
2
3
4
5
6
7
8
9
10
11
12
zst_ps1()
{
Date=$(date +%F)
Time=$(date +%H:%M:%S)
PS1="\\n\[\e[1;37m[\e[m\]\[\e[1;33m\u\e[m\]\[\e[1;33m@\h\e[m\]\[\e[1;35m $Time \e[m\]\e[1;36m\w\e[m\e[1;37m]\e[m\n\\$"
}
PROMPT_COMMAND=zst_ps1
export PATH=/usr/local/mysql/bin:$PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/mysql/lib

停复制,记录位置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
[root@iZ23pn0u8g5Z tmp]# /data/bin/login_db.sh
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9500936
Server version: 5.5.46-log MySQL Community Server (GPL) by Remi
Copyright (c) 2000, 2015, 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.
root@localhost 14:53: [dbe8je6i4c3gjd50]> stop slave;
Query OK, 0 rows affected (0.03 sec)
root@localhost 14:53: [dbe8je6i4c3gjd50]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.31.124.23
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.007611
Read_Master_Log_Pos: 669126117
Relay_Log_File: mysqld-relay-bin.021406
Relay_Log_Pos: 669125856
Relay_Master_Log_File: mysql-bin.007611
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%,union_log%.%,test.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 669125710
Relay_Log_Space: 669124929
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: NULL
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: 32
1 row in set (0.00 sec)

关库

1
2
[root@iZ23pn0u8g5Z tmp]# /usr/bin/mysql --default-character-set=utf8 --socket=/data/mysql/mysql.sock -uroot -p"password" -e"set global innodb_fast_shutdown=0"
[root@iZ23pn0u8g5Z tmp]# /data/bin/stop_db.sh

创建软连接

1
2
3
ln -s /usr/local/mysql-5.5.59/mysql-5.5.59-linux-glibc2.12-x86_64/ /usr/local/mysql
chown mysql:mysql -R /usr/local/mysql-5.5.59/
chown mysql:mysql -R /usr/local/mysql

my.cnf添加skip_slave_start,sql_mode=””

否则启动数据库后会自动开始同步

sql_mode=””是为了避免下一步出现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
dbe8je6i4c3gjd50.admin
error : Table rebuild required. Please do "ALTER TABLE `admin` FORCE" or dump/reload to fix it!
CREATE TABLE `admin` (
`userid` int(11) NOT NULL DEFAULT '0',
`username` varchar(200) NOT NULL COMMENT '管理员用户名',
`password` varchar(32) NOT NULL COMMENT '密码',
`realname` varchar(200) NOT NULL COMMENT '管理员姓名',
`createtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态 0 封禁 1正常',
`myprivate` text NOT NULL COMMENT '权限',
`mygame` text NOT NULL COMMENT '游戏权限',
`myunion` text NOT NULL COMMENT '推广查看权限',
`department_id` int(11) NOT NULL DEFAULT '0' COMMENT '部门id',
`job_number` int(11) NOT NULL DEFAULT '0' COMMENT '呼叫中心的工号',
`yesorno` varchar(200) NOT NULL DEFAULT '0' COMMENT '是否有xxx权限',
`myrows` text NOT NULL COMMENT '列权限',
`email` varchar(200) NOT NULL COMMENT '邮箱',
`bkemail` varchar(200) NOT NULL COMMENT '备用邮箱',
`leaderid` int(11) NOT NULL DEFAULT '0' COMMENT '上级ID',
`myaccount` text NOT NULL COMMENT '账号权限',
`img` varchar(1000) DEFAULT NULL COMMENT '头像',
`nickname` varchar(200) DEFAULT NULL COMMENT '昵称',
KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='管理员表'
这是由于createtime列default值为'0000-00-00 00:00:00' 违反了默认的sql_mode

启动数据库,执行mysql_upgrade

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
source ~/.bash_profile
/data/bin/start_db.sh
mysql_upgrade --socket=/data/mysql/mysql.sock -uroot -p"password"
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info 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
Upgrading the sys schema.
Checking databases.
dbe8je6i4c3gjd50.adlist OK
dbe8je6i4c3gjd50.adlist_bk OK
dbe8je6i4c3gjd50.admin
error : Table rebuild required. Please do "ALTER TABLE `admin` FORCE" or dump/reload to fix it!
升级时留意有没有error

关闭数据库

1
2
3
4
mysql --default-character-set=utf8 --socket=/data/mysql/mysql.sock -uroot -p"password" -e"set global innodb_fast_shutdown=0"
/data/bin/stop_db.sh
删除软连接
rm /usr/local/mysql

重复之前的步骤继续升级5.6.39

升级只5.7.21

修改配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
[client]
user=root
password=
socket=/data/mysql/mysql.sock
[mysqld]
########basic settings########
server-id = 853306
port = 3306
autocommit = 1
transaction_isolation = REPEATABLE-READ
character_set_server=utf8
skip_name_resolve = 1
secure_file_priv=/var/lib/mysql-files/
#连接
max_connections = 800
max_connect_errors = 1000
back_log=512
socket=/data/mysql/mysql.sock
pid-file=/data/run/mysqld.pid
basedir=/usr/local/mysql
datadir = /data/mysql
tmpdir = /data/tmpdir
explicit_defaults_for_timestamp = 1
#mem
thread_stack = 512K
thread_cache_size = 1024
table_open_cache = 1024
table_definition_cache = 1024
join_buffer_size = 134217728
tmp_table_size = 67108864
read_buffer_size = 16777216
read_rnd_buffer_size = 33554432
sort_buffer_size = 33554432
tmp_table_size = 32M
max_heap_table_size = 32M
#bulk_insert_buffer_size = 64M 默认8M
#myisam_sort_buffer_size = 128M 默认8M
max_prepared_stmt_count=50000
query_cache_size = 0
query_cache_type = 0
query_cache_limit =0
max_allowed_packet = 128M
#sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
#默认就是ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION
sql_mode=""
interactive_timeout = 1800
wait_timeout = 1800
#optimizer_switch="index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on"
auto_increment_offset = 1
auto_increment_increment = 2
########log settings########
log_output=FILE
log_error = /data/mysql/error.log
slow_query_log = 1
slow_query_log_file = /data/mysql/slow-queries.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
long_query_time = 0.8
min_examined_row_limit = 100
general_log=0
general_log_file=/data/mysql/general_query.log
expire_logs_days=7
########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = mysql-bin
sync_binlog = 10
innodb_flush_log_at_trx_commit = 2
#gtid_mode = on
#enforce_gtid_consistency = 1
log_slave_updates=1
binlog_format = row
relay_log = mysqld-relay-bin
relay_log_recovery = 1
#启用这个参数需要repository=TABLE,官方文档没有明确要求,老吴这么说应该是为了一致性吧
#Enabling the --relay-log-recovery option when relay-log-purge is disabled risks reading the relay log from files that were not purged, leading to data inconsistency.
relay_log_purge=1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=8
slave_preserve_commit_order = 1
skip_slave_start=1
#ZST
#1M - 2M
binlog_cache_size = 2M
#max_binlog_size #默认1G, 建议生成间隔2分钟以上 推荐128M 或 256M 这样用mysqlbinlog解析更快些
log_bin_trust_function_creators=1
#开启binlog时,是否允许创建存储过程(除非有super权限,且指定deterministic, reads sql data,no sql)
#max_binlog_cache_size #binlog最大的cache size,有大SQL写入时需要用到,或者大数据LOAD DATA时. 默认1G吧,基本不用改
#binlog_stmt_cache_size #大量prepare statement时.加大
#binglog_direct_non_transactional_update
#relay_log_purge=1 #relay log使用完就删掉
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=performance_schema.%
replicate-wild-ignore-table=union_log%.%
replicate-wild-ignore-table=test.%
########innodb settings########
#innodb_page_size = 8192
innodb_buffer_pool_size = 45G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
#此值= innodb_io_capacity/innodb_buffer_pool_instances
innodb_lru_scan_depth = 1000
innodb_lock_wait_timeout = 5
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话,可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话,则可以调整的更高,比如 50000 - 80000
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_log_files_in_group = 3
innodb_log_group_home_dir = /data/mysql/
innodb_undo_directory = /data/mysql/
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 1G
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 128
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_data_file_path=ibdata1:1024M:autoextend
########semi sync replication settings########
#plugin_dir=/usr/local/mysql/lib/plugin
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#loose_rpl_semi_sync_master_enabled = 1
#loose_rpl_semi_sync_slave_enabled = 1
#loose_rpl_semi_sync_master_timeout = 5000
#######performance schema#######
performance_schema = 1
performance_schema_instrument='memory/%=COUNTED'
performance_schema_digests_size = 40000
performance_schema_max_table_instances = 40000
performance_schema_max_sql_text_length = 4096
performance_schema_max_digest_length = 4096
#innodb monitor
innodb_monitor_enable=module_innodb,module_dml,module_ddl,module_trx,module_os,module_purge,module_log,module_lock,module_buffer,module_index,module_ibuf_system,module_buffer_page,module_adaptive_hash
[mysqld-5.7]
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 8
#innodb_undo_log_truncate = 1
#innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
#transaction_write_set_extraction=MURMUR32
show_compatibility_56=on
[mysql]
no-auto-rehash
prompt="\u@\h \R:\m:\s [\d]> "

创建文件夹

1
2
mkdir -p /var/lib/mysql-files/
chown mysql:mysql /var/lib/mysql-files

创建软连接

1
2
3
ln -s /usr/local/mysql-5.7.21/mysql-5.7.21-linux-glibc2.12-x86_64/ /usr/local/mysql
chown mysql:mysql -R /usr/local/mysql-5.7.21/
chown mysql:mysql -R /usr/local/mysql

mysql_upgrade

1
mysql_upgrade -uroot -pmysql -S /data1/mysqldata/3306/mysql.sock

5.7.21由于一些数据类型存储范式有改变比如

TIME3 bytes3 bytes + fractional seconds storage

DATETIME8 bytes5 bytes + fractional seconds storage

TIMESTAMP4 bytes4 bytes + fractional seconds storage

需要重新建标,所以可能会很慢

可以使用--upgrade-system-tables只升级数据字典,根据pecona的文章也不影响DML和复制

https://www.percona.com/blog/2016/04/27/upgrading-to-mysql-5-7-focusing-on-temporal-types/

需要注意的参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
下面三个参数没有了
table_cache
thread_concurrency
key_buffer
innodb_additional_mem_pool_size
独立undo必须是MySQL5.6.3引入的,需要初始化数据库时就启用,由于这里是5.5升级至5.7所以也没法用了
innodb_undo_tablespaces
innodb_undo_directory
innodb_undo_log_truncate
innodb_max_undo_log_size
这个是由于线上主库还是基于position的,如果开了就没法建立同步了
gtid_mode = off
enforce_gtid_consistency = 0

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2018 Fan() All Rights Reserved.

访客数 : | 访问量 :

#