====================================================

一 生产环境主从节点复制配置注意事项

1.1 第一次如何做？

>> 申请设备资源，用来做从库服务器

>> 撰写文档方案和实施步骤

linux主从复制原理。假如你的服务器只有主库，而且已经跑了生产线上的应用了

现在由于业务需要第一次做从库，此时可能需要和公司申请

停机维护，即再用户访问量很小的时候，且不影响内部其他

业务运转时间点来停机配置

也可以不申请停机，在定时任务备份的时候，每天都夜里的

Mysql主从、定时备份做一些措施也可以

 

1.1.1 一键备份和获取主节点binlog位置的脚本

>>锁表备份全备份一份 -A -B --single-transaction -E

>>锁表前后取得show master status值

MySQL主从同步？方法一：

#! /bin/sh

 

####################################################

MYSQL_USER=root

mysql主从模式。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主从复制，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"

redis主从复制漏洞，${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"

Redis主从复制？mail -s "mysql slave log"530075499@qq.com<$LOG_FILE

 

有个问题，$MYSQL_CMD -e "SHOWMASTER STATUS;" >>

$LOG_FILE 执行完这个窗口退出了，锁不住表

 

主从。方法二：

#! /bin/sh

 

####################################################

MYSQL_USER=root

mysql主从复制原理、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主从复制的优缺点。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主从复制的步骤、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

redis主从复制和redis集群的区别、SYSTEM ${MYSQL_DUMP} |gzip > $DATA_FILE;

$MYSQL_CMD -e "SHOW MASTER STATUS;" >>$LOG_FILE

$MYSQL_CMD -e "UNLOCK TABLE;"

EOF

 

mongodb主从复制原理、方法三：

#! /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;