专栏首页Linyb极客之路MySQL主从复制能完美解决数据库单点问题吗?

MySQL主从复制能完美解决数据库单点问题吗?

一、单个数据库服务器的缺点

  • 数据库服务器存在单点问题;
  • 数据库服务器资源无法满足增长的读写请求;
  • 高峰时数据库连接数经常超过上限。

二、如何解决单点问题

  • 增加额外的数据库服务器,组建数据库集群;
  • 同一集群中的数据库服务器需要具有相同的数据;
  • 集群中的任一服务器宕机后,其它服务器可以取代宕机服务器。

三、MySQL主从复制架构

1、主库将变更写入到主库的binlog中

  • 一些MySQL版本并不会开启二进制日志,所以一定要检查是否开启;
  • 如果刚开始没有开启,后面再进行开启的话,需要重启数据库才能生效,而且数据库的重启往往会对业务造成很大的影响;
  • 尽管二进制日志对性能有稍许的影响,所以还是建议大家无论是否使用复制功能,都要开启MySQL二进制日志,因为增量备份也需要二进制日志。

2、从库的IO线程在指定位置读取主库binlog内容存储到本地的中继日志(Relay Log)中

要完成二进制日志的传输过程,MySQL会在从服务器上启动一个工作线程,称为IO线程

这个IO线程会跟主数据库建立一个普通的客户端连接,然后在主服务器上启动一个特殊的二进制转储线程称为binlogdown线程。

从库上的IO线程通过这个二进制转储线程来读取主库上的二进制事件,如果该事件追赶上主库,则会进入sleep状态,直到主库发起信号通知有新事件产生时,才会被唤醒,relay log的格式和binlog格式是完全相同的,

可以使用mysqlbinlog来读取relay log中的内容。

3、从库的SQL线程读取Relay Log日志中的内容,并在从库中重放

SQL线程所执行的事件,我们可以通过配置选项来决定是否要写入到从服务器的二进制日志中。

目前MySQL支持两种复制类型:

  • 基于二进制日志点的复制
  • 基于GTID的复制(MySQL>=5.7推荐使用)

四、MySQL主从配置步骤

1、配置主从数据库服务器参数

有些参数配置后需要数据库重启才能生效,为了不影响数据库的正常使用,我们最好在服务器上线的同时就把参数都配置好。

特别是master服务器的参数,更应该作为服务器初始参数来进行配置。

master服务器:

log_bin  = /data/mysql/sql_log/mysql-bin    # 指定mysql的binlog的存放路径 /data/mysql/sql_log,以及日志文件名前缀mysql-bin ,
                                                                    # 如果只是为了启用binlog,可以不指定存放路径,默认会存放到mysql的data目录下,也就是会把日志和数据文件存放在一起
                                                                    # 之所以指定路径分开存放,是为了提高IO性能,所以还是建议日志文件和数据文件分开存放
server_id = 100    # mysql的复制集群中通过server_id的值区分不同的服务器,建议使用服务器ip的后一段或后两段的值进行配置,比如192.168.3.100,就设置为100或2100

slave 服务器:

log_bin  = /data/mysql/sql_log/mysql-bin 
server_id = 101

relay_log = /data/mysql/sql_log/relay-bin    # 指定relay_log日志的存放路径和文件前缀 ,不指定的话默认以主机名作为前缀

read_only = on    #    使所有没有server权限的用户,在从服务器上不能执行写操作,不论这个用户是否拥有写权限 (mysql5.7 可以使用 super_read_only = on ,限制super用户也不能在从服务器上执行写操作)

skip_slave_start = on    # 在slave服务器重启时,不会自动启动复制链路。默认情况下slave服务器重启后,mysql会自动启动复制链路,如果这个时候存在问题,则主从链路会中断,所以正常情况下,我们应该在服务器重启后检查是否存在问题,然后再手动启动复制链路

