MySQL的主从复制步骤详解及常见错误解决方法
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 = 452)在主服务器上建立同步账号
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性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
- 新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
深入研究mysql中的varchar和limit(容易被忽略的知识)
为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15- 这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
- 一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
- 我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
- 这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
- 宿主机使用网线的时候,客户机在Bridged Adapter模式下,使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候,客户机在Bridged Adapter模式下,使用可选项里唯一一个WIFI选项,Microsoft Virtual Wifi Miniport Adapter也无法上网,故弃之。...2013-09-19
- 首先要声明一点,大部分情况下,修改MySQL密码是需要有mysql里的root权限的...2013-09-11
- MySQL命令行导出数据库: 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26
- 一、连接Mysql格式: mysql -h主机地址 -u用户名 -p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口,然后进入目录mysql/bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密...2015-11-08
- Navicat for MySQL注册码用来激活 Navicat for MySQL 软件,只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23
- 这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14
- 这篇文章主要介绍了node.js如何操作MySQL数据库,帮助大家更好的进行web开发,感兴趣的朋友可以了解下...2020-10-29
- 这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25
- 一、准备编译环境,安装所需依赖包yum groupinstall 'Development' -y yum install openssl openssl-devel zlib zlib-devel -y yum install readline-devel pcre-devel ncurses-devel bison-devel cmake -y二、编译安...2015-10-21
Mysql中 show table status 获取表信息的方法
这篇文章主要介绍了Mysql中 show table status 获取表信息的方法的相关资料,需要的朋友可以参考下...2016-03-12- 这篇文章主要为大家分享了20分钟MySQL基础入门教程,快速掌握MySQL基础知识,真正了解MySQL,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2016-12-02
- 传统的关系数据库一般由数据库(database)、表(table)、记录(record)三个层次概念组成,MongoDB是由数据库(database)、集合(collection)、文档对象(document)三个层次组成。MongoDB对于关系型数据库里的表,但是集合中没有列、行和关...2013-09-11
- 这篇文章主要介绍了Delphi远程连接Mysql的实现方法,需要的朋友可以参考下...2020-06-30