MySQL主主

MySQL主主

准备环境

• 主机

主机名	IP地址1（外网）	IP地址2（内网）	系统	其他
mysql-master-01	192.168.72.130	202.207.240.130	CentOS7.5.1804	MEM:2G;CPU:2P/2C;DISK:15G
mysql-master-02	192.168.72.131	202.207.240.131	CentOS7.5.1804	MEM:2G;CPU:2P/2C;DISK:15G

• 配置域名解析
• 在mysql-master-01和mysql-master-02上部署好MySQL服务

mysql-master-01主机配置

• 修改配置文件 /etc/my.cnf

[mysqld]
basedir = /application/mysql
datadir = /application/mysql/data
socket = /tmp/mysql.sock
server_id = 1
log-bin = mysql-bin
binlog-ignore-db = mysql,information_schema
binlog-do-db = master_master
auto_increment_offset = 1
auto_increment_increment = 2
log_bin_trust_function_creators = 1
port = 3306
log_error = /application/mysql/data/error_mysql.err

[mysql]
socket = /tmp/mysql.sock
prompt = wjz [\d]>

选项	功能
server_id = 1	mysql-master-01节点数据库的ID
log-bin = mysql-bin	开启log-bin日志
binlog-ignore-db = mysql,information_schema	忽略主主复制的数据库有哪些
binlog-do-db = master_master	指定主主复制的数据库有哪些
auto_increment_offset = 1	该参数一般用在主主同步中，用来错开自增值，防止键值冲突
auto_increment_increment = 2	这个参数一般用在主主同步中，用来错开自增值，防止键值冲突
log_bin_trust_function_creators = 1	将函数复制到slaver【将SQL从mstaer-01复制到mater-02】

• 重启数据库服务

• 登录mysql-master-01节点数据库，授权

  注意：IP地址是需要登录到本机的IP地址，而不是本机IP地址

grant replication slave,replication client on *.* to 'repl'@'192.168.72.131' identified by '123456';
flush privileges;
show master status;

mysql-master-02主机配置

• 修改配置文件 /etc/my.cnf

[mysqld]
basedir = /application/mysql
datadir = /application/mysql/data
socket = /tmp/mysql.sock
port = 3306
log_error = /application/mysql/data/error_mysql.err
server_id = 2
log-bin = mysql-bin
binlog-ignore-db = mysql,information_schema
binlog-do-db = master_master
auto_increment_offset = 2
auto_increment_increment = 2
log_bin_trust_function_creators = 1

[mysql]
socket = /tmp/mysql.sock
prompt = wjz [\d]>

• 重启数据库服务
• 登录mysql-master-02节点数据库，授权

grant replication slave,replication client on *.* to 'repl'@'192.168.72.130' identified by '123456';
flush privileges;
show master status;

配置mysql-master-02同步mysql-master-01

• 登录mysql-master-02数据库，做mysql-master-02同步mysql-master-01

stop slave;

change master to master_host='192.168.72.130',master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1110;

start slave;

show slave status\G;

配置mysql-master-01同步mysql-master-02

• 登录mysql-master-01数据库，做mysql-master-01同步mysql-master-02

stop slave;

change master to master_host='192.168.72.131',master_user='repl',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=632;

start slave;

show slave status\G;

验证主主配置

方法：

1. 分别登录mysql-master-01和mysql-master-02数据库，选择任意一个节点创建数据库master_master，从另一个节点查看数据库是否存在。
2. 选择任意一个节点，进入master_master数据库，创建表，并插入数据，在另一节点查看表数据，并进行修改，返回第一个节点查看数据是否修改成功。