mysql mariadb 5 or 8 数据库管理，破解密码，创建用户和数据库，开放远程登录

#!/bin/bash
# 数据库管理脚本: 安装mysql8数据库创,建用户并创建测试表,修改密码,

InputRead(){
echo " 懒得输入信息，我已经在脚本内配置登录信息,请一直回车确认"
read -p "请输入管理员用户：" rootuser
read -p "请输入管理员密码：" rootuserpasswd
read -p "指定用户名：" user
read -p "指定用户密码：" passwd
read -p "指定用户数据库：" database
sleep 3;
# rootuser=root             ;rootuserpasswd=eisc.cn        ;user=eisc          ;passwd=eisc.cn          ;database=eisc   ;
echo "您输入的信息为：$rootuser $rootuserpasswd $user $passwd $database"
}

Mysql8(){
mysqlrpm=$(rpm -qa | grep mysql | wc -l)
   if [ $mysqlrpm -lt 1 ]
      then
          echo "发现MySQL 没有安装，正再安装。。。。"
          yum localinstall https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm -y
          # yum localinstall是用来安装本地rpm包的命令，首先rpm包要先下载到本地，然后在本地目录执行yum localinstall *.rpm
          yum install mysql-community-server -y
          # 安装mysql
      else
          echo "----  mysql 已经安装 ----"
   fi
}

CreateUser5(){
#------------ mysql5.x 一键创建和删除数据库和用户  --------------#
mysql -u$rootuser -p$rootuserpasswd -e "create database $database character set utf8 collate utf8_bin; grant all on $database.* to '$user'@'localhost' identified by '$passwd'; grant all on $database.* to '$user'@'%' identified by '$passwd'; show databases;SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS ListUsername FROM mysql.user where User='$user';grant lock tables on $database.* to '$user'@'localhost';"
                                                               #一键创建数据库，用户
                                                               #创建数据库eisc写入字符编码utf8校验字符编码utf8_bin
                                                               #需要先在本地grantall创建用户，然后再创建远程用，否则出错。
                                                               #查看数据库，从mysql中的user表选择user，host列的信息查看 指定用户列表
                                                               #mysqlCONCAT（）函数用于将多个字符串连接成一个字符串
                                                               # grant lock tables on $database.* to '$user'@'localhost';  导入导出数据库的权限
}
DropUser5(){
 mysql -u$rootuser -p$rootuserpasswd -e "DROP user '$user'@'localhost'; DROP user '$user'@'%';  drop database $database;show databases;SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS ListUser FROM mysql.user where User='$user';"
                                                              #一键删除数据库，用户
}

CreateUser7(){
#------------ mysql7.x 一键创建和删除数据库和用户  --------------#
 mysql -u$rootuser -p$rootuserpasswd -e "use mysql;select user,authentication_string from user; set global read_only=0; set global validate_password.policy=0 ; set global validate_password.length=6;create database $database character set utf8 collate utf8_bin; create user '$user'@'localhost' identified by '$passwd';create user '$user'@'%' identified by '$passwd' ; show databases;SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS ListUsername FROM mysql.user where User='$user';           grant all privileges on $database.* to '$user'@'localhost' ; grant all privileges  on $database.* to '$user'@'%' ; SHOW GRANTS FOR $user;grant lock tables on $database.* to '$user'@'localhost';FLUSH PRIVILEGES;"
                                                     # 一键创建用户
                                                     # grant all privileges on eisc.* to 'eisc'@'localhost'        # 授 予在eisc数据库上所有权限
                                                     # 开放指定权限：将all privileges 改为：select,update,delete,create  # 增删查改
                                                     # revoke all privileges on *.* from 'eisc'@'localhost'           # 撤销所有权限
                                                     # set global read_only=0; 主库写入权限，0开启，1关闭，
                                                     # 关闭状态报错  The MySQL server is running with the --read-only option  任何用户无法创建数据库表等
                                                     # grant lock tables on $database.* to '$user'@'localhost'; 导入导出数据库的权限

}
DropUser7(){
 mysql -u$rootuser -p$rootuserpasswd -e "drop database $database; drop user '$user'@'localhost' ;drop user '$user'@'%'; "
                                                    # 一键删除：数据库，用户
}


UpdateUser(){
#--------------------  mysql 破解重置root密码 ----------------#
echo "Hello, welcome to continuous integration eisc.cn mysql 同时破解远程和localhost数据库密码";


mycnf="/etc/my.cnf"
NR=`cat -n $mycnf | grep "\[mysqld\]" | awk -F" " '{print $1}'` ; echo $NR
                  # 找到 [mysqld] 标签所在行
sed -i "/skip-grant-tables/d" $mycnf
                  # 删除这个root免密登录配置，重新添加
sed -i "$NR a skip-grant-tables" $mycnf ; systemctl restart mysqld mariadb
                  # 在mysqld 标签下面增加免密登录配置信息
                  # 重启数据库
echo "您输入的管理员root信息为：$rootuser $rootuserpasswd"
mysqlversion=$(mysql -e "select version();" | awk -F"." 'NR==2{print $1}') ; echo "mysql版本：$mysqlversion";
if [ $mysqlversion -ge 7 ];then
      echo "当前数据库版本大于7"
      mysql -e "use mysql;select user,authentication_string from user; set global read_only=0;set global read_only=1; set global validate_password.policy=0 ; set global validate_password.length=6;flush privileges;alter user '$rootuser'@'localhost' identified by '$rootuserpasswd';alter user '$setuser'@'%' identified by '$rootuserpasswd';"
   else
      mysql -e "use mysql;select user,authentication_string from user; set global read_only=0;set global read_only=1; use mysql;select user,authentication_string from user; set global read_only=0;set global read_only=1; UPDATE user SET password=password('$rootuserpasswd') WHERE user='$rootuser';"
      echo "低于7的版破解"
fi

                  # 登录数据库修改root密码为：eisc.cn
                  # 重点：set global read_only=0; # 开启主库写入 flush privileges; # 生效权限
                                  # set global validate_password.policy=0 ; 修改密码  set global validate_password.length=6; 密码长度为6
                  # 关掉新主库的只读属性,读写属性，密码长度为6
sed -i "/skip-grant-tables/d" $mycnf; systemctl restart mysqld mariadb
                  # 删除之前的配置，重启数据
}

TestTables(){
#--------------------  test aaa bbb 测试表 ----------------#
mysql -u$user -p$passwd -e "use $database; select now(); DROP TABLE IF EXISTS aaa;create table aaa(id int,name varchar(20),password varchar(255),xingqi varchar(255),city varchar(255));insert into aaa(id,name,password,xingqi,city) values('1','aaa','qqqqq','11111','ggggggg'),('2','bbb','qqqqq','11111','ggggggg'),('3','ccc','qqqqq','11111','ggggggg'),('4','ddd','qqqqq','11111','ggggggg'),('5','eee','qqqqq','11111','ggggggg');show tables;select * from aaa;"

mysql -u$user -p$passwd -e "use $database; select now(); drop table if exists bbb;create table bbb(id int,name varchar(20),password varchar(255),xingqi varchar(255),city varchar(255));insert into bbb(id,name,password,xingqi,city) values('1','aaa','qqqqq','11111','ggggggg'),('2','bbb','qqqqq','11111','ggggggg'),('3','ccc','qqqqq','11111','ggggggg'),('4','ddd','qqqqq','11111','ggggggg'),('5','eee','qqqqq','11111','ggggggg');show tables;select * from bbb;"

}

runningeisc(){
Mysql8
echo "

编号            功能
1           破解重置root密码
2           创建用户并开放远程登录 创建aaa  bbb 测试表
3           删除用户，并且删除该用户的数据库
"
read -p "eisc 为您提供服务，请输入编码进行执行功能：" runread
echo "您执行了函数： $runread  ";
case $runread in
"1") echo "执行函数：1           破解重置root密码"
InputRead
UpdateUser
echo "使用用户更改后的信息：$rootuser $rootuserpasswd  登录数据库查询时间："
mysql -u$rootuser -p$rootuserpasswd -e" select now();"
;;

"2") echo "执行函数：2           创建用户并开放远程登录"
InputRead
mysqlversion=$(mysql -u$rootuser -p$rootuserpasswd -e "select version();" | awk -F"." 'NR==2{print $1}') ; echo "mysql版本：$mysqlversion";
if [ $mysqlversion -ge 7 ];then
      echo "当前数据库版本大于7，正在执行。。。"
      CreateUser7
      TestTables
   else
      echo "数据库版本低于7 ，正在执行。。。 "
          CreateUser5
fi

;;

"3") echo "执行函数：3           删除用户，并且删除该用户的数据库"
InputRead
mysqlversion=$(mysql -u$rootuser -p$rootuserpasswd -e "select version();" | awk -F"." 'NR==2{print $1}') ; echo "mysql版本：$mysqlversion";
if [ $mysqlversion -ge 7 ];then
      echo "当前数据库版本大于7，正在执行。。。"
      DropUser7
   else
      echo "数据库版本低于7 ，正在执行。。。 "
          DropUser5
fi
;;
*) echo "输入无效，请重新输入！"
;;
esac
}
runningeisc
#mysql -u$rootuser -p$rootuserpasswd -e "drop database eisc;drop user eisc@'localhost';drop user eisc@'%';" 一键删除测试账号
# wget www.eisc.cn/file/shell/mysql-contol-config.sh ; sh mysql-contol-config.sh;