设为首页收藏本站

LinuxTone | 运维专家网论坛 - 最棒的Linux运维与开源架构技术交流社区!

 找回密码
 注册

QQ登录

只需一步,快速开始

#公告#抱歉,网站将关闭,不再开放。由于PC时代已远逝 。在这个后移动互联网时代,我们继续携手前行,保持对技术的热情。共同构建linuxtone知识星球欢迎加入,一起讨论技术、招聘人才、分享资源。请新老linuxtone人 扫码移步到 知识星球:linuxtone

学习是一种信仰!分享是一种快乐!能力= 心态 * 沟通 * 知识 (你的每一天需要正能量!)

 网站的发展需要你贡献一份力量!希望你能每天坚持看贴1小时,并回答网友的问题!祝你在浏览论坛的过程中取得进步!谢谢!linuxtone加油!大家加油! 友情提示: 你今天学习了吗?你今天进步了吗?少一点抱怨!多一点进步!Life is short ! Why not linuxtone ?  

网站的发展、感谢每位坛友的努力!

查看: 8341|回复: 6

[MySQL管理] mysql主从复制,读写分离 [复制链接]

Rank: 9Rank: 9Rank: 9

签到
231
注册时间
1970-1-1
最后登录
2015-12-2
在线时间
170 小时
阅读权限
200
积分
19977
帖子
119
主题
35
精华
2
UID
9223

LT总司令 LT元老 LT教授

发表于 2010-7-17 11:15:43 |显示全部楼层
本帖最后由 kindle 于 2010-7-17 12:24 编辑

看到群里有朋友在问,我也发点以前整理得
环境:freebsd7.2
用到的有:俩个mysql,mysql-proxy,和ytjwt写的一个中间件,详见http://bbs.linuxtone.org/thread-6754-1-1.html
不废话了,占笔记了

MySQL 提供了数据库的同步功能,这对我们实现数据库的冗灾、备份、恢复、负载均衡等都是有极大帮助的。本文描述了常见的同步设置方法。
一、准备服务器
由于MySQL不同版本之间的(二进制日志)binlog格式可能会不一样,因此最好的搭配组合是Master的 MySQL版本和Slave的版本相同或者更低,Master的版本肯定不能高于Slave版本。
本文中,我们假设主服务器(以下简称Master)和从服务器(以下简称Slave)的版本都是5.0.15,操作系统 是Linux Ubuntu 5.0.x。
假设同步Master的主机名为:rep1,Slave主机名为:rep2,2个MySQL的basedir目录都是 /usr/local/mysql,datadir都是:/usr/local/mysql/data。
二、设置同步服务器
1、设置同步Master
每个同步服务器都必须设定一个唯一的编号,否则同步就不能正常运行了。接下来开始修改 my.cnf,增加以下几行:
  1. server-id = 1
  2. log-bin
  3. set-variable=binlog-ignore-db=mysql
复制代码

然后在Master上增加一个账号专门用于同步,如下:
  1. mysql>GRANT REPLICATION SLAVE ON *.* TO rep@rep2 IDENTIFIED BY 'rep';
  2. 如果想要在Slave上有权限执行 "LOAD TABLE FROM MASTER" 或 "LOAD DATA FROM MASTER" 语句的话,必须授予全局的 FILE 和 SELECT 权限:
  3. mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO rep@rep2 IDENTIFIED BY 'rep';
  4. 第三行表示不记录数据库mysql的更新日志,这就避免了Master上的权限设置等被同步到Slave 上,如果对这方面没有限制,就可以不设置这个参数。
  5. 接下来备份Master上的数据,首先执行如下SQL语句:
  6. mysql>FLUSH TABLES WITH READ LOCK;
  7. 不要退出这个终端,否则这个锁就不生效了;接着导出数据,可以直接打包压缩数据文件,也可以使 用mysqldump工具来做,推荐前者的方法,这样更为快捷简便。
  8. root$cd /usr/local/mysql
  9. root$tar zcf data.tar.gz ./data (在这里也可能是 "var" 等其它实际存放数据文件的目录,根据实情而定)
  10. 然后将这些数据拷贝到Slave服务器上,解开,设置好正确的权限及属主等;之后,执行 "UNLOCK TABLES" 语句来释放锁。
复制代码

