MySQL主从复制及主主复制的实现

MySQL主从复制的实现

环境准备:MySQL主从架构环境

服务器IP数据库版本描述
Mysql-masterCentOS7.9-10.0.0.7MariaDB-10.4.22写节点(主)
Mysql-slaveCentOS7.9-10.0.0.17MariaDB-10.4.22只读节点(从)

一、主节点

1.1修改master主节点的配置

[root@master ~]#vim /etc/my.cnf
[mysqld]
server_id=7
binlog_format=row
log-bin=/data/logbin/mysql-bin

1.2 建立二进制文件存放目录,修改所有者和所属组

[root@master ~]#mkdir /data/logbin/ -p
[root@master ~]#chown -R mysql.mysql /data/logbin/

1.3 重启数据库服务

[root@master ~]#systemctl restart mysqld

1.4 数据库完全备份

[root@master ~]#mysqldump -A -F -uroot -pMySQL@2022. --master-data=1 --single-transaction > /data/all.sql

1.5 创建复制用户并授权

MariaDB [mysql]> grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';

1.6 将备份复制到从节点

[root@master ~]#scp /data/all.sql 10.0.0.17:/data

二、从节点

2.1 修改slave从节点的配置

[root@slave ~]#vim /etc/my.cnf
[mysqld]
server_id=17
binlog_format=row
read_only=on
log-bin=/data/logbin/mysql-bin

2.2 建立二进制文件存放目录,修改所有者和所属组

[root@slave ~]#mkdir /data/logbin/ -p
[root@slave ~]#chown -R mysql.mysql /data/logbin/

2.3 重启数据库服务

[root@slave ~]#systemctl restart mysqld

2.4 从节点修改备份文件

[root@slave ~]#vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.7',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=328;

2.5关闭二进制日志

MariaDB [mysql]> set sql_log_bin=off;

2.6 导入备份数据

MariaDB [mysql]> source /data/all.sql

2.7查看从节点备份状态

MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State:
                   Master_Host: 10.0.0.7
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 328
                Relay_Log_File: centos7-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: mysql-bin.000002
              Slave_IO_Running: No
             Slave_SQL_Running: No
               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: 328
               Relay_Log_Space: 256
               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: NULL
 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: 0
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State:
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0

2.8开启从节点slave进程,从节点开始复制

MariaDB [mysql]> start slave;

2.9 再次查看从节点备份状态

MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.0.0.7
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 714
                Relay_Log_File: centos7-relay-bin.000002
                 Relay_Log_Pos: 941
         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: 714
               Relay_Log_Space: 1252
               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: 7
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 2
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0

3.0 查看从节点的账号信息及数据库

MariaDB [mysql]> select user,host from mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| repluser    | 10.0.0.%  |
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+
MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

3.1从节点开启二进制日志

MariaDB [mysql]> set sql_log_bin=on;

MySQL主主复制的实现

环境准备:MySQL主主复制架构环境

服务器IP数据库版本
master1CentOS7.9-10.0.0.7MariaDB-10.4.22
master2CentOS7.9-10.0.0.17MariaDB-10.4.22

一、master1节点

1.1修改master1的配置

[root@master1 ~]#cat /etc/my.cnf
[mysqld]
server_id=7
binlog_format=row
log-bin=/data/logbin/mysql-bin

1.2 建立二进制文件存放目录,修改所有者和所属组

[root@master1 ~]#mkdir /data/logbin/ -p
[root@master1 ~]#chown -R mysql.mysql /data/logbin/

1.3 重启数据库服务

[root@master1 ~]#systemctl restart mysqld

1.4 查看二进制位置

MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+

1.5 创建复制用户并授权

MariaDB [mysql]> grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';
MariaDB [mysql]> FLUSH PRIVILEGES;

1.6 完成备份后再次查看二进制位置

MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       656 |
+------------------+-----------+

1.7 确定同步master2的数据

MariaDB [mysql]> CHANGE MASTER TO
    -> MASTER_HOST='10.0.0.17',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='123456',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=328;

1.8 开启master1进程,开始复制

MariaDB [mysql]> start slave;

1.9 查看master1备份状态

MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.0.0.17
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 328
                Relay_Log_File: master1-relay-bin.000002
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql-bin.000001
              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: 328
               Relay_Log_Space: 866
               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: 17
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0

2.0 创建数据库db1

MariaDB [mysql]> create database db1;

2.1 查看master2 创建的db2数据

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

二、master2节点

2.1 修改master2的配置

[root@master2 ~]#cat /etc/my.cnf
[mysqld]
server_id=17
binlog_format=row
log-bin=/data/logbin/mysql-bin

2.2 建立二进制文件存放目录,修改所有者和所属组

[root@master2 ~]#mkdir /data/logbin/ -p
[root@master2 ~]#chown -R mysql.mysql /data/logbin/

2.3 重启数据库服务

[root@master2 ~]#systemctl restart mysqld

2.4 查看二进制位置

MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+

2.5 确定同步master1的数据

MariaDB [mysql]> CHANGE MASTER TO
    -> MASTER_HOST='10.0.0.7',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='123456',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=328;

2.6查看master2备份状态

MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State:
                   Master_Host: 10.0.0.7
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 328
                Relay_Log_File: master2-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: No
             Slave_SQL_Running: No
               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: 328
               Relay_Log_Space: 256
               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: NULL
 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: 0
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State:
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0

2.7开启master2进程,开始复制

MariaDB [mysql]> start slave;

2.8 再次查看master2备份状态

MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.0.0.7
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 656
                Relay_Log_File: master2-relay-bin.000002
                 Relay_Log_Pos: 883
         Relay_Master_Log_File: mysql-bin.000001
              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: 656
               Relay_Log_Space: 1194
               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: 7
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 2
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0

2.9再次查看二进制位置

MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+

3.0 查看master2的账号信息

MariaDB [mysql]> select user,host from mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| repluser    | 10.0.0.%  |
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+

3.1 查看master1 创建的db1数据

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

3.2 创建db2数据库

MariaDB [mysql]>  create database db2;
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

一直在努力学习的菜鸟

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值