MySQL5.6 Replication主从复制(读写分离) 配置完整版

本贴最后更新于 2156 天前,其中的信息可能已经物是人非

MySQL5.6主从复制(读写分离)教程

1MySQL5.6开始主从复制有两种方式:

基于日志(binlog);

基于GTID(全局事务标示符)。

需要注意的是:GTID方式不支持临时表!所以如果你的业务系统要用到临时表的话就不要考虑这种方式了,至少目前最新版本MySQL5.6.12GTID复制还是不支持临时表的。

所以本教程主要是告诉大家如何通过日志(binlog)方式做主从复制!

 

2MySQL官方提供的MySQL Replication教程:

 http://dev.mysql.com/doc/refman/5.6/en/replication.html

第一步:准备工作

主服务器: 192.168.1.100

从服务器: 192.168.1.101

MySQL软件版本

MySQL-server-advanced-5.6.18-1.el6.x86_64.rpm

MySQL-cient-advanced-5.6.18-1.el6.x86_64.rpm

 

第二步:在主服务器和从服务器上安装MySQL数据库软件

安装方法,请参见 

RedHat6.5MySQL5.6集群配置完整版  http://www.linuxidc.com/Linux/2014-06/103748.htm 

 

MySQL数据库软件安装完成后,不要急着做mysql启动操作。建议把mysql初始化生成的/usr/my.cnf

(如果是从源文件编译安装时,路径应该是在/usr/local/mysql/mysql.cnf)删除,然后把优化好的mysql

配置文件my.cnf放到/etc下。 

 

第三步:修改主数据库的配置文件/usr/my.cnf 

[mysqld]

 server-id=1

log-bin=mysqlmaster-bin.log

sync_binlog=1

 

innodb_buffer_pool_size=512M

innodb_flush_log_at_trx_commit=1

 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 

lower_case_table_names=1

log_bin_trust_function_creators=1

 

第四步:修改从数据库配置文件/usr/my.cnf

server-id=2

log-bin=mysqlslave-bin.log

sync_binlog=1

innodb_buffer_pool_size=512M

innodb_flush_log_at_trx_commit=1

 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

lower_case_table_names=1

log_bin_trust_function_creators=1

 

第五步:在主数据库和从数据库服务器上分别执行以下命令重新启动主数据库和从数据库

[root@master ~]# service mysql restart

[root@slave ~]# service mysql restart

 

第六步:在主数据库上创建用于主从复制的账户

[root@master ~]# mysql -uroot -p

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.101' IDENTIFIED BY '111111';

Query OK, 0 rows affected (0.00 sec)

 

注意:以上命令中的IP地址,是从数据库服务器的IP地址。

 

第七步:主数据库锁表(禁止再插入数据以获取主数据库的的二进制日志坐标)

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

 

第八步:查看主数据库的状态(并记录下File字段和Position字段的值,在配置从服务器时有用到)

mysql> show master status;

+------------------------+----------+--------------+------------------+-------------------+

| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------------+----------+--------------+------------------+-------------------+

| mysqlmaster-bin.000004 |      327 |              |                  |                  |

+------------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

第九步:创建主数据库的快照文件

[root@master ~]# cd /usr/bin/

# ./mysqldump -uroot -p -h127.0.0.1 -P3306 --all-databases --triggers --routines --events >>/mnt/windows/all.sql

上面命令中的红色部分,是一个共享目录,这个目录可以同时被主数据库服务器和从数据库服务器访问到。

如果没有这样的共享目录,可以将all.sql放在其它任何目录下,然后使用scp命令复制到远程从数据库服务器的某个目录中

这条命令的执行时间根据数据量的不同,会有所不同,如果主数据库的数据量很大,可能需要很长时间,那么在这种情况下,就最好在晚上没有业务的时候进行这个操作,否则第七步中的锁表操作会对业务系统造成很大的影响

 

第十步:解锁主数据库的锁表操作

[root@master ~]# mysql -uroot -p    (本命令在主数据库服务器上执行)

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

 

第十一步:在从数据库服务器上导入第七步创建的快照文件到从数据库中

[root@slave ~]# mysql -uroot -p -h127.0.0.1 -P3306 < /mnt/windows/all.sql

 

第十二步:在从数据库服务器上设置主数据库服务器向从数据库服务器同步

[root@slave ~]# mysql -uroot -p

mysql> change master to master_host = '192.168.1.100',master_user='repl',master_password='111111',master_log_file='mysqlmaster-bin.000004',master_log_pos=327;