2、设置Slave
修改my.cnf,增加如下几行:
  1. server-id = 2
  2. master-host = rep1 #主服务器名
  3. master-user = rep  #同步账户名,默认是test
  4. master-password = rep #同步帐户密码,默认是空
  5. master-port = 3306 #主服务器的 TCP/IP 端口号,默认是3306
  6. set-variable=replicate-ignore-db=mysql #略过同步的数据库名,如果有多个,请设置多次
  7. set-variable=replicate-do-db=yejr #想要同步的数据库名,如果有多个,请设置多次
复制代码

接下来在Slave上检验一下是否能正确连接到Master上,并且具备相应的权限。
  1. root$mysql -hrep1 -urep -prep
  2. mysql>SHOW GRANTS;
  3. +------------------------------------------------------------------------------+
  4. | Grants for rep@rep2                                                                                                                  |
  5. +------------------------------------------------------------------------------+
  6. | GRANT SELECT, FILE, REPLICATION SLAVE ON *.* TO 'rep'@'rep2' IDENTIFIED BY
  7. PASSWORD '*9FF2C222F44C7BBA5CC7E3BE8573AA4E1776278C'      |
  8. +------------------------------------------------------------------------------+
  9. 现在,可以启动Slave了。启动成功后,登录Slave,查看一下同步状态:
  10. mysql -hlocalhost -uroot
  11. mysql>SHOW SLAVE STATUS\G
  12. *************************** 1. row ***************************
  13.              Slave_IO_State: Waiting for master to send event
  14.                 Master_Host: rep1
  15.                 Master_User: rep
  16.                 Master_Port: 3306
  17.               Connect_Retry: 60
  18.             Master_Log_File: binlog.000001
  19.         Read_Master_Log_Pos: 98
  20.              Relay_Log_File: relay.000003
  21.               Relay_Log_Pos: 232
  22.       Relay_Master_Log_File: binlog.000001
  23.            Slave_IO_Running: Yes
  24.           Slave_SQL_Running: Yes
  25.             Replicate_Do_DB:
  26.         Replicate_Ignore_DB:
  27.          Replicate_Do_Table:
  28.      Replicate_Ignore_Table:
  29.     Replicate_Wild_Do_Table:
  30. Replicate_Wild_Ignore_Table:
  31.                  Last_Errno: 0
  32.                  Last_Error:
  33.                Skip_Counter: 0
  34.         Exec_Master_Log_Pos: 98
  35.             Relay_Log_Space: 232
  36.             Until_Condition: None
  37.              Until_Log_File:
  38.               Until_Log_Pos: 0
  39.          Master_SSL_Allowed: No
  40.          Master_SSL_CA_File:
  41.          Master_SSL_CA_Path:
  42.             Master_SSL_Cert:
  43.           Master_SSL_Cipher:
  44.              Master_SSL_Key:
  45.       Seconds_Behind_Master: 0
  46. 1 row in set (0.00 sec)
复制代码

可以看到,Slave_IO_Running 和 Slave_SQL_Running 两列的值都为 "Yes",这表明 Slave 的 I/O 和 SQL 线程都在正常运行。
至此,同步设定成功。


