MySQL数据库主从(MasterSlave)配置详细步骤
⼀、背景
服务器上放了很多MySQL数据库,为了安全,现在需要做Master/Slave⽅案,因为操作系统是Window的,所以没有办法使⽤keepalived 这个HA⼯具,但是我们可以接受⼈⼯进⾏切换,有什么好的⽅案呢?
⼆、⼏种Master/Slave逻辑架构图
(Figure1:单Master-单Slave)
(Figure2:单Master-多Slave)
(Figure3:单Master-级联Slave)
(Figure4:Master/Slave部署逻辑图)
三、搭建过程
环境:Windows Server 2008 R2 + mysql-5.5.22-winx64
主服务器(Master):47.98.142.170
从服务器(Slave):23.13.5.206
使⽤Master/Slave架构,Slave为只读模式;
(⼀) 配置主服务器(Master)
1. 编辑数据库配置⽂件my.ini,在[mysqld]标签下⾯加⼊下⾯代码(嫌⿇烦的就加红⾊的两个就⾏):
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog_do_db=barfoo_weibo_hottop
binlog_ignore_db=mysql
说明:
1) log-bin=mysql-bin启⽤Binary Log,会在数据⽂件夹中⽣成⼀系列的滚动⽂件,类似:mysql-bin.000002
2) server-id=1中的1可以任定义,只要是唯⼀的就⾏,为了与其它Master和Slave进⾏区别;
3) innodb_flush_log_at_trx_commit = 0,每秒写⼀次trax log,并执⾏fsync;
innodb_flush_log_at_trx_commit = 1,每次trax 提交的时候写⼀次trax log, 并执⾏fsync;
innodb_flush_log_at_trx_commit = 2,每次trax 提交的时候写⼀次trax log, 不会执⾏fsync;
4) sync_binlog=1表⽰异步进⾏⽇志记录;
5) binlog_do_db=barfoo_weibo_hottop是表⽰只对数据barfoo_weibo_hottop做⽇志记录,如果想记录多个数据库,添加多⼏条binlog_do_db就可以了;
6) binlog_ignore_db=mysql表⽰忽略备份mysql;不加binlog_do_db和binlog_ignore_db,那就表⽰备份全部数据库。
2. 重启MySQL服务
3. 在Master服务器新建⼀个⽤户赋予“REPLICATION SLAVE”的权限,你不需要再赋予其它的权限,这⾥指定的IP为Slave的IP;
mysql>CREATE USER viajar@'23.13.5.206' IDENTIFIED BY 'abc2013';
mysql>GRANT REPLICATION SLAVE ON *.* TO viajar@'23.13.5.206' IDENTIFIED BY 'abc2013';
mysql>GRANT file ON *.* TO viajar@'23.13.5.206' IDENTIFIED BY 'abc2013';
4. 在Master服务器执⾏下⾯的脚本,让数据库只能读;
mysql>FLUSH TABLES WITH READ LOCK;
5. 拷贝数据库⽂件夹和ibdata1⽂件到本地临时⽂件夹中,马上执⾏下⾯的步骤;
每新建⼀个数据库会在data⽂件夹中⽣成⼀个数据库⽂件夹,这⾥我们需要拷贝的数据库⽂件夹就是主库中需要同步到从库的数据库对应的⽂件夹(如果需要将主库中的⽤户同步到从库也可以将主库中的mysql⽂件夹拷贝到从库的data⽂件夹下)
6. 查看Master服务器的状态,记下File及Position的值,后⾯设置Slave服务器的时候需要⽤到(注意安装MySQL的时候,安装板安装好的MySQL之后再⽤配置⽂件的install会出现两个MySQL服务,如果下⾯的命令没有结果,说明有可能MySQL服务启动错了,关闭当前MySQL服务,启动另外⼀个,就能解决此问题);
mysql>SHOW MASTER logs;
mysql-bin.000001 706(命令执⾏结果)
mysql>SHOW MASTER STATUS;简述安装mysql的过程
(Figure1:Master状态)
7. 对表进⾏解锁,恢复数据库读写;
mysql>UNLOCK TABLES;
8. 使⽤FTP等⼯具把第5步骤的临时⽂件传到Slave服务器的mysql的data⽂件夹中;
(⼆) 配置从服务器(Slave)
1. 编辑数据库配置⽂件my.ini,在[mysqld]的下⾯加⼊下⾯代码,server-id可以⾃⼰定义数值,只要保证唯⼀的就⾏,与Master的能区分开就⾏;
server-id=2
read-only=1
2. 重启MySQL
3. 登录MySQL服务器,执⾏下⾯的脚本命令(创建与master服务器的连接):
mysql>CHANGE MASTER TO
MASTER_HOST='47.98.142.170',
MASTER_USER='viajar',
MASTER_PASSWORD='abc2013',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=706,
MASTER_CONNECT_RETRY=30;
说明:
1) MASTER_HOST:Master服务器的IP;
2) MASTER_USER:配置主服务器时建⽴的⽤户名;
3) ASTER_PASSWORD:⽤户名对应的密码;
4) ASTER_PORT:主服务器mysql端⼝,如果未曾修改,默认即可;
5) MASTER_LOG_FILE:log⽂件名,参考Figure1;
6) MASTER_LOG_POS:只上次备份时的log⽂件的偏移量,参考Figure1;
7) MASTER_CONNECT_RETRY:重新连接Master的时间间隔,单位为秒;
4. 启动Slave进程,执⾏下⾯的脚本命令;
mysql>START SLAVE;
5. 检查主从同步状态,执⾏下⾯的脚本命令,其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES,才表明状态正常。
mysql>SHOW SLAVE STATUS;
(Figure2:Slave状态1)
(Figure3:Slave状态2)
6. 查看Slave服务器的MySQL⽇志;
(Figure4:Slave⽇志⽂件)
四、补充说明
1. 查看帮助
mysql>help
2. 查看slave帮助
mysql>help slave
3. 查当前有哪些⼆进制⽇志⽂件
mysql> show binary logs;
4. 删除mysql-bin.000018之前的所有⼆进制⽇志⽂件
mysql> purge binary logs to 'mysql-bin.000018';
5. 从 MySQL 5.1.12 开始,能够⽤以下三种形式来告终:基于SQL语句的复制(statement-based replication, SBR),基于⾏的复制(row-based replication, RBR),混杂形式复制(mixed-based replication, MBR)。相应地,binlog的款式也有三种:STATEMENT,ROW,MIXED。MBR 形式中,SBR 形式是默认的。
#binlog_format="STATEMENT"
#binlog_format="ROW"
#binlog_format="MIXED"
当然了,也能够在运⾏时动态修正binlog的款式。例如
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';
6. MySQL同步故障:Slave_SQL_Running:No
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;
五、疑问
(⼀) 复制级别有以下⼏种,默认级别是那种?如何显式设置这个值?
1. Row Level:5.1.5开始⽀持。mater记录每⾏数据的更改⽇志,slave根据⽇志逐⾏应⽤。优点:数据⼀致性更有保障。缺点:可能造成⽇志⽂件⽐较⼤;
2. Statement Level:master记录每个执⾏的query语句以及⼀些上下⽂信息,slave节点根据这些信息重新在slave上执⾏。优点:binary log⽐较⼩。缺点:某些情况下数据⼀致性难以保障;
3. Mixed Level:MySQL根据情况选择哪种复制⽅式。5.1.8开始⽀持。
(⼆) 常⽤架构有以下⼏种,我想要的效果是:当MasterA宕机之后,MasterB还能读写,在MasterA在恢复之后重新让MasterA成为主Master。
1. Master-Slaves:通常都采⽤这种⽅式;
2. Dual Master(Master-Master):2个master节点互相同步更新。因为MySQL的异步复制⽅式,为了防⽌数据冲突造成的不⼀致性,⼀般仅将其中⼀台⽤于写操作,另⼀台不⽤或仅⽤于读操作。⽬的是其中⼀台master停机维护或者故障中断时可以使⽤另⼀台master;
3. 级联复制(Master-Slaves-Slaves):在Master Slaves中,如果slaves过多replication将增加master的
负载,这时可以让master只向其中⼏台slave分发更新⽇志,这⼏台slave作为⼀级节点再向下级节点分发更新⽇志。
(三) 如果slave报错或者宕机⼀段时间,那么应该如何重新同步宕机时间master的数据到slave呢?
(四) 在Master设置binlog_do_db=test的时候,如何可以设置记录多个数据库?
解答:直接在配置⽂件my.ini加⼊多条binlog_do_db就可以了;
(五) 每天的⽇志⽂件就有10GB,如何清理?如何维护?有没什么设置或者⽅法可以减少⽇志量呢?
⽂章乃参考、转载其他博客所得,仅供⾃⼰学习作笔记使⽤
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论