MySQL数据库优化之主从复制

MySQL数据库优化之主从复制

思想部分

(喜欢实战的可以先看实战)

  1. MySQL复制能解决什么问题

    • MySQL的负载均衡(分担读负载)
    • 为高可用 灾难恢复 备份提供更多的选择
    • 读写分离
  2. 主从配置优点

    • 解决应用系统,数据库出现的性能瓶颈,采用数据库集群的方式来实现查询负载;通常情况下一个系统中数据库的查询操作比更新操作要多得多,通过多台查询服务器将 数据库的查询分担到不同的查询服务器上从而提高查询效率。
    • 数据备份,高可用性和故障切换,增强了数据库的安全性,利用从库备份来减少主库负载,从库复制并不能代替备份.,备份是有必要的
    • 实现数据库的在线升级
  3. MySQL日志
    MySQL有很多种日志分为两类

    • MySQL存储引擎日志

      Innodb: 重做日志 , 回滚日志

      • MySQL服务层日志(默认只有error日志,其他的需要在配置文件中手动开启) 慢查询日志、二进制日志(习惯称bin_log日志)、通用日志等
  4. 二进制日志

    • 记录了所有对MySQL数据库的修改事件,包括增删改查事件和对表结构的修改事件
    • 没有执行成功的SQL是不会记录到二进制日志 还有就是当你重启了mysql之后,会从新生成一个二进制文件,旧的会保存一段时间
  5. 复制如何工作

    • 在主库上把数据更改记录到二进制日志(bin-log)中(这些记录日志被称为二进制日志事件)
    • 从库将主库上的日志复制到自己中继日志
    • 从库读取自己的中继日志中的事件,将其重放到自己数据上
流程

6. 一主多从

本章实例仅展示一主一从, 一主多从是指,多台从库;其中从库不再走主服务器,而是挂在从服务器上。思想架构基本一致,大致结构如下:
一主多从

实战开始

笔者按量购买了两台阿里云服务器,1小时4毛钱

image

两台机器都需要安装mysql

主库和从库分别执行以下命令:

apt-get update
apt-get install mysql-server

登陆主库,创建一个从库可以访问的帐号

进入mysql,输入以下命令创建用户

grant replication slave on *.* to 'USERNAME'@'IP' identified by 'PASSWORD';

Username(帐号)、IP(服务器地址)、Password(密码)需要自己指定。
mysql库中user表的数据

同源策略,将主库的库文件复制到从库

注意,先要停止Mysql服务,主库从库都要停止。

service mysql stop

库位置/var/lib/mysql,mysql文件夹就是这个所有的库,需要把这个文件夹复制到从库。现将这个文件进行压缩。

tar -zcvf mysql.tar.gz ./mysql/

然后,复制到从库。下面这条命令是超服务器传输的复制命令

scp ./mysql.tar.gz UserName@IP:/tmp

Username 换成主库的帐号,IP主库的IP,/tmp是复制到主库的/tmp文件夹下,当然可以自己指定。 首先进入从库,查看从库是否有了这个压缩文件,并进行移动到从库的/var/lib/

cd /tmp/
mv mysql.tar.gz /var/lib/

将从库mysql文件夹进行备份,以防失败。

cd /var/lib/
mv mysql mysql.bak

将主库传过来的mysql.tar.gz 解压,这就完成了同源策略

tar -zxvf mysql.tar.gz

这时,就可以主从就都可以开启mysql服务了。

service mysql start

配置主库和从库

主库配置/etc/mysql/my.cnf

[mysqld] 
server-id               = 1  
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog-ignore-db=mysql   //不进行主从复制的库
# 将bind-address注释,为了能够使用从库服务器IP登陆主库
#bind-address           = 127.0.0.1

从库配置/etc/mysql/my.cnf

[mysqld] 
server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog-ignore-db=mysql

注意配置文件可能因为版本不同,而产生配置文件不对应,只需要自己去找一下,配置中包含[mysqld]选项的进行修改就可以了。

配置主从库登陆信息

在主库执行命令,查看bin-log日志写到了什么位置

show master status;

查看bin-log

查看到了File和Position,我们会用到这两个值。 接下来,在从库中执行面的命令。配置主库的信息

CHANGE MASTER TO MASTER_HOST ='120.24.47.140',MASTER_USER = 'test1', MASTER_PASSWORD='123456',MASTER_LOG_FILE = 'mysql-bin.000001',MASTER_LOG_POS=107;

参数解析

MASTER_HOST:主服务器IP

MASTER_USER:在主服务器中创建一个帐号,笔者是test1

MASTER_PASSWORD:自己定义的密码,笔者是123456

MASTERLOGFILE:上一步查看的FILE文件,笔者是mysql-bin.000001

MASTERLOGPOS:上一步查看的Postion位置,笔者是107

最后,成功的最后一步

启动复制

mysql > START SLAVE;

停止复制

STOP SLAVE;

查看状态

SHOW SLAVE STATUS;

主从复制成功.png

扩展:Laravel中读写分离的实现
image
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 155,704评论 4 358
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 66,084评论 1 286
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 105,560评论 0 237
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 43,360评论 0 201
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 51,686评论 3 285
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 40,150评论 1 204
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,528评论 2 306
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 30,256评论 0 193
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,878评论 1 235
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,215评论 2 239
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,757评论 1 255
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 28,121评论 2 249
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,659评论 3 228
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,932评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,638评论 0 191
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 35,159评论 2 263
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 35,112评论 2 258

推荐阅读更多精彩内容