mysql数据库笔记

t网 :http://dev.mysql.com/downloads/mysql
Day1
1.什么是数据库?
存储数据的仓库
DB 数据库 DBMS 数据库管理系统 DBS 数据库系统 即DB+DBMS
连接数据库的方式
命令行
工具软件:提供图形界面对数据做管理

2.搭建数据库服务器?
3.连接方式 使用规则 基本操作 ?
4.MySQL数据类型 字符类型 数据类型 日期时间 枚举 ?
5.数据库错误日志文件路径(初始密码存储位置)

  /var/log/mysqld.log

6.新安装密码存放路径

   grep “password”   /var/log/mysqld.log

7.修改登录密码

	alter user root@”localhost” identified by “密码”;
	mysqladmin -uroot  -p密码  password “新密码”

8.修改密码规则:

show variables like 'validate_password%';
validate_password_length=6
validate_password_policy=0

主配置文件 /etc/my.cnf
传输协议 tcp
数据库目录 /var/lib/mysql
stuinfo.frm 存放表头信息 stuinfo.ibd 存放数据信息
floa单精度 0–2^32-1 double双精度 0–2^64-1
数据库的增删改查
删

drop  database  库名;   //删除库
drop  table  库.表;         //删除表
delete  from 库.表;     //删除表所有记录
delete  from 库.表   where   条件;    //删除表表符合条件的内容

增

create   database/table  库/库.表;       //创建新的库/表
insert   into  库.表[(指定插入的字段)] values();   //插入字段值   

改

update  库.表  set  字段=值   [where]  条件;    //更新字段内容,可加条件

查

desc  查看表结构  show 查看       select 

常见的信息种类
数值型 字符型 枚举型 日期时间型

字符型
定长 char 最大字符个数255
变长 varchar 按数据实际大小分配存储空间
数值类型

浮点型

日期时间类型

datetime / timestamp    格式: yyyymmddhhmmss
当未给timestamp字段赋值时,自动以当前系统时间赋值,而datetime值为null
date   yyyymmdd
year yyyy
01--69视为 2001-2069    70--99视为 1970--1999
time  HH:MM:SS

时间函数

枚举类型

enum   格式: 字段名   enum(值1,值2...)  仅能选一个值,并且必须在列表选择
set   格式:字段名 (值1,值2...)  选择一个或者多个,字段值必须在列表里选择

Day2

1.约束条件

 create table t4(name char(10) not null,homeaddr char(30) not null default "");
 mysql> create  table t2(class char(9),name char(10) not null default "",age tinyint not null default "19",likes set("a","b","c","d") default “a,b”);

1.修改表结构

  Mysql>alter table  库名.表名  执行动作

1.1添加字段

  mysql> alter table t1 add dirke enum("weater","kele") first;  //添加字段到首行
  mysql> alter table t1 add play set("sleep","read","run") not null  default "sleep" after dirke;   //添加字段到指定字段下,如不指定默认为最底行.
+-------+---------------------------+------+-----+----------+-------+
| Field | Type                      | Null | Key | Default  | Extra |
+-------+---------------------------+------+-----+----------+-------+
| dirke | enum('weater','kele')      | YES  |     | NULL     |       |
| play  | set('sleep','read','run')    | NO   |     | sleep    |       |
| name | char(10)                  | NO   |     | NULL     |       |
| age   | tinyint(3) unsigned       | YES  |     | 19       |       |
| class  | char(7)                   | NO  |     | nsd1907  |       |
| pay    | float(7,2)                | YES  |     | 28000.00 |       |
+-------+---------------------------+------+-----+----------+-------+
mysql>insertintot1values("weater","sleep,read","tom","18","nsd1908","30000.00");
修改字段名 eg:
mysql> alter table t1 change name xingming varchar(20) not null default "yy";
        change可以修改字段名称,也可以修改字段类型.
删除字段 eg:
 mysql> alter  table  t1  drop party,drop your_chart;
修改表名 eg:
 mysql> alter table t2 rename wb;  
  mysql> alter table  t1  add qq  char(11) not null,add sina varchar(20), modify xuexiao varchar(30) not null default "CN",change birthday shengri char(8),drop qq;   //综合

MySQL 键值

  Index 索引       btree  使用二叉树木算法
  一个表中可以有多个Index字段,字段值允许重复,且可以赋null值,通常把作为查询条件的字段设置为index字段,index字段的标志是 MUL
 mysql> create table t1 (name char(9) not null default "xbb",age tinyint unsigned,pay float(5,1),index(name),index(age));   //建表时添加index字段
mysql> create  index  索引名  on a(字段名);    //在已有表里添加index字段
mysql> show index from  a\G;       //查看索引信息
mysql> drop index ab on a;          //删除索引
在已有表里添加主键
mysql>alter table 表名 add primary key(字段名);
复合主键
create table t3(name char(10),class char(10),pay enum("yes","no"),primary key(name,class,pay));    //建表时创建复合主键(多个字段作为主键)
mysql>insertintot3values("bob","nsd1907",yes),("bob","nsd1908",yes),("bob","nsd1907",no);


mysql> create  table t4 (id int primary key auto_increment,name char(10),age tinyint unsigned,class char(7) default "nsd1907");(每次增加表记录,主键值自增1,在不赋予指定值得情况下,字段值不允许重复,且不允许赋空值null(若设置 auto_increment,则若输入null时,自增长1)).
主键通常与 auto_increment 连用,自增长依赖主键.

