当前位置： 首页 > news >正文

mysql 主从同步详细配置教程

news 来源：原创 2024/5/4 9:37:04

Mysql 主从同步

主从原理

mysql主从同步的原理：
    1、在master上开启bin-log日志，用于记录master上的更改删的一些记录。
    2、主从各开启io线程，从上开启io线程和sql线程。同时都配置好主从上的serveid唯一性
    3、主上配置好授权用户，从上设置change master授权连接的命令
    3、 从上io线程通过授权连接master，master通过io线程检查到slav的请求的日志、postsion点位置。
    4、master将这些相应的请求内容发送给slave，slave收到后，将内容存放在中继日志realy_log里面，同时生成写入一个master-info，写入日志记录和pos点。用于下次连接的记录
    日志记录同步的点。
    5、slave的sql线程检查到realy-log日志更新的内容，并将更新的内容解析成ｓｑｌ语句然后在本地去exec执行。同时也将记录写入realy-info
    6、主从同步是属于异步方式。

主从同步状态

mysql> show slave status\G;
*************************** 1. row ***************************
                             Slave_IO_State: Waiting for master to send event
                                    Master_Host: 10.204.3.13
                                    Master_User: tongbu
                                    Master_Port: 3306
                                Connect_Retry: 10
                            Master_Log_File: mysql-bin.000004
                    Read_Master_Log_Pos: 239948407
                             Relay_Log_File: mysqld-relay-bin.000006
                                Relay_Log_Pos: 30462462
                Relay_Master_Log_File: mysql-bin.000004
                         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: 239948407
                            Relay_Log_Space: 112003215
                            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
Master_SSL_Verify_Server_Cert: No
                                Last_IO_Errno: 0
                                Last_IO_Error: 
                             Last_SQL_Errno: 0
                             Last_SQL_Error:

同步状态成功的参考值：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes 
IO线程和SQL线程都为YES

Master_Log_File: mysql-bin.000004
Relay_Master_Log_File: mysql-bin.000004
主日志文件和从读取的文件为同一个

Read_Master_Log_Pos: 239948407
Exec_Master_Log_Pos: 239948407
读取主日志的偏移量和执行主日志的偏移量相等

主从同步的不同状态：

    查看命令：
    show processlist;
    show slave status;

    sending binlog event to slave;
    二进制日志由各种事件组成，一个事件通常为一个新加一些其它信息，线程已经从二进制日志读取了一个事件并且正将发送到从服务器

    finished reading one binlog;switching to next binlog
    线程已经读完二进制日志文件并且打开下一个要发送到从服务器的日志文件

    has sent all binlog to slave;waiting for binlog to be updated
    线程已经从二进制日志读取所有主要的更新并已经发送到了从服务器，线程现在正空闲，等待由主服务器上新的更新导致的出现在二进制日志中的新事件

    waiting to finalize termination
    线程停止时发生的一个很简单的状态

MySQL主从同步配置

(mysql中有自增长字段，在做数据库的主主同步时需要设置自增长的两个相关配置：
在主主同步配置时，需要将两台服务器的auto_increment_increment增长量都配置为2，而要把auto_increment_offset分别配置为1和2,这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。)

配置主master

1.  vi /etc/my.cnf  # 修改添加
server-id=1
log-bin=mysql-bin     # mysql-bin日志前缀，可自定义
binlog-do-db=db1,db2  # 需要同步的库
binlog-ignore-db=db1,db2 # 忽略不同步的库

binlog两参数2选1 都不写全部同步
查询bin-log是否开启 show variables like '%log_bin%';

2.  grant replication slave on *.* to root@'localhost' identified by '123456';  # 创建同步用户
     Flush privileges;

3.  show master status; # 一定要记住前两列的内容，
| File             | Position  | Binlog_Do_DB   |
 mysql-bin.0000007    106   

4.同时不退出Mysql，复制窗口,使用mysqldump在逻辑上保证两台数据库的一致性，比如将主库导出并导入到从上。
mysqldump -uroot -p123456 -A -B --events --master-data=1 -x > /backup/all.mysql

加只读锁： flush table with locak;
解锁：     unlocak tables;

