mariadb实现主从同步

有谁看见我的剑了？

于 2024-01-05 15:08:50 发布

阅读量1.4k

点赞数 20

分类专栏： mysql 文章标签： mariadb android adb

本文链接：https://blog.csdn.net/qq_50247813/article/details/135409572

版权

mysql 专栏收录该内容

27 篇文章 1 订阅

订阅专栏

准备两台服务器
Mariadb-Master：192.168.44.150
Mariadb-Backup：192.168.44.148

安装mariadb：

https://blog.csdn.net/qq_50247813/article/details/135402502?spm=1001.2014.3001.5502

组从复制原理如下
在这里插入图片描述

修改主数据库配置如下

vi /etc/my.cnf.d/server.cnf

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
server_id = 150
log-bin= mysql-bin
lower_case_table_names=1
binlog-format=ROW
log-slave-updates=True
sync-master-info=1
slave-parallel-threads=3
binlog-checksum=CRC32 
master-verify-checksum=1
slave-sql-verify-checksum=1

character-set-server=utf8 设置数据库默认字符集
collation-server=utf8_general_ci 设置校验规则
server_id = 150 主从id必须唯一，推荐使用ip位
log-bin= mysql-bin 二进制日志，后面指定存放位置。如果只是指定名字，默认存放在/var/lib/mysql下
lower_case_table_names=1 库表不区分大小写
binlog-format=ROW 二进制日志文件格式
log-slave-updates=True slave更新是否记入日志
sync-master-info=1 值为1确保信息不会丢失
slave-parallel-threads=3 同时启动多少个复制线程，最多与要复制的数据库数量相等即可
binlog-checksum=CRC32 效验码
master-verify-checksum=1 启动主服务器效验
slave-sql-verify-checksum=1 启动从服务器效验

修改从数据库配置

 vi /etc/my.cnf.d/server.cnf

	[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
server_id=148
relay_log = relay-bin
lower_case_table_names=1

server_id=148 主从id必须唯一
relay_log = relay-bin 中继日志, 后面指定存放位置。如果只是指定名字，默认存放在/var/lib/mysql下

重启主从数据库，配置生效
```
 systemctl restart mariadb
```

master节点配置

注：如果使用普通用户需要授权binlog权限

 grant replication slave, replication client on *.* to 'user'@'%' identified by 'passwd';

还有注意将slave加入到master的远程访问列表中

 GRANT ALL PRIVILEGES ON *.* to  `root`@`192.168.44.*` identified by '123456';
 flush privileges;

 mariadb -uroot -p123456

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |     1218 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> select binlog_gtid_pos('mysql-bin.000002', 1218 );
+--------------------------------------------+
| binlog_gtid_pos('mysql-bin.000002', 1218 ) |
+--------------------------------------------+
| 0-150-6                                    |
+--------------------------------------------+
1 row in set (0.000 sec)

从节点配置

MariaDB [(none)]> set global gtid_slave_pos='0-150-6';
	Query OK, 0 rows affected (0.004 sec)
	
	MariaDB [(none)]>  change master to master_host='192.168.44.150',master_user='root',master_password='123456',master_use_gtid=slave_pos;
	Query OK, 0 rows affected (0.008 sec)
	
	MariaDB [(none)]> start slave;
	Query OK, 0 rows affected (0.005 sec)
	
	MariaDB [(none)]>

验证salve状态

		MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.44.150
                   Master_User: root
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 1722
                Relay_Log_File: relay-bin.000002
                 Relay_Log_Pos: 1174
         Relay_Master_Log_File: mysql-bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
          Replicate_Rewrite_DB: 
               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: 1722
               Relay_Log_Space: 1477
               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: 150
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: Slave_Pos
                   Gtid_IO_Pos: 0-150-9
       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: 2
Slave_Non_Transactional_Groups: 1
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)