注意:红色部分的值,是在第八步中查出来的,这里不能弄错了

 

第十三步:启动从数据库复制线程

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

 

第十四步:查询从数据库的复制线程状态

mysql> show slave status \G

*************************** 1. row ***************************

              Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.1.100

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysqlmaster-bin.000004

          Read_Master_Log_Pos: 327

              Relay_Log_File: slave-relay-bin.000002

                Relay_Log_Pos: 289

        Relay_Master_Log_File: mysqlmaster-bin.000004

          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: 327

              Relay_Log_Space: 462

              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: 2e5e1b22-f0a9-11e3-bbac-000c297799e0

            Master_Info_File: /var/lib/mysql/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

          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

1 row in set (0.00 sec)

 

如果Slave_IO_RunningSlave_SQL_Running两项都为yes,就表示主从复制配置成功了.

下面可以开始测试配置是否成功了,首先在主数据库的test数据库中新建一张表,然后插入几条数据,然后到从数据库看看是否同步过来了。

注意:当从数据库有大量的查询时,可以暂时将从数据库的复制线程关闭掉,等查询量降下来了,再打开,这样也不会丢失数据。

 

 

--------------------------------------分割线 --------------------------------------

Ubuntu 14.04下安装MySQL http://www.linuxidc.com/Linux/2014-05/102366.htm

MySQL权威指南(原书第2)》清晰中文扫描版 PDF http://www.linuxidc.com/Linux/2014-03/98821.htm

Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL http://www.linuxidc.com/Linux/2014-05/102351.htm

Ubuntu 14.04下搭建MySQL主从服务器 http://www.linuxidc.com/Linux/2014-05/101599.htm

Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群 http://www.linuxidc.com/Linux/2013-11/93019.htm

Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb http://www.linuxidc.com/Linux/2013-08/89270.htm

--------------------------------------分割线 --------------------------------------

 

  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    690 引用 • 535 回帖
  • Replication
    2 引用
  • Ubuntu

    Ubuntu(友帮拓、优般图、乌班图)是一个以桌面应用为主的 Linux 操作系统,其名称来自非洲南部祖鲁语或豪萨语的“ubuntu”一词,意思是“人性”、“我的存在是因为大家的存在”,是非洲传统的一种价值观,类似华人社会的“仁爱”思想。Ubuntu 的目标在于为一般用户提供一个最新的、同时又相当稳定的主要由自由软件构建而成的操作系统。

    125 引用 • 169 回帖 • 1 关注
  • 运维

    互联网运维工作,以服务为中心,以稳定、安全、高效为三个基本点,确保公司的互联网业务能够 7×24 小时为用户提供高质量的服务。

    149 引用 • 257 回帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...

