文档章节

sql --mysql主从复制

求是科技
 求是科技
发布于 2016/09/19 11:29
字数 1319
阅读 309
收藏 3

基于windows平台,mysql版本mysql-5.7.14-winx64,步骤如下
###1.创建两个mysql实例
详见https://my.oschina.net/u/2312022/blog/747955
###2.查看mysql官网
http://dev.mysql.com/doc/refman/5.7/en/replication.html
###3.配置master
http://dev.mysql.com/doc/refman/5.7/en/replication-howto-masterbaseconfig.html
我的配置如下

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
basedir = E:\mysql-5.7.14-winx64\mysql-5.7.14-winx64
datadir = E:\mysql-5.7.14-winx64\mysql-5.7.14-winx64\data
port = 3306
server_id = 1
#开启二进制日志
log-bin= master-bin

备注:我们下载的mysql.zip,默认配置文件是my-default.ini,但是mysql文档描述如下

To configure the binary log and server ID options, shut down the MySQL server and edit the my.cnf or my.ini file. Within the [mysqld] section of the configuration file, add the log-bin and server-id options. If these options already exist, but are commented out, uncomment the options and alter them according to your needs. For example, to enable binary logging using a log file name prefix of mysql-bin, and configure a server ID of 1, use these lines: 

我试过my-default.ini,但是没有成功,因此这里手动的将配置文件的名称改成my.ini。master数据库改了,slave数据库也要修改,slave修改了之后,mysql2启动不起来,同时要修改windows注册表里面的信息。
###4.启动master
####1.启动master

E:\mysql-5.7.14-winx64\mysql-5.7.14-winx64\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

####2.登陆master

E:\mysql-5.7.14-winx64\mysql-5.7.14-winx64\bin>mysql -u root -p777888999

####3.查看master

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

####4.查看用户

mysql> select user,host from mysql.user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| lxl       | %         |
| panda     | %         |
| mysql.sys | localhost |
| root      | localhost |
+-----------+-----------+
4 rows in set (0.00 sec)

mysql>

####5.新建用户
由于slave连接master需要一个用户,所以,我们这里新建一个用户,并且赋予它权限。

mysql> create user zhangsan identified by '123456';
Query OK, 0 rows affected (0.03 sec)

mysql> select user,host from mysql.user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| lxl       | %         |
| panda     | %         |
| zhangsan  | %         |
| mysql.sys | localhost |
| root      | localhost |
+-----------+-----------+
5 rows in set (0.00 sec)

mysql>

####6.赋予新建用户权限

mysql> grant replication slave on *.* to zhangsan;
Query OK, 0 rows affected (0.03 sec)

mysql>

赋予 zhangsan这个用户slave权限,*.*表示master所有的数据库下面所有的表,实际项目中,应该是指定数据库。
###5.配置slave
mysql官网
http://dev.mysql.com/doc/refman/5.7/en/replication-setup-slaves.html
我的配置如下

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
basedir = F:\mysql2\mysql-5.7.14-winx64\mysql-5.7.14-winx64
datadir = F:\mysql2\mysql-5.7.14-winx64\mysql-5.7.14-winx64\data
port = 3307
server_id = 2
relay_log=slave-relay-bin

####1..启动/登陆/进入slave

F:\mysql2\mysql-5.7.14-winx64\mysql-5.7.14-winx64\bin>net start mysql2
mysql2 服务正在启动 .
mysql2 服务已经启动成功。
F:\mysql2\mysql-5.7.14-winx64\mysql-5.7.14-winx64\bin>mysql -P3307 -u root -p
Enter password:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

这里可以发现,slave与master是一样的,都是新安装后的初始状态。 ####2.配置slave归属master
1.获取master二进制文件名称与位置

mysql> show master status;
+-------------------+----------+--------------+------------------+--------------
-----+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid
_Set |
+-------------------+----------+--------------+------------------+--------------
-----+
| master-bin.000009 |     1419 |              |                  |
     |
+-------------------+----------+--------------+------------------+--------------
-----+
1 row in set (0.00 sec)

mysql>

2.配置slave归属master

mysql> change master to
    -> master_host='192.168.2.67',
    -> master_user='zhangsan',
    -> master_password='123456',
    -> master_log_file='master-bin.000009',
    -> master_log_pos=1419;
Query OK, 0 rows affected, 2 warnings (0.18 sec)

mysql>

3.开启slave

mysql> start slave;
Query OK, 0 rows affected (0.04 sec)

4.查看slave状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.67
                  Master_User: zhangsan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000009
          Read_Master_Log_Pos: 1419
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1419
              Relay_Log_Space: 528
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: ed77c8ba-7496-11e6-8100-c86000d039ef
             Master_Info_File: F:\mysql2\mysql-5.7.14-winx64\mysql-5.7.14-winx64
\data\master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more up
dates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

