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 <业务database>;
GRANT SELECT,INSERT,UPDATE,DELETE ON <业务database>.* 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('<dbname>');
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 <业务database>.* TO 'deployop'@'%' IDENTIFIED BY '&password';
flush privileges;
8.数据同步工具用户
Grant create ,drop,alter,select,insert, update, delete on <业务database>.* 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 STATUSG
--设置从库为只读
11、set global read_only = on