一、概述
复制是将主数据库的DDL和DML操作通过二进制日志传到复制服务器（从服务器）上，然后在从库对这些日志重复执行，使得从库和主库的数据保持同步。
通过3个线程来完成主从库间的数据复制，首先，从库启动复制（start slave）时，创建I/O线程连接主库，主库随后创建Binlog Dump线程读取数据库事件并发送给I/O线程，I/O线程获取到事件数据后更新到从服务器的中继日志Relay Log中，之后从库上的SQL线程读取中继日志中更新的数据库事件并应用，执行完成后SQL线程会自动删除当前中继日志文件，避免占用过多磁盘空间。
Show processlist; 查看线程。
从库中会创建两个日志文件master.info和relay-log.info用来保存复制的进度，防止从库crash重启后，告知I/O线程和SQL线程从哪里开始复制。分别记录从库的I/O线程当前读取主库二进制日志的进度和SQL线程应用中继日志的进度。
Show slave status; 查看当前从库复制的状态。
二、原理图
1:主服务器凡运行语句,都产生一个二进制日志 binlog，默认存放在参数datadir（数据目录）指定的目录
2:从服务器不断读取主服务器的binlog
3:从主服务读取到的binlog,转换为自身可执行的中继日志relaylog,
4:执行relaylog
三、配置
主从复制至少需要两个MySQL服务，可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。
实现步骤:
1:首先确保主服务器打开二进制日志功能.
这样,主服务器一旦有数据变化,立即产生二进制日志.
2:从服务器也需要开启二进制日志和relay日志功能.
这样可以从主服务器读取binlog,并产生relaylog
3:在主服务器建立一个从服务器的账号,并授予数得上权限.
4: 指定从服务对应的主服务器,开启从服务器.
主服务器[mysqld]：
#给服务器起一个唯一的id
server-id=1
#开启二进制日志
log-bin=mysql-bin
#指定日志格式
binlog-format=mixd/row/statement
重启mysql
show master status；获得主库当前的二进制日志名和偏移量，此操作是为了在从数据库启动后，总这个点开始进行数据复制。
已经能够充当master服务器
5: 配置从服务器打开binlog和relaylog
Read-only 设置从库只能接受超级用户的更新操作
重启从服务器
6: 在主服务器上创建相应的复制账号
7: 在从服务器通过语句指定要复制的主服务器(注意,可以一主多从,不可一从多主).
change master to
master_host=‘192.168.0.112‘,
master_user=‘repl‘,
master_password=‘repl‘;
8:启动从服务器功能
mysql>start slave;
8: 测试.
四、常用语句
show master status ; 查看master的状态, 尤其是当前的日志及位置
show slave stattus; 查看slave的状态.
reset slave ; 重置slave状态.
start slave ; 启动slave 状态(开始监听msater的变化)
stop slave; 暂停slave状态;
主服务器的二进制日志格式用哪种好?
有 statement,row, mixed3种,其中mixed是指前2种的混合.
Set global binlog_format=”xxx”;
以insert into xxtable values (x,y,z)为例,
影响: 1行,且为新增1行, 对于其他行没有影响.
这个情况,用row格式,直接复制磁盘上1行的新增变化.
以update xxtable set age=21 where name=’sss’;
这个情况,一般也只是影响1行. 用row也比较合适.
以过年发红包,全公司的人,都涨薪100元.
update xxtable set salary=salary+100;
这个语句带来的影响,是针对每一行的, 因此磁盘上很多row都发生了变化.
此处,适合就statment格式的日志.
2种日志,各有各的高效的地方,mysql提供了mixed类型.可以根据语句的不同,而自动选择适合的日志格式.
Statement：基于SQL语句级别的Binlog,每条修改数据的SQL都会保存到日志。
Row：基于行级别，记录每一条数据的变化，并不是记录原始SQL，更能保证从库数据的一致性，但是日志会更大；
五、主主复制
在上面的配置中,2台服务器地位有差别,一主一从。
从服务器一是起到备份作用,一是起到分担查询压力的作用.
接下来的配置,2台服务器之间,没有明显的地位差距, 两者可以同步对方的内容.
一般的格局如下图:
两台服务器相互复制
大致思路:
1: 2台服务器都设置上2进制日志和relay日志
2: 都设置上replcation账号
3: 都设置对方为自己的master
主主复制下一定要注意避免的问题---------同步冲突
例:
create table stu (
id int primary key auto_increment.
)......
2台mysql地位相等, 假如2个请求同时到达2台服务器,
请求的A节点, stu 的id为1
请求的B 节点, stu的id为1 ,
同步--->冲突
如何解决?
让1台服务器 1,3,5,7来增长
另1台服务器 2,4,6,8来增长
一台服务器：
set global auto_increment_increment = 2;
set global auto_increment_offset = 1;
set session auto_increment_increment = 2;
set session auto_increment_offset = 1;
另一台服务器：
set global auto_increment_increment = 2;
set global auto_increment_offset = 2;
set session auto_increment_increment=2;
set session auto_increment_offset = 2;
注：auto-increment-increment 和 auto-increment-offset 要写到配置文件 中，防止下次重启后失效．
操作后得到如下类似效果
如果后期需要加服务器,这个办法就有限制了.
我们可以在业务逻辑上来解决,
比如在racle 有sequnce,序列.
序列每次访问,生成递增/递减的数据.
以redis为例, 我们可以专门构建一个 global:userid
每次PHP插入Mysql前,先 incr->global:userid, 得到一个不重复的userid.
六、被动模式下的主主复制
被动模式下的主主复制
是指 2台服务器地位一样, 但其中一台为只读,并且业务中也只写某1台服务器.
好处: 如果供写入的服务器出了故障,能迅速的切换到从服务器,
或者出于检修等目的,把写入功能切换到另一台服务器也比较方便.
拥有从服务器的主主复制,在实际应用中也很多.
如何在客户端应用的时候,路由语句.
一般用两种方法
1: 直接在PHP的mysql类做判断,最简单,不用额外加软件
比如discuz论坛.
2:用集群中间件
比如官方的mysql_proxy
,还有国产的中间件 amoeba
七、读写分离、负载均衡
实战: mysql 读写分离 ,mysql_proxy实现
下载安装mysql_proxy:
http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz
解压后, 该目录包含已经编译好的二进制文件 .
1: 利用mysql_proxy实现负载均衡
执行mysql_proxy
./mysql-proxy-path/bin/mysql-proxy \
--proxy-backend-addresses=192.168.1.199:3306 \
--proxy-backend-addresses=192.168.1.200:3306
2: 连接mysql_proxy,用mysql客户就可以,因为proxy是mysql的前端代理
注意proxy的端口是4040
mysql -h “proxy的IP” -P 4040 -u username -p password
在连接上之后,做sql查询,却总是往某1台mysql server来发送------负载均衡没体现出来?
答: 不是没体现出来.
均衡不是体现在sql语句,一会请求mysqlA服,一会请求mysqlB服.
均衡是体现”连接”的均衡上.,
mysql_proxy会把连接mysql服务器的tcp/IP连接缓存进连接池,以提高性能.
在缓存池里, 缓存的连接大致是平均分配在每台mysql服务器上.
但具体的每一个连接,始终连某台服务器.
./bin/mysql-proxy \
--proxy-backend-addresses=192.168.1.199:3306 \
--proxy-read-only-backend-addresses=192.168.1.200:3306 \
--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
简写:
./bin/mysql-proxy
-b=192.168.0.199:3306
-r=192.168.0.200:3306
-s=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
八、切换主从
模拟master（M）slave1（S1）slave2（S2）
1.在每个从库上执行 stop slave io_thread; 检查show processlist的输出，直到状态是has read all relay log，表示更新都执行完毕。
2.在从服务器S1，执行stop slave，然后执行reset master重置成主数据库；
3.在S2上，执行stop slave停止从服务，执行 change master to master_host=”S1地址”重新设置主数据库，再执行start salve重启复制；
4.通知所有客户端将应用执行S1
5.删除新的主数据库服务器上的master.info和relay_log.info文件，否则下次重启还会按照从库启动
6.若M修复，按照S2的方法配置成S1的从库
7.上述默认S1打开log-bin，其次没有打开log-slave-updates参数
log-slave-updates 配置从服务器上的更新操作是否写入二进制日志，默认是不打开的
原文：https://www.cnblogs.com/cshaptx4869/p/10482105.html