OS: CentOS7
master:192.168.1.10
backup:192.168.1.20
VIP:192.168.1.30
一、安装MySQL数据库.
在master 和 backup 上安装mysql,安装完后自动启动,mysql root密码为123456
二、修改MySQL配置文件:
1.master端配置文件如下:
1.master端配置文件如下:
# vim /etc/my.cnf #添加
server_id = 1 #backup上设置为2
log-bin = /data/mysql/mysql-bin
log-bin-index=/data/mysql/my-bin.index
binlog-ignore-db = mysql,information_schema #忽略写入binlog日志的库
auto-increment-increment = 2 #字段变化增量值
auto-increment-offset = 1 #初始字段ID为1
slave-skip-errors = all #忽略所有复制产生的错误
# systemctl restart mysqld
2. backup端配置文件如下:
master端和backup端配置只有server_id不一样,别的都一致.
三、创建数据同步用户并查看log bin日志和pos位置:
1.> master上创建 mysql 同步账号并查看log bin日志和pos位置:
# mysql -uroot -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED BY 'repl';
mysql> flush privileges;
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 | 618 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
master配置如下:
# mysql -uroot -p123456
mysql> change master to
-> master_host='192.168.1.20', #这里填backup的IP
-> master_user='repl',
-> master_password='repl',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=618;
mysql> start slave;
2.> backup上创建mysql同步账号配置如下:
# mysql -uroot -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED BY 'repl';
mysql> flush privileges;
mysql> change master to
-> master_host='192.168.1.10', #这里填master的IP
-> master_user='repl',
-> master_password='repl',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=618;
mysql> start slave;
---------------------
分别查看同步状态:
master查看:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.20
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1082
Relay_Log_File: test2-relay-bin.000002
Relay_Log_Pos: 784
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
---------------------
backup查看:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 618
Relay_Log_File: test3-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
---------------------
Slave_IO和Slave_SQL是YES说明主主同步成功。
四、MySQL主主同步测试
master上插入数据测试:
mysql> create database testdb;
mysql> use testdb;
mysql> create table user (number INT(10),name VARCHAR(255));
mysql> insert into user values(01,'testid');
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
---------------------
backup上查看: