mysql

二进制安装

# 关闭防火墙、selinux
[root@mysql57:~]# getenforce
[root@mysql57:~]# systemctl status firewalld.service 
[root@mysql57:~]# rpm -qa|grep mariadb
[root@mysql57:~]# yum -y remove mariadb-libs

# 创建mysql用户和相关目录
[root@mysql57:~]# groupadd mysql
[root@mysql57:~]# useradd -r -g mysql -s /sbin/nologin mysql
# 下载安装
[root@mysql57:~]# mkdir /soft/src -p && cd /soft/src
[root@mysql57:/soft/src]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
[root@mysql57:/soft/src]# tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz  -C /soft
[root@mysql57:/soft]# ln -s /soft/mysql-5.7.30-linux-glibc2.12-x86_64/ /soft/mysql

# 创建初始化⽬录以及数据库数据⽬录
[root@mysql57:/soft]# mkdir /soft/mysql/{mysql-file,data}
[root@mysql57:/soft]# chown mysql.mysql /soft/mysql/

# 初始化数据库
[root@mysql57:/soft]# /soft/mysql/bin/mysqld --initialize --user=mysql --basedir=/soft/mysql --datadir=/soft/mysql/data
# 没有初始密码
/soft/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/soft/mysql --datadir=/soft/mysql/data

# 初始化数据库会告诉默认登陆账户与密码
[root@sql ~]# egrep 'temporary password is generated' /var/log/mysqld.log
2023-05-21T08:17:39.165590Z 1 [Note] A temporary password is generated for
root@localhost: Ug,R)avY8wje
# 使⽤ssl连接, 初始化后重新授权⽬录权限[如不需要可忽略]
[root@mysql57:~]# /soft/mysql/bin/mysql_ssl_rsa_setup --datadir=/soft/mysql/data/
[root@mysql57:~]# chown -R mysql.mysql /soft/mysql/

# 创建配置文件
[root@mysql57:~]# cp /etc/my.cnf /etc/my.cnf_bak
[root@mysql57:~]# vim /etc/my.cnf
[mysqld]
basedir=/soft/mysql
datadir=/soft/mysql/data
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
socket=/tmp/mysql.sock

# 开机自启
[root@mysql57:~]# cp /soft/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@mysql57:~]# chkconfig --add mysqld
[root@mysql57:~]# chkconfig mysqld on

# 修改安装目录和数据存放目录
[root@mysql57:~]# sed -i '/^basedir=/cbasedir=\/soft\/mysql' /etc/init.d/mysqld
[root@mysql57:~]# sed -i '/^datadir=/cdatadir=\/soft\/mysql\/data' /etc/init.d/mysqld

# 启动
[root@mysql57:~]# /etc/init.d/mysqld start


# 检查进程
ps aux|grep mysql
# 检查端口
lsof -i :3306

# 添加环境变量
[root@mysql57:~]# echo "export PATH=$PATH:/soft/mysql/bin" >> /etc/profile
[root@mysql57:~]# source /etc/profile

登录数据库

[root@mysql57:~]# mysql -uroot -p'DQ_lmebqX6-w'
# 修改密码
mysql> alter user root@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql基础

DQL

# 执行顺序：
from --->where --->group by --->having --->select --->order by --->limit
# 条件查询
> = < between..and..（闭区间，左小右大） is null、and、or、in(具体值不是区间)、not、like
# 排序order by
# 单行处理函数
lower upper substr contact length...
# 多行处理函数
count sum avg max min
# 连接查询（内连接、外连接、多表、全连接）
join on
# 子查询
select 、where、from
# union合并结果集（效率高，但需要结果集的列数相同）
# limit分页查询

DML

# insert
insert into 表名(字段名1，字段名2，字段名3，…) values(值1，值2，值3，…);

# delet（注意：没有条件限制会导致所有数据全部更新。）
delete from 表名 where 条件;
# delete 语句删除数据的原理：
#	表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！
# 	缺点：删除效率比较低
#	优点：支持回滚，数据可以恢复

# update（注意：没有条件限制会导致所有数据全部更新。）
update 表名 set  字段名1=值1，字段名2=值2，字段名3=值3…where条件;

触发器tigger（监控DML语句）

# 创建
create tigger 触发器名 触发时间 触发事件 on 表 for each row 触发器执行语句（DML语句或存储过程）;

# 查看触发器
show tiggers\G

# 删除
drop tigger 触发器名;

DDL