有上述信息可知:
1.Slave_IO_State:等待master发送事件
作用:监控master二进制日志是否有变更
2.Slave_IO_Running:正在运行
作用:将master二进制日志变更部分取回slave
3.Slave_SQL_Running:正在运行
作用:在slave上重现变更部分,即实现了数据同步
###6.测试
####1.在master上创建数据库

mysql> create database test;
Query OK, 1 row affected (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql>

####2.查看slave

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql>

发现已经同步了。接着测试下数据。
####3.master建表,添加数据

mysql> create table t_user(id int(4),name varchar(100));
Query OK, 0 rows affected (0.41 sec)

mysql>

添加数据

mysql> insert into t_user(id,name) values(1000,'zhangsan');
Query OK, 1 row affected (0.16 sec)

mysql>

####4.查看slave

mysql> use test
Database changed
mysql> select * from t_user;
+------+----------+
| id   | name     |
+------+----------+
| 1000 | zhangsan |
+------+----------+
1 row in set (0.01 sec)

mysql>

如上信息,表明已经同步成功了。
注意:如果slave不需要与master同步了,一定要

mysql> stop slave;
Query OK, 0 rows affected (0.08 sec)

mysql>

否则,下次设置该slave的时候会出现如下错误

mysql> change master to
    -> master_host='192.168.2.67',
    -> master_user='zhangsan',
    -> master_password='123456',
    -> master_log_file='master-bin.000009',
    -> master_log_pos=1419;
ERROR 3021 (HY000): This operation cannot be performed with a running slave io t
hread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.

当然解决办法很明显,先stop slave,在设置slave。

© 著作权归作者所有

求是科技

求是科技

粉丝 100
博文 455
码字总数 238876
作品 0
成都
后端工程师
私信 提问
MySQL主从复制

1.1 MySQL主从复制介绍 MySQL数据库的主从复制方案,和使用scp/rsync等命令进行的文件级别复制类似,都是数据的远程传输,只不过MySQL的主从复制是其自带的功能,无需借助第三方工具,而且,...

lsy950109
2017/06/29
0
0
mysql 主从复制的 工作原理

mysql 主从复制原理 主从形式 mysql主从复制 灵活 一主一从 主主复制 一主多从---扩展系统读取的性能,因为读是在从库读取的; 多主一从---5.7开始支持 联级复制--- 用途及条件 mysql主从复制...

我不是九爷
2018/07/13
0
0
MySQL传统主从复制(第一弹)

0、引言 MySQL主从复制的原理及搭建,故障分析 一、MySQL主从同步的架构及原理 原理: 1)Slave连接到master,主从数据一致,开启同步,开始同步数据 2) 用户在主上写入数据,日志储存到bin...

IT--小哥
2016/12/13
54
0
为什么要对MySQL做主从同步复制

为什么要对MySQL做主从同步复制 一.MySQL主从方案主要作用 1.读写分离,使数据库能支撑更大的并发 在报表中尤其重要。由于部分报表sql语句非常的慢,导致锁表,影响前台服务。如果前台使用m...

zkhylt
2016/05/15
0
0
mysql主从配置实现一主一从读写分离

主从介绍 Mysql主从又叫Replication、AB复制。简单讲就是A与B两台机器做主从后,在A上写数据,另外一台B也会跟着写数据,实现数据实时同步 mysql主从是基于binlog,主上需开启binlog才能进行...

超罗
06/19
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Spring使用ThreadPoolTaskExecutor自定义线程池及实现异步调用

多线程一直是工作或面试过程中的高频知识点,今天给大家分享一下使用 ThreadPoolTaskExecutor 来自定义线程池和实现异步调用多线程。 一、ThreadPoolTaskExecutor 本文采用 Executors 的工厂...

CREATE_17
今天
5
0
CSS盒子模型

CSS盒子模型 组成: content --> padding --> border --> margin 像现实生活中的快递: 物品 --> 填充物 --> 包装盒 --> 盒子与盒子之间的间距 content :width、height组成的 内容区域 padd......

studywin
今天
7
0
修复Win10下开始菜单、设置等系统软件无法打开的问题

因为各种各样的原因导致系统文件丢失、损坏、被修改,而造成win10的开始菜单、设置等系统软件无法打开的情况,可以尝试如下方法解决 此方法只在部分情况下有效,但值得一试 用Windows键+R打开...

locbytes
昨天
8
0
jquery 添加和删除节点

本文转载于:专业的前端网站➺jquery 添加和删除节点 // 增加一个三和一节点function addPanel() { // var newPanel = $('.my-panel').clone(true) var newPanel = $(".triple-panel-con......

前端老手
昨天
8
0
一、Django基础

一、web框架分类和wsgiref模块使用介绍 web框架的本质 socket服务端 与 浏览器的通信 socket服务端功能划分: 负责与浏览器收发消息(socket通信) --> wsgiref/uWsgi/gunicorn... 根据用户访问...

ZeroBit
昨天
10
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部