mysql 主从同步

=

主从同步配置过程：

1，将一个MySQL实例配置为主服务器（Master），将其他MySQL实例配置为从服务器（Slave）

2，在主服务器上，需要开启二进制日志（Binary Log）。二进制日志记录了主服务器上所有的数据变更操作，如INSERT、UPDATE、DELETE等。

3，为从服务器创建一个专用的账号，并授予其REPLICATION SLAVE权限。这样，从服务器才能连接到主服务器并读取二进制日志。

4，从服务器连接到主服务器后，会读取主服务器的二进制日志，并将其中的数据变更操作写入到自己的中继日志（Relay Log）中。接着，从服务器的SQL线程会读取中继日志，并执行其中的数据更新操作，以保持与主服务器的数据一致。

 

实验环境：

Server1 156

Server2  150

mysql  Ver 8.0.21 for Linux on x86_64

 

 

server1：

 

[root@server1 ~]# cat /etc/my.cnf.d/server1.cnf

[mysqld]

log_bin=source-bin

server_id=1

 

[root@server1 ~]# systemctl restart mysqld.service

 

mysql> CREATE USER 'replica-user'@'192.168.153.%' IDENTIFIED BY 'redhat';

Query OK, 0 rows affected (0.00 sec)

 

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica-user'@'192.168.153.%';

Query OK, 0 rows affected (0.01 sec)

 

[root@server1 ~]# mysqldump -u root -predhat test > backup.sql   \\备份test里面的表格

 

mysql > drop test;

Query OK, 1 row affected (0.00 sec)

 

 

mysql> show master status;

+-------------------+----------+--------------+------------------+-------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| source-bin.000003 |      899 |              |                  |                   |

+-------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

 

 

Server2：

 

[root@server2 ~]# cat /etc/my.cnf.d/server2.cnf

[mysqld]

server_id = 2

relay_log = relipca-relay

relay_log_index = relipca-relay-index

 

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> reset slave all;

Query OK, 0 rows affected (0.01 sec)

 

mysql> CHANGE MASTER TO 

    ->     MASTER_HOST='192.168.153.156', 

    ->     MASTER_USER='replica-user', 

    ->     MASTER_PASSWORD='redhat', 

    ->     MASTER_LOG_FILE='source-bin.000003', 

    ->     MASTER_LOG_POS=899, 

    ->     GET_MASTER_PUBLIC_KEY=1;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

 

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.153.156

                  Master_User: replica-user

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: source-bin.000003

          Read_Master_Log_Pos: 899

               Relay_Log_File: relipca-relay.000004

                Relay_Log_Pos: 325

        Relay_Master_Log_File: source-bin.000003

             Slave_IO_Running: Yes    \\表示

            Slave_SQL_Running: Yes   \\成功

 

 

测试：

Server1：

 

mysql> create database testa;

Query OK, 1 row affected (0.00 sec)

 

mysql> use testa;

Database changed

mysql> source /root/backup.sql;

mysql> show tables;

+-----------------+

| Tables_in_testa |

+-----------------+

| a               |

+-----------------+

1 row in set (0.00 sec)

 

mysql> select * from a;

+------+------+

| id   | name |

+------+------+

|    1 | a    |

|    2 | b    |

|    3 | c    |

+------+------+

3 rows in set (0.00 sec)

 

 

Server2:

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

| testa              |

+--------------------+

5 rows in set (0.00 sec)

 

mysql> use testa;

mysql> select * from a;

+------+------+

| id   | name |

+------+------+

|    1 | a    |

|    2 | b    |

|    3 | c    |

+------+------+

3 rows in set (0.00 sec)s

 

 

也可以配置一个双主同步

 

 

 

Server2:

 

[root@server2 ~]# cat /etc/my.cnf.d/server2.cnf

[mysqld]

server_id = 2

log-bin=source-bin

 

[root@server2 ~]# systemctl restart mysqld.service

 

mysql> CREATE USER 'replica-user2'@'192.168.153.%' IDENTIFIED BY 'redhat';

Query OK, 0 rows affected (0.00 sec)

 

mysql> grant  REPLICATION SLAVE ON *.* to 'replica-user2'@'192.168.153.%';

Query OK, 0 rows affected (0.00 sec)

 

mysql> show master status;

+-------------------+----------+--------------+------------------+-------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| source-bin.000001 |      900 |              |                  |                   |

+-------------------+----------+--------------+------------------+-------------------+

 

 

Server1:

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> CHANGE MASTER TO 

    MASTER_HOST='192.168.153.150', 

    MASTER_USER='replica-user', 

    MASTER_PASSWORD='redhat', 

    MASTER_LOG_FILE='source-bin.000001', 

    MASTER_LOG_POS=900, 

GET_MASTER_PUBLIC_KEY=1;

 

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.153.150

                  Master_User: replica-user

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: source-bin.000001

          Read_Master_Log_Pos: 900

               Relay_Log_File: server1-relay-bin.000002

                Relay_Log_Pos: 325

        Relay_Master_Log_File: source-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes