MySQL 从了解到入门 （包含主从复制读写分离）傻瓜式流程

基础

存储引擎查看 show engines；
修改搜索引擎 alter table 表名 engine=引擎；
查看当前默认的存储引擎 show variables like ‘%storage_engine%';
查看mysql 服务器版本 select version();
创建时候指定引擎 create table 表名(字段 类型 约束) engine=引擎；
事务的四个特性 原子性 一致性 隔离性 持续性
创建库 create database 库名；
查看数据库 show databases；
进入数据库 use 库名；
查看当前所在数据库 select database()；
查看当前库下所有表格 show tables；

表相关

创建表 create table 表名(字段 类型(宽度) 约束条件,字段 类型(宽度) 约束条件)；
查看有那些表 show tables；
查看表结构 desc t1；
查看表里所有记录 select * from 表名
查看表内指定字段 select 字段,字段 from 表名
查看表状态 show table status like '表名'\G；
修改表名称 rename table 旧表名 to 新表名；
删除表 drop table 表名；
删除库 drop database 库名；

数据类型

整型 int bigint mediumint
浮点数类型 float double  flocal(5,3)  一共五位,小数占三位
定点数类型 DEC 以字符串形式存储 比浮点精确
字符串类型 char 定长字符串 varchar 可变长字符串 都可进行限制长度
枚举类型 enum 只能从枚举中选择数
日期类型 date 年月日 time 时间 datetime年月日时间

表完整性约束

