续mysql数据库主从同步过程详述(二)
在此说明下:在最后试验过程中,当查看从库状态的时候,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 "Starting MySQL...\n"
        /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/etc/my.cnf >/dev/null 2>&1 &
}
function_stop()
{
        printf "Stoping MySQL...\n"
        ${CmdPath}/mysqladmin -u${mysql_user} -p${mysql_pwd} -S /tmp/mysql.sock shutdown >/dev/null
}
function_restart()
{
        printf "Restarting MySQL...\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`.sql.gz
 
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.com < $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方法
在从库的my.cnf中加入如下参数
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权限.