mysql基础复习

1.数据库:存放数据的仓库,按照数据结构来组织、存储和管理数据的仓库

a)关系型数据库,存储的格式可以直观地反映实体间的关系的数据存储系统。

b)非关系型数据库:not only sql,非关系型的、不保证遵循ACID原则的数据存储系统。(ACID,指数据库事务正确执行的四个基本要素的缩写。包含:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。)

c)在轻量或者小型的应用中,使用不同的关系型数据库对系统的性能影响不大,但是在构建大型应用时,则需要根据应用的业务需求和性能需求,选择合适的关系型数据库。

2.SQL:结构化查询语言(Structured Query Language)简称SQL,常见4种分类:DMCQ

a)数据定义语言(DDL:Data Definition Language):其语句包括动词CREATE,ALTER和DROP。在数据库中创建新表或修改、删除表(CREAT TABLE 或 DROP TABLE);为表加入索引等.
适用范围:对数据库中的某些对象(例如,database,table)进行管理,如Create,Alter和Drop.

b)数据操作语言(DML:Data Manipulation Language):其语句包括动词INSERT、UPDATE和DELETE。它们分别用于添加、修改和删除。

c)数据控制语言(DCL:Data Control Language):常用操作是授权。它的语句通过GRANT或REVOKE实现权限控制,确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

d)数据查询语言(DQL:Data Query Language):其语句,也称为“数据检索语句”,用以从表中获得数据,确定数据怎样在应用程序给出。保留字SELECT是DQL(也是所有SQL)用得最多的动词,其他DQL常用的保留字有WHERE,ORDER BY,GROUP BY和HAVING。这些DQL保留字常与其它类型的SQL语句一起使用。

3.数据库库的操作

a)常用动作:create,show,use,select,drop
b)create,创建数据库:
create database test1 charater set 'utf8' collate 'utf8-general-ci';
//后面的参数可以省略用默认的

c)show,查看数据库信息:
show databases;//查看所有的数据库;
show creaate database test1;//查看数据库创建时的语句

d)use,使用数据库:use test1;
e)select,查看数据库版本:
select version();//查看当前数据库版本
select database();//查看当前use使用的数据库

f)drop,删除数据库:drop database test1;

4.表的操作

a)常用动作:create,show/desc,alter,drop,rename
b)create,创建语法:
create table 表名(
字段名 类型(长度) 约束
);
create table user(
id int primary key not null
);//最后一个字段不用逗号

c)show/desc,查看创建语句:
show create table user;
desc user;

d)alter,修改表信息,操作有:add,modify,drop,change
alter table 表名 操作 [column|constraint] ...;//默认不写是修改字段:column
alter table user add username varchar(20) unique; //添加一列,//最后添加after/before 字段名,可调整插入字段的顺序。
alter table user modify name varchar(50) unique;//修改字段的类型为varchar(50)
alter table user drop iamge;
alter table user change username name varchar(50) unique; //修改字段名
rename table user to users;//修改表名

5.DML操作:insert,update,delete

a)insert,插入,有3种方式
insert into 表名 values(所有字段的值); //用于插入所有的字段
insert into 表名(字段名) value(对应字段的值);//插入指定的值
insert into 表名 select ...//使用查询的结果作为表的插入
如:insert into users(id,name) select id,name from users_old;

b)update,修改
update 表名 set 字段='' where 条件
c)delete,删除
##### 注意:与truncate区别,delete是纯粹一行一行删除数据,而truncate是删除旧表建新表,会重置自增标记,所以truncate是DDL!!!
delete from 表名 where 条件
delete from user where id=1

6.DQL:查询语句

常用关键字:select,where,group by , order by,having
a)select 查询开始
b)where 约束条件,在
c)group by 分组,常与聚合函数(SUM, COUNT, MAX, AVG等)配合
d)order by 排序
e)having 过滤条件,对已经返回的结果筛选,并可以使用聚合函数

7.where与having区别:

a)位置:如有group by,where在groupby 之前;having在之后
b)作用范围:where在结果返回之前起作用;having是在查询返回结果集以后,对查询结果进行的过滤操作,也就是对group by 后的数据作过滤。
select product,sum(price) as sumprice from orders
where
price>90 //结果返回之前,对要查询的数据有约束,行数据中price大于90的才被查询出来
group by product
having sum(price)>100;//结果返回之后,再作过滤,分组查询后统计结果中总价大于100的留下来。

