[系统运维]MySQL简要记录

零、前言

默认以5.7最新版本及8.0版本为模板编写，涉及5.7-的内容部分会提及

一、安装

传送门：MySQL :: MySQL Community Downloads https://dev.mysql.com/downloads/repo/apt/)

yum源 ：https://dev.mysql.com/downloads/repo/yum/

例如：当前rpm文件为 mysql80-community-release-el8-3.noarch.rpm

rpm -ivh mysql80-community-release-el8-3.noarch.rpm

①获取当前可获取mysql版本

yum check-update mysql

②选择包，安装

#过滤mysql8.0包
yum list | grep mysql80
#安装mysql-server
yum install mysql-community-server.x86_64

①启动并设置开机自启

systemctl start mysqld
systemctl enable mysqld

②基本命令

检验状态： mysql status

启/停应用

#停止运行mysql
mysql stop
#启动mysql
mysql start

apt源：https://dev.mysql.com/downloads/repo/apt/

例如：当前deb文件为 mysql-apt-config_0.8.22-1_all.deb

sudo dpkg -i mysql-apt-config_0.8.22-1_all.deb

①获取最新版本讯息

sudo apt update

②安装

sudo apt install mysql-server

①加固mysql

通常用于加强密码安全性，同时可以设置mysql的root密码

sudo mysql_secure_installation

②基本命令：

校验状态：systemctl status mysql

启/停应用：

#####启动应用#######
#1、service 启动
service mysql start
#2、safe_mysqld 启动，官方推荐的启动方式(通常用于调试阶段)
mysqld_safe
亦或：mysqld_safe --defaults-file=my.cnf > mysqld.log 2>&1 &

######关闭应用######
service mysql stop

③设置开机自启

sudo update-rc.d -f mysql defaults

二、偷懒备份脚本

下面英文都是为了装逼，简要来说就是分了三个部分

①备份所有数据结构和数据

②备份所有数据

③备份数据结构

#!/bin/bash
# mysql locate here
# just use the format like example that can be backup the db;
# ./backup.sh demo
# extra param is model :
#1、 default is meaning to backup all struct and data
# eg:    ./backup.sh demo 
#2、 data is meaning to keep the data only
# eg:    ./backup.sh demo data
#3、struct is meaning to keep the struct only
# eg:    ./backup.sh demo struct
excute_db=$1
excute_model=$2
mysql_home=/usr/local/mysql/bin/
base_home=/home/backup/
username=root
password=root
dbadress=localhost
# get current datetime
cur=$(date +%Y%m%d)
# some error maybe occur in working procedure and we can using --skip-lock-tables to fix it
# like: ${mysql_home}/mysqldump -h${dbadress}  -u${username} -p${password}  ${excute_db} --skip-lock-tables > ${base_home}/${cur}/${excute_db}_${cur}.sql




defaultKeep(){
 echo "now is backup all struct and data : ${excute_db}"
 ${mysql_home}/mysqldump -h${dbadress}  -u${username} -p${password}  ${excute_db} > ${base_home}/${cur}/${excute_db}_${cur}.sql
}

customKeep(){
 if [ "$1" = "data" ];then
   echo "backup data only: ${excute_db}"
   ${mysql_home}/mysqldump  -h${dbadress} -u${username} -p${password} -t ${excute_db} > ${base_home}/${cur}/${excute_db}_${cur}_dataOnly.sql
 elif [ "$1" = "struct" ];then
   echo "backup struct only: ${excute_db}"
   ${mysql_home}/mysqldump  --opt -h${dbadress} -u${username} -p${password}  -d ${excute_db} > ${base_home}/${cur}/${excute_db}_${cur}_structOnly.sql
 else 
   defaultKeep
 fi
}


folderCreatedByDate(){

echo -e "current would be created folder is ${cur}, the mysql_home is ${mysql_home}\n"

if [ ! -d "./${cur}" ]; then
  mkdir "./${cur}"
else
  echo "folder ./${cur} has been created" 
fi

}

backupSelector(){
echo -e "\n ready to backup ${excute_db}"
folderCreatedByDate
if [ $2 ];then
 customKeep $2
else
 defaultKeep;
fi
}

if [ $1 ];then
  backupSelector $1 $2
else
  echo -e "\n you can just use the format like example that can be backup the db;
 eg: ./backup.sh demo\n
 extra param is model :
1、 default is meaning to backup all struct and data
 eg:    ./backup.sh demo \n
2、 data is meaning to keep the data only
 eg:    ./backup.sh demo data \n
3、struct is meaning to keep the struct only
 eg:    ./backup.sh demo struct \n"
fi

三、用户授权相关操作

Tips：整体上建议先有库再授权，否则不生效

1、创建用户

整体上创建用户需要注意的部分是是否需要限制访问地址。

# 创建用户，不限制访问ip地址
create user 'demo' @'%' identified by "demo_123"
#创建用户，限定ip，例：192.168.110.22
create user 'demo' @'192.168.110.22' identified by "demo_123"

2、授权给用户

1、通常可赋予的权限如下，但是若是猛点，可以直接all

①Alter: 修改已存在的数据表(例如增加/删除列)和索引。
②Create: 建立新的数据库或数据表。
③Delete: 删除表的记录。
④Drop: 删除数据表或数据库。
⑤INDEX: 建立或删除索引。
⑥Insert: 增加表的记录。
⑦Select: 显示/搜索表的记录。
⑧Update: 修改表中已存在的记录。

2、grant 期间也可以限定ip赋予权限

#常用授权用于基于增/删/查/更新即可
grant select,insert,update,delete on *.* to "demo"@"%";

#其实不是很推荐，所有权限all put
grant all on *.* to "demo"@"%";

#刷新权限
flush privileges;
#查看权限,demo为用户名
show grants for demo;

四、其他配置参数

参考讯息：

8.0版本：MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables

5.7版本：MySQL :: MySQL 5.7 Reference Manual :: 5.1.7 Server System Variables

1、Client

针对客户端访问部分内容配置

2、mysqlId

针对mysql应用

①通用配置

1、innodb配置

2、主从配置

参考地址： MySQL :: MySQL 8.0 Reference Manual :: 13.4.2.1 CHANGE MASTER TO Statement

②日志方面配置

③优化配置

④对应超时相关配置

⑤版本中过期配置

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