shell脚本初始化mysql

随心~稳心

已于 2022-05-26 16:32:19 修改

阅读量697

点赞数 1

分类专栏： MySQL shell 文章标签： mysql 数据库 mariadb

于 2022-05-26 16:30:30 首次发布

本文链接：https://blog.csdn.net/PengDW12/article/details/124986643

版权

MySQL 同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

shell

2 篇文章 0 订阅

订阅专栏

这里mysql的安装方式是解压缩包的形式

#!/bin/bash
user=root
host=localhost
port=23306

source /etc/profile
mkdir -p /mnt/data/mysqldb

tar -zxvf ./software-package/sql.tgz -C ${MYHOME}

#create user
groupadd mysql
#密码加密
pass=$(perl -e 'print crypt($ARGV[0], "password")' "mysql_QWE123")
useradd -g mysql mysql -p ${pass}

# 授权
chmod -R 777 /mnt/data/mysqldb
#chown -R [用户]:[所属组] 目录
chown -R mysql:mysql /mnt/data/mysqldb

newpasswd='root_mysql&QWE'
mysqld --initialize --console --user=mysql
passwd=$(gawk '/root@localhost: /{print $13}' /mnt/data/mysqldb/mysqldb.log)
echo ${passwd}

systemctl start mysql.service
systemctl enable mysql.service

#sql文件夹存放sql语句，是从旧的mysql用Navicat导出整个数据库
if [ -d "./sql/" ];then
  echo "sql文件夹存在"
  cd ./sql
else
  echo "sql文件夹不存在"
fi


# 非交互改密码
mysql -u root -p$passwd --connect-expired-password -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '$newpasswd';"
mysql -uroot -p${newpasswd} << EOF
use mysql;
update user set user.Host='%' where user.User='root';
flush privileges;
EOF

# 判断文件是否插入过，在sql文件头插入建数据库操作
first=$(sed -n '1p;1q' my1.sql)
echo "$first"
if [[ "$first" =~ "CREATE" ]]
then
	echo "以修改过my1这张表"
else
	echo "未修改过my1这张表，执行插入"
	sed -i "1iCREATE database if NOT EXISTS \`my1\` default character set utf8mb4 collate utf8mb4_general_ci;" my1.sql 
	sed -i "2iuse my1;" my1.sql 
	sed -i "3iset names utf8mb4;" my1.sql 
	echo "插入完成"
fi

# 免登录直接在命令行导入sql文件
mysql --user=$user --password=$newpasswd --host=$host -P$port --default-character-set=utf8 -f < my1.sql
echo 'my1 done'

mysql安装包形式安装

离线安装rpm出现依赖问题

在离线安装出现依赖问题时，可在联网环境下（本地虚拟机即可）用

yum install -y 软件名 --downloadonly --downloaddir=./保存路径

保存下载的这些包，直接在生产环境中离线安装即可
在下载的所有文件中执行

rpm -Uvh --force --nodeps *.rpm

#!/bin/bash

# 获取脚本当前位置
export MYHOME="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
var=$(rpm -qa | grep mysql)
echo "$var"
rpm -e --nodeps $var

var=$(rpm -qa | grep mariadb)
echo "$var"
rpm -e --nodeps $var

rpm -ivh ${MYHOME}/files/mysql/mysql-community-common-*.rpm
rpm -ivh ${MYHOME}/files/mysql/mysql-community-libs-*.rpm
rpm -ivh ${MYHOME}/files/mysql/mysql-community-client-*.rpm
rpm -ivh ${MYHOME}/files/mysql/mysql-community-server-*.rpm

#rpm -ivh ${MYHOME}/files/mysql/mysql-community-libs-compat-*.rpm
rpm -ivh ${MYHOME}/files/mysql/libxml2-*.rpm
rpm -ivh ${MYHOME}/files/mysql/libxslt-*.rpm

#echo 'validate_password=off' >> /etc/my.cnf
#echo 'lower_case_table_names=1' >> /etc/my.cnf
systemctl enable mysqld
systemctl start mysqld

newpasswd=1qaz@WSX
passwd=$(gawk '/root@localhost:/{print $11}' /var/log/mysqld.log)
echo ${passwd}
expect mysql-init ${passwd} ${newpasswd}

mysql -uroot -p${newpasswd} << EOF
grant all privileges on *.* to 'root'@'%' identified by '${newpasswd}' with grant option;
flush privileges;
EOF
#systemctl restart mysqld