mysql 主从复制

  • mysql 主从复制
    • 主从复制理论
    • 主从配置环境
    • 主从复制参数
    • 主从复制配置

1.0 主从复制理论

1.1 什么是主从复制

1
2
3
4
5
6
7
8
// 主从复制: 
    1. 是MySQL数据库的一种容灾备份方案,是MySQL自带的功能,无需借助第三方工具
    2. MySQL的主从复制并不是数据磁盘上的文件直接拷贝,而是通过逻辑的binlog日志复制到要同步的服务器本地然后由本地的线程读取日志里面的SQL语句重新应用到MySQL数据库中。

// MySQL 复制技术: 
    1. MySQL内建的复制功能是通过将MySQL的某一台主机(master)的数据复制到其它主机(slaves)上,并重新执行一遍来实现的
    2. 复制过程中一个服务器充当主服务器,而一个活多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环。
    3. 当一个从服务器连接主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新

1.2 MySQL 复制作用、应用场景等

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// MySQL 复制能干什么: 
    数据分布
    负载平衡
    备份/容灾
    高可用性和容错

// MySQL 主从复制应用场景
    应用场景: 数据备份与容灾、读写分离、业务拆分
    使用公司: 适合于初创公司、小型电商/互联网公司、一些小型云环境
    切换技术: 手工切换,时间: >30分钟(含重做主从关系,数据校验等)

// MySQL 主从复制如何工作:
    1. master 将改变记录到二进制日志中(这些记录叫做二进制日志事件)
    2. slave 将master的binary log events拷贝到它的中继日志(relay log)
    3. slave 重做中继日志中的事件,将日志操作还原并生成数据

1.3 MySQL 四种同步方式

1
2
3
4
5
// MySQL 有四种同步方式:
    异步复制: 搭建简单,使用非常广泛,从mysql诞生之初,就产生了这种架构,性能非常好,可谓非常成熟。但这种架构数据是异步的,所以有丢失数据的风险
    全同步复制: 保证数据安全,不丢数据,损失性能
    传统半同步复制: 性能,功能都介于异步和全同步中间。从mysql5.5开始诞生,目的是为了折中上述两种架构的性能以及优缺点
    无损复制,增强版的半同步复制: 数据零丢失,性能好,mysql 5.7 诞生

2.0 主从配置环境

  • 主从架构: 一主两从、主库用于生产、从库用于数据容灾和主库备机
主机IP角色端口版本最小配置系统版本
10.10.10.150master3306mysql 5.7.401C/1G/100Gcentos 7.9
10.10.10.151slave3306mysql 5.7.401C/1G/100G
10.10.10.152slave3306mysql 5.7.401C/1G/100G

3.0 主从复制参数

1
2
3
4
5
// MySQL 主从复制常用配置参数
bind-address=10.10.10.150                                       # 绑定地址
server_id=1503306                                               # 主从不相同,规范建议: IP+PORT
skip_name_resolve = off                                         # 跳过主机名/域名解析
transaction-isolation = read-committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
// 关于 binglog 二进制日志参数
log_bin=/mysql/log/3306/binlog/ink8sdb-binlog                   # 二进制日志路径
log_bin_index=/mysql/log/3306/binlog/ink8sdb-binlog.index       # 二进制日志索引
binlog_format=row                                               # 二进制日志格式; 行记录(必须)
binlog_rows_query_log_events=on                                 # 二进制日志中记录更详细的SQL操作
sync_binlog = 1                                                 # 默认, MySQL每次提交事务之前会将二进制不同到磁盘,保证服务器崩溃时不会丢失事件
innodb_flush_log_at_trx_commit=1                                # 默认, 每次事务提交时MySQL都会把log buffer 的数据写入 log file,并且flush(刷到磁盘)中去
log_bin_trust_function_cteators=1                               # 默认为 0,同步函数和存储过程
max_binlog_size=2048M                                           # 默认为 1024M
expire_logs_days=7                                              # binlog 保留天数,看具体安排
binlog_cache_size=1M                                            # 默认32k,binlog缓存的大小,设置时要当心,建议1~4M,根据业务繁忙情况
innodb_support_xa=1                                             # 这个参数是在主库上设置的,默认是自动开启;看到xa首先首先想到的就是分布式事务了,
                                                                这个参数确保事务日志写入bin-log的顺序与事务的time-line是一致的。
                                                                这样系统崩溃的时候,启用日志恢复,可以严格按照时间线来恢复数据库
