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

需求

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

步骤

• 主服务器

1. 配置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，需要的数据库就写上去。

2. 重启mariadb

systemctl restart mariadb

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

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

4. 登录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)

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

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

• 从服务器

6. 配置my.cnf ，加入 server_id = 2

[mariadb]
#log-bin
server_id=2

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

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

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

8. 将gz文件解压缩导入 从mysql

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

9. 登录数据库，执行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;

10. 重启mariadb

systemctl restart mariadb

11. 查看从状态

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)

测试

登录主数据库，修改一条记录，再登录从数据库，查询，马上更新。

文档

官方文档：https://mariadb.com/kb/en/setting-up-replication/