设置从(slave)

    1.  vi /data/mysql/my.cnf

    server -id =2   # 这个数据不能和主一样
    以下可选参数：
    replicate-do-db=db1,db2
    replicate-ignore-db=db1,db2

    2.  进入mysql   执行：slave stop;

    CHANGE MASTER TO
        MASTER_HOST='master2.mycompany.com',
        MASTER_USER='replication',
        MASTER_PASSWORD='bigs3cret',
        MASTER_PORT=3306,
        MASTER_LOG_FILE='master2-bin.001',
        MASTER_LOG_POS=4,
        MASTER_CONNECT_RETRY=10;

    3.  执行：slave start;

    4.  从上查看从的状态:  show slave status\G;

Mysql主从同步一键脚本实现

MYUSER=mysql
MYPASSWD=123456
MYSOCK=/usr/local/mysql/var/mysql.sock
LOG_FILE=${DATE_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$MYPASSWD -S $MYSOCK"
MYSQL_DUPM="$MYSQL_PATH/dump -u$MYUSER -p$MYPASSWD -A -B --master-data=1 --single-tarnsaction -e"

${MYSQL_DUMP} | gzip > $DATA_FILE

Mysql 主从切换

Reset master 和Reset slave

 RESET MASTER

删除所有index file 中记录的所有binlog 文件，将日志索引文件清空，创建一个新的日志文件，这个命令通常仅仅用于第一次用于搭建主从关系的时的主库，

注意
    reset master 不同于purge binary log（清除二进制日志）的两处地方
1 reset master 将删除日志索引文件中记录的所有binlog文件，创建一个新的日志文件 起始值从000001 开始，然而purge binary log 命令并不会修改记录binlog的顺序的数值
2 reset master 不能用于有任何slave 正在运行的主从关系的主库。因为在slave 运行时reset master 命令不被支持，reset master 将master 的binlog从000001 开始记录,slave 记录的master log 则是reset master 时主库的最新的binlog,从库会报错无法找的指定的binlog文件。

 RESET SLAVE

reset slave 将使slave 忘记主从复制关系的位置信息。该语句将被用于干净的启动, 它删除master.info文件和relay-log.info 文件以及所有的relay log 文件并重新启用一个新的relaylog文件。
使用reset slave之前必须使用stop slave 命令将复制进程停止。

MySQL主主同步故障

图中出现了两个No。
Slave_IO_Running: No
Slave_SQL_Running: No
说明slave没有成功， 即，从B到A的同步没有成功。 我们去查看mysql错误日志，前面说过位置：

找到  机器名.err 文件，打开看看：

看图中的error信息。  说找不到中继日志文件。
这是因为我们在配置A的中继文件时改了中继文件名，但是mysql没有同步。解决办法很简单。

先停掉mysql服务。  找到这三个文件，把他们删掉。 一定要先停掉mysql服务。不然还是不成功。你需要重启一下机器了。 或者手动kill mysqld。
好了， 启动mysql之后。 我们在来检查一下slave状态：

master突然down机，如何恢复：

1.  检查slave服务器
检查从库与主库的最后状态
mysql> show processlist;
| 46 | tongbu | WEB-1:58181 | NULL | Binlog Dump | 3221 | Has sent all binlog to slave; waiting for binlog to be updated
如果看到上面一行状态，说明主从之间同步是正常的

2.  对比pos点
在主服务器上查询show master status;上的pos点与从上的master.info的最新pos点是否一致
如果是一主多从的情况下，查看从的master.info中的信息最新，pos点大，说明更新最完整，然后将它提升为主库，。

3.  停止从库的io线程
确保所有relay log全部更新完毕
在每个从库上执行stop slave;show processlist;
直到看到Has read all relay log;表示从库更新都执行完毕

4.  提升从库为主库

a)  首先配置/etc/my.cnf文件，开启binlog
注释掉一些log-slave-updates read-only
b)  在数据库目录下清除master info和mysql-bin文件
c)  执行以下命令
reset master;

5.  在新的主库上添加grant授权
6.  在新的主库上show master status\G;  查看post点和mysqlbin文件
7.  登入从库
stop slave;
change master to masetr_host  设置主库
start slave;