c)使用聚合函数:where不能使用,而having可以使用聚合函数

8.聚合函数:SUM, COUNT, MAX, AVG等

9.约束Constraint:规定表中数据的规则。6种常见约束

“`
a)not null,不为空约束
b)unique ,唯一约束,一个表可以有多个字段组成,也可能有多个unique约束
c)primary key :主键约束,一个表只能有一个,相当于not null和unique合并。后面常用auto_increment; insert into orders values(null,'电视','900');//当使用auto_increment时用nul代替自动增长。
d)default,默认值约束
e)check,用于限制列中的值的范围
f)foreign,一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。虽然能保证数据的完整性,但影响性能和增加数据库复杂度,强烈建议不使用,而是把逻辑放在代码层。

****独立使用sql创建约束,请自行查阅****

“`

10.表设计

a)数据库三范式
1.字段的原子性:字段表示的值不能再有其他类型
2.主键的唯一性:表要有主键,其他字段都依赖于表中唯一的主键。
3.消除传递依赖:要求一个数据库表中不包含已在其它表中已包含的非主键字段(可以有一个其他表的主键)
4.不一定要严格按照三范式建表,为了需求和性能,可以适当放松,比如字段冗余
b)表结构关系
1.一对一:表的垂直切分,常用于表中的字段较多或者表中字段有分为常用和不常用,如用户登录信息表与用户详细信息表
2.一对多:不用建关系表,直接在表中增加另一张表的主键
3.多对多:一般需要建中间关系表来存放关系。
c)多表查询
1.内连接:范围是交集,join on;
2.左外连接:只要左边表中有记录,数据就能检索出来,而右边有的记录必须在左边表中有的记录才能被检索出来。 范围是左集的所有和与右集交叉的集合,假如没有右集,左集一样有但右集为null
3.右外连接:与左链接相反.范围是右集的所有和与左集交叉的集合,假如没有左集,右集一样有但左集为null

### 11.子查询:一个查询的结果是另一个查询的条件
### 12.索引:排好序的数据结构,用来提高查询速度.教程
a)索引的数据结构有四种:二叉树,红黑树(平衡二叉树),B树,Hash
b)mysql索引的常用存储类型/数据结构有两种:B+TREE、HASH
c)索引的类型:单列索引、唯一索引、主键索引和聚集索引
d)聚集索引:索引与叶子中的数据保存在同一个文件中,如主键索引,所以比较快.聚集索引一个表只有一个,聚集索引存储记录逻辑顺序与物理相同,且是物理上连续存在,非聚集索引是逻辑上的连续。
e)非聚集索引:索引与数据保存在不同文件中,索引中的叶子保存的是真实数据的地址,而不是数据本身.

mysql二进制安装

####1.卸载旧mysql数据库,删除干净
####2.解决依赖包:libaio(从MySQL5.5版本开始对此包的依赖)安装libaio
yum install libaio # install libraryrpm的安装方式:
####3.下载二进制包:glibc
curl https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz >mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
####4.添加mysql用户和用户组
groupadd mysql
useradd -g mysql mysql
chown -R mysql:mysql /usr/local/mysql
####5.初始化数据库:初始密码在初始化时,显示在屏幕上
bin/mysqld –initialize –user=mysql –basedir=/usr/local/mysql –datadir=/data/mysql/
####6.修改配置文件
vim /etc/my.cnf
####7.创建软链接或者自己写shell
ln -s /usr/local/mysql/bin/mysql /usr/bin
8.启动并修改root密码,初始密码在初始化时,显示在屏幕上
alter user ‘root’@’localhost’ identified by ‘111111’;

mysql8.0登录

1.使用旧密码形式,新增suroot
vim my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password

先创建一个用户

create user ‘sroot’@’%’ identified by ‘123123’;
#再进行授权
grant all privileges on . to ‘sroot’@’%’ with grant option;

http://blog.51cto.com/arthur376/2108183?utm_source=oschina-app

2.修改现在有root密码形式
https://blog.csdn.net/c359719435/article/details/80432508

3.降级使用5.7版本

mysql使用keepalive+互为主从,实现高可用,主从分离

http://gaoke.iteye.com/blog/2283890
https://www.2cto.com/database/201411/353346.html