推荐标签 标签

  • WebComponents

    Web Components 是 W3C 定义的标准,它给了前端开发者扩展浏览器标签的能力,可以方便地定制可复用组件,更好的进行模块化开发,解放了前端开发者的生产力。

    1 引用
  • C++

    C++ 是在 C 语言的基础上开发的一种通用编程语言,应用广泛。C++ 支持多种编程范式,面向对象编程、泛型编程和过程化编程。

    107 引用 • 153 回帖
  • wolai

    我来 wolai:不仅仅是未来的云端笔记!

    2 引用 • 14 回帖
  • Openfire

    Openfire 是开源的、基于可拓展通讯和表示协议 (XMPP)、采用 Java 编程语言开发的实时协作服务器。Openfire 的效率很高,单台服务器可支持上万并发用户。

    6 引用 • 7 回帖 • 94 关注
  • RIP

    愿逝者安息!

    8 引用 • 92 回帖 • 351 关注
  • 京东

    京东是中国最大的自营式电商企业,2015 年第一季度在中国自营式 B2C 电商市场的占有率为 56.3%。2014 年 5 月,京东在美国纳斯达克证券交易所正式挂牌上市(股票代码:JD),是中国第一个成功赴美上市的大型综合型电商平台,与腾讯、百度等中国互联网巨头共同跻身全球前十大互联网公司排行榜。

    14 引用 • 102 回帖 • 376 关注
  • 机器学习

    机器学习(Machine Learning)是一门多领域交叉学科,涉及概率论、统计学、逼近论、凸分析、算法复杂度理论等多门学科。专门研究计算机怎样模拟或实现人类的学习行为,以获取新的知识或技能,重新组织已有的知识结构使之不断改善自身的性能。

    83 引用 • 37 回帖 • 1 关注
  • 面试

    面试造航母,上班拧螺丝。多面试,少加班。

    325 引用 • 1395 回帖
  • TGIF

    Thank God It's Friday! 感谢老天,总算到星期五啦!

    287 引用 • 4484 回帖 • 669 关注
  • V2EX

    V2EX 是创意工作者们的社区。这里目前汇聚了超过 400,000 名主要来自互联网行业、游戏行业和媒体行业的创意工作者。V2EX 希望能够成为创意工作者们的生活和事业的一部分。

    17 引用 • 236 回帖 • 327 关注
  • GAE

    Google App Engine(GAE)是 Google 管理的数据中心中用于 WEB 应用程序的开发和托管的平台。2008 年 4 月 发布第一个测试版本。目前支持 Python、Java 和 Go 开发部署。全球已有数十万的开发者在其上开发了众多的应用。

    14 引用 • 42 回帖 • 764 关注
  • 学习

    “梦想从学习开始,事业从实践起步” —— 习近平

    169 引用 • 506 回帖
  • SOHO

    为成为自由职业者在家办公而努力吧!

    7 引用 • 55 回帖 • 19 关注
  • JRebel

    JRebel 是一款 Java 虚拟机插件,它使得 Java 程序员能在不进行重部署的情况下,即时看到代码的改变对一个应用程序带来的影响。

    26 引用 • 78 回帖 • 664 关注
  • 酷鸟浏览器

    安全 · 稳定 · 快速
    为跨境从业人员提供专业的跨境浏览器

    3 引用 • 59 回帖 • 26 关注
  • 区块链

    区块链是分布式数据存储、点对点传输、共识机制、加密算法等计算机技术的新型应用模式。所谓共识机制是区块链系统中实现不同节点之间建立信任、获取权益的数学算法 。

    91 引用 • 751 回帖 • 2 关注
  • 职场

    找到自己的位置,萌新烦恼少。

    127 引用 • 1705 回帖 • 1 关注
  • Gitea

    Gitea 是一个开源社区驱动的轻量级代码托管解决方案,后端采用 Go 编写,采用 MIT 许可证。

    4 引用 • 16 回帖 • 5 关注
  • Unity

    Unity 是由 Unity Technologies 开发的一个让开发者可以轻松创建诸如 2D、3D 多平台的综合型游戏开发工具,是一个全面整合的专业游戏引擎。

    25 引用 • 7 回帖 • 173 关注
  • 前端

    前端技术一般分为前端设计和前端开发,前端设计可以理解为网站的视觉设计,前端开发则是网站的前台代码实现,包括 HTML、CSS 以及 JavaScript 等。

    247 引用 • 1348 回帖
  • JavaScript

    JavaScript 一种动态类型、弱类型、基于原型的直译式脚本语言,内置支持类型。它的解释器被称为 JavaScript 引擎,为浏览器的一部分,广泛用于客户端的脚本语言,最早是在 HTML 网页上使用,用来给 HTML 网页增加动态功能。

    729 引用 • 1327 回帖
  • SEO

    发布对别人有帮助的原创内容是最好的 SEO 方式。

    35 引用 • 200 回帖 • 22 关注
  • 房星科技

    房星网,我们不和没有钱的程序员谈理想,我们要让程序员又有理想又有钱。我们有雄厚的房地产行业线下资源,遍布昆明全城的 100 家门店、四千地产经纪人是我们坚实的后盾。

    6 引用 • 141 回帖 • 585 关注
  • 以太坊

    以太坊(Ethereum)并不是一个机构,而是一款能够在区块链上实现智能合约、开源的底层系统。以太坊是一个平台和一种编程语言 Solidity,使开发人员能够建立和发布下一代去中心化应用。 以太坊可以用来编程、分散、担保和交易任何事物:投票、域名、金融交易所、众筹、公司管理、合同和知识产权等等。

    34 引用 • 367 回帖
  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    690 引用 • 535 回帖
  • Sym

    Sym 是一款用 Java 实现的现代化社区(论坛/BBS/社交网络/博客)系统平台。

    下一代的社区系统,为未来而构建

    524 引用 • 4601 回帖 • 700 关注
  • SendCloud

    SendCloud 由搜狐武汉研发中心孵化的项目,是致力于为开发者提供高质量的触发邮件服务的云端邮件发送平台,为开发者提供便利的 API 接口来调用服务,让邮件准确迅速到达用户收件箱并获得强大的追踪数据。

    2 引用 • 8 回帖 • 483 关注