MySQL数据库详解
一、数据库概述
1、数据库与文件系统文件系统对比。
数据冗余和不一致性
数据访问
数据格式相对独立
数据完整性和原子性
数据的并发访问
数据的安全性问题
2、数据库概念
1、DBMS:数据库管理系统
2、数据模型
层次模型：
按照层次结构的形式组织数据库的模型
IMS
缺点：大量的冗余数据
网状模型：
按照网状结构的形式组织数据库数据的模型
DBTG
缺点：后期维护困难
关系模型：
按照关系结构（行和列，表）的形式组织数据库数据的模型。
RDBMS 关系式数据库管理系统
3、关系
E-R模型
实体：数据对象
联系：一个或多个实体之间的联系关系
属性：实体的某一个特性
4、SQL类型
DML：数据操作语言，操作数据库中的数据。如select update
DDL：数据描述语言，用来建立数据库，定义数据关系。如create drop
DCL：数据控制语言，用来控制数据库的权限组件。如grant revoke
5、数据结构
文件的逻辑关系：
上层：文件
中层：文件系统
下层：磁盘中的数据块
数据库的逻辑关系：
上层：数据表（展现给用户的）
中层：存储引擎（数据库的组件）
下层：文件系统
存储引擎：为存储数据、管理数据、查询数据所建立的数据库实现方法。
6、事务和锁的概念
事务：SQL语句的批处理。要么执行成功，要么保持原样。
锁 ：
表级锁：速度快、冲突多
行级锁：冲突少、速度慢
页级锁：折中方案（锁定相邻的多行）速度较快，冲突较少
约束：
域约束：限制每个数据的类型
外键约束：
唯一性约束，主键约束。
7、MySQL常用的存储引擎：
MyISAM
MySQL诞生起默认的存储引擎
较高的插入、查询速度。
不支持事务，行级锁以及外键约束等功能。
InnoDB
支持事务、行级锁、外键约束等功能。
Memory
存在于内存中，通常使用hash存储数据，速度快。
8、数据的存储和查询
存储管理器（专用的存储组件，通过DDL来创建数据表结构，再通过DML来保存SQL语句的处理结果）
权限及完整性管理器
事务管理器
文件管理器
缓冲区管理器
查询管理器（负责接收用户的查询请求，理解用户请求，并将结果交给存储管理器来真正实现数据管理）
DDL、DML解释器
查询执行引擎
9、数据库的后台工作模式
单进程多线程的工作模式
1、守护线程
2、应用线程
10、数据库优化
缓存
用户的请求不需要每次都消耗系统资源）
线程重用
用户退出后不撤销线程，而是分配给其他用户使用。
提升内存
缺点：不支持海量数据，不支持SMP对称多处理器架构。
二、MySQL的命令基础
1、安装
社区版
GPL
企业版
集群版
主配置文件：/etc/my.cf
[mysqld] 数据库主进程设置区
datadir=/var/lib/mysql 数据存放目录
socket=/var/lib/mysql/mysql.sock 套接字文件目录
user=mysql 指定运行用户
symbolic-links=0 是否允许软连接连接一些文件让系统来调用
[mysqld_safe] 启动设置区
log-error=/var/log/mysqld.log 错误日志
pid-file=/var/run/mysqld/mysqld.pid 进程id存放文件
2、SQL语句
管理：
设置用户名密码
初次设置：mysqladmin -uroot password '密码'
更改 ：mysqladmin -uroot password '密码' -p 需要输入原密码。
登陆数据库：
mysql -u root -h localhost -P 3306 -S /var/lib/mysql/mysql.sock -p
-u 指定用户
-h 指定数据库地址
-P 指定端口
-S 指定套接字文件
-p 输入用户名密码
创建用户
create user 'zhuangsan'@'localhost' identified by '123456'; 用户名zhangsan,只能本地登陆，密码123456
登陆地址：
localhost 本地登陆
IP地址 只能从指定IP地址登陆
% 除本地外的所有地址。
更改密码
set password=password('123'); 登陆用户更改自己的密码（所有用户都可以操作）
set password for 'zhuangsan'@'localhost'=password('123'); 为zhangsan用户在本地登陆更改密码。（管理员操作）
管理员用户忘记密码
更改数据配置文件/etc/my.cnf 在[mysqld]主配置设置区增加 skip-grant-tables
重启服务器 service mysqld restart
直接执行mysql不加用户名密码登陆。
更改root密码 update mysql.user set password=password('890') where user='root';
删除配置文件中添加的内容，重启服务，用新密码登陆。
权限操作：
show grants for 'zhuangsan'@'localhost'; 查看zhangsan用户在本地的权限
grant all on aa.* to zhangsan@'localhost' identified by '123';
revoke drop on aa.* from 'zhangsan'@'localhost'; 删除drop权限
all表示所有权限
aa.*表示aa数据库下的所有表
zhangsan@'localhost'表示登陆方式
identified by '123'密码123
数据操作
增
create database aa; 创建数据库
mysql> create table a2( 创建表a2
-> id int unsigned not null auto_increment, 字段名:id、数值类型:int、不允许为负数:unsigned、不允许为空:not null、自增:auto_increment
-> name char(20) not null default '', 字段名:name、数值类型:char(20)、不许为空、默认值空
-> age int not null default '0', 字段名:age、默认值：0
-> primary key (id)); 主键：id
insert into a2 (name,age) values ('zhuangsan','13'); 为a2表插入记录
insert into a1 (id,name) select id,name from a2; 将a2中的id,name字段复制到a1表里面
删
drop database aa; 删除数据库，如果里面有表会一并删除。
delete from a2 where id=1;
delete from a2 where age between 20 30; 区间删除，删除a2表里age字段在20到30之间的记录。
between 值1 值2 匹配区域数据。
改
update aa.a1 set name='lisi' where id=1; 将aa数据库里a1表的id字段等于1的记录里name字段更改为lisi
alter table a1 rename a3; 更改表名，将a2改为a3
alter table aa.a2 modify name char(30) not null default 'lisi'; 更改字段属性
alter table aa.a2 change name username char(30) not null default 'lisi'; 连着字段名一块更改，name改为username
alter table aa.a2 add time datetime; 添加一个字段，类型是datetime
alter table aa.a2 age int first; 表示记录，first表示添加到第一行。
alter table aa.a2 add age int after id; 在id字段之后添加一个字段。after表示添加到某条字段后。
alter table aa.a2 drop time; 删除字段
查
show databases; 查看所有数据库
use 数据库; 选择需要操作的数据库
show tables; 显示数据库中的数据表
describe a1; 查看表结构，或者简写成desc a1;也可。
select * from 表\G; 显示表里的所有记录，\G表示竖排显示。
三、MySQL备份
1、备份方案遵循
数据库的失效次数减到最少
数据库的恢复时间减到最少
数据库失效后，确保尽可能少的数据丢失或根本不丢失
总结：通过特定的方式，将数据库的必要文件复制到转储设备的过程。
2、备份方式
冷备份
热备份
快照备份，例如LVM写时复制（copy-on-write）
逻辑备份
提取数据中的所有记录
mysqldump(主要的备份方式) 单线程，速度慢
物理备份
直接拷贝数据库文件和日志来完成备份，多线程、速度更快
云备份
异地备份
高可用
负载均衡
3、mysqldump备份
mysqldump -uroot -p aa > 1.sql 将aa数据库备份至1.sql文件
mysqldump -uroot -p test a1 > 2.sql 将test库中的a1表备份至2.sql数据库
mysqldump -uroot -p --databases aa bb > 3.sql 同时将aa和bb数据库备份至3.sql文件,--databases参数表示后面空格隔开的都是数据库
mysqldump -uroot -p --all-databases > 4.sql --all-databases表示备份所有数据库
mysql -uroot -p aa < 2.sql 将2.sql还原至aa数据库中（单个数据库或多个表还原时需要指定数据库）
mysql -uroot -p < 3.sql 将多个数据库还原的时候不需要指定库名称。
4、mysqlhotcopy备份
mysqlhotcopy --flushlog -u='root' -p='890' --regexp=^a /backup/
--flushlog 标准格式。表示通过日志进行备份
-u：用户名 -p：密码 --regexp：要使用的正则表达式
最后跟备份目录
使用mysqlhotcopy备份是直接将数据目录下的数据拷贝过来
还原的时候直接将备份的数据库cp到数据库目录
文件格式：（每个数据表都保存成三个后缀名不同的文件）
.frm 数据表格式
.myd 数据表内容
.myi 数据表索引
mysqldump备份的文件是数据库端的sql语句集合，而mysqlhotcopy是快速文件意义上的COPY。速度上备份sql语句更快，mysqldump可以适用MYISQM和INNODB引擎，nysqkhotcopy只适用于MYISAM
日志备份
MySQL常见日志：
错误日志
log_error=路径
查询日志/一般查询日志
general_log=NO/OFF
general_log_file=路径
log=NO/OFF 全局日志打开开关
慢查询日志
slow_query_log=NO/OFF
slow_query_log_file=路径
long_query_time=时间
log-slow-queries=路径 根据版本不同可能需要添加这一条
二进制日志
记录更改数据库状态的操作。
log-bin=文件名
mysqlbinlog mysql.bin.000001 查看二进制日志
中继日志
主从环境中，从服务器保存数据的日志文件relay-bin.000001
查看日志信息：
show global variables like "%log%"; 显示所有日志开启状态
show global variables like "%long_query%"; 显示慢查询设置的时间
show binary logs; 查看当前系统中正在使用的二进制文件
show binlog events in 'mysql-bin.000001'; 查看文件的详细信息
show master status; 查看简要信息
通过二进制日志进行备份和还原
二进制日志管理工具：mysqlbinlog
两种恢复方式：
根据时间恢复数据：
mysqlbinlog --start-datetime '2019-03-01 04:18:13' --stop-datetime '2019-03-01 04:19:07' mysql-bin.000002 | mysql -uroot -p
--start-datetime 选定日志的开始时间
--stop-datetime 选定日志的结束时间
| mysql -uroot -p 将截取的日志输出到数据库中
根据数据大小恢复数据：
mysqlbinlog --start-position=189 --stop-position=311 mysql-bin.000002 | mysql -uroot -p
--start-position 选定开始大小
--stop-position 选定结束大小
多机备份：
备份原理：
从服务器通过读取主服务器的二进制日志文件来进行数据同步
备份过程：
当客户端对主服务器进行数据操作时，主服务器的I/O线程将操作写入二进制日志文件mysql-bin.000001中。
主服务器给从服务器读取二进制日志文件的授权
管理员通过change master to ...语句。将需要读取的操作写入master.info中
从服务器的I/O线程根据master.info读取主服务器的二进制日志文件
从服务器将读取的二进制日志文件写入本机的继日志文件relay-bin.000001中。
从服务器的SQL线程读取中继日志，将SQL语句操作写入数据库中。完成备份
主从配置：
将数据单向备份至从服务器
主
配置文件my.cnf
log-bin=mysql-bin 开启二进制日志
server-id=205 server-id表示唯一标识符。
授权
grant replication slave on *.* to 'zhangsan'@'10.248.24.206' identified by '123456';
flush privileges; 刷新权限列表，有些老版本更改权限后需要操作一下
从
配置文件my.cnf
log-bin=mysql-bin 开启二进制日志
server-id=206 server-id表示唯一标识符。
读取信息：
change master to master_host='10.248.24.205',master_user='zhangsan',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=339;
启用从服务器属性：
start slave;
查看从服务器状态信息。
show slave status\G; 保证两个YES，表示主从正常。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
主主配置：
将数据双向备份至对方主机。
配置文件选项：
replicate-do-db=aa 只复制指定数据库
replicate-do-table=a1 只复制指定数据表
replicate-ignore-table=a1 复制时忽略指定数据表
binlog-do-db 只记录指定数据库的更新到二进制文件
binlog-do-table 只记录指定数据表的更新到二进制文件
binlog-ignore-db 忽略指定数据库的更新到二进制文件
auto-increment-increment=2 指定自增长的步长为2（用于避免主键冲突）
auto-increment-offset=1 指定起始值为1
配置文件：
主：
log-bin=mysql-bin
server-id=205
replicate-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
auto-increment-increment=2
auto-increment-offset=1
备：
log-bin=mysql-bin
server-id=206
replicate-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
auto-increment-increment=2
auto-increment-offset=2
配置方式：
先以第一台为主第二台为从进行授权配置。然后以第二台为主第一台为从进行授权配置。
多主一从
原理：主服务器正常设置，在从服务器上模拟多个mysql实例，每个实例当作一台从服务器对应主服务器。
所用到的模块：mysqld_multi
主服务器设置：略
从服务器设置
配置文件：
[mysqld_multi] 建立模块区域
mysqld=/usr/bin/mysqld_safe 指定启动脚本路径
mysqladmin=/usr/bin/mysqladmin 指定更改密码脚本所在路径
log=/tmp/mysqld_multi.log 指定日志记录位置
[mysqld1] 创建数据库实例1(第几个实例就在mysqld后面加数字)
port=3306 监听端口
datadir=/var/lib/mysqla/ 数据目录
pid-file=/var/lib/mysqla/mysqld.pid pid文件
socket=/var/lib/mysqla/mysql.sock 套接字文件
user=mysql 指定运行用户
server-id=207
[mysqld2] 创建数据库实例2
port=3307
datadir=/var/lib/mysqlb/
pid-file=/var/lib/mysqlb/mysqld.pid
socket=/var/lib/mysqlb/mysql.sock
user=mysql
server-id=207
初始化MySQL进程，生成数据目录
mysql_install_db --datadir=/var/lib/mysqla/ --user=mysql
mysql_install_db --datadir=/var/lib/mysqlb/ --user=mysql
更改数据目录属主
chown -R mysql /var/lib/mysqla/
chown -R mysql /var/lib/mysqlb/
启动数据库实例
mysqld_multi --defaults-file=/etc/my.cnf start 1
mysqld_multi --defaults-file=/etc/my.cnf start 2
登陆实例1并设置从服务器属性
mysql -P 3306 -S /var/lib/mysqla/mysql.sock
change master to master_host='10.248.24.205',master_user='zhangsan',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=336;
start slave；
show slave status\G
登陆实例2并设置从服务器属性
mysql -P 3307 -S /var/lib/mysqlb/mysql.sock
change master to master_host='10.248.24.206',master_user='zhangsan',master_password='456',master_log_file='mysql-bin.000001',master_log_pos=336;
start slave;
show slave status\G
三、MySQL读写分离
将服务器事务性查询交由主服务器处理，将服务器select类查询交由从服务器处理。适用于读取操作远大于写操作的场景
提升性能的原因：
1、物理服务器数量增加、提升负荷
2、主从服务器架构，减少了数据库锁的消耗
3、提升从服务器性能（MYISAM）
4、数据写入的性能提升
5、减少网络阻塞
6、分摊读取
7、增加冗余
所用中间件：
Mysql proxy
读写分离搭建
搭建主从环境：略
配置Java环境
mkdir /amoeba
tar -zxf jdk-7u40-linux-x64.gz
ln -s jdk1.7.0_40/ /amoeba/jdk
vim /etc/profile
JAVA_HOME=/amoeba/jdk
export JAVA_HOME
PATH=$JAVA_HOME/bin:$PATH
export PATH
CLASSPATH=.:$JAVA_HMOE/bin/tools.jar:$JAVA_HOME/lib/dt.jar:$CLASSPATH
export CLASSPATH
source /etc/profile 加载一下
java -version 测试Java环境配置
安装amoeba
unzip amoeba-mysql-1.3.1-BETA.zip -d /usr/local/amoeba 解压amoeba到指定路径
chmod -R +x /usr/local/amoeba/bin/ 给amoeba的bin目录执行权限
配置amoeba
vim /usr/local/amoeba/conf/amoeba.xml 更改amoeba主配置文件(配置文件是默认的根据具体需要更改)
<server> server区域时amoeba的参数配置区域
<property name="port">8066</property> 默认端口
<property name="ipAddress">10.248.24.207</property> amoeba的地址
<property name="user">root</property> 登陆用户
<property name="password">963</property> 登陆密码
</server>
<dbServerList> 数据库列表配置区域
<dbServer name="server1"> 单台数据库配置区域，如果有多台，可以复制粘贴此区域
<property name="port">3306</property> 数据库端口
<property name="ipAddress">10.248.24.205</property> 数据库地址
<property name="schema">test</property> 要操作的数据库名，要操作多台库，复制粘贴多台。
<property name="user">zhangsan</property> 登陆数据库所用的用户名
<property name="password">123</property> 登陆数据库所用的密码
</dbServer>
<dbServer name="master" virtual="true"> master数据库池（用于写入操作），单台数据库要加到数据库池里，可以配置集群访问方式
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<property name="poolNames">server1</property> 加入的数据库，多台以分号隔开
</poolConfig>
</dbServer>
<dbServer name="server2">
<property name="port">3306</property>
<property name="ipAddress">10.248.24.206</property>
<property name="schema">test</property>
<property name="user">zhangsan</property>
<property name="password">456</property>
</dbServer>
<dbServer name="slave" virtual="true"> slave数据库池（用于读取操作）
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<property name="poolNames">server2</property>
</poolConfig>
</dbServer>
</dbServerList>
<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
<property name="defaultPool">server1</property> 默认操作server1
<property name="writePool">master</property> 写入数据操作master数据库池
<property name="readPool">slave</property> 读取数据操作slave数据库池
</queryRouter>
更改amoeba启动脚本
vim ./bin/amoeba
DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k" 将最后的值改为256，要不然显示有问题。
启动amoeba
nohup bash -x /usr/local/amoeba/bin/amoeba &
netstat -antp | grep 8066 查看端口是否起来
ps aux 查看有没有amoeba进程
登陆amoeba代理
mysql -uroot -h 10.248.24.207 -P 8066 -p
