注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle专业打杂

定会重回巅峰……

 
 
 

日志

 
 

Mysql5.6.25主主复制配置  

2015-09-08 11:03:53|  分类: MYSQL基础知识 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
Mysql版本:MySQL Database 5.6.25 TAR for Generic Linux (glibc2.5) x86 (64bit).zip
操作系统:RHEL5.5 x86_64

在本文中采用mysql多实例来做mysql的主主复制。
实例1:端口号3307,数据文件/data/3307/data,配置文件/data/3307//my.cnf,主实例1:Slave
实例2:端口号3308,数据文件/data/3308/data,配置文件/data/3308/my.cnf,主实例2:Master

1、修改端口号为3307的配置文件

[root@localhost 3307]# cat my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /data/3307/data
port = 3307
server_id = 2
socket = /data/3307/mysql.sock
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log-bin = mysql-bin
auto-increment-increment = 2
auto-increment-offset = 1
2、修改端口号为3308的配置文件

[root@localhost 3307]# cat ../3308/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /data/3308/data
port = 3308
server_id = 3
socket = /data/3308/mysql.sock
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log-bin = mysql-bin
auto-increment-increment = 2
auto-increment-offset = 2
3、分别启动两个主实例

/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3308/my.cnf &

4查看3307、3308端口号是否开启

ss -lnt|grep 330

5、分别在3307、3308上创建复制用户并查看bin-log信息
3307

grant replication client,replication slave on *.* to slave@'127.0.0.1' identified by '123'; 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      352 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

3308

grant replication client,replication slave on *.* to master@'127.0.0.1' identified by '123';
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 353 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

6、配置各主服务器参数

--3307
mysql> change master to master_host='127.0.0.1',master_port=3308,master_user='master',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=353;
mysql> start slave;

--3308
mysql> change master to master_host='127.0.0.1',master_port=3307,master_user='slave',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=352;
mysql> start slave;


7、查看各slave的状态
mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
8、验证是否是真正的双主,建立一个xiaopangzi数据库,并建立一张ru的表,分别在两个实例上插入数据,具体操作如下所示
Step1:

--在3308上创建数据库
mysql> create database xiaopangzi;
Query OK, 1 row affected (0.03 sec)
mysql> use xiaopangzi;
Database changed
--创建表ru
mysql> create table ru(id int primary key auto_increment,name varchar(64) ,describle text);
Query OK, 0 rows affected (0.21 sec)
--插入一条数据
mysql> insert into ru(name,describle) values('pangzi','pangzi sd');
Query OK, 1 row affected (0.00 sec)


Step2:

--在3307上查看有哪些数据库,发现3307上数据库xiaopangzi已成功创建
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| xiaopangzi         |
+--------------------+
5 rows in set (0.05 sec)
--使用xiaopangzi数据库
mysql> use xiaopangzi;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
--查看xiaopangzi中有哪些表,表ru已成功创建
mysql> show tables;
+----------------------+
| Tables_in_xiaopangzi |
+----------------------+
| ru                   |
+----------------------+
1 row in set (0.00 sec)
--插入一条记录
mysql> insert into ru(name,describle) values('peng','clear');
Query OK, 1 row affected (0.00 sec)
--可以看到表中有两条记录,说明3308上插入的记录已同步到3307上
mysql> select * from ru;
+----+--------+-----------+
| id | name   | describle |
+----+--------+-----------+
|  2 | pangzi | pangzi sd |
|  3 | peng   | clear     |
+----+--------+-----------+
Step3:

--在3308上查看表ru中记录的条目,与预期的一样,说明MM已经成功!
mysql> select * from ru;
+----+--------+-----------+
| id | name   | describle |
+----+--------+-----------+
|  2 | pangzi | pangzi sd |
|  3 | peng   | clear     |
+----+--------+-----------+
2 rows in set (0.00 sec)

  评论这张
 
阅读(29)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017