文档章节

MySQL 主从复制,mysql主从复制

zyt_1978
 zyt_1978
发布于 2016/07/09 23:04
字数 1826
阅读 6
收藏 0

1 复制概述

      Mysql内建的复制功能是构建大型,高性能应用程序的基础。将Mysql的数据分布到多个系统上去,这种分布的机制,是通过将Mysql的某一台主机的数据复制到其它主机(slaves)上,并重新执行一遍来实现的。复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,然后封锁并等待主服务器通知新的更新。

请注意当你进行复制时,所有对复制中的表的更新必须在主服务器上进行。否则,你必须要小心,以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

1.1 mysql支持的复制类型:

  (1):基于语句的复制:  在主服务器上执行的SQL语句,在从服务器上执行同样的语句。MySQL默认采用基于语句的复制,效率比较高。  
            一旦发现没法精确复制时,   会自动选着基于行的复制。    
  (2):基于行的复制:把改变的内容复制过去,而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
  (3):混合类型的复制: 默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制。

1.首先在主库上执行操作

1.1 设置server-id值并开启binlog参数

 根据前文MySQL 的主从同步原理,我们知道要实现主从复制,关键因素就是开启binlog日志功能,所以,我们首先打开主库的binlog日志参数。

1.2实现主从复制的必要条件:

(1)在配置文件my.cnf中配置server-id不能一样。

(2)在主库的配置文件my.cnf中开启log-bin。

查询是否开启代码如下:

 

  1. [root@mysql ~]# egrep "log-bin|server-id"/data/{3306,3307}/my.cnf
  2. /data/3306/my.cnf:log-bin =/data/3306/mysql-bin ##显示已经开启
  3. /data/3306/my.cnf:server-id =1
  4. /data/3307/my.cnf:#log-bin =/data/3307/mysql-bin
  5. /data/3307/my.cnf:server-id =3
  6. [root@mysql ~]#

如果未开启修改主库的配置文件 执行vim /data/3306/my.cnf,编辑多实例3306的my.cnf配置文件,两个参数按如下内容修改: 提示: 1.上面两参数要放在my.cnf中的[mysqld]模块下,否则会出错。  1.3.修改完配置文件后,重启数据库

  1. [root@mysql ~]#/data/3306/mysql stop
  2. StopingMySQL...
  3. [root@mysql ~]#/data/3306/mysql start

1.4.我们也可以在登录数据库中,查看修改的两处配置是否生效; 使用show variables;查看mysql究竟配置了什么参数;

  1. mysql> show variables like "log_bin";
  2. +---------------+-------+
  3. |Variable_name|Value|
  4. +---------------+-------+
  5. | log_bin | ON |
  6. +---------------+-------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> show variables like "server_id";
  10. +---------------+-------+
  11. |Variable_name|Value|
  12. +---------------+-------+
  13. | server_id |1|
  14. +---------------+-------+
  15. 1 row in set (0.00 sec)

2.1 建立用于主从复制的账号  根据主从复制的原理,从库要想和主库同步,必须有一个可以连接主库的账号,并且这个账号的权限是主库上创建的,权限是允许主库的从库连接并同步数据。  

 