# create
create table 表名(字段名1 数据类型，字段名2 数据类型，字段名3 数据类型);
create table emp2 as select * from emp;//快速创建一张表

# drop
drop table if exists表名； //即使这张表不存在也不会报错

# alter
alter user root@'localhost' identified by '123456';//修改密码
mysqladmin -uroot -p密码 password'新密码'//shell修改

TCL

# 事务transaction：多条DML语句同时成功或者同时失败
# 特性：原子性、一致性、隔离性（读未提交、读已提交、可重复读、系列化）、持久性
# 查看隔离级别
select @@tx isolation
# 设置隔离级别
set global tranction isolation level 隔离级别;

# 事务提交commit
# 事务回滚rollback

DCL

# 授权grant（all所有权限；单独权限[select，update，insert，delete]）
grant all privileges on 数据库.* to 用户@'172.16.1.%' identified by '密码'; 
flush privileges;//刷新权限
grant select,update(字段) on 数据库.表 to 用户@'ip' identitfied by '密码';

# 撤销权限revoke(5.6需要先收回权限，再删除用户)
revoke all privileges 用户;
drop user 用户@'%';//删除用户

存储引擎

索引index

# 创建索引
create index 索引名 on 表名(字段);
create table 表名(字段1,字段2,..unique index 索引名 (字段));//唯一索引
create table 表名(字段1,字段2,..fulltext index 索引名 (字段));//全文索引

# 删除索引
drop index 索引名 on 表名;

# 查看一个sql语句是否使用了索引进行检索？
explain select * from 表名 where 字段='value';

# 索引失效
模糊查询、只有一边的or条件字段使用索引、复合索引、where当中索引参加了运算、where当中索引使用了函数

视图view

# 创建（注意：只有DQL语句才能以view的形式创建）
create view 视图名 as select * from 表名; //as后面只能是DQL语句，但是创建完成后，可以对视图进行增删改查
# 删除
drop view 视图名;
# 修改
alter view 视图名 as DQL语句;

存储过程

# 存储过程：一组预编译的sql语句；存储过程保存在mysql.proc表中
# 创建
create procedure 存储过程名 DQL语句;//无参
create procedure 存储过程名(in 参数名 数据类型) DQL语句;//有参

# 查看存储过程列表
show procedure status\G

# 查看存储过程定义
show create procedure 存储过程名;

# 调用
call 存储过程名(参数);//无参数括号可以省略

# 删除
drop procedure 存储过程名;

数据库日志管理

作用

1. 排查故障
2. 性能优化
3. 安全审计
4. 统计分析
5. 数据备份与恢复

mysql日志管理

错误日志

# 错误⽇志 error log 记录 mysqld 服务进程启动/关闭或运⾏过遇到的错误⽇志
# 配置文件中配置
[root@mysql57:~]# grep log-error /etc/my.cnf
log-error=/var/log/mysqld.log

# 创建对应目录与日志文件，并授权
[root@mysql57:~]# mkdir -p /log/mysql
[root@mysql57:~]# touch /log/mysql/err.log
[root@mysql57:~]# chown -R mysql.mysql /log/mysql

# 重启生效
[root@mysql57:~]# systemctl restart mysqld

# 查看错误日志
[root@mysql57:~]# egrep -i "error" /log/mysql/err.log 

# 终端查看
mysql> show variables like "log_error%";
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| log_error           | /var/log/mysqld.log |
| log_error_verbosity | 3                   |
+---------------------+---------------------+
2 rows in set (0.01 sec)

查询日志

general query log 记录客户端连接信息和执⾏的所有 SQL 语句信息, 默认关闭

# 开启general日志
[root@mysql57:~]# head -3 /etc/my.cnf 
[mysqld]
general_log=ON
general_log_file=/log/mysql/select.log
# 创建目录和文件并授权
[root@mysql57:~]# mkdir -p /log/mysql
[root@mysql57:~]# touch /log/mysql/select.log
[root@mysql57:~]# chown mysql:mysql /log/mysql/select.log
# 查看
[root@mysql57:~]# cat /log/mysql/select.log
# 终端查看
mysql> show variables like "general_log%";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| general_log      | ON                    |
| general_log_file | /log/mysql/select.log |
+------------------+-----------------------+
2 rows in set (0.00 sec)

//临时开启查询⽇志
mysql> set global general_log = On;
//临时关闭查询⽇志
mysql> set global general_log = Off;

慢查询日志

