MySQL企业级主从复制
来源:互联网 发布:2017java最新面试题 编辑:程序博客网 时间:2024/06/06 09:46
====================================================
一 生产环境主从节点复制配置注意事项
1.1 第一次如何做?
>> 申请设备资源,用来做从库服务器
>> 撰写文档方案和实施步骤
假如你的服务器只有主库,而且已经跑了生产线上的应用了
现在由于业务需要第一次做从库,此时可能需要和公司申请
停机维护,即再用户访问量很小的时候,且不影响内部其他
业务运转时间点来停机配置
也可以不申请停机,在定时任务备份的时候,每天都夜里的
定时备份做一些措施也可以
1.1.1 一键备份和获取主节点binlog位置的脚本
>>锁表备份全备份一份 -A -B --single-transaction -E
>>锁表前后取得show master status值
方法一:
#! /bin/sh
####################################################
MYSQL_USER=root
MYSQL_PASS=123456
LOG_BAK_PATH=/opt/bak
DATA_PATH=/opt/bak
LOG_FILE=$DATA_PATH/mysqllogs_`date +%F`.log
DATA_FILE=$DATA_PATH/mysql_backup_`date +%F`.sql.gz
MYSQL_PATH=/usr/local/mysql/bin
MYSQL_CMD="${MYSQL_PATH}/mysql -u${MYSQL_USER} -p
${MYSQL_PASS}"
MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${MYSQL_USER}
-p${MYSQL_PASS} -A -B -E --single-transaction"
${MYSQL_CMD} -e "FLUSH TABLE WITH READ LOCK;"
echo "##show master status result##" >>$LOG_FILE
$MYSQL_CMD -e "SHOW MASTER STATUS;" >>$LOG_FILE
$MYSQL_DUMP | gzip > $DATA_FILE
$MYSQL_CMD -e "UNLOCK TABLE"
mail -s "mysql slave log"530075499@qq.com<$LOG_FILE
有个问题,$MYSQL_CMD -e "SHOWMASTER STATUS;" >>
$LOG_FILE 执行完这个窗口退出了,锁不住表
方法二:
#! /bin/sh
####################################################
MYSQL_USER=root
MYSQL_PASS=123456
LOG_BAK_PATH=/opt/bak
DATA_PATH=/opt/bak
LOG_FILE=$DATA_PATH/mysqllogs_`date +%F`.log
DATA_FILE=$DATA_PATH/mysql_backup_`date +%F`.sql.gz
MYSQL_PATH=/usr/local/mysql/bin
MYSQL_CMD="${MYSQL_PATH}/mysql -u${MYSQL_USER} -p
${MYSQL_PASS}"
MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${MYSQL_USER}
-p${MYSQL_PASS} -A -B -E --single-transaction"
cat |$MYSQL_CMD<<EOF
FLUSH TABLE WITH READ LOCK;
SYSTEM echo "##show master status result##">>$LOG_FILE
SYSTEM $MYSQL_CMD -e "SHOW MASTER STATUS"|tail -l>>
$LOG_FILE
SYSTEM ${MYSQL_DUMP} |gzip > $DATA_FILE;
$MYSQL_CMD -e "SHOW MASTER STATUS;" >>$LOG_FILE
$MYSQL_CMD -e "UNLOCK TABLE;"
EOF
方法三:
#! /bin/sh
####################################################
MYSQL_USER=root
MYSQL_PASS=123456
LOG_BAK_PATH=/opt/bak
DATA_PATH=/opt/bak
LOG_FILE=$DATA_PATH/mysqllogs_`date +%F`.log
DATA_FILE=$DATA_PATH/mysql_backup_`date +%F`.sql.gz
MYSQL_PATH=/usr/local/mysql/bin
MYSQL_CMD="${MYSQL_PATH}/mysql -u${MYSQL_USER} -p
${MYSQL_PASS}"
MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${MYSQL_USER}
-p${MYSQL_PASS} -A -B -E --master-data=1
--single-transaction"
$MYSQL_DUMP |gzip > $DATA_FILE
1.1.2 不停主节点一键批量创建从库
#! /bin/sh
####################################################
MYSQL_USER=root
MYSQL_PASS=123456
MYSQL_HOST=192.168.199.210
MYSQL_PORT=3306
MYSQL_REP_USER=rep
MYSQL_REP_PASS=123456
LOG_BAK_PATH=/opt/bak
DATA_PATH=/opt/bak
LOG_FILE=$DATA_PATH/mysqllogs_`date +%F`.log
DATA_FILE=$DATA_PATH/mysql_backup_`date +%F`.sql.gz
MYSQL_PATH=/usr/local/mysql/bin
MYSQL_CMD="${MYSQL_PATH}/mysql -u${MYSQL_USER} -p
${MYSQL_PASS}"
#倒入或者恢复数据
cd $DATA_PATH
gzip -d mysql_backup_`date +%F`.sql.gz
$MYSQL_CMD < mysql_backup_`date +%F`.sql
#配置从节点
cat |$MYSQL_CMD<<EOF
CHANGE MASTER TO
MASTER_HOST='$MYSQL_HOST',
MASTER_PORT=$MYSQL_PORT,
MASTER_USER='$MYSQL_REP_USER',
MASTER_PASSWORD='$MYSQL_REP_PASS';
####为什么不需要binlog文件和点,因为之前dump 使用
master-data=1了;如果没有指定,需要写脚本去取出来
EOF
$MYSQL_CMD -e "START SLAVE;"
$MYSQL_CMD -e "SHOW SLAVE STATUS\G;"|egrep
"IO_Running|SQL_Running" >> $LOG_FILE
mail -s "MySQL SLAVE RESULT"530075499@qq.com<$LOG_FILE
====================================================
二 生产环境MySQL主从复制读写分离授权方案及实战
2.1 生产MySQL复制环境用户授权方案
从库:连接用户授权上控制
web程序写指向主节点,读指向从库
法一:
生产环境主库用户授权:
GRANT SELECT,INSERT,UPDATE,DELETE ON '$db_name'.* TO
'$user'@'$IP' IDENTIFIED BY '$pass';
生产环境从库用户授权:
GRANT SELECT,INSERT,UPDATE,DELETE ON '$db_name'.* TO
'$user'@'$IP' IDENTIFIED BY '$pass';
REVOKE INSERT,UPDATE,DELETE ON '$db_name'.* FROM
'$user'@'$IP';
我们可以不回收权限:通过read-only参数防止数据写从节
点
法二:通过忽略授权表的方式防止数据写从库的方法及实践
也不和开发约定,直接让开发在从库写不了
只对从服务器用户授权SELECT权限,不对mysql库进行同步
这样可以保证主节点和从节点相同的用户可以
授权不同的权限
replicate-ignore-db=mysql
#binlog-do-db=hadoop//仅仅同步某一个库都可以
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
风险:当从库替换主库,就有权限问题了;
解决:保留一个从节点专门接替主;通过read-only参数防
止数据写从节点,但是得授予所有权限
实践:
主节点:vim /etc/my.cnf
replicate-ignore-db=mysql
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
然后重启,生效
法三:通过read-only参数防止数据写从节点
也就是主从节点用户一样,权限一样,只需要设置read-only
既可以确保从节点只读。
在slave节点启动选项增加参数或者再my.cnf配置文件种加
read-only参数来确保从节点只读,当然授权用户和read-on
ly参数同时操作更好
read-only参数可以让slave节点只允许来自slave服务器线程
或具有SUPER权限的用户更新。可以确保slave节点不接受来自
普通用户的更新,SLAVE节点启动选项增加--read-only 也是
同样的功能
实践:
从节点:vim /etc/my.cnf
[mysqld]
read-only
注意:不能有super权限
====================================================
三 生产环境主从复制故障解决
3.1 从库数据冲突导致同步停止
Slave_IO_Running:Yes
Slave_SQL_Running:No
Sedonds_Behind_Master:NULL
就是从库还在取binlog,但是SQL 线程已经不应用了
重现步骤:
slave节点添加一个数据库:
CREATE DATABASE abc;
检查从库状态:
show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
然后在主Master点添加一个同名数据库
CREATE DATABASE abc;
在检查从库状态:
show slave status\G;
Slave_IO_Running:Yes
Slave_SQL_Running:
Error 'Can't create database 'abc'; database exists'
on query. Default database: 'abc'. Query: 'create
database abc'
解决方案一:sql_slave_skip_counter=N 从relay-log忽略
N个位置点
第一步:先停止同步
stop slave;
set global sql_slave_skip_counter=1;
start slave;
在查询slave状态:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
取决于公司业务对主从同步要求高还是数据一致性高
解决方案二:slave-skip-errors=1007 根据错误号跳过
指定的错误
slave-skip-errors=1032,1062,1007 忽略错误,没必要
解决的错误可以在my.cnf中配置
然后重启mysql;
====================================================
四 从库需要记录binlog
场景1:主从复制级联,就是当前从库作为下一个从库的主
场景2:从库也需要备份的时候
方案:
my.cnf:
#表示从库记录binlog
log-slave-updates
log-bin=/usr/local/mysql/data/mysql-bin
#自动保留binlog文件的时间,主库也可以有
expire_logs_days=7
====================================================
五 生产环境Master 节点宕机
分为数据库宕机和服务器宕机
假设需要切换从库:
第一 在从库showprocesslist\G;
查看状态:
mysql> show processlist\G;
*************************** 1. row ******************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 1150
State: Slave has readall relay log; waiting for the
slave I/O thread to update it
Info: NULL
*************************** 2. row *******************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 1202
State: Waiting formaster to send event
Info: NULL
*************************** 3. row *******************
如果从库是这个状态。说明主从时同步的
然后查看所有从库master info 信息,哪一个位置点更大,
说明更快。然后用这个机器作为Master.
切换过程:
>> 确保所有从库relay-log全部更新完毕,即:
stop slave io_thread;
show processlist\G;
直到看到Has read all relay log.
>>登录你已经选定的从库作为master
mysql -uroot -p123456
stop slave;
reset master;
quit;
>>进到数据目录,删除master.info relay-log.info
cd /usr/local/mysql/data
rm -f master.info relay-log.info
>> 检查授权表,还有类似read-only的参数
>>提升slave 为 master节点
vi /etc/my.cnf
确定是否开启binlog
如果存在log-slave-updatesread-only参数之类等
一定要注释掉
/etc/init.d/mysqld start
>>其他从库的操作:
STOP SLAVE;
#如果没有VIP,需要这一步操作,入锅有了,就不需要
#这一步操作
CHANGE MASTER TO MASTER_HOST='192.168.199.211';
START SLAVE;
SHOW SLAVE STATUS\G;
切换完成。
====================================================
六 从库宕机
stop slave;
gzip -d /opt/bak/xxx.sql.gz
mysql -uroot -p123456 < /opt/bak/xxx.sql
CHANGE MASTER TO
MASTER_HOST='192.168.3.210',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=914;
START SLAVE;
SHOW SLAVE STATUS\G;
相当于重新做一次slave
====================================================
七 双主或者多主同步过程
7.1 解决主键自增长变量冲突
Master1:
#自增ID的间隔时间,将形成1,3,5,7...序列
auto_increment_increment = 2
#自增ID的初始位置
auto_increment_offset = 1
Master2:
#自增ID的间隔时间,将形成2,4,6,8...序列
auto_increment_increment = 2
#自增ID的初始位置
auto_increment_offset = 2
7.2 互为主从参数
Master1:
#开启从库binlog日志
log-bin=/usr/local/mysql/data/mysql-bin
log-slave-updates
mysql -uroot -p123456
CHANGE MASTER TO
MASTER_HOST='192.168.3.211',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456';
START SLAVE;
SHOW SLAVE STATUS\G;
Master2:
#开启从库binlog日志
log-bin=/usr/local/mysql/data/mysql-bin
log-slave-updates
mysql -uroot -p123456
CHANGE MASTER TO
MASTER_HOST='192.168.3.210',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456';
START SLAVE;
SHOW SLAVE STATUS\G;
- MySQL企业级主从复制
- Mysql实现企业级数据库主从复制架构
- Mysql实现企业级数据库主从复制架构实战
- Mysql实现企业级数据库主从复制架构实战
- 【mysql 主从复制】掌握MySQL主从复制
- mysql 主从复制 主从配置
- MySQL主从复制配置
- mysql主从数据复制
- Mysql 主从复制
- mysql的主从复制
- Mysql搭建主从复制
- MySQL主从复制配置
- Mysql 主从数据库复制
- mysql主从复制
- MySQL主从复制
- mysql主从复制
- 配置MySQL主从复制
- Mysql的主从复制
- CSR蓝牙测试仪 项目
- [ IntelliJ IDEA Java开发配置 : 不断完善中]
- ST-emWIN ICON图标索引
- 菜鸟网址
- MongoDB系列—Linux安装MongoDB
- MySQL企业级主从复制
- 业务团队如何高效实施自动化测试
- 【人工智能】【Java 】【微信支付】【架构高性能网站】
- 漫画 | 什么是 HashMap?
- Anaconda使用
- 从TwoSum浅析时间复杂度的优化策略
- 字符串String类
- 在Mac上搭建自己的服务器——Nginx
- GMSSL SM9-加密流程