1.主从分离,两个VIP
主库:192.168.0.90 (写库,VIP:192.168.0.199)
从库:192.168.0.91 (读库,VIP:192.168.0.200)
2.主库keepalived.conf:

vrrp_instance VI_1 {   
    state BACKUP   #从库VI_1同为backup
    interface eth0
    virtual_router_id 51  #与VI_2的要不相同
    priority 100    #主库写,这里设置大
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.0.199
    }
}

vrrp_instance VI_2 {
    state BACKUP    #从库读,为MASTER
    interface eth0
    virtual_router_id 44  #与VI_1的要不相同
    priority 50     #从库读,这里设置小
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.0.200
    }
}

virtual_server 192.168.0.199 3306 {
     delay_loop 2
     lb_algo wrr #带有权重的轮询
     lb_kind DR
     persistence_timeout 60 #同一IP的连接60秒内被分配到同一台真实服务器
     protocol TCP
     real_server 192.168.0.90 3306 {
         weight 3 #权重为3
         notify_down /data/keepalived_shutdown.sh #当mysq服down时,执行此脚本,杀死keepalived实现切换, 自杀脚本.
         TCP_CHECK {
             connect_timeout 10
             nb_get_retry 3
             delay_before_retry 3
             connect_port 3306
         }
     }
}

virtual_server 192.168.0.200 3306 {
     delay_loop 2
     lb_algo wrr #带有权重的轮询
     lb_kind DR
     persistence_timeout 60 #同一IP的连接60秒内被分配到同一台真实服务器
     protocol TCP
     real_server 192.168.0.90 3306 {
         weight 3 #权重为3
         notify_down /data/keepalived_shutdown.sh #当mysq服down时,执行此脚本,杀死keepalived实现切换, 自杀脚本.
         TCP_CHECK {
             connect_timeout 10
             nb_get_retry 3
             delay_before_retry 3
             connect_port 3306
         }
     }
}

3.从库keepalived.conf:

vrrp_instance VI_1 {
    state BACKUP   #与主库写VI_1相同
    interface eth0
    virtual_router_id 51   #与主库写VI_1相同,与从库读不同
    priority 50
    advert_int 1
    #nopreempt #不主动抢占资源,设置非抢占模式
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.0.199
    }
}

vrrp_instance VI_2 {
    state MASTER  #从库读,这里设置成master
    interface eth0
    virtual_router_id 44 
    priority 100   #从库读,这里设置成大
    advert_int 1
    #nopreempt #不主动抢占资源,设置非抢占模式
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.0.200
    }
}

virtual_server 192.168.0.199 3306 {
     delay_loop 2 #每2秒检查一次real_server存活
     lb_algo wrr
     lb_kind DR
     persistence_timeout 60
     protocol TCP
     real_server 192.168.0.91 3306 {
         weight 10
         notify_down /data/keepalived_shutdown.sh
         TCP_CHECK {
             connect_timeout 10 #连接超时时间
             nb_get_retry 4 #重连次数
             delay_before_retry 3 #重连间隔时间
             connect_port 3306 #健康检查端口,配置自己mysql服务端口
         }
     }
}


virtual_server 192.168.0.200 3306 {
     delay_loop 2 #每2秒检查一次real_server存活
     lb_algo wrr
     lb_kind DR
     persistence_timeout 60
     protocol TCP
     real_server 192.168.0.91 3306 {
         weight 10
         notify_down /data/keepalived_shutdown.sh
         TCP_CHECK {
             connect_timeout 10 #连接超时时间
             nb_get_retry 4 #重连次数
             delay_before_retry 3 #重连间隔时间
             connect_port 3306 #健康检查端口,配置自己mysql服务端口
         }
     }
}

4.keepalived_shutdown.sh脚本

#!/bin/bash
#kill掉keepalived进程,以防止脑裂问题。
pkill keepalived

mysql使用keepalive+互为主从,实现高可用

