verify that the file mysql 5.3_转 mysql 升级事宜

1.配置免密登陆

/USER/USER/mysql/app/bin/mysql_config_editor set --login-path=root --user=root --password --socket=/USER/USER/mysql/data/mysql.sock --port=3306

-----------------------------------------

2.新建业务database和user,不要加字符集参数，用系统默认字符集

--默认授业务database的增删改查权限，如果有其他权限需求，沟通后按需授予，禁止授予all privileges权限

create database ;

GRANT SELECT,INSERT,UPDATE,DELETE ON .* TO 'xxopr'@'%' IDENTIFIED BY 'xxxxxx';

flush privileges;

/* 例如：

create database USER_base;

create database USER_budget;

create user 'USERopr'@'%' identified by 'USERopr$8';

grant create,drop,alter,delete, index,insert,select,update,create view,show view on USER_base.* to 'USERopr'@'%';

grant create,drop,alter,delete, index,insert,select,update,create view,show view on USER_budget.* to 'USERopr'@'%';

flush privileges;

*/

-----------------------------------------------------

3.配置监控

mysql添加监控前数据库准备工作，然后把dbuser1opr用户名跟密码发给监控

create database dbuser1;

use dbuser1

create table user1_conn (dbname char(32));

insert into user1_conn values('');

grant select on dbuser1.user1_conn to dbuser1opr@'%' identified by 'Dbuser1oprUSER11$';

GRANT SELECT ON *.* TO 'dbuser1opr'@'localhost' IDENTIFIED BY 'Dbuser1oprUSER11$';

flush privileges;

#########连接测试

remote:

mysql -h 10.198.227.29 -udbuser1opr -p

local:

mysql -udbuser1opr -p --socket=/USER/mysql/data/mysqltmp/mysql.sock

-----------------------------------------

4.配置dbmgr 用户

grant all on *.* to 'dbmgr'@'%' identified by 'Yshs3s4jx!ABD';

5.配置appuser1用户，用于应用运维同事日常查询业务数据

grant select,SHOW VIEW on *.* to 'appuser1'@'10.200.%' IDENTIFIED BY 'Appuser1123$';

flush privileges;

6.配置数据库slave同步用户

GRANT REPLICATION SLAVE ON *.* TO 'resync'@'%' IDENTIFIED BY 'Resync$123';

flush privileges;

7.配置数据库版本部署用户

GRANT ALL PRIVILEGES ON .* TO 'deployop'@'%' IDENTIFIED BY '&password';

flush privileges;

8.数据同步工具用户

Grant create ,drop,alter,select,insert, update, delete on .* to ‘xxetl’@'%' IDENTIFIED BY '&password';

flush privileges;

Hi all，mysql的配置文件my.cnf修改了。增加了字符集的参数(oracle技术支持给的字符集参数)，原来字符集是utf8，oracle推荐utf8mb4，是utf8编码的超集，兼容utf8。

[client]

port=3306

socket=/USER/USER/mysql/data/mysql.sock

default-character-set=utf8mb4

[mysqld]

########basic settings########

port = 3306

server_id = 180437194

socket=/USER/USER/mysql/data/mysql.sock

pid-file=/USER/USER/mysql/data/mysql.pid

basedir=/USER/USER/mysql/app

datadir=/USER/USER/mysql/data

tmpdir=/USER/USER/mysql/data/tmp

character_set_server= UTF8MB4

collation-server = utf8mb4_unicode_ci

init_connect='SET NAMES utf8mb4'

skip-character-set-client-handshake = true

autocommit = 1

transaction_isolation = READ-COMMITTED

explicit_defaults_for_timestamp = 1

skip-name-resolve

log_timestamps=SYSTEM

########cache and limit########

max_connections = 2000

max_connect_errors = 1000

interactive_timeout = 1800

wait_timeout = 1800

read_buffer_size = 8M

read_rnd_buffer_size = 8M

lock_wait_timeout = 50

tmp_table_size = 8M

table_open_cache = 2000