删除主键:
mysql> alter table t4 modify id int not null; 移除主键前,如果有自增属性,必须先去掉
mysql> alter table t4 drop primary key;        取消主键
eg:
mysql> insert into t4 values(null,"yy",28,"nsd1917");

Foreign key 外键
功能:插入记录时,字段值在另一个表字段值范围内选择.
使用规则:表存储引擎必须是innodb,字段类型要一致,被参照字段必须时索引类型的一种(primary key)
 mysql> create table yg(yg_id int primary key auto_increment,name char(9))engine=innodb;
mysql> insert   into yg(name) values("wb"),("bb"),("yy"),("xbb");
mysql> create table gz(gz_id int,gz float(7,2),foreign key(gz_id) \   //创建字段时指定外键
references yg(yg_id)  \   //参照字段
on update cascade  \     //同步更新
on delete cascade) \      //同步删除
engine=innodb;          //指定存储引擎

mysql> delete from gz;
mysql> alter  table gz add primary key(gz_id);
删除外键
 mysql> show create table gz;
 mysql> alter table gz drop foreign key gz_ibfk_1;
 mysql> show create table gz;

Day 3

1. 数据的导入导出
   数据库导入导出默认检索目录 /var/lib/mysql-files/
   自定义检索目录
   mkdir /myload 目录必须真实存在,且用户所有者为mysql用户;

    vim /etc/my.cnf
    [mysqld]
    secure_file_priv=”/myload”   指定检索目录
    systemctl  restart  mysqld   重新加载配置文件
   

Mysql在登录状态下使用shell脚本 前面要使用 system命令.

• 数据导入的步骤?
  1.把系统文件拷贝到检索目录下;

   mysql>system  cp /etc/passwd   /myload/
  

  2.创建存储数据的库和表

    Mysqld>create table user(name char(50),passwd char(1),uid int,gid int,comment char(150),homedir char(150),shell char(150));
  

  3. 导入数据

  mysql> load data infile “/myload/passwd” into table user fields terminated by “:” lines terminated by “\n”;
  mysql> alter table user add id int primary key auto_increment first; 添加id字段,添加主键,自增长.

  4.查看数据

  desc user;
  select * from user\G;

2.数据的导出

  mysql> select name,uid,shell,id from user where homeaddr="/root"into outfile "/myload/user.txt" fields terminated by "::" lines terminated by "\n";  //指定分隔符和换行符

2.查询表记录

  mysql> select name,passwd from user where 10>id and id>5;
              delete from yg where yg_id=3;

4.更新表记录(删除差不多)

  mysql> update t1 set play=run where xingming="wb";

匹配条件
数值比较,字段必须是数值类型

字符比较,字段必须是字符类型

逻辑匹配,多个判断条件时使用

范围匹配/去重显示,匹配范围内的任意一个值即可

高级匹配条件
模糊查询
用法 --where 字段名 like “通配符” “_” 匹配一个字符 “%”匹配0-n个字符

正则表达式
用法 --where 字段名 regexp ‘正则表达式’ //正则查询必须加regexp选项
^以什么开头 $以什么结尾 . 匹配所有 * 匹配前面字符任意次数 [] 匹配选项任意一个即可 | 或者

四则运算
字段必须是数值类型

select 数值字段(符号)数值字段 from 表 [条件]
聚集函数,MySQL内置数据统计函数

select 函数(字段名) from 表 [条件]
查询结果排序
SQL查询 order by 字段名(通常是数值类型字段) [asc|desc];
asc 升序排序 默认为asc
desc 降序排序
查询结果分组
SQL查询 group by 字段名; //跟去重查询区别不一样,查询结果分组时执行查询后的结果.

DAY 4
用户授权:在数据库服务器上添加新的连接用户;
撤销授权:删除添加的用户对数据的访问权限;
删除用户:把新添加的用户删除;
授权库: mysql库(系统默认存在)----------> 保存新添加的用户信息和权限信息;
数据备份策略有哪几种?
完全备份 -----------------> 备份所有数据
增量备份 ------------------> 备份上次备份后,所有新产生的数据
差异备份 ------------------> 备份完全备份后,所有新产生的数据
Binlog日志也叫二进制日志,记录除查询之外的\所有sql命令,可用于数据备份与恢复.

数据的备份
冷备份(也称作物理备份): cp tar

