집 >데이터 베이스 >MySQL 튜토리얼 > Centos6.3下mysql主从复制笔记
Centos6.3下mysql主从复制笔记
- WBOY원래의
- 2016-06-07 17:41:571003검색
MySQL主从复制结构是基于mysqlbin-log日志基础上，从库通过打开IO进程收到主库的bin-log日志增量信息,并保存到本地relaylog,而后再通过打开MYSQL进程从relaylog
MySQL主从复制结构是基于mysql bin-log日志基础上，从库通过打开IO进程收到主库的bin-log日志增量信息,并保存到本地relay log,而后再通过打开MYSQL进程从relay log上获取的增量信息并翻译成SQL语句后写到从数据库。
主从复制结构，实际上可以实现两个功能：
1.从库充当主库的数据库备份实例
2.读写分离，主库负责正常读写数据，从库只负责读数据
实际生产环境，虚拟主机，因为很多应用实际读数据库的次数远大于写数据库的次数，所以在项目开发初期，编写程序时做一个判断，对所有读的操作全部推到从库，若从库无法获取数据，则再向主库获取数据，从而在一定意义上实现读写分离，缓解主库的IO压力。
所以生产环境下推荐使用这种架构。
系统环境：centos6.3 x64
数据库： mysql-5.6.10
mysql master:192.168.100.90
mysql slave:192.168.100.91
一.部署环境：
1.关闭iptables和SELINUX
# service iptables stop
# setenforce 0
# vi /etc/sysconfig/selinux
---------------
SELINUX=disabled
---------------
2.安装配置mysql传送门：
二.主mysql配置:(mysql master)
修改mysql配置文件：
# vi /etc/my.cnf
添加：
-----------------
# Replication Master Server
# bin日志路径
log-bin = /usr/local/mysql/log/bin.log
# 服务器ID号
server-id = 1
# 忽略mysql系统库复制
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
------------
重启服务
# service mysqld restart
登录mysql后台：
# mysql -u root -p123456
查看此刻登录账号：
> select user();
在master为slave添加同步帐号：
> grant replication slave on *.* to 'slave'@'192.168.100.91' identified by '123456';
查看创建的用户：
> select user.host from mysql.user;
查看权限：
> show grants for 'slave'@'192.168.100.91';
mysql锁表只读(其他账户登录mysql后无法进行写表操作，防止备份数据库后，主mysql表更新，导致和从数据库内容不一致)
> flush tables with read lock;
查看锁表倒计时时间：
> show variables like '%timeout%';
------------------------
....
wait_timeout | 28800
------------------------
将master的数据库表全部备份导出，并传送到slave服务器上。
# /usr/local/mysql/bin/mysqldump -u root -p123456 --opt --flush-logs --all-database > /root/allbak.sql
# cd ~
# scp allbak.sql root@192.168.100.91:/root
查看mysql偏移量(数据库如果有写操作，偏移值会递增)
# mysql -u root -p123456 -e "show master status"
----------------------
+------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000009 | 120 | | | |
+------------+----------+--------------+------------------+-------------------+
----------------------
保证FILE列和Position列与从库配置一致：
三.从mysql配置（mysql slave）
修改mysql配置文件：
# vi /etc/my.cnf
添加：
--------------------
# Replication Slave Server
# bin日志路径（无需开bin-log日志）
#log-bin = /usr/local/mysql/log/bin.log
server-id=2
# 只读：
read-only
# 忽略mysql系统库复制
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
---------------------
重启服务
# service mysqld restart
恢复server的数据库到slave
#/usr/local/mysql/bin/mysql -u root -p123456
配置连接同步到server端：
# mysql -u root -p123456;
> stop slave;
> change master to master_host='192.168.100.90',master_user='slave',master_password='123456',master_log_file="bin.000009",master_log_pos= 120 ;
> start slave;
注：master_log_file表示从主数据库哪个bin-log文件开始同步
master_log_pos表示从该bin-log文件哪条记录点开始同步
需与主库偏移值保持同步
回到主mysql数据库解锁（mysql master）
# mysql -u root -p123456
> unlock tables;
最后登陆从mysql后台查看主从连接状态
# mysql -u root -p123456 -e "show slave status\G;"
找到这五行，如下则主从配置成功：
------------------------
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Read_Master_Log_Pos: 120
Relay_Master_Log_File: bin.000009
-------------------------
四.测试主从是否同步：
(server)
# mysql -u root -p123456 -e "create database test02;"
# mysql -u root -p123456 -e "show databases like 'test02';"
------------
+-------------------+
| Database (test02) |
+-------------------+
| test02 |
+-------------------+
------------
(cilent)
# mysql -u root -p123456 -e "show databases like 'test02';"
-------------
+-------------------+
| Database (test02) |
+-------------------+
| test02 |
+-------------------+
-------------
测试成功。。
当server端的数据库数据发生变化时，client端会同步更新，从而实现主库备份和读写分离作用。
这里注意几点：
1.mysql从库中需在my.cnf配置文件中加入 read-only参数，保证从库只读。
# echo "read-only" >> /etc/my.cnf
2.忽略从库mysql与information_schema系统表同步
# echo "binlog-ignore-db=mysql" >> /etc/my.cnf
# echo "binlog-ignore-db=information_schema" >> /etc/my.cnf
生产环境主库用户的授权,授权增删改查权限。
> GRANT SELECT,INSERT,UPDATE,DELETE ON *.* to 'user'@'%' identified by '123456';
生产环境从库的授权,仅授权查权限。
> GRANT SELECT ON *.* to 'user'@'%' identified by '123456';
3.从库默认不开启bin-log日志功能，除非做下级从库级联同步，服务器空间，才需开启从库的bin-log日志。
4.主库由于硬件故障，如何将从库提升为主库(一主多从)
(mysql slave)
(1) 确保从机没有再同步的SQL语句，即出现Has read all relay log再关闭从库IO_Threat进程
# mysql -uroot -p123456
> stop slave IO_THREAD
(2) 关闭从库slave服务，然后将其提升为主库
> stop slave
> reset master
(3) 更换从库IP为故障主库IP(配置方法略)
(4) 删除新的主库master.info和relay-log.info,防止下次重启还会按照从库启动
# cd /usr/local/mysql/log
# rm -rf master.info relay-log.info
(5) 重新配置从库连接主库的账号同步信息，以及在下级从库重新设置偏移量保持与新的主库一致即可。
最后待主库硬件恢复，将其再设置为从库并更换为上述从库IP地址,完成主从切换。
五.配置脚本：
1.如果想实现无人值守备份主数据库，可添加如下脚本，并在凌晨执行定时任务：
# vi /etc/rc.d/mysql_bak.sh
---------------------
#!/bin/sh
MYSQL_USER=root
MYSQL_PW="123456"
LOG_PATH=/usr/local/mysql/log
DATA_PATH=/usr/local/mysql/data
LOG_FILE=${LOG_PATH}/mysqllog_`date +%F`.log
DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz
BIN_PATH=/usr/local/mysql/bin
MYSQL_CMD="$BIN_PATH/mysql -u$MYSQL_USER -p$MYSQL_PW"
MYSQL_DUMP="$BIN_PATH/mysqldump -u$MYSQL_USER -p$MYSQL_PW --opt --flush-logs --all-database"
$MYSQL_CMD -e "flush tables 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 tables"
mail -s "mysql slave log" 1234567@qq.com
---------------------
凌晨3:30执行备份数据库操作：
# crontab -e
---------------------
30 3 * * * /bin/sh /etc/rc.d/mysql_bak.sh > /dev/mull 2>&1
---------------------
# service crond restart
2.如果想实现将主库备份的数据分发到从库，恢复从库数据库，以及开启从库功能，香港服务器租用，可添加如下脚本
注：这里建议主从先做好秘钥认证：
详见传送门：
# vi /etc/rc.d/mysql_bak1.sh
------------------
#!/bin/sh
MYSQL_USER=root
MYSQL_PW="123456"
MYSQL_SLAVE_IP="192.168.100.91"
SSH_PATH="/usr/bin/ssh"
SSH_CMD="${SSH_PATH} ${MYSQL_SLAVE_IP}"
GZIP_CMD="/bin/gzip"
LOG_PATH=/usr/local/mysql/log
DATA_PATH=/usr/local/mysql/data
LOG_FILE=${LOG_PATH}/mysqllog_`date +%F`.log
DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz
BIN_PATH=/usr/local/mysql/bin
MYSQL_CMD="$BIN_PATH/mysql -u$MYSQL_USER -p$MYSQL_PW"
MYSQL_DUMP="$BIN_PATH/mysqldump -u$MYSQL_USER -p$MYSQL_PW --opt --flush-logs --all-database"
$MYSQL_CMD -e "flush tables with read lock;"
echo "-------show master status result-------" >> $LOG_FILE
$MYSQL_CMD -e "show master status" >> $LOG_FILE
${MYSQL_DUMP}|gzip > $DATA_FILE
#config slave
cd ${DATA_PATH}
scp "mysql_backup_`date +%F`.sql.gz" $MYSQL_SLAVE_IP:/tmp/
${SSH_CMD} "${GZIP_CMD} -d /tmp/mysql_backup_`date +%F`.sql.gz"
${SSH_CMD} "${MYSQL_CMD}
${SSH_CMD} "cat |$MYSQL_CMD"
stop slave;
change master to
master_host='192.168.100.90',
master_user='slave',
master_password='123456',
master_log_file="bin.000009",
master_log_pos= 120;
start slave;
EOF
$SSH_CMD $MYSQL_CMD -e "show slave status\G;"|egrep "IO_Running|SQL_Running" >> $LOG_FILE
$MYSQL_CMD -e "unlock tables"
mail -s "mysql slave log" 1234567@qq.com
$SSH_CMD mail -s "mysql slave log" 1234567@qq.com
------------------
-------大功告成---------
本文出自 “一路向北” 博客，请务必保留此出处