mysql主从

1、本章面试题

什么是mysql主从，主从有什么好处

什么是读写分离，有什么好处，使用mycat如何实现

2、知识点

2.1、课程回顾

dubbo ORM->MVC->RPC->SOA

provider consumer registry monitor

负载均衡，容错，降低（倍权，半权）

2.2、本章重点

mysql 主从好处，原理，实现过程

mycat 简介，主要作用，读写分离配置

3、具体内容

3.1 mysql主从配置

3.1.1 好处

负载均衡 ：业务量增大时，I/O读写频率变高，单机可能无法满足需求，使用主从，让从服务器分担读压力，实现负载均衡

数据备份 : 传统的备份，都是本地备份，但是存在弊端，无法防止服务器本身出问题，配置主从，可以把本地数据备份到其他服务器

提高可用性 ： 一旦主服务器出现问题，可以直接使用从服务器进行更新或者查询

3.1.2 原理

1，主服务器（master）进行写操作时，会把所有的更新语句以二进制日志(binary log)的形式存储

2，从服务器（slave）中有一个i/o线程，会读取主服务器上的二进制日志文件到中继文件中(relay log)

3， 从服务器（slave）中另外一个sql线程，会读写中继文件中的语句，执行并持久化到从服务器的数据库文件中，从而实现了主从复制的功能

3.1.3 实现

配置规划： 创建两台虚拟机

mysql-master1 192.168.23.51 主服务器(master)

mysql-slave1 192.168.23.52 从服务器(slave)

参考linux课件中的虚拟机克隆，克隆出一台23的机子，安装mysql

1）克隆两台虚拟机，成功安装mysql

安装mysql:

查看已经安装mysql组件：（centos6.9 需要卸载原来的mysql组件）

yum list installed | grep mysql

卸载：

yum -y remove mysql-libs.x86_64

如果wget没有安装

yum -y install wget

下载yum源

wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm

安装yum源

rpm -ivh mysql-community-release-el6-5.noarch.rpm

查看yum源中mysql有效版本

yum repolist all | grep mysql

使用下载yum源安装

yum install mysql-community-server -y

查看mysql服务是否启动

service mysqld status

启动mysql服务

service mysqld start

mysql设置密码和远程连接配置

登录mysql

mysql -uroot -p 回车，让输入密码，默认密码为空，再次回车，就可以登录

mysql -uroot -p 回车， 让输入密码，默认密码为空，再次回车，就可以登录

mysql>show databases; 查看所有数据库

mysql>use mysql; 选择使用mysql库

mysql>show tables; 显示所有表

mysql>update user set password=password('tiger') where user='root'; 更新root的密码为tiger

mysql>flush privileges; 刷新权限

mysql> exit; 退出

mysql -uroot -ptiger 再次登录

使用navicat连接时，报错：

mysql>update user set host='%' where host='127.0.0.1'; 修改本地访问为% %代表统配符，可以远程访问

mysql>flush privileges; 刷新权限

已经安装好的m1克隆一台s1

使用上面讲过的克隆方法，再克隆一个s1

vim /etc/sysconfig/network-scripts/ifcfg-ens33

修改ipadd

wq保存退出

vim /etc/hostname 修改主机名称 mysqls1

reboot;

使用xshell连接，测试mysql是否能成功启动

service mysqld status 查看状态

mysql -uroot -ptiger 登录

使用navicat测试

2) 主从配置

master:

主服务器上修改配置文件，添加日志操作

vim /etc/my.cnf

修改第12行 binary.log 日志文件名称可以随便起

log_bin=binary_log

添加一行当前服务器唯一识别

server-id=23

重启服务，让修改生效

service mysqld restart

登录mysql

mysql -uroot -ptiger

查看master状态

show master status;

slave：

vim /etc/my.cnf

添加一行

server-id=24

重启服务，让修改生效

service mysqld restart

登录mysql

mysql -uroot -ptiger

