mysql基础运维

mysql四种语句：

数据定义语言DDL

主要作用是创建、删除和修改库表结构

库操作

对数据库的操作主要有

• 查看所有数据库show databaes;
• 切换数据库use databases;
• 创建数据库create database name;
• 删除数据库drop database name;

表操作

• 创建表create table 表名()
• 查看表show tables;
• 查看表的结构desc tablename;
• 查看创建表的SQL语句show create table tablename;
• 删除表drop table tablename;
• 修改字段信息alter table 表名 modify 字段名 varchar(20) not null;
• 修改字段名字alter table 表名 change name user_name varchar(50) not null;
• 添加字段alter table 表名 add password char(30) not null comment("password");
• 在指定位置添加字段after
• 删除某个字段alter table 表名 drop password;
• 修改表名alter table user rename (to) users （将user表改为users）

数据操作语言DML

数据操作语言主要是对表中的记录进行操作的语言：增删改

• 增insert into，其实就是向表中插入数据信息
  insert into user (字段1, 字段2, .....) values (值1, 值2, ....)
• 改update，就是更新表中的数据
  update user set 字段1=值1, 字段2=值2,.... where 条件
• 删除表，包含两种delete和truncate

• • delete 删除以后，如果重启msyql插入数据，id在之前的基础上增减
  • truncate 清空之后，id从1开始

数据操作语言DCL

数据控制语言主要是对数据中的root账户和普通账户的创建及权限进行操作

root账户

• 查看可登录MySQL的账户，默认进入mysql数据库中的user表
  select user,host from user;
• 修改密码
  update user set password=password("admin") where user="root";
  flush privileges;
• 忘记密码，跳过验证权限进行修改：skip -grant-tables

数据查询语言DQL

• 普通查询select * from user;， 查询所有
• 连接查询concat，还可以自定义列名或者带上连接符
• concat...as...
• concat_ws("==",字段1, 字段2,...)
  -模糊查询like
• 查询时进行排列：默认是升序asc，降序是desc
• 聚合函数：总记录count、总和sum、平均值/最大/最小值
• 分组查询group by
• 内连接、左右连接
• 联合查询union all
• 子查询语句1 where id in (语句2);
• 限制查询条数limit

日志

错误日志

错误日志是一个文本文件。错误日志记录了MySQL Server每次启动和关闭的详细信息以及运行过程中所有较为严重的警告和错误信息。可以用--log-error[=file name]选项来开启mysql错误日志，该选项指定mysqld保存错误日志文件的位置。

该日志是默认开启的，默认存放目录 /var/log/，默认的日志文件名为 mysqld.log 。查看日志位置:

二进制日志

二进制日志(binlog)记录了数据库中所有的DDL和DML操作

log_bin

该参数控制binlog文件存放的位置，用法log_bin=path/file_name。

binlog_format

该参数控制二进制日志文件的格式，可选格式有：

• STATEMENT:日志中记录的是执行的SQL语句，日志量小，但是复制会出现主从不一致的情况
• ROW:日志记录每一行数据的变化，日志量大，好处是主从复制数据不会出现不一致的情况，推荐使用这种方式
• MIXED:结合STATEMENT和ROW的日志格式。

expire_logs_days

该参数控制二进制日志保留天数，过了指定的天数后，日志将会自动删除。

max_binlog_size

该参数控制二进制日志文件的大小，当日志文件达到该参数指定的大小时，就会创建新的二进制日志文件。

binlog_do_db

该参数控制要保存二进制日志的数据库，如果有多个数据库，需要多次使用该参数，不能使用逗号分隔。

binlog_ignore_db

该参数控制要不保存二进制日志的数据库，如果有多个数据库，需要多次使用该参数，不能使用逗号分隔。

server_id

如果要在MySQL5.7版本中开启binlog，必须设置该参数,否则实例无法启动。

一个简单的参数配置如图：

作用

复制

