MySQL主主同步指北

最新推荐文章于 2024-01-24 15:31:38 发布

MELF晓宇

最新推荐文章于 2024-01-24 15:31:38 发布

阅读量314

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/qq_29537269/article/details/124944438

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

修改IP

主数据库

查看ip

ip address

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:01:89:03 brd ff:ff:ff:ff:ff:ff
inet 192.168.11.103/24 brd 192.168.11.255 scope global dynamic ens33
valid_lft 86321sec preferred_lft 86321sec
inet6 fe80::3348:812f:b6ff:b965/64 scope link
valid_lft forever preferred_lft forever

修改静态IP

vim /etc/sysconfig/network-scripts/ifcfg-ens33

TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=static
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=yes
IPV6_AUTOCONF=yes
IPV6_DEFROUTE=yes
IPV6_FAILURE_FATAL=no
IPV6_ADDR_GEN_MODE=stable-privacy
NAME=ens33
DEVICE=ens33
ONBOOT=yes
IPADDR=192.168.11.103
PREFIX=24
GATEWAY=192.168.11.1
DNS1=114.114.114.114

在 vim 普通模式下键入ggdG即可删除其中全部内容。
说明：
gg：光标跳转到该文件的行首；
dG：删除光标行及其以下行的全部内容。（注：d为删除，G为光标跳转到末尾行）

重启网络服务

service network restart

从数据库

查看ip

ip address

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:01:89:03 brd ff:ff:ff:ff:ff:ff
inet 192.168.11.103/24 brd 192.168.11.255 scope global dynamic ens33
valid_lft 86321sec preferred_lft 86321sec
inet6 fe80::3348:812f:b6ff:b965/64 scope link
valid_lft forever preferred_lft forever

修改静态IP

vim /etc/sysconfig/network-scripts/ifcfg-ens33

TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=static
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=yes
IPV6_AUTOCONF=yes
IPV6_DEFROUTE=yes
IPV6_FAILURE_FATAL=no
IPV6_ADDR_GEN_MODE=stable-privacy
NAME=ens33
DEVICE=ens33
ONBOOT=yes
IPADDR=192.168.11.103
PREFIX=24
GATEWAY=192.168.11.1
DNS1=114.114.114.114

在 vim 普通模式下键入ggdG即可删除其中全部内容。
说明：
gg：光标跳转到该文件的行首；
dG：删除光标行及其以下行的全部内容。（注：d为删除，G为光标跳转到末尾行）

重启网络服务

service network restart

安装MySQL

下载并安装 MySQL 官方的 Yum Repository

wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

-bash: wget: command not found
yum install wget

使用 Yum 进行安装

yum install mysql57-community-release-el7-10.noarch.rpm

安装 MySQL 服务器

yum install mysql-community-server

Public key for mysql-community-common-5.7.38-1.el7.x86_64.rpm is not installed

Failing package is: mysql-community-common-5.7.38-1.el7.x86_64
GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

启动 MySQL

systemctl start mysqld

设置MySQL开机自启

systemctl enable mysqld

查看MySQL密码

grep 'password' /var/log/mysqld.log

2022-05-24T01:16:49.408008Z 1 [Note] A temporary password is generated for root@localhost: *vi50&.?w5aR

查看防火墙状态

firewall-cmd --state

开启3306端口

firewall-cmd --zone=public --add-port=3306/tcp --permanent

重启防火墙

systemctl restart firewalld

查询3306端口是否开放

firewall-cmd --zone=public --query-port=3306/tcp

进入数据库

mysql -u root -p

输入密码

*vi50&.?w5aR

修改密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root123@';

设置允许远程访问

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Root123@' WITH GRANT OPTION;

配置从库同步主库

主数据库执行

vim /etc/my.cnf

添加如下内容

[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1

gtid_mode=ON
enforce_gtid_consistency=1   # 强制执行GTID一致性。

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

创建日志文件夹

mkdir /var/log/mysql
chown mysql.mysql /var/log/mysql

重启mysql

systemctl restart mysqld

登陆数据库

mysql -u root -p

确保在主服务器上 skip_networking 选项处于 OFF 关闭状态, 这是默认值。
如果是启用的，则从站无法与主站通信，并且复制失败。

show variables like '%skip_networking%';

±----------------±------+
| Variable_name | Value |
±----------------±------+
| skip_networking | OFF |
±----------------±------+
1 row in set (0.00 sec)

创建用户并授权

CREATE USER 'repl'@'%' IDENTIFIED BY 'Root123@';

用户名：repl
主机：使用通配符%，允许任意远程主机登陆
密码：123456789

对repl用户进行授权

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
flush privileges;

在从库测试用户有效性
 mysql -urepl -p'Root123@' -h192.168.11.103

锁表，禁止写入，当前窗口不能退出，这时候开启另一个终端继续操作

flush table with read lock;

查看master状态，记录二进制文件名(mysql-bin.000001)和位置(747 )：

SHOW MASTER STATUS;

±-----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±------------------+
| mysql-bin.000001 | 747 | | | |
±-----------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)

