5.5.x升级5.7.21步骤

2018-02-11 阅读量

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/

设置环境变量

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)

关库

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=””是为了避免下一步出现

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

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]> "

创建文件夹

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/

需要注意的参数

下面三个参数没有了
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