MySQL常見問題解決辦法以及自動化安裝腳本

更新時間：2024年05月29日 09:32:01 作者：Byyyi耀

自動化運維是一個DBA應該掌握的技術,其中,自動化安裝數(shù)據(jù)庫是一項基本的技能,下面這篇文章主要給大家介紹了關于MySQL常見問題解決辦法以及自動化安裝腳本的相關資料,需要的朋友可以參考下

常見問題

MySQL密碼正確但無法登錄的情況

這種情況一般都是因為緩存，使用mysql -u root -p123456直到成功登陸為止，并且進入之后重新修改密碼，多次重復修改密碼的命令并且再一次清除緩存后退出。

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

MySQL安裝的GPG密鑰問題

前言

在MySQL安裝的過程中，我們出現(xiàn)了不能通過YUM安裝的MySQL的問題，YUM工具警告說安裝包的GPG密鑰無效或未安裝，這意味著不能驗證該包的真實性和完整性。系統(tǒng)嘗試從指定的本地路徑檢索密鑰，但沒有找到有效的密鑰與包匹配。

具體錯誤

Downloading packages:
warning: /var/cache/yum/x86_64/7/mysql80-community/packages/mysql-community-client-8.0.36-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID a8d3785c: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql


The GPG keys listed for the "MySQL 8.0 Community Server" repository are already installed but they are not correct for this package.
Check that the correct key URLs are configured for this repository.


 Failing package is: mysql-community-client-8.0.36-1.el7.x86_64
 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

解決方式

yum install net-tools -y # 檢查和安裝網(wǎng)絡工具包
yum list installed | grep mysql # 檢查已安裝的MySQL安裝包
rpm -e --nodeps mysql80-community-release-el7-8.noarch # 強制刪除指定的MySQL倉庫包，不檢查依賴。
rm -rf /etc/my.cnf # 刪除MySQL的主配置文件。
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022 # 導入MySQL的GPG公鑰，用于驗證軟件包。
rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm# 安裝新的MySQL倉庫配置包。
yum -y install mysql-community-server# 安裝MySQL服務器及其依賴。

此處出現(xiàn)了GPG的錯誤，我們需要通過繞過GPG的檢查來實現(xiàn)安裝

yum -y install mysql-community-server  --nogpgcheck # 忽略GPG密鑰檢查，重新安裝
systemctl start mysqld && systemctl enable mysqld # 啟動MySQL服務并設置為開機啟動。
systemctl status mysqld # 檢查MySQL服務狀態(tài)，確認服務已經(jīng)運行
grep 'temporary password' /var/log/mysqld.log # 獲取隨機密碼并登錄

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
set global validate_password.policy=0;
ALTER USER USER() IDENTIFIED BY 'wingkin45@A.';
set global validate_password.policy=0;
set global validate_password.length=4;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

MySQL自動化安裝腳本

echo "INFO : BEGIN INSTALLING MYSQL8 ..."
#檢查mysql服務版本，若存在Mysql8,直接退出
me=`ps -ef|grep mysqld|wc -l`
if [ $me -eq 2 ];then
        me=`systemctl status mysqld|grep Docs|awk -F: '{print $NF}'`
        if [ $? -eq 0 -a "$me" == "mysql(8)" ];then
                echo "INFO : MYSQL8 FOUND "
                exit 0
        fi

        #若存在舊版本的mysql,且服務處于啟動狀態(tài)
        systemctl status mysqld|grep "active (running)"
        if [ $? -eq 0 ];then
                systemctl stop mysqld
                echo "INFO : OLD MYSQL SERVICE FOUND BUT STOPPED"
        fi
fi
#若存在舊版本的殘留，則直接卸載
names=`rpm -qa|grep mysql`
for name in $names;do
        yum -y remove $name 1>/dev/null 2>&1
        echo "INFO : OLD MYSQL RESOURCE [ $name ] FOUND AND REMOVED"
done

#rpm 安裝mysql8
src=$1
if [[ ! -e $src || ! -f $src ]];then
        echo "ERROR : PLEASE PROVIDE MYSQL8 RPM FILE "
        exit 1
fi
if [[ ! "$src" =~ mysql8.*\.rpm$ ]];then
        echo "ERROR : RPM PACKAGE OR NOT MYSQL8"
        exit 1
fi

rpm -ivh $src 1>/dev/null 2>&1
if [ $? -eq 0 ];then
        echo "INFO : SUCCESS TO RPM INSTALL MYSQL8"
else
        echo "ERROR : FAIL TO RPM INSTALL MYSQL8"
        exit 2
fi

# yum 安裝mysql8服務
yum -y install mysql-community-server --nogpgcheck 1>/dev/null 2>&1
if [ $? -eq 0 ];then
        echo "INFO : SUCCESS TO YUM MYSQL8 SERVICE"
else
        echo "ERROR : FAIL TO YUM MYSQL8 SERVICE"
        exit 3
fi

#檢查服務是否已啟動,若未啟動則啟動
me=`systemctl status mysqld|grep "active (running)"`
if [ $? -ne 0 ];then
        systemctl start mysqld
fi
echo "INFO : MYSQL8 SERVICE STARTED"

echo "INFO : FINISH INSTALL MYSQL8"

unset me
unset names
unset src