关于mysql5.1升级为mysql5.7.25的那些事儿

分类：Mysql |

  1. 当使用source大量导入数据时报错, 数据库的 /opt/mysql/bin/mysqld  进程被直接关闭并重新启动一个新进程, 而且没有error报错, 导致导入失败, 
    解决办法: 这里的原因是 innodb_buffer_pool_size = 6G 设置成了 6G, 实际总内存也只有6G, 减少为500M后, 问题得以解决, 请根据实际情况自行配置

image.png



2. error 日志警告: 

[Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key

解决方法:

my.cnf 里的[mysqld]下添加 skip-ssl


3.  关于sql_mode

mysql> select @@global.sql_mode;

+-------------------------------------------------------------------------------------------------------------------------------------------+

| @@global.sql_mode                                                                                                                         |

+-------------------------------------------------------------------------------------------------------------------------------------------+

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

+-------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec) 

二、sql_mode值的含义

ONLY_FULL_GROUP_BY

对于GROUP BY聚合操作如果在SELECT中的列没有在GROUP BY中出现那么将认为这个SQL是不合法的因为列不在GROUP BY从句中



STRICT_TRANS_TABLES

在该模式下如果一个值不能插入到一个事务表中则中断当前的操作对非事务表不做任何限制



NO_ZERO_IN_DATE

在严格模式不接受月或日部分为0的日期。如果使用IGNORE选项我们为类似的日期插入'0000-00-00'。在非严格模式可以接受该日期但会生成警告。



NO_ZERO_DATE

在严格模式不要将 '0000-00-00'做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式可以接受该日期但会生成警告



ERROR_FOR_DIVISION_BY_ZERO

在严格模式在INSERT或UPDATE过程中如果被零除(或MOD(X0))则产生错误(否则为警告)。如果未给出该模式被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中MySQL生成被零除警告但操作结果为NULL。



NO_AUTO_CREATE_USER

防止GRANT自动创建新用户除非还指定了密码。



NO_ENGINE_SUBSTITUTION

如果需要的存储引擎被禁用或未编译那么抛出错误。不设置此值时用默认的存储引擎替代并抛出一个异常


注: 如果是集群部署, 两边是复制的mysql, 那么注意 auto.cnf 值 不然会报如下错误: 


Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.   


将两台服务器的auto.cnf文件重命名为auto.cnf.bak然后重启MySQL服务即可, 会自动生成一个auto.cnf 值



升级正式开始

最初使用xtrabackup 进行还原升级, 结果报了太多了如下错误

Table rebuild required. Please do "ALTER TABLE ` FORCE" or dump/reload


最终还是选择了使用 mysqldum 进行备份和升级

总体思路过程流水如下: 

1. 导出所有表记录: 


mysqldump -R -A -x -q --master-data=2 --default-character-set='utf8' --events --ignore-table=mysql.events  >all.sql


mysqldump -R -A -q --master-data=2 --default-character-set='utf8' --events --ignore-table=mysql.events  >all.sql


锁表整库备份:

mysqldump -R -A -x -q --master-data=2 --default-character-set='utf8' >all.sql

不锁表整库备份:

mysqldump -R -A -q --master-data=2 --default-character-set='utf8' >all.sql


2. 在新的server上导入数据库

关闭数据库服务: service mysqld stop

首先清空mysql5.7 表的data 里的数据, 

在 /etc/my.cnf 里的 [mysqld] 下添加 skip-grant-tables


去除以下配置以方便快速导入:

log-bin=mysql-bin

#expire_logs_days = 60 # binlog 自动清理天数, 即60天之前的binlog 自动删除, 只保留最近60天数据

#log_slave_updates # 从库级联复制, 只有主从时 主->从->从 时才有用

log_slave_updates = 1

binlog_format=mixed 



3. service mysqld start 开启服务

mysql 登录, 使用 source  /srv/all.sql 进行数据导入

导入完毕

4. 使用 /opt/mysql/bin/mysql_upgrade 进行升级

5. 在 /etc/my.cnf 里的 [mysqld] 下去除 skip-grant-tables

还原相关配置:

log-bin=mysql-bin

#expire_logs_days = 60 # binlog 自动清理天数, 即60天之前的binlog 自动删除, 只保留最近60天数据

#log_slave_updates # 从库级联复制, 只有主从时 主->从->从 时才有用

