开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL主从复制及主主复制的实现 -> 正文阅读

[大数据]MySQL主从复制及主主复制的实现

MySQL主从复制的实现

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

服务器	IP	数据库版本	描述
Mysql-master	CentOS7.9-10.0.0.7	MariaDB-10.4.22	写节点(主)
Mysql-slave	CentOS7.9-10.0.0.17	MariaDB-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	数据库版本
master1	CentOS7.9-10.0.0.7	MariaDB-10.4.22
master2	CentOS7.9-10.0.0.17	MariaDB-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 |
+--------------------+