配置mysql-proxy,事先要装libevent,我是pkg_add装的
  1. fetch http://www.lua.org/ftp/lua-5.1.2.tar.gz
  2. tar zxvf lua-5.1.2.tar.gz
  3. cd lua-5.1.2
  4. make freebsd
  5. make install
  6. fetch http://mysql.cdpa.nsysu.edu.tw/D ... d7-x86-32bit.tar.gz
  7. tar zxvf mysql-proxy-0.8.0-freebsd7-x86-32bit.tar.gz
  8. mkdir /data/soft/mysql-proxy
  9. mv mysql-proxy-0.8.0-freebsd7-x86-32bit/* /data/soft/mysql-proxy
  10. 编辑 .cshrc增加/data/soft/mysql-proxy/bin到path环境变量最后面
  11. source ~/.cshrc
  12. 下载rw-splitting-yt.lua放到/data/soft/mysql-proxy/下
  13. 执行:mysql-proxy -b 192.168.8.42:3306 -r 192.168.8.43:3306 --proxy-lua-script=rw-splitting-yt.lua
  14. 测试:mysql -u root -ppassword -P 4040 -h 192.168.8.42 -e "show databases"(测试的时候需多次执行)
复制代码

下面检查下是否是master写,slave读,这个很简单不用我多说了,:-)

Rank: 3Rank: 3

注册时间
2009-10-17
最后登录
2019-4-20
在线时间
798 小时
阅读权限
30
积分
391
帖子
325
主题
313
精华
0
UID
5964
发表于 2010-7-17 11:24:24 |显示全部楼层
好东东……mysql-proxy 之前版本都会出现cant connaction mysql的错误。0.8对rw脚本进行了bug修复,没有这个错误了。请教一个问题

未用mysql-proxy时权限设置可以针对来源主机IP进行配置

但用了mysql-proyx后,所有的访问都被解析成127.0.0.1来访问,mysql-proxy对连接者的IP如何做限制呢?

使用道具 举报

Rank: 7Rank: 7Rank: 7

注册时间
2008-11-27
最后登录
2019-3-21
在线时间
208 小时
阅读权限
100
积分
6501
帖子
772
主题
367
精华
4
UID
632

LT同学

发表于 2010-7-17 22:07:31 |显示全部楼层
本帖最后由 ytjwt 于 2010-7-17 22:11 编辑

回复 2# liguxk
纠正下你的:(所有的访问都被解析成127.0.0.1来访问),应该是解析成proxy的地址,也就是说,你的用户,proxy机器必须能访问。
你可以在proxy 脚本的connect_server()  read_auth()这两个函数中对用户、password、IP,作出适合你自己的控制,

mysql-proxy的强大之处,可能也就在此,写出适合你自己的脚本。

使用道具 举报

Rank: 9Rank: 9Rank: 9

注册时间
1970-1-1
最后登录
2015-12-2
在线时间
170 小时
阅读权限
200
积分
19977
帖子
119
主题
35
精华
2
UID
9223

LT总司令 LT元老 LT教授

发表于 2010-7-18 12:07:30 |显示全部楼层
本帖最后由 kindle 于 2010-7-18 17:22 编辑

回复 3# ytjwt


    补充下:让他做别的事情的思路??防止注入攻击。
启动MySQL Proxy的时候,加载一个Lua脚本,对每一个进入的query或者insert之类的语句做一次安全检查,甚至替换查询中的某些内容,这样在程序员的 程序中忘记了过滤参数的情况下,还有最后一道防线可用。而且由于是Lua这样的动态脚本语言,在开发,修正,部署方面都会有极大的灵活性。当然,或许会担 心性能方面的问题,那么在前面加memcached吧,或者干脆用c来写这样的扩展,毕竟MySQL Proxy是开源的,而且有清晰的接口。

MySQL Proxy提供给Lua的接口主要有以下几个函数:

connect_server() ? 这个函数每次client连接的时候被调用,可以用这个函数来处理负载均衡,决定当前的请求发给那个后台的服务器,如果没有指定这个函数,那么就会采用简单的轮询机制。

read_handshake() ? 这个函数在server返回初始握手信息时被调用,可以调用这个函数在验证信息发给服务器前进行额外的检查。

read_auth() ? client发送验证信息给服务器的时候会调用这个函数。

read_auth_result() ? 服务器验证信息返回后调用这个函数。

read_query() ? 每次client发送查询请求函数的时候被调用,可以用这个函数进行查询语句的预处理,过滤掉非预期的查询等等,这个是最常用的函数。

read_query_result() ? 查询结果返回是调用的函数,可以进行结果集处理。

http://code.google.com/p/mysql-ids

使用道具 举报

Rank: 8Rank: 8

注册时间
2009-11-17
最后登录
2015-9-26
在线时间
361 小时
阅读权限
90
积分
56805
帖子
518
主题
25
精华
1
UID
6686
发表于 2010-7-20 19:08:07 |显示全部楼层
好强大

使用道具 举报

Rank: 6Rank: 6

注册时间
2009-11-25
最后登录
2017-5-9
在线时间
97 小时
阅读权限
70
积分
6836
帖子
84
主题
2
精华
0
UID
6829
发表于 2010-7-23 11:09:54 |显示全部楼层
好深奥,还要继续学习,呵呵

使用道具 举报

Rank: 1

注册时间
2011-6-6
最后登录
2011-6-10
在线时间
1 小时
阅读权限
1
积分
9
帖子
2
主题
0
精华
0
UID
14114
发表于 2011-6-10 16:04:39 |显示全部楼层
:):):):):):D

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

IT运维专家网感谢您的支持

合作联系: QQ:67888954/MSN:cnseek@msn.com/mail:netseek@linuxtone.org

Archiver|手机版|感谢所有关心和支持过LinuxTone的朋友们 转载本站内容请注明原作者名及出处 ( 京ICP备08103151 )   |

GMT+8, 2019-4-26 15:49 , Processed in 0.054945 second(s), 14 queries , Apc On.

Powered by Discuz! X2 Licensed

© 2001-2011 Comsenz Inc.

回顶部