mysql主主复制配置
来源:互联网 发布:win7 数据恢复 编辑:程序博客网 时间:2024/05/21 22:54
1、原理:两个主服务建立二进制日志和relay-log,主服务器建立复制账号供另一个主服务器来连接监听用,另一个主服务器也建立复制账号提供主服务器连接监听
2.建立mysql在两个主节点master(192.168.1.110)和slave1(192.168.1.111)复制
1)配置master(192.168.1.110)和slave1(192.168.1.111)的my.cnf的配置信息(需要配置主机名、二进制文件、二进制文件格式和relay-log)
master(192.168.1.110):/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#skip-grant-tables
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#server-id
server-id=110
#binary log
log-bin=mysql-bin
#statement row mixed
binlog-format=mixed
#relay log
relay-log=mysql-relay
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
slave1(192.168.1.111):/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#server-id
server-id=111
log-bin=mysql-bin
binlog-format=mixed
#relay log
relay-log=mysql-relay
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
**重启master(192.168.1.110)和slave1(192.168.1.111)的mysql,目的重启加载配置信息
[root@master ~]# service mysqld restart
[root@slave1 mysql]# service mysqld restart
2)master(192.168.1.110) 和slave1(192.168.1.111)建立授权账号:
master(192.168.1.110):mysql> grant replication client, replication slave on *.* to 'root'@'%' identified by '123456';
master(192.168.1.110):mysql> flush privileges;
slave1(192.168.1.111):mysql> grant replication client, replication slave on *.* to 'root'@'%' identified by '123456';
slave1(192.168.1.111):mysql> flush privileges;
3)master(192.168.1.110)建立slave1(192.168.1.111)复制信息;slave1(192.168.1.111)建立master(192.168.1.110)复制信息
a.master(192.168.1.110)要需要建立slave1(192.168.1.111)复制信息,即在master(192.168.1.110)中建立slave信息,启动slave(mysql>start slave)就可以从slave1(192.168.1.111)监听复制变化的二进制文件信息:
//查看slave1(192.168.1.111)的master信息:
slave1(192.168.1.111):mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 268 | | |
+------------------+----------+--------------+------------------+
master(192.168.1.110)需要在slave配置复制slave1(192.168.1.111)的信息为:
master_host='192.168.1.111'
master_user='root'
master_password='123456'
master_log_file='mysql-bin.000003'
master_log_pos=268
master(192.168.1.110):mysql>change master to master_host='192.168.1.111', master_user='root', master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=268;
master(192.168.1.110):mysql>start slave;
b.同理slave1(192.168.1.111)建立master(192.168.1.110)复制信息
master(192.168.1.110):mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 268 | | |
+------------------+----------+--------------+------------------+
slave1(192.168.1.111):mysql>change master to master_host='192.168.1.110', master_user='root', master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=268;
c.启动各自配置好的slave进行互相复制监听
master(192.168.1.110):mysql> start slave
slave1(192.168.1.111):mysql> start slave
2.建立mysql在两个主节点master(192.168.1.110)和slave1(192.168.1.111)复制
1)配置master(192.168.1.110)和slave1(192.168.1.111)的my.cnf的配置信息(需要配置主机名、二进制文件、二进制文件格式和relay-log)
master(192.168.1.110):/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#skip-grant-tables
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#server-id
server-id=110
#binary log
log-bin=mysql-bin
#statement row mixed
binlog-format=mixed
#relay log
relay-log=mysql-relay
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
slave1(192.168.1.111):/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#server-id
server-id=111
log-bin=mysql-bin
binlog-format=mixed
#relay log
relay-log=mysql-relay
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
**重启master(192.168.1.110)和slave1(192.168.1.111)的mysql,目的重启加载配置信息
[root@master ~]# service mysqld restart
[root@slave1 mysql]# service mysqld restart
2)master(192.168.1.110) 和slave1(192.168.1.111)建立授权账号:
master(192.168.1.110):mysql> grant replication client, replication slave on *.* to 'root'@'%' identified by '123456';
master(192.168.1.110):mysql> flush privileges;
slave1(192.168.1.111):mysql> grant replication client, replication slave on *.* to 'root'@'%' identified by '123456';
slave1(192.168.1.111):mysql> flush privileges;
3)master(192.168.1.110)建立slave1(192.168.1.111)复制信息;slave1(192.168.1.111)建立master(192.168.1.110)复制信息
a.master(192.168.1.110)要需要建立slave1(192.168.1.111)复制信息,即在master(192.168.1.110)中建立slave信息,启动slave(mysql>start slave)就可以从slave1(192.168.1.111)监听复制变化的二进制文件信息:
//查看slave1(192.168.1.111)的master信息:
slave1(192.168.1.111):mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 268 | | |
+------------------+----------+--------------+------------------+
master(192.168.1.110)需要在slave配置复制slave1(192.168.1.111)的信息为:
master_host='192.168.1.111'
master_user='root'
master_password='123456'
master_log_file='mysql-bin.000003'
master_log_pos=268
master(192.168.1.110):mysql>change master to master_host='192.168.1.111', master_user='root', master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=268;
master(192.168.1.110):mysql>start slave;
b.同理slave1(192.168.1.111)建立master(192.168.1.110)复制信息
master(192.168.1.110):mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 268 | | |
+------------------+----------+--------------+------------------+
slave1(192.168.1.111):mysql>change master to master_host='192.168.1.110', master_user='root', master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=268;
c.启动各自配置好的slave进行互相复制监听
master(192.168.1.110):mysql> start slave
slave1(192.168.1.111):mysql> start slave
0 0
- mysql主主复制配置
- mysql主主复制配置
- mysql主主复制配置
- mysql主主复制(双主复制)配置步骤
- mysql 主从复制和主主复制配置
- Mysql主主复制构架配置
- 配置MYSQL服务器实现主主复制
- mysql 主从复制 主主配置
- MYSQL双主同步复制配置
- 在不停止mysql复制主服务器的情况下,配置一个mysql复制从服务器
- mysql主主复制和keepalived配置过程
- 配置keepalived实现高可用性mysql主主复制
- MySQL配置主主复制和高可用
- 基于主主复制的mysql读写分离配置
- MySQL建立双向主备复制服务器配置方法
- mysql 主master 从 slaver 复制 配置详细
- linux环境下配置mysql双主复制
- mysql主主复制
- 按之字形打印二叉树
- square869120Contest #4 B.Buildings are Colorful!【二进制枚举】
- 常用批处理内部命令使用详解
- Java垃圾回收机制
- 删除链表中重复的结点
- mysql主主复制配置
- java多线程:15、阻塞队列【BlockingQueue】
- decimalformat
- rapidxml 文件读写、增加、删除、编辑节点
- SPOJ 2829 TLE
- intellij idea: Error configuring listener org.springframework.web.context.ContextLoaderListener
- 简单Node.js搭建本地站点
- Miracast与Wi-Fi Direct技术简介
- Spring注解 @Component、@Controller、@Service、@Repository