cp  -r   /var/lib/mysql/   备份目录/文件名
Tar  -zcvf /root/mysql.tar.gz  /var/lib/mysql/*

恢复操作

cp  -r  备份/文件名   /var/lib/mysql/
Tar  -zxvf   /root/mysql.tar.gz  /var/lib/mysql/
Chown  -R  /var/lib/mysql/

逻辑备份

mysqldump -uroot  -p密码 (-A(所有),-B库名1 库名2,库名,库名 表名) > 目录/文件名

恢复数据

mysql -uroot -p密码  [库名]  < 目录/文件名 ------------->若要恢复某个库数据或表的数据,首先得新建空库,再执行恢复.
一定要验证用户对目录的权限.

增量备份
启用binlog日志来备份和恢复数据
Binlog日志也称为二进制日志;
binlog日志记录除查询命令以外的sql命令,属于mysql服务日志的一种;
配置mysql主从同步的必要条件;
启用binlog日志文件:
log_bin=/mysql/zl.log(目录要存在,且对mysql有权限)
server_id=50(1-255)
show master status 查看增量日志信息 默认日志文件大小为1G,若文件内存不足,则自动创建新的日志文件,也可以手动创建新的日志文件,则数据库自动使用数值最大的日志文件.
手动创建新的日志文件:
1.systemctl restart mysqld
2.mysql>flush logs;
/]# mysql -uroot -p密码 -e “flush log”;
3.mysqldump --flush-logs 完全备份的时候加上这个选项,自动生成日志文件
/]# mysqldump --flush-logs -uroot -phahaha -A > /myload/wq.sql
2.删除指定编号之前的binlog日志文件
mysql> purge master logs to “zl.000014”; (删除指定表号之前的日志文件)
mysql> reset master; 删除所有日志,重建新日志
使用binlog日志恢复数据
1.在主配置文件中写入 binlog_format=“mixed” ,修改记录模式为混合模式,在配置文件内添加字段:修改记录方式为 “混合模式”,混合模式可以显示易读的偏移量与时间,用来恢复数据.
2.拷贝日志文件给用来恢复数据的服务器主机;
Mysqlbinlog(–start(stop)-datetime=”yyyy-mm-ddhh:mm:ss”,–start(stop)-position=数字(起始or结束偏移量)) | mysql -uroot -p密码 ------->恢复指定范围内数据3.mysqlbinlog 日志文件名称 | mysql -uroot -p密码 恢复上次备份后所有数据
恢复指定范围内的数据:
要是想恢复到最后一条操作命令的数据只需要指定起始的”时间”或”偏移量”,系统默认恢复到最后一条命令.若只指定结束的时间或者偏移量,则默认恢复全部.
实验使用另外一台服务器恢复数据,必须提前创建好要恢复的库;
读取日志文件的指定命令范围恢复数据;
日志的记录模式有哪几种?
1.statement 报表模式
2.row 行模式
3.mixed 混合模式
Day 5
使用percona软件完全备份数据,备份过程中不锁表,innobackupex组件以per\脚本封装xtrabaclup.
需要软件: percona-xtrabackup, libev
完全备份:
innobackupex --user 用户名 --password 密码 备份目录名 [–notimestamp]
完全恢复
systemctl stop mysqld
rm -rf /var/lib/mysql/*
Innobackupex --apply-log 目录名 //准备恢复数据
]# cat xtrabackup_checkpoints (可选操作查看)
backup_type = full-backuped (当完成准备恢复数据命令后,ful-backuped 会变为full-prepared)
Innobackupex --copyback 目录名 //恢复数据
chown -R mysql:mysql /var/lib/mysql/
systemctl start mysqld

恢复单张表数据:
模拟误删除一张表的数据,使用所有数据的备份来恢复单张表的数据.
sql>alter table db5.a discard tablespace; //删除表空间
]#innobackupex --apply-log --export 备份目录 //导出表信息
]#cp 目录/库.表名.{ibd,cfg,exp} 数据库目录/库名 //拷贝表信息
]#chown -R mysql:mysql /var/lib/mysql/ //授权
sql>alter table 库.表 import tablespace; //导入表空间
Sql> select ***
]#rm -rf /var/lib/mysql/库名/表.{cfg,exp}
增量备份与增量恢复:
增量备份
]#innobackupex --user root --password hahaha /fullback --no-timestamp //所有备份
]#innobackupex --user root --password hahaha --incremental /new1dir --incremental-basedir=/fullback --no-timestamp //增量备份
]#innobackupex --user root --password hahaha --incremental /new2dir --incremental-basedir=/new1dir --no-timestamp //增量备份
…
增量恢复数据
systemctl stop mysqld
rm -rf /var/lib/mysql/*
innobackupex --apply-log --redo-only /fullback/
Innobackupex --apply-log --redo-only /fullback/ --incremental-dir=/new1dir
cat xtrabackup_checkpoints
Innobackupex --apply-log --redo-only /fullback/ --incremental-dir=/new2dir
innobackupex --copy-back /fullback/
chown -R mysql:mysql /var/lib/mysql/
systemctl start mysqld

第二周
Day 1
Mysql主从同步 (master,slave)
用来实现数据的自动同步的服务结构.
主从同步模式(结构类型)有哪几种?
答:一主一从 ---------->单向复制
一主多从
链式复制(主从从)
互为主从
主服务器:接受客户端访问连接
从服务器:自动同步主服务器数据
原理:当设置主(master)从(slave)后,主服务器开启binlog日志,从服务器启动i/o thread 与SQL thread 线程,i/o thread线程自动拷贝主服务器的binlog日志sql命令到从服务器的relaylog(中继日志文件,从服务器自动创建),SQL thread 执行中继日志的sql命令,将数据写入从服务器的数据库中.
构建思路:
配置主服务器
—启用binlog日志,授权用户,查看binlog日志信息,谁做主服务器谁必须启用binlog日志
/etc/my.cnf —>打开binlog选项,加log_bin=可自定义目录/文件名(自定义需要修改用户归属),也可使用默认目录
Mysql>grant replication slave on 库/表 to 用户名@”主机地址” identified by “密码”;
Mysql>show master status;
]#mysqldump -uroot -p密码 --master-data 库名 > 备份目录/名称
当备份主服务器正在使用的库数据时,必须加上 --master-data 选项,记录备份库时的偏移量与binlog日志名称,当从服务器自动备份数据时,从备份数据中读取偏移量与备份时使用的binlog日志名称,备份时自动从偏移量后开始备份数据,确保数据的完整性.
配置从服务器
1.设置server_id
2.确保与主服务器数据一致 ------>确保库一致,若主服务器备份数据,从服务器需要导入备份数据.
]#mysql -uroot -p密码 库名 < 备份数据
3.指定主库信息
mysql>change master to master_host=”目标主机”,master_user=”用户名”,master_password=”密码”,master_log_file=”binlog日志文件名”,master_log_pos=”偏移量”;
4.启动slave程序
5.查看状态信息
主从同步的复制模式
异步复制(默认模式)
全同步复制
半同步复制
半同步复制模式设置
1.查看是否允许动态加载模块
mysql> show variables like “have_dynamic_loading”;
2.使用命令行加载模块
mysql> install plugin rpl_semi_sync_master soname “semisync_master.so”;
mysql> install plugin rpl_semi_sync_slave soname “semisync_slave.so”;
3.查看是否加载成功
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like ‘%semi%’;
4.查看是否启用
mysql> show variables like “rpl_semi_sync_%enabled";
5.启用模块
mysql> set global rpl_semi_sync_master_enable=1;
mysql> set global rpl_semi_sync_slave_enabled=1;
6.查看是否启用
mysql> show variables like "rpl_semi_sync%_enabled”;
7.永久启用模块
/etc/my.cnf
plugin-load=“rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so”
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
Day 2
数据读写分离 -------->减少单台服务器并发的压力;
将客户端的读写操作分离到不同的服务器处理.
构建思路
1.部署mysql主从结构
2.部署mysql代理服务器
装包 maxscale 服务名
修改配置文件
[maxscale] //定义线程个数
threads=auto

[server1] //定义数据库服务器
type=server
address=192.168.4.51 //master 主机ip地址
port=3306
protocol=MySQLBackend
[server2] //定义数据库服务器
type=server
address=192.168.4.52 //slave主机ip地址
port=3306
protocol=MySQLBackend

#指定监视的服务器与监控用户,监控主从服务器状态
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1, server2
user=maxmysql
passwd=123qqq…A
monitor_interval=10000

#指定路由用户,查询主从服务器是否存在
[Read-Write Service]
type=service
router=readwritesplit
servers=server1, server2
user=mysqlwb
passwd=123qqq…A
max_slave_connections=100%

[MaxAdmin Service]
type=service
router=cli

[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006

[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
port=4016
3.配置数据库服务器
4.启动服务
5.查看进程信息
6.访问管理服务查看监控信息
7.在主服务器添加访问数据的连接用户
在客户机连接代理服务器访问数据
二. 多实例服务
1.多实例服务介绍
在一台物理服务器上运行多个数据库服务.
2.为什么要使用多实例服务
优:节约运维成本 提高硬件利用率
缺:对硬件要求高,防止单点故障.
3.配置多实例服务

4.启动服务
5.客户端连接多实例服务
6.使用多实例服务
Day 3 数据分片
分库/分表
-将存放在一台的数据库服务器中的数据,按照特定的方式进行拆分,分散存储到多台数据库服务器中,达到分散单台服务器负载的效果.
实现方法:
水平分割(横向切分):将单个数据库中的多个表按业务类型分类,分散存储在不同的数据库.

垂直分割(纵向切分):按照表中指定字段的分片规则,将表记录按行切分,分散存储到多个数据库中.

装包
mycat
基于java的分布式数据库系统中间件,为高并发环境的分布式存储提供解决方案.
----适合数据大量写入的存储需求
----提供数据读写分离服务
----提供数据分片服务
----支持MySQL,Oracle,Sqlserver,mongodb等;
工作原理
1.解析SQL命令涉及到的表;
2.然后看对表的配置,如果有分片规则,则获取SQL命令里分片字段的值,并分配分片函数,获取分片列表;
3.将SQL命令发往对应的分片服务器去执行
4.收集和处理所有分片结果数据,并返回客户端
拓扑结构

部署Mycat服务
分片规则
shading-by-intfile 枚举法
mod-long 求模法
装包 openjdk mycat 自定义安装包\一般放在/usr/local/下
/usr/local/mycat/目录下
conf/ ------------> 存放各种配置文件
/conf/server.xml -----> 设置连接账号及逻辑库
/conf/schema.xml -------> 配置数据分片
/conf/rule.xml --------> 分片规则
/logs/ -------------> 存放mycat启动日志和运行日志
/logs/wrapper.log ------------> 存放mycat服务启动日志
/logs/mycat.log ------------> 存放sql脚本执行后报错内容
/bin/ ---------------> 存放mycat命令
创建连接用户
1.vim /usr/local/mycat/conf/server.xml
//指定连接用户名称
123456 //指定密码
TESTDB,DDSDB,wahaha //指定 连接后显示虚拟库名称,可自定义库名称

2.vim /usr/local/mycat/conf/schema.xml //配置数据分片信息

3.在节点存储服务器上授权用户,创建配置文件指定存放的库名,配置文件写几台授权几台
Mysql>grant all on . to 用户@”主机” identified by “密码”; //用户名与密码必须与schema.xml写的用户名密码一致
4.启动服务,服务端口 8066
/usr/local/mycat/bin/mycat start/stop/status
netstat -atuulp | grep 8066
5.客户端连接
mysql -h(分片服务器地址) -P8066 -uroot -p123456 //用户名密码为 server.xml设置的用户名密码
6.客户端测试配置
rule.xml 指定分片规则
客户端连接后显示库为虚拟库(不存在的),需要创建库,创建的库只能是配置文件指定的库名,并且创建的表必须与配置文件里的表名一致,新建表要遵循指定的分片规则,要创建分片规则指定的字段,其他的字段可以随便加.
枚举法 sharding-by-intfile
字段必须在列举范围内选择,根据分片规则和对应算法创建表结构.

1. vim /usr/local/mycat/conf/partition-hash-int.txt
   10000=0 当sharding_id字段数值为10000,则数据存放在节点1下的db1
   10010=1 …
   10020=2
2. vim /usr/local/mycat/conf/rule.xml
   //指定分片规则
   
   sharding_id //指定字段名,读取指定字段的数值,根据分片规则存放到指定节点
   hash-int //指定算法
   
   
   3.根据分片规则和对应算法创建表结构.
   当创建配置文件中指定的表时,节点服务器自动创建表,数据存储基于分片规则存储至不同节点服务器;
   求模法 mod-long
   1.vim /usr/local/mycat/conf/rule.xml
   
   
   3 //几个节点服务器,写几
   

id mod-long 2.根据分片规则和对应算法创建表结构. 添加新的库和表 参照上面,修改server.xml,schema.xml文件,添加库名与表名和指定表的分片规则,重启服务,根据分片规则和对应算法创建表结构. Day 5 MHA集群 使用过哪些搭建集群的软件? LVS nginx proxy MHA(只能对数据库搭建集群) 什么是服务器高可用,高性能? MHA Manager (管理节点) --管理所有数据库服务器 --可以单独部署在一台独立机器上 --也可以部署在某台数据库服务器上(不建议,当这个节点down机,整个服务瘫痪) MHA Node (数据节点) --存储数据的MySQL服务器 --运行在每台MySQL服务器上 MHA工作过程 由manager定是探测集群中的master节点,当master故障时,manager自动将拥有最新数据的slave提升为新的master. 

一. 准备MHA集群环境
1.所有服务器都装包
cd mha-soft-student/
yum -y install perl-*
yum -y install perl-ExtUtils-*
yum -y install perl-CPAN*
2.配置ssh秘钥对认证登录(所有服务器都免密码登录)
配置MHA manager 免密码连接 MHA Node
]# ssh-keygen
]# ssh-copy-id root@192.168.4.51
…
配置MHA Node 之间免密码登录
]# ssh-keygen
]# ssh-copy-id root@192.168.4.52
]# ssh-copy-id root@192.168.4.53
…
3.配置一主多从同步结构(保证数据一致性)
master 服务器开启binlog日志,指定server_id,授权用户
从服务器指定主库信息,开启slave服务.
二. 配置MHA manager管理节点
1.安装软件包
mha4mysql-node-0.56-0.e16.noarch.rpm
tar -xf mha4mysql-manager-0.56.tar.gz
cd mha4mysql-manager-0.56/
perl Makefile.pl
make && make install
新建工作目录
mkdir /etc/mha/
编写主配置文件
vim /etc/mha/app1.cnf

创建故障切换脚本
与app1.cnf指定的故障切换脚本名称一致
vim /etc/mha/master_ip_failover
my $vip = ‘192.168.4.100/24’; //指定vip地址
my $key = “1”;
my $ss{h}_{s}tar{t}_{v}ip="/sbin/ifconfigeth0:$key $vip";
my $ss{h}_{s}to{p}_{v}ip="/sbin/ifconfigeth0:$key down";
授予脚本执行权限
chmod +x /etc/mha/master_ip_failover
在主库部署vip地址
Ifconfig eth0:1 192.168.4.100 //master_ip_failover指定地址一致
三. 配置MHA Node数据节点步骤总结
1.安装mha-node软件
2.在主服务器51主机手动设置vip地址 192.168.4.100
3.所有数据节点添加监控用户 权限为 all .
与app1.cnf 指定的监控用户一致
该用户是管理节点用来连接数据节点的.
root 123qqq…A
4.在所有数据节点主机添加数据同步用户,权限为 replication slave
repluser 123qqq…A
5.在所有数据节点服务器启动版同步复制模式 禁用自动删除中继日志文件
server_id=53
plugin-load=“rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so” //加载主从模块
rpl_semi_sync_master_enabled=1 //启用master模块
rpl_semi_sync_slave_enabled=1 //启用slave模块
relay_log_purge=0 //禁止自动删除中继日志文件
log_bin=zl53 //启用binlog日志

6.再从服务器52和53启用版同步复制模式, 禁用自动删除中继日志文件
7.重启mysqld服务
8.查看从服务器状态
四. 测试配置
1.在MHA manager(管理节点)上测试ssh配置 ----->测试MHA Node(数据所有节点之间是否能免密码登录)
]# masterha_check_ssh --conf=/etc/mha/app1.cnf ----> 目录为自定义的目录
All SSH connection tests passed successfully. ------>当看到这条语句则说明所有数据节点可以免密登录.
2. 在MHA manager测试主从同步配置
masterha_check_repl --conf=/etc/mha/app1.cnf
MySQL Replication Health is OK. ----->当看到这个字段表示配置检测成功
3.在MHA manager开启管理服务
]# masterha_manager --conf=/etc/mha/app1.cnf
–remove_dead_master_conf //启服务时自动删除宕机的主库配置
–ignore_last_failover //忽略跳过xxx.health文件
4.查看服务状态
]# masterha_check_status --conf=/etc/mha/app1.cnf
5.停止服务
]# masterha_stop --conf=/etc/mha/app1.cnf
五.访问集群
1.在主服务添加访问数据的连接用户,slave服务器自动备份
六.测试高可用
模拟主服务器故障
1.在从服务器查询是否竞选为主服务器(vip地址)
2.测试服务是否正常运行
修复故障服务器
1.配置数据库服务器为主服务器的从服务器
—启动MySQL服务
—与主服务器数据一致
—指定主服务器信息
—启动slave进程
—查看状态信息
2.配置管理服务器
—修复主配置文件
在app1.cnf加载修复的数据节点服务器信息
—测试集群环境
—重启管理服务
—查看服务状态
Day 5
Pxc免费开源 MySQL集群服务,MySQL高可用集群解决方案
特点:
数据强一致性,无同步延迟
没有主从切换操作,无需使用虚拟IP
支持InnoDB存储引擎支持
多线程复制
部署使用简单
支持节点自动加入,无需手动拷贝数据
相应端口

3306 与 4567 端口常开,4444 4568端口只有在同步数据时才开.
装包
libev-4.15-1.el6.rf.x86_64.rpm
percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm
qpress-1.1-14.11.x86_64.rpm
Percona-XtraDB-Cluster-*.rpm
修改配置文件
cd /etc/percona-xtradb-cluster.conf.d/
mysqld.cnf //数据库运行配置参数 ---->>指定server_id,保证唯一性
mysqld_safe.cnf //配置文件 ----->>不需要改
Wsrep.cnf //表分片

启动服务
systemctl start mysql@bootstrap.service
grep pass /var/log/mysqld.log //查看初始密码
修改登录密码,其他集群成员不用改,自动同步.
添加SST数据同步授权用户
grant reload,lock tables,replication client,process on . to sstuser@”%” identified by “密码”; //其他成员自动同步
添加访问数据库用户
其他成员启动服务
systemctl start mysql -------> 查看端口,确定服务启动,用修改的密码登录
测试配置
客户端随便登录一台服务器,测试是否自动同步数据.
查看集群状态
mysql> show status like “%wsrep%”;
测试集群的高可用,测试故障自动恢复
模拟停止任意一台服务器,任何一台数据库服务宕机都不影响用户存取数据,服务器运行后,自动同步宕机期间的数据.
Mysql服务启动读取wsrep文件,当主服务机宕机恢复时,需要修改wsrep.cnf文件,在集群成员列表中删除故障机,再启动服务,启动完成自动恢复数据,再添加至成员列表中.
MySQL存储引擎
MySQL 5.0/5.1 -----> MyISAM
MYSQL 5.5/5.6 -----> InnoDB
列出可用的存储引擎类型
mysql>show engines;
修改默认存储引擎
Vim /etc/my.cnf
[mysqld]
…
default-storage-engine=MyISAM
查看建表时使用的存储引擎
mysql>show create table 表名;
修改表的存储引擎
mysql>alter table 表名 engine=存储引擎;
建表时指定存储引擎
Mysql>create table 表名() engine=存储引擎(不指定默认);
Innodb存储引擎
主要特点:
–>支持行级锁定 ---->仅对被访问的行分别加锁
–>支持事务,事务回滚,外键
表文件
—> 表名.frm //存放表结构
—> 表名.ibd //存放表空间,数据
事务日志文件
– ibdata1
– ib_logfile0
– ib_logfile1
Myisam存储引擎
特点:
–支持表级锁 -------->对整张表加锁
–不支持事务,事务回滚,外键
表文件
–表名.frm //表结构
–表名.MYI //索引
–表名.MYD //数据
MySQL锁机制
锁粒度
—表级锁 -----对整张表加锁
—行级锁 -----仅对被访问的行分别加锁
锁类型
–读锁(共享锁):支持并发读
–写锁(互斥锁,排它锁):时独占锁,上锁期间其他线程不能读表或写表
查看当前锁状态
–show status like “%wsrep%”;
事务特性 ( ACID )
原子性:事务的整个操作是一个整体,不可分割,要么全部成功,要么全部失败.
一致性:事务操作的前后,表中的记录没有变化.
隔离性:事务操作的相互隔离不受影响的.
持久性:数据一旦提交.不可改变,永久改变表数据.
操作:
–mysql>show status like “%wsrep%”;
–set autocommit=off; —>//关闭自动提交,执行此操作后,只要不commit提交数据,就可以执行rellback(数据回滚),撤销所有操作,回到数据编辑前的原始状态.
第三周 day 1 NoSQL
RDBMS 关系数据库管理系统(MySQL,Mariadb,Oracle,DB2,SQL Server)
–按照预先设置的组织结构,将数据存储在物理介质上
–数据之间可以做关联操作
NoSQL (Memcached,Redis,MongoDB…)
-泛指非关系型数据库
-不需要预先定义数据存储结构
-每条记录可以有不同的数据类型和字段个数
部署Redis服务
Redis(远程字典服务器),高性能,分布式存储,持久化(定期把内存里数据存储在硬盘),支持多种数据类型string,list,hash,支持主从同步.
1.装包
–gcc 源码编译
./utils/install_server.sh //初始化
默认端口 6379
主配置文件 /tc/redis/6379.conf
数据库目录 /var/lib/redis/6379/
/var/lib/redis/6379/dumpedb //存放数据库数据,当停止服务时,自动把数据写入文件中(保存到硬盘中),运行中定期把内 存数据保存到硬盘中,当启动服务时,读取该文件数据加载到内存中.
日志文件 /var/log/redis_6379.log
服务启动 /usr/local/bin/redis-server
命令连接 /usr/local/bin/redis-cli
cd redis/ && yum -y install gcc && tar -xvf redis-4.0.8.tar.gz && cd redis-4.0.8/ && make && make install && cd utils/ && ./install_server.sh && netstat -atnulp && /etc/init.d/redis_6379 status
管理服务
]#/etc/init.d/redis_6379 start/status/restart/stop //stop/restart执行要修改/etc/init.d/redis_6379脚本
---->ttl 变量名 返回值为1,表示不过期,不为1表示已过期
6379> type name //查看key类型
---->只要是用set赋值变量,保存的类型;都是字符类型(string)
---->lpush赋值的变量类型为列表
6379> exists name //测试key名是否存在,如果存在,重新赋值会覆盖原有的值
6379> move school 1 //移动key到指定库
6379[1]> expire school 5 //设置key有效时间
6379[1]> ttl school //查看key值生存周期,当返回值为-1,表示该变量永不过期.
6379> del key名 //删除指定的key
–save //保存所有key到硬盘
–shutdown //停止服务
内存管理

优化设置

部署LNMP+Redis
1.装包
Nginx php-fpm gcc php pcre-devel zlib-devel redis
2.启用nginx配置动静分离
3.配置支持Redis
3.1装包
-php-devel autoconf automake
-tar -xvf php-redis-2.2.4.tar.gz
-cd phpredis-2.2.4/
–phpize //生成配置文件php-config及configure命令
-./configure --with-php-config=/usr/bin/php-config
-make && make install
4.修改配置文件,加载redis模块
-vim /etc/php.ini
–extension_dir = “模块目录路径”; /usr/lib64/php/modules/
–extension = “模块名”; // redis.so
–systemctl restart php-fpm
5.编写php写入脚本
访问测试
Day 2
Redis集群服务(分布式,高可用,自动备份)
工作原理:
客户端访问集群服务,定义key值,CRC16算法自动计算槽值(Slot),匹配至对应的redis主库节点(也就是分布式存储),从库自动备份数据.
1.配置管理主机,装包,环境准备
–rubygems(提供gem命令) ruby(解释ruby脚本) 脚本 redis-3.2.1.gem,官网下载
–chmod +x redis-3.2.1.gem
–which gem
–gem install redis-3.2.1.gem
–mkdir /root/bin/ 创建命令检索目录 (echo $PATH)变量,命令可以查看liunx执行命令检索的目录
– cd redis-4.0.8/src/
– cp redis-trib.rb /root/bin/ //拷贝redis-trib.rb脚本(管理集群的脚本)到检索目录,可以直接执行
2.至少六台服务器,修改redis服务器配置文件,启用集群功能,不设置登录密码
#]vim /etc/redis/6379.conf //修改配置文件
bind 192.168.4.56 //修改ip
port 6356 //修改端口（可选配置）
cluster-enabled yes //启用集群功能
cluster-config-file nodes-6379.conf //存储集群信息的配置文件
cluster-node-timeout 5000 //集群节点通信超时时间
端口查看 netstat -atnulp 集群通信端口=默认服务端口+10000
3.创建集群,加入集群的节点数据库必须是空的.
–]#redis-trib.rb create --replicas 1 192.168.4.51:6351 192.168.4.52:6352 192.168.4.53:6353 192.168.4.54:6354 192.168.4.55:6355 192.168.4.56:6356 … //replicas 数值 定义每台主库从库个数
/var/lib/redis/6379/ 数据库存放目录路径,若集群创建失败,槽口已被占用,需要停止redis服务,删除目录下所有文件, 重启redis服务,重新在管理服务器创建集群,存储数据的多少取决于分配的内存大小,而不是槽值多少.
/var/lib/redis/6379/nodes-6379.conf //存放集群信息
4.查看状态
–redis-trib 脚本用法
]#redis-trib.rb …

查看集群信息
]# redis-trib.rb info ip地址:端口 //随便一台redis服务器
检查集群主机角色,查看详细信息
]#redis-trib.rb check 192.168.4.51:6351
在redis服务本机查看集群信息
192.168.4.51:6351> cluster info
在redis服务本机查看集群节点信息
192.168.4.51:6351> cluster nodes
5.访问集群
在客户端可以连接集群中任意一台redis服务器
]#~]# redis-cli -c -h 192.168.4.55 -p 6355 // -c 指定访问集群模式
根据算法分布式存储数据在不同的master服务器redis服务中,从服务器自动备份.
6.测试集群的高可用.
随便停止一台master服务器的redis服务,对应的slave服务器自动被选举为master服务器,原master服务器启动后,会自动配置为当前master的slave,并且自动恢复数据.
管理集群
1.添加redis服务到集群,由于redis是分布式内存数据库,并不是hash槽(slots)越多存储的数据越多,所以当物理内存不足时,需要添加新的redis服务器到集群,缓解服务器压力,保证数据完整.
1.1 添加一台新的redis服务器,当不指定主从时,默认为主服务器,新添加的master主机需要手动分配hash槽.
添加master主机步骤
1.添加主机
]#redis-trib.rb add-node 新添加的主机IP地址:端口 集群任意一台IP地址:端口
2.分配hash槽(slots)
~]# redis-trib.rb reshard 192.168.4.58:6358 //重新分片
how many slots do you want to move (from 1 to 16384)? 4096 //指定移出hash槽个数
what is the receiving node ID? //指定接收hash槽的节点id
source node #1: //指定移出hash槽节点id
当槽中存放有数据时,若hash槽被平均分配到另外一台master redis服务器中,数据也跟着迁移.
1.2添加slave服务器
部署一台新的redis服务器
]#redis-trib.rb all-node --slave [ --master-id值] ip地址:端口 集群服务器任意一台ip地址:端口
如果不指定主节点id的话,默认把新节点随机添加为从服务最少的主库
2.移除redis集群服务器
移除redis集群slave服务器,当移除服务器时会自动停止被删除主机的redis服务.
]# redis-trib.rb del-node 192.168.4.51:6351 5df8de66b7d43c413d7590955850a366f6ba150d
删除节点 连接集群 被删除服务器的ID值
3.移除redis集群redis服务器
1.移除master服务器
1.1 释放占用的hash槽
2.1 移除主机
redis-trib.rb reshard 192.168.4.58:6358 //重新分片
指定接收槽值的节点id-------指定移除服务器的id值
redis-trib.rb del-node 192.168.4.51:6351 0cea4bc00fcc4b390b6a89d786d80764a2c1757f
删除节点 连接 指定被删除服务器ID
2.3 – redis-trib.rb rebalance ---------->> 平均分配集群中hash槽的命令,当槽中存放有数据时,若hash槽 被平均分配到另外一台master redis服务器中,数据也跟着迁移.
2. 移除slave服务器,直接移除,slave服务器没有hash槽(slots),当移除完成,会自动关闭redis服务.
]#redis-trib.rb del-node 集群任意一台ip地址:端口 slave服务id值
把删除的服务器还原到集群
cluster reset 命令行重置redis数据库 等同于 rm -rf /var/lib/redis/6379/
库中库中不予许有任何数据
在管理服务器中添加redis服务到集群中.
Day 3 主从复制 持久化 数据类型
主从复制结构模式
一主一从 一主多从 主从从
主从复制工作原理:
—salve向master发送sync命令;
—master启动后台存盘进程,并收集所有修改数据命令;
–master完成后台存盘后,传送整个数据文件到slave;
–slave接收数据文件,加载到内存中完成首次完全同步; //从服务器
—后续有新数据产生时,master继续收集数据修改命令依次传给slave,完成同步.
注意:配置为从服务器如果有数据,配置为从服务器后,同步主服务器后会覆盖原有数据,导致原有数据丢失.
配置一主一从
—info replication //查看主从信息
—slaveof master IP:端口 //临时在从服务器命令行配置为从服务器
—slaveoff no one //从服务器命令行临时取消为从服务器
永久配置主从结构
–vim /etc/redis/6309.conf
–slaveof master IP 端口
–masterauth 主服务器密码
配置一主多从
–config set masterauth 主机密码 //命令行直接指定主机密码
–slaveof 主机IP 端口 //指定主机地址,端口
–config rewrite //写入配置文件
–config get masterauth //获取值
配置主从从结构,参照一主多从
哨兵服务
—加入主从结构的节点master主机必须设置从服务器用来连接密码,从服务器设置主库密码.
—监视master服务器,发现master宕机后,将从服务器升级为主服务器
–主配置文件: 需要手动创建,默认创建在/etc/目录下
—vim /etc/sentinel.conf
–bind 0.0.0.0 //服务地址
–port 26379 //指定端口
–sentinel monitor server51 192.168.4.51 6351 1 //指定监视的master主机IP地址,端口,票数
–sentinel auth-pass server51 123456 //连接服务密码,故障切换从服务器用来连接主服务器
测试哨兵服务,高可用
停止master主机,哨兵脚本自动推选出新的master服务器,当故障主机启动服务,自动设置为从服务器,并且自动同步数据.
数据的持久化,实际生产中\需要同时开启RDB与AOF持久化选项.
RDB /var/lib/redis/6379/dump.rdb
Redis数据库文件,数据持久化方式之一,数据持久化默认方式;
按照指定的时间间隔,将内存中的数据集快照(Snapshot)写入硬盘;
恢复时,将快照文件直接读入内存;
优点: —高性能的持久化实现 ----------创建一个子进程来执行持久化,先将这个数据写入临时文件,持久化过程结束后,再用这个临时文件替换上次持久化好的文件;过程中主进程不作任何操作.
缺点: —意外\宕机时,丢失最后一次持久化的所有数据(由于设置了数据从内存保存到硬盘的频率,当key值改变不满足时间或者数量时,数据仍保存在内存中,当意外宕机时,数据丢失).
优化设置: /etc/redis/6379.conf
-----数据以内存保存到硬盘的频率(可自定义)
–save 900(秒) 1 //15分钟且有一个key值改变
–save 300 10 //5分钟且有10个key值改变
–save 60 10000 //1分钟且有10000个key值改变
使用RDB文件备份数据,恢复数据
–备份dump.rdb文件
–拷贝dump.rdb文件到/var/lib/redis//6379/下,重启服务
AOF
-----追加方式记录写操作的文件,记录redis]服务所有写操作,不断地将新的写操作,追加到文件的末尾
启用AOF
----config set appendonly yes //使用命令行开启AOF持久化选项
----config rewrite //写进配置文件
使用AOF文件恢复数据
-----备份数据 cp /var/lib/redis/6379/appendonly.aof 备份目录
-----恢复数据 cp 备份数据 …/6379/appendonly.aof
-----/etc/init.d/redis_6379 start //启动服务