xtrabackup8 全备恢复Starting MySQL… ERROR! The server quit without updating PID file (/data01/mysql/tmp/mysql.pid).

#######################shell一键安装mysql8脚本


cat mysql8install.sh


#!/bin/bash
start_time=$(date +%s)
#yum -y install gcc gcc-c++ openssl openssl-devel libaio libaio-devel ncurses ncurses-devel >> /dev/null




# 软件安装 '根' 目录
installRootDir="/data01/mysql"

设定mysql的新密码
newpassword="wwwwww"


# 创建数据库文件目录和创建日志目录
mkdir -p $installRootDir/{data,log,tmp}


#创建mysql组和用户
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
chown -R mysql:mysql $installRootDir/
mkdir -p /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql
tar -xvf /data01/soft/mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz -C /usr/local/mysql --strip-components 1 >> /dev/null
chown -R mysql:mysql /usr/local/mysql $installRootDir/



#mysql server-id
serverid=$(($[$RANDOM%2+1]+$[$RANDOM%900+100]))

#配置/etc/my.cnf
cat > /etc/my.cnf <<EOF


[client]
port = 3306
socket = $installRootDir/tmp/mysql.sock

[mysql]
prompt="\u@db \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = $installRootDir/data
socket = $installRootDir/tmp/mysql.sock
pid-file = $installRootDir/tmp/mysql.pid
character-set-server=utf8mb4
collation-server = utf8mb4_general_ci

sql_mode='NO_UNSIGNED_SUBTRACTION,NO_ENGINE_SUBSTITUTION'

open_files_limit = 65535
innodb_open_files = 65535
back_log=1024
max_connections = 512
max_connect_errors=1000000
interactive_timeout=300
wait_timeout=300
max_allowed_packet = 1024M

secure_file_priv=''

log-error=$installRootDir/log/error.log

slow_query_log=ON
slow_query_log_file=$installRootDir/log/slow_mysql.log
long_query_time=2

innodb_flush_log_at_trx_commit=1
innodb_log_file_size =1G
innodb_log_files_in_group=3
innodb_log_group_home_dir=./

log-bin-trust-function-creators=1
sync_binlog = 1
binlog_cache_size = 16M
max_binlog_cache_size = 1G
max_binlog_size=1G
expire_logs_days = 30

log-bin= $installRootDir/log/binlog-mysql
binlog_format=row
binlog_row_image=full
server-id = $serverid
default_authentication_plugin =mysql_native_password

# 大小根据实际系统内存情况而定
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=2


EOF


#配置环境变量
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile


#初始化数据库
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql　--datadir=$installRootDir/data >> /dev/null


#制作启动文件
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
chmod 755 /etc/init.d/mysqld


#启动mysql服务
/etc/init.d/mysqld start


#关闭mysql服务　
#/etc/init.d/mysqld stop
echo "#####mysql8安装完成#####"


#修改mysql登录密码
b=`grep 'temporary password' $installRootDir/log/error.log`
a=`echo ${b##*localhost:}`
echo $a

#创建/mysql/bin/mysql软连接，便于命令行直接执行mysql
#ln -s /mysql/bin/mysql /usr/bin/mysql





#mysql -e　可以直接在命令行执行命令,wwwwww是设定的新密码
mysql -uroot -p"${a}" -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'wwwwww'" --connect-expired-password
echo "#####mysql8密码修改成功####### $newpassword "


end_time=$(date +%s)
cost_time=$((end_time - start_time))
echo "安装耗时————————> $cost_time 秒 "

#ps -ef|grep mysql|awk '{print $2}'|xargs kill -9;$(rm -rf /data01/mysql/;rm -rf /usr/local/mysql/; rm -rf /usr/local/mysqlrouter/;rm -rf /usr/local/mysqlshell;)
###############开启远程访问
#### mysql -uroot -pwwwwww -e "USE mysql;update user set host='%';FLUSH PRIVILEGES;"









#####################################重装mysql8 使用xtrabackup备份全库恢复#####################



ps -ef|grep mysql|awk '{print $2}'|xargs kill -9;$(rm -rf /data01/mysql/;rm -rf /usr/local/mysql/);
bash -x /data01/soft/mysql8install.sh
mysql -uroot -pwwwwww -e "USE mysql;update user set host='%';FLUSH PRIVILEGES;"

mysql -uroot -pwwwwww -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;create database db01;use db01;create table t_myisam(id int,name varchar(100),cdate datetime default now())engine=myisam;insert into t_myisam(id,name) values(1,'Alen'),(2,'MyISAM');"


rm -rf /data01/backup/full_backup_2023-03-28
xtrabackup --backup --host=127.0.0.1 --user=root --password=wwwwww --target-dir=/data01/backup/full_backup_2023-03-28

