Mysql读写分离（数据库的主从复制）

 

 

一、读写分离（数据库的主从复制）

原理：

mysql中有一种日志，叫做bin日志（二进制日志），会记录下所有修改过数据库的sql语句。主从复制的原理实际是多台服务器都开启bin日志，然后主服务器会把执行过的sql语句记录到bin日志中，之后把这个bin日志发给从服务器，在从服务器再把bin日志中记录的sql语句同样的执行一遍。这样从服务器上的数据就和主服务器相同了。

二、mysql的用户管理

（1）给用户授权（创建一个用户让该用户可以在其他电脑上登录到本 mysql服务器）

 

grant 权限名称 on *.* to ‘用户名’@’主机名称’identified by ‘密码’

all:是值所有的权限

on *.*  是可以操作所有的库和所有的表

‘用户名’@’主机名称’   ：如果主机名称是%则代表在所有的电脑上面都可以登录

也可以指定一个具体的ip地址。

identified  by ‘密码’  设置的授权用户的密码。

 

select user,host,password frommysql.user  查看当前mysql里面的用户情况

（2）删除用户：

drop user ‘用户的名称’@’%’;删除用户

（3）收回权限，

revoke 权限名称on *.* from ‘username’@’localhost’

收回全部权限的revoke语句，

revoke all privileges,grant option from‘username’@’localhost’

 

收回权限：

revoke 权限名称  on *.*  from ‘用户名称’@’%’

（4）如果root 用户的密码丢失，如何登录修改。

解决方案：

第一步：关闭mysql服务器

第二步：通过cmd方式，进入到mysql安装目录的bin目录，执行

        mysqld.exe –skip-grant-tables

第三步：启动另外一个cmd开始登录

      

第四步：要修改root用户的密码

第五步：要重新加载权限：重新登录

flushprivileges

三、binlog日志的讲解

 

1、如何开启bin-log日志

打开配置文件，添加如下内容：

也可以自己设置存储路径比如：d:/mysql-bin

日志文件的名字

2、如何查看bin-log日志，

bin-log日志默认存储在data目录里面

查看bin-log日志里面的内容：

使用该命令查看bin-log日志里面的具体的内容。

语法：mysqlbinlog.exe –no-defaults 日志文件（包含路径）

刚才执行的sql语句已经记录到bin-log日志文件中

3、和bin-log 日志相关的一些函数：

flushlogs :产生一个新的bin-log日志文件。

showmaster status :查看最新的一个bin-log日志文件。

resetmaster;

清空所有的bin-log日志

 

4、使用b in-log日志来恢复数据

简单案例：

（1）先创建一张表。

（2）flush logs

（3）向表里添加数据

（4）flush logs

（5）不小心把表里面的数据给弄丢了

（6）恢复

扩展案例：

一个公司在9:00的时候，备份了一次数据，到9:30的时候，由于员工误操作把9:00之后的数据给弄丢了，如何把数据给恢复过来。

打开 bin-log日志文件，分析记录9：00以后 的执行sql 语句，记录下pos点的位置，就可以执行恢复。

主要使用的参数是：

--start-position=”开始点”

--stop–positon=”接收的点”;

四、读写分离的配置

1、主服务器的配置，

（1）要把主服务器包括从服务器开启bin-log日志

主服务器：

从服务器：

（2）每台服务器设置一个唯一的server-id的值。并重启服务器。

（3）给从服务器设置授权用户

grantall  on *.* to user1@192.168.10.10identified by ‘456’;

或

grantreplication slave on *.* to user@192.168.10.10 identified by ‘456’

或

grantreplication slave on *.* to user@’%’identified by ‘456’

（4）记录下主服务器最新的bin-log 日志，

注意：此时要停止对主服务器的操作，一直到配置完从服务器。原因是一旦对主服务器有了操作，bin-log日志文件会有变化。

2、配置从服务器：

（1）要停止从服务器：

（2）开始配置

changemaster to master_host="主服务器的ip地址",master_user="授权用户的用户名称",master_password="授权用户的密码",

master_log_file="主服务器上最新的bin-log日志文件",master_log_pos="日志文件中最后的position值";

 

（3）要开启从服务器

（4）查看从服务器的状态：

表名配置成功

 

Slave_IO_Running:Yes

此进程负责从服务器从主服务器上读取binlog 日志，并写入从服务器上的中继日志。

Slave_SQL_Running:Yes

此进程负责读取并且执行中继日志中的binlog日志，

注：以上两个都为yes则表明成功，只要其中一个进程的状态是no，则表示复制进程停止，错误原因可以从”last_error”字段的值中看到。

注意的问题：

如果配置失败：

失败原因：

（1）肯定是 sql语句哪里写错。

（2）主从服务器的mysql版本号必须相同。

查看服务器的版本号：

 

五、常见的web服务器：

apache:功能完善，历史悠久

缺点：处理每一个php比较费资源，导致如果高并发时会太耗费服务器资源无法处理更多请求。

nginx：省资源，省cpu,所以在高并发时能够处理更多的请求，高端能达到3万到5万的并发量。

六、nginx服务器特点：

 

lamp=èlinuxapache mysql php

lnmp==>linuxnginx mysql php

1、它可以高并发连接，官方测试能够支撑5万并发连接，在实际生产环境中可以支撑2到4万并发连接。

2、内存消耗少

Nginx+php(FastCGI)服务器再3万并发连接下，开启的10个Nginx进程消耗150MB内存（15MB*10=150MB）开启的64个php-cgi进程消耗1280MB内存（20MB*64=1280MB）

3、成本低廉

购买F5 BIG-IP ,NetScaler等硬件负载均衡交换机需要10多万甚至几十万人民币。而Nginx为开源软件，可以免费试用，并且可用于商业用途。

4、其他理由：

（1）配置文件非常简单：通俗易懂，即使非专业管理员也能看懂。

（2）支持 rewrite重写规则：能根据域名、URL的不同，将HTTP请求分到不同的后端服务器群组。

（3）内置的健康检查功能：如果nginxproxy后端的某台服务器宕机了，不会影响前端访问。

（4）节省带宽，支持gzip压缩。

（5）稳定性高：用于反向代理，宕机的概率微乎其微。

（6）支持热部署。在不间断服务的情况下，对软件版本升级

5、支持的操作系统；

FreeBSD3.x,4.x,5.x,6.x i386; FreeBSD 5.x,6.x amd64;

Linux2.2,2.4,2.6 i386; Linux 2.6 amd64;

Solaris8 i386; Solaris 9 i386 and sun4u; Solaris 10 i386;

MacOSX （10.4） PPC;

WindowsXP，Windows Server 2003和Windows 7等。

七、安装测试(window安装)

1、解压会，拷贝到指定的目录

网站的根目录

配置文件所在的目录

2、以cmd的方式进入到nginx的目录，执行start nginx 直接启动即可。

3、我们通过浏览器访问：

 

八、nginx的虚拟主机的配置

（1）打开nginx的配置文件，里面看到

server{

       //具体的内容

}

监听的端口

每一个server段是一个虚拟主机

 

访问的默认文件。

虚拟主机的目录

虚拟主机的域名

 

（2）配置完成后要重新加载nginx

执行命令：nginx –s reload

(3)修改hosts 文件，访问：

cdnginx

startnginx//启动nginx服务。

nginx-s stop // 停止nginx
nginx -s reload // 重新加载配置文件
nginx -s quit // 退出nginx

 

九、nginx支持php

（1）把php程序拷贝和nginx同一个目录下面，

（2）配置加载扩展的目录

（3）打开一个cgi的一个接口，nginx遇到后缀是php文件的文件，交给9000端口来处理。

（4）打开nginx的配置文件，进行配置

该段内容是支持php程序的代码

（5）写一个脚本便于管理：

一定要按照当前安装的目录进行设置，千万不还要照抄

RunHiddenConsole一个让你的程序隐藏运行的小工具

（6）

双击即可启动

 

最终效果：

十、完成负载均衡的实验：

前提：至少三台主机

有一台负责转发请求的（192.168.3.250）

另外两台：192.168.3.252  192.168.3.253

 

(1)配置主服务器：

（2）从服务器的配置：

十一、锁机制

 

 

 

比如表里面有一个字段id

取出id的值，

减1操作，

在把数据给写会到表里面

 

 

假如有两个人同时操作：比如原来id值为100

 

第一人 ：100

-1==》99===》99

 

第二人：100

-1==》99===》99

 

最终应该变成98才对。

 

可以通过锁机制来完成。

mysql里面的锁操作

locktable 表名  read|write

read:使用read锁，所有的用户操作只能读出，不能写的操作

write锁：自己能够任何操作，其他客户端，连读也不能读出。

只能操作自己锁定的表，其他表不能操作，若要操作多个表则，需要锁定多张表，

locktable 表名1 read|write,表名2 read|write

缺点：

产生阻塞。在实际项目中，一般不使用表锁，原因是会阻塞，会拖慢整个网站的速度。

php文件里面锁操作