Mysql数据库主从
- 数据库
- 16天前
- 31热度
- 0评论
1、数据库主从
1.1、什么是数据库主从
主库把所有的操作都记入二进制日志，从库通过网络把主库日志拷贝入自己的日志，从库读取日志进行数据更改。
示意图：
原理图：
注意事项：
1. 主数据库和从数据库版本应一致，如果不一致，从数据库版本应高于主数据库版本。
2. 主从同步实质是同步数据库操作，不是保证两者数据一致。所以启动主从前，应先保证两者数据一致。
3. 从库的数据相对主库有滞后性。
4. 主从配置会影响主库的性能，从库越多对主库的影响越大。
5. 当一次配置成功后，主从随着数据库启动而启动。
6. 从库必须在主库启动后启动。
7. 只要主库正常，从库停机后启动，主从自动可以把主库数据同步过来。
1.2、主从的同步方式
1. 同步复制
所谓的同步复制，意思是master的变化，必须等待slave-1,slave-2,...,slave-n完成后才能返回。
这样，显然不可取，也不是MYSQL复制的默认设置。比如，在WEB前端页面上，用户增加了条记录，需要等待很长时间。
2. 异步复制
如同AJAX请求一样。master只需要完成自己的数据库操作即可。至于slaves是否收到二进制日志，是否完成操作，不用关心。MYSQL的默认设置。
3. 半同步复制
master只保证slaves中的一个操作成功，就返回，其他slave不管。
1.3、主从数据库有什么用
1. 读写分离
把统计等费时、占资源高的操作移到从库
2. 异地备份
类似于高可用的功能，一旦master挂了，可以让slave顶上去，同时slave提升为master。
1.4、主从架构选择
数据库主从架构有多种选择，每种架构都有其适应性，我认为最有用的有如下两种：
一主多从
当写入操作较少，读操作较多可以采取如下架构
可以有效的均衡压力，但是，当slave增加到一定数量时，slave对master的负载以及网络带宽都会成为一个严重的问题。
这种结构虽然简单，但是，它却非常灵活，足够满足大多数应用需求。一些建议：
1. 不同的slave扮演不同的作用(例如使用不同的索引，或者不同的存储引擎)；
2. 用一个slave作为备用master，只进行复制；
3. 用一个远程的slave，用于灾难恢复；
级联复制架构
在有些应用场景中，可能读写压力差别比较大，读压力特别的大，一个Master可能需要上10台甚至更多的Slave才能够支撑注读的压力。这时候，Master就会比较吃力了，因为仅仅连上来的SlaveIO线程就比较多了，这样写的压力稍微大一点的时候，Master端因为复制就会消耗较多的资源，很容易造成复制的延时。
2、全新配置数据库主从
准备工作：已经安装好了三台电脑，centos6.5，mysql5.5.45，分别是主库master，从库slave1，slave2
2.1、配置主库master
登录主库mysql并创建同步用户：mysync
mysql> CREATE USER 'mysync'@'%' IDENTIFIED BY '123456';
授权
mysql> grant replication slave on *.* to 'mysync'@'%' identified by '123456';
配置文件：my.cnf
# vi /etc/my.cnf
#在mysqld中增加
server-id=1
log_bin=master-bin
binlog_format=MIXED
expire_logs_days=7
gtid_mode = on
enforce_gtid_consistency = 1
#binlog-do-db=test
#binlog-ignore-db=mysql
#说明：
# server-id：唯一ID，主库建议为1，必须
# log_bin：是否开启二进制日志，必须
# binlog_format：日志记录格式，推荐
# expire_logs_days 日志过期删除的时间
# binlog-do-db：需要同步的数据库
# binlog-ignore-db：不需要同步的数据库
重启数据库
# service restart mysqld
查看状态
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 1285 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
2.2、配置从库
配置文件
# vi /etc/my.cnf
#在mysqld中增加
server-id=2
log_bin=slave-bin
expire_logs_days=7
relay_log=mysql-relay-bin
read_only=1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1
binlog_order_commits=on
slave_preserve_commit_order=1
#replicate-do-db=test
#replicate-ignore-db=mysql
#说明：
# server-id：唯一ID，必须
# log_bin：是否开启二进制日志，单纯作为从库不需要配置
# expire_logs_days 日志过期删除的时间
# relay_log 中继日志
# log_slave_updates 将复制事件写进自己的二进制日志，单纯作为从库不需要配置
# read_only 防止改变数据
# replicate-do-db：需要同步的数据库（多个写多行）
# replicate-ignore-db：不需要同步的数据库
重启
# service restart mysqld
启动主从
mysql> stop slave;
mysql> change master to master_host='192.168.1.1',
master_user='mysync',
master_password='123456',
master_auto_position=1,
master_port=3306;
mysql> start slave;
--------------------
mysql> change master to master_host='192.168.1.1',
master_user='mysync',
master_password='123456',
master_log_file='master-bin.000001',
master_log_pos=0,
master_port=3306;
检查状态
mysql> show slave status\G;
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.222 //主服务器地址
Master_User: mysync //授权帐户名，尽量避免使用root
Master_Port: 3306 //数据库端口，部分版本没有此行
Connect_Retry: 60
Master_Log_File: master-bin.000004
Read_Master_Log_Pos: 600 //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos
Relay_Log_File: ddte-relay-bin.000003
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
主要检查：Slave_IO_Running、Slave_SQL_Running，两个必须为YES
2.3、监控与报警
nagios
3、中途配置数据库主从
假如master已经运行很久了，想对新安装的slave进行数据同步，甚至它没有master的数据。
3.1、新增从库
1、master锁表
mysql> flush tables with read lock;
2、master备份
mysqldump -uroot -p -hlocalhost > mysql.bak.sql
3、备份数据库导入slave
mysql> stop slave;
mysql> source /tmp/mysql.bak.sql;
4、配置主从，并启动
参考章节二
5、master释放锁
mysql> unlock tables;
4、常见错误及排查
4.1、同步不成功
查主库
mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 | 3260 | | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)
查从库
mysql> show slave status\G;
#主要观察：
Slave_IO_Running: Yes
Slave_SQL_Running: No
4.2、跨库更新
举例：某两个数据库已经实现了主从同步，现在主库中有两个数据库test01和test02，然后test01中有一张表table01，如果在my.cnf的参数里面设置了replicate_do_db=test01,test02，即只同步这两个库的数据，然后执行以下的更新语句
use test01;
update test01.table1 set……
执行的结果是主从库都能看到更新的数据
但如果是另外一种执行的情况进行更新语句
use test02;
update test01.table1 set……
执行的结果是主库能够看到数据，但是从库却无法看到更新的数据
原因：设置replicate_do_db后，MySQL执行sql前检查的是当前默认数据库，所以跨库更新语句在Slave上会被忽略。
而对于跨库更新SQL语句的问题，replicate_wild_do_table可以解决，即在my.cnf的参数里面设置
（正确写法）
replicate_wild_do_table=test01.%
replicate_wild_do_table=test02.%
（错误写法）
replicate_wild_do_table=test01.%,test02.%
注意需要同步的库必须分行写而不能在同一行用逗号隔开，否则在同步的时候该参数不生效。
**建议：同步所有数据，如果确实不需要同步某几个库，一定要确认没有跨库问题。**
4.3、出现错误
查看从库状态，确定日志位置
mysql> show slave status\G;
4.4、处理sql错误
mysql主从复制，经常会遇到错误而导致slave端复制中断，这个时候一般就需要人工干预，跳过错误才能继续，跳过错误有两种方式：
1、跳过指定数量的事务：
mysql>slave stop;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 #跳过一个事务
mysql>slave start
2、修改mysql的配置文件，通过slave_skip_errors参数来跳所有错误或指定类型的错误
vi /etc/my.cnf
[mysqld]
#slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
#slave-skip-errors=all #跳过所有错误
5、测试
5.1、主从配置测试
| 名称 | 步骤 | 结果 | 结论 |
| 操作1 | 主库开启mysql，master 是否自动开启成功 | (1)master 自动开启 (2)每当mysql重启，master-bin.000001在000001上自增+1 |
master 随主库mysql启动而启动 |
| 操作2 | 从库开启mysql，slave是否自动开启成功 | (1)slave自动开启 (2)master-bin.000001跟随主库mysql重启而变化 |
slave 随从库mysql启动而启动 |
| 操作3 | 1、主库：mysql stop 2、从库：mysql start，slave stop 3、主库：mysql start，slave start |
配置成功 | 必须主库先启动，再启动从库 |
| 操作4 | 1、主库：mysql stop 2、从库：mysql start，salve start 3、主库：mysql start |
(1)配置不成功,master-bin跟主库不一致 (2)slave start,配置成功 |
必须主库先启动，再启动从库 |
5.2、主从同步测试
| 名称 | 步骤 | 结果 |
| 操作1 | 1、主库mysql start、master start 2、从库mysql start,slave start |
主库操作增删改，从库能及时同步 |
| 操作2 | 1、主库mysql start、master start 2、从库mysql stop,主库进行增删改后 3.从库mysql start 、slave start |
从库能同步主库的操作 |
| 操作3 | 1、主库mysql start、master start 2、从库mysql start,slave stop,主库进行增删改后 3、slave start |
从库能同步主库的操作 |
结论：在主库开启的状态，只要从库重新正常状态，就能及时同步主库的操作。
