MySQL的主从复制步骤详解及常见错误解决方法

   2016-05-18 0
核心提示:这篇文章主要介绍了MySQL的主从复制步骤详解及常见错误解决方法,文中主张同步时跳过临时错误,并对Slave_IO_Running: No错误的解决给出了方案,需要的朋友可以参考下

 mysql主从复制(replication同步)现在企业用的比较多,也很成熟.它有以下优点:

1.降低主服务器压力,可在从库上执行查询工作.

2.在从库上进行备份,避免影响主服务器服务.

3.当主库出现问题时,可以切换到从库上.

不过,用它做备份时就会也有弊端,如果主库有误操作的话,从库也会收到命令.

    下面直接进入操作.这里使用的是debian5操作系统,mysql5.0,默认引擎innodb

     10.1.1.45 主库

     10.1.1.43 从库

1.设置主库

1)修改主库my.cnf,这里主要是server-id一定主从不要设置成一样的.打开binlog日志

log-bin   = /opt/log.bin/45
server-id  = 45

2)在主服务器上建立同步账号
mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'10.1.1.43' IDENTIFIED BY 'replpass';

注意:mysql的权限系统在实现上比较简单,相关权限信息主要存储在几个系统表中:mysql.user,mysql.db,mysql.host,mysql.table_priv,mysql.columm_priv.由于权限信息的数据量比较小,访问又非常频繁,所以mysql在启动的时候,就会将所有的权限信息都加载到内存中,并保存在几个特定的结构里.这就使得每次手动修改了相关权限表之后,都需要执行flush privileges,通知mysql重新加载mysql的权限信息.当然,如果通过grants,revoke或drop user 命令来修改相关权限,则不需要手动执行flush privileges命令.

3)在主服务器上导出数据库当时的快照,传给从库上.

root@10.1.1.45:mysql# mysqldump -uroot -p --single-transaction --flush-logs --master-data --all-databases > all.sql
--single-transaction:这个选项能够让innoDB和Falcon数据表在备份过程中保持不变.这一做法的关键在于它是在同一个事务里来导入各有关数据表的.mysqldump使用repeatable read事务隔离层来生成一份稳定一致的转储文件,同时不会阻塞其他客户(对于非事务性表,转储过程可能有变化),它不能与--lock-all-tables选项一起使用.

--flush-logs:在导出工作开始之前先清空mysql服务器的日志文件.这样更容易恢复操作,知道在检查点时间之后创建的二进制日志文件是在备份给定数据库之后完成的.结合使用--lock-all-tables或--master-data,只有在所有数据表都锁定之后才清除日志.这个选项需要具备reload权限.

--master-data:使用后mysqldump会在dump文件中产生changer master to命令,里面记录了dump时刻所对应的详细的log position信息.

root@10.1.1.45:mysql# sed -n '1,40p' all.sql 
-- MySQL dump 10.11
--
-- Host: localhost Database: 
-- ------------------------------------------------------
-- Server version 5.0.51a-24+lenny1-log
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
--
-- Position to start replication or point-in-time recovery from
--
 
CHANGE MASTER TO MASTER_LOG_FILE='45.000064', MASTER_LOG_POS=98;
 
--
-- Current Database: `bbs`
--
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `bbs` /*!40100 DEFAULT CHARACTER SET latin1 */;
 
USE `bbs`;
 
--
-- Table structure for table `davehe`
--
 
DROP TABLE IF EXISTS `davehe`;
SET @saved_cs_client  = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `davehe` (

2.设置从库

1).修改从库my.cnf

server-id = 43 #主从可1对多 从各id不能相同

2)将主库的快照灌入从库

root@10.1.1.43:tmp# cat all.sql | mysql -uroot -p

3)在从库上设置同步.查看从库状态.

mysql> change master to master_host='10.1.1.45', master_user='repl',master_password='replpass',master_log_file='45.000064',master_log_pos=98;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
 
 
mysql> show slave status\G;
*************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 10.1.1.45
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
   Master_Log_File: 45.000064
  Read_Master_Log_Pos: 98
    Relay_Log_File: mysqld-relay-bin.000002
    Relay_Log_Pos: 228
  Relay_Master_Log_File: 45.000064
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
   Replicate_Do_DB: 
  Replicate_Ignore_DB: 
   Replicate_Do_Table: 
  Replicate_Ignore_Table: 
 Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
     Last_Errno: 0
     Last_Error: 
    Skip_Counter: 0
  Exec_Master_Log_Pos: 98
   Relay_Log_Space: 228
   Until_Condition: None
    Until_Log_File: 
    Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File: 
   Master_SSL_CA_Path: 
   Master_SSL_Cert: 
   Master_SSL_Cipher: 
    Master_SSL_Key: 
  Seconds_Behind_Master: 0