http://blog.csdn.net/l1028386804/article/details/52904449
http://blog.csdn.net/socho/article/details/51804720
https://www.cnblogs.com/dazhidacheng/p/8028474.html
在主库机器上:vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived
global_defs {
    router_id HA_MySQL #标识,双主相同
}
vrrp_instance VI_1 {
     state BACKUP #注意,主从两端都配置成了backup,因为使用了nopreempt,即非抢占模式
     interface eth0
     virtual_router_id 51 #分组,主备相同 
     priority 100 #优先级,这个高一点则先把它作为master
     advert_int 1
     nopreempt #不主动抢占资源,设置非抢占模式
     authentication {
         auth_type PASS
         auth_pass 1111
     }
     virtual_ipaddress {
         192.168.1.199
     }
}
virtual_server 192.168.1.199 3306 {
     delay_loop 2
     lb_algo wrr #带有权重的轮询
     lb_kind DR
     persistence_timeout 60 #同一IP的连接60秒内被分配到同一台真实服务器 
     protocol TCP
     real_server 192.168.1.201 3306 {
         weight 3 #权重为3
         notify_down /data/keepalived_shutdown.sh #当mysq服down时,执行此脚本,杀死keepalived实现切换, 自杀脚本.
         TCP_CHECK {
             connect_timeout 10
             nb_get_retry 3
             delay_before_retry 3
             connect_port 3306
         }
     }
}

在从机器上:vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived
global_defs {
     router_id HA_MySQL
}
vrrp_instance VI_1 {
     state BACKUP
     interface eth0
     virtual_router_id 51
     priority 90 #优先级,这个低一点
     advert_int 1
     nopreempt
     authentication {
         auth_type PASS
         auth_pass 1111
     }
     virtual_ipaddress { #虚拟IP (VIP)
         192.168.1.199
     }
}
virtual_server 192.168.1.199 3306 {
     delay_loop 2 #每2秒检查一次real_server存活
     lb_algo wrr
     lb_kind DR
     persistence_timeout 60
     protocol TCP
     real_server 192.168.1.202 3306 {
         weight 3
         notify_down /data/keepalived_shutdown.sh
         TCP_CHECK {
             connect_timeout 10 #连接超时时间
             nb_get_retry 3 #重连次数
             delay_before_retry 3 #重连间隔时间
             connect_port 3306 #健康检查端口,配置自己mysql服务端口
         }
     }
}

杀死keepalived的脚本
vim /data/keepalived_shutdown.sh

#!/bin/bash
#kill掉keepalived进程,以防止脑裂问题。
pkill keepalived

#####注意:
有一点要注意的是,主从两端的state,都配置成了backup,因为使用了nopreempt,即非抢占模式。
举个例子,当主端先启动mysql实例和keepalived后,如果此时从端也启动了mysql实例和keepalived,那么vip不会跳到从端上去,即使它的优先级为100,要大于主端的90
而如果不设置nopreempt,那么这个时候,又分2种情况:
1.state相同,即都是master或都是backup
优先级高的,会占有vip,和角色无关
2.state不同,即master->backup或backup->master
优先级高的,会占有vip,和角色无关
前提不同,结果都是一样的,即优先级是主导,谁的优先级高,vip就漂到谁那里
查看切换日志:/var/log/messges
#####脑裂问题:
VIP同时被绑定到多个机器上,都变成了主
查看是否绑定了VIP: ip addr#如果一个网卡有两个IP表示绑定了一个vip

#####标识:
互为主从才设置成一样
当单向主从时,从库还未完全同步主库数据,主库就挂掉了,这时升级从库为主库也无法解决问题.但双主结构,配合keepalived,实现无缝转换,可最大化解决此问题.
#####mysql修改:
增加虚拟IP访问数据的权限:
grant select,insert,update,delete on . to test2@192.168.0.199 Identified by “abc”;
grant select,insert,update,delete on . to test2@192.168.0.200 Identified by “abc”;
####### 防火墙可能也要增加虚拟IP的访问权限
firewall-cmd –permanent –zone=public –add-rich-rule ‘rule family=ipv4 source address=192.168.0.199 port port=3306 protocol=tcp accept’
firewall-cmd –permanent –zone=public –add-rich-rule ‘rule family=ipv4 source address=192.168.0.200 port port=3306 protocol=tcp accept’

mysql互为主从(双主)

参考http://www.tanguu.com/2018/02/22/mysql-%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6/
设置好一个主从后,反过来再设置一次,就是互为主从.
###从机只有变成主机数据被修改的时候,才会改变自己的bin-log地址,同步是不会改变bin-log地址的。

####在添加用户时,使用’username’@’%’ 方便测试