关闭slave(第一次操作不需要，但是以后如果修改该服务器为其他服务的从节点时，需要先停止slave服务)

stop slave;

配置当前服务器为master服务器的从机

change master to master_host='192.168.23.23',master_user='root',master_password='tiger',master_log_file='binary_log.000001',master_log_pos=120;

启动slave

start slave;

查看slave的状态

show slave status \G;

没有错最好（两台机子都全新安装mysql）,克隆时，会报下面错误：

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

原因因为克隆机子，两边的sever-uuid完全一致，所以错误

cat /var/lib/mysql/auto.cnf

解决办法：

从机上先停掉服务

service mysqld stop;

删除server-uuid配置文件

rm -rf /var/lib/mysql/auto.cnf

再次启动服务：

service mysqld start;

再次查看，不同，就解决问题：

cat /var/lib/mysql/auto.cnf

登录mysql

mysql -uroot -ptiger

启动slave

start slave;

查看slave的状态

show slave status \G;

主从都可以执行：

show processlist \G;

3） 测试

使用navicat在m1主机上创建库，查看s1上是否有库

在m1创建表, 在s1查看是否存在：

create table tb_testa(

tid int primary key auto_increment,

name varchar(20) not null,

sex int

);

在m1执行添加语句，在s1查看效果：

insert into tb_testa values(null,'zhangsan',1);

insert into tb_testa values(null,'lisi',2);

查看文件位置是否变化：

master:

show master status;

slave:

show slave status \G;

3.2 mycat 双主双从，读写分离

3.2.1,集群规划：

3.2.2，克隆出m2和s2 做双主双从配置

关闭szqy07-mysql-m1 作为克隆的原始服务器 克隆出szqy07-mysql-m2和szqy07-mysql-s2

直接关闭m1

poweroff

使用前面学的知识，克隆

m2:

vim /etc/sysconfig/network-scripts/ifcfg-ens33

修改ip addr 为25

vim /etc/hostname

修改主机名称为 mysqlm2

rm -rf /var/lib/mysql/auto.cng

reboot 重启

s2:

vim /etc/sysconfig/network-scripts/ifcfg-ens33

修改ip addr 为26

vim /etc/hostname

修改主机名称为 mysqls2

rm -rf /var/lib/mysql/auto.cng

reboot 重启

使用xshell进行连接，配置主从

先配置m2和s2的主从

m2：

vim /etc/my.cnf

日志文件已经修改，修改server-id=25

重启mysql服务：

service mysqld restart

登录mysql：

mysql -uroot -ptiger

显示master状态：

show master status;

s2:

vim /etc/my.cnf

日志文件可以删除，修改server-id=26

重启mysql服务：

service mysqld restart

登录mysql：

mysql -uroot -ptiger

先停止原有的slave服务

stop slave

配置主服务器

change master to master_host='192.168.23.25',master_user='root',master_password='tiger',master_log_file='binary_log.000004',master_log_pos=120;

启动slave

start slave;

显示slave状态：

show slave status \G;

测试：

使用navicat连接，测试m2删除库，创建库，创建表，插入语句，s2是否有变化

配置m1和m2互为主从:

m1:

登录mysql

mysql -uroot -ptiger

显示master状态

show master status;

先停止原有的slave服务

stop slave

配置主服务器

change master to master_host='192.168.23.25',master_user='root',master_password='tiger',master_log_file='binary_log.000004',master_log_pos=1120;

启动slave

start slave;

显示slave状态：

show slave status \G;

m2:

登录mysql

mysql -uroot -ptiger

显示master状态

show master status;

先停止原有的slave服务

stop slave

配置主服务器

change master to master_host='192.168.23.23',master_user='root',master_password='tiger',master_log_file='binary_log.000002',master_log_pos=120;

启动slave

start slave;

显示slave状态：

show slave status \G;

m1和m2都需要加配置：

m1执行更新时，m2和s1都会变化，因为m2和s1都是它的从机，想让s2随着m2的数据变化而变化，就需要在m2加入下面配置

