5.5.x升级5.7.21步骤
环境准备
上传软件包
根据官方文档说明
Upgrading to the latest release is recommended before upgrading to the next version
当前版本5.5.46,需要上传三个包
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
创建目录
mkdir /usr/local/{mysql-5.5.59,mysql-5.6.39,mysql-5.7.21}
解压
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/
设置环境变量
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
停复制,记录位置
[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)
关库
[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
创建软连接
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=”“是为了避免下一步出现
xxdb.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
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
关闭数据库
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
修改配置文件
[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]> "
创建文件夹
mkdir -p /var/lib/mysql-files/
chown mysql:mysql /var/lib/mysql-files
创建软连接
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
mysql_upgrade -uroot -pmysql -S /data1/mysqldata/3306/mysql.sock
5.7.21由于一些数据类型存储范式有改变比如
TIME
3 bytes3 bytes + fractional seconds storage
DATETIME
8 bytes5 bytes + fractional seconds storage
TIMESTAMP
4 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/
需要注意的参数
下面三个参数没有了
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