mysql master slave模式_MySQL数据库（master-slave-slave）（master-master-slave）

已测试

实验环境：

主机名   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