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做异地备份，在从库定时物理备份，这样就不会在备份的时候占用业务性能。这些想法等我后期在慢慢验证了，哈哈