log_slave_updates = 1

binlog_format=mixed #



6. service mysqld restart 


我的my.cnf配置文件:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It‘s a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
explicit_defaults_for_timestamp=1
skip-external-locking
log-error=/srv/logs/mysql/error.log
server_id=1
# 一般配置选项
basedir = /opt/mysql
datadir = /srv/db/mysql/data
port = 3306
socket = /tmp/mysql.sock
key_buffer_size = 384M

default_storage_engine=INNODB
collation_server=utf8_general_ci
character_set_server = utf8

max_connections = 3000
#max_connect_errors = 50 # 默认 100
table_open_cache = 4096
max_allowed_packet = 1024M
#binlog_cache_size = 4M
read_rnd_buffer_size = 16M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 16
# query_cache_type = 1 # 从库建议开启, 以加速, 因为可能导致锁问题, 主库不建议开启
query_cache_size = 128M
query_cache_limit = 4M
ft_min_word_len = 8
thread_stack = 512K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 1024M
max_heap_table_size = 1024M
log-bin=mysql-bin
# expire_logs_days = 60 # binlog 自动清理天数, 即60天之前的binlog 自动删除, 只保留最近60天数据
# log_slave_updates # 从库级联复制, 只有主从时 主->从->从 时才有用
log_slave_updates = 1
binlog_format=mixed # 可选模式 ROW

#慢查询sql日志设置
#slow_query_log = 1
#long_query_time = 6
#slow_query_log_file = slow.log
#检查未使用到索引的sql
# log_queries_not_using_indexes = 1
slow_query_log = 1
long_query_time = 10
slow_query_log_file=/srv/logs/mysql/slow-sql.log
log_timestamps=SYSTEM 
log-queries-not-using-indexes = on

#针对log_queries_not_using_indexes开启后记录慢sql的频次、每分钟记录的条数
# log_throttle_queries_not_using_indexes = 5
#innodb_buffer_pool_size = 6G    # 生产环境可以调为更大的值, 以提升读取效率
innodb_buffer_pool_size = 500M
#innodb_thread_concurrency = 32 #innodb_thread_concurrency默认是0则表示没有并发线程数限制所有请求都会直接请求线程执行。注意当 innodb_thread_concurrency 设置为0时则innodb_thread_sleep_delay的设置将会被忽略不起作用。如果数据库没出现性能问题时使用默认值即可。
innodb_log_buffer_size = 16M
#innodb_log_file_size = 1024M
innodb_log_file_size = 500M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_file_per_table = 1
innodb_print_all_deadlocks = 1 # 强制将死锁语句写入err log 中, 以方便压测监控查看
#innodb_flush_log_at_trx_commit=0 # 导入时可设为 0 加快速度
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout = 300  # 锁等待时间 300s
sync_binlog=0                   # 该值默认为0采用操作系统机制进行缓冲数据同步
skip-ssl
# other
lower_case_table_names = 1  # 是否对sql语句大小写敏感1表示不敏感  默认: lower_case_table_names | 0
# 临时目录 比如load data infile会用到, 默认 /tmp, 如果你系统盘容量不大, 请更改, 如果系统盘不是ssd 请更改
#tmpdir  = /srv/db/mysql/tmp
#在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中
#官方建议back_log = 50 + (max_connections / 5),封顶数为900
back_log = 300
secure_file_priv=''
[mysqldump]
quick
max_allowed_packet = 1024M
[mysql]
no-auto-rehash
default-character-set=utf8
safe-updates
[myisamchk]
key_buffer = 16M
sort_buffer_size = 16M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192


其它测试: 

  1. mysql5.1 与mysql5.7 无法使用主从同步, 现象: 配置完成主从后, 提示成功, 但并不会进行数据同步, 可能是版本的主从机制变动导致的

  2. innodb_buffer_pool_size = 500M 正式环境因数据量较大, 设置为500M时导致sql查询较慢的情况,  正式环境 16G 性能提升1倍左右, 一般建议设置为内存的 80% , 也不易过大, 太大会导致mysql执行报错, 
    可通过命令:  show status like 'innodb_buffer_pool%'; 命令查看 Innodb_buffer_pool_wait_free 值是否大于0, 如果大于0说明,需要添加内存,并提高此参数数值了


    image.png