跳至主要內容

MySQL主从复制

zhangtq2023年8月13日大约 5 分钟技术栈数据库mysql

主从复制

原理

  1. master 服务器将数据的改变都记录到binlog日志中, 只要master上的数据发生改变都会将其写入二进制日志
  2. slave服务器会在一定的时间间隔内对master的二进制日志文件进行探测，检测是否发生改变，如果改变，就开启一个I/O Thread 请求master二进制时间
  3. 同时主节点为I\O Thread启动dump线程，向slave发送二进制事件，保存到从节点的终极日志中
  4. 从节点启动SQL 线程从中继日志中读取二进制时间，在本地重放，使主从节点的数据保持一致
  5. 最后I/O Thread和SQL Thread将进入睡眠状态，等待下一次被唤醒。
  • 注意：
    • 从库会生成两个线程,一个I/O线程,一个SQL线程;
    • I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
    • 主库会生成一个log dump线程,用来给从库I/O线程传binlog;
    • SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行*

流程图

  • master将操作语句记录到binlog日志中
  • 2、salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变
  • 3、salave开启两个线程：IO线程和SQL线程
    • 1）IO线程：负责读取master的binlog内容到中继日志relay log里；
    • 2）SQL线程：负责从relay log日志里读出binlog内容，并更新到slave的数据库里（保证数据一致）
img
img

MySQL同步延迟

  • 1、造成mysql同步延迟常见原因
    • 1）网络：如主机或者从机的带宽打满、主从之间网络延迟很大，导致主上的binlog没有全量传输到从机，造成延迟。
    • 2）机器性能：从机使用了烂机器？比如主机使用SSD而从机还是使用的SATA。
    • 3）从机高负载：有很多业务会在从机上做统计，把从机服务器搞成高负载，从而造成从机延迟很大的情况
    • 4）大事务：比如在RBR模式下，执行带有大量的delete操作，这种通过查看processlist相关信息以及使用mysqlbinlog查看binlog中的SQL就能快速进行确认
    • 5）锁: 锁冲突问题也可能导致从机的SQL线程执行慢，比如从机上有一些select .... for update的SQL，或者使用了MyISAM引擎等。
  • 2、硬件方面（优化）
    • 1.采用好服务器，比如4u比2u性能明显好，2u比1u性能明显好。
    • 2.存储用ssd或者盘阵或者san，提升随机写的性能。
    • 3.主从间保证处在同一个交换机下面，并且是万兆环境。
    • 总结：硬件强劲，延迟自然会变小。一句话，缩小延迟的解决方案就是花钱和花时间。

mysql主从配置与实战

安装mysql主从服务器

初始化docker中mysql挂载目录

# 新建2个目录, 存放master和slave的配置和数据
cd ~   # ~ 代表家目录   /home/你的用户名/
mkdir mysql_master
mkdir mysql_slave

cd mysql_master
mkdir data
cp -r /etc/mysql/mysql.conf.d ./

cd ../mysql_slave
mkdir data
cp -r /etc/mysql/mysql.conf.d ./

MySQL主机配置和从机配置

  • 主机配置
# vim /root/mysql_master/mysql.conf.d/mysqld.cnf
# 端口
port = 3307
# 开启日志
general_log_file = /var/log/mysql/mysql.log
general_log = 1
# 主机唯一编号
server-id = 1
# binlog日志文件
log_bin = /var/log/mysql/mysql-bin.log
  • 从机配置
# 从机配置  /root/mysql_slave/mysql.conf.d/mysqld.cnf
port = 3308
general_log = 0  
server-id = 2

使用docker安装mysql主从

#1.下载mysql 5.7.32版本的mysql
docker pull mysql:5.7.32                     # 直接通过官方下载

#2.启动master
sudo docker run  -d --name mysql-master -e MYSQL_ROOT_PASSWORD=1 --network=host -v /root/mysql_master/data:/var/lib/mysql -v /root/mysql_master/mysql.conf.d:/etc/mysql/mysql.conf.d mysql:5.7.32
            
#3.启动slave         
sudo docker run  -d --name mysql-slave -e MYSQL_ROOT_PASSWORD=1 --network=host -v /root/mysql_slave/data:/var/lib/mysql -v /root/mysql_slave/mysql.conf.d:/etc/mysql/mysql.conf.d mysql:5.7.32
            
#4.测试master/slave  (密码是1)
mysql -uroot -p1 -h 192.168.56.100 --port=3307
mysql -uroot -p1 -h 192.168.56.100 --port=3308

#5.导出导入数据
#5.1 从主机导出
mysqldump -uroot -p1 -h192.168.56.100 -P3307 --all-databases --lock-all-tables > ~/master_db.sql
#5.2 导入从机
mysql -uroot -p1 -h192.168.56.100 -P3308 < ~/master_db.sql

配置MySQL主从

  • 配置mysql master
# 配置master

# 登录到主机
mysql –uroot –p -h 192.168.56.100 -P 3307
# 创建从机账号
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
# 刷新权限
FLUSH PRIVILEGES;

# 查看二进制日志信息, 记录 文件名 和 偏移量, 后面会用到
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      722 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
  • 配置mysql slave
# 配置slave
# 登录到从机
$ mysql -uroot -p1 -h192.168.56.100 -P3308
# 从机连接到主机
$ change master to master_host='192.168.56.100', master_port=3307, master_user='slave', master_password='slave',master_log_file='mysql-bin.000003', master_log_pos=722;
# 开启从机服务
$ start slave;
# 展示从机服务状态
$ show slave status \G

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.100
                  Master_User: slave
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 722
               Relay_Log_File: dev-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

django使用mysql主从

django中配置mysql主从

# 配置django
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'syl',
        'HOST': '127.0.0.1',
        'PORT': '3307',
        'USER': 'root',
        'PASSWORD': '1',
    },
    'slave': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'syl',
        'HOST': '127.0.0.1',
        'PORT': '3308',
        'USER': 'root',
        'PASSWORD': '1',
    }
}

# 数据库路由配置
DATABASE_ROUTERS = ['utils.db_router.MasterSlaveDBRouter']

编写mysql路由文件

# utils/db_router.py
class MasterSlaveDBRouter(object):
    """数据库读写路由"""

    def db_for_read(self, model, **hints):
        """读"""
        return "slave"

    def db_for_write(self, model, **hints):
        """写"""
        return "default"

    def allow_relation(self, obj1, obj2, **hints):
        """是否运行关联操作"""
        return True