MySQL主从复制配置指导及PHP读写分离源码分析

时间：2021-06-20 07:10:00

开发环境

master环境：ubuntu16.04.5LTS/i5/8G/500G/64位/mysql5.7.23/php7/apache2

slave环境：kvm虚拟机/ubuntu14.04.01/1G/30G/mysql5.7.23

主从复制读写分离原理

  • 主从复制：

    主服务器数据库的每次操作都会记录在二进制日志文件 A 中。从服务器的I/O线程到主服务器中读取 A ，并将 A 内容写入到自己本地的中继日志 B 中。然后从服务器的SQL线程会根据 B 中的内容执行SQL语句从而完成数据的复制。

    可做数据的备份、架构拓展、读写分离。

  • 读写分离：

    读写分离就是在主服务器上修改，数据会同步到从服务器，从服务器只能提供读取数据，不能写入，实现备份的同时也实现了数据库性能的优化，以及提升了服务器安全。

    可以实现读操作的负载均衡，适合读操作远远大于写操作的业务模型。

1 准备

1、KVM安装虚拟机

参考文章：三步玩转ubuntu kvm虚拟机系统

2、虚拟机安装mysq

参考文章：linux centos7下源码 tar安装mysql5.7.22

2 配置主从复制

2.1 配置主服务器

主机mysql配置文件在 /etc/mysql/mysql.conf.d/mysqld.cnf 打开新终端 执行 sudo -s 执行 vi /etc/mysql/mysql.conf.d/mysqld.cnf //修改配置文件 在[mysqld]模块里注释掉bind-address，用来允许远程访问数据库（默认是注释的） 在[mysqld]模块添加如下代码：

server-id = 1 #server-id 服务器唯一标识
log_bin = master-bin #log_bin 启动MySQL二进制日志
log_bin_index = master-bin.index
binlog_do_db = myslave #binlog_do_db 指定记录二进制日志的数据库
binlog_ignore_db = mysql #binlog_ignore_db 指定不记录二进制日志的数据库

保存退出 mysql -u root -p 登入主服务器数据库 数据库模式执行 grant replication slave,reload,super on . to slave@slaveip identified by 'password' //建 立一个帐户slave，并且只能允许从slaveip这个地址上来登陆，密码是你设置的password。 执行 exit //退出mysql模式 执行 service mysql restart //重启数据库（mysqld 则 service mysqld restart） 执行 mysql -u root -p //重新进入mysql 执行 FLUSH TABLES WITH READ LOCK; // 锁定主数据库 数据库模式执行 show master status\G; 记住查询出来的file和position的值，在后面的从库配置会用到 UNLOCK TABLES; //解锁主数据库

2.2 配置从服务器

虚拟机mysql配置文件在 /etc/my.cnf 执行 vi /etc/my.cnf //编辑从数据库配置 在该文件后直接添加如下（[mysqld模块]）:

server-id = 2
replicate-do-db =myslave
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index

保存退出 执行service mysql restart //重启数据库（mysqld 则 service mysqld restart） 执行 mysql -u root -p

change master to master_host='masterip',master_port=3306,master_user='slave',master_password='password',master_log_file='master-bin.000002',master_log_pos=1528;

master_host为主服务器ip，master_port为主服务器端口（默认3306）,master_user为主服务器grant replication的用户名,master_password为对应的密码password，master_log_file为主服务器操作2.1最后一步的file值，master_log_pos为相应的position值

执行 start slave; //开启数据同步 执行 show slave status\G; //查看同步状态 若slave_io_running为connecting时，可能是slave链接master服务器的用户名/密码错误或者网络连接失败。 若slave_io_running和slave_sql_running都是yes时，说明同步成功。此时主服务器数据库的增删改查都会被实时同步到从服务器数据库。

若有其他slave服务器需要配置，重复2.2，将server-id依次设置成3、4、5..即可

2.3 验证主从复制结果

  • 首先，我们在从服务器show slave status\G 查看同步状态，若slave_io_running和slave_sql_running都是yes时，说明同步成功。

  • 进一步检验：master中进入mysql执行 use myslave; 执行

    CREATE TABLE sla_student (id int(10) unsigned NOT NULL AUTO_INCREMENT,name varchar(255) NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8创建了一个sla_student表，这时候我们取slave中，mysql模式下（root账户或新创建的test都行）： 执行 use myslave; 执行 show tables； 发现也多了一个sla_student表。我们如果在master表中进行insert，slave中数据也会做相应改动哦。如果直接在slave表中进行数据insert，master是不会添加数据的，因为我们没有配置双主相互复制

3 实现读写分离、读负载均衡

3.1 读写分离

  • 读写分离实现：当我们的sql比较简单时（只是简单的select、update、insert、delete），判断sql是否以 ”select“为开头，是则连接从库；否则连接主库。

​ 关键代码如下：

'mdb' => [   //主数据库
'dbcharset' => 'utf8',
'dbhost' => 'localhost',     
'dbuser' => 'test',
'dbpw' => '123456',
'dbname' => 'myslave',
'dbport' => NULL,
'tbpre' => '',
'slaves_balancer' => ['slave1'=>1],     
],
​
'slave1'=>[   //从数据库slave1
'dbhost' => '192.168.xxx.xx1',
'dbuser' => 'test',
'dbpw' => '123456',
'dbcharset' => 'utf8',
'dbname' => 'myslave',
'tbpre' => '',
],
 if(strtoupper(substr(ltrim($sql), 0 , 6)) === 'SELECT'){
    $this->slaveConn();   //连接从库slave1
 }else{
    $this->masterConn();  //连接主库mdb
 }

3.2 检验读写分离结果

3.2.1 验证写操作

  • 程序中：

    //每次query打印出当前host
    var_dump($this->conn->host_info);
    ​
    ...
    ​
    //插入数据
    $tb = new table('sla_student');
    echo print_r($tb->insert(['name'=>'testNm']),1).PHP_EOL;

  • 1、将mdb数据库密码修改为错误的，slave1正常

    //mdb
    'dbpw' => '123455',

    返回：

Caught Exception: Error! Can not connect to mysql Database.

  • 2、将mdb数据库密码修改为正常，slave1正常

    //mdb
    'dbpw' => '123456',

    返回：

    string(9) "localhost"

    1

  • 3、将slave1的密码改为错误的,mdb正常

    //slave1
    'dbpw' => '123455',

    返回：

    string(9) "localhost"

    1

  • 小结根据打印，写操作走的localhost，当从库连接失败对写操作无影响。

3.2.2 验证读操作

  • 程序中：

  //每次query打印出当前host
  var_dump($this->conn->host_info);
  
  ...
  
  //读取一条数据
  $tb = new table('sla_student');
  echo print_r($tb->fetchRow(),1).PHP_EOL;

  • 1、将slave1的密码改为错误的，mdb正常

    //slave1
    'dbpw' => '123455',

    返回：

Caught Exception: Error! Can not connect to mysql Database.

  • 2、将slave1的密码改为正常的，mdb正常

    //slave1
    'dbpw' => '123456',

    返回

    string(15) "192.168.xxx.xx1"

    Array( [id] => 1 [name] => zhangsan)

  • 小结：当从数据库连接正常时，重复十次查询，打印结果都显示走的从数据库。

3.3 读负载均衡

这里也只是讲一下思路。

  • 读负载均衡解析：程序中，当我们连接从库时，可以加一个读均衡器，用均衡器随机得出我们连接选择。我们给性能较好的slave分配更高的权重，这样就会有更高的几率连接该机器使其更好地发挥作用。

  • 读负载均衡实现：当我们选择连接slave机器的时候，先读一下配置文件，查看针对该表设置的各个slave机器的权重：

关键代码如下：

'mdb' => [   //主数据库
'dbcharset' => 'utf8',
'dbhost' => 'localhost',     
'dbuser' => 'test',
'dbpw' => '123456',
'dbname' => 'myslave',
'dbport' => NULL,
'tbpre' => '',
'slaves_balancer' => ['slave1'=>3,'slave2'=>1],    //读负载均衡器 
],
​
'slave1'=>[   //从数据库slave1
'dbhost' => '192.168.xxx.xx1',
'dbuser' => 'test',
'dbpw' => '123456',
'dbcharset' => 'utf8',
'dbname' => 'myslave',
'tbpre' => '',
],
​
'slave2'=>[   //从数据库slave2
'dbhost' => '127.0.0.1',
'dbuser' => 'test',
'dbpw' => '123456',
'dbcharset' => 'utf8',
'dbname' => 'myslave',
'tbpre' => '',
],

这里我们给读负载均衡器增加了slave2并且给了其权重1，slave1的权重为3；

注意：slave2我没有配置，所以给其设置iphost为127.0.0.1，不同于于mdb中的localhost的是，var_dump打印出的host为'127.0.0.1'，而不是'localhost'。测试效果一样。

```
'slaves_balancer' => ['slave1'=>3,'slave2'=>1],  //读负载均衡器
```

进而：

```
$gravity = ['slave1'=>3,'slave2'=>1];
```

​

我们遍历这个gravity：

    $pick = '';
    foreach ($gravity as $sid => $weight)
    {
        $total_weight += $weight;
        $weights[$total_weight] = $sid;
    }
​
    $rand_weight = mt_rand(1, $total_weight);
​
    foreach ($weights as $weight => $sid)
    {
        if ($rand_weight <= $weight)
        {
            $pick = $sid; break;
        }
    }

最终我们得到的$pick就是我们最终的连接选择

3.4 检验读负载均衡结果

  • 程序中：

​
  //打印每次访问slave2次数和查询总次数的比
  var_dump($this->slave2Tms/$this->queryTimes);
  ...
  
  //读取一条数据，循环1000次
   $tb = new table('sla_student');
   for($i=0;$i<1000;$i++){
       echo print_r($tb->fetchRow(),1).PHP_EOL;
   }

返回

...Array( [id] => 1 [name] => zhangsan)

float(0.25150300601202)

Array( [id] => 1 [name] => zhangsan)

float(0.25225225225225)

Array( [id] => 1 [name] => zhangsan)

我们看到，slave2的访问概率到1000次访问时为0.252接近1/4。正好符合我们读负载均衡器 设置的1:3的概率。

  • 我们改动slave2值为7时，得到

...float(0.72417251755266)

Array( [id] => 1 [name] => zhangsan)

float(0.72444889779559)

Array( [id] => 1 [name] => zhangsan)

float(0.72472472472472)

Array( [id] => 1 [name] => zhangsan)

接近1000时概率为0.72也符合期望。

结尾

主从复制可以帮助我们实现读写分离，读操作又可以进一步实现为读负载均衡，其实我们还可以利用DNS 负载均衡并添加一台监控设备实现 双机热备（基于 Discuz!X 的双机热备部署方案 ）。

更多精彩期待我们共同的探索。

相关文章