mysql 主从 有大量数据

mysql 主从 有大量数据

mysqldump备份时导致所有数据表锁定，无法提供服务？
https://blog.csdn.net/zzddada/article/details/138259366

Mysql主从同步和mysqldump备份
https://blog.csdn.net/lqcbj/article/details/135762824

https://www.cnblogs.com/Dominic-Ji/articles/15429590.html
1.1 什么是主从复制
将主服务器的binlog日志复制到从服务器上执行一遍，达到主从数据的一致状态，称之为主从复制。

一句话表示就是，主数据库做什么，从数据库就跟着做什么。
1.2 为何要做主从
（1）为实现服务器负载均衡/读写分离做铺垫，提升访问速度
#1、什么是读写分离
有了主从保持数据一致作为大前提，我们便可以可以分离读写操作，其中Master负责写操作的负载，也就是说一切写的操作都在Master上进行，而读的操作则分摊到Slave上进行。那么读写分离的作用是什么呢？

#2、读写分离的作用
先说答案：读写分离可以大大提高读取的效率。

在一般的互联网应用中，经过一些数据调查得出结论，读/写的比例大概在 10：1左右 ，也就是说写操作非常少，大量的数据操作是集中在读的操作（如某些应用，像基金净值预测的网站。其数据的更新都是有管理员更新的，即更新的用户比较少。而查询的用户数 量会非常的多。）
此时我们可以制作一主多从，因为写操作很少，所以由一个主库负责即可，而大量的读操作则分配给多个从库，这样占据比例最大的读操作的压力就被负载均衡了，因此读效率会得到了极大的提升，另外，还有一个原因就是：
熟悉DB的研发人员都知道，写操作涉及到锁的问题，不管是行锁还是表锁还是块锁，都是比较降低系统执行效率的事情。我们这样的分离是把写操作集中在一个节点上，而读操作其其他的N个节点上进行，这从另一个方面有效的提高了读的效率，保证了系统的性能及高可用性。

#3、具体做法
方案一：
就是主库写，从库读

方案二：
主库负责写，还有部分读，从库只负责读，而且是读操作的主力

即当主服务器比较忙时，部分查询请求会自动发送到从服务器中，以降低主服务器的工作负荷。
（2）通过复制实现数据的异地备份，保障数据安全
可以定期的将数据从主服务器上复制到从服务器上，这实现了数据的异地备份。
在传统的备份体制下，是将数据备份在本地。此时备份作业与数据库服务器运行在同一台设备上，当备份作业运行时就会影响到服务器的正常运行。有时候会明显的降低服务器的性能。同时，将备份数据存放在本地，也 不是很安全。如硬盘因为电压等原因被损坏或者服务器被失窃，此时由于备份文件仍然存放在硬盘上，数据库管理员无法使用备份文件来恢复数据。这显然会给企业 带来比较大的损失。

（3）提高数据库系统的可用性
数据库复制功能实现了主服务器与从服务器之间数据的同步，增加了数据库系统的可用性。主库宕机后，从库尚可用，即当主服务器出现问题时，数据库管理员可以马上让从服务器作为主服务器，用来数据的更新与查询服务。然后回过头来再仔细的检查主服务器的问题。此时一般数据库管理员会采用两种手段。

​ 一：从库临时取代主库，只用来读
主服务器故障之后，虽然从服务器取代了主服务器的位置，但是对于主服务器可以采取的操作仍然做了一些限制,例如仍然只能够进行数据的查询，而不能够进行数据的更新、删除等操作。这主要是基于从数据的安全性考虑。如现在一些银行系统的升级，在升级的过程中，只能够查询余额而不能够取钱。这是同样的道理。

​ 二：从库永久取代主库，负责读和写
从服务器真正变成了主服务器。当从服务器切换为主服务器之后，其地位完全与原先的主服务器相同。此时可以实现对数据的查询、更新、删除等操 作。为此就需要做好数据的安全性工作。即数据的安全策略，要与原先的主服务器完全相同。否则的话，就可能会留下一定的安全隐患

1.3 主从复制的原理
整体上来说，复制有3个步骤：

