【MySQL优化】读写分离（主从同步）

在程序中，70%的业务是对数据库的查询操作。高并发数据量大的情况下，通过采用多台服务器，对数据库的读写分离架构可以优化对数据库的操作。

要求：

1、读库和写库的数据一致。数据库版本一致。

2、写数据必须写到写库（或者主库），读数据必须到读库（从库）。

方案：

应用层解决（推荐）：代码正则匹配判断SQL语句类型，然后引导到不同的数据库进行操作。

中间件解决：所有请求到通过中间件，中间件判断SQL语句类型然后转发到不同的数据库进行操作。比如：mysql-proxy 、Amoeba

-----------------------------

主从数据库同步：

当master(主)库的数据发生变化的时候，变化会实时的同步到slave(从)库。

优点：水平扩展数据库的负载能力。数据备份。

实现原理：

在master机器上，主从同步事件会被写到特殊的log文件中(binary-log);

在slave机器上，slave读取主从同步事件，并根据读取的事件变化，在slave库上做相应的更改。

具体操作：

工具：

2台Linux服务器（推荐同一个内网的服务器）

master IP:192.168.33.22

slave  IP:192.168.33.33

master机器上的操作

1、更改配置文件

我们找到数据库配置文件：   /etc/mysql/mysql.conf.d/mysqld.cnf。

配置如下：

bind-address =192.168.33.22#your master ip
server-id =1#在master-slave架构中，每台机器节点都需要有唯一的server-id
log_bin =/var/log/mysql/mysql-bin.log #开启binlog

2、重启mysql，以使配置文件生效

systemctl restart mysql

3、创建主从同步的mysql user。

$ mysql -u root -p
Password:
##创建slave1用户，并指定该用户只能在主机192.168.33.33上登录。
mysql> CREATE USER 'slave1'@'192.168.33.33' IDENTIFIED BY 'slavepass';
Query OK,0 rows affected (0.00 sec)
##为slave1赋予REPLICATION SLAVE权限。即授权给从数据库。
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'192.168.33.33';
Query OK,0 rows affected (0.00 sec)

4、为MYSQL加读锁（转移已有数据）

为了主库与从库的数据保持一致，我们先为mysql加入读锁，使其变为只读。

mysql> FLUSH TABLES WITH READ LOCK;
Query OK,0 rows affected (0.00 sec)

5、使用以下命令，并且记录下File和Position 的值。

SHOW MASTER STATUS;

6、将master DB中现有的数据信息导出（转移已有数据）

$ mysqldump -u root -p --all-databases --master-data > dbdump.sql

7、解除master DB的读锁（转移已有数据）

mysql> UNLOCK TABLES;

8、将步骤6中的dbdump.sql文件copy到slave（转移已有数据）

scp dbdump.sql ubuntu@192.168.33.33:/home/ubuntu

slave机器上的操作

1、更改配置文件： /etc/mysql/mysql.conf.d/mysqld.cnf。

更改配置如下：

bind-address =192.168.33.33#your slave ip
server-id =2#master-slave结构中，唯一的server-id
log_bin =/var/log/mysql/mysql-bin.log #开启binlog

2、重启mysql，以使配置文件生效

systemctl restart mysql

3、导入从master DB。 导出的dbdump.sql文件，以使master-slave数据一致（转移已有数据）

$ mysql -u root -p </home/ubuntu/dbdump.sql

4、使slave与master建立连接，从而同步

$ mysql -u root -p
Password:
mysql> STOP SLAVE;
Query OK,0 rows affected,1 warning (0.00 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.33.22',
-> MASTER_USER='slave1',
-> MASTER_PASSWORD='slavepass',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=613;
Query OK,0 rows affected,2 warnings (0.01 sec)
mysql> START SLAVE;
Query OK,0 rows affected (0.00 sec)

MASTER_LOG_FILE='mysql-bin.000001'与MASTER_LOG_POS=613的值，是从上面的 SHOW MASTER STATUS 得到的。

经过如此设置之后，就可以进行master-slave同步了~

--------------------------------------------------------------------
承接网站开发、小程序开发、app开发、商城开发、公众号开发。
百度SEO排名、百度搜索霸屏、软文代写等业务。
联系QQ：2779459018     联系微信：qianxia39