# 下面两个参数是把主从复制信息存储到innodb表中,默认情况下主从复制信息是存储到文件系统中的,如果从服务器宕机,很容易出现文件记录和实际同步信息不同的情况,存储到表中则可以通过innodb的崩溃恢复机制来保证数据记录的一致性
master_info_repository = TABLE
relay_log_info_repository = TABLE

2、在master服务器上创建用于复制的数据库账号

用于IO线程连接master服务器获取binlog日志,需要* REPLICATION SLAVE** 权限:

create user 'repl'@'ip段' identified by 'password';
    grant replication slave on *.* to 'repl'@'ip段';

3、备份master服务器上的数据并初始化slave服务器数据

  • 建议主从数据库服务器采用相同的MySQL版本;
  • 建议使用全库备份的方式初始化slave数据。

采用相同版本的好处:

  • 我们可以使用全备的方式来初始化slave数据,还可以避免不同版本之间的差异造成数据库同步失败的问题。
  • 如果我们使用的主从复制的服务器MySQL版本不同,则一定要注意master上的版本一定要低于slave服务器,不然同步的时候就可能出现错误。

由于我们演示过程中的MySQL服务器都是使用的MySQL5.7,所以我们可以使用全备的方式进行:

mysqldump --master-data=2 -uroot -p -A --single-transaction -R --triggers

4、启动基于日志点的复制链路

在slave服务器上运行,MySQL命令:

CHANGE MASTER TO
MASTER_HOST= 'master_host_ip',
MASTER_USER= 'repl',
MASTER_PASSWORD = 'password',
MASTER_LOG_FILE='mysql_log_file_name',
MASTER_LOG_POS=xxxxxx;

5、启动基于GTID的复制链路

GTID:全局事务ID,GTID可以保证每一个在主上提交的事务,在复制集群中可以生成一个唯一的ID值,要使用基于GTID的复制,我们要在主从复制的配置文件中同时加入以下配置项。

MySQL配置:

gtid_mode=on # 是否启动gtid模式,启动了此模式会在二进制日志中会额外记录每个事务的GTID标识符
enforce-gtid-consistency    # 强制gtid一致性,用于保证启动gtid后事务的安全
log-slave-updates = on    # mysql5.6一定要启用参数,5.7可以不启用

MySQL命令:

CHANGE MASTER TO
MASTER_HOST= 'master_host_ip',
MASTER_USER= 'repl',
MASTER_PASSWORD = 'password',
MASTER_AUTO_POSITION=1;

GTID复制的限制:

  • 无法再使用create table ... select语句建立表,只能先create表,再insert数据;
  • 无法在事务中使用create temporary table建立临时表;
  • 无法使用关联更新同时更新事务表和非事务表。

4和5中选一个执行即可。

五. MySQL主从复制演示

1. 先对主服务器进行配置

[client]
port  = 3306     # 客户端端口号为3306
socket = /home/mysql/data/mysql.sock

[mysqld]

# skip #
skip_name_resolve = 1
skip-external-locking =1

# GENERAL #
user = mysql   # MySQL启动用户
default_storage_engine = InnoDB  # 新数据表的默认数据表类型
character-set-server = utf8      #     #服务端默认编码(数据库级别)
socket = /home/mysql/data/mysql.sock
pid_file =  /home/mysql/data/mysqld.pid
basedir = /home/mysql    #使用该目录作为根目录(Mysql安装目录);

port = 3306
bind-address = 0.0.0.0
log_error_verbosity = 3
explicit_defaults_for_timestamp = off
#sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#sql_mode = NO_ENGINE_SUBSTITUTION

# undo log
# innodb_undo_directory = /home.mysql/undo
# innodb_undo_tablespaces = 32

# MyISAM #
key_buffer_size =32M

# SAFETY #
max_allowed_packet    = 100M
max_connect_errors    = 1000000
sysdate_is_now    =1
#innodb = FORCE
#innodb_strict_mode = 1  

# Replice #
server-id = 100
relay_log =  /home/mysql/sql_log/mysqld-relay-bin 

