mysql

包下载地址：Index of /232905 (mysql.com)

#8.0安装
wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

rpm -ivh mysql80-community-release-el7-1.noarch.rpm

cd /etc/yum.repos.d/

yum install mysql-server

chown -R root:root /var/lib/mysql

service mysqld restart

ss -tnl

grep 'temporary password' /var/log/mysqld.log

mysql -u root -p

ALTER USER 'root'@'localhost' IDENTIFIED BY 'ASDFasdf123.';        #将root密码设置为ASDFasdf123. 如果报错就是密码安全性不高

flush privileges;

#5.7安装
wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql-community-server  --nogpgcheck
systemctl start mysqld.service
grep 'password' /var/log/mysqld.log看初始密码
mysql -u root -p
#use mysql;
#update user set password=PASSWORD("ASDFasdf123.") where User='root';
#flush privileges; 

#5.6安装
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum -y install mysql-server
systemctl start mysqld.service

#备份
mysql_config_editor set --user=root --host=localhost --port=3306 --password
#在当前用户下生成一个密文的mysql密码文件

#bin/bash
user="root"
passwd="QWER123."
dir="/root/"
date=$(date +%Y%m%d)
mysqldump -u$user -p$passwd --all-databases > $dir/$date.sql

#计划任务
crontab -e
50 22 * * * bash /usr/sbin/mysqlbak.sh
每天的22点50执行

#优化
show processlist; 
#查看连接数，处于sleep状态的可以杀掉

show variables like "max_connections"; 
#查看最大连接数，应该是与上面查询到的连接数相同

set GLOBAL max_connections=1000;
#修改最大连接数为1000

show global variables like 'wait_timeout'; 
#mysql在关闭一个非交互的连接之前要等待的秒数，默认是28800s

set global wait_timeout=300; 
#修改等待的时间

set global interactive_timeout=500; 
#mysql在关闭一个连接之前要等待的秒数，至此可以让mysql自动关闭那些没用的连接

create user 'jjy'@'localhost' IDENTIFIED BY '123';
#创建jjy用户密码为123

grant select,update,insert on jj.* to jjy@'localhost';
#给jjy用户对jj这个库授予读，查，添权限

select user,host from mysql.user;
#查看有哪些用户

DROP USER 'jjy'@'localhost';
#删除用户jjy

show processlist;
#查看连接数

flush privileges;
#刷新

#####查询数据大小
use information_schema;       
#一定要先进这个库，才能查询信息 
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;