MySQL Master端的二进制日志发送到slave端，slave端根据日志进行重做，达到主从复制的目的。

恢复

不管是使用mysqldump还是xtrabackup，我们都只能将数据库恢复到有备份的时刻，如果要将数据库恢复到任何时刻，则需要使用二进制日志。

binlog清理

方法1：执行“reset master”命令，该命令将删除所有的binlog日志，新日志编号从“000001”开始

方法2：执行“purge master logs to ‘hostname-bin.******’ ”命令，该命令将删除******之前的日志

执行“purge master logs before‘yyyy-mm-dd hh24:mi:ss’ ”命令，该命令将删除指定日志之前的日志

查询日志

用--log [=file_name]或-l [file_name]选项启动它。如果没有给定file_name的值，默认名是host_name.log。

show variables like '%general%';

慢查询日志

用--log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件.如果没有给出file_name值，默认未主机名，后缀为 -slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。

slow query_log-1 #开启慢查询日志

long query_tinh=2 #时间超过两秒为慢查询日志

主从复制

作用

将主库的数据变更同步到从库，从而保证主库和从库数据一致。

数据备份、失败迁移，读写分离，降低单库读写压力。

原理

1.Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。

2.从库读取主库的二进制日志文件 Binlog，写入到从库的中继日志 Relay Log。

3.slave重做中继日志中的事件，将改变反映它自己的数据

主库配置

登录mysql，创建远程连接的账号，并授予主从复制权限#创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务

CREATE USER itcast'@%' IDENTIFIED WITH mysql native password BY 'Root@123456'；

#为“itcast @'%' 用户分配主从复制权限

GRANT REPLICATION SLAVE ON ** TO itcast @'%'

通过指令，查看二进制日志坐标

show master 'status ;

字段含义说明:

file:从哪个日志文件开始推送日志文件

position:从哪个位置开始推送日志

binlog_ignore_db:指定不需要同步的数据库

从库配置

1.修改配置文件 /etc/my.cnf

#mysq[服务ID，保证整个集群环境中唯一，取值范围: 1 - 232-1，和主库不一样即可server-id-2

#是否只读,1 代表只读,0 代表读写

read-only=1

super-read-only=1 #root用户也只读

3.登录mysql，设置主库配置

CHANGE REPLICATION SOURCE TO SOURCE HOST='xxx.xxx',SOURCE_USER='xxx', SOURCE_PASSWORD='xxx', SOURCE_LOG_FILE='xxx', SOURCE_LOG_POS='xxx';

上述是8.0.23中的语法。如果mysql是8.0.23之前的版本，执行如下SQL:

CHANGE MASTER TO MASTER HOT=' x.x.,MASTER USER-',MASTER PASSWORD-x,MASTER LG FILE-',MASTER LOG POS-:

4.开启同步操作

#8.0.22之后start replica ;

#8.0.22之前start slave ;

IO线程和SQL线程

2.MySQL复制包括两部分，IO线程 和 SQL线程。

3.IO线程主要是用于拉取接收Master传递过来的binlog，并将其写入到relay log

4.SQL线程主要负责解析relay log，并应用到slave中

5.不管怎么说，IO和SQL线程都是单线程的，然后master却是多线程的，所以难免会有延迟，为了解决这个问题，多线程应运而生了。

6.IO多线程？

6.1 IO没必要多线程，因为IO线程并不是瓶颈啊

7.SQL多线程？

7.1 没错，目前最新的5.6，5.7，8.0 都是在SQL线程上实现了多线程，来提升slave的并发度

分库分表

随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈:

1，IO瓶颈:热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。请求数据太多，带宽不够，网络IO瓶颈

2.CPU瓶颈:排序、分组、连接查询、聚合统计等SOL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。

分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。

实现技术

读写分离

前提是主动复制

一主一从

balance参数

双主双从

主库设置

从库设置

从库关联主库

start slave;

启动从库

两个主库之间配置主从复制

配置 读写分离