MySQL从服务器写入报错吗_MySQL主从复制配置指导及PHP读写分离源码分析

最新推荐文章于 2024-04-21 03:22:36 发布

weixin_39819138

最新推荐文章于 2024-04-21 03:22:36 发布

阅读量89

点赞数

文章标签： MySQL从服务器写入报错吗

本文链接：https://blog.csdn.net/weixin_39819138/article/details/113443631

版权

开发环境

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安装虚拟机

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"

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

//slave1

'dbpw' => '123455',

string(9) "localhost"

小结根据打印，写操作走的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 负载均衡并添加一台监控设备实现双机热备(

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

weixin_39819138

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