service mysqld stop
rm -rf /data01/mysql/data/*
rm -rf /data01/mysql/log/*
touch /data01/mysql/log/error.log

xtrabackup --prepare --target-dir=/data01/backup/full_backup_2023-03-28
xtrabackup --copy-back --target-dir=/data01/backup/full_backup_2023-03-28 --datadir=/data01/mysql/data
chown -R mysql:mysql /data01/mysql/ /usr/local/mysql







xtrabackup 全库恢复后启动如下错误 Starting MySQL.. ERROR! The server quit without updating PID file (/data01/mysql/tmp/mysql.pid).



Killed
[root@node1 soft]# ^C
[root@node1 soft]# tail -f /data01/mysql/log/error.log
2023-03-29T03:27:23.516191Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2023-03-29T03:27:23.516222Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.30) starting as process 69386
2023-03-29T03:27:23.525843Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=3221225472. Please use innodb_redo_log_capacity instead.
2023-03-29T03:27:23.529067Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-03-29T03:27:23.846346Z 1 [ERROR] [MY-013862] [InnoDB] Neither found #innodb_redo subdirectory, nor ib_logfile* files in /data01/mysql/log/
2023-03-29T03:27:23.846399Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2023-03-29T03:27:24.325203Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2023-03-29T03:27:24.325414Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-03-29T03:27:24.325447Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-03-29T03:27:24.326194Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.30) MySQL Community Server - GPL.
Killed



解决方法1 查看 innodb_log_group_home_dir 删除innodb_log_group_home_dir



[root@node1 data]# ll /data01/backup/full_backup_2023-03-28
total 114744
-rw-r----- 1 root root 451 Mar 29 16:01 backup-my.cnf
-rw-r----- 1 root root 157 Mar 29 16:01 binlog-mysql.000003
-rw-r----- 1 root root 37 Mar 29 16:01 binlog-mysql.index
drwxr-x--- 2 root root 4096 Mar 29 16:01 db01
-rw-r----- 1 root root 5586 Mar 29 16:01 ib_buffer_pool
-rw-r----- 1 root root 12582912 Mar 29 16:01 ibdata1
-rw-r----- 1 root root 12582912 Mar 29 16:01 ibtmp1
drwxr-x--- 2 root root 4096 Mar 29 16:01 #innodb_redo
drwxr-x--- 2 root root 4096 Mar 29 16:01 mysql
-rw-r----- 1 root root 25165824 Mar 29 16:01 mysql.ibd
drwxr-x--- 2 root root 4096 Mar 29 16:01 performance_schema
drwxr-x--- 2 root root 4096 Mar 29 16:01 sys
-rw-r----- 1 root root 16777216 Mar 29 16:01 undo_001
-rw-r----- 1 root root 16777216 Mar 29 16:01 undo_002
-rw-r----- 1 root root 24 Mar 29 16:01 xtrabackup_binlog_info
-rw-r----- 1 root root 141 Mar 29 16:01 xtrabackup_checkpoints
-rw-r----- 1 root root 525 Mar 29 16:01 xtrabackup_info
-rw-r----- 1 root root 33554432 Mar 29 16:01 xtrabackup_logfile
-rw-r----- 1 root root 39 Mar 29 16:01 xtrabackup_tablespaces
[root@node1 data]# scp -pr /data01/backup/full_backup_2023-03-28/#innodb_redo .
[root@node1 data]# ls
db01 ib_buffer_pool ibdata1 ibtmp1 #innodb_redo mysql mysql.ibd performance_schema sys undo_001 undo_002 xtrabackup_info
[root@node1 data]# service mysqld start
Starting MySQL.. ERROR! The server quit without updating PID file (/data01/mysql/tmp/mysql.pid).
[root@node1 data]# ll
total 90532
-rw-r----- 1 mysql mysql 56 Mar 29 16:03 auto.cnf
drwxr-x--- 2 mysql mysql 4096 Mar 29 16:01 db01
-rw-r----- 1 mysql mysql 196608 Mar 29 16:03 #ib_16384_0.dblwr
-rw-r----- 1 mysql mysql 8585216 Mar 29 16:03 #ib_16384_1.dblwr
-rw-r----- 1 mysql mysql 5586 Mar 29 16:01 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Mar 29 16:01 ibdata1
-rw-r----- 1 mysql mysql 12582912 Mar 29 16:01 ibtmp1
drwxr-x--- 2 root root 4096 Mar 29 16:01 #innodb_redo
drwxr-x--- 2 mysql mysql 4096 Mar 29 16:01 mysql
-rw-r----- 1 mysql mysql 25165824 Mar 29 16:01 mysql.ibd
drwxr-x--- 2 mysql mysql 4096 Mar 29 16:01 performance_schema
drwxr-x--- 2 mysql mysql 4096 Mar 29 16:01 sys
-rw-r----- 1 mysql mysql 16777216 Mar 29 16:01 undo_001
-rw-r----- 1 mysql mysql 16777216 Mar 29 16:01 undo_002
-rw-r----- 1 mysql mysql 525 Mar 29 16:01 xtrabackup_info
[root@node1 data]# chown -R mysql:mysql /data01/mysql/ /usr/local/mysql
[root@node1 data]# ll
total 90532
-rw-r----- 1 mysql mysql 56 Mar 29 16:03 auto.cnf
drwxr-x--- 2 mysql mysql 4096 Mar 29 16:01 db01
-rw-r----- 1 mysql mysql 196608 Mar 29 16:03 #ib_16384_0.dblwr
-rw-r----- 1 mysql mysql 8585216 Mar 29 16:03 #ib_16384_1.dblwr
-rw-r----- 1 mysql mysql 5586 Mar 29 16:01 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Mar 29 16:01 ibdata1
-rw-r----- 1 mysql mysql 12582912 Mar 29 16:01 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Mar 29 16:01 #innodb_redo
drwxr-x--- 2 mysql mysql 4096 Mar 29 16:01 mysql
-rw-r----- 1 mysql mysql 25165824 Mar 29 16:01 mysql.ibd
drwxr-x--- 2 mysql mysql 4096 Mar 29 16:01 performance_schema
drwxr-x--- 2 mysql mysql 4096 Mar 29 16:01 sys
-rw-r----- 1 mysql mysql 16777216 Mar 29 16:01 undo_001
-rw-r----- 1 mysql mysql 16777216 Mar 29 16:01 undo_002
-rw-r----- 1 mysql mysql 525 Mar 29 16:01 xtrabackup_info
[root@node1 data]# service mysqld start
Starting MySQL............. SUCCESS!