慢查询日志 slow query log 记录执行过长的 SQL 语句, 可利用慢查询优化进行调整优化

long_query_time 参数设置为2秒,如果⼀个查询的执行时间超过2秒，它将被记录在慢查询日志中

注意 : 开启慢查询日志会对MySQL服务器的性能产生一定的影响，因此在生产环境中，应该谨慎地使⽤它

[root@mysql57:~]# cat /etc/my.cnf 
[mysqld]
# 慢查询日志
slow_query_log = on
slow_query_log_file=/log/mysql/slow.log
long_query_time=2
log_queries_not_using_indexes #没有启⽤索引的查询语句
[root@mysql57:~]# mkdir -p /log/mysql/
[root@mysql57:~]# touch /log/mysql/slow.log
[root@mysql57:~]# chown mysql:mysql /log/mysql/slow.log
[root@mysql57:~]# systemctl restart mysqld
# 查看
[root@mysql57:~]# cat /log/mysql/slow.log

二进制日志

二进制日志binary log记录数据被修改的相关信息

[root@mysql57:~]# cat /etc/my.cnf
[mysqld]
# 二进制日志
server-id = 135 # mysql服务器唯一标识
log-bin = /log/mysql/bin/sql_135
expire_logs_days = 30 #多少天天数后志将会被自动删除
[root@mysql57:~]# mkdir /log/mysql/bin
[root@mysql57:~]# chown -R mysql.mysql /log/mysql/
[root@mysql57:~]# systemctl restart mysqld
# 查看
[root@mysql57:~]#  mysqlbinlog -v /log/mysql/bin/sql_135

日志截断

恢复日志

数据库备份恢复

数据库备份需要考虑：数据一致性和服务可用性

数据库备份方式：

• 逻辑备份-备份DDL/DML/DCL语句（中小型数据库）
• 物理备份-直接复制数据库文件（大型数据库）

逻辑备份-mysqldump

# 完整备份（在恢复备份时暂停 binlog ⽇志记录）
1.停⽌数据库
2.删除破损数据库
3.重新初始化数据库
4.重置密码,否则⽆法恢复
5.恢复数据[新密码]
6.刷新授权[备份时密码]

# 增量备份
关闭binlog，恢复完整备份数据，找到增量数据的开始结束位置，进行恢复

物理备份-xtrabackup

通过协议连接到mysql服务端，然后读取并复制innodb底层的”数据块”，完成所谓的”物理备份”

[官网][(https://www.percona.com/downloads)]

# 安装
mkdir -p /soft/xtrabackup && cd /soft/xtrabackup
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.6/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm

# 完整备份
创建备份目录-->执行全备
恢复：停止数据库-->清理-->回滚innobackupex --apply-log-->恢复数据innobackupex --copy-back

# 增量备份
全备-->增备-->恢复全备-->恢复增备--恢复对应数据innobackupex --copy-back

# 差异备份
全备-->将差异备份应⽤⾄完整备份上-->恢复对应数据-->启动数据库-->针对起始点与结束点位置恢复

简单命令进行物理备份

停止数据库，将整个数据库文件打包
清理环境，导入打包备份，启动数据库，binlog恢复

生产备份

数据库主从复制

传统主从复制（基于异步）

主库数据备份、授权从库，拷贝到从库导入，关联change master to，启动slave

gitd复制（基于事务id复制）

解决了延迟和数据丢失的问题

主库数据备份、授权从库，拷贝到从库导入，从库配置文件开启gtid_mode = ON，enforce_gtid_consistency=1，关联change master to，启动slave

双主（gitd M-M复制）

主库开启gtid_mode = ON，enforce_gtid_consistency=1
从库授权

数据库多源复制

双主双从

数据库优化

本栏目推荐文章
• MySQL安装与配置(免安装版)
• MySQL - 生产环境线上，如何安全地给小表加字段？
• Mysql - Error 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'user.nickname' which is not functionally dependent on columns in GROUP BY clause
• [转]Linux 安装 MySQL 经验
• MySQL - 元数据锁MDL - 生产线上修改表结构，为何可能导致整个库崩溃？
• mysql备份-innobackupex备份mysql容器
• 【技术文档】mysql内存持续升高
• go链接mysql 和 数据库
• MySQL查看表占用空间大小
• mysql 数据库自行加密解密

mysqlmysql8 mysql5 mysql8 mysql docker mysql mysql_fdw mysql mysql-connector-java springboot connector windows server mysql 2022 explain mysql mysql-innodb mysql-bin