primary key  标识该字段为该表主键 唯一且不可为空
foreign key 标识该字段为该表外键 实现表与表之间的联系
null 标识该字段可以为空
not null 标识该字段不能为空
unique key 标识该字段是唯一 可以为空 
auto_increment 自增
default 为字段设置默认值
unsigned 无符号 正数
设置为空不为空 alter table 表名 change 字段 字段 类型 not null 或 null；
唯一约束 alter table 表名 add unique key(指定字段）；
删除唯一约束 drop index 唯一约束字段 on 表名
设置主键 alter table 表名 add primary key(指定字段)；
删除主键 alter table 表名 drop primary key；
为表添加外键约束的语法格式
alter table 表名 add 外键约束特有名 foreign key（外键字段名）references 主表表名（主键字段名）；
删除外键约束 alter table 表名 drop foreign key 外键名；
添加自增 alter table 表名 modify 字段 类型 auto_increment；
删除自动增长 alter table 表名 change 字段 字段 类型 not null；
指定自增起始值 update user set auto_increment=起始值 
    alter table 表名 auto_increment=起始值
设置默认值 alter table 表名 alter column 字段名 set default 默认值；
删除默认值 alter table 表名 alter column 字段名 drop default；
创建表时指定约束 create table 表名(字段 类型 () 约束)

添加字段或者记录

        create table 表名(字段 类型,字段 类型,约束(指定字段))；
添加新字段 alter table 表名 add 字段 类型；
alter table 表名 add 添加的字段(类型) after name 把字段添加到name后面
alter table 表名 add 添加的字段(类型) first；把字段添加到第一位
修改字段和类型
alter table 表名 change 旧字段 新字段 类型；
删除字段 alter table 表名 drop 字段；
插入数据
添加一条记录 insert into 表名(字段,字段) values(数据，数据)；
添加多条记录 insert into 表名(字段,字段) values(数据，数据)，(数据，数据)；
更新记录 update 表名 set 修改的字段 where 给谁修改 例句：update b1 set id=3
where name="xiaoli"；
删除记录 delete from 表名 where 记录； 例句 delete from b1 where id=1；
删除所有记录 delete from 表名；
单表查询 select 字段名，字段名 from 表名 条件
统计 count（） 例句 select count（*）from 表名； 避免重复在select后加 distinct

复制表 复制表结构加记录

（主键外键索引 不会被复制）create table 新表 select * from 旧表；
复制单个字段和记录 create table 新表（select 字段，字段 from 旧表）；
多表查询 and和
select 字段，字段 from 表名 where 条件 and 条件；
select 字段，字段 from 表名 where 条件 or 条件；

查询

范围查询 select 字段，字段 from 表名 where 字段 between 数值 and 数值；not 给条件取反
关键字查询 select 字段，字段  from 表名 where 字段 in（数值，数值）； not加在in之前 给条件取反
排序查询 order by select 字段，字段 from 表名 order by 字段；从小到大排序 末尾加desc降序
limit 限制 末尾加该单词 跟上数字 只显示前几行 例句 limit4,5 从第四行开始 只显示五行
分组查询 group by
函数 max() 最大值   min()最小值  avg()平均值 now()现在的时间 sum()计算和 直接把字段插入括号之内查找
多表查询 内连接多张表内有存在着关联的两个字段组成一条行 只连接匹配到的行
    select 表名.字段，表名.字段 from 表名1,表名2 where 表名1.字段=表名2.字段；
外连接 在做多表查询时 所需要的数据 除了满足关联条件的数据外 还有不满足关联条件的数据 外连接分三种
左外连接 表A left join 表B on 关联条件 A表为主表
右外连接 表A right join 表B on 关联条件 B表为主表
全外连接  表A full join 表B on 关联条件 两张表的数据不管满不满足 都做显示

索引

在创建表时指定
create table 表名（字段 类型 约束，字段 类型 约束，索引 索引名 （字段（长度）））；
基于表结构创建
create 索引类型  索引名 on 表名(字段(长度))；
修改表结构创建
alter table 表名 add 索引 索引名(字段（长度）)；
查看表里的索引 show 索引 from 表名；
删除索引 drop 索引类型 索引名 on 表名；
修改表结构删除 alter table 表名 drop 索引类型 索引名

权限管理

mysql -u 用户名 -p密码 -h ip地址 -P端口号
修改用户登录限制范围 进入 use mysql 
update user set host = '指定范围' where user = '指定用户'；
flush privileges；刷新授权表
远程登录 -h 指定主机 -P指定端口 -u指定用户 -p指定密码 -e接sql语句 用；分开
创建用户 create user 用户名@'指定登录范围' identified by '密码'；
% 代表所有主机都可以远程登录  localhost 只允许本地用户登录
授权
grant 权限 on 库.表 to '用户名'@'登录范围'；
select user from mysql.user 查看有哪些用户
查看自己权限 show grants\G；
查看别人权限 show grants for 用户名@'登录范围'\G；
查看所有用户权限 登录到mysql 用  select * from mysql.user\G
移除权限 revoke 权限 on 数据库.数据表 from '用户'@'ip地址'
 整个数据库，使用 ON datebase.*；
 特定的表：使用 ON datebase.table；
修改密码
修改root自己密码 mysqladmin -uroot -p'密码' password '新密码'
方法二 登录进去 set password='新密码'
修改其他用户密码 set password for 用户@'ip地址'='新密码'
删除密码 drop user '用户名'@'IP地址'

查看密码复杂度
安装插件
mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
查看密码复杂度
mysql> show variables like 'validate%';
查看密码策略
mysql> select @@validate_password_policy;
查看密码的长度
mysql> select @@validate_password_length; 
策略：
- 0 or LOW 设置密码长度（由参数validate_password_length指定）
- 1 or MEDIUM 满足LOW策略，同时还需满足至少有1个数字，小写字母，大写字母和特殊字符
- 2 or STRONG 满足MEDIUM策略，同时密码不能存在字典文件（dictionary file）中
设置密码复杂度
mysql> set global validate_password_length=1; #设置密码长度为1
设置密码复杂性策略
mysql> set global validate_password_policy=LOW; 也可以是数字表示。#设置密码策略
备份        mysql 官方备份工具 ibbackup 收费 物理备份
        xtrabackup 开源免费 物理备份
        mysqldump 官方自带备份工具 开源免费 逻辑备份 速度较慢
        mysqlbackup  innodb 引擎的表该软件只能进行热备 非innodb引擎只能温备 物理备份 速度快 适合大规模数据使用

xtrabackup 备份

    完全备份流程 
    备份 innobackupex --user=用户 --password='密码'  /指定备份到哪个目录
    重演回滚 innobackupex --apply-log /备份目录/备份的文件
    恢复数据 innobackupex --copy-back /备份目录/备份的文件
    修改权限 chown mysql.mysql /mysql数据目录
    启动
    增量备份流程
    完整备份 innobackupex --user=root --password='密码' /指定备份到哪个目录
    备份增量一 innobackupex --user=root --password='密码' --incremental /备份目录 --incremental-basedir=/备份目录/基于哪个增量（为完全备份）
    备份增量二 innobackupex --user=root --password='密码' --incremental /备份目录 --incremental-basedir=/备份目录/基于前一次备份
    依次重演回滚
    innobackupex --apply-log --redo-only /备份目录/完全备份
    innobackupex --apply-log --redo-only /备份目录/完全备份 --incremental-dir=/备份目录/第二个备份
    innobackupex --apply-log --redo-only /备份目录/完全备份 --incremental-dir=/备份目录/第三个备份
    恢复数据 innobackupex --copy-back /备份目录/完全备份
    差异备份流程
    完整备份 innobackupex --user=用户 --password='密码' /备份目录
    差异备份 innobackupex --user=用户 --password='密码' --incremental /备份目录 --incremental-basedir=/备份目录/完全备份目录（参照对比差异）
    恢复完全备份 innobackupex --apply-log --redo-only /备份目录/完全备份
    恢复差异备份 innobackupex --apply-log --redo-only /备份目录/完全备份 --incremental-dir=/备份目录/某个差异备份
    恢复数据 innobackupex --copy-back /备份目录/完全备份

    mysql日志管理

    错误日志 log-error
    二进制日志（bin log）实现备份 增量备份 只记录数据改变
    中继日志（relay log）读取主服务器的binlog 一般用于主从数据恢复
    slow log 慢查询日志 进行调优
    ddl log 定义语句的日志
在mysql配置文件（vim /etc/my.cnf）中【mysql】插入
    log-bin=/指定日志保存目录
    server-id=指定id 指定id在复制的时候便于区分
在mysql里 输入 flush logs；会主动截断日志
解决日志binlog日志不记录insert语句
登录mysql后 设置binlog的记录格式
mysql> set binlog_format=statement;
然后，最好在my.cnf中添加
binlog_format=statement
修改完成之后重启

mysqlbinlog 跟日志名 查看日志

备份      

物理备份
热备 数据库处于运行状态进行备份 对应用基本无影响但是性能会有所下降 一般依赖于日志文件
冷备 备份数据文件 需要停机 是在关闭数据库时候进行的
温备 一般针对myisam的备份（myisam不支持热备） 备份的时候只读不写 数据库锁定表格

逻辑备份 备份的是建表 建库 插入 等操作所执行的sql语句 适用于中小型数据库 效率相对比较低
逻辑备份 远程备份语法 mysqldump -h 服务器 -u 用户名 -p 密码 数据库名 > 备份文件.sql
    本地备份语法 mysqldump -u 用户名 -p密码 数据库 > 备份文件.sql
-A 备份所有数据库       -B备份多个数据库         -F备份之前刷新的binlog日志
-d 不导出任何数据 只导出数据表结构     lock-tables 备份前锁定数据库表（所有）
single-transaction 保证数据一致性和服务的可用性        -f 强制导出 无论错误
    恢复数据语法 mysql -u用户 -p密码 指定库 < /调取备份文件
数据的导入导出 不含表结构   show variables like "secure_file_priv"  查询导入导出数据的目录
在配置文件里添加 secure_file_priv=指定目录 （该目录必须以mysql为属主属组）
重启 mysql
导出数据 select 字段 from 表 into outfile '导出到的目录和文件名'
导入数据 load data infile '数据存放的目录和文件' into table 导入导那个表
通过binlog恢复
开启 binlog 日志
在配置文件中 log-bin=指定日志存放目录
    server-id=1
    创建日志存放目录
    把日志存放目录的属组属主设为mysql
    重启mysqld

主从复制  

GTID方式主从复制 
master（主端）操作
进入配置文件 添加
server-id=1   （定义server id 必写）
log-bin = mylog 开启binlog日志 指定日志文件存放目录 
gtid_mode = ON 开启gtid
enforce_gtid_consistency=1 强制gtid
重启mysql
主服务器创建用户 grant replication slave,reload,super on *.* to '用户名'@'指定登录用户ip' identified by '密码'；
flush privileges；刷新授权表
replication slave 权限 拥有此权限可以查看从服务器，从主服务器读取二进制日志
super 权限 允许用户使用全局变量的set语句以及 change master语句
reload 权限 必须拥有reload 权限 才可以执行flush
slave（从端）操作
进入配置文件 添加
server-id=2
gtid_mode = ON
enforce_gtid_consistency=1 
master-info-repository=TABLE
relay-log-into-repository=TABLE 把秘密文件保存成表
重启 mysqld
在mysql里编辑 \e
change master to
master_host='主ip地址',
master_user='授权用户',
master_password='授权密码',
master_auto_position=1;自动获取对方日志 1为开启
；
start slave；启动slave角色
show slave status\G；查看状态
slave io running 为yes slave sql running 为yes 时 启动连接成功

主从复制binlog日志方式

给主服务器在配置文件内添加
log-bin=/var/log/mysql/mysql-bin 
server-id=1
创建并赋予日志目录属主属组为mysql
重启mysqld
在主服务器上mysql> show master status\G
    在弹出的条件里 position后面跟的是日志的当前节点
在从服务器上的配置文件里 操作
server-id=2 为了区分机器（必须跟主不同）
重启mysql
mysql>\e
change master to
master_host='主ip地址'，
master_user='用户名'，
master_password='密码'，
master_log_file='主日志目录'，
master_log_pos='主日志节点'，
；
start slave; 启动从机
show slave status\G slave_io_running 和 slave_sql_running 后为yes 成功

读写分离

使用mycat mycat为数据库中间件
安装jdk 将jkd压缩包解压到/usr/local/目录下
设置环境变量 mv /usr/local/jdk1.8.0_221/ java
添加帕斯变量 vim /etc/profile
    JAVA_HOME=/usr/local/java  指定目录
    exprot PATH=$JAVA_HOME/bin:$PATH   声明帕斯变量
下载mycat 并解压到 /usr/local/ 下
mycat 的相关配置文件 /usr/local/mycat/conf/server.xml
        定义用户以及系统相关的变量 如端口 或者 用户信息
mycat 的相关配置文件 /usr/local/mycat/conf/schema.xml
        定义逻辑库 表 分片节点等内容
读和写端操作
登入mysql 创建用户 create user 用户名@‘指定登录范围’ identified by‘密码’
    给用户授权 grant 权限 on 库.表 to '用户名'@‘ip范围’ 
    给用户修改可登录范围 登录 use mysql  update user set host = ‘指定范围’ where user = ‘指定用户’
    刷新权限列表 flush privileges
权限尽量给全部 读端写段必须指定同一个库    可登录范围必须能让mycat端登录
mycat端配置server.xml
vim /usr/local/mycat/conf/server.xml
    文件末尾 只保留一个<user> 并更改其中内容
    <user name="mycat用户名">
        <property name="password">密码</property>
        <property name="schemas">逻辑mycat库名</property>
    </user>
mycat端配置schema.xml
vim /usr/local/mycat/conf/schema.xml
 可以只保留以下内容
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="指定逻辑数据库" checkSQLschema="false" sqlMaxLimit="100" dataNode="指定节点 必须与下面的一致">
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="指定读写分离的数据库 一般需要数据库重名" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="操作标识 读写分离标识为3"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="写端的ip地址:3306" user="写端的授权用户"
                                   password="写端授权用户的密码">
                        <readHost host="hostS2" url="读端的IP地址:3306" user="读端的授权用户" password="读端授权用户的密码" />
                </writeHost>
        </dataHost>
</mycat:schema>

balance 属性
1 balance=“0” 不开启读写分离机制 所有操作都发送到当前可用的writehost上
2 balance=“1”  全部的 readHost 与  writeHost 参与 select 语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且 M1 与 M2 互为主备),正常情况下,M2,S1,S2 都参与 select 语句的负载均衡。
3 balance=“2” 所有读操作都随机的在 writehost readhost上
4 balance="3", 所有读请求随机的分发到 wiriterHost 对应的 readhost 执行,writerHost 不负担读压力, #注意 balance=3 只在 1.4 及其以后版本有,1.3 没有。

writeType 属性
负载均衡类型
1. writeType="0", 所有写操作发送到配置的第一个 writeHost,第一个挂了切换到还生存的第二个writeHost,重新启动后已切换后的为准.
2. writeType="1",所有写操作都随机的发送到配置的 writeHost,#版本1.5 以后废弃不推荐。

心跳检测 健康检查配置文件 <heartbeat>语句</heartbeat>
启动mycat /usr/local/mycat/bin/mycat start 检查是否启动成功 netstat -lntp | grep java 端口为8066 
在外面登录 mysql -h 指定mycat的ip -P 指定mycat的端口 -p指定密码 -u 指定用户