1
2
3
4
5
6
7
8
// 关于 relay_log 中继日志参数
rela_log = /mysql/log/3306/relaylog/ink8sdb-relay.log
relay-log-recover = 1                                           # 中继日志打开
// I/O thread crash safe -IO线程安全; 打开 replication 中继日志崩溃恢复模式,replication 支持中继日志的自我修复功能。当 slave从库宕机后,如果 replay-log发生损坏,导致一部分中继日志没有处理,就自动放弃未执行的replay-log,
// 重新从 msater 上获取日志,完成了中继日志的恢复,该参数表示当前接收的relay-log全部删除,然后从SQL线程回放到的位置重新拉取
relay_log_info_repository = table                               # 默认是 file，SQL线程的数据回放是写暑假库操作,relay-info是写文件操作,这两个操作很难保证一致性,relay-info将写入到mysql.slave_relay_log_info这张表中
master_info_repository = table                                  # 默认是 file, IO线程也是接收一个个的event,将接收到的event,通过设置参数master_info_repository可以将master-info信息写到什么位置,性能上比设置为FILE有很高的提升,
// 可靠性也得到保证,设置为TABLE后,master-info将信息保存到mysql.slave_master_info
1
2
3
4
5
6
7
// 关于同步方式的参数(不加下面参数默认就是异步同步)
loose_rpl_semi_sync_master_enabled = 1                           # MySQL5.6开启半的半同步复制(rpl_semi_sync_master_enabled)
loose_rpl_semi_sync_slave_enabled = 1                            # MySQL5.6开启从的半同步复制(rpl_semi_sync_slave_enabled)
loose_rpl_semi_sync_master_timeout = 5000                        # 超过 5s,切回异步
rpl_semi_sync_master_wait_for_slave_count = 1                    # 至少收到 1 个slave 发回的 ack
rpl_semi_sync_master_wait_point = AFTER_SYNC                     # MySQL5.7的方法,开启无损复制(与下面设置只能选择一个)
rpl_semi_sync_master_wait_point = AFTER_COMMIT                   # MySQL5.7的方法,开启半同步复制
1
2
3
4
5
6
7
8
9
10
11
12
13
// 关于 GTID 参数
gtid_mode = on                                                   # 打开 gtid 模式
    on                      # 产生 GTID, slave 只接受带 GTID 的事务
    ON_PERMISSIVE           # 产生 GTID,slave接受不带 GTID 事务也接受带 GTID 的事务
    OFF                     # 不产生 GTID, slave 只接受不带参数 GTID 的事务
    OFF_PERMISSIVE          # 不产生 GTID, slave 接受不带 GTID 事务也接受带GTID 的事务
log_slave_updates = 1                                            # 当从库log_slave_updates参数没有开启时,从库的binlog不会记录来源于主库的操作记录
                                                                 只有开启log_slave_updates,从库binlog才会记录主库同步的操作日志
                                                                 如果是多个从库,切换后,建议关掉log-slave-updates参数,否则重置成主库以后,可能会将已经执行过的二进制日志重复传送给S2,导致S2同步错误
enforce_gtid_consistency = 1
    on(1)                   # 当发现语句/事务不支持 GTID 时,返回错误信息
    WARN                    # 当发现不支持语句/事务,返回警告,并在日志中记录警告信息
    OFF(0)                  # 不检查是否有 GTID 不支持的语句/事务

upload successful

4.0 主从复制配置

4.1 MySQL 安装和目录准备

  • 以下配置3台服务器均操作
4.1.1 MySQL 安装
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# mkdir -p /mysql/{app,data/3306,log/3306}
# tar -xf mysql-5.7.40-linux-glibc2.12-x86_64.tar 
# tar -zxf mysql-5.7.40-linux-glibc2.12-x86_64.tar.gz
# mv mysql-5.7.40-linux-glibc2.12-x86_64 /mysql/app/mysql

# vim ~/.bash_profile 
...
PATH=$PATH:/mysql/app/mysql/bin:$HOME/bin
# source ~/.bash_profile 
# which mysql

