mysql 主从同步

jack_zhou
240 阅读4分钟

这是我参与8月更文挑战的第13天，活动详情查看：8月更文挑战

配置方式

1.前提条件

停止对 master 数据库的操作，把 master 中的数据库全部导入到 slave，使两边数据库完全一致。

2. 配置 master

  1. 修改 master 的配置文件，使用二进制日志，指定 server-id，重启服务。目的是让各自都有了自己的唯一标示，并以二进制文件格式进行交流。Centos 中路径为 /etc/my.cnf。 [mysqld]
log-bin=mysql-bin //[必须]启用二进制日志
server-id=10//[必须]服务器唯一 ID，默认是 1，一般取 IP 最后一段

配置完成后需要重启 mysqlserver 才能生效。

systemctl restart mysqld
  1. 创建授权用户

登陆主服务器 mysql 命令行，创建一个用于从服务器复制的用户。

mysql -uroot -p
mysql> grant replication slave on *.* to 'root'@'%' identified by '123456';

"."表示对所有库的所有操作，“%”表示所有客户端都可能连，也可用具体客户端 IP代替，如 192.168.33.11，加强安全。

  1. 记录 master 状态信息

查看二进制日志文件名，及最新位置。让 slave 知道用哪个用户信息访问 master，知道读取哪个日志文件，及从哪儿开始读。

mysql> show master status;

其中 file、position 字段需要记录下值，mysql-bin.000001 是用于主从复制的文件名，437 是日志文件内的最新位置。

3. 配置 slave

  1. 修改配置文件 my.cnf，使用二进制日志，指定 server-id，重新启动服务。 [mysqld]
log_bin=mysql-bin
server_id=11
  1. 将 slave 指向 master

登陆从服务器 mysql 命令行，使用之前创建的用户和 master 的日志文件及其位置。 slave 中使用被授权用户信息及日志文件信息，进行指向 master。这时已经建立了和 master的联系，明确了从哪儿读取日志文件。

mysql>change master to
master_host='192.168.33.10',master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=437;

注意不要断开，“437”无单引号。

  1. 启动 slave

执行启动 slave 的命令，开始主从复制

mysql>start slave;
  1. 查看 slave 状态
mysql> show slave status;

结果中有两个重要数据项：

1) Slave_IO_Running: Yes IO 线程状态，必须 YES

2) Slave_SQL_Running: Yes SQL 线程状态，必须 YES

常见的问题是 SQL 线程没有正常工作 Slave_SQL_Running: No

通常是两边的数据库不是完全对应的，需要确保 master 上的库及到目前为止的最新记录都复制到 slave 上了。

对slave的操作：

stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;
show slave status;

4. 验证测试

当 IO 线程和 SQL 线程都正常后，到 master 中随意测试下插入、修改、删除操作，同时到 slave 中检查。

master 执行以下命令：

Create database mastertest；

Use mastertest;

CREATE TABLE `test` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(100) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert into test(name) values(‘boxuegu1test’);

Insert into test(name) values(‘boxuegu2test’);

Insert into test(name) values(‘boxuegu3test’);

slave 下验证

show databases;

use mastertest;

show tables;

select * from test;

主从复制涉及到的线程

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

  1. binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
  2. I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的重放日志（Relay log）中。
  3. SQL 线程 ：负责读取重放日志并重放其中的 SQL 语句。

主从同步的延迟原因及解决办法

主从同步的延迟的原因：

假如一个服务器开放 Ｎ 个连接给客户端，这样有会有大并发的更新操作, 但是从服务器的里面读取 binlog 的线程仅有一个， 当某个 SQL 在从服务器上执行的时间稍长或者由于某个 SQL 要进行锁表就会导致主服务器的 SQL 大量积压，未被同步到从服务器里。这就导致了主从不一致， 也就是主从延迟。

主从同步延迟的解决办法：

实际上主从同步延迟根本没有什么一招制敌的办法， 因为所有的 SQL 必须都要在从服务器里面执行一遍，但是主服务器如果不断的有更新操作源源不断的写入，那么一旦有延迟产生，那么延迟加重的可能性就会原来越大。当然我们可以做一些缓解的措施。

  1. 我们知道因为主服务器要负责更新操作， 它对安全性的要求比从服务器高，所有有些设置可以修改，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而 slave 则不需要这么高的数据安全，完全可以将 sync_binlog 设置为 0 或者关闭 binlog、innodb_flushlog、innodb_flush_log_at_trx_commit 也 可以设置为 0 来提高 SQL 的执行效率。
  2. 增加从服务器，这个目的还是分散读的压力， 从而降低服务器负载。
avatar
文章
阅读
粉丝