javaWeb学习

MySQL复制功能

2020-03-12  本文已影响0人  小波同学

mysql复制功能

MySQL 内建的复制功能是构建大型，高性能应用程序的基础。将 MySQL 的 数据分布到到多个系统上去，这种分布的机制，是通过将 MySQL 的某一台主机的数据复制到其它主机( Slave )上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置，从服务器接收从那时起发生的任何更新，然后封锁等等主服务器通知新的更新。请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对服务器上的表所进行的更新之间的冲突

复制问题解决了什么问题：

MySQL二进制日志

MySQL有很多种日志，按照记录日志的MySQL组件可以分为MySQL服务层日志和MySQL存储引擎层日志，比较重要的是MySQL二进制日志，如下：

主服务器的所有变更点都记录在二进制日志里面。二进制日志里记录了与更新相关的SQL文，执行查询的日期时间等元数据也记录在内。事务提交的同时以二进制的形式进行记录(sync_binlog=1)。

mysqlbinlog 命令可以查看日志内容。指定启动选项进行输出二进制日志。

--log-bin[=file_name]

MySQL二进制日志格式

基于段的格式：binlog_format=STATEMENT 
mysql> show variables like 'binlog_format'; #查看二进制日志格式，下边显示使用row
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)


mysql> set session binlog_format=statement;  #设置成statement格式

mysql> show variables like 'log_bin'; #查询二进制日志是否开启，显示关闭
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+

开启二进制日志
打开mysql的配置文件添加log_bin配置

[mysqld]
log_bin = mysql_bin#位置自定义,如/var/lib/mysql/sql_log/mysql_bin

刷新bin_logs;

mysql>flush logs; #刷新bin_logs
mysql>flush binary logs;
+---------------------+-------------+
| Log_name            | File_size   |
+---------------------+-------------+
| mysql-bin.000001    | 201         |
+---------------------+-------------+
| mysql-bin.000002    | 154         |
+---------------------+-------------+
#各种数据库操作，statement日志会记录自行的SQL
>create xx
>select xxx
>update xx
>delete xx

查看二进制日志

$ cd /var/lib/mysql/sql_log/
$ ls 
mysql-bin.000001  mysql-bin.000002  mysql-bin.index  mysql-error.log

$ mysqlbinlog -vv mysql-bin.000002  #查看日志内容，可以清楚的看到执行的SQL语句
基于行的日志格式：binlog_format=ROW

ROW格式可以避免MySQL复制中出现的主从不一致问题，MySQL5.7默认采用的日志格式

基于行的日志会分别记录每一行的数据修改
优点

缺点

binlog_row_image
MySQL5.6为row格式增加一个binlog_row_image参数，这个参数可以控制row格式记录日志的方式

binlog_row_image=[FULL|MINIMAL|NOBLOB]
FULL：数据修改时，会记录一行数据的所有的列的内容，不论列是否被修改过
MINIMAL：只会记录修改的列的变化
NOBLOB：不记录BLOB类型的变化
混合日志格式：binlog_format = MIXED

特点：

如何选择二进制日志的格式

建议：binlog_format=mixed 或 binlog_format=row，使用row格式的话建议设置参数为binlog_row_image=minimal

MySQL二进制日志格式对复制的影响

根据二进制的数据格式，复制可以分为：

基于SQL语句的复制（SBR）

二进制日志格式使用statement格式
优点：

缺点：

基于行的复制（RBR）

二进制日志格式使用的是基于行的日志格式
优点：

缺点：

混合模式

根据实际内容在以上两者间切换

MySQL复制工作方式

基于日志点的复制

如果主从服务器上的MySQL都是最新安装的，为了实现主从复制需要进行如下步骤

mysql>create user 'repl'@'IP段' identified by 'PassWord';
mysql>grant replication slave on *.* to 'repl'@'IP段';

binlog_format二进制格式要设置，前面内容讲解到

my.cnf文件配置

[mysqld]
#启用二进制日志(可自定义存放位置,需要先创建并赋予权限)
log_bin = /var/lib/mysql/sql_log/mysql_bin
# mkdir /var/lib/mysql/sql_log
# chown -R mysql:mysql /var/lib/mysql/sql_log/

#指定主服务器的服务ID,整个服务集群中式唯一的(自定义,集群中不重复即可)
server_id = 100

#启用二进制日志(可自定义存放位置,需要先创建并赋予权限)
log_bin = /var/lib/mysql/sql_log/mysql_bin
# mkdir /var/lib/mysql/sql_log
# chown -R mysql:mysql /var/lib/mysql/sql_log/

#指定从服务器的服务ID,整个服务集群中式唯一的(自定义,集群中不重复即可)
server_id = 101

#启用中继日志(可自定义存放位置)
relay_log = /var/lib/mysql/sql_log/mysql_relay_bin

#可选，建议从服务器只读
read_only = on

#可选(如果需要将从服务器作为其他服务器的主服务器时必须设置，否则不设置)
log_slave_updates = on

如果是新服务器 或 数据都是一致的跳过此步骤

两种导出方式：

mysqldump --master-data -single-transaction#会对表造成大量阻塞
xtrabackup --slave-info#对全部都是innodb的表最好的选择,不会阻塞

mysql> SHOW MASTER STATUS \G;
*************************** 1. row ***************************
             File: mysql_bin.000007
         Position: 5856
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

mysql>CHANGE MASTER TO
    ->MASTER_HOST='master_host_ip',#主DB服务器IP
    ->MASTER_USER='repl',#主DB复制用户
    ->MASTER_PASSWORD='PassWord',#主DB复制用户密码
    ->MASTER_LOG_FILE='mysql_bin.000007',#在主DB查看日志文件
    ->MASTER_LOG_POS=5856;#在主DB查看节点

mysql>show slave status \G;#查看复制链路是否启动
mysql>start slave;#启动复制链路
mysql>show processlist; #可以看到从服务器上建立的进程

mysql>show slave status \G;
*************************** 1. row ***************************
             Slave_IO_Running: Yes                  #YES表示成功
            Slave_SQL_Running: Yes                  #YES表示成功
                Last_IO_Errno: 0                    #IO错误代码
                Last_IO_Error:                      #IO错误提示
               Last_SQL_Errno: 0                    #SQL错误代码
               Last_SQL_Error:                      #SQL错误提示

mysql> SHOW PROCESSLIST \G;
*************************** 2. row ***************************
     Id: 143
   User: repl
   Host: 192.168.10.1:55402
     db: NULL
Command: Binlog Dump
   Time: 9527
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL
*************************** 3. row ***************************

基于日志点的复制的
优点：

缺点：

基于GTID的复制

MySQL5.6 时支持的模式

基于GTID的复制与基于日志的的复制有很大的区别


什么是GTID

即全局事务ID，其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID
GTID=source_id:transaction_id

使用了GTID后不可使用如下操作

操作步骤

mysql>create user 'repl' @ 'IP段' identified by 'PassWord';
mysql>grant replication slave on *.* to 'repl' @ 'IP段';

log_bin = /usr/local/mysql/log/mysql-bin
server_id = 100
gtid_mode = on  #启动gtid
enforce-gtid-consiste #强制gtid一致性，保证事务安全
log-slave-updates = on # mysql5.7可以不用这个参数

server_id = 101
relay_log = /usr/local/mysql/log/relay_log
gtid_mode = on
enforce-gtid-consistency
log-slave-updates = on # mysql5.7可以不用这个参数
read_only = on  #建议
master_info_repository = TABLE #建议
realy_log_info_repository = TABLE #建议

mysqldump --single-transaction --master-data --triggers --routines --all-database -uroot -p >> all2.sql;
 #会对表进行加锁
xtrabackup --slave-info 

#scp all2.sql root@192.168.3.101:/root #拷贝备份文件到从库

# mysql -uroot -p < all2.sql #初始化从服务器，导入后主从数据库初始化是一致的。

mysql>CHANGE MASTER TO MASTER_HOST = 'master_host_ip'
                    MASTER_USER = 'repl'
                    MASTER_PASSWORD = 'PassWord'
                    MASTER_AUTO_POSITION = 1;

基于GTID复制的优缺点

优点：

缺点：

选择复制模式要考虑的问题

MySQL复制拓扑

下边来了解一下MySQL常见的拓扑结构

1、一主多从的复制拓扑

优点：

用途：

2、主-主复制
主备模式的主主复制模式：

有一台主服务器对外提供服务，一台服务器处于只读状态，并且只作为热备使用。
在对外提供服务的主库出现故障或者是计划性的维护时才会进行切换，使原来的备库成为主库，而原来的主库会成为新的备库，并处理只读或者是下线状态，维护完成后重新上线。

主备模式下的主-主复制的配置注意事项：

主主模式的主主复制模式

两个主同时对外提供服务，并不能分担写负载

主主模式下的主主复制缺点：

如果一定要使用主主复制模式，应该注意一下事项：

auto_increment_increment = 2
auto_increment_offset = 1 | 2

3、拥有备库的主-主复制拓扑
4、级联复制

可以避免分发主库连接从库过多而占用主库过多的带宽，使用分发主库可以解决这个问题。


MySQL复制性能优化

影响主从延迟的原因：

可以通过：控制主库的事务大小，分割事务来解决

可以通过：段使用MIXED日志格式或行设置set binlog_row_image=minimal来解决

可以使用使用多线程复制（MySQL5.6）,在MySQL5.7中可以按照逻辑时钟的方式来分配线程

MySQL5.7上使用多线程复制

mysql>stop slave
mysql>set global slave_parallel_type = 'logical_clock'; #使用逻辑时钟的方式复制
mysql>set global slave_parallel_workers=4; #设置多线程复制的数量
mysql>start slave;#启动复制
mysql>show processlist; #查看进程的数量
mysql>show variables like 'slave_parallel_type'; #查看并发类型配置
mysql>show variables like 'slave_parallel_workers'; #查看并发线程数量

MySQL复制常见问题处理

由于数据损坏或丢失所引起的主从复制错误

MySQL复制无法解决的问题

参考：
https://www.cnblogs.com/happy4java/p/11206023.html

http://blog.itpub.net/31549334/viewspace-2215034/

https://blog.csdn.net/Code_Pupil/article/details/89377400

https://www.cnblogs.com/davidwang456/p/10909905.html

上一篇 下一篇

猜你喜欢

热点阅读