Mysql主从

简介:

数据库高可用一直是企业的重中之重,而采用主从方案,一主一从,能实现负载均衡,读写分离的作用,分担数据库的负荷,提高性能,而如果搭配keepalived还能实现高可用性,当主服务器故障以后,自动切换到从服务器上。

前提:

mysql主从的搭建前提有:

  • 两台虚拟机
  • 两台虚拟机上都装有mysql,并且版本一样,两边的mysql数据库文件一致,mysql的安装方式我在前面博客已经介绍过了。

    环境:

Host Role IP
mysql-1 Master 192.168.30.106
mysql-2 Slave 192.168.30.107

搭建

在主从服务器上都创建一个用户

$ mysql -u root -p
Enter password: ****
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'192.168.30.%' IDENTIFIED BY 'sjjhsn520@';

其实主服务器上只需要给REPLICATION SLAVE 权限,但是因为后面会把从库切换成主库所以就把权限设置一样了。

配置主库

修改配置文件

vi /etc/my.cnf

修改

server_id=1 //主从要不一样的id
log_bin=/home/mysql/log/datamybinlog   //看你自己编译的时候的配置

:wq保存
重启主库的Mysql服务:

systemctl restart mysqld
确认主库是否配置完成

登录

mysql -u root -p
mysql> SHOW MASTER STATUS;
+---------------------+----------+--------------+------------------+------------------------------------------+ 
|  File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |   Executed_Gtid_Set                     |
+---------------------+----------+--------------+------------------+------------------------------------------+
| datamybinlog.000012 |      952 |              |                  | 5d316b50-1955-11e9-950b-000c29204668:1-9 |
+---------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

因为我之前创建实验索引的时候操作过mysql,所以我的日志不是从001开始的,(原先创建的数据库删掉了)

配置从库

修改配置文件:

vi /etc/my.conf

修改

server-id = 2
log-bin = /home/mysql/log/datamybinlog
relay_log=/home/mysql/relaylog/relay-bin

需要创建一个主库的二进制日志文件的存放目录

mkdir /home/mysql/relaylog/relay-bin
chown mysql:mysql -R /home/mysql/relaylog/relay-bin
systemctl restart mysqld

配置一下参数

mysql -u root -p
Enter password: ****

设置

mysql> CHANGE MASTER TO MASTER_HOST='192.168.30.106',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='sjjhsn520@',
    -> MASTER_LOG_FILE='mysql-bin.0000012',
    -> MASTER_LOG_POS=0;

这里MASTER_LOG_FILE是根据上面主库的SHOW MASTER STATUS;输出来的。
开启服务

START SLAVE;
SHOW SLAVE STATUS\G  //查看状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.30.106
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: datamybinlog.000012
          Read_Master_Log_Pos: 952
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 1131
        Relay_Master_Log_File: datamybinlog.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql,information_scheme
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
....
1 row in set (0.00 sec)

Slave_IO_State, Slave_IO_Running, Slave_SQL_Running的值,可以看出复制已经运行。
可以测试一下,在主库创建一个数据库,然后去从库看有没有同步。
测试的过程我这里就不说了,自己尝试,

缺点

主从虽然能解决数据库服务器负载过高的问题以及备份的问题,但是主从同步是有延迟的,特别是当慢查询语句过多,主从服务器性能差别较大,或者网络中断情况的时候,也会造成同步延迟的问题,
mysql如何减少主从复制延迟:
如果延迟比较大,就先确认以下几个因素:

  • 从库硬件比主库差,导致复制延迟
  • 主从复制单线程,如果主库写并发太大,来不及传送到从库
    就会导致延迟。更高版本的mysql可以支持多线程复制
  • 慢SQL语句过多
  • 网络延迟
  • master负载
    主库读写压力大,导致复制延迟,架构的前端要加buffer及缓存层
  • slave负载
    一般的做法是,使用多台slave来分摊读请求,再从这些slave中取一台专用的服务器
    只作为备份用,不进行其他任何操作.
    另外, 2个可以减少延迟的参数:
–slave-net-timeout=seconds 单位为秒 默认设置为 3600秒

参数含义:当slave从主数据库读取log数据失败后,等待多久重新建立连接并获取数据

–master-connect-retry=seconds 单位为秒 默认设置为 60秒

参数含义:当重新建立主从连接时,如果连接建立失败,间隔多久后重试

通常配置以上2个参数可以减少网络问题导致的主从数据同步延迟

MySQL数据库主从同步延迟解决方案
最简单的减少slave同步延时的方案就是在架构上做优化,尽量让主库的DDL快速执行
还有就是主库是写,对数据安全性较高,比如sync_binlog=1,innodb_flush_log_at_trx_commit
= 1 之类的设置,而slave则不需要这么高的数据安全,完全可以讲sync_binlog设置为0或者关闭binlog
innodb_flushlog也可以设置为0来提高sql的执行效率。另外就是使用比主库更好的硬件设备作为slave

扩展

主从还能配合MySQL中proxy实现真正意义的读写分离,还能配合keepalived做主从切换高可用,还能配合mysqljump做异地备份,在从库定时物理备份,这样就不会在备份的时候占用业务性能。这些想法等我后期在慢慢验证了,哈哈

文章目录
  1. 1. 简介:
  2. 2. 前提:
  3. 3. 环境:
  4. 4. 搭建
    1. 4.1. 配置主库
    2. 4.2. 确认主库是否配置完成
    3. 4.3. 配置从库
  5. 5. 缺点
  6. 6. 扩展