​ (1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）。
​ (2) slave的io线程将master的binary log events拷贝到它的中继日志(relay log)；

​ (3) slave的sql线程解析中继日志中的事件并在从库执行，保持与主库一致。
复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

详解如下

从库准备

（1）从库change master to 时，ip port user password binlog position写入到master.info进行记录
（2）从库 start slave 时，会启动IO线程和SQL线程

同步的过程

1.从库的IO线程，读取master.info信息，获取主库信息并连接主库
2.主库接收从库的链接请求后，会生成一个准备binlog DUMP的线程，来响应从库
3.主库一旦有新的日志生成，会发送“信号”给主库的binlog dump线程，然后binlog dump线程会读取binlog日志的更新
4.TP(传送)给从从库的IO线程
5.IO线程将收到的日志存储到了TCP/IP 缓存
6.写入TCP/IP缓存后，立即返回ACK给主库 ，此时主库工作完成
7.IO线程更新master.info文件binlog 文件名和postion
8.IO线程将缓存中的数据，存储到relay-log日志文件,此时io线程工作完成
9.从库SQL线程读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点
10.从库SQL线程基于从步骤9中获取到的起点，去中继日志relay-log.000001获取后续操作，在从库回放relay-log
11.SQL线程回放完成之后，会更新relay-log.info文件，把当前操作的位置记入，作为下一次操作的起点。
12. relay-log会有自动清理的功能。

1.4 主从复制的日志格式
这里的日志格式就是指二进制日志的三种格式

binlog_format=statement

binlog_format=row

binlog_format=mixed

其中基于row的复制方式更能保证主从库数据的一致性，但日志量较大，在设置时考虑磁盘的空间问题

1.5 主从复制的方式
MySQL的主从复制有两种复制方式，分别是异步复制和半同步复制
二 异步复制
2.1 异步复制原理介绍
我们之前介绍的就是异步复制，即客户端线程提交一个写操作，写入主库的binlog日志后就立即返回，并不需要等待从库完成同步操作，而主库的dump线程会监测binlog日志的变量然后主动将更新推送给从库。

MySQL 主从复制默认是异步的模式。
2.2 异步复制部署
前提：主从数据库版本一致

2.2.1 主库停服时制作主从
一、主库：192.168.15.101

1）在主库上创建一个用于复制的账号，并赋予replication slave权限，这里必须 . 不能指定库授权，因为 replication slave 是全局的

mysql> grant replication slave on *.* to 'jason'@'%' identified by '123';
mysql> flush privileges;

修改主库配置文件，开启主库的Binlog，并设置server-id

[mysqld]
# 节点ID，确保唯一
server-id = 1        #开启mysql的binlog日志功能
log-bin = mysql-bin
#控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
sync_binlog = 1     
#binlog日志格式
binlog_format = row   
#binlog过期清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m   
#最大binlog缓存大小
max_binlog_cache_size= 512m         #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制黏贴下述配置项，写多行
binlog-ignore-db=mysql # 表中自增字段每次的偏移量
auto-increment-offset = 1 
# 表中自增字段每次的自增量
auto-increment-increment = 1  
#跳过从库错误
slave-skip-errors = all 

重启主库
systemctl restart mysql
4）备份主库，备份时锁表保证备份一致

mysqldump -uroot -pEgon@123 -A -E -R --triggers --triggers --master-data=2 --single-transaction > /tmp/all.sql

5）将备份文件发送给从库
scp /tmp/all.sql root@192.168.15.100:/tmp

二、从库：192.168.15.100
1）测试复制账号
mysql -ujason -p123 -h 192.168.15.101
2）导入初始数据
mysql -uroot -pEgon@123 < /tmp/all.sql
3）修改从库配置文件，增加server-id，注意这个值是唯一的，要区别于主库和其他从库

[mysqld]
server-id = 2relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%# 从库也可以开启binlog，但通常关闭
# log-bin=mysql-bin

4）重启从库
systemctl restart mysqld
5）配置复制

先去主库查看一下binlog日志名与位置

然后在从库进行配置

[root@slave1 ~]# mysql -uroot -pEgon@123 # 登录然后执行
change master to
master_host='192.168.15.101',  -- 库服务器的IP
master_port=3306,              -- 主库端口
master_user='jason', -- 主库用于复制的用户
master_password='123', -- 密码
master_log_file='mysql-bin.000001', -- 主库日志名
master_log_pos=120; -- 主库日志偏移量,即从何处开始复制

6）从库启动slave线程，并检查

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

2.2.2 主库运行时制作主从

1.准备纯净的从库环境
主库：192.168.15.101
从库：192.168.15.1002.修改主库配置文件（略）
3.重启主库（略）
4.主库创建账号（略）
5.模拟主库运行，有数据写入、
for i in `seq 1 1000000`
do mysql -uroot -p123 -e "insert db1.t1 values($i)";sleep 1;
done6.主库全备数据
#不打点备份(no)
mysqldump -uroot -p123 -A -R --triggers > /tmp/all.sql
#打点备份(yes)
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction > /tmp/all.sql7.将热备数据传达从库
scp /tmp/all.sql 192.168.15.100:/tmp8.修改从库配置文件（略）9.重启从库（略）10.在从库导入全备数据(导入打点备份，原因如下)
#导入不打点的数据的话，接下来主从，位置点只能去主库查看 show master status; 而数据在不停地往主库里插入，该命令查看的只是那一刻binlog的记录的位置，基于该位置同步数据会丢失所获得位置点往前一直到全备的数据，以及操作从库过程中新写入主库的数据。#如果导入的是打点的数据，那么全量备份的起始点可以从备份文件中获得，从库可以以此为同步的基准点，去主库获取全量备份之后的数据11.查看sql文件中的位置点（如果是打点备份的话）
该位置即主库刚刚做完全量备份时，主库binlog日志所处的位置
[root@jason ~]# head -50 /tmp/all.sql|grep 'MASTER_LOG_POS'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=129005;12.从库配置同步
[root@slave1 ~]# mysql -uroot -pEgon@123 # 登录然后执行
change master to
master_host='192.168.15.101',
master_port=3306, 
master_user='jason',
master_password='123',
master_log_file='mysql-bin.000001', 
master_log_pos=129005; 13.开启SQL线程和IO线程start slave;
14.查看主从状态show slave status;

111

1.进行每日的全备
mysqldump -uroot -p123 -A -R --triggers --master-data=2 –single-transaction > /backup/full.sql2.调整延时从库延时时间为60分钟stop slave;CHANGE MASTER TO MASTER_DELAY = 3600;start slave;

2、备份数据库，并且记录pos点

如果主库有数据，半路中途要加从库，那么使用change master to的时候，这个pos点无法确定，因为主库的pos点一直在变化，而且也很难找到业务的起始点，所以只能通过mysqldump将主库当前数据库里面的所有数据全部导出来做个标记，然后查看二进制文件以及pos点信息

[root@v1 ~]# mysqldump -uroot -p -A --master-data > all.sql