小程序开发方案优化大师有必要安装吗
说明
一台已有数据的机器，停机升级，从MySQL Server5.6.48，升级到 5.7.38。
环境介绍
10.24.10.247，Mysql 5.6.48
CentOS Linux release 7.9.2009 (Core)
32G内存、500G数据盘/home；
实际数据量约120M，2个logfile共8G；
basedir = /home/mysql/mysql-5.6.33
datadir = /home/mysql/mysql-5.6.33/data
升级至 5.7.38
MySQL数据与软件分离：
basedir = /usr/local/mysql
datadir = /home/mysql/data
mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
操作流程
以下步骤如无特殊说明，均使用root用户操作。
- 事前检查：两个节点上，操作系统版本、位数；防火墙、selinux；磁盘容量；
- 记录与备份信息：MYSQL软件与数据路径；my.cnf，/etc/init.d/mysqld，etc/profile
- 获取并测试my.cnf，确认不兼容参数；确认这台机器上是否还有其他的应用或者服务，是否受升级影响；
- 逻辑与物理备份，停止MYSQL 5.6服务；
- 准备配置文件；
- 安装MYSQL5.7；
- 将5.6/data复制回来5.7，升级；
- 检查进程与状态。
实施步骤
事前检查
- 两个节点上，操作系统版本、位数；
- 防火墙、selinux；
- 磁盘容量；
- 记录与备份信息；
操作系统版本、位数：
[root@CentOS79 ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
[root@CentOS79 ~]# getconf LONG_BIT
64
关闭防火墙、selinux
[root@CentOS79 ~]# setenforce 0
[root@CentOS79 ~]# vi /etc/sysconfig/selinux
SELINUX=disabled
[root@CentOS79 ~]# systemctl stop firewalld
[root@CentOS79 ~]# systemctl disable firewalld
查看磁盘容量
[root@CentOS79 ~]# df -h
文件系统 容量 已用 可用 已用% 挂载点
devtmpfs 16G 0 16G 0% /dev
tmpfs 16G 200K 16G 1% /dev/shm
tmpfs 16G 1.6G 15G 10% /run
tmpfs 16G 0 16G 0% /sys/fs/cgroup
/dev/mapper/centos-root 450G 1.1G 449G 1% /
/dev/mapper/centos-usr 50G 8.1G 42G 17% /usr
/dev/sda1 1014M 232M 783M 23% /boot
/dev/sda2 300M 7.9M 292M 3% /boot/efi
/dev/mapper/centos-home 465G 14G 452G 3% /home
/dev/mapper/centos-var 10G 907M 9.2G 9% /var
tmpfs 3.2G 12K 3.2G 1% /run/user/42
tmpfs 3.2G 0 3.2G 0% /run/user/0记录与备份信息
MYSQL软件与数据路径；
my.cnf，/etc/init.d/mysqld，etc/profile
basedir = /home/mysql/mysql-5.6.33
datadir = /home/mysql/mysql-5.6.33/data
获取并测试my.cnf，确认不兼容参数；
mysql5.6 /etc/my.cnf
相关参数调整见后续步骤。
[client]
port = 3306
socket = /home/mysql/mysql-5.6.33/mysql.sock[mysql]
prompt="\u@\h \R:\m:\s [\d]> "
socket = /home/mysql/mysql-5.6.33/mysql.sock[mysqld]
########basic settings########
server_id = 1
port = 3306
basedir = /home/mysql/mysql-5.6.33
datadir = /home/mysql/mysql-5.6.33/data
socket = /home/mysql/mysql-5.6.33/mysql.sock
pid_file = /home/mysql/mysql-5.6.33/mysql.pid
autocommit = 1
character_set_server=utf8mb4
skip_name_resolve
max_connections = 2000
max_connect_errors = 2000
lower_case_table_names = 1
back_log = 200
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 134217728
tmp_table_size = 67108864
tmpdir = /home/mysql/mysql-5.6.33/tmp
max_allowed_packet = 16M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER"
interactive_timeout = 28800
wait_timeout = 28800
max_user_connections = 0
skip-external-locking
read_buffer_size = 64M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
#########log settings########
log_output = FILE
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 15
long_query_time = 2
min_examined_row_limit = 100
#########replication settings########
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
log_bin = master_bin
#binlog-ignore-db=mysql
#sync_binlog = 1
#gtid_mode = on
#enforce_gtid_consistency = 1
#log_slave_updates
binlog_format = row
#relay_log = relay3306.log
#relay_log_recovery = 1
#binlog_gtid_simple_recovery = 1
#slave_skip_errors = ddl_exist_errors#########innodb settings########
#按服务器实际内存大小调整
innodb_buffer_pool_size = 8G
#innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 32M
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 = 600
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_log_group_home_dir = /home/mysql/mysql-5.6.33/data/
innodb_undo_directory = /home/mysql/mysql-5.6.33/data/
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 4G
innodb_log_buffer_size = 16M
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
innodb_max_dirty_pages_pct = 75
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
逻辑与物理备份，停止MYSQL 5.6服务
逻辑与物理备份:
mysqldump -u root -p -S /home/mysql/mysql-5.6.33/mysql.sock --add-drop-table --routines --events --all-databases --force > /home/mysql/bc/mysql56.sql
mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"
停止MYSQL 5.6服务:
systemctl stop mysqld
systemctl disable mysqld
mv /etc/init.d/mysqld /home/bc/
4.4准备配置文件
/etc/my.cnf
修改
basedir =/usr/local/mysql 和其他dir;
log_error = /home/mysql/error.log
slow_query_log_file = /home/mysql/slow.log
注释：
pid_file = /home/mysql/mysql.pid
tmpdir = /home/mysql/tmp
innodb_additional_mem_pool_size = 32M：
4.5安装MYSQL5.7
创建数据目录
mkdir -p /home/mysql/data
chown -R mysql:mysql /home/mysql
chmod 750 /home/mysql
tar -zxvf /software/mysql-5.7.38-linux-glibc2.12-x86_64 -C /usr/local
cd /usr/local
ln -s mysql-5.7.38-linux-glibc2.12-x86_64 mysql
chown -R mysql:mysql mysql
chmod -R 750 mysql
安装MYSQL：
cd /usr/local/mysql
./bin/mysqld --initialize --user=mysql
记录error.log中的初始密码；
添加路径：
vi /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
source /etc/profile
启动MYSQL:
bin/mysqld_safe --user=mysql &
确认配置文件中存在以下参数：
[mysqld]
federated
skip-external-locking
skip-name-resolve
修改root密码：
set password for root@localhost = password('A_isino#888');
flush privileges;
shutdown;
4.6将5.6/data复制回来5.7，升级
cp -rp /home/bc/mysql-5.6.33/data/* /home/mysql/data/
删除/home/mysql/data/中的error.log\slow.log；
后台启动MYSQL
cd /usr/local/mysql
nohup ./bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql >/dev/null 2>&1 &
./bin/mysql_upgrade -u root -p -S /home/mysql/mysql.sock