#plugin-load = semisync_master.so
log_slave_updates = on
master_info_repository = TABLE
relay_log_info_repository =TABLE
# gtid_mode = on
# enforce_gtid_consistency =on
# skip-slave-start =1
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_timeout=200    # 0.2 second
master_info_respository = TABLE
# gtid_mode= on
# enforce_gtid_consistency = on
# skip-slave-start = 1

# DATA STORAGE #

datadir = /home/mysql/data     #mysql 数据文件存放的目录
tmpdir = /tmp    # MySQL存放临时文件的目录

# BINARY LOGGING #

log_bin = /home/mysql/sql_log/mysql-bin
max_binlog_size  = 1000M
binlog_format = row
expire_log_days = 7
sync_binlog = 1

# CACHES AND LIMITS #

tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0  

由于主服务器一直在运行着,在生产环境中主服务器是很少会重启的,如果主服务器重启,会造成正常的业务访问的中断,所以在服务器启动之前就启动了二进制日志。

这里不需要重启主服务器了,由于主服务器的默认server_id=1,我们虽然在配置文件中更改了它的值 ,但实际运行环境中并没有改变。

我们可以查看一下当前server_id:

mysql> show variables like '%server_id%';

可以通过以下命令动态的进行修改:

mysql> set global server_id = 100;

2. 再对从服务器进行配置

[client]
port  = 3306     # 客户端端口号为3306
socket = /home/mysql/data/mysql.sock

[mysqld]

# skip #
skip_name_resolve = 1
skip-external-locking =1

# GENERAL #
user = mysql   # MySQL启动用户
default_storage_engine = InnoDB  # 新数据表的默认数据表类型
character-set-server = utf8      #     #服务端默认编码(数据库级别)
socket = /home/mysql/data/mysql.sock
pid_file =  /home/mysql/data/mysqld.pid
basedir = /home/mysql    #使用该目录作为根目录(Mysql安装目录);

port = 3306
bind-address = 0.0.0.0
log_error_verbosity = 3
explicit_defaults_for_timestamp = off
#sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#sql_mode = NO_ENGINE_SUBSTITUTION
read_only = on

# undo log
# innodb_undo_directory = /home.mysql/undo
# innodb_undo_tablespaces = 32

# MyISAM #
key_buffer_size =32M

# SAFETY #
max_allowed_packet    = 100M
max_connect_errors    = 1000000
sysdate_is_now    =1
#innodb = FORCE
#innodb_strict_mode = 1  

# Replice #
server-id = 101
relay_log =  /home/mysql/sql_log/mysqld-relay-bin 

#plugin-load = semisync_master.so
log_slave_updates = on
master_info_repository = TABLE
relay_log_info_repository =TABLE
# gtid_mode = on
# enforce_gtid_consistency =on
# skip-slave-start =1
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_timeout=200    # 0.2 second
master_info_respository = TABLE
# gtid_mode= on
# enforce_gtid_consistency = on
# skip-slave-start = 1

# DATA STORAGE #

datadir = /home/mysql/data     #mysql 数据文件存放的目录
tmpdir = /tmp    # MySQL存放临时文件的目录

# BINARY LOGGING #

log_bin = /home/mysql/sql_log/mysql-bin
max_binlog_size  = 1000M
binlog_format = row
expire_log_days = 7
sync_binlog = 1

# CACHES AND LIMITS #

tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0  

修改完从服务器配置后,重启MySQL服务器。如果使用的是MySQL5.7版本的需要注意:

  • MySQL5.7增加了server-uuid值,默认情况下载auto.cnf文件中 如果是使用的镜像的方式安装,可能大家的uuid一样 ,所以需要把auto.cnf文件删除掉。 MySQL重启后会自动重新生成uuid的值,这样就可以保证不同服务器上的MySQL实例的uuid的值是不一样的;
  • 如果server-uuid的值相同,主从复制会出现问题。

以上我们就完成了主从复制的配置,接下来我们要在主服务器上建立复制账号。

3. 在MySQL主服务器上建立MySQL复制账号

mysql> create user 'dba_repl'@'192.168.3.%' identified by '123456';
mysql> grant replication slave on *.* to 'dba_repl'@'192.168.3.%';

