1,服务器安装:
搞两台虚拟机CentOS7 64位,IP分别为192.168.216.201、192.168.216.202
2,数据库安装:
参考官网yum安装:MariaDB
3,主服务器配置:192.168.216.201
> vi /etc/my.cnf.d/server.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
4,从服务器配置:192.168.216.202
> vi /etc/my.cnf.d/server.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=mixed
server-id = 202
5,重启数据库服务器:192.168.216.201、192.168.216.202
> service mysql restart
6,设置数据库访问密码:192.168.216.201、192.168.216.202
> mysql
> grant all privileges on *.* TO 'root'@'%' identified by 'password' with grant option
7,查看主服务器状态:192.168.216.201
> mysql
> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 469 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
8,配置从服务器:192.168.216.202
> mysql
> change master to master_host='192.168.216.201',master_user='root',master_password='password',master_log_file='mysql-bin.000005',master_log_pos=469;
> start slave;
9,启动复制:192.168.216.202
> mysql
> stop slave;
> reset slave; // 重启后master信息将被删除
10,停止或者删除复制
> mysql
> start slave;
至此,单向主从服务器配置成功,在主服务器上进行修改,从服务器会进行相应的自动复制
如果要设置双向复制,则只要把主服务器当做从服务器,从服务器当做主服务器来再设置一下就可以了。
设置互为主从后存在一个问题就是,递增字段的插入,可能出现冲突,所以通过设置偏移量和步长可以避免这个问题,修改步骤3、4:
3,主服务器配置:192.168.216.201
> vi /etc/my.cnf.d/server.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=mixed
server-id = 202
#auto increment set:1,3,5,7,9......
auto_increment_offset=1
auto_increment_increment=2
4,从服务器配置:192.168.216.202
> vi /etc/my.cnf.d/server.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=mixed
server-id = 202
#auto increment set:2,4,6,8......
auto_increment_offset=2
auto_increment_increment=2