MySQL_主从复制_一主一从_互为主从

ch#

已于 2023-04-14 22:19:22 修改

阅读量187

点赞数

文章标签： mysql 服务器数据库

于 2023-04-12 21:05:34 首次发布

本文链接：https://blog.csdn.net/chenhenryjin/article/details/130113879

版权

主从复制以及主从复制的作用：

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力

Mysql的复制功能主要有3个步骤：

(1) 主服务器（master）将改变记录到二进制日志(binarylog)中（这些记录叫做二进制日志事件，binary log events）

（在每个事务更新数据完成之前，master在二进制日志记录这些改变。写入二进制日志完成后，master通知存储引擎提交事务。）

(2) 从服务器（slave）将主服务器master的binary log 中的events拷贝到它的中继日志 (relay log)

（Slave将master的binary log复制到其中继日志。首先slave开始一个I/O线程，I/O线程在master上打开一个普通的连接，然后开始binlog dump process。binlog dump process从master的二进制日志中读取事件，如果已经跟上 master，它会睡眠并等待master产生新的事件，I/O线程将这些事件写入中继日志。）

(3) slave重做中继日志中的事件，将改变反映它自己的数据

（Sql slave thread（sql从线程）处理该过程的最后一步，sql线程从中继日志读取事件，并重放其中的事件而更新slave数据，使其与master中的数据一致，只要该线程与I/O线程保持一致，中继日志通常会位于os缓存中，所以中继日志的开销很小。）

I/O线程：将master数据库二进制日志拉到slave数据库上，并将二进制日志写到中继日志，写完之后，他会睡眠并等待master数据库二进制日志更新，一旦更新，就会写入slave数据库的中继日志中

SQL线程：读取中继日志的事件，并在数据库中执行，写入到内存中，使slave 数据库的数据与master数据库中的数据一致

主从复制_一主一从

环境准备：

主机名	IP
1_master	192.168.126.6
1_slave	192.168.126.7

centos系统服务器2台、一台做Mysql主服务器(192.168.126.6)，一台用于做Mysql从服务器(192.168.126.7)，都在同一个网段中，配置好yum源、防火墙关闭、各节点时钟服务同步、各节点之间可以通过主机名互相通信。（克隆后更改uuid，ip，mac地址）

cat /proc/sys/kernel/random/uuid
# 随机生成uuid，把新ip和生成uuid写入/etc/sysconfig/network-scripts/ifcfg-ens33
# mac地址在Vmware上修改
systemctl  restart  network

两台都使用yum安装MySQL，并启动

在两台主机的/etc/hosts都添加以下内容

192.168.126.6   1_master
192.168.126.7   1_slave

并分别修改主机名

 hostnamectl set-hostname 1_master
 hostnamectl set-hostname 1_slave

分别查看MySQL的server_uuid，我是先克隆虚拟再安装MySQL的，所以server_uuid并不一样。如果一样，找到/var/lib/mysql文件夹下的auto.cnf文件，修改里面的uuid值，保证各个db的uuid不一样，重启db即可。

mysql> show variables like '%server_uuid%'
    -> ;
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 45411ca5-d911-11ed-9fec-005056255767 |
+---------------+--------------------------------------+
1 row in set (0.01 sec)

配置主服务器的/etc/my.cnf文件

vim /etc/my.cnf
server-id=28  
# 配置server-id，让主服务器有唯一ID号（让从服务器知道
# 他的主服务器是谁）,建议使用ip最后3位，这里随便打的
log-bin=mysql-bin 
#打开Mysql日志，日志格式为二进制
skip-name-resolve 
#关闭名称解析，（非必须）

systemctl restart mysqld

在Master的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。（给从服务器授权，让它能从主服务器拷贝二进制日志）

mysql> create user '1_slave'@'%' identified with mysql_native_password by 'Ch#123456';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO '1_slave'@'%';
Query OK, 0 rows affected (0.00 sec)

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

查看主服务器状态（show master status\G;），并记住两个值

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 1320
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

配置slave从服务器/etc/my.cnf文件

vim /etc/my.cnf
server-id=29 
# 配置server-id，让从服务器有唯一ID号
relay_log = mysql-relay 
# 打开Mysql日志，日志格式为二进制
read_only = 1 
# 设置只读权限
log_bin = mysql-bin 
# 开启从服务器二进制日志
log_slave_updates = 1 
# 使得更新的数据写进二进制日志中

systemctl restart mysqld

启动从服务器复制线程

让slave连接master，并开始重做master二进制日志中的事件。

mysql> CHANGE MASTER TO master_host = '192.168.126.6',
    -> master_user = '1_slave',
    -> master_password = 'Ch#123456',
    -> master_log_file = 'mysql-bin.000005',
    -> master_log_pos = 1320;
Query OK, 0 rows affected, 8 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

查看从服务器状态

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.126.6
                  Master_User: 1_slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 1320
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
# 下面还有很对键值对，但只要看到这个两个连续的‘yes’就代表成功

这一次比较顺利，没什么出错，但是MySQL密码policy不是很好改，建议不要倔，老老实实弄个复杂的密码，免得像我一样浪费时间。

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

并且不能跳过密码验证（skip-grant-tables）

如果跳过密码验证，MySQL服务器将不再需要提供用户名和密码，这将导致Telnet客户端无法提供凭据来验证身份，从而无法建立TCP/IP连接。因此，跳过密码验证将导致无法使用Telnet通信端口。

需要注意的是，使用Telnet登录MySQL服务器并不是最安全的方式，因为Telnet传输的数据是明文的，容易被黑客窃取。建议使用SSH等更安全的远程登录协议来连接MySQL服务器。

最后结果就是1_master的增删改操作都能对1_slave有效，反之不行。

主从复制_互为主从

同上：server_id必须要使用不同值；

异上：read only = 0(因为互为主从，所以必须是可写的)；存在自动增长id的表，为了使得id不相冲突，需要定义其自动增长方式；均启用binlog和relay log

主机名	IP
2_Tom	192.168.126.6
2_Jerry	192.168.126.7

2_Tom上/etc/my.cnf

server-id = 28
log_bin = mysql_bin
relay_log = relay-log
skip-name-resolve = on
log_slave_updates = 1
auto_increment_increment=2
auto_increment_offset=1

2_Jerry上/etc/my.cnf

server-id = 29
relay_log = relay-log
log_bin = mysql-log
skip-name-resolve = on
log_slave_updates = 1
auto_increment_increment=2
auto_increment_offset=2

auto_increment_increment表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535
auto_increment_offset表示自增长字段从那个数开始，取值范围是1 .. 65535

在主主同步配置时，需要将两台服务器的auto_increment_increment增长量都配置为2，而要把auto_increment_offset分别配置为1和2，这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。

都重启

systemctl restart mysqld

分别在两台数据库服务器上创建slave账号

2_Tom上
create user '2_Jerry'@'%' identified with mysql_native_password by
'Ch#123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 2_Jerry@'%';
2_Jerry上
create user '2_Tom'@'%' identified with mysql_native_password by
'Ch#123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 2_Tom@'%';

分别在两台数据库服务器上启动从服务器复制线程

（分别在两台主机查看master信息，再配置另一台主机的slave信息）

2_Tom上查看

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000006
         Position: 157
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

2_Jerry上查看

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-log.000001
         Position: 2612
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

2_Tom上配置

mysql> CHANGE MASTER TO MASTER_HOST='192.168.126.7',
    -> MASTER_USER='2_Jerry',
    -> MASTER_PASSWORD='Ch#123456',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=2612;

2_Jerry上配置

mysql> CHANGE MASTER TO master_host = '192.168.126.6',
    -> master_user = '2_Tom',
    -> master_password = 'Ch#123456',
    -> master_log_file = 'mysql-bin.000006',
    -> master_log_pos = 157;


start slave;

最后两台主机输入SHOW SLAVE STATUS\G

输出内容中都有如下键值表示成功

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

如果没有该键值对获知在启动slave（start slave;）有如下报错

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

就使用下面命令

stop slave;
reset slave;
# 这时重新设置slave进程，但好像不设置也行？不知道为什么，反正我没设置都成功过了
start slave;

RESET SLAVE：删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件

reset slave将使slave忘记主从复制关系的位置信息。该语句将被用于干净的启动, 它删除master.info文件和relay-log.info 文件以及所有的relay log 文件并重新启用一个新的relaylog文件。

RESET MASTER:删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。用于第一次进行搭建主从库时，进行主库binlog初始化工作；

reset slave 将使slave 忘记主从复制关系的位置信息。该语句将被用于干净的启动, 它删除master.info文件和relay-log.info 文件以及所有的relay log 文件并重新启用一个新的relaylog文件。

使用后日志文件会从序列000001开始，(relay-bin或者mysql-bin），且两个命令都必须在从机stop slave; 后进行使用。

所谓master和slave都是相对的关系。且关系非常随便。一台机可以同时是两者，且一台master可以有多台slaves，但一个slave不能有多台masters，因为在一从多主的情况下，多个主库会同时向同一个从库写入数据，这样就会导致数据冲突和数据不一致的问题。因此，如果需要实现多主复制，需要使用多主多从的复制模式，即多个主库和多个从库相互复制和同步数据。

实际情况，可以考虑采用更加灵活的读写分离方案，根据具体情况将读请求和写请求分别路由到不同的数据库实例上，以提高数据库的并发性和可用性。这是主从架构的初衷。