管理工具—连接池—SQL接口—分析器—优化器—查询缓存—存储引擎—硬盘
数据库基础语法
注：在数据库里面的库，在现实中是以文件目录形式存在的；
#查看当前数据库show databases；#查看数据库中有没有表show tables;#数据库密码验证策略：0,1,2；mysql> set global validate_password_policy=0; //只验证长度默认为1；mysql> set global validate_password_length=6; //设置密码长度为6；#MySQL中root修改密码mysql> alter user root@“localhost” identified by “密码”;#切换数据库mysql> use 数据库#查看当前所在库mysql> select database();#创建数据库mysql> create datebase 数据库名字;#删除数据库mysql> drop datebase 数据库名字;#在数据库里面创建表mysql> create table 表名（）;#查看表的结构mysql> desc user;#查看表的数据mysql> select * from 库名.表名;#插入表记录（信息）mysql> insert into 表(指定结构) values (表的结构，其中用“,”分割,字段用""表示);#修改表记录mysql> update 表名 set 结构=更换的值;#删除表记录mysql> delete from 表名;#删除表mysql> drop table 表;
数据库基础语法拓展
#查看建表时的信息mysql>show create table 表名;#无符号存储：mysql> ‘unsigned’#从给定值的集合中选择单个值mysql> enum#给定值中选取一个或多个mysql> SET#注Null:允不允许为空Key：索引标记Default：默认值Exter:额外设置——自增长：自动X++；add 添加字段名first 指定字段位置（第一）after 指定字段位置（在之后）modify 修改字段类型change 修改字段名drop 删除字段名rename 修改表名
新建指定名称的表
create table 库名.表名(字段名1 字段类型(宽度) 约束条件,字段名2 字段类型(宽度) 约束条件,......字段名N 字段类型(宽度) 约束条件);
MySQL语法
ALTER TABLE 表名 执行动作；
——Add 添加字段;
——Modify 修改字段类型、约束条件;
——Change 修改字段名;
——Drop 删除字段;
——Rename 修改表名;
添加新字段
基本语法ALTER TABLE 表名ADD 字段名 类型（宽度） 约束条件；可加 AFTER 字段名；或者 FIRST;FIRST 添加在行首AFTER 添加在指定字段后添加的字段默认在已有的字段后边
修改字段类型
基本用法ALTER TABLE 表名MODIFY 字段名 类型（宽度） 约束条件；可加 AFTER 字段名；或者 FIRST；注意事项：如果表中字段已经有值，修改的字段类型和字段里边的值不匹配，不允许修改需要修改的地方写信值，不需要修改的原样抄下来
修改字段名
基本用法ALTER TABLE 表名CHANGE 原字段名 新字段名 类型（宽度） 约束条件；修改字段名时，改变的写新的，不变的原样抄下来修改字段名时也可修改字段类型和约束条件，但如果类型与约束条件与字段的值发生冲突时不允许修改
删除表字段
ALTER TABLE 表名DROP 字段名；当字段中有多条值时，所有此字段的值都会被删除
修改表名
基本用法ALTER TABLE 表名RENAME 新表名；修改表名后，对应的表文件也会改变
索引
索引是什么？
- 索引的作用相当于书的目录，可以根据目录中的页码快速找到…”
- 在关系数据库中，索引是一种单独的、物理的对数 据库表中一列或多列的值进行排序的一种存储结构
索引的优点
索引的类型
INDEX 普通索引
UNIQUE 唯一索引
FULLTEXT 全文索引
PRIMARY KEY 主键
FOREIGN KEY 外键
INDEX普通索引
使用说明一个表中可以有多个INDEX字段字段的值允许有重复，且可以赋空值把查询频率高的字段设置为INDEX字段INDEX字段的KEY标志是MUL建表的时候指定索引字段INDEX（字段1），INDEX（字段2），INDEX（字段3）….查看表的索引信息SHOW INDEX FROM 表名\G；在已有的表中设置INDEX字段CREATE INDEX 索引名 NO 表名(字段名);删除指定表的索引字段DROP INDEX 索引名 ON 表名；当有索引名字的时候删除索引名字没有索引名字删除索引字段名（默认索引名与字段名相同）
PRIMARY KEY 主键
注意事项一个表中只能有一个primary key字段对应的字段值不允许有重复，且不允许赋NULL值主键字段的KEY标志是PRIprimary key通常与AUTO_INCREMENT连用当需要删除primary key时，须先删除AUTO_INCREMENT经常把表中能够唯一标识记录的字段设置为主键字段，如ID字段建表时指定PRIMARY KEY 字段PRIMARY KEY（字段名）；在已有的表中设置primary key 字段ALTER TABLE 表名 ADD PRIMARY KEY（字段名）；在给表字段添加主键时如果该字段的值有重复或空值，不允许添加移除表中的PRIMARY KEY 字段ALTER TABLE 表名 DROP PRIMARY KEY；删除主键后，NULL字段不会自动恢复成 YES建表时创建PRIMARY KEY 主键字段与 AUTO_INCREMENT 自增长连用AUTO_INCREMENT必须用在主键字段且字段类型必须是数值类型
foreign key 外键
什么时外键？
用来在两个表的数据之间建立链接
让当前表字段的值在另一个表中字段值的范围内选择
使用外键的条件
表的存储引擎必须是 innodb 类型
字段类型宽度要一致
被参照字段必须是索引类型的一种primary key
基本用法
FOREIGN KEY （表A字段名）
REFERENCES 表B （字段名）
ON UPDATE CASCADE //同步更新
ON DELETE CASCADE //同步删除
数据库导入导出：
导入
查看数据导入路径
mysql> show variables like "secure_file_priv";
创建导入文件的表时，表内的字段、类型、约束条件等取决于导入文件的内容来定
此实验以导入 /etc/passwd 文件为例：
cp /etc/passwd /var/lib/mysql-files/
mysql> create table 账号信息表( 用户名 varchar(40)
-> ,
-> 密码占位符 char(1),
-> UID smallint(6),
-> GID smallint(6),
-> 描述信息 varchar(20),
-> 家目录 varchar(200),
-> 解释器 varchar(50));
基本用法
LOAD DATA INFILE “目录名/文件名”
INTO TABLE 表名
FIELDS TERMINATED BY “分隔符”
LINES TERMINATED BY “\n”;
注意事项
字段分隔符要与文件内的一致
指定导入文件的绝对路径
导入数据的表字段类型要与文件字段匹配
禁用SELinux保护机制
导出
基本用法
SQL查询
INTO OUTFILE “目录名/文件名”
FIELDS TERMINATED BY “分隔符”
LINES TERMINATED BY “\n”;
注意事项
导出的内容由SQL查询语句决定
导出的是表中的记录，不包括表字段名
禁用SELinux保护机制
#########################################################
数据导出时，不可预先创建文件，在导出时指定文件名
查什么导什么
管理表记录
增加表记录
语法格式
格式一：增加1条记录，给所有字段赋值
insert into 表名 values (字段值列表)；
格式二：增加N条记录，给所有字段赋值
insert into 表名
values
(字段值列表)， ——第一条表记录
(字段值列表), ——第二条表记录
(字段值列表); ——第三条表记录
注意事项
字段值要与字段类型想匹配
对于字符类型的字段，要用双引或单引号括起来
依次给所有字段赋值时，字段名可以省略
只给一部分字段赋值时，必须明确写出对应的字段名称
查询表记录
语法格式
格式一
SELECT 字段1,…,字段N FROM 表名；
格式二
SELECT 字段1,…,字段N FROM 表名
WHERE 条件；
注意事项
使用 * 可匹配所有字段
更新表记录
语法格式
格式一：更新表内所有内容
UPDATE 表名
SET 字段1=字段1值,字段2=字段2值，字段N=字段N值；
格式二：只更新符合条件的内容
UPDATE 表名
SET 字段1=字段1值,字段2=字段2值,字段N=字段N值
WHERE 条件；
注意事项
字段值要与字段类型匹配
字符类型的字段要用双引或单引号括起来
如果不加WHERE条件会更新所有记录
使用WHERE条件时只更新匹配条件的记录
删除表记录
语法格式
格式一：仅删除符合条件的记录
DELETE FROM 表名 WHERE 条件；
格式二：删除所有的记录
DELETE FROM 表名；
匹配条件查询
数值比较
= 等于
> 大于
>= 大于等于
< 小于
<= 小于等于
!= 不相等
查区间
mysql> select * from 账号信息表 where id>=10 and id <=20;
字符比较
= 相等
!= 不相等
IS NULL 匹配空
IS ONT NULL 非空
范围内匹配/去重显示
匹配范围内的任意一个值即可
in （值列表） 在..里..
not in (值列表) 不在..里..
between 数字1 and 数字2 在..之间..
distinct 字段名 去重显示
基本用法
SQL查询 limit N； //显示查询结果的N条记录
SQL查询 limit N，M //显示指定范围内的查询结果
SQL查询 where 条件查询 limit 3； //显示查询结果的3条记录
SQL查询 where 条件查询 limit 3，3； //从第四条开始，共显示3条
MySQL存储引擎
MySQL服务软件自带的功能程序，处理表的处理器
查看存储引擎
查看可用存储引擎类型
SHOW ENGINES；或 SHOW ENGINES\G
MyISAM存储引擎特点
主要特点
支持表级锁
不支持事务、事务回滚、外键
相关的表文件
表名.frm ——》 存放表结构
表名.MYI ——》 存放索引信息
表名.MYD ——》 存放数据
InnoDB存储引擎特点
主要特点
支持行级锁
支持事务、事务回滚、外键
相关表文件
表名.frm ——》存放表结构
表名.ibd ——》存放数据、索引信息
ib_logfile0 ——》事务日志文件（已提交的SQL命令）
ib_logfile1 ——》事务日志文件（已提交的SQL命令）
ibdata1 ——》事务日志文件（未提交的SQL命令）
MySQL锁机制
锁粒度
- 表级锁：一次直接对整张表进行加锁
- 行级表：只锁定某一行
- 页级锁：对整个页面进行加锁
锁类型
- 读锁（共享锁）：支持并发读
- 写锁（互斥锁）：时独占锁，上锁期间其他线程不能对表进行读写操作
MySQL事务特性
l原子性：事务的整个操作是一个整体，要么全部成功，要么全部失败
l一致性：事务的操作前后，表中的记录没有变化
l隔离性：事务操作时相互隔离不受影响的
l持久性：数据一旦提交，不会改变，永久存储到硬盘上
修改 /etc/my.cnf 配置文件，指定默认存储引擎
default-storage-engine=“存储引擎名称”(myisam innodb)
数据库语法——多表查询
复制表
复制表：复制表的字段和内容取决于SQL的查询结果，但是不包括表中的键值
将源表AAAA复制为新表XXXX
CREATE TABLE XXXX SELECT * FROM AAAA;
例如：
mysql> create table 复制表 select 用户名 from test.账号信息表;
将指定的查询结果复制为新表 XXOO；
CREATE TABLE XXOO SQL查询语句；
多表查询
多表查询概述
将2个或2个以上的表，按照某个条件连接起来，从中选取需要的数据，当多个表 中存在相同意义的字段（字段名可以不同）时，可以通过该字段连接多个表
格式：
select 字段名列表 from 表A，表B where 条件；
使用where子查询
把内层的查询结果作为外层的查询条件
语法格式
select 字段名列表 from 表名
where 条件 in
(select 字段名列表 from 表名 where 条件);
左连接和右连接
基本用法
select 字段名列表 from
表A left join 表B on 条件表达式；
当条件成立时，以左表为主显示查询结果
基本用法
select 字段名列表 from
表A right join 表B on 条件表达式；
当条件成立时，以右表为主显示查询结果，没有相匹配时用NULL表示
用户授权及撤销
修改密码
密码忘了怎么办？
1：停止MySQL服务程序
2：跳过授权表启动MySQL服务程序
skip-grant-tebles ——》写入/etc/my.cnf配置文件
3：重设root密码（更新user表记录）
4：以正常方式重启MySQL服务程序
正常修改密码格式
[root@localhost ~]#mysqladmin -hlocalhost -uroot -p password “新密码“
Enter password: 旧密码
跳过授权表登录数据库服务器
[root@localhost ~]# vim /etc/my.cnf
[mysql]
skip-grant-tables
重新启动MySQL服务器 systemctl restart mysqld
修改mysql.user表
mysql> update mysql.user
> set
> authentication_string=password("123456")
> where
> user="root";
mysql> flush privileges;
退出MySQL，将配置文件的跳过授权参数注释掉，打开密码验证策略后重启mysql
[root@localhost ~]# vim /etc/my.cnf
[mysql]
#skip-grant-tables
validate_password_policy=0
validate_password_length=6
#default-storage-engine="myisam"
[root@localhost ~]# systemctl restart mysqld
用新密码重新登录数据库服务器
MySQL授权库和表信息
MySQL库下主要的几个表
user表：存储授权用户的访问权限
db表：存储授权用户对数据库的访问权限
tables_priv表：存储授权用户对表的访问权限
columns_priv：存储授权用户对字段的访问权限
GRANT授权
基本用法
GRANT 权限列表.. .. ON 库名.表名
TO 用户名@”客户端地址”
IDENTIFIED BY “密码”；
当库名.表名 为 *.* 时，匹配所有库的所有表
授权库存放在mysql库下的user表里
权限列表
all：匹配所有权限
select，update，insert…..
select,update（字段1，字段2，….字段N）
客户端地址
匹配所有主机：%
匹配指定网段主机：192.168.0.%
匹配指定单个主机：ip全地址
创建一个用户对我所有库下的所有表有完全权限，并且可以从任何主机登录
grant all on *.* to 用户名@"%" identified by "密码"；
用户查看自己权限
SHOW GRANTS；
管理员查看其他用户权限
SHOW GRANTS FROM 用户名@”客户端地址”;
授权用户连接后修改自己密码
set password=password(“新密码”)；
管理员重置授权用户连接密码
set password for 用户名@”客户端地址”=password(“新密码”);
撤销用户权限
REVOKE 权限列表 ON 库名.表名 FROM 用户名@”客户端地址”;
权限撤销只是撤销了用户的权限，并不是删除该用户，所以该用户还是可以连接数据库服务器
删除授权用户
drop user "用户"@"用户创建时的ip"
备份
1. **物理备份方式：拷贝 打包 + 脚本 + 计划任务 crond**
1. **MySQL主从同步完成数据的自动备份**
1. **逻辑备份方式：执行备份操作时，备份数据库里的SQL命令**
1. **完全备份策略：MySQLdump**
1. **增量备份策略：备份上一次备份后新产生的数据**
1. **差异备份策略：备份完全备份后新产生的数据**
物理备份
备份操作
cp -rp /var/lib/mysql/数据库 //备份目录/文件名
tar -zcvf xxx.tar.gz /var/lib/mysql/数据库/*
恢复操作
cp -rp 备份目录/文件名 /var/lib/mysql/
tar -zxvf xxx.tar.gz -C /var/lib/mysql/数据库名/
逻辑备份
逻辑备份：执行备份操作时，根据备份的库、表产生的对应的sql命令，把sql命令存储到指定的文件里
备份操作
mysqldump -uroot -p密码 库名 > 路径/xxx.sql
恢复操作
mysql -uroot -p密码 库名 < 路径/xxx.sql
库名表示方式
--all-databases 或 -A 所有库
库名 单个库
库名 表名 单个表
-B 库1 库2 多个库
注意事项：无论备份还是恢复，都要验证用户权限
实时增量备份
binlog日志的优点
记录除查询之外的所有sql命令
可用于数据恢复
配置mysql主从同步的必要条件
[root@localhost ~]# vim /etc/my.cnf
【mysql】
log-bin //启用binlog日志
server_id=数字 //指定服务器id号
binlog_format= "“ //记录格式
查看当前日志记录格式
mysql> show variables like "binlog_format";
格式：
row：不记录sql语句上下文相关信息，仅保存哪条记录备修改
statement：每一条修改数据的sql命令都会记录在binlog日志中
mixed：是以上两种格式的混合使用
恢复：
#cd /var/lib/mysql
[root@localhost mysql]# mysqlbinlog --start-position=起始偏移量值
--stop-position=结束偏移量值 “用哪里的文件恢复名“
“|（管道）mysql -uroot -p密码”（恢复到数据库里面去了）
删除当前使用日志之前的所有日志文件，不包括当前使用的
mysql> purge master logs to “日志文件名";
删除所有日志文件，重新生成第一个日志文件
mysql> reset master;
主从同步
主库启用binlog日志记录本机所有的增删改查的SQL命令，从库复制主库binlog日志文件里的所以SQL命令到自己本机的中继日志文件里，在执行本机中继日志文件里的所以SQL命令实习数据同步。
主从同步原理
Master记录数据更改操作
启用binlog日志
设置binlog日志格式
设置server_id
Slave运行2个线程
Slave_IO：复制master主机binlog日志文件里的SQL到本机的relay-log文件里
Slave_SQL：执行本机relay_log文件里的SQL语句重现Master的数据操作
主从同步构建思路
确保数据相同，从库必须要有主库上的数据
主服务器
启用binlog日志、授权用户、查看当前正在使用的日志
从服务器
设置server_id，指定主库信息
测试配置
客户端连接主库写入数据，在从库上也能查到
基本应用
单项复制：一主 →一从
链式复制：主 → 从 → 从
互为主从：主 ← → 主
一主多从：从 → 主 → 从
↓
从
一主一丛案例拓扑
注：单项复制时，建议把从库设为只读
配置
主
[root@localhost]# vim /etc/my.cnf
【mysql】
log-bin //启用binlog日志
server_id=数字 //指定服务器id号
binlog_format=mixed //记录格式
重启服务
[root@localhost]#systemctl restart mysqld
授权用户
允许授权用户 从IP 192.168.0.20 访问
对所有库（默认只允许对单个表）有同步权限
mysql> create user 用户名@”从库IP地址” IDENTIFIED BY “密码”;
mysql> GRANT REPLICATION SLAVE ON *.* TO
> 用户名@”从库IP地址”
[root@localhost]# replication slave //同步数据的权限
查看Master状态，记住当前的日志文件名和偏移量值
mysql> show master status;
从库指定server_id 不允许与主库sever_id的值相同
[root@slave ~]# vim /etc/my.cnf
[mysqld]
server_id=值
[root@slave ~]# systemctl restart mysqld 重启服务
进入MySQL
指定主库信息change master to master_host='10.1.1.10',master_user='slave',master_password='123',master_port=3306,master_log_file='binlog.000002',master_log_pos=597;
mysql> CHANGE MASTER TO
>MASTER_HOST=“192.168.0.10”, //主库的IP地址
>MASTER_USER=“lw” , //主库授权的用户
>MASTER_PASSWORD=“123456” , //授权用户的密码
>MASTER_LOG_FILE=“localhost-bin.000002” , //binlog日志文件
>MASTER_LOG_POS=334; //日志文件的便宜值
mysql> START SLAVE; //启动Slave进程
查看2个进程是否启动：
mysql> show slave status\G;
这两个值必须同时是yes状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
从库启动成功后，数据库目录下新增加四个类型相关文件
master.info //存放主库信息
relay-log.info //中继日志信息
主机名-relay-bin.index //索引文件
主机名-relay-bin.xxxxxx //中继日志
客户端测试
在主库授权一个用户然后客户端用授权用户登录测试
mysql> grant all on *.* to lz@"%" identified by "密码"
客户端登录
# mysql -h192.168.0.10 -Uroot -p
用户对数据库进行创建和写入操作
从库进行验证
一主两从
第二台从库
在现有的基础上在搭建一台从库，实现一主多从
[root@slave-1]# vim /etc/my.cnf
【mysql】
server_id=数字
[root@slave-1]# systemctl start mysqld
主库：
主库授权用户
mysql> grant replication slave on *.* to lw@"192.168.0.30"
identified by "123456";
第二台从库
从库要保证有主库上的数据
从库指定主库信息
mysql> change master to
-> master_host="192.168.0.10",
-> master_user="lw",
-> master_password="123456",
-> master_log_file="localhost-bin.000002",
-> master_log_pos=1640；
启动进程并查看状态
mysql> start slave;
mysql> show slave status\G;
主从配置常用参数:
log_slave_updates //记录从库更新，允许级联复制（A-B-C）
适用于主库的配置选项
binlog_do_db=名字 //设置主库对那些库记日志
binlog_ignore_db=名字 //设置主库对那些库不记日志
主库的设置对所有的从生效
适用于从库配置选项
log_slave_updates //记录从库更新，允许级联复制（A-B-C）
relay_log=名字-relay-bin //指定中继日志文件名
replicate_do_db=库1，库2 //只复制指定库，其他库将被忽略，可设置多条
replicate_ignore_db=库1，库2 //不复制那些库，其他库将被忽略，和上一条参数只选用一种即可
异步复制模式（默认为该模式）
主库执行完一次事务后，立即将结果返回给客户端，不关心从库是否已经同步成功
全同步复制模式
当主库执行完一次事务后，等所有从库都同步成功后将结果返回给客户端
半同步复制模式
主库在执行完一次事务后，等待至少一个从库同步成功才将结果返回给客户端
半同步模式：
查看是否允许加载模块
默认是允许状态
mysql> show variables like "have_dynamic_loading";
主库上安装插件
mysql> install plugin rpl_semi_sync_master soname "semisync_master.so";
从库上安装插件
mysql> install plugin rpl_semi_sync_slave soname "semisync_slave.so";
查看插件是否安装成功
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";
在安装完插件后，半同步复制模式默认是关闭的，需要手动启用
主库上启用半同步复制模式
mysql> set global rpl_semi_sync_master_enabled=1;
在从库上启用半同步模式
mysql> set global rpl_semi_sync_slave_enabled=1;
查看半同步复制模式
mysql> show variables like "rpl_semi_sync_%_enabled";
修改配置文件永久启用半同步复制模式
主库配置
vim /etc/my.cnf
plugin-load=rpl_semi_sync_master=semisync_master.so rpl_semi_sync_master_enabled=1
从库配置
vim /etc/my.cnf
plugin_load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
注意：是两行的，不是一行
MySQL性能调优
一些参数：
缓冲区、线程数量、打开表数量
thread_cache_size //允许保存在缓存中被重复使用的线程数量
table_open_cache //为所有线程缓存打开表的数量
key_buffer_size //用于MyISAM引擎的关键索引缓存大小
'如何提高MySQL系统的性能和响应速度？'
替换有问题的硬件（cpu/磁盘/内存等）
调整服务的运行参数
对SQL查询进行优化
查看所有变量
mysql> show variables\G;
查看变量
mysql> show variables like “变量名";
修改变量
mysql> set global 变量名=值;
常用变量
max_connections 允许的最大并发连接数
connect_timeout 等待连接超时，默认10秒，仅登陆时有效
查看最大连接数：
mysql> show global status like "max_connections";
设置最大连接数
mysql> set global max_connections=值;
查看曾经有过的最大连接数
mysql> show global status like "max_used_connections";
刷新连接数
mysql> flush status;
查看默认使用线程：
mysql> show variales like "thread_cache_size";
查询缓存的参数
mysql> show variables like "%query_cache%"
查看当前查询缓存统计
mysql> show global status like "%qcache%";
使用mysqldumpslow查看慢查询日志信息
l[root@clent ~]# mysqldumpslow /var/lib/mysql/mysql-slow.log
MySQL日志类型
vim /etc/my.cnf
错误日志：记录启动/运行/停止过程中的错误信息
log_error[=文件名] //名字不指定为默认名字
查询日志：记录数据库所有SQL操作
general_log //启用日志
general_log-file=文件名 //指定日志文件名，不指定为默认名
慢查询日志：记录耗时较长或不使用索引的查询操作
slow_query_log //启用日志
slow_query_log_file=文件名 //指定日志文件名，不指定为默认名
long-query_time=超时时间 //不指定默认为10秒
log_queries_not_using_indexes=1 //记录未使用索引的sql查询
使用mysqldumpslow 查看慢查询日志信息
[root@clent ~]# mysqldumpslow /var/lib/mysql/mysql-slow.log
读写分离原理
1. **多台MySQL服务器通过主从复制保持数据一致**
1. **由maxscale代理服务器面向客户端**
1. **收到SQL写请求时，交给主服务器处理**
1. **收到SQL读请求时，交给从服务器处理**
构建思路
1. **构建主从同步**
1. **部署maxscale代理服务器**
1. **客户端访问代理服务器测试**
部署maxscale代理服务器
1. **MaxScale代理软件**
1. **由MariaDB公司开发**
1. **下载地址：**[https://downloads.mariadb.com/MaxScale/](https://downloads.mariadb.com/MaxScale/)
1. **rpm -ivhmaxscale-2.1.2-1.rhel.7.x86_64.rpm**
配置maxsale代理服务器
修改配置文件 /etc/maxscale.cnf
[server1] 定义数据库服务器名
type=server
address=192.168.0.10 主服务器IP地址
port=3306
protocol=MySQLBackend
[server2] 定义数据库服务器
type=server
address=192.168.0.20 从库IP地址
port=3306
protocol=MySQLBackend
[MySQL Monitor] 定义要监控的数据库节点
type=monitor
module=mysqlmon
servers=server1,server2 定义的数据库服务器名
user=myuser 监控数据库服务器时用的用户名
passwd=123456 监控数据库服务器时用的密码
monitor_interval=10000
[Read-Write Service] 定义读写分离的数据库节点
type=service
router=readwritesplit
servers=server1,server2 定义的数据库服务器名
user=maxuser 定义连接数据库服务器的验证用户
passwd=123456 密码
max_slave_connections=100%
创建授权用户
在主、从数据库上授权监控用户（主库进行授权，从库上验证是否同步）
mysql> grant replication slave,replication client on *.* to myuser@"%" identified by "123456";
创建验证用户
mysql> grant select on mysql.* to maxuser@"%" identified by "123456";
创建访问用户
mysql> grant all on *.* to test@"%" identified by "123456";
启动maxscale服务rerrr
启动服务
[root@agent ~]# maxscale -f /etc/maxscale.cnf
查看服务
[root@agent ~]# ss -nptul | grep maxscale
停止服务
kill -9 进程号
连接管理端口
连接管理端口
[root@agent ~]# maxadmin -uadmin -pmariadb –P端口
查看当前正在监视的服务器
MaxScale> list servers
客户端验证
客户端连接读写分离服务器验证
[root@clent ~]# mysql -h192.168.0.30 -utest -p123456 -P4006
mysql> select @@hostname;
向表里写入数据在主库上查看是否有该数据
MHA高可用集群
MHA介绍
MHA由日本DeNA公司开发，是一套优秀的实现MySQL高可用的解决方案，实现0~30秒之内数据库故障自动切换，MHA能确保在故障切换过程中保证数据一致性，达到真正意义上的高可用
MHA组成
Manager（管理节点）
- **——可以单独部署在一台独立的机器上管理其他节点**
- **——也可以部署在一台slave节点上**
MHA Node（数据节点）
- **——运行在每台MySQL服务器上**
MHA工作过程
- **有Manager定时探测集群中的master节点，**
- **当master节点发生故障时，Manager自动将拥有最新数据的slave提升为新的master**
技术关键
1. **从宕机崩溃的master保存二进制日志事件**
1. **识别含有最新更新的slave**
1. **应用差异的中继日志（relat log）到其他的slave**
1. **应用从master保存的二进制日志事件**
1. **提升一个slave为新的master**
1. **其他的slave连接新的master进行复制**
部署MHA集群
| 角色 | IP地址 | 主机名 |
|---|---|---|
| master主节点服务器 | 192.168.0.22 | master22 |
| 备用1主节点服务器 | 192.168.0.61 | master61 |
| 备用2主节点服务器 | 192.168.0.62 | master62 |
| 第1台slave服务器 | 192.168.0.63 | slave63 |
| 第2台slave服务器 | 192.168.0.64 | slave64 |
| MHA_manager管理节点 | 192.168.0.65 | MHA-manager65 |
| VIP地址 | 192.168.0.110 |
配置数据节点：ssh免密登录
所有数据节点之间配置相互免密登录（不包括管理节点）
生成密钥（该步骤在所有数据节点操作）
- [root@master22 ~]# ssh-keygen
l传递密钥（将密钥传递给所有数据节点，不包括自己本机）
- [root@master22 ~]# ssh-copy-id 192.168.0.61
l验整数据节点之间可以相互免密登录
配置管理节点：免密登录所有数据节点
管理节点免密登录所有数据节点
生成密钥
- [root@MHA-manager65 ~]# ssh-keygen
传递密钥（传递给所有数据节点）
- [root@MHA-manager65 ~]# ssh-copy-id 192.168.0.22
l验证管理节点可以免密登录所有数据节点
配置主从同步
配置一主多从结构
- 22 主库 开启半同步复制 只要有一台数据库把数据同步成功，就把结果返回给客户端）
- 61从库（备用主库）开启半同步复制模式
- 62 从库（备用主库）开启半同步复制模式
- 63 从库只做从库不需要开启半同步
- 64 从库 只做从库不需要开启半同步
配置主库master22
授权从库用户
mysql> grant replication slave on *.* to repluser@"%" identified by "1234";
安装半同步插件（主库插件）
mysql> install plugin rpl_semi_sync_mastersoname "semisync_master.so";
安装从库半同步插件
mysql> install plugin rpl_semi_sync_slavesoname "semisync_slave.so";
查看插件信息
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";
启用主库半同步复制模式
mysql> set global rpl_semi_sync_master_enabled=1;
启用从库半同步复制模式
mysql> set global rpl_semi_sync_slave_enabled=1;
查看插件状态
mysql> show variables like "rpl_semi_sync_%_enabled";
修改配置文件，启用半同步
[root@master22 ~]# vim /etc/my.cnf
......
[mysqld]
validate_password_policy=0
validate_password_length=4
server_id=22 //指定server_id
log-bin //启用binlog日志
binlog_format=“mixed“ //指定日志格式
plugin-load=rpl_semi_sync_master=semisync_master.so //开启主库半同步复制
rpl_semi_sync_master_enabled=1
plugin_load=rpl_semi_sync_slave=semisync_slave.so //开启从库半同步复制
rpl_semi_sync_slave_enabled=1
.. .. ..
[root@master22 ~]# systemctl restart mysqld //重启数据库服务
配置备用从库61
安装主库插件
mysql> install plugin rpl_semi_sync_mastersoname "semisync_master.so";
安装从库插件
mysql> install plugin rpl_semi_sync_mastersoname "semisync_master.so";
查看插件
mysql> install plugin rpl_semi_sync_mastersoname "semisync_master.so";
启用主库半同步复制模式
mysql> set global rpl_semi_sync_master_enabled=1;
启用从库上半同步复制模式
mysql> set global rpl_semi_sync_slave_enabled=1;
查看插件状态
mysql> show variables like "rpl_semi_sync_%_enabled";
修改配置文件
[root@master61 ~]# vim /etc/my.cnf
[mysqld]
.. .. ..
validate_password_policy=0
validate_password_length=4
server_id=61
log-bin
binlog_format="mixed"
plugin-load=rpl_semi_sync_master=semisync_master.so //启用主库半同步复制
rpl_semi_sync_master_enabled=1
plugin_load=rpl_semi_sync_slave=semisync_slave.so //启用从库半同步复制
rpl_semi_sync_slave_enabled=1
.. .. ..
[root@master61 ~]# systemctl restart mysqld //重启数据库服务
指定主库信息
mysql> change master to
-> master_host="192.168.0.22",
-> master_user="repluser",
-> master_password="1234",
-> master_log_file="master-bin.000005",
-> master_log_pos=450;
mysql> start slave; //启动slave进程
mysql> mysql> show slave status\G; //查看从库状态
配置备用从库62
安装主库半同步插件
mysql> install plugin rpl_semi_sync_mastersoname "semisync_master.so";
安装从库半同步插件
mysql> install plugin rpl_semi_sync_slavesoname "semisync_slave.so";
查看插件
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";
启用主库半同步复制模式
mysql> set global rpl_semi_sync_master_enabled=1;
启用从库上半同步复制模式
mysql> set global rpl_semi_sync_slave_enabled=1;
查看插件状态
mysql> show variables like "rpl_semi_sync_%_enabled";
修改配置文件
[root@master62 ~]# vim /etc/my.cnf
.. .. ..
[mysqld]
validate_password_policy=0
validate_password_length=4
server_id=30
plugin-load=rpl_semi_sync_master=semisync_master.so //启用主库半同步复制
rpl_semi_sync_master_enabled=1
plugin_load=rpl_semi_sync_slave=semisync_slave.so //启用从库半同步复制
rpl_semi_sync_slave_enabled=1
log-bin
binlog_format="mixed“
.. .. ..
u重启数据库服务
[root@agent ~]# systemctl restart mysqld
指定主库信息
mysql> change master to
-> master_host="192.168.0.10",
-> master_user="repluser",
-> master_password="1234",
-> master_log_file="slave-bin.000010",
-> master_log_pos=450;
u
mysql> start slave; //启动slave进程
mysql> mysql> show slave status\G; //查看状态
配置从库63
修改配置文件，指定server_id
[root@slave63 ~]# vim /etc/my.cnf
validate_password_policy=0
validate_password_length=4
server_id=63 //指定id
重启服务
[root@slave63 ~]# systemctl restart mysqld
指定主库信息
mysql> change master to
-> master_host="192.168.0.22",
-> master_user="repluser",
-> master_password="1234",
-> master_log_file="master-bin.000001",
-> master_log_pos=154;
mysql> start slave; //启动slave进程
mysql> show slave status\G; //查看状态
其他主从库按照以上案例配
主库授权用户
主库为MHA管理主机授权连接数据库用户
mysql> grant all on *.* to root@"%" identified by "1234";
在所有数据节点都要保证有主从同步用户，可以直接在主库授权，让从库全部同步
mysql> grant replication slave on *.* to repluser@"%" identified by "1234";
在所有数据库内，查看授权用户
mysql> select user,host from mysql.user;
安装MHA软件包
在所有主机安装MHA依赖包（包括管理主机）
[root@master22 mha-soft]# yum -y install perl-*.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.art.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
安装MHA数据节点软件包
所有数据节点安装mha4mysql-node依赖包
[root@master22 mha_soft]# yum -y install perl-DBD-mysqlperl-DBD-DBI
所有节点安装数据节点mha4mysql-node包（包括管理节点）
[root@manager22 mha-soft]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
查看软件包
[root@manager22 mha-soft]# rpm -q mha4mysql-node
部署管理节点
安装管理节点软件包
[root@manager65 mha-soft]# yum -y install perl-ExtUtils-*
[root@manager65 mha-soft]# yum -y install perl-CPAN-*
解压mha4mysql-manager源码包
[root@manager65 mha-soft]# tar -xf mha4mysql-manager-0.56.tar.gz
进入源码包路径
[root@manager65 mha-soft]# cd mha4mysql-manager-0.56
检测安装环境
[root@manager65 mha4mysql-manager-0.56]# perl Makefile.PL
编译&&安装源码
[root@manager65 mha4mysql-manager-0.56]# make && make install
命令创键快捷方式
[root@manager65 mha4mysql-manager-0.56]# ln -s /root/mha-soft-student/mha4mysql-manager-0.56/bin/ /root/
创键工作目录
[root@manager65 mha4mysql-manager-0.56]# mkdir /etc/mha_manager
拷贝模板文件到工作目录
[root@manager65 mha4mysql-manager-0.56]# cp samples/conf/app1.cnf /etc/mha_manager/
修改配置文件
[root@manager65 mha4mysql-manager-0.56]# vim /etc/mha_manager/app1.cnf
[server default] //mha本机的配置
manager_workdir=/etc/mha_manager //指定工作目录
manager_log=/etc/mha_manager/manager.log //指定日志文件
master_ip_failover_script=/etc/mha_manager/master_ip_failover //指定监控脚本,先测试再#解除
ssh_user=root //通过ssh连接数据节点，用户为root
ssh_port=22 //端口
user=root //连接数据库用户
password=1234 //连接密码
repl_user=repluser //主从同步用户
repl_password=1234 //用户密码
[server1] //被监控服务器名称（默认即可）
hostname=192.168.0.22 //被监控数据库IP地址
candidate_master=1 //竞选主库
[server2]
hostname=192.168.0.61
candidate_master=1 //竞选主库
[server3]
hostname=192.168.0.62
candidate_master=1 //竞选主库
[server4]
hostname=192.168.0.63
no_master=1 //纯从库不竞选主库
将脚本带入 cd /etc/mha_manager/
chmod +x /etc/mha_manager/master_ip_farilover
在35——38更改虚拟ip和真实网卡名字（ens xx）
master_ip_failover.sh
测试配置:主库和mha管理
测试ssh登录
[root@mha_manager ~]# masterha_check_ssh --conf=/etc/mha_manager/app1.cnf
测试主从同步
[root@mha_manager ~]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf
在主库配置VIP地址
[root@master22 ~]# ifconfig ens32:1 192.168.0.110/24
启动manager服务
[root@mha_manager~]#masterha_manage r--conf=/etc/mha_manager/app1.cnf \
--remove_dead_master_conf \ //将故障机器从配置文件移除
--ignore_last_failover //忽略健康文件
另开终端查看服务状态
[root@mha_manager]#masterha_check_status--conf=/etc/mha_manager/app1.cnf
停止主库MySQL服务测试故障迁移
[root@master22 ~]# systemctl stop mysqld
MySQL视图
1. -**视图是虚拟表，内容与真实表相似，但是表里有字段有记录**
1. -**视图并不在数据库中以存储的数据形式存在**
1. -**行和列的数据来自定义视图时查询所引用的基表，并且在具体引用视图时动态生成**
1. -**更新基表数据，视图的数据也会跟着改变**
1. -**更新视图表数据，基表的数据也会跟着改变**
特点：
简单
-用户不需要关心视图中的数据如何查询获得
-视图中的数据已经是过滤好的符合条件的结果集
安全
-用户只能看到视图中的数据
数据独立
-一旦视图结构确定，可以屏蔽表结构对用户的影响
视图的基本使用
创键视图:create view 视图名称 as SQL查询
mysql> create view user_view as select name,shell from user;
查看视图信息
mysql> show table status\G; //查看当前库下所有表的状态信息
mysql> show table status where comment=“view”\G; //加条件查看
Comment: VIEW //视图表
查看创建视图命令
查看创键视图命令
show create view 视图名;
mysql> show create view user_view\G;
查询记录
-select 字段名列表 from 视图名 where 条件;
mysql> select * from user_view;
插入记录
-insert into 视图名(字段名列表) values(字段值列表);
mysql> insert into user_view values(“ooxx","/sbin/nologin");
更新表记录
-update 视图名 set 字段名=值 where 条件;
mysql> update user_view
-> set name="xxxx" where name=“ooxx";
删除表记录
-dalete from 视图名 where 条件;
mysql> delete from user_view where name="xxxx";
删除视图表
-drop view 视图表名;
mysql> drop view user_view;
对视图表进行操作，基表也会跟着改变
对基表进行操作，视图表也会跟着改变
若有收获，就点个赞吧
0 人点赞