有计划的切换主从

1.  先锁表 防止主数据库有新的数据写入
2.  登陆从查看状态 show processlist; 查看最后的同步是否完成
3.  在需要提升主库的从库上，
a)  修改配置文件 my.cnf  
            添加log-bin=mysql-bin
b)  删除本地的master.info  
执行：stop slave;　reset master;
c)  重启mysql服务
4.  添加grant授权，show master status状态
5.  在所有从库上执行
stop slave;
change master to master_host=’10.204.1.200’;
start slave;

主库降级为从库

【注意】将原主库切换成从，步骤中执行reset slave,意思就是将本地的mysql-bin和mster-info信息清除，清除历史的主从关系文件

1.  停止mysqld服务，修改配置文件，删除log-bin
2.  删除mysql目录下的所有mysqlbin（binlog）文件和master.info信息
3.  启动服务进入mysql 执行reset slave;
4.  将现在的主库的全备恢复至从库（重要，数据必须一致）
5.  在现在的主库show master status\G; 查看日志文件和Post点
6.  在从库上stop slave;change master; start slave;  show slave ststus\G;查看状态

主从同步廷迟

1.  误删除数据的快速还原 再追加binlog
2.  廷迟测试
3.  历史查询（很少场景会用）
stop slave
change master to master-delay=600;       单位s秒
start slave;
取消，=0;

    Show slave status\G;

此版本在5.5以上才可以配置

主从同步故障

mysql> show master status\G;
*************************** 1. row ***************************
                        File: mysql-binlog.000001
                Position: 309
        Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

ERROR: 
No query specified

Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

原因： change master的时候指定的文件错误或者格式不对，导致无法读取文件

解决方法：找出出错的地方，重新change master

错误提示:
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

/etc/init.d/mysql stop

mysql_salfe --user=mysql --skip-grant-tables --skip-networking &

mysql -u root mysql
mysql>update user set password=password('newpasswd') where user='root';
mysql>flush privileges;
mysql>quit

mysql>SET PASSWORD = PASSWORD('newpasswd');

根据错误代码跳过指定的错误

   方法1：配置文件方式
在配置文件中，添加以下参数，对一些不重要的错误代码自动跳过
vim /etc/my.cnf
--slave-skip-errors=1032，1062，1007  
一般由于入库重复导致的失败可以忽略

   方法2：执么命令
在master端执行锁表，防止新的数据写入
mysql > flush tables with read lock；

    在salve端执行下列语句
stop slave;
set global sql_slave_skip_counter=1;s
start slave;

主从同步常见错误代码

1005：创建表失败
1006：创建数据库失败
1007：数据库已存在，创建数据库失败<=================可以忽略
1008：数据库不存在，删除数据库失败<=================可以忽略
1009：不能删除数据库文件导致删除数据库失败
1010：不能删除数据目录导致删除数据库失败
1011：删除数据库文件失败
1012：不能读取系统表中的记录
1020：记录已被其他用户修改
1021：硬盘剩余空间不足，请加大硬盘可用空间
1022：关键字重复，更改记录失败
1023：关闭时发生错误
1024：读文件错误
1025：更改名字时发生错误
1026：写文件错误
1032：记录不存在<=============================可以忽略
1036：数据表是只读的，不能对它进行修改
1037：系统内存不足，请重启数据库或重启服务器
1038：用于排序的内存不足，请增大排序缓冲区
1040：已到达数据库的最大连接数，请加大数据库可用连接数
1041：系统内存不足
1042：无效的主机名
1043：无效连接
1044：当前用户没有访问数据库的权限
1045：不能连接数据库，用户名或密码错误
1048：字段不能为空
1049：数据库不存在
1050：数据表已存在
1051：数据表不存在
1054：字段不存在
1062：字段值重复，入库失败<==========================可以忽略
1065：无效的SQL语句，SQL语句为空
1081：不能建立Socket连接
1114：数据表已满，不能容纳任何记录
1116：打开的数据表太多
1129：数据库出现异常，请重启数据库
1130：连接数据库失败，没有连接数据库的权限   一般常见的是做授权时授权错误
1133：数据库用户不存在
1141：当前用户无权访问数据库
1142：当前用户无权访问数据表
1143：当前用户无权访问数据表中的字段
1146：数据表不存在
1147：未定义用户对数据表的访问权限
1149：SQL语句语法错误
1158：网络错误，出现读错误，请检查网络连接状况
1159：网络错误，读超时，请检查网络连接状况
1160：网络错误，出现写错误，请检查网络连接状况
1161：网络错误，写超时，请检查网络连接状况
1169：字段值重复，更新记录失败
1177：打开数据表失败
1180：提交事务失败
1181：回滚事务失败
1203：当前用户和数据库建立的连接已到达数据库的最大连接数，请增大可用的数据库连接数或重启数据库
1205：加锁超时
1211：当前用户没有创建用户的权限
1216：外键约束检查失败，更新子表记录失败
1217：外键约束检查失败，删除或修改主表记录失败
1226：当前用户使用的资源已超过所允许的资源，请重启数据库或重启服务器
1227：权限不足，您无权进行此操作
1235：MySQL版本过低，不具有本功能