vim /etc/my.cnf 加入下面配置

log-slave-updates

m2执行更新时，m1和s2都会变化，因为m1和s2都是它的从机，想让s1随着m2的数据变化而变化，就需要在m1加入下面配置

vim /etc/my.cnf 加入下面配置

log-slave-updates

重启m1和m2的mysqld服务

service mysqld restart

m1,m2,s1,s2都执行

登录mysql

mysql -uroot -ptiger

显示slave状态：

show slave status \G;

测试：

m1和m2，执行任意更新操作时，其他3台都会发生变化

m1创建库 test_dbc 其他3台都有

m2创建表 ，其他3台都有

create table tb_testc(

tid int primary key auto_increment,

name varchar(20),

sex int

)

3.2.3，什么是mycat，有什么特点

官网： http://www.mycat.io/

Mycat数据库分库分表活跃的、性能好的开源数据库中间件，基于阿里开源的Cobar产品而研发，Cobar的稳定性、可靠性、优秀的架构和性能以及众多成熟的使用案例使得MYCAT一开始就拥有一个很好的起点，站在巨人的肩膀上，我们能看到更远。

一个彻底开源的，面向企业应用开发的大数据库集群

支持事务、ACID、可以替代MySQL的加强版数据库

一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群

一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server

结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品

一个新颖的数据库中间件产品

Mycat 原理：

Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的 SQL 语句，首先对 SQL 语句做了 一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发往后端的真实数据库， 并将返回的结果做适当的处理，最终再返回给用户。

3.2.5，什么是读写分离，有什么好处

读写分离就是数据的更新（insert udpate delete 等等）和查询（select等）让不同的数据库服务器执行。

数据库读写分离对于大型系统或者访问量(并发量)很高的互联网应用来说，是必不可少的一个重要功能。 从数据库的角度来说，对于大多数应用来说，从集中到分布，最基本的一个需求不是数据存储的瓶颈，而是在于计算的瓶颈，即SQL查询的瓶颈，我们知道，正常情况下，Insert SQL就是几十个毫秒的时间内写入完成，而系统中的大多数Select SQL则要几秒到几分钟才能有结果，很多复杂的SQL，其消耗服务器CPU的能力超强，不亚于死循环的威力。在没有读写分离的系统上，很可能高峰时段的一些复杂SQL查询就导致数据库服务器CPU爆表，系统陷入瘫痪，严重情况下可能导致数据库崩溃。

3.2.4 ，克隆出mycat，下载安装配置mycat，实现读写分离，自动故障切换和读的负载均衡

使用xftp上传到虚拟机里面

ls 查看

tar -xzf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr 解压

ls /usr

cd /usr/mycat 进入目录

pwd 查看当前目录

配置环境变量：

vim /etc/profile 在文件JAVA_HOME下加上

export MYCAT_HOME=/usr/mycat

export PATH=$JAVA_HOME/bin:$PATH:$MYCAT_HOME/bin

wq保存退出

source /etc/profile 让配置生效

echo $MYCAT_HOME 测试

server.xml 几乎保存了所有 mycat 需要的系统配置信息，配置登录mycat的用户名，密码，端口号和逻辑数据库名称

cd /usr/mycat 进入mycat目录

vim conf/server.xml

复制33行到31下（yy p） 去除9066 留下8066 配置mycat访问的端口号

注意观察81行后的配置，可以看到使用的用户,密码和逻辑数据库名称

Schema.xml 作为 MyCat 中重要的配置文件之一，管理着 MyCat 的逻辑库、表、分片规则、DataNode 以 及 DataSource。

<dataNode> dataNode 标签定义了 MyCat 中的数据节点，也就是我们通常说所的数据分片。一个 dataNode 标签就是 一个独立的数据分片。

<dataHost>作为 Schema.xml 中最后的一个标签，该标签在 mycat 逻辑库中也是作为最底层的标签存在，直接定义了具 体的数据库实例、读写分离配置和心跳语句。