1 row in set (0.00 sec)
 
 
ERROR: 
No query specified

测试OK

当然这只是最简单的配置

还有很多参数可根据环境需求变化.

比如

  • replicate-do-db=test    过滤拉主日志到从只需要这个库和下面的表
  • replicate-wild-do-table=test.dave
  • replicate-wild-do-table=test.davehe

mysql数据库同步跳过临时错误 

stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; (事务类型,可能需要执行几次)
start slave;

stop slave IO_THREAD //此线程把master段的日志写到本地
start slave IO_THREAD
stop slave SQL_THREAD //此线程把写到本地的日志应用于数据库
start slave SQL_THREAD

Slave_IO_Running: No错误

由于主库的主机192.168.1.1宕机,再次启来后,从库192.168.71.1连接主库发现报错. Slave_IO_Running: No

root@192.168.71.1:~# mysql -uroot -p --socket=/opt/mysql/3399/3399.sock 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 452723
Server version: 5.0.51a-24+lenny2 (Debian)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> show slave status\G;
*************************** 1. row ***************************
    Slave_IO_State: 
    Master_Host: 192.168.1.1
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
   Master_Log_File: 99.000302
  Read_Master_Log_Pos: 165112917
    Relay_Log_File: 3399-relay-bin.000013
    Relay_Log_Pos: 165113047
  Relay_Master_Log_File: 99.000302
   Slave_IO_Running: No
   Slave_SQL_Running: Yes
   Replicate_Do_DB: 
  Replicate_Ignore_DB: mysql
   Replicate_Do_Table: 
  Replicate_Ignore_Table: 
 Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
     Last_Errno: 0
     Last_Error: 
    Skip_Counter: 0
  Exec_Master_Log_Pos: 165112917
   Relay_Log_Space: 165113047
   Until_Condition: None
    Until_Log_File: 
    Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File: 
   Master_SSL_CA_Path: 
   Master_SSL_Cert: 
   Master_SSL_Cipher: 
    Master_SSL_Key: 
  Seconds_Behind_Master: NULL
1 row in set (0.00 sec)


查看错误日志

mysql@192.168.71.1:/opt/mysql/3399$ cat 192.168.71.1.err
140115 1:51:01 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
140115 1:51:01 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log
140115 1:51:01 [Note] Slave I/O thread exiting, read up to log '99.000302', position 165112917


根据错误位置,查找主库上log ‘99.000302' 对应的位置 165112917

root@192.168.1.1:mysql.bin# mysqlbinlog 99.000302 > /tmp/test
root@192.168.1.1:mysql# tail -n 10 /tmp/test 
#140115 0:50:25 server id 1176 end_log_pos 165111351  Query thread_id=111 exec_time=0 error_code=0
SET TIMESTAMP=1389718225/*!*/;
INSERT INTO user_info_db_86.region_info_table_56 (userid, region, gameflag) VALUES (563625686, 0, 2) ON DUPLICATE KEY UPDATE gameflag = (gameflag | 2)/*!*/;
# at 165111351
#140115 0:50:25 server id 1176 end_log_pos 165111378  Xid = 17877752
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

结果发现主库上位置最后是165111351 比165112917要小. 也就是从库同步找的位置比主库要大,故同步不成功

为什么会这样,这是因为这个在sync_binlog=0的情况,很容易出现。
sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让系统自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。

sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统默认的设置是sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候,是最安全但是性能损耗最大的设置。因为当设置为1的时候,即使系统Crash,也最多丢失binlog_cache中未完成的一个事务,对实际数据没有任何实质性影响。从以往经验和相关测试来看,对于高并发事务的系统来说,“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

这里由于mysql是默认配置所以该报错原因是: sync_binlog=0时 ,master binlog文件的flush log buffer(这个buffer是由于binlog文件的os buffer)  到disk是依赖于OS本身的,但Slave IO 线程在读取master dump 线程的位置,一般是直接读取log buffer的,这个位置,可能远远大于binlog文件实际大小。 所以当主机宕机后,binlog buffer未刷盘,当Master主机再次启动后,此时从库的binlog pos 165112917  已经比实际的binlog位置大小165111351 还大了。

解决方法:

直接做change master to到当下一个binlog。

