准备两台服务器
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 -p123456MariaDB [(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)