balance 属性

负载均衡类型，目前的取值有 3 种：

1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。

2. balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双 主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载 均衡。

3. balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。

4. balance="3"，所有读请求随机的分发到 wiriterHost 对应的 readhost 执行，writerHost 不负担读压力， 注意 balance=3 只在 1.4 及其以后版本有，1.3 没有。

writeType 属性

负载均衡类型，目前的取值有 3 种：

1. writeType="0", 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个 writeHost，

重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .

2. writeType="1"，所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐。

switchType 属性

-1 表示不自动切换 1 默认值，自动切换

2 基于 MySQL 主从同步的状态决定是否切换 心跳语句为 show slave status

3 基于 MySQL galary cluster 的切换机制（适合集群）（1.4.1） 心跳语句为 show status like ‘wsrep%’

测试：

读写分离

没有启动mycat之前 创建表：

create table tb_test1(

id int,

username varchar(20)

);

vim conf/log4j2.xml 修改mycat日志文件 conf/log4j2.xml 为debug,为了方便查看日志

启动mycat 使用navicat测试连接：

mycat start 启动（ 因为配置了环境变量，在任何都可以执行）

mycat status 查看启动状态，看到running (pid)

在navicat连接的mycat上执行下面语句：

m1是写的服务器，所有的更新操作，都是在m1上执行

执行10条插入语句，使用下面语句查看在哪个服务器执行

insert into tb_test1 values(1,'zhangsan1');

insert into tb_test1 values(2,'zhangsan2');

insert into tb_test1 values(3,'zhangsan3');

insert into tb_test1 values(4,'zhangsan4');

insert into tb_test1 values(5,'zhangsan5');

insert into tb_test1 values(6,'zhangsan6');

insert into tb_test1 values(7,'zhangsan7');

insert into tb_test1 values(8,'zhangsan8');

insert into tb_test1 values(9,'zhangsan9');

insert into tb_test1 values(10,'zhangsan10');

grep --color=auto insert logs/wrapper.log

所有的insert 语句都是在192.168.23.23上执行

m2,s1,s2都读的服务器，所有的select查询操作，在这3台执行

在mycat上执行查询语

select * from tb_test1; -- 全部数据 可定是 m2执行的 192.168.23.25

select * from tb_test1; -- 如果缺少5,6,7 在s2执行

select * from tb_test1; -- 如果缺少8,9,10 在s1执行

所有的读操作都是在m2 s1和s2上执行

负载均衡

m2,s1,s2 轮流执行select语句 从上面就可以看到效果 单台300 项目一共支持 900并发

自动故障切换

m1挂掉后，心跳语句会判断m1是否正常，发现不正常后，会自动切换到第2个存活的writehost 节点作为写节点m2，如果是写节点m1挂了，该写节点下的读节点s1也不参与读

service mysqld stop 停止192.168.23.23的mysql

在mycat上执行插入语句

insert into tb_test1 values(11,'zhangsan11');

insert into tb_test1 values(12,'zhangsan12');

grep --color=auto insert logs/wrapper.log

发现写操作在192.168.23.25的mysql执行

select * from tb_test1; -- 每次都在s2上执行了

如果m1恢复正常 刚才m2执行的插入的数据m1上也会有，m2仍然是写节点，并且m1 s1以后会参与读操作

service mysqld start 启动服务192.168.23.23

参与读操作m1 s1和s2

insert into tb_test1 values(13,'zhangsan13');

insert into tb_test1 values(14,'zhangsan14');

grep --color=auto insert logs/wrapper.log

发现写操作还是192.168.23.25

select * from tb_test1; -- 全部数据 可定是 m1执行的 192.168.23.23

select * from tb_test1; -- 如果缺少5,6,7 在s2执行

select * from tb_test1; -- 如果缺少8,9,10 在s1执行

4、本章总结

4.1 总结本章知识点

4.2 面试题答案

4.3 预习下一章重点