2.1、登录mysql3306 实例主数据库

  1. [root@mysql ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock

2.2、建立用于从库复制的账号rep:

  1. mysql> grant replication slave on *.* to 'rep'@'192.168.1.%' identified by '123456';
  2. Query OK,0 rows affected (0.00 sec)

2.3、查看授权

  1. mysql> drop user 'root'@'mysql';##为安全考虑,将无用的授权删除
  2. Query OK,0 rows affected (0.02 sec)
  3.  
  4. mysql> select user,host from mysql.user;
  5. +------+-------------+
  6. | user | host |
  7. +------+-------------+
  8. | root |127.0.0.1|
  9. | rep |192.168.1.%|
  10. | root | localhost |
  11. +------+-------------+
  12. 3 rows in set (0.00 sec)
  13. mysql> flush privileges; ##刷新权限 Query OK, 0 rows affected (0.00 sec)

2.4 查看我们做过授权的用户,所具备的权限2.5 给数据库一个读锁,是的用户无法创建库,只能读:

  1. mysql> flush table with read lock; ##将数据库表锁起来,锁表窗口不能退出,否则锁表失效
  2. Query OK,0 rows affected (0.00 sec)
  3.  
  4. mysql> create database zhu;
  5. ERROR 1223(HY000):Can't execute the query because you have a conflicting read lock

 

2.6记录锁表的位置点
  1. mysql> show master status;##查看锁表的位置点
  2. +------------------+----------+--------------+------------------+
  3. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
  4. +------------------+----------+--------------+------------------+
  5. | mysql-bin.000004|894|||
  6. +------------------+----------+--------------+------------------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql>

  2.7 锁表打包数据库

  1. [root@mysql ~]# mysqldump -uroot -p123456 --events -S /data/3306/mysql.sock -A -B|gzip>/opt/bak_$(date +%F).sql.gz
  2. [root@mysql ~]# ll /opt/
  3. 总用量148
  4. -rw-r--r--.1 root root 1443244月900:13 bak_2016-04-09.sql.gz

2.8 锁表打包完毕后,检查位置点有无变化,如果有变化,表示在打包的过程中有数据写入,锁表无效

  1. mysql> show master status;##锁表后位置点事894
  2. +------------------+----------+--------------+------------------+
  3. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
  4. +------------------+----------+--------------+------------------+
  5. | mysql-bin.000004|894|||
  6. +------------------+----------+--------------+------------------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> show master status;##锁表打包后,位置点没有变化,表示打包数据完成
  10. +------------------+----------+--------------+------------------+
  11. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
  12. +------------------+----------+--------------+------------------+
  13. | mysql-bin.000004|894|||
  14. +------------------+----------+--------------+------------------+
  15. 1 row in set (0.00 sec)

2.9 接着将锁表打开

  1. mysql> unlock tables;
  2. Query OK,0 rows affected (0.00 sec)
  1. mysql> create database zhu;##解锁以后可以写数据
  2. Query OK,1 row affected (0.03 sec)
  3.  
  4. mysql>

  2.9.1 查看生成的binlog文件  2.9.2 使用mysqlbinlog命令查看生成的binlog日志,mysqlbinlog 专门是将二进制语句翻译成sql语句的工具:

  1. [root@mysql 3306]# mysqlbinlog mysql-bin.000004

3.接着做恢复从库的工作

  1. [root@mysql opt]# cd /opt/
  2. [root@mysql opt]# ll
  3. 总用量664
  4. -rw-r--r--.1 root root 5282954月900:50 all-tmp.sql
  5. -rw-r--r--.1 root root 1443244月900:13 bak_2016-04-09.sql.gz
  6. drwxr-xr-x.2 root root 40963月262015 rh
  7. [root@mysql opt]# gzip -d bak_2016-04-09.sql.gz
  8. [root@mysql opt]# mysql -uroot -p123456 -S /data/3307/mysql.sock <bak_2016-04-09.sql
  9. [root@mysql opt]#

  3.1.1 接着在从库上执行change master to命令,实质实在从库上生成master.info文件的过程

  1. [root@mysql opt]# mysql -uroot -p123456 -S /data/3307/mysql.sock<<EOF
  2. stop slave;
  3. CHANGE MASTER TO
  4. MASTER_HOST='192.168.1.31',
  5. MASTER_PORT=3306,
  6. MASTER_USER='rep',
  7. MASTER_PASSWORD='123456',
  8. MASTER_LOG_FILE='mysql-bin.000004',
  9. MASTER_LOG_POS=894;
  10. EOF

   3.1.2 最后一步开启从库开关:

  1. mysql> start slave; ##打开开关
  2. Query OK,0 rows affected (0.00 sec)
  3.  
  4. mysql>

接着检查从库状态信息:

  1. mysql> show slave status \G;
  2. ***************************1. row ***************************
  3. Slave_IO_State:Waitingfor master to send event
  4. Master_Host:192.168.1.31
  5. Master_User: rep
  6. Master_Port:3306
  7. Connect_Retry:60
  8. Master_Log_File: mysql-bin.000004
  9. Read_Master_Log_Pos:975
  10. Relay_Log_File: relay-bin.000002
  11. Relay_Log_Pos:334
  12. Relay_Master_Log_File: mysql-bin.000004
  13. Slave_IO_Running:Yes
  14. Slave_SQL_Running:Yes
  15. Replicate_Do_DB:
  16. Replicate_Ignore_DB: mysql
  17. Replicate_Do_Table:
  18. Replicate_Ignore_Table:
  19. Replicate_Wild_Do_Table:
  20. Replicate_Wild_Ignore_Table:
  21. Last_Errno:0
  22. Last_Error:
  23. Skip_Counter:0
  24. Exec_Master_Log_Pos:975
  25. Relay_Log_Space:484
  26. Until_Condition:None
  27. Until_Log_File:
  28. Until_Log_Pos:0
  29. Master_SSL_Allowed:No
  30. Master_SSL_CA_File:
  31. Master_SSL_CA_Path:
  32. Master_SSL_Cert:
  33. Master_SSL_Cipher:
  34. Master_SSL_Key:
  35. Seconds_Behind_Master:0
  36. Master_SSL_Verify_Server_Cert:No
  37. Last_IO_Errno:0
  38. Last_IO_Error:
  39. Last_SQL_Errno:0
  40. Last_SQL_Error:
  41. Replicate_Ignore_Server_Ids:
  42. Master_Server_Id:1
  43. 1 row in set (0.00 sec)

3.1.3 最后测试主从   主库创建库:  从库:    最佳方案: 4.1 无须锁表的mysqldump备份命令       myisam 引擎企业生产备份命令: mysqldump -uroot -p123456 -A -B --mastet-data=2 -x --events|gzip >/opt /all.sql.gz       innodb 引擎企业生产备份命令:推荐使用的 mysqldump -uroot -p123456    -A     -B     -F     --mastet-data=2    --events   --single-transaction|gzip   >/opt/all.sql.gz  

  1. [root@mysql opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B --master-data=2-x --events >/opt/all-tmp.sql ##演示如下命令
  2. [root@mysql opt]# vim all-tmp.sql
  3. 其中--master-data=2,表示在前面加--
     
  4. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=975;##记录了增量备份的起点位置,主要是参数--master-data=2起的作用

  注: --master-data作用: 1、使用--master-data=2 进行备份文件会增加如下内容:适合普通备份增量恢复  

本文转载自:http://www.bkjia.com/Linuxjc/1118054.html

共有 人打赏支持
zyt_1978
粉丝 8
博文 166
码字总数 21058
作品 0
程序员
高手问答第 141 期 —— MySQL 主从复制的那些事

OSCHINA 本期高手问答(2017 年 2 月 13 日 — 2 月 19 日)我们请来了@叶金荣 和@吴炳锡为大家解答 MySQL 主从复制相关的问题。 @叶金荣 ,知数堂培训联合创始人,Oracle MySQL ACE,ACMUG...

凝小紫
2017/02/13
6.7K
40
Mysql主从复制

Mysql主从复制 背景: Mysql可以实现主从复制,在学习了Mysql主从复制后,将一些如何主从复制过程记录下来,供以后复习使用。 准备: 在做Mysql的主从复制前需要做一些准备工作: 1、同步时间...

657188918
2017/11/13
0
0
Mysql数据库AB复制简单实现

Mysql 主 从 复 制 在实际企业应用环境当中,单台mysql数据库是不足以满足日后业务需求的。譬如服务器发生故障,没有备份服务器来提供服务的话,业务就得停止。介于这种情况,我们可以对mys...

YLSL2014
07/04
0
0
MySQL的主从复制介绍及配置

1.MySQL主从复制介绍 MySQL数据库支持单向、双向、链式级联、环状等不同业务场景的复制。在复制过程中,一台服务器充当主服务器(Master),接收来自用户的内容更新,而一个或多个其他的服务...

10pcm
06/26
0
0
Mysql主从复制以及常见错误问题分析

Mysql主从复制以及常见错误问题分析 一、主从复制简介: 1、mysql主从复制原理: Mysql主从复制的实现,主要依赖于二进制日志来实现,过程主要是根据把主的MySQL 的数据复制到其它主机( Sla...

技术小疯子
01/22
0
0

没有更多内容

加载失败,请刷新页面

加载更多

面向对象设计原则(OOP)

单一职责原则(Single responsibility principle)又称单一功能原则。它规定一个类应该只有一个发生变化的原因。 核心原则:低耦合,高内聚。 一个类,应该只有一个引起它变化的原因,也就是...

gackey
24分钟前
2
0
C++ 锁

C++提供了两种常用的锁,std::lock_guard<Lockable &T>和std::unique_lock<Lockable &T>。通常使用场景下,这两个锁用法一致。即,在构造锁对象时上锁,在析构锁对象时解锁。使用户从上锁/解...

yepanl
27分钟前
1
0
Kali Linux Docker 練習

docker pull kalilinux/kali-linux-docker docker run -t -i kalilinux/kali-linux-docker /bin/bash apt-get update apt-get install htop apt-get install nmap apt-get install wpscan ap......

BaiyuanLab
今天
1
0
通俗大白话来理解TCP协议的三次握手和四次分手

最近在恶补计算机网络方面的知识,之前对于TCP的三次握手和四次分手也是模模糊糊,对于其中的细节更是浑然不知,最近看了很多这方面的知识,也在系统的学习计算机网络,加深自己的CS功底,就...

onedotdot
今天
2
0
TiDB 在爱奇艺的应用及实践

爱奇艺,中国高品质视频娱乐服务提供者,2010 年 4 月 22 日正式上线,推崇品质、青春、时尚的品牌内涵如今已深入人心,网罗了全球广大的年轻用户群体,积极推动产品、技术、内容、营销等全方...

TiDB
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部