mariadb配置主从复制

时间:2022-06-08 01:05:25

实验1: 主从复制( 一主 一从 )

实验前:关闭selinux和防火墙

主服务器:192.168.21.104

yum -y install mariadb-server //安装mariadb

[[email protected] ~]#vim /etc/my.cnf
[mysqld]
log_bin= mysql-bin //启用二进制日志
server_id=1 //为当前节点设置一个全局惟一的ID号

systemctl start mariadb //启动mariadb

创建有复制权限的用户账号
MariaDB [hellodb]> grant replication slave on . to ‘repluser‘@‘192.168.21.106‘ identified by ‘replpass‘;
MariaDB [(none)]> show master status;
------------------ ---------- -------------- ------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
------------------ ---------- -------------- ------------------
| mysql-bin.000001 | 512 | | |
------------------ ---------- -------------- ------------------
从服务器:192.168.21.106

yum -y install mariadb-server //安装mariadb

[[email protected] ~]#vim /etc/my.cnf
[mysqld]
server_id=2

systemctl start mariadb //启动mariadb

使用有复制权限的用户账号连接至主服务器,并启动复制线程
MariaDB [hellodb]> CHANGE MASTER TO MASTER_HOST=‘192.168.21.104‘,
MASTER_USER=‘repluser‘,
MASTER_PASSWORD=‘replpass‘,
MASTER_LOG_FILE=‘mysql-bin.000001‘,
MASTER_LOG_POS=512;

MariaDB [hellodb]> start slave;

查看slave状态
MariaDB [hellodb]> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.21.104
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 413
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 699
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes //IO-thread线程已启动
Slave_SQL_Running: Yes //SQL-thread线程已启动
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: 413
Relay_Log_Space: 995
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

测试:
主服务器插入一条记录并查看:
MariaDB [hellodb]> insert into teachers (Name, age) values(‘mage‘,20);

MariaDB [hellodb]> select * from teachers;
----- --------------- ----- --------
| TID | Name | Age | Gender |
----- --------------- ----- --------
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | mage | 20 | NULL |
----- --------------- ----- --------

从服务器上查看:
MariaDB [hellodb]> select * from teachers;
----- --------------- ----- --------
| TID | Name | Age | Gender |
----- --------------- ----- --------
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | mage | 20 | NULL |
----- --------------- ----- --------
实验2: 主从复制 ( 一主 一从 一从 )
======================================================
一主: 192.168.21.104
一从: 192.168.21.106
一从: 192.168.21.111

在上面实验基础之上,做以下配置:
从服务器 192.168.21.106
[[email protected] ~]#vim /etc/my.cnf
[mysqld]
server_id=2
log_bin
log_slave_updates

systemctl start mariadb //启动mariadb

MariaDB [hellodb]> grant replication slave on . to ‘centos‘@‘192.168.21.111‘ identified by ‘centos‘;

从服务器 192.168.21.111

yum -y install mariadb-server //安装mariadb

[[email protected] ~]#vim /etc/my.cnf
[mysqld]
server_id=3
read_only=on

systemctl start mariadb //启动mariadb

MariaDB [hellodb]> CHANGE MASTER TO MASTER_HOST=‘192.168.21.106‘,
MASTER_USER=‘centos‘,
MASTER_PASSWORD=‘centos‘,
MASTER_LOG_FILE=‘mariadb-bin.000001‘,
MASTER_LOG_POS=745;

MariaDB [hellodb]> start slave;

MariaDB [hellodb]> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.21.106
Master_User: centos
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 745
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000001
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: 745
Relay_Log_Space: 827
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: 2
1 row in set (0.00 sec)

测试:
主服务器 192.168.21.104上插入一条记录并查看
MariaDB [hellodb]> insert into teachers (Name, age) values(‘mage‘,20);
MariaDB [hellodb]> select * from teachers;
----- --------------- ----- --------
| TID | Name | Age | Gender |
----- --------------- ----- --------
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 6 | mage | 20 | NULL |
----- --------------- ----- --------

从服务器192.168.21.106上查看
MariaDB [hellodb]> select * from teachers;
----- --------------- ----- --------
| TID | Name | Age | Gender |
----- --------------- ----- --------
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 6 | mage | 20 | NULL |
----- --------------- ----- --------

从服务器192.168.21.111上查看
MariaDB [hellodb]> select * from teachers;
----- --------------- ----- --------
| TID | Name | Age | Gender |
----- --------------- ----- --------
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 6 | mage | 20 | NULL |
----- --------------- ----- --------