mysql数据库的主从同步过程
1复制准备
主库(mysql master): ip为192.168.1.5 port为3306
从库(mysql slave): ip为192.168.1.4 port为3306
数据库环境准备，具备两台服务器每个机器⼀个数据库的环境.
2.主库上执⾏操作
2.1设置server-id值并开启binlog参数
根据mysql的同步原理，我们知道复制的关键因素就是binlog⽇志.
执⾏vi /etc/myf编辑myf配置⽂件，按如下两个参数内容修改:
[mysqld]
server-id = 1
log-bin = mysql-bi
提⽰:
1.上⾯两参数放在myf中的[mysqld]模块下，否则会出错;
2.要先在myf⽂件中查相关参数，并按要求修改，不存在时在添加参数，切记，参数不能重复;
3.修改myf配置后需要重启数据库命令为:/etc/init.d/mysql restart，注意确认真正重启了(此处数据库为源码包安装，在后⾯我将贴
出mysql的启动脚本)。mysql下载starting the server
检查配置后的结果(这是⼀个好的习惯):
grep -E "server-id|log-bin" /etc/myf 和egrep⼀样的作⽤
/etc/init.d/mysql restart
2.2建⽴⽤于同步的账号rep
登陆mysql 3306实例主数据库
mysql -uroot -p'123123' -S /tmp/mysql.sock
mysql>select user(); 查看⽤户
mysql>grant replication slave on *.* to 'rep'@'192.168.1.%' identified by "123456"; 建⽴⽤于库复制的账号rep
#replication slave为mysql同步的必须权限，此处不要授权all
#*.*表⽰所有库所有表，库也是可以指定具体的库和表进⾏复制，如st1（test库的test1表）；
#'rep'@'192.168.1.%' rep为同步账号，192.168.1.%为授权主机，使⽤了%表⽰允许整个192.168.1.0⽹段以rep⽤户访问；
#identified by "123456" , 123456为密码，实际环境时复杂⼀点为好。
再次检查创建的rep账号
select user.host from mysql.user;
也可以查看⽤户权限 mysql>show grants for rep@'192.168.1.%';
2.3对数据库锁表只读（当前窗⼝不要关闭）
⽣产环境时，操作主从复制，需要申请停机时间，锁表会影响业务。
mysql>flush tables with read lock;
提⽰，这个锁表命令的时间，在不同引擎的情况，会受下⾯参数的控制，锁表时，如果超过设置时间不操作会⾃动解锁；
interactive_timeout = 60
wait_timeout = 60
默认情况下的时长为：
mysql>show variables like "%timeout%"; 可以查看到默认值很⼤
完成后测试下是否锁表，打开另⼀窗⼝创建⼀test1表，是不会执⾏的，证明锁表不能更新，但可读，不可写，因为是read读锁，锁表主要是为了导出数据库⽂件，从⽽取得正确的偏移量的值，保证导⼊从数据库，数据⼀致。
2.4查看主库状态
查看主库状态，即当前⽇志⽂件名和⼆进制⽇志偏移量
show master status；命令显⽰的信息要记录在案，后⾯的从库复制时是从这个位置开始的。
2.5导出数据库数据
单开新窗⼝，导出数据库数据，如果数据库量很⼤（100G+），并且允许停机可以，可以直接停库打包数据⽂件迁移。
mkdir /server/backup/ -p
mysqldump -uroot -p"123123" -S /tmp/mysql.sock -A -B | gzip > /server/backup/mysql_bak.$(date +%F).
#注意，-A表⽰备份所有库， -B表⽰增加user DB和drop等参数（导库时会直接覆盖所有的）。
ls -l /server/backup/mysql_bak.$(date +%F).
为了确保导库期间，数据库没有数据插⼊，可以再检查下主库状态信息
mysql -uroot -p"123123" -S /tmp/mysql.sock -e "show master status"
提⽰，⽆特殊情况,binlog⽂件及位置点是保持不变的。
导库后，解锁主库，恢复可写；
mysql>unlock tables;
特别提⽰，有读者这⾥犯迷糊，实际上做从库的，⽆论主库更新多少数据了，最后从库都会从上⾯show master status 的位置很快赶上主库的位置进度的。
2.6把主库备份的mysql数据迁移到从库
这步常⽤命令有scp,rsync等。
ls -l /server/backup/mysql_bak.$(date +%F).
3从库上执⾏操作
3.1 设置server-id值并关闭binlog设置
数据库的server-id⼀般在LAN内是唯⼀的，这⾥的server-id要和主库及其他从库不同，并注释掉从库的binlog参数配置；
执⾏vi /etc/myf 编辑myf配置⽂件，按如下两个参数内容修改；
[mysqld]
server-id = 2
#log-bin = mysql-bin
检查配置后的结果
grep -E "server-id|log-bin" /etc/myf
#log-bin = mysql-bin log-bin后⾯也可以不带等号内容，mysql会使⽤默认⽇志。
server-id = 2
重启从数据库
/etc/init.d/mysql restart
3.2还原主库导出的数据到从库
cd /server/backup/ && ls -l
然后解压刚才备份的
gzip -d mysql_bak.2012-05-22.sql
恢复命令
mysql -uroot -p"123123" < mysql_bak.2012-05-22.sql
3.2登陆从库配置同步参数
mysql -uroot -p"123123"
CHANGE MASTER TO 连接主数据库
MASTER_HOST="192.168.1.5", 这⾥是主库的ip
MASTER_PORT=3306, 这⾥是主库的端⼝，从库的端⼝可以和主库不同
MASTER_USER="rep", 这⾥是主库上建⽴的⽤于复制的⽤户rep
MASTER_PASSWORD="123456", 这⾥是rep的密码
MASTER_LOG_FILE="mysql-bin.000004", 这⾥是show master status时查看到的⼆进制⽇志⽂件名称，注意不能多空格MASTER_LOG_POS=1273; 这⾥是show master status时查看到的⼆进制⽇志偏移量，注意不能多空格。
不登陆数据库，在命令⾏快速执⾏change master的语句（适合在脚本中批量建slave库⽤）
本⽂即⽤此法来操作
cat | mysql -uroot -p"oldboy" -S /data/3307/mysql.sock<<EOF
CHANGE MASTER TO
MASTER_HOST="192.168.1.5",
MASTER_PORT=3306,
MASTER_USER="rep",
MASTER_PASSWORD="123456",
MASTER_LOG_FILE="mysql-bin.000004",
MASTER_LOG_POS=1273;
EOF
3.2启动从库同步开关
启动从库同步开关，并查看同步状态
mysql -uroot -p"123123" -S /tmp/mysql.sock -e "start slave;"
mysql -uroot -p"123123" -e "show slave status\G;"
也可登陆数据库⾥⾯执⾏下⾯两个命令:
start slave
show slave status\G;
判断搭建是否成功就看如下IO和SQL两个线程是否显⽰为yes状态
Slave_to_Running: Yes #负责从库去主库读取binlog⽇志，并写⼊从库中继⽇志中
Slave_SQL_Running: Yes #负责读取并执⾏中继⽇志中的binlog，转换sql语句后应⽤到数据库汇总
也可以执⾏命令过滤查看如下
mysql -uroot -p"123123" -S /tmp/mysql.sock -e "show slave status\G;" | egrep "IO_Running|SQL_Running"
3.3测试复制结果
主库创建⼀数据库，看从库是否有.
mysql -uroot -p"123123" -S /tmp/mysql.sock -e "create database diablo4;"
mysql -uroot -p"123123" -e "show databases like 'diablo4';"
如下图主库创建库
从库如下图⽰:
4.报错
在此说明下：在最后试验过程中，当查看从库状态的时候,IO_Running显⽰为no，从error_log中看到如下报错提⽰:
120523 0:55:31 [Note] Slave I/O thread: connected to master 'rep@192.168.1.5:3306', replication started in log ' mysql-bin.000004' at position 1273
120523 0:55:31 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236)
120523 0:55:31 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log
错误代码为1236
我的解决⽅法为：重新检查授权，确认⽆误，重新按前⾯步骤记录偏移量,⼆进制⽂件，停⽌从数据库，然后重新在从数据库中CHANGE MASTER TO 开始，指向正确的⼆进制⽂件及偏移量.如下图:
下⾯我将贴出mysql的启动脚本(仅作参考):
#!/bin/bash
mysql_user="root"
mysql_pwd="123123"
CmdPath="/usr/local/mysql/bin"
function_start()
{
printf "\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/etc/myf >/dev/null 2>&1 &
}
function_stop()
{
printf "\n"
${CmdPath}/mysqladmin -u${mysql_user} -p${mysql_pwd} -S /tmp/mysql.sock shutdown >/dev/null
}
function_restart()
{
printf "\n"
function_stop
sleep 2
function_start
}
case $1 in
start)
function_start
;;
stop)
function_stop
;;
restart)
function_restart
;;
*)
printf "Usage: $0 {start|stop|restart}\n"
esac
⽣产环境在⼯作时间轻松配置从库
在定时任务备份时，每天的夜⾥服务⾥压⼒⼩时侯的定时备份时做⼀些措施即可，如
1.锁表备份全备⼀份;
2.锁表前后取得show master status值记录⽇志⾥.
这样可以在⽩天从容的实现主从同步了，如下⾯脚本:
#!/bin/bash
MYUSER=root
MYPASS="123123"
MYSOCK=/tmp/mysql.sock
MAIN_PATH=/server/backup
DATA_PATH=/server/backup
LOG_FILE=${DATA_PATH}/mysqllogs_`date +%F`.log
DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.
MYSQL_PATH=/usr/local/mysql/bin
MYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS -S $MYSOCK"
MYSQL_DUMP="$MYSQL_PATH/mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -A -B --flush-logs --single-transaction -e"
$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" 123456@163 < $LOG_FILE
5.相关mysql技术技巧概览
5.1配置忽略权限库同步参数
binlog-ignore"db"information_schema
binlog_ignore"db"mysql
5.2主从复制故障解决
show slave status报错:Error xxx don't exist
且show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running : No
Seconds_Behind_Master: NULL
解决⽅法:
stop slave;
set global sql_slave_skip_counter=1;
start slave;
这样slave就会和master去同步，主要看点:
secon是否为0 # 0表⽰已经同步状态
提⽰: set global sql_slave_skip_counter=n; # n取值 >0忽略执⾏N个更新5.3让mysql slave记录binlog⽅法
在从库的myf中加⼊如下参数
log-slave=updates
log-bin=mysql3306-bin
expize_logs_days = 7
应⽤场景：级联复制或从库做数据备份
5.4严格设置从库只读
read-only的妙⽤
5.5⽣产环境如何确保从库只读?
1)mysql从服务器中加⼊read-only参数或者在从服务器启动时加该参数;
2)忽略mysql库及information_schema库同步;
3)授权从库⽤户时仅授权select权限.
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论