已测试
实验环境:
主机名 ip
apache 192.168.24.50
master1 192.168.24.10
master2 192.168.24.20
slave1 192.168.24.30
slave2 192.168.24.40
mysql主从架构(master-slave)
master必须开启binlog
master配置:
vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=binlog
log-bin-index=binlog.index
slave配置:
vim /etc/my.cnf
[mysqld]
server_id=333
relay_log=/var/lib/mysql/mysql-relay-bin
relay_log_index=/var/lib/mysql/mysql-relay-bin.index
master && slave
service mysqld restart
master:
mysql> GRANT replication slave ON *.* TO 'slave'@'192.168.24.30' identified by '123';
mysql> flush privileges;
slave:
cd /var/lib/mysql
rm -fr *
service mysqld restart
master
mysqldump -A -x > /tmp/full.sql -A 所有库 -x锁表
scp /tmp/full.sql root@salveip:/tmp/
slave:
mysql < /tmp/full.sql
master:
mysql> flush tables with read lock;加个锁
mysql> show master status; 查看主状态 用的是001at368
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000001 | 368 | | |
mysql>unlock tables;
slave:(改变master)
mysql> change master to master_host='192.168.24.10', master_port=3306, master_user='slave', master_password='123', master_log_file='binlog.000001',master_log_pos=368;
mysql> start slave;
slave:(查看一下状态,下面两个为yes说明成功了)
mysql> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
MYSQL 一主两辅配置(master-slave-slave)在上面配置中继续修改即可,刚才已经设置了30,现在增加40就可以了
在master上为slave添加一个同步账号,
master
mysql> grant replication slave on *.* to 'slave'@'192.168.24.30' identified by '123';
//在slave1上登陆成功
mysql> grant replication slave on *.* to 'slave'@'192.168.24.40' identified by '123';
//在slave2上登陆成功
保存后,重启master的mysql服务:
service mysql restart;
用show master status命令查看日志情况
mysql> show master status\G;
修改slave1上的配置文件my.cnf。
在[mysqld]下添加如下字段
vi /etc/my.cnf
server-id=182
master-host=192.168.24.10
master-user= slave
master-password=123
master-port=3306
relay_log = /var/lib/mysql/mysql-relay-bin
relay_log_index=/var/lib/mysql/mysql-relay-bin.index
保存后,重启slave的mysql服务:
service mysql restart;
修改slave2上的配置文件my.cnf,和上面类似,只是把server-id改下
slave2上我设置的server-id是222。
在进入两个slave机中的mysql。
mysql>start slave;
mysql>show slave status\G;
两个yes就可以了
MySQL主动-被动的双主模式a-b-c (master-master-slave)
继续上面配置修改master1与master2
master1
server-id = 1
log-bin=binlog
log-bin-index=binlog.index
log_slave_updates = 1
auto_increment_increment=2
auto_increment_offset=1
mater2
server-id = 2
log-bin=binlog
log-bin-index=binlog.index
log_slave_updates = 1
auto_increment_increment=2
auto_increment_offset=2
接着就是两台服务器都配上slave用户。
master1
mysql> GRANT replication slave ON *.* TO 'slave'@'192.168.24.20' identified by '123';
mysql> flush privileges;
master2
mysql> GRANT replication slave ON *.* TO 'slave'@'192.168.24.10' identified by '123';
mysql> flush privileges;
SHOW MASTER STATUS 查看一下binlog
master1上 SHOW MASTER STATUS
master2
mysql> change master to master_host='192.168.24.10', master_port=3306, master_user='slave',master_password='123', master_log_file='binlog.000002',master_log_pos=616;
mysql> start slave;
master1
mysql> change master to master_host='192.168.24.20', master_port=3306, master_user='slave',
master_password='123', master_log_file='binlog.000002',master_log_pos=616;
mysql> start slave;
补上测试图。。。
在master1创建数据库,另外三台则同步,一个master2 ,另外两个是从服务器
master1
master2
slave1
slave2