// 创建授权用户
# groupadd mysql
# useradd -r -g mysql -s /bin/false mysql
# chown -R mysql.mysql /mysql
4.1.2 目录准备
1
2
3
4
// 准备主从二进制与中继日志目录; 3台服务器全部配置
# mkdir -p /mysql/log/3306/{binlog,relaylog}
# chown -R mysql.mysql /mysql/log/3306/{binlog,relaylog}
# chmod -R 755 /mysql/log/3306/{binlog,relaylog}
4.1.3 Systemd 启停文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# cat /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Service]
User=mysql
Group=mysql
ExecStart=/mysql/app/mysql/bin/mysqld --defaults-file=/mysql/data/3306/my.cnf
LimitNOFILE = 65536
LimitNPROC = 65536

[Install]
WantedBy=multi-user.target

# systemctl daemon-reload
# systemctl enable mysqld
4.1.4 hosts 配置
1
2
3
4
5
6
# cat /etc/hosts
...
10.10.10.150    mysql01
10.10.10.151    mysql02
10.10.10.152    mysql03

4.2 MySQL 主配置文件

4.2.1 主库配置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
# cat /mysql/data/3306/my.cnf
[mysql]
default-character-set=utf8
socket=/mysql/data/3306/mysql.sock

[mysqld]
port = 3306
socket=/mysql/data/3306/mysql.sock
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
character-set-server=utf8

default-storage-engine=INNODB
innodb_buffer_pool_size=200M
max_allowed_packet=16M
explicit_defaults_for_timestamp=1
log-output=FILE
general_log=0
general_log_file=/mysql/log/3306/mysqldb-general.err
slow_query_log = ON
slow_query_log_file=/mysql/log/3306/mysqldb-query.err
long_query_time=10
log-error=/mysql/log/3306/mysqldb-error.err
# 以下主库配置
bind-address=10.10.10.150
server_id=1503306 
skip_name_resolve=ON 
expire_logs_days=7 
innodb_support_xa=1 
binlog_cache_size=1M 
max_binlog_size=2048M 
log_bin_trust_function_creators=1 
innodb_flush_log_at_trx_commit=1 
sync_binlog=1

transaction-isolation=READ-COMMITTED
gtid_mode=ON
enforce_gtid_consistency=1 
log-slave-updates=1 
binlog_gtid_simple_recovery=1
log_bin=/mysql/log/3306/binlog/mysqldb-binlog 
log_bin_index=/mysql/log/3306/binlog/mysqldb-binlog.index 
binlog_format=ROW
binlog_rows_query_log_events=on

plugin_dir=/mysql/app/mysql/lib/plugin/
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled=1 
loose_rpl_semi_sync_slave_enabled=1 
loose_rpl_semi_sync_master_timeout=5000 
rpl_semi_sync_master_wait_point=AFTER_SYNC 
rpl_semi_sync_master_wait_for_slave_count=1
4.2.2 slave1 从库配置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
# cat /mysql/data/3306/my.cnf
[mysql]
default-character-set=utf8
socket=/mysql/data/3306/mysql.sock

[mysqld]
port = 3306
socket=/mysql/data/3306/mysql.sock
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
character-set-server=utf8

default-storage-engine=INNODB
innodb_buffer_pool_size=200M
max_allowed_packet=16M
explicit_defaults_for_timestamp=1
log-output=FILE
general_log=0
general_log_file=/mysql/log/3306/mysql-general.err
slow_query_log = ON
slow_query_log_file=/mysql/log/3306/mysql-query.err
long_query_time=10
log-error=/mysql/log/3306/mysql-error.err
# 以下从库配置
bind-address=10.10.10.151
server_id=1513306 
skip_name_resolve=ON 
expire_logs_days=7 
innodb_support_xa=1 
binlog_cache_size=1M 
max_binlog_size=2048M 
log_bin_trust_function_creators=1 
innodb_flush_log_at_trx_commit=1 
sync_binlog=1

transaction-isolation=READ-COMMITTED
gtid_mode=ON
enforce_gtid_consistency=1
log-slave-updates=1
binlog_gtid_simple_recovery=1 
log_bin=/mysql/log/3306/binlog/mysql-binlog
log_bin_index=/mysql/log/3306/binlog/mysql-binlog.index 
binlog_format=ROW
binlog_rows_query_log_events=on
relay_log=/mysql/log/3306/relaylog/mysql-relay.log 
read_only=1

plugin_dir=/mysql/app/mysql/lib/plugin/
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled=1 
loose_rpl_semi_sync_slave_enabled=1 
loose_rpl_semi_sync_master_timeout=5000 
rpl_semi_sync_master_wait_point=AFTER_SYNC 
rpl_semi_sync_master_wait_for_slave_count=1
4.2.3 slave2 从库配置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
# cat /mysql/data/3306/my.cnf
[mysql]
default-character-set=utf8
socket=/mysql/data/3306/mysql.sock

