1、安装MySQL环境(不讲怎么安装了,太简单)
2、注意事项
保持数据一致性、版本一样、修改root密码
3、准备了4台虚拟机 准备做Master>Slave——Master>Slave Master>Master
ip:192.168.0.101(主Master) 192.168.0.102 (从Slave) 192.168.0.103(主Master) 192.168.0.104(从Slave)
4、修改mysql配置文件my.cnf(192.168.0.101、192.168.0.102、192.168.0.103、192.168.0.104)
server-id = 101 server-id = 102 server-id = 103 server-id = 104 (这儿不同就可以 我为了方便)
5、重新启动mysql服务
service mysql restart
6、进入(Master 192.168.0.101)mysql建立账户并授权
# mysql -u root -p123456
mysql>GRANT REPLICATION SLAVE ON *.* to 'xiao'@'%' identified by '123456'; #授权语法 一般不用root账号
mysql>start master; #启动Master
mysql> show master status;
7、登陆Slave(192.168.0.102)从机
#mysql -u root -p123456
mysql>change master to master_host='192.168.0.101',master_user='xiao',master_password='123456',master_log_file='mysql-bin.000016',master_log_pos=708;
mysql>start slave; //启动从服务器复制功能
mysql>show slave status\G; //查看启动状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes 这两个状态必须Yes
主从搭建好了,192.168.0.103和192.168.0.104搭建过程省略。
开始搭建双主 192.168.0.101和192.168.0.103 (Master>Master)
1、 修改my.cnf配置文件
(192.168.0.101):
auto_increment_offset=1
auto_increment_increment=2 #奇数ID
log-slave-updates=true
replicate-ignore-db=mysql #忽略不同步主从的数据库
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
(192.168.0.103)
auto_increment_offset = 2
auto_increment_increment = 2 #偶数ID
log-slave-updates=true
replicate-ignore-db=mysql
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
2、添加授权用户(两台都得添加)
192.168.0.101:
mysql>grant replication slave on *.* to 'xiao'@'192.168.0.101' identified by '123456';
mysql>flush privileges;
192.168.0.103:
mysql>grant replication slave on *.* to 'xiao'@'192.168.0.103' identified by '123456';
mysql>flush privileges;
3、查看两台主库的状态
192.168.0.101:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000016 | 708 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
192.168.0.103:
mysql> show master status;
+------------------+----------+--------------+---------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-bin.000015 | 708 | | mysql,information_schema,performance_schema | |
+------------------+----------+--------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)
4、配置同步信息
192.168.0.101:
mysql>change master to master_host='192.168.0.103',master_port=3306,master_user='xiao',master_password='123456',master_log_file='mysql-bin.000015'
,master_log_pos=708;
mysql>start slave;
mysql>show slave status\G; //Slave_IO_Running: Yes Slave_SQL_Running: Yes 双yes才可以
192.168.0.101:
mysql>change master to master_host='192.168.0.101',master_port=3306,master_user='xiao',master_password='123456',master_log_file='mysql-bin.000016'
,master_log_pos=708;
mysql>start slave;
mysql>show slave status\G; //Slave_IO_Running: Yes Slave_SQL_Running: Yes 双yes才可
5、测试创建一个数据库 4个数据库同步则完成