将当前数据导出，如果两数据库不一样，手动调整

mysqldump -u root -p --all-databases > /root/alldb.sql

解锁

unlock table;

SHOW MASTER STATUS;

查看binlog日志位置，如果没变证明锁定成功。从库将从这个binlog日志开始恢复

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       |       |     |
+------------------+----------+--------------+------------------+

从数据库执行

导入数据

mysql -uroot -p < alldb.sql

修改配置文件

[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=2

gtid_mode=ON
enforce_gtid_consistency=1   # 强制执行GTID一致性。

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

重启mysql

systemctl restart mysqld

配置同步

CHANGE MASTER TO MASTER_HOST='192.168.11.103', MASTER_USER='repl', MASTER_PASSWORD='Root123@', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;

开启同步

start slave;

查看slave状态

show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.11.103
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 531
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 2f7f85a5-daff-11ec-a468-000c29018903
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
都是yes即可

配置主库同步从库

从数据库执行

创建用户并授权

CREATE USER 'repl'@'%' IDENTIFIED BY 'Root123@';

用户名：repl
主机：使用通配符%，允许任意远程主机登陆
密码：123456789

对repl用户进行授权

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
flush privileges;

查看master状态，记录二进制文件名(mysql-bin.000001)和位置(747 )：

SHOW MASTER STATUS;

±-----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±------------------+
| mysql-bin.000002 | 747 | | | |
±-----------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)

主数据库执行

配置同步

CHANGE MASTER TO MASTER_HOST='192.168.11.104', MASTER_USER='repl', MASTER_PASSWORD='Root123@', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=747;

开启同步

start slave;

查看slave状态

show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.11.104
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 747
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 747
              Relay_Log_Space: 531
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 76788c3e-db03-11ec-a59c-000c29976660
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.01 sec)

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
都是yes即可

参考文档：

https://www.cnblogs.com/rxysg/p/15687766.html

疑难杂症解决

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

造成此结果的原因是服务器uuid重复，需要修改服务器的uuid

select uuid();

+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 76788c3e-db03-11ec-a59c-000c29976660 |
+--------------------------------------+
1 row in set (0.00 sec)

查找 uuid 配置文件

find / -name 'auto.cnf'

/var/lib/mysql/auto.cnf

编辑 uuid 配置文件

vim /var/lib/mysql/auto.cnf

写入如下内容

[auto]
# 按照这个16进制格式，修改server-uuid，重启mysql即可(此id为mysql中查询出的ID)
server-uuid=76788c3e-db03-11ec-a59c-000c29976660

参考文档：

https://blog.csdn.net/co1590/article/details/120190659

Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

此问题为日志读取错误

停止 slave

stop slave;

SHOW MASTER STATUS;

刷新日志

flush logs;
SHOW MASTER STATUS;

±-----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±------------------+
| mysql-bin.000002 | 154 | | | |
±-----------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)

马上到 slave 执行

CHANGE MASTER TO MASTER_HOST='192.168.11.103', MASTER_USER='repl', MASTER_PASSWORD='Root123@', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;

start slave;

show slave status \G;

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.11.103
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 531
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 2f7f85a5-daff-11ec-a468-000c29018903
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

参考文档：

https://blog.csdn.net/u011488009/article/details/104608760

Mysql配置文件详解

添加如下内容

[mysqld]
# 定义mysql主程序目录
# basedir = /usr/local/mysql

#定义数据目录
datadir = /var/lib/mysql

#定义sock文件
socket = /var/lib/mysql/mysql.sock

# pid 文件
pid-file=/var/run/mysqld/mysqld.pid

# 指定客户端字符集
character-set-server = utf8mb4

# 启动mysql二进制日志，如果没有配置这个将无法远程链接
log-bin=/var/log/mysql/mysql-bin

# 可以为任意自然数，必须保证两台mysql主机不重复，如果省略server-id（或将其显式设置为默认值0），则主服务器拒绝来自从服务器的任何连接。
server-id = 1

gtid_mode=ON

# 强制执行GTID一致性。
enforce_gtid_consistency=1   

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log