欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

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

程序员文章站 2022-05-01 19:11:42
开发环境 master环境：ubuntu16.04.5LTS/i5/8G/500G/64位/mysql5.7.23/php7/apache2 slave环境：kvm虚拟机/ubuntu14.04.01/1G/30G/mysql5.7.23 主从复制读写分离原理 主从复制： 主服务器数据库的每次操作都 ......

开发环境

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

参考文章：

 

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 的双机热备部署方案 ）。

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

 

上一篇： 敦煌在古代有着怎样的发展历史？莫高窟又是如何诞生的？

下一篇： Oracle开发之分析函数简介Over用法

推荐阅读