CHANGE MASTER TO

 MASTER_HOST='192.168.1.1',

 MASTER_USER='repl',

 MASTER_PASSWORD='replpass',

 MASTER_PORT=3306,

 MASTER_LOG_FILE='99.000303',

 MASTER_LOG_POS=98;


 
标签: MySQL 主从复制
反对 0举报 0 评论 0
 

免责声明:本文仅代表作者个人观点,与乐学笔记(本网)无关。其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
    本网站有部分内容均转载自其它媒体,转载目的在于传递更多信息,并不代表本网赞同其观点和对其真实性负责,若因作品内容、知识产权、版权和其他问题,请及时提供相关证明等材料并与我们留言联系,本网站将在规定时间内给予删除等相关处理.

  • sql:mysql:函数:TIMESTAMPDIFF函数实现TimeStamp字段相减,求得时间差
    sql:mysql:函数:TIMESTAMPDIFF函数实现TimeS
     函数内指定是minute,则最终结果value值的单位是分钟,如果函数内指定为hours,则最终结果value值单位为小时。//UPLOAD_TIME 减去 CREATE_DTTM 求得时间差,以分钟数计时select avg(TIMESTAMPDIFF(MINUTE,CREATE_DTTM,UPLOAD_TIME)) value,LEFT(CREATE_DTTM
    03-08
  • mysql下如何执行sql脚本 执行SQL脚本
    1.编写sql脚本,假设内容如下:  create database dearabao;  use dearabao;  create table niuzi (name varchar(20));  保存脚本文件,假设我把它保存在F盘的hello world目录下,于是该文件的路径为:F:\hello world\niuzi.sql2.执行sql脚本,可以有2种方法: 
    02-10
  • MySQL 5.7版本sql_mode=only_full_group_by问题
    用到GROUP BY 语句查询时com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'col_user_6.a.START_TIME' which is not functionally dependent on colu
    02-10
  • Oracle迁移到MySQL性能下降的注意点 oracle数据
    背景:最近有较多的客户系统由原来由Oracle改造到MySQL后出现了性能问题CPU 100%,或是后台的CRM系统复杂SQL在业务高峰的时候出现堆积导致业务故障。在我的记忆里面淘宝最初从Oracle迁移到MySQL期间也遇到了很多SQL的性能问题,记忆最为深刻的子查询,当初的
    02-10
  • MySQL与Oracle 差异比较之六触发器
    触发器编号类别ORACLEMYSQL注释1创建触发器语句不同create or replace trigger TG_ES_FAC_UNIT  before insert or update or delete on ES_FAC_UNIT  for each rowcreate trigger `hs_esbs`.`TG_INSERT_ES_FAC_UNIT` BEFORE INSERT on `hs_esbs`.`es_fac_u
    02-10
  • mysql where条件:某时间字段为今天的sql语句
    1.查询:注册时间为今天的所有用户数:select count(*) from customer where TO_DAYS(createtime) = TO_DAYS(NOW())2.获取当前时间到凌晨24点还有多长时间:(Java中可用于判断某时间是否为今天)final Calendar cal = Calendar.getInstance();    ca
    02-10
  • mysql中的sql
    变量用户变量: 在用户变量前加@系统变量: 在系统变量前加@@运算符算术运算符有: +(加), -(减), * (乘), / (除) 和% (求模) 五中运算位运算符有:(位于), | (位或), ^ (位异或), ~ (位取反),(位右移),(位左移)比较运算符有: = (等于),(大于),(小于), = (大
    02-10
  • mysql5.7配置文件修改sql_mode 重启无效解决方法。this is incompatible with sql_mode=only_full_group_by
    mysql5.7配置文件修改sql_mode 重启无效解决方
    whereis my.cnf找到配置路径:/etc/my.cnf找到[mysqld],在下面添加sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION重要:如果没有[mysqld],一定要先添加[mysqld]再在下
    02-10
  • mysql 8 查询报错(sql_mode=only_full_group_by)
    mysql 8 查询报错(sql_mode=only_full_group_by
    Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_
    02-10
  • Oracle、MySql、Sql Server比对
    MySql:廉价(部分免费):当前,MySQL採用双重授权(DualLicensed),他们是GPL和MySQLAB制定的商业许可协议。假设你在一个遵循GPL的***(开源)项目中使用MySQL,那么你能够遵循GPL协议免费使用MySQL。否则,你须要购买MySQLAB制定的那个商业许可协议。Windows $
    02-10
点击排行