4. 建立好复制账号以后,通过mysql主服务器上的全备初始化从服务器上数据

进行全备:

[root@localhost data]# cd /data/db_backup/
[root@localhost db_backup]#  mysqldump -uroot -p --master-data=1 --single-transaction --routines --triggers --events  --all-databases > all.sql
Enter password: 

将其拷贝到从服务器上:

[root@localhost db_backup]# scp all.sql root@192.168.3.101:/root

在从服务器上恢复备份进行初始化:

[root@Node2 ~]# mysql -uroot -p < all.sql

初始化完成后,准备。

5. 从服务器进行基于日志点的复制链路的配置

mysql> change master to master_host='192.168.3.100',
        -> master_user='dba_repl',
        -> master_password='123456',
        ->MASTER_LOG_FILE='mysql-bin.000017',MASTER_LOG_POS=663;

MASTER_LOG_FILE和MASTER_LOG_POS的值从全备文件中的CHANGE MASTER中获取

以上复制链路的配置完成。

启动slave:

mysql> start slave;

检查是否启动成功状态:

mysql> show slave status \G

显示:

Relay_Master_Log_File: mysql-bin.000017
Slave_IO_Running:Yes
Slave_SQL_Running: Yes

说明启动成功了,可以在主服务器上插入数据,在从服务上查看数据是否同步过来了。

六. 主从复制的一些缺点

虽然主从复制增加了一个数据库副本,但从数据库和主数据库的数据最终会是一致的。

之所以说是最终一致,因为MySQL复制是异步的,正常情况下主从复制数据之间会有一个微小的延迟。

通过这个数据库副本看似解决了数据库单点问题,但并不完美:

因为这种架构下,如果主服务器宕机,需要手动切换从服务器,业务中断不能忍受,不能满足应用高可用的要求。

作者:听风 来源:https://www.cnblogs.com/huchong/p/10253522.html

本文分享自微信公众号 - Linyb极客之路(gh_c420b2cf6b47),作者:听风

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-10-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL数据库“十宗罪”(十大经典错误案例)

    Too many connections(连接数过多,导致连接不上数据库,业务无法正常进行)

    lyb-geek
  • MySQL慢查询日志总结

    MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过longquerytime值的SQL,则...

    lyb-geek
  • 面对峰值响应冲击,解决高并发的三大策略

    在写这篇博客的前2天,听说某系统在25人的用户量下就宕机了,实在让人震惊,所以捋了下互联网交易系统我们可以采取哪些技术来解决互联网平台下大数据量高并发的问题。

    lyb-geek
  • (8) MySQL主从复制架构使用方法

    数据库服务器存在单点问题 数据库服务器资源无法满足增长的读写请求 高峰时数据库连接数经常超过上限

    用户1214487
  • CentOS6.5下MySQL 5.6安装

    mysql安装包默认下载到/data/src目录下 mysql安装目录为 /data/soft/mysql mysql的数据库data目录 /data/da...

    luxixing
  • Linux基础(day55)

    13.4 mysql用户管理 mysql用户管理目录概要 grant all on . to 'user1' identified by 'passwd'; g...

    运维小白
  • MYSQL安装之最新版MySQL手把手安装教程

    MySQL8.0.13 For Windows zip包下载地址:https://dev.mysql.com/downloads/file/?id=480557...

    测试小兵
  • 超详细的MySQL5.6安装部署教程

    如果需要配置服务(如进行开机自启动等),可以进行配置,生产环境上如有多个实例等不建议如此配置

    July
  • 超详细的MySQL5.6安装

    如果需要配置服务(如进行开机自启动等),可以进行配置,生产环境上如有多个实例等不建议如此配置

    July
  • macOS 安装 mysql

    打开下载页面 https://dev.mysql.com/downloads/mysql/5.7.html ,下载镜像安装文件。

    我是一条小青蛇

扫码关注云+社区

领取腾讯云代金券

玩转腾讯云 有奖征文活动