我们用两个节点来做实验:
node1节点:172.16.251.132
node2节点:172.15.252.32
node1节点配置:
# systemctl stop mariadb.service 先停止服务
# rm -rf /var/lib/mysql/* (这里是将mysql恢复初始状态,便于实验)
# vim /etc/my.cnf.d/server.cnf
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id=1
log-bin=master-log 二进制日志
relay_log=relay-log 中继日志
auto_increment_offset=1 定义节点使用奇数ID
auto_increment_increment=2 定义节点ID号一次加几
#systemctl start mariadb.service 配置完启动服务
node2节点配置:
# systemctl stop mariadb.service 先停止服务
# rm -rf /var/lib/mysql/* (这里是将mysql恢复初始状态,便于实验)
# vim /etc/my.cnf.d/server.cnf
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id=12
log-bin=master-log 二进制日志
relay_log=relay-log 中继日志
auto_increment_offset=2 定义节点使用偶数ID
auto_increment_increment=2 定义节点ID号一次加几
#systemctl start mariadb.service 配置完启动服务
接下来对数据库进行操作:
node1节点:
#mysql
>GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'172.16.252.%' IDENTIFIED BY 'replpass';
>FLUSH PRIVILEGES;
>SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 | 508 | | |
+-------------------+----------+--------------+------------------+
node2节点:
#mysql
>GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'172.16.251.%' IDENTIFIED BY 'replpass';
>FLUSH PRIVILEGES;
>SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 | 508 | | |
+-------------------+----------+--------------+------------------+
node1节点:
#mysql
>CHANGE MASTER TO MASTER_HOST='172.16.252.32',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_POST=3306,MASTER_LOG_FILE='master_log.000001',MASTER_LOG_POS=508;
>START SLAVE;
>SHOW SLAVE STATUS;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.252.32
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000001
Read_Master_Log_Pos: 508
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.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: 508
Relay_Log_Space: 818
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
node2节点:
#mysql
>CHANGE MASTER TO MASTER_HOST='172.16.251.132',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_POST=3306,MASTER_LOG_FILE='master_log.000001',MASTER_LOG_POS=508;
>START SLAVE;
>SHOW SLAVE STATUS;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.251.132
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000001
Read_Master_Log_Pos: 508
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.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: 508
Relay_Log_Space: 818
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
接下来可以测试了:
node1:
#mysql
>CREATE DATABASE mydb;
node2:
#mysql
>USE mydb;
>CREATE TABLE tbl1 (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,Name CHAR(30));
node1:
>USE mydb;
>DESC tbl1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | char(30) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
实验成功,两个数据库都是主,都有写和读的权限。
实验理论总结:
主主复制:
互为主从:两个节点各自都要开启binlog和relay log;
1、数据不一致;
2、自动增长id;
定义一个节点使用奇数id
auto_increment_offset=1 从几开始
auto_increment_increment=2 一次加几
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2
配置:
1、server_id必须要使用不同值;
2、均启用binlog和relay log;
3、存在自动增长id的表,为了使得id不相冲突,需要定义其自动增长方式;
服务启动后执行如下两步:
4、都授权有复制权限的用户账号;
5、各把对方指定为主节点;