mysql的主从服务Ｍ-S

最新推荐文章于 2024-08-06 10:01:49 发布

Rio520

最新推荐文章于 2024-08-06 10:01:49 发布

阅读量508

点赞数 1

分类专栏： Linux服务搭建

本文链接：https://blog.csdn.net/Rio520/article/details/102735925

版权

Linux服务搭建专栏收录该内容

87 篇文章 1 订阅

订阅专栏

什么是MySQL Replication

Replication可以实现将数据从一台数据库服务器（master）复制到一或多台数据库服务器（slave）
默认情况下属于异步复制，无需维持长连接
通过配置，可以复制所有的库或者几个库，甚至库中的一些表
是MySQL内建的，本身自带的

Replication的原理
简单的说就是master将数据库的改变写入二进制日志，slave同步这些二进制日志，并根据这些二进制日志进行数据操作
在这里插入图片描述
DML:SQL操作语句，update, insert，delete
Relay log :中继日志

Replication的作用
1、Fail Over 故障切换
2、Backup Server 备份服务，无法对SQL语句执行产生的故障恢复，有限的备份
3、High Performance高性能，可以多台slave,实现读写分离

Replication如何工作
整体上来说，复制有3个步骤：
(1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
(2) slave将master的binary log events拷贝到它的中继日志(relay log)；
(3) slave重做中继日志中的事件，修改salve上的数据。
在这里插入图片描述

mysql主从复制中：
第一步：master记录二进制日志。在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
第二步：slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经执行完master产生的所有文件，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
第三步：SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重新执行其中的事件而更新slave的数据，使其与master中的数据一致。

Replication常见方案：
1、One master and Muti salve 一主多备
在这里插入图片描述
一般用来做读写分离的，master写，其他slave读，这种架构最大问题I/O压力集中
在Master上<多台同步影响IO>

2、M-S-S
使用一台slave作为中继，分担Master的压力，slave中继需要开启bin-log,并配置log-slave-updates
在这里插入图片描述
Slave中继可使用Black-hole存储引擎，不会把数据存储到磁盘，只记录二进制日志

3、M-M 双主互备 (互为主从)
很多人误以为这样可以做到MySQL负载均衡，实际没什么好处，每个服务器需要做同样的同步更新，破坏了事物的隔离性和数据的一致性
在这里插入图片描述
不推荐，这样的并没有实现负载均衡
4、M-M-M
监控三台机器互相做对方的master

天生的缺陷：复制延迟，slave上同步要慢于master,如果大并发的情况那延迟更严重

Mysql在5.6已经自身可以实现fail over故障切换
5、One slave Muti master 一从对多主
好处：节省成本，将多个master数据自动化整合
缺陷：对库和表数据的修改较多

二：部署MySQL主从同步

模式：C/S
端口：3306

系统版本centos 7.4
数据库版本mysql 5.7
两台服务器分别安装好mysql5.7,启动服务

修改密码安全策略（这个主要测试主从暂时关闭）
编辑/etc/my.cnf文件

[root@localhost ~]# cat /etc/my.cnf   添加到配置文件
validate_password=off

重启数据库

[root@localhost ~]# systemctl restart mysqld

过滤数据库密码，因为这个数据库使用yum安装的，密码默认存放在/var/log/mysql.log

[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log

登录数据库，修改密码，修改完记得退出重新用新密码登录，每个数据库都做同样的操作。

mysql> set password for root@localhost=password( '123456' );
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit

准备工作完成。开始配置主从 M-S
在主上创建一个数据库，因为这个是主从同步，所以库，表的结构需要保持一致，需要提前创建好
创建一个库，一张用户表

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table test(id int, name varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

修改配置文件

[root@localhost ~]# vim  /etc/my.cnf   
把下面的内容添加到[mysqld]下
log-bin=mysql-bin-master     #启用二进制日志
server-id=1              #本机数据库ID 标示，id不能重复
binlog-do-db=test    #可以被从服务器复制的库, 二进制需要同步的数据库名
binlog-ignore-db=mysql       #不可以被从服务器复制的库，每个数据的mysql库存放的内容不一样，不能复制，否则会出问题

重启服务

[root@localhost ~]# systemctl restart mysqld   #修改完配置，重启报错

登录数据库授权

mysql> grant replication slave on *.* to slave@'10.10.100.%'  identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)

对整个网段的IP授权，整个网段都可以连接主库，用户slave,密码123456

查看主库的状态

mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000004 |      448 | test         | mysql            |                   |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

查看二进制日志
在这里插入图片描述

mysql> show binlog events\G

在这里插入图片描述

导出数据库的数据

[root@localhost ~]# mysqldump -root -p123456 test >test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

将导出的test.sql传送至从库服务器上

[root@localhost ~]# scp test.sql 10.10.100.28:/root
The authenticity of host '10.10.100.28 (10.10.100.28)' can't be established.
ECDSA key fingerprint is SHA256:edmhSyxI0hyv3i5Xp+blhDC5ZdTa/yD7GAxp6l1SHwE.
ECDSA key fingerprint is MD5:87:91:4f:a4:75:ae:e2:cf:3b:dc:28:ca:3d:03:c2:db.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.10.100.28' (ECDSA) to the list of known hosts..
root@10.10.100.28's password: 
test.sql                                                                       100%    0     0.0KB/s   00:00

从库操作
导入test.sql

mysql> create database test;
Query OK, 1 row affected (0.01 sec)
[root@localhost ~]# mysql -uroot -p123456 test <test.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

修改从服务器配置文件：
从服务器没必要开启bin-log日志

[root@localhost ~]# cat /etc/my.cnf
server-id=2    #从服务器ID号，不要和主ID相同 ，如果设置多个从服务器，每个从服务器必须有一个唯一的server-id值，必须与主服务器的以及其它从服务器的不相同。可以认为server-id值类似于IP地址：这些ID值能唯一识别复制服务器群集中的每个服务器实例。

#master-host=10.10.100.22 #指定主服务器IP地址
#master-user=slave  #指定定在主服务器上可以进行同步的用户名
#master-password=123456 #密码
#master-port=3306
#master-connect-retry=60  #断点重新连接时间

重启服务

[root@localhost ~]# systemctl restart mysqld

登录数据库

mysql> stop slave； #停止slave
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='10.10.100.22',master_user='slave',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;    #启动slave

mysql> show slave status\G  查看状态

在这里插入图片描述
查看从库状态，IO线程有问题，一直处以connecting连接的状态，说明从库无法与主库连接，
排查思路查看防火墙，selinux,主库服务是否有启动，等等，默认防火墙是开启的，可以把防火墙关闭，重启服务

这个是主库的防火墙
在这里插入图片描述

Slave_SQL_Running: No
可能是slave机器重起后，事务回滚造成的.(我的就是这个错误)
解决办法：

mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.100.22
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-master.000002
          Read_Master_Log_Pos: 757
               Relay_Log_File: localhost-relay-bin.000005
                Relay_Log_Pos: 327
        Relay_Master_Log_File: mysql-bin-master.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

在从库测试与主库的连接是否成功

[root@localhost ~]# mysql -uslave -p123456 -h10.10.100.22  #能够登录说明能够与主库正常连接
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

这个时候再看看slave的状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.100.22
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-master.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000008
                Relay_Log_Pos: 381
        Relay_Master_Log_File: mysql-bin-master.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table:

Slave_IO_Running ：一个负责与主机的io通信
Slave_SQL_Running：负责自己的slave mysql进程
两个为YES 就成功了！

到主库服务器上查看状态

mysql> show processlist \G
*************************** 1. row ***************************
     Id: 2
   User: slave
   Host: 10.10.100.28:33060
     db: NULL
Command: Binlog Dump
   Time: 712
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL
*************************** 2. row ***************************
     Id: 4
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
2 rows in set (0.00 sec)

插入数据测试同步：

mysql> insert into test values (1,'dz');
Query OK, 1 row affected (0.03 sec)

mysql> insert into test values (1,'ddz');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (44,'ddz');
Query OK, 1 row affected (0.00 sec)

在从库查看数据
在这里插入图片描述

排错：

如果遇到主从不同步，看一下主从bin-log的位置，然后再同步。
在这里插入图片描述
在主服务器上看二进制日志事件列表
mysql> show binlog events \G
从服务器执行MySQL命令下：
mysql> stop slave； #先停止slave服务
mysql> change master to master_log_file=‘mysql-bin-master.000001’,master_log_pos=1164;
#根据上面主服务器的show master status的结果，进行从服务器的二进制数据库记录回归，达到同步的效果
mysql>slave start; #启动从服务器同步服务
mysql> show slave status\G; #用show slave status\G;看一下从服务器的同步情况
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果都是yes，那代表已经在同步

重启从服务器，再查看状态：
停止从服务器slave stop;
开启从服务器slave start;
排错思路：
1、二进制日志没有开启
2、IPTABLES 没有放开端口
3、对应的主机 IP地址写错了
SQL线程出错
1、主从服务器数据库结构不统一
出错后，数据少，可以手动解决创建插入，再更新slave状态。
注：如果主上误删除了。那么从上也就误删除了。 #因此主上要定期做mysqldump备份。