重新做主从，完全同步

该方法适用于主从库数据相差较大，或者要求数据完全统一的情况
解决步骤如下：
1) 进入mater，进行锁表， 
mysql> flush tables with read lock;
注意：该处是锁定为只读状态，语句不区分大小写
2) 进行数据备份 
 [root@server01 mysql]#mysqldump -uroot -p  mysql> mysql.sql
3) 查看master 状态
mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 |     272 |              |
+-------------------+----------+--------------+-------------------------------+
4) 把mysql备份文件传到从库机器，进行数据恢复
[root@server01mysql]# scp mysql.sql root@192.168.1.13:/tmp/
5) 停止slave的状态
mysql> stop slave;
6) 在slave上恢复数据库
 mysql> source /tmp/mysql.sql
7) 设置从库同步用户
 change master to master_host = '192.168.1.12', master_user = root,master_password='123456', master_log_file = 'mysqld-bin.000001', master_log_pos=272;
8)重新开启从同步
mysql> start slave;
9)查看同步状态
mysql> show slave status\G  查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
10)在master上解锁：mysql>unlock tables;

忽略错误后，继续同步

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况
解决： 
1.  stop slave;
2.  set  global sql_slave_skip_counter =1;     # 1指的是跳一步，可以自定义多少步
3.  start slave;
之后再用mysql> show slave status\G  查看：

数据库硬盘坏了，如何恢复数据？

1. 找一台新设备，先恢复全备数据备份，
2. 恢复备份之后到当前的binlog日志，

The server is not configured as slave; fix in config file or with CHANGE MASTER TO
解决方法：先stop slave;再change master,再start slave;

Waiting to reconnect after a failed master event read
日志报错 Access denied; you need the REPLICATION SLAVE privilege for this operation

Got fatal error 1236 from master when reading data from binary log

原因：可能是在changemaster的时候，指定的bin_log日志的名称书写格式有误，比如多了空格，或者写错字等，

本人经历的方法:

  1. 重启master端的mysqld服务，查看show master status\G; 查看最新post点
  2. 在slave上，执行change master to master_log_file='mysql_bin.000005',master_log_pos=106;

网上方法2：
在source那边，执行：
flush logs;
show master status;
记下File, Position。
在target端，执行：
CHANGE MASTER TO MASTER_LOG_FILE='testdbbinlog.000008',MASTER_LOG_POS=107;
slave start;
show slave status \G
一切正常。

mysql出现同步廷迟原因是什么，如何解决

  1. MySQL数据库主从同步延迟是怎么产生的。
    当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。
  2. 网络延迟
  3. master负载
  4. slave负载
  5. 硬件性能差

一般的做法是，使用多台slave来分摊读请求，再从这些slave中取一台专用的服务器，只作为备份用，不进行其他任何操作，就能相对最大限度地达到’实时’的要求了

优化
答：最简单的减少slave同步延时的方案就是在架构上做优化，尽量让主库的DDL快速执行。还有就是主库是写，对数据安全性较高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog也可以设置为0来提高sql的执行效率。另外就是使用比主库更好的硬件设备作为slave。