table_definition_cache = 1400

sort_buffer_size = 8M

join_buffer_size = 8M

max_allowed_packet = 32M

query_cache_type = 1

query_cache_size = 128M

net_buffer_length = 1M

#########log settings########

log_error = error.log

slow_query_log = 1

slow_query_log_file = slow.log

log_queries_not_using_indexes = on

log_slow_admin_statements = 1

log_slow_slave_statements = 1

log_throttle_queries_not_using_indexes = 10

expire_logs_days = 7

long_query_time = 1

min_examined_row_limit = 100

log_bin_trust_function_creators = 1

#########replication settings########

master_info_repository = TABLE

relay_log_info_repository = TABLE

log_bin = mysql-bin

sync_binlog = 1

gtid_mode = on

enforce_gtid_consistency = 1

log_slave_updates

lower_case_table_names = 1

binlog_format = row

relay_log = relay.log

binlog_gtid_simple_recovery = 1

slave_skip_errors = ddl_exist_errors

relay_log_recovery = ON

binlog-checksum = CRC32

master-verify-checksum = 1

slave-sql-verify-checksum = 1

#########safety########

skip_name_resolve = 1

local_infile = OFF

sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

plugin-load = validate_password.so

#########innodb settings########

innodb_buffer_pool_size = 16G

innodb_buffer_pool_instances = 8

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_lru_scan_depth = 2000

innodb_lock_wait_timeout = 5

innodb_io_capacity = 640

innodb_io_capacity_max = 8000

innodb_flush_method = O_DIRECT

innodb_undo_logs = 128

innodb_undo_tablespaces = 0

innodb_flush_log_at_trx_commit = 1

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

innodb_print_all_deadlocks = 1

innodb_strict_mode = 1

innodb_sort_buffer_size = 67108864

[mysql]

######### client ########

max_allowed_packet = 32M

socket=/USER/USER/mysql/data/mysql.sock

default-character-set = utf8mb4

[mysqldump]

quick

max_allowed_packet = 32M

#########sample 3

又发现个问题。

5.7的参数文件中，如果没有设置character_set_server，默认是latin1，所以我们在升级后，要注意字符集问题，要设置为character_set_server = utf8。

另外还有个参数log_timestamps=SYSTEM，如果不设置，error.log文件中的时间不对。

###sample 4

源库mysql 5.6.25，要升级到5.7.29。按上一封邮件的方法做，我在一台服务器上直接安装了5.7.29的软件，从5.6.25版本用mysqlbackup备份一份数据过来，恢复到5.7.29软件下面，用5.7.29的软件起库，库起不来，报了下面的错误：

2020-08-06T03:35:35.808995Z 0 [ERROR] InnoDB: Upgrade after a crash is not supported. This redo log was created before MySQL 5.7.9, and it appears corrupted. Please follow the instructions at http://dev.mysql.com/doc/refman/5.7/en/upgrading.html

2020-08-06T03:35:35.809013Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error

2020-08-06T03:35:36.409519Z 0 [ERROR] Plugin 'InnoDB' init function returned error.

2020-08-06T03:35:36.409533Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

2020-08-06T03:35:36.409539Z 0 [ERROR] Failed to initialize builtin plugins.

2020-08-06T03:35:36.409545Z 0 [ERROR] Aborting

报错原因：

CHANGES

MySQL Server was upgraded from MySQL 5.6 or earlier to MySQL 5.7 with one of the following:

•The data directory was created with MySQL Enterprise Backup (MEB) based on a backup created with MySQL 5.6 or earlier (or MySQL 5.7.8 or earlier).

•MySQL was shutdown with innodb_fast_shutdown = 2 prior to the upgrade.

•MySQL did not go through a clean shutdown prior to the upgrade (e.g. a crash).

CAUSE

The redo log format has changed during the development of MySQL 5.7. This means that it is not possible to apply the redo log records created with an earlier version of MySQL with a mysqldbinary version 5.7.9 or later.

SOLUTION

In order to complete the upgrade perform the following steps:

1.Use the MySQL binaries from the version of MySQL the backup or last shutdown was performed with.

2.Start MySQL with the binaries from 1.

3.Verify the recovery was completed without errors.

4.Shut down MySQL

5.Upgrade to MySQL 5.7.

解决方法：

不要直接把备份恢复到5.7.29版本，先装一个5.6.25的软件，用5.6.25软件先启动数据库，配好slave，同步没有问题后，停掉同步，shutdown数据库。

再用5.7.29软件去启动数据库，这时可以正常起库了。再执行upgrade升级，升级完成后，继续执行上一封邮件接下来的步骤。

########sample 6

Mysql主从同步支持slave端版本比master版本高。所以我们在升级mysql的时候可以先搭建高版本的从库，配置好同步关系，然后在维护窗口把库切到高版本的从库上完成升级(或者先升级从库)。

补充了mysql主从搭建手册，如下：

1、在MySQL的生产从库进行mysqlbackup的全备

mysqlbackup -uroot -p --socket=/paic/my$port/var/mysql.sock --backup-dir=$mybackdir_full --backup-image=$mybackdir_full/mybackup.mbi --compress backup-to-image

ps：在187的/dbsoft/mysql下面有两个版本的mysqlbackup，5.6版本拷贝mysqlbackup-5.6，5.7版本拷贝mysqlbackup-5.7

2、将备份文件拷贝到目标主机

3、在目标库进行恢复

mysqlbackup --defaults-file=$mybackdir_full/backup-my.cnf --datadir=/paic/my$port/data --backup_image=$mybackdir_full/mybackup.mbi --backup_dir=$mybackdir_full copy-back-and-apply-log --uncompress

4、调整my.cnf文件中的server_id参数

server-id只需采用ip地址的整数形式如:

select INET_ATON('10.193.64.208')，server_id=3232238637

5、启动数据库

cd /paic/mysql/base

nohup ./bin/mysqld_safe --defaults-file=/paic/my${port}/my.cnf &

---可以把auto.cnf文件先删除再启动数据库，否则可能报uuid冲突。

slave端软件版本高于源端，启动以后会出现以下报错

2020-07-15T15:50:43.142405+08:00 0 [ERROR] Incorrect definition of table performance_schema.replication_connection_status: expected column 'RECEIVED_TRANSACTION_SET' at position 7 to have type longtext, found type text.

2020-07-15T15:50:43.142702+08:00 0 [ERROR] Incorrect definition of table performance_schema.replication_group_member_stats: expected column 'COUNT_TRANSACTIONS_ROWS_VALIDATING' at position 6, found 'COUNT_TRANSACTIONS_VALIDATING'.

执行mysql升级脚本：

/USER/mysql/BankWallet/base/bin/mysql_upgrade -udbmgr -p --socket=/USER/mysql/BankWallet/3501/mysql.sock --port=3501

升级过程中可能出现以下报错：

mysql_upgrade: [ERROR] 1347: 'sys.version' is not VIEW

---删除sys：

mysql> drop database sys;

---再次执行upgrade命令：

/USER/mysql/BankWallet/base/bin/mysql_upgrade -udbmgr -p --socket=/USER/mysql/BankWallet/3501/mysql.sock --port=3501

Upgrade process completed successfully. ---升级成功后再继续执行下一步操作

Checking if update is needed.

6、为了接续复制需要重新设置gtid_purged，

运行$mybackdir_full/meta/backup_gtid_executed.sql重置gtid_purged

7、修改MASTER，MASTER_HOST需要设置为生产从库IP

CHANGE MASTER TO MASTER_HOST='*.*.*.*', MASTER_USER='dbsync',Master_Port=3311, MASTER_PASSWORD='******', MASTER_AUTO_POSITION=1;

8、RESET SLAVE

必须reset，否则会报错：ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

---启动从库同步

9、START SLAVE

---检查主从库同步状态

10、SHOW SLAVE STATUS\G

--设置从库为只读

11、set global read_only = on