MySQL8.0常用知识
MySQL8.0 常用知识
0.MySQL 8.0 和 MySQL之前版本的区别
0.1.端口有两个 3306 和 33060
0.2.修改密码不一样
0.3.赋予权限必须先 create user 然后 再 grant 权限，不能合并一起写
0.4.用户密码加密方式不太一样，8.0 caching_sha2_password，5.7 mysql_native_password
0.5.用户角色上的变化
1.修改密码
MySQL
# 8.0 之前
set password = password('mysql');
# 8.0 之后
alter user user() indentified by 'mysql';
Linux
mysqladmin -u root password 'root'
2.修改配置文件
vim .bash_profile
3.生成数据表
bin/mysqld --initialize --user=mysql --datadir /user/local/mysql/data
如果出现提示 libaio 错误，安装 libaio
4.创建 MySQL 的 Linux 用户
#-s 表示指定用户所用的 shell，此处为 /sbin/nologin，表示不登录。 #-M 表示不创建用户主目录。 #-g 表示指定用户的组名为 mysql。 useradd -s /sbin/nologin -M -g mysql mysql
5.挪移数据目录
#新建目录 mkdir /data cd /data mkdir mysql ls #移动 MySQL 下的 data mv /usr/local/mysql/data . ls #启动一下看看 /etc/init.d/mysql.server start #启动有错，需要配置 my.cnf vi /etc/my.cnf #输入以下内容 #[mysqld] #datadir=/data/mysql/data #启动，成功 /etc/init.d/mysql.server start
6.挪移整个 MySQL
#先把 data 挪移回原 mysql 目录下 mv data /usr/local/mysql/ #挪移整个 mysql cd /usr/local/ mv mysql /data/mysql cd /data/mysql/mysql #修改 my.cnf 文件 vi /etc/my.cnf #修改内容如下 #[mysqld] #basedir=/data/mysql/mysql #datadir=/data/mysql/mysql/data #启动 /etc/init.d/mysql.server start
7.初始化时直接指定
#删掉 data 目录文件 cd /data/mysql/mysql/data rm -rf * #指定初始化 bin/mysqld --initialize --user=mysql --basedir=/data/mysql/mysql --datadir=/data/mysql/mysql/data & #启动 /etc/init.d/mysql.server start #查看一下 ps -ef|grep mysql #登录，报错 mysql -u root -p #修改配置文件路径，因为 mysql 路径改变了 vi .bash_profile source .bash_profile #通过初始化密码登录后，修改密码 alter user user() identified by 'mysql';
8.一台机器跑两个 MySQL
cd /data/mysql/ #生成多一份 mysql cp mysql mysql2 -R #检查一下 cd mysql2 ps -ef|grep mysql #登录 mysql mysql -u root -p #登录后查看 show variables like '%sock%'; #退出 mysql，查看端口，发现 8.0 与以前版本有区别，多了一个 33060 端口 netstat -an|grep LISTEN #新增一个 my.cnf 文件 cp /etc/my.cnf /etc/my3307.cnf vi /etc/my3307.cnf #添加内容如下 #[mysqld] #basedir=/data/mysql/mysql2 #datadir=/data/mysql/mysql2/data #port=3307 #socket=/tmp/mysql3307.sock #mysqlx_port=33070 #mysqlx_socket=/tmp/mysqlx33070.sock #启动数据库，提示权限错误 cd /data/mysql/mysql2 bin/mysqld --default-file=/etc/my3307.cnf --user=mysql & #因为是用 root 拷贝，需要改变目录权限为 mysql chown mysql:mysql data -R #重新启动 bin/mysqld --default-file=/etc/my3307.cnf --user=mysql & #查看，多了一个进程 ps -ef|grep mysql netstat -an|grep LISTEN #登录 mysql -u root -p -S /tmp/mysql3307.sock #创建一个库 create database course; show databases; exit #再重新登录 3306 mysql -u root -p
9.查询数据库 log 和 sock 信息
show variables like '%log%'; show variables like '%sock%';
10.MySQL 锁表原因，解决方法以及修改表结构的正确姿势
MySQL（5.6 以后，支持在线 DDL，支持操作与查询的并发）其实你更改表结构不一定会锁表，但是还是会有概率锁表的。
首先了解一下 MySQL 的两种表锁，一种是 lock table 的表锁，一种是元数据锁 metadata Lock。
第一种表级别的锁，一个线程执行 lock table 操作，比如说你执行读操作，别的线程想写操作就不行了，但是他也要读呢？没问题啊，反正你不影响我就好了。同理写操作，你这时候读肯定就不行了。什么时候可以正常的读写，那就是执行 unlock table 以后就可以了。
第二种元数据锁，metedata Lock 也叫 MDL。这个其实是我们使用中最常见的一个锁表。这个MDL是一个隐式锁，当你访问一个表它会自动加一个 MDL，来保证你数据的读写的正确。举个例子，人家在访问，这时候一个字段正在修改甚至删除或者增加，肯定会影响对写的结果，这时候就把这表锁上。
同样我们在修改字段时，有的时候会被锁表很大的概率是MDL导致的，而同样的操作可能有的时候就不会锁表，那是因为你在执行修改的时候，没有人遍历你所修改的表。而实际情况中，你的程序可能在跑，在访问这个表，别人不知情的情况下也回去访问读写这个表，结果就可能导致表被锁，尽管你只做了一个操作，或者访问，或者修改。
处理
/*步骤1 查看锁的表 显示的结果就是被锁的表，有的表会关联别的表会导致多个表被锁 (其实你可以越过这个步骤，因为第二步你能知道那些操作锁了。)*/ SHOW OPEN TABLES WHERE In_use > 0; /*步骤2 查找进程*/ SHOW PROCESSLIST; /*步骤3 找到锁表的进程*/ /*就是那个锁表的进程，记住这个进程号，就是第一个id字段。*/ /*步骤4 kill 掉这个进程，（类似于我们 Linux 那种杀死进程那种。）*/ KILL xxxxx /*步骤5 回头看一看还有没有被锁的表，发现没有异常的锁表进程就OK。*/ SHOW OPEN TABLES WHERE In_use > 0;
11.忘记 root 密码
#步骤1：关闭 MySQL 服务器
service mysqld stop
#步骤2：使用 --skip-grant-tables 选项重启 MySQL 服务
./bin/mysqld_safe --skip-grant-tables --user=root &
#其中 --skip-grant-tables 选项的意思是启动 MySQL 服务的时候跳过权限表认证。启动后，连接到 MySQL 的 root 将不需要命令。
#步骤3：用空密码的 root 用户连接到 MySQL，并且更新 root 口令：
mysql -u root
#MySQL 5.7 以下版本：
update mysql.user set password=password('新密码') where user='root' and host='localhost';
#MySQL 5.7 版本：
update user set authentication_string=password('新密码') where user='root' and Host='localhost';
#步骤4：刷新权限表，使得权限认证重新生效：
flush privileges;
#步骤5：重启 MySQL 数据库
service mysqld restart
#步骤6：登录 MySQL
mysql -u root -p
11.安装 MySQL 5.6
#每个步骤结束后，都可以使用 echo $? 查看返回值是否为 0，为 0 表示正确 #步骤1：查看当前的 Linux 系统环境 #查看操作系统版本 cat /etc/redhat-release #内核版本 uname -r #64位系统 uname -m #步骤2：安装 MySQL 需要的依赖包 yum install ncurses-devel libaio-devel -y rpm -qa ncurses-devel libaio-devel #步骤3：安装编译 MySQL 需要的软件 yum install cmake -y rpm -qa cmake #步骤4：建立 MySQL 用户账号 groupadd mysql useradd -s /sbin/nologin -M -g mysql mysql id mysql #步骤5：采用编译方式安装 MySQL mkdir -p /home/oldboy/tools cd /home/oldboy/tools/ wget -q http://mirrors.163.com/mysql/Downloads/MYSQL-5.6/mysql-5.6.40.tar.gz ls -lh #步骤6：解压并配置 MySQL tar xf mysql-5.6.40.tar.gz cd mysql-5.6.40 cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.40 \ -DMYSQL_DATADIR=/application/mysql-5.6.40/data \ -DMYSQL_UNIX_ADDR=/application/mysql-5.6.40/tmp/mysql.sock \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_EXTRA_CHARSETS=all \ -DWITH_INNDOBASE_STORAGE_ENGINE=1 \ -DWITH_FEDERATED_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \ -DWITH_ZLIB=bundled \ -DWITH_SSL=bundled \ -DENABLED_LOCAL_INFILE=1 \ -DWITH_EMBEDDED_SERVER=1 \ -DENABLE_DOWNLOAD=1 \ -DWITH_DEBUG=0 #步骤7：编译并安装 MySQL # 如果是多核 cpu，则可指定 make -j cpu 核数，加快编译速度 make make install #步骤8：为 MySQL 安装路径设置不带版本号的软连接 /application/mysql ln -s /application/mysql-5.6.34/ /application/mysql ls -l /application/ ls /application/mysql/ #步骤9：创建 MySQL 数据库配置文件并对数据库目录授权 ll support-files/*.cnf mv /etc/my.cnf /etc/my.cnf.bak cp support-files/my-default.cnf /etc/my.cnf chown -R mysql.mysql /application/mysql/ #步骤10：初始化 MySQL 数据库 /application/mysql/scripts/mysql_install_db --basedir=/application/msyql/ --datadir=/application/mysql/data --user=mysql ls -l /application/mysql/data tree /application/mysql/data #步骤11：设置 MySQL 启动脚本 pwd cp support-files/mysql.server /etc/init.d/mysqld chmod 700 /etc/init.d/mysqld ls -l /etc/init.d/mysqld #步骤12：启动 MySQL 数据库 mkdir -p /application/mysql-5.6.40/tmp chown -p /application/mysql/ /etc/init.d/mysqld start #步骤13：检查 MySQL 数据库是否启动 netstat -lntup|grep mysql #步骤14：查看 MySQL 数据库启动结果日志 tail /application/mysql/data/oldboy.err #步骤15：设置 MySQL 开机自启动 chkconfig --add mysqld chkconfig --list mysqld #步骤16：将 MySQL 相关命令加入全局路径 ls /application/mysql/bin/mysql echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile tail -l /etc/profile source /etc/profile echo $PATH #步骤17：登录 MySQL 测试 mysql -u root -p #步骤18：为 root 用户设置密码 mysqladmin -u root password 'oldboy123' mysql -u root -p #步骤19：清理 MySQL 服务器内无用的用户 select user, host from mysql.user; drop user root@'::1'; drop user root@'oldboy'; drop user ''@'oldboy'; drop user ''@'localhost'; select user, host from mysql.user; #如果无法删除，采用 delete 命令 delete from mysql.user where user='' and host='MySQL'; flush privileges; #步骤20：删除 MySQL 数据库内无用的 test 库 drop database test;
12.MySQL权限
/*
权限存储在 mysql 库的 user、db、tables_priv、columns_priv and procs_priv 几个系统表内
Alter 权限代表允许修改表结构的权限
Alter routine 权限代表允许修改或者删除存储过程、函数的权限
Create tablespace 权限代表允许创建、修改、删除表空间和日志组的权限
Create temporary tables 权限代表允许创建临时表的权限
Process 权限代表允许查看 MySQL 中的进程信息
Super 权限代表允许执行一系列数据库管理命令、包括 kill 强制关闭某个连接
Usage 权限是创建一个用户之后的默认权限，其本身代表 "无权限"
*/
/*不同用户*/
SHOW GRANTS FOR 'joe'@'office.example.com';
SHOW GRANTS FOR 'joe'@'home.example.com';
/*创建用户赋予权限*/
create user cdq@localhost indentified by 'mysql';
grant all privileges on *.* to cdq@localhost with grant option;
/*回收全部权限*/
revoke all privileges on *.* from cdq@localhost;
/*赋予所有数据库的 select 权限*/
grant select on *.* to cdq@localhost;
/*赋予 course 数据库的 select 权限*/
grant select on course.* to cdq@localhost;
/*回收 course 数据库的 select 权限*/
revoke select on course.* from cdq@localhost;
/*赋予 course 数据库的表 students 的 select 权限*/
grant select on course.student to cdq@localhost;
/*回收 course 数据库的 select 权限*/
revoke select on course.student from cdq@localhost;
/*赋予 course 数据库的表 students 的 select 字段 id 的权限*/
grant select(id) on course.student to cdq@localhost;
/*创建用户*/
create user cdq@'10.0.0.201' identified by 'mysql';
create user cdq@'10.0.0.%' identified by 'mysql';
create user cdq@'10.0.0.20%' identified by 'mysql';
/*
用户 cdq@10.0.0.% 和 cdq@10.0.0.20% 权限不一样
如果用 cdq@10.0.0.201 去访问会用哪个权限呢？
答案是它是一个叠加的效果，两个用户权限都有
*/
/*
执行 Grant、revoke、set password、rename user 会自动 flush
如果执行 insert/update/delete 操作上述的系统权限表之后，必须执行刷新权限
*/
flush privileges
mysqladmin flush-privileges
mysqladmin reload
/*
回收 all 权限不影响业务须注意可以先如下执行，例如回收 UPDATE
*/
revoke delete on *.* from cdq@localhost;
/*删除用户*/
drop user xxx@localhost;
/*
设置 MySQL 用户资源限制（都是大写），0 代表没有限制
max_user_connections
max_queries_pre_hour
max_updates_pre_hour
max_connections_pre_hour
max_user_connections
*/
CREATE USER 'xxx'@localhost INENTIFIED BY 'mysql' WITH MAX_UPDATES_PRE_HOUR 20;
ALTER USER 'xxx'@localhost WITH MAX_UPDATES_PRE_HOUR 20;
13.多实例
实例1：IBM 服务器 48 核 CPU，内存 96 GB，一台服务器运行 3~4 个实例
实例2：访问量不大，可以通过 3 台服务器部署 9~15 个实例，交叉做主从复制、数据备份及读写分离
实例3：新浪网，服务器 DELL R510 居多，CPU 是 E5210、内存 48 GB、磁盘为 12*300G SAS、采用的是 RAID10。单机 1~4 个数据库实例居多，其中又数 1~2 个的最多，这是因为大业务占用的机器比较多。
极力推荐：多配置文件、多启动程序部署方案
# 步骤1：安装 MySQL 需要的依赖包和编译软件
yum install ncurses-devel libaio-devel -y
rpm-qa ncurses-devel libaio-devel
# 步骤2：安装编译 MySQL 需要的软件
yum install cmake -y
rpm -qa cmake
# 步骤3：建立 MySQL 用户账号，默认会创建和 mysql 用户同名的组
useradd -s /sbin/nologin -M mysql
id mysql
# 步骤4：获取 MySQL 软件包
# 省略
# 步骤5：采用编译方式安装 MySQL
# 省略
# 步骤6：创建 MySQL 多实例的数据文件目录，一般以 2~4 个实例为佳
mkdir -p /data/{3306,3307}/data
tree /data
# 步骤7：创建 MySQL 多实例的配置文件
ll support-files/*.cnf
mv /etc/my.cnf /etc/cnf.bak
vim /data/3306/my.cnf
vim /data/3307/my.cnf
cat /data/3306/my.cnf
# 内容如下
[client] #<== 客户端模块
port = 3306
socket = /data/3306/mysql.sock
[mysqld] #<== 服务端模块
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /application/mysql
datadir = /data/3306/data
log-bin = /data/3306/mysql-bin
server-id = 6
[mysqld_safe] #<== 启动服务模块
log-error=/data/3306/oldboy_3306.err
pid-file=/data/3306/mysqld.pid
cat /data/3307/my.cnf
# 内容如下
[client] #<== 客户端模块
port = 3307
socket = /data/3307/mysql.sock
[mysqld] #<== 服务端模块
user = mysql
port = 3307
socket = /data/3307/mysql.sock
basedir = /application/mysql
datadir = /data/3307/data
log-bin = /data/3307/mysql-bin
server-id = 7
[mysqld_safe] #<== 启动服务模块
log-error=/data/3307/oldboy_3307.err
pid-file=/data/3307/mysqld.pid
# 执行命令查看
tree /data
#步骤8：创建 MySQL 多实例的启动文件
vim /data/3306/mysql
vim /data/3306/mysql
# 3306 mysql 实例启动文件
# 内容如下
#!/bin/sh
####################################
# this scripts is created by oldboy at 2017-03-09
# site:http://www.oldboyedu.com
# blog:http://oldboy.blog.51cto.com
####################################
# init
prot=3306
mysql_user="root"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
mysqld_pid_file_path=/data/3306/3306.pid
start(){
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=/data/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 >/dev/null &
sleep 3
else
printf "MySQL is running...\n"
fi
}
stop(){
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit 1
else
printf "Stoping MySQL...\n"
mysqld_pid=`cat "$mysqld_pid_file_patn"`
if (kill -0 $mysqld_pid 2>/dev/null)
then
kill $mysqld_pid
sleep 2
fi
fi
}
restart(){
printf "Restarting MySQL...\n"
stop
sleep 2
start
}
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
printf "Usage: /data/${port}/mysql{start|stop|restart}\n"
# 3307 mysql 实例启动文件
# 内容如下
#!/bin/sh
####################################
# this scripts is created by oldboy at 2017-03-09
# site:http://www.oldboyedu.com
# blog:http://oldboy.blog.51cto.com
####################################
# init
prot=3307
mysql_user="root"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
mysqld_pid_file_path=/data/3307/3307.pid
start(){
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=/data/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 >/dev/null &
sleep 3
else
printf "MySQL is running...\n"
fi
}
stop(){
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit 1
else
printf "Stoping MySQL...\n"
mysqld_pid=`cat "$mysqld_pid_file_patn"`
if (kill -0 $mysqld_pid 2>/dev/null)
then
kill $mysqld_pid
sleep 2
fi
fi
}
restart(){
printf "Restarting MySQL...\n"
stop
sleep 2
start
}
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
printf "Usage: /data/${port}/mysql{start|stop|restart}\n"
# 执行命令检查
tree /data
# 步骤9：命令授权 mysql 用户
chown -R mysql.mysql /data
find /data -name msyql|xargs ls -l
find /data -name mysql|xargs chmod 700
find /data -name mysql -exec ls -l {} \;
# 步骤10：配置 MySQL 全局路径的方法
ls /application/mysql/bin/mysql
echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile
tail -1 /etc/profile
source /etc/profile
echo $PATH
# 步骤11：初始化 MySQL 多实例的数据库文件
cd /application/mysql/scripts
./mysql_install_db --default-file=/data/3306/my.cnf --basedir=/application/mysql --datadir=/data/3306/data --user=mysql
./mysql_install_db --default-file=/data/3307/my.cnf --basedir=/application/mysql --datadir=/data/3307/data --user=mysql
tree /data
# 步骤12：启动 MySQL 多实例数据库
mkdir -p /application/mysql-5.6.40/tmp
chown -R mysql.mysql /application/mysql/
/etc/init.d/mysqld stop
chkconfig mysqld off
chkconfig --list mysqld
/data/3306/mysql start
/data/3307/mysql start
netstat -lntup|grep 330
# 步骤13：配置 MySQL 多实例数据库开机自启动
echo "#mysql multi instances" >>/etc/rc.local
echo "/data/3306/mysql start" >>/etc/rc.local
echo "/data/3307/mysql start" >>/etc/rc.local
tail -3 /etc/rc.local
# 步骤14：登录测试
mysql -S /data/3306/mysql.sock
mysql -S /data/3307/mysql.sock
# 步骤15：MySQL 多实例数据库的管理方法
mysqladmin -u root -S /data/3306/mysql.sock password 'oldboy123'
mysqladmin -u root -S /data/3307/mysql.sock password 'oldboy456'
mysql -u root -p -S /data/3306/mysql.sock
mysql -u root -p -S /data/3307/mysql.sock
# 步骤16：如果再增加一个 MySQL 的实例
mkdir -p /data/3308/data
\cp /data/3306/my.cnf /data/3308/
\cp /data/3306/mysql /data/3308/
sed -i 's/3306/3308/g' /data/3308/my.cnf
sed -i 's/server-id = 6/server-id = 8/g' /etc/3308/my.cnf
sed -i 's/3306/3308/g' /data/3308/mysql
chown -R mysql.mysql /data/3308
chmod 700 /data/3308/mysql
cd /application/mysql/scripts ./mysql_install_db --default-file=/data/3308/my.cnf --datadir-/data/3308/data --basedir=/application/mysql --user=mysql
chown -R mysql.mysql /data/3308
egrep "server-id|log-bin" /data/3308/my.cnf
/data/3308/mysql start
sleep 5
netstat -lnt|grep 3308
netstat -lnt|grep 330
14.设置 MySQL 用户密码过期策略
ALTER USER 'xxx'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY; ALTER USER 'xxx'@'localhost' PASSWORD EXPIRE NEVER; ALTER USER 'xxx'@'localhost' PASSWORD EXPIRE DEFAULT; # 手动强制某个用户密码过期，让其登录时提示必须重设密码 ALTER USER 'xxx'@'localhost' PASSWORD EXPIRE;
15.使用 MySQL 8.0 新建的数据库用户无法使用旧的数据库工具连接，原因是 8.0 的新建的用户使用了新的加密方式，不再支持旧的，应该如下创建用户使用旧的加密方式
create user cdq@localhost indentified with mysql_nation_password by 'mysql'
16.MySQL 用户 lock
ALTER USER 'xxx'@'localhost' ACCOUTN LOCK; ALTER USER 'xxx'@'localhost' ACCOUNT UNLOCK; SELECT user, host, account_locked from mysql.user;
17.角色
/*等于一个组*/ CREATE ROLE app_readonly; CREATE ROLE app_readwrite; GRANT SELECT ON *.* to app_readonly; GRANT SELECT, INSERT, DELETE, UPDATE ON *.* TO app_readwrite; CREATE USER 'xxx'@'localhost' IDENTIFIED BY 'mysql'; CREATE USER 'xxxx'@'localhost' IDENTIFIED BY 'mysql'; CREATE USER 'xxxxx'@'localhost' IDENTIFIED BY 'mysql'; CREATE USER 'xxxxxx'@'localhost' IDENTIFIED BY 'mysql'; GRANT app_readonly TO 'xxx'@'localhost'; GRANT app_readonly TO 'xxxx'@'localhost'; GRANT app_readwrite TO 'xxxxx'@'localhost'; GRANT app_readwrite TO 'xxxxxx'@'localhost'; /*单纯查看权限只能查看到角色，具体该角色的权限无法查到*/ SHOW GRANTS FOR 'xxx'@'localhost'; /*如果想看，可以这样写*/ SHOW GRANTS FOR 'xxx'@'localhost' using app_readonly; /*取消角色*/ revoke app_readwrite from 'xxxxx'@'localhost';
18.数据库
/*
数据库
*/
SHOW DATABASES;
CREATE DATABASE xxx；
CREATE DATABASE IF NOT EXISTS xxx;
USE xxx;
/*
Alter database语句用来修改数据库的属性
1.Db_name可以不指定，如果不指定说明是修改当前数据库的属性
2.Character set代表修改数据库的默认字符集
3.Collate代表修改数据库的默认排序规则
4.如果修改了数据库的默认字符集或排序规则，那数据库中的所有存储过程和函数都需要重新创建一遍
*/
/*
Drop database语句用来删除数据库操作，既删除了数据库也删除了库里的所有表
语法为DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
删除数据库的操作将删除该数据库所在文件夹下如下格式的文件以及 db.opt文件
.bak .dat .hsh .mrg .myd .myi .trg .trn .cfg .db .frm .ibd .ndb .par
*/
drop database test2;
drop database if exists test4;
19.在没有添加 WHERE 条件，在 MySQL 中阻止 UPDATE 和 DELETE 语句的执行
################################################## # 1.阻止 update 设置 # sql_safe_updates 这个 MySQL 自带的参数就可以完美的解决我们的问题， # 并且该参数是可以在线变更的哦~当该参数开启的情况下， # 你必须要在UPDATE语句后携带WHERE条件，否则就会报出 ERROR。 ######################### # 1.1.数据库中查看 show variables like 'sql_safe_updates'; ######################### # 1.2.设置 # 不开启 set sql_safe_updates = 0; # 开启 set sql_safe_updates = 1; # 检查 show variables like 'sql_safe_updates'; ################################################## # 2.阻止 delete 设置 # 直接限制mysql删除 # 启动MySQL的时候加上参数 -U # -U, --safe-updates Only allow UPDATE and DELETE that uses keys. # 他的作用是防止执行delete的时候没带有条件语句，如果没加上 where 则语句不执行。
20.如何在 MySQL 中添加数据时可回滚
////////////////////////////////////////////////////////////////////////////////////////
// 1.数据库中查看
show variables like '%commit%';
////////////////////////////////////////////////
// 2.begin rollback commit
// 开始事务
begin;/start transaction;
// 回滚事务（撤销所有未提交的事务）
rollback;
// 提交事务
commit;
////////////////////////////////////////////////
// 3.改变 MySQL 的提交模式
// 禁止自动提交
set autocommit=0;
// 开启自动提交
////////////////////////////////////////////////////////////////////////////////////////
// 4.保存点
// 注：自动提交事务，就是不需要 commit 就能提交事务
// 设置自动保存点 s1
savepoint s1;
// 回滚到 s1
rollback to s1;
////////////////////////////////////////////////////////////////////////////////////////
// 实例
start transaction；
insert into tbl_name(col1, col2) values('1', '2');
rollback;
# 3.保存点
# 设置自动保存点 s1
savepoint s1;
# 回滚到 s1
rollback to s1;
////////////////////////////////////////////////////////////////////////////////////////
// 实例
create table test(id int);
// 开始事务，提交事务
// 所以1被插入
begin;
insert into test values(1);
commit;
// 先回滚事务，再提交事务
// 所以2没有被插入
begin;
insert into test values(2);
rollback;
commit;
// 先回滚到s1，再提交事务
// 所以3被插入，4没有被插入
begin;
insert into test values(3);
savepoint s1;
insert into test values(4);
rollback to s1;
commit;
// 回滚事务，事务未提交
// 所以5没有被插入
start transaction;
insert into test values(5);
rollback;
// 自动提交，再回滚
// 所以6会被插入
set autocommit=1;
insert into test values(6);
rollback;
// 禁止自动提交 再回滚
// 所以7未被插入
set autocommit=0;
insert into test value(7);
rollback;
// 查询
select * from test;
21.表
CREATE TABLE xxx(id INT, name VARCHAR(10), gender INT);
CREATE TABLE xxx(id INT);
CREATE TABLE IF NOT EXISTS xxx;
CREATE TABLE [其它库名].xxx(id INT);
/*
创建临时表，只为存放临时数据
临时表仅对本链接可见，另外的数据库链表不可见
当本链接断开时，临时表也自动被 DROP 掉
*/
CREATE TEMPORARY TABLE xxx(id INT);
/*复制一张新的表，不复制数据*/
CREATE TABLE xxx_copy like xxx;
/*复制一张表，但索引和主外键信息不会同步过来，但是数据会复制过来*/
CREATE TABLE xxx AS SELECT * FROM xxxx WHERE id = 1;
/*添加字段*/
ALTER TABLE xxx ADD name varchar(20);
/*
NOT NULL/NULL
提示：修改表时数据中不能存在 NULL
NULL 代表未知，不代表空，空是 ""
*/
ALTER TABLE xxx MODIFY name varchar(20) NOT NULL;
/*DEFAULT*/
ALTER TABLE xxx MODIFY name varchar(20) DEFAULT NULL;
/*
AUTO_INCREMENT
表示字段为整数或者浮点数类型的 value + 1 递增数值
value 为当前表中该字段的最大值，默认从 1 开始递增
一个表只允许一个自增字段，且该字段必须有 key 属性
不能含有 default 属性，且插入负值会被当成很大的正数
*/
CREATE TABLE xxx(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20));
/*
Column format 目前仅在 ndb 存储引擎的表上有，表示该字段的存储类型是 fixed，dynamic 或者是 default
Storage 目前仅在 ndb 存储引擎上有用
Constraint 表示为主键、唯一键、外键等约束条件命名，如果没有命名则 MySQL 会默认给一个
Primary key 表示该字段为主键，主键字段必须唯一，必须非空
Key/index 表示索引字段
Unique 表示该字段为唯一属性字段，且允许包含多个 null 值
Foreign key 表示该字段为外键字段
*/
/*修改主键*/
ALTER TABLE xxx ADD PRIMARY KEY(id);
/*创建普通索引*/
CREATE INDEX idx_1 ON xxx(name);
/*创建唯一索引*/
CREATE UNIQUE KEY idx_2 ON xxx(name);
CREATE UNIQUE KEY idx_3 ON xxx(name, gender);
/*创建外键*/
CREATE TABLE xxx(
sid int,
course_id int,
constraint for_1 foreign key (sid) references xxxx(id),
constraint for_2 foreign key (course_id) references xxxx(id)
);
/* 插入数据到表中，其基本语法有三种方式*/
desc xxx;
insert into xxx values(1, 'aaa');
insert into xxx set sid=2, sname='bbb';
insert into xxx select * from xxxx;
select * from xxx;
/*Value 中除了可以指定确定的数值之外，还可以使用表达式 expr*/
INSERT INTO tbl_name(col1, col2) VALUES(15, col1*2); ## 正确
INSERT INTO tbl_name(col1, col2) VALUES(col1*2, 15); ## 错误
/*插入多条*/
INSERT INTO tbl_name(col1, col2) VALUES(1, 2),(3, 4),(5,6);
INSERT INTO tbl_name VALUES(1, 2),(3, 4),(5,6);
/*
当目标表和 SELECT 语句中的表相同时
则会先将 SELECT 语句的结果存放在临时表中
再插入到目标表中（注意执行顺序）
*/
INSERT INTO tbl_name SELECT * FROM tbl_name;
/*
INSERT ON DUPLICATE KEY UPDATE 语句详解
当 INSERT 语句中使用 ON DUPLICATE KEY UPDATE 子句时，如果碰到当前
插入的数据违反主键或唯一键的唯一性约束，则 INSERT 会转变成 UPDATE 语
句修改对应的已经存在表中的这条数据，比如如果 a 字段有唯一性约束且已经含
有 1 这条记录，则以下两条语句的执行结果相同
*/
INSERT INTO tbl_name(a, b, c) VALUES(1,2,3) ON DUPLICATE KEY UPDATE c = c + 1;
UPDATE tbl_name SET c = c + 1 WHERE a = 1;
/*
上述例子中如果 b 字段也有唯一性约束，则与此语句的执行结果相同，
但一般应该避免出现对应多条的情况
*/
UPDATE tbl_name SET c = c + 1 WHERE a = 1 OR b = 2 LIMIT 1;
/*
实例
*/
update tbl_name set id = 10000 + id; // 没有问题
update tbl_name set id = 1 + id; // 可能会报错，因为会出现 id 冲突
update tbl_name set id = 1 + id order by id desc; // 这样就可以解决冲突问题
/*
update 语句有两种方式
1.单表修改
2.多表修改
多表修改不允许使用 order by 和 limit 子句
*/
update tbl_name set name = 'aaaa' where id = 1;
update tbl_name, tbl_namex set tbl_name.name = tbl_namex.name where tbl_name.id = tbl_namex.id
/*
delete语句,非常危险
删除前先使用SELECT查看数据是否是自己要删除的，然后再删除
不过现在项目中都不会真的删除数据，用一个标识设置数据表数据显示或者隐藏
*/
delete from tbl_name;
delete from tbl_name where sid = 1;
delete from tbl_name order by sid;
delete from tbl_name limit 1;
/*
多表删除两种方式
*/
delete t1, t2 from t1 inner join t2 inner join t3 where t1.id = t2.id and t2.id = t3.id;
delete from t1, t2 using t1 inner join t2 inner join t3 where t1.id = t2.id and t2.id = t3.id;
/*
对多表删除语句而言，如果想对表使用别名，则只能在table_references子句中使用，否则会报错
*/
DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id; /*正确*/
DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id; /*正确*/
DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2 WHERE a1.id=a2.id; /*错误*/
DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2 WHERE a1.id=a2.id; /*错误*/
/*
查询语句常规用法
1.Where子句代表只查询满足条件的表数据，如果没有where子句则代表查询表中所有的数据Where条件中不能使用
select_expr中定义的字段别名，因为语句执行顺序是where在select之前，所以where在执行时字段别名未知
当多个表中有相同的字段名，且需要查询出来时，需要使用tbl_name.column_name来显视指定要查询哪个表的字段
2.For update关键词代表将查询的数据行加上写锁，直到本事务提交为止
3.Lock in share mode关键词代表将查询的数据行加上读锁，则其他的链接可以读相同的数据但无法修改加锁的数据
4.HIGH_PRIORITY代表赋予读操作较高的操作优先级
5.Max_statement_time=N子句代表设置语句执行超时时间(毫秒)
6.Straight_join关键词代表强制优化器在表连接操作时按照语句中from子句中的表的顺序执行
7.Sql_big_result/sql_small_result通常是和group by/distinct一起使用，其作用是事先告诉优化器查询结果是大还是小，
以便优化器事先准备好将查询结果存放在磁盘临时表或者快速临时表中以便后续操作
8.Sql_buffer_result强制将查询结果存入临时表中
9.Sql_calc_found_rows关键词代表要求查询结果的同时计算结果的行数，以便后续通过SELECT FOUND_ROWS()直接获取行数
10.Sql_cache/sql_no_cache代表是否直接从query cache中获取查询结果
*/
Select * from students; /*查看表中所有数据*/
Select sid,sname from students; /*查看所有的sid和sname*/
Select sid,sname from students where sid=1; /*查看符合条件的数据*/
Select * from students order by sid; /*查看排序后的数据*/
Select sex,count(*) from students group by sex having count(*)>=2; /*查看分组的数据*/
Select * from students a inner join students2 b on a.sid=b.sid; /*查看两个表链接后的数据*/
Select sid as a,sname as b from students; /*字段使用别名的方法1*/
Select sid a,sname b from students; /*字段使用别名的方法2*/
Select * from test2.students;
/*当涉及的表不在当前的数据库时，需要使用db_name.tbl_name来指定表和所在的数据库名*/
Select * from students a inner join test2.students b on a.sid=b.sid;
/*Group by子句代表分组，通常和聚合函数配合使用，如最大值max, 最小值min, 平均值avg, 个数count，求和sum*/
insert into scores values(1,'english',88),(1,'chinese',86),(1,'math',90),(2,'english',95),(2,'chinese',84);
select sum(score) from scores;
select sid,count(*),max(score),min(score),avg(score),sum(score) from scores group by sid;
/*Order by和group by子句可以引用select_expr中的列，通过以下三种方式*/
SELECT college, region, seed FROM tournament ORDER BY region, seed;
SELECT college, region AS r, seed AS s FROM tournament ORDER BY r, s;
SELECT college, region, seed FROM tournament ORDER BY 2, 3;
/*Order by子句表示查询结果按照顺序排列，默认是升序排列，可以指定DESC表明按照降序排列*/
select * from scores order by sid desc,score;
/*报错,原因是where是在group by之前执行的,所以导致语句还没有根据dept_id分组的时候where已经执行了*/
select dept_id, count(*) from students where count(*) >= 2 group by dept_id;
/*改成如下*/
select dept_id, count(*) from students where group by dept_id having count(*) >= 2;
/*Having子句一般是跟在group by子句之后，代表限制分组之后的结果*/
SELECT user, MAX(salary) FROM users GROUP BY user HAVING MAX(salary) > 10;
select sid, avg(score) from scores group by sid having avg(score) > 88;
/*Limit子句用来限制查询结果的条数，其后可以带两位>0的整数，第一位代表offset，第二位代表取多少行*/
SELECT * FROM tbl LIMIT 5; /*Retrieve first 5 rows，等同于select * from tbl limit 0,5*/
SELECT * FROM tbl LIMIT 5,10; /*Retrieve rows 6-15*/
/*
ALL/Distinct关键词代表是否将查询结果中完全重复的行都查询出来，
ALL是默认值代表都查询出来，指定distinct代表重复行只显示一次
*/
select distinct dept_id from students;
/*如果查询加多一个字段，就会产生组合，不重复就变成组合不重复*/
select distinct dept_id, gender from students;
/*
Select语句中的表连接
在MySQL中，join/inner join/cross join三者的意思是一样的
From子句后面还可以跟子查询，但子查询必须带别名
当inner join或者表之间用逗号隔开，且没有表之间的关联字段，则代表结果是两者的笛卡尔积
conditional_expr子句一般代表指定两个表之间的关联条件，而where条件中指定查询结果的筛选条件
STRAIGHT_JOIN和Join的用法大致相同，唯一不同是确保左表是先被读取的，以保证优化器的读取顺序
*/
SELECT * FROM students INNER JOIN scores ON students.sid=scores.sid;
SELECT * FROM students LEFT JOIN scores ON students.sid=scores.sid;
SELECT * FROM students LEFT JOIN scores USING (sid);
SELECT * FROM students LEFT JOIN students2 ON students.sid=students2.sid LEFT JOIN scores ON students2.sid=scores.sid;
/*没有关联条件,表示笛卡尔积*/
SELECT * FROM students, scores;
/*
Select…into语句
Select … into语句代表将查询结果存入定义的变量或者文件
SELECT ... INTO var_list将查询结果存入定义的变量
SELECT ... INTO OUTFILE将查询结果按照一定的格式写入到文件中
SELECT ... INTO DUMPFILE将查询结果以一行的格式写入到文件中，且只能写入一行
使用Select … into outfile 'file_name'时，文件会创建在本地服务器上，所以要确保你的用户能创建文件，
而且此file_name不能已经存在在服务器上以免覆盖其他文件
下面实例会报错
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
My.ini配置文件中添加secure_file_priv=/tmp/后重启再执行，成功
*/
SELECT sid,sname,sex INTO OUTFILE '/tmp/students.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM students;
/*
当使用存入变量方法是，需要保证查询结果返回一行，如果不返回数据则报no data错误，
如果返回多行则报Result consisted of more than one row错误，当返回行数不确定时，可以用limit 1强制只返回一行
*/
SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;
/*
Select语句中的union
1.Union用来将多个select语句的执行结果合并成一个结果
2.第一个select语句的column_name会被当做最后查询结果的列名，
接下来的每个select语句所一一对应的列应该和第一个语句的列的数据类型最好保持一致
3.默认情况下union语句会把最终结果中的重复行去掉，这和增加distinct这个关键词的作用一样，
如果使用union all则代表最终结果中的重复行保留
*/
select sid,sname from students
union
select sid,gender from students2;
Select sid,sname from students
Union all
Select sid,sname from students2;
/*如果相对union语句的最后结果做排序或者limit限制，则需要将每个select语句用括号括起来，把order by或limit语句放在最后*/
(Select sid,sname from students)
Union
(Select sid,sname from students2)
order by sid limit 2;
/*
Drop table语句用来删除一个或多个表操作，当然也可以删除临时表
Restrict/cascade两个关键词在5.7版本中没用
*/
drop table students2;
drop table if exists students2;
/*Rename table语句用来重命名一个或多个表名*/
RENAME TABLE old_table TO new_table;
/*当想让两个表名相互调换时，可以执行语句*/
RENAME TABLE old_table TO tmp_table, new_table TO old_table, tmp_table TO new_table;
/*Rename table能将表中的数据，索引，主键定义都自动转换到新表下，但视图和对原表分配的权限不能自动转换到新表，需要手动执行*/
rename table students to students_test;
show create table students_test;
/*原有视图查询失败*/
select * from v_students_female;
/*
truncate table语句
1.Truncate table语句用来删除/截断表里的所有数据
2.和delete删除所有表数据在逻辑上含义相同，但性能更快
3.类似执行了drop table和create table两个语句
*/
truncate table students_test;
select * from students_test;
/*alter table语句*/
Alter table … add [column_name]
Alter table … add constraint [name] unique [index/key] [name]
Alter table … add constraint [name] foreign key (column_name) references table_name(column_name)
Alter table … drop column [column_name]
Alter table … drop [index/key] [index_name]
22.视图
/*
Create view语句
Create view语句是指将某个查询数据的定义保留下来，以便随时调用
view本身不存储查询结果，只是一个定义
1.Or replace关键词表示当创建的视图已经存在时，执行替换命令
2.Select_statement子句则是创建视图的select语句，可以是从表中查询数据，也可以从其他视图中查询数据
3.当视图被创建之后，则其定义就已经固定不会再改变，比如一个视图是由select创建的，则后续对表增加的字段不会成为视图的一部分,而后续对表删除字段则会导致查询视图失败
4.创建的视图默认情况下是属于当前数据库的，当要创建到另外的数据库时则需要在视图名前面加上数据库名
*/
CREATE VIEW test.v AS SELECT * FROM test2.t;
/*指定字段名*/
CREATE VIEW v_today (today) AS SELECT CURRENT_DATE;
CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
create view v_students_male as select sid,sname from students where sex=0;
create view v_students_female as select sid,sname from students where sex=1;
select * from v_students_female where sid>1;
/*创建相同名称的视图会事变*/
create view v_students_male as select sid,sname,sex from students where sex=0;
/*添加or replace参数后，原视图被替换*/
create or replace view v_students_male as select sid,sname,sex from students where sex=0;
select * from v_students_male;
create view v_test as select * from students;
/*表增加一个字段*/
alter table students add test int;
/*查询视图依然是之前的三个字段结果*/
select * from v_test;
/*表删除原来的一个字段*/
alter table students drop column sex;
/*查询视图失败*/
select * from v_test;
/*
Order by子句在创建视图过程中是允许的，但当后续的查询视图的语句中有自己的order by子句时则会被忽略掉
视图在满足特定条件时是可以执行insert/update/delete语句的，条件就是视图中的每一行和视图对应的表中的每行数据都能一一对应起来
*/
insert into v_students_male values(3,'ddd',0);
update v_students_male set sname='eee' where sid=3;
delete from v_students_male where sid=3;
/*
Alter view语句用来修改视图的定义，本身的语法结构和createview相同，语句所起到的作用和create or replace view语句相同
*/
alter view v_students_male as select sid,sname from students where sex=0;
select * from v_students_male;
/*
Drop view语句用来删除一个或多个视图
同样，restrict/cascade两个关键词也是无效
*/
drop view v_students_male;
drop view if exists v_students_male;
23.索引
/*
Create index语句
1.Index_col_name可以包含一个字段，也可以包含多个字段（逗号隔开），如果包含多个字段，则表明此索引是复合索引
2.Unique index代表索引中的值不能有重复
3.Fulltext index只能创建在innodb和myisam存储引擎的char,varchar和text字段上
4.Index可以创建在包含NULL值的字段上
5.Key_block_size=value是在myisam存储引擎的表上指定索引键的block大小
*/
/*创建普通索引*/
create index idx_st_sname on students(sname);
/*创建复合索引*/
create index idx_st_union on students(sname,sex);
/*创建唯一索引*/
create unique index idx_st_sid on students(sid);
/*
Index_type代表创建索引的类型
1.InnoDB DTREE
2.MyISAM DTREE
3.MEMORY/HEAP HASH,DTREE
4.NDB HASH,DTREE
*/
/*Comment ‘string’代表可以为索引添加最长1024的注释*/
CREATE TABLE t1 (id INT);
CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';
/*
Drop index语句用来删除索引操作
删除tbl_name表中的名字叫index_name的索引
*/
drop index idx_st_sname on students;
24.开启bin-log日志
################################################## # 1.进入 MySQL 命令行界面,查看 bin-log 是否开启 show variables like 'log_bin'; ########################### # 2.如果没有开启 # 修改 my.cnf 文件，如果没有 my.cnf 或者是集成的环境，找到 my.ini，修改 my.ini 文件 [mysqld] log-bin = /usr/local/var/mysql/logs/mysql-bin.log expire-logs-days = 14 max-binlog-size = 500M server-id = 1 ########################### # 3.重启 MySQL ########################### # 4.检查，log_bin 是否等于 on show variables like 'log_bin'; show binlog events;
25.防止人为误操作MySQL数据库技巧
##################################################
# 案例：在若干年前，老男孩亲自遇到一个“命案”，老大登录数据库update一个记录，
# 结果忘了加where，于是悲剧发生了，这使得我对MySQL的增量恢复熟练度远超过其他的
# 知识点，同时也很在意对内的数据库安全，并且每次讲课讲到此处，都会给学生讲这个领导
# 的故事。
############################
# 1.mysql帮助说明
# 在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序就会拒绝执行
mysql --help|grep dummy
# 结果:
#
# -U, --i-am-a-dummy Synonym for option --safe-updates, -U.i-am-a-dummy FALSE
############################
# 2.指定-U登录测试
# 提示：不加条件无法删除，目的达到。
mysql -uroot -poldboy123 -S /data/3306/mysql.sock -U
delete from oldboy.student;
quit
############################
# 3.做成别名防止老大和DBA误操作
# 3.1.数据库命令窗口
alias mysql='mysql -U'
mysql -uroot -poldboy123 -S /data/3306/mysql.sock
delete from oldboy.student;
quit
# 3.2.命令窗口
echo "alias mysql='mysql -U'" >>/etc/profile
. /etc/profile
tail -1 /etc/profile
##############################
# 结论：
#
# 在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序拒绝执行
26.MySQL 常用管理基础知识
26.MySQL 常用管理基础知识
######################################################
# 单实例 MySQL 启动与关闭
######################################################
# 数据库自带的启动脚本
cp support-files/mysql.server /etc/init.d/mysqld
# 方法1
/etc/init.d/mysqld start
ss -lnt|grep 330
# 方法二
/etc/init.d/mysqld stop
mysqld_safe --user=mysql >/dev/null 2>&1 &
ss -lnt|grep 330
# 查看 MySQL 服务进程
ps -ef|grep mysql|gerp -v grep
######################################################
# 正确关闭单实例 MySQL 数据库
######################################################
# 方法1
/etc/init.d/mysqld stop
ps -ef|grep mysql|grep -v grep
# 方法2
mysqladmin -u root -p oldboy123 shutdown
ps -ef|grep mysql|grep -v grep
# 方法3，以上方法无法关闭
kill pid
killall mysqld
pkill mysqld
# 禁止使用下面方式，很可能会导致 MySQL 无法启动
killall -9 mysqld
kill -9 pid
######################################################
# 多实例 MySQL 启动和关闭
######################################################
# 方法1
mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &
# 方法2
mysqladmin -u root -p oldboy123 -S /data/3306/mysql.sock shutdown
######################################################
# MySQL 连接方式
######################################################
# 不用密码
mysql
mysql -u root
# 交互式输入密码可有效防止密码泄露
mysql -u root -p
# 明文
mysql -u root -p 'oldboy123'
# 多实例方式
mysql -u root -p -S /data/3306/mysql.sock
mysql -u root -p -S /data/3307/mysql.sock
# 异地远程登录
# 1.单实例
mysql -u root -p -h 127.0.0.1
# 2.多实例
mysql -u root -p -h 127.0.0.1 -P3306
mysql -u root -p -h 127.0.0.1 -P3307
######################################################
# 防止 MySQL 密码涉密的小妙招
######################################################
# 1.可以通过环境变量来强制 Linux 不记录敏感历史命令
HISTCONTROL=ignorespace
# 2.操作敏感的命令后及时删除命令行记录
history|tail -4
# 删除某一个
history -d 252
# 清楚所有记录
history -c
# 清除所有记录
>~/.bash_history
# 3.为带密码的启动脚本以及备份脚本等加 700 权限，用户名和组改成为 root
chmod 700 /data/3306/mysql
# 将密码写入 my.cnf 配置文件，使得执行备份命令不需要加密码
chmod 700 /server/scripts/bak.sh
# 4.把密码写入 my.cnf 配置文件并加 600 权限，用户和组改为 mysql
cp /application/mysql/my.cnf /etc/
# 配置文件开头添加如下三行，无须重启服务
# grep -A 2 client /etc/my.cnf
vim /etc/my.cnf
# 内容如下
[client]
User = root
Password = oldboy123
######################################################
# MySQL 连接提示设置
######################################################
# 1.修改登录，MySQL 下
prompt \u@oldboy \r:\m:\s->
# 2.配置文件修改登录提示符
# 在 my.cnf 配置文件的 [mysql] 模块下添加如下内容
# 内容如下
[client]
prompt=\\u@oldboy \\r:\\m:\\s->
# 3.多实例场景登录提示符说明
# 在 my.cnf 配置文件的 [mysql] 模块下添加如下内容
[client]
port = 3307
socket = /data/3307/mysql.sock
user = root
Password = oldboy123
prompt=\\u@oldboy \\r:\\m:\\s->
# 执行
mysql --default-extra-file=/data/3307/my.cnf -S /data/3307/mysql.sock
######################################################
# 退出 MySQL 数据库
######################################################
# MySQL 界面
exit
######################################################
# 查看 MySQL 帮助
######################################################
# MySQL 界面
help
help contents
help Data Definition
help CREATE DATABASE
######################################################
# MySQL 数据库用户安全策略介绍
######################################################
# 1.数据库不设置外网 IP
# 2.为 root 用户设置比较复杂的密码
# 3.删除无用的 mysql 库内的用户账号，只保留 root@localhost 以及 root@127.0.0.1
# 4.删除默认的 test 数据库
# 5.增加用户的时候，授权的权限应尽量最小，允许访问的主机范围最小化
# 6.登录命令行操作不携带密码，而是回车后输入密码，从 MySQL 5.6 版本起命令行携带密码会报警提示
# 下面是更加严格的做法
# 1.删除所有 mysql 中的用户，包括 root 超级用户
delete from mysql.user
# 这里的 root 可以保留，修改为其他用户也可以
# 2.增加 system 并将管理员用户提升为超级管理员，即与 root 等价的管理员用户，只是名字不同而已
grant all privileges on *.* to system@'localhost' identified by 'oldboy123' with grant option;
flush privileges;
# 3.对于带密码的文件或脚步权限，最好是文件用 600，脚本用 700，用户和组则用 root 或 mysql
######################################################
# 为管理员 root 用户设置及修改密码
######################################################
# 1.为 root 用户设置密码
# 单实例
mysqladmin -u root password 'oldboy123';
# 多实例
mysqladmin -u root password 'oldboy123' -S /data/3306/mysql.sock;
# 2.为 root 用户修改密码
# 2.1.Linux
mysqladmin -u root -p 'oldboy123' pasword 'oldboy';
mysqladmin -u root -p 'oldboy123' pasword 'oldboy' -S /data/3306/mysql.sock;
# 2.2.SQL
# 这种方法适合密码丢失后，通过 "--skip-grant-tables" 参数启动数据库
# 再对密码进行修改
update mysql.user set password=PASSWORD("oldboy123") where user='root' and host='localhost';
flush privileges;
# 2.3.SQL
# 本方法仅为修改当前用户密码
# 不适合通过 "--skip-grant-tables" 参数启动数据库，不然会报错
set password=password('oldboy');
flush privileges;
######################################################
# 找回 MySQL root 用户密码
######################################################
# 1.单实例
# 1.1.停止 MySQL 运行
/etc/init.d/mysqld stop
# 1.2.使用 --skip-grant-tables，修改完密码必须重启服务
mysqld_safe --skip-grant-tables --user=mysql >/dev/null 2>&1 &
ss -lnt|grep 330
# 1.3.修改密码
# 报错
set password=password('oldboy123')
# 可以
update mysql.user set password=PASSWORD("oldboy123") where user='root' and host='localhost';
flush privileges;
quit
# 1.4.重启服务
/etc/init.d/mysqld stop
######################################################
# 2.多实例
# 2.1.关闭多实例 3307 MySQL 服务，mysqladmin 的方式关闭必须使用密码
killall mysqld
# 或者
kill pid
# 2.2.启动数据库加 "--skip-grant-tables"
mysqld_safe --defaults-file=/data/3307/my.cnf --skip-grant-tables >/dev/null 2>&1 &
ss -lnt|grep 3307
# 2.3.登录
mysql -S /data/3307/mysql.sock
# 2.4.修改密码
update mysql.user set password=PASSWORD("oldboy123") where user='root' and host='localhost';
flush privileges;
# 2.5.重启服务
/data/3307/mysql stop
/data/3307/mysql start
ss -lnt|grep 3307
# 2.6.登录
mysql -S /data/3307/mysql.sock
mysql -u root -p oldboy -S /data/3307/mysql.sock
######################################################
# 特殊提示：
# 请在使用单实例数据库之前，停止掉多实例中的 3306 实例
# 如果想使用多实例 3306 实例及 3307 实例，则需要提前停止单实例数据库
######################################################
# SQL 分类
# 1.数据查询语言(DQL)
# 2.数据操作语言(DML)
# 3.事务处理语言(TPL)
# 4.数据控制语言(DCL)
# 5.数据定义语言(DDL)
# 6.指针控制语言(CCL)
######################################################
# MySQL 基本结构体系
# 应用程序在连接MySQL数据库时，首先要经过连接池技术，然后通过连接池进入SQL解析层，
# 经过 SQL 解析层的一系列处理及解析后，再进入到存储引擎层去查找数据，最后通过内存
# 或磁盘读取到数据，然后再逐级返回，最终将数据返回给应用程序或用户。
######################################################
# SQL 解析流程
# 1)当接收到SQL语句后，数据库首先会判断SQL语句的正确性、类型，然后根据不同类型，
# 由命令分发模块投递到相应的模块处理。如果是select语句，则首先会查找SQL高速缓存
# (query_cache);如果命中目标数据，则SQL不需要再做相应的解析执行操作，直接将请求
# 的数据返回客户端应用程序即可。
# 2)如果用户请求的数据未在高速缓存中命中，那么会进入SQL的解析过程。
# a)对于SQL层本身来说，其是无法直接读懂SQL语句的，不能直接执行。这时，就要靠SQL
# 层的解析器(Parser)来进行SQL的词法、语法分析，最终得出1个或多个SQL语句的执行计划
# b)得出执行计划之后，还不能直接使用，因为解析器可能给出了一条SQL语句的多种执行方
# 式，需要进行进一步的判断，那么哪条执行计划是最好呢？这件事情将交由查询优化器
# (Optimizer)去判断。优化器会根据自身的算法，找到代价最低(一般是有合理索引的那条)
# 的那个执行计划进行下发。(当然有些情况例外，不过其都是由优化器本身的算法来决定的)
# c)这时我们已经找到合适的执行计划了，是否就可以执行查询了呢？其实SQL层中还有其它
# 相关的模块控制,例如,是否有权限去执行,执行是否需要等待等。
# d)经过SQL层的逐层处理，SQL终于被执行，然后将执行后的结果，投递到下层的存储引擎
# 接口，最终经过存储引擎，获取到磁盘数据文件上的数据。
# e)SQL执行完成后，会将此次获取的数据更新到查询缓存(若有特定情况则不会更新)
######################################################
# DDL 语句之管理数据库
help show;
show create database oldboy\G;
create database oldboy;
create database oldboy_gbk default character set gbk collate gbk_chinese_ci;
create database oldboy_utf8 default character set utf8 collate utf8_general_ci;
# 编译时指定特定的字符集
cmake . -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
# 提示: 使用 MySQL 二进制包安装的数据库字符集默认为 latin1
# 根据公司开发的程序确定字符集(建议选择UTF8，移动互联网环境下可选utf8mb4)
# 显示数据库
show database;
show database like 'oldboy';
# 切换数据库
use database;
# 查看当前管理员所在的库名
select database();
# 查看数据库包含的表信息
show tables from oldboy;
show tables from mysql like 'db%';
# 删除数据库
drop database;
######################################################
# DDL&DCL 语句之管理用户
#
# 查看当前数据库的用户列表
select user, host from mysql.user;
# 创建数据库用户
create user blog@localhost indentified by 'blog123';
select user, host from mysql.user;
create user bbs@'172.16.1.%' indentified by 'bbs123';
select user,host from mysql.user where user='bbs';
# 创建后测试登录
mysql -u blog -p blog123
mysql -u bbs -p bbs123
# 删除数据库用户
select user, host from mysql.user where user = 'blog';
drop user 'blog'@'localhost';
select user, host from mysql.user where user = 'blog';
flush privileges;
# 如果使用drop删除不了用户，则很可能因为用户或主机部分是特殊字符或大写内容等，此时可以使用下面的方式删除
select user, host from mysql.user where user='bbs';
delete from mysql.user where user='bbs' and host='172.16.1.%';
flush privileges;
select user, host from mysql.user where user='bbs';
# 授权数据库用户
help grant
grant all privileges on oldboy.* to 'test'@'localhost' identified by 'test123';
show grants for 'test'@'localhost';
show grants for root@localhost;
# 赋予权限跟root一样
grant all privileges on *.* to 'system'@'localhost' identified by 'system123' with grant option;
# 允许创建代理用户
grant proxy on ''@'' to 'system'@'localhost' with grant option;
show grant for 'system'@'localhost';
# 授权局域网的主机远程连接数据库
# 方法1
grant all on *。* to 'test'@'172.16.1.%' identified by 'test123';
# 方法2
grant all on *.* to test@'172.16.1.0/255.255.255.0';
# 方法3
create user 'test'@'172.16.1.%' identified by 'test123';
grant all on *.* to 'test'@'172.16.1.0/255.255.255.0'
# 最后记得flush
flush privileges;
# 取消test用户的只读权限(select)
revoke select on oldboy.* from 'test'@'localhost';
#######################
# 1.企业中grant授权权限问题说明
#
# 工作授权时，授权用户应尽量授权为最小的满足业务需求的权限，而不是直接授权为"all privileges"
# 库不能用 "*.*",而应用 "oldboy.*" 格式具体到库
# 主机不能用%,而应用内网IP段,即 '172.16.1.%'格式
grant select,insert,update,delete on oldboy.* to 'test'@'172.16.1.%' identified by 'test123';
#######################
# 2.博客、CMS、BBS等产品的数据库授权
#
# 在安装期间除了 select、insert、update、delete这4个权限之外，还可能还需要create、drop等比较危险的权限。
# 针对这种情况，需要建库、建表、授权
grant select.insert,update,delete,create,drop on blog.* to 'blog'@'172.16.1.%' identified by 'blog123';
# 生成数据库、表后，可以使用revoke命令收回create、drop授权
revoke create, drop on `blog`.* from 'blog'@'172.16.1.%';
show grants for 'blog'@'172.16.1.%';
#######################
# 2.生产环境针对主库(写为主读为副)用户的授权策略
# 2.1.如果是单机
grant all privileges on `blog`.* to 'blog'@'localhost' identified by 'blog123';
# 如果应用程序服务器和数据库服务器不在一个主机上
grant all privileges on `blog`.* to 'blog'@'172.16.1.%' identified by 'oldboy123';
# 由于工作中异机环境比较多，因此下面都是针对异机情况进行说明的
# 下面的命令为严格授权，使用该命令虽然重视了安全，但却忽略了方便
grant select, insert, update, delete on `blog`.* to 'blog'@'172.16.1.%' identified by 'blog123';
#######################
# 4.生产环境从库(只读)用户的权限
grant select on `blog`.* to 'blog'@'172.16.1.%' identified by 'blog123';
#######################
# 5.生产环境主从库高级授权策略
#
# 1.使用简单方法(最专业)
# 用户和密码相同，只有IP不同
# 2.配置简单方法
# 用户不同，IP不同
#######################
# 6.生产环境下具体授权
#
# 主库授权
grant select, insert, update, delete on `blog`.* to 'blog'@'172.16.1.%' identified by 'blog123';
# 从库授权
# 从库除了做select的授权之外，还可以加read-only等只读参数，严格控制Web用户写从库
grant select on `blog`.* to 'blog'@'172.16.1.%' identified by 'blog123';
#######################
# 7.生产环境下，主从库读写分离授权难点与解决方案
#
# 若主从库的mysql库和表是同步的，则会无法针对同一个用户授权不同的权限。
# 主库授权后会自动同步到从库上，导致从库的授权只读失败。那么这种情况该怎么办呢？
#
# 解决方法如下
# 1.取消数据库中mysql库的同步功能
# 2.授权主库权限后，从库执行收回增删改权限，只保留查的权限
# 3.不在授权上控制增删改，而是用read-only参数控制普通用户更新从库。注意，read-only参数对超级用户无效
#######################
# 8.授权不规范导致的生产血案
#
# 实例：
# 生产环境下运维人员授权用户all权限，导致开发人员通过该用户自行修改了表结构，造成服务出问题，最后把锅甩在了运维人员身上。
# 运维对比表结构(对比生产数据和备份数据)发现了问题，最后告诉开发人员，把字段修改回去，服务好了。
#
# 启发：生产环境下尽量不要给开发人员select以外的权限，对于网站的连接账号不要授权select、insert、update以外的权限。
# 对别人仁慈，就是对自己的岗位和公司最大的背叛
########################
# 查看用户及授权
select user, host from mysql.user;
show grants for 'blog'@'172.16.1.%';
######################################################
# DDL 语句之管理表
########################
# 1.建立表
create table student(
id int(4) not null,
name char(20) not null,
age tinyint(2) not null default '0',
dept varchar(16) default NULL
);
#
create table student(
id int(4) not null,
name char(20) not null,
age tinyint(2) not null default '0',
dept varchar(16) default NULL
) engine=innodb default charset=utf8;
#############
# 1.1.查看表, 5.1之前默认引擎 MyISAM，5.5.5之后默认引擎 InnoDB
use oldboy;
show create table student\G;
desc student;
#############
# 1.2.CHAR和VARCHAR的差别小结
# char类型是定长，不够的在右边用空格补全，这会浪费存储空间，以此列为查询备件时，速度更快，多数系统表的字段都是定长
# varchar类型是变长，节省存储空间，以此列为查询条件时速度较慢
use sns;
set names gbk;
create table subject_comment_manager (
subject_comment_manager_id bigint(12) not null auto_increment comment '主键',
subject_type tinyint(2) not null comment '素材类型',
subject_primary_key varchar(255) not null comment '素材的主键',
subject_title varchar(255) not null comment '素材的名称',
edit_user_nick varchar(64) default null comment '修改人',
edit_user_time timestamp null default null comment '修改时间',
edit_comment varchar(255) default null comment '修改的理由',
state tinyint(1) not null default '1' comment '0代表关闭，1代表正常',
primary key ('subject_comment_manager_id'),
key idx_primarykey (subject_primary_key(32)),
key idx_subject_title (subject_title(32),
key index_nick_type (edit_user_nick(32),subject_type)
) engine=innodb auto_increment=1 default charset=utf8;
########################
# 2.查看表结构
#
use oldboy;
desc student;
show columns from oldboy.student;
########################
# 3.更改表名
#
rename table student to test;
alter table test rename to student;
show tables;
#######################
# 4.增、删、改表的字段
#
create table test(
id int(4) not null auto_increment,
name char(20) not null,
primary key(id)
) engine=innodb default charset=utf8;
#
desc test;
alter table test add sex char(4);
desc test;
alter table test add age int(4) after name;
desc test;
alter table test add qq varchar(15) first;
desc test;
alter table test drop qq;
alter table test drop age;
alter table test add age tinyint(2) first, add qq varchar(15);
##########
# 生产环境
#
alter table etiantion add fristphone_url varchar(255) default null comment '第一张图片url';
alter table basic add adhtml_top varchar(1024) default null comment '顶部广告html',
addhtml_right varchar(1024) default null comment '右侧广告html';
alter table ett_ambiguity change ambiguity_stat ambiguity_state tinyint comment '状态,默认1=正常,0=失效';
alter table ett_photo modify column photo_description varchar(512) character set utf8 collate
utf8_general_ci not null comment '描述' after photo_title;
alter table test modify age char(4) after name;
alter table test change age oldboyage char(4) after name;
#######################
# 5.创建和删除索引
# 注意:请尽量选择在业务流量低谷时建立索引
# 5.1.方法1
alter table student drop index index_name;
desc student;
alter table test add index index_name(name);
# 5.2.方法2
create index index_qq on test(qq);
desc test;
alter table test drop index index_name;
#######################
# 6.查看建表语句
show create table test\G;
#######################
# 7.删除表
show tables from oldboy;
drop table student;
show tables from oldboy;
######################################################
# DML 语句之管理表中的数据
#######################
# 1.往表中插入数据
insert into test(id, name) values(1, 'oldboy');
insert into test(name) values('oldgirl');
insert into test(id, name) values(null, 'zhangsan');
insert into test values(3, 'inca');
insert into test values(4, 'inca'), (5, 'kaka');
# 备份数据
mysqldump -u root -p oldboy123 -B oldboy > /opt/bak.sql
egrep -v "#|\/|^$|--" /opt/bak.sql
#######################
# 2.查询表中数据
select * from test;
select * from oldboy.test;
select user, host from mysql.user;
select * from test limit 2;
select * from test limit 0, 3;
select * from test where id = 1;
select * from test where name = 'oldgirl';
select * from test where id = 2 and name = 'oldgirl';
select * from test where id > 2 and id < 5;
select * from test where id > 3 or id < 2;
select id, name from test where id > 3 order by asc;
select id, name from test where id > 3 order by desc;
#######################
# 3.修改表中数据
update test set name = 'xiaoting' where id = 3;
#######################
# 4.修改数据导致的事故案例和解决方案
# 修改数据导致的事故案例和解决方案
# 错误执行了不带条件的更改表记录
update test set name = 'xiaoting';
# 3.1.首先查看数据库
select * from test;
# 3.2.用备份的数据进行恢复
drop table test;
source /opt/bak.sql
#######################
# 5.删除表中数据
delete from test where id = 1;
delete from test where name = 'oldboy';
#######################
# 6.清空表中的数据
#
truncate table test;
############
# truncate和delete的区别
# 1.truncate与不带where子句的delete语句功能相同;两者均删除表中的全部行，但truncate比delete速度更快
# 2.truncate通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放，因此使用的系统和事务日志资源更少
# 3.delete语句每次删除一行，并且会在事务日志中为所删除的每一行记录一项
######################################################
# MySQL数据库备份与恢复基础实践
#######################
posted on 2020-03-27 13:45 herisson_pan 阅读(19) 评论(0) 收藏 举报
浙公网安备 33010602011771号