另外，再介绍2个可以减少延迟的参数
–slave-net-timeout=seconds
参数含义：当slave从主数据库读取log数据失败后，等待多久重新建立连接并获取数据
slave_net_timeout单位为秒 默认设置为 3600秒
| slave_net_timeout | 3600
–master-connect-retry=seconds
参数含义：当重新建立主从连接时，如果连接建立失败，间隔多久后重试。
master-connect-retry单位为秒 默认设置为 60秒
通常配置以上2个参数可以减少网络问题导致的主从数据同步延迟

Host 'mysql-slave' is not allowed to connect to this MariaDB server

error connecting to master 'tongbu@10.204.3.21:3306' - retry-time: 10 retries: 86400 message: Host 'mysql-slave' is not allowed to connect to this MariaDB server

180408 9:23:13 [ERROR] Slave I/O: error connecting to master 'tongbu@10.204.3.21:3306' - retry-time: 10 retries: 86400 message: Host 'mysql-slave' is not allowed to connect to this MariaDB server, Error_code: 1130

故障原因： 报错误代码1130 主从状态显示IO一直处理无法连接，根据代码判断是授权连接有问题

处理方法：查询主的用户信息，看是不是授权时授权的用户名和IP有错，重新授权

转载于:https://blog.51cto.com/zhongliang/2152917

相关文章：

  • cURL error 60: SSL certificate problem...
  • OSPF动态路由重分发实验
  • 数据库名称
  • 分库分表的面试题5
  • nginx配置.htaccess伪静态
  • pip更改国内源
  • android ndk cmake Invalid Android ABI
  • 基于命令序列的异常行为分析 业界研究现状分析
  • Python标准库（待续）
  • Python 爬虫获取网易云音乐歌手信息
  • WPF查找父元素子元素
  • 如何用vue打造一个移动端音乐播放器
  • VBoot1.0发布,Vue SpringBoot 综合开发入门
  • Taglist: Exuberant ctags (http://ctags.sf.net) not found in PATH. Plugin is not loaded.
  • String 转 Json 记录[abc,bcd] json 解析
  • @angular/forms 源码解析之双向绑定
  • [译] React v16.8： 含有Hooks的版本
  • CAP 一致性协议及应用解析
  • css的样式优先级
  • docker容器内的网络抓包
  • HTTP传输编码增加了传输量，只为解决这一个问题 | 实用 HTTP
  • IOS评论框不贴底（ios12新bug）
  • Javascript Math对象和Date对象常用方法详解
  • JavaScript设计模式系列一：工厂模式
  • Java精华积累：初学者都应该搞懂的问题
  • laravel 用artisan创建自己的模板
  • Less 日常用法
  • SQLServer之创建数据库快照
  • Sublime Text 2/3 绑定Eclipse快捷键
  • 安卓应用性能调试和优化经验分享
  • 服务器之间，相同帐号，实现免密钥登录
  • 更好理解的面向对象的Javascript 1 —— 动态类型和多态
  • 官方新出的 Kotlin 扩展库 KTX，到底帮你干了什么？
  • 诡异！React stopPropagation失灵
  • 入门级的git使用指北
  • 数据科学 第 3 章 11 字符串处理
  • 数据可视化之 Sankey 桑基图的实现
  • 一、python与pycharm的安装
  • 你对linux中grep命令知道多少？
  • Android开发者必备：推荐一款助力开发的开源APP
  • 不要一棍子打翻所有黑盒模型，其实可以让它们发挥作用 ...
  • ​虚拟化系列介绍（十）
  • （第一天）包装对象、作用域、创建对象
  • （二）JAVA使用POI操作excel
  • （分布式缓存）Redis哨兵
  • （转）C#开发微信门户及应用(1)--开始使用微信接口
  • .net程序集学习心得
  • /etc/sudoers (root权限管理)
  • @Autowired注解的实现原理
  • @ResponseBody
  • @软考考生，这份软考高分攻略你须知道
  • [2016.7 test.5] T1
  • [20170705]diff比较执行结果的内容.txt
  • [2669]2-2 Time类的定义
  • [ACL2022] Text Smoothing: 一种在文本分类任务上的数据增强方法