[mysqld]
port = 3306
socket=/mysql/data/3306/mysql.sock
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
character-set-server=utf8

default-storage-engine=INNODB
innodb_buffer_pool_size=200M
max_allowed_packet=16M
explicit_defaults_for_timestamp=1
log-output=FILE
general_log=0
general_log_file=/mysql/log/3306/mysql-general.err
slow_query_log = ON
slow_query_log_file=/mysql/log/3306/mysql-query.err
long_query_time=10
log-error=/mysql/log/3306/mysql-error.err
# 以下从库配置
bind-address=10.10.10.152
server_id=1513306 
skip_name_resolve=ON 
expire_logs_days=7 
innodb_support_xa=1 
binlog_cache_size=1M 
max_binlog_size=2048M 
log_bin_trust_function_creators=1 
innodb_flush_log_at_trx_commit=1 
sync_binlog=1

transaction-isolation=READ-COMMITTED
gtid_mode=ON
enforce_gtid_consistency=1
log-slave-updates=1
binlog_gtid_simple_recovery=1 
log_bin=/mysql/log/3306/binlog/mysql-binlog
log_bin_index=/mysql/log/3306/binlog/mysql-binlog.index 
binlog_format=ROW
binlog_rows_query_log_events=on
relay_log=/mysql/log/3306/relaylog/mysql-relay.log 
read_only=1

plugin_dir=/mysql/app/mysql/lib/plugin/
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled=1 
loose_rpl_semi_sync_slave_enabled=1 
loose_rpl_semi_sync_master_timeout=5000 
rpl_semi_sync_master_wait_point=AFTER_SYNC 
rpl_semi_sync_master_wait_for_slave_count=1
4.2.4 初始化 MySQL
  • 3台 MySQL 均操作
1
2
3
4
5
6
7
8
9
# ln -sf /mysql/data/3306/my.cnf /etc/my.cnf
# yum -y install mariadb-libs libaio			# 解决初始化依赖问题
# /mysql/app/mysql/bin/mysqld --initialize --user=mysql --basedir=/mysql/app/mysql/ --datadir=/mysql/data/3306/data/
// 初始完成，查看初次登录密码
# tail -f /mysql/log/3306/mysqldb-error.err 
# systemctl start mysqld

// 密码修改
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'ink8s.com';
4.2.5 创建复制用户并授权
  • 3台 MySQL 均操作
1
2
3
mysql> create user 'synuser'@'10.10.10.%' identified with mysql_native_password by 'ink8s.com';
mysql> grant replication client,replication slave on *.* to 'synuser'@'10.10.10.%';
mysql> flush privileges;
4.2.6 slave 与 master 建立连接
  • 2台 slave 做相同操作
1
2
3
4
5
6
7
8
9
10
11
12
mysql> stop slave;
mysql> reset master;
mysql> reset slave;
mysql> change master to
    -> master_host='10.10.10.150',
    -> master_port=3306,
    -> master_user='synuser',
    -> master_password='ink8s.com',
    -> master_auto_position=1;

mysql> start slave;

4.2.7 SQL 线程报错解决
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
// SQL线程报错: mysql_native_password

1. 停掉2台slave
mysql> stop slave;

2. 删除3台synuser的用户。重新创建
mysql> drop user 'synuser'@'10.10.10.%';
mysql> create user 'synuser'@'10.10.10.%' identified with mysql_native_password BY 'ink8s.com';
mysql> grant replication client,replication slave on *.* to 'synuser'@'10.10.10.%';
mysql> flush privileges;

3. 主库重设
mysql> reset master;

4. 2台从库重设
mysql> reset master;
mysql> reset slave; 
mysql> change master to
    -> master_host='10.10.10.150', 
    -> master_port=3306, 
    -> master_user='synuser', 
    -> master_password='ink8s.com', 
    -> master_auto_position=1;
mysql> start slave;
mysql> show slave status\G;

upload successful

微信

加微信,入技术群

微信

扫一扫，分享到微信

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia-plus根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

运维技术面：
1. Linux 基础、脚本、服务部署、虚拟化
2. Linux 自动化、服务器硬件
3. Kubernetes、kube 服务部署
4. Windows 客户端,服务端的便捷应用

微信： wnn_chat

写博客的动力,源自于个人爱好!