mysql/mariadb 主从同步配置全解步骤教程

原创已于 2022-02-23 13:24:11 修改 · 1.9k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#mariadb #mysql #数据库

于 2022-02-23 13:20:48 首次发布

本文详细介绍了如何配置Mysql/MariaDB的主从同步，包括在主服务器上配置my.cnf，创建slave用户并授权，以及在从服务器上导入数据库并设置server_id。测试显示主数据库的修改能即时反映到从数据库。

部署运行你感兴趣的模型镜像

需求

将主服务器数据库同步到从服务器的数据库上。

步骤

主服务器

配置my.cnf
打开配置文件 /etc/my.cnf，加入如下内容

[mariadb]
log-bin
server_id=1
log-basename=master1
binlog-format=mixed
binlog_do_db=db00
binlog_do_db=db002
binlog_do_db=db003

一定要开启 log-bin ，server_id 必须设置。
如果不是全部开启则可以设置 binlog_do_db，需要的数据库就写上去。

重启mariadb

systemctl restart mariadb

创建slave登录用户，并赋予权限

create user slave_u1@'%' identified by 'slavepwd';
grant replication slave on *.* to slave_u1@'%';

登录mariadb，记录pos 和bin-log文件名，并锁定

MariaDB [(none)]> show master status;
+--------------------+----------+---------------------------+------------------+
| File               | Position | Binlog_Do_DB              | Binlog_Ignore_DB |
+--------------------+----------+---------------------------+------------------+
| master1-bin.000003 |   112452 | db00,db002,db003          |                  |
+--------------------+----------+---------------------------+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.004 sec)

解除锁定可以使用

MariaDB [(none)]> unlock tables;
Query OK, 0 rows affected (0.004 sec)

锁定的同时导出要同步的数据库

cd ~
mysqldump -u root -p --databases db00 db002 db003 | gzip > dump.master.gz

从服务器

配置my.cnf ，加入 server_id = 2

[mariadb]
#log-bin
server_id=2

注意，从服务器可以不用开启bin-log。将bin-log注释也可以

将主服务器导出的数据库同步到从服务器

scp -P 2277 root@10.2.44.151:/root/dump.master.gz dump.master.gz

将gz文件解压缩导入从mysql

gunzip dump.master.gz
mysql -uroot -p < dump.master

登录数据库，执行sql

change master to 
	master_host='10.2.44.151',
	master_user='slave_u1',
	master_password='slavepwd',
	master_port=3306,
	master_log_file='master1-bin.000003',
	master_log_pos=74668,
	master_connect_retry=10;

重启mariadb

systemctl restart mariadb

查看从状态

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.2.44.151
                   Master_User: slave_u1
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: master1-bin.000003
           Read_Master_Log_Pos: 131035
                Relay_Log_File: VM-20-5-centos-relay-bin.000003
                 Relay_Log_Pos: 56924
         Relay_Master_Log_File: master1-bin.000003
              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: 131035
               Relay_Log_Space: 57242
               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_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 189
1 row in set (0.000 sec)