侧边栏壁纸
博主头像
落叶人生博主等级

走进秋风，寻找秋天的落叶

  • 累计撰写 130562 篇文章
  • 累计创建 28 个标签
  • 累计收到 9 条评论
标签搜索

目 录CONTENT

文章目录
数据库

mysql 主从同步详细配置教程

2024-05-12 星期日 / 0 评论 / 0 点赞 / 97 阅读 / 21091 字

Mysql 主从同步主从原理mysql主从同步的原理： 1、在master上开启bin-log日志，用于记录master上的更改删的一些记录。 2、主从各开启io线程，从上开启io线程和s

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: 0Master_SSL_Verify_Server_Cert: No                                Last_IO_Errno: 0                                Last_IO_Error:                              Last_SQL_Errno: 0                             Last_SQL_Error:

同步状态成功的参考值：

Slave_IO_Running: YesSlave_SQL_Running: Yes IO线程和SQL线程都为YESMaster_Log_File: mysql-bin.000004Relay_Master_Log_File: mysql-bin.000004主日志文件和从读取的文件为同一个Read_Master_Log_Pos: 239948407Exec_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=1log-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=mysqlMYPASSWD=123456MYSOCK=/usr/local/mysql/var/mysql.sockLOG_FILE=${DATE_PATH}/mysqllogs_`date +%F`.logDATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gzMYSQL_PATH=/usr/local/mysql/binMYSQL_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: NoSlave_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-onlyb)  在数据库目录下清除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-binb)  删除本地的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-bin2.  删除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.  误删除数据的快速还原 再追加binlog2.  廷迟测试3.  历史查询（很少场景会用）stop slavechange 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 specifiedGot 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 stopmysql_salfe --user=mysql --skip-grant-tables --skip-networking &mysql -u root mysqlmysql>update user set password=password('newpasswd') where user='root';mysql>flush privileges;mysql>quitmysql>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;sstart 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.sql3) 查看master 状态mysql> show master status;+-------------------+----------+--------------+-------------------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |+-------------------+----------+--------------+-------------------------------+| mysqld-bin.000001 |     272 |              |+-------------------+----------+--------------+-------------------------------+4) 把mysql备份文件传到从库机器，进行数据恢复[root@server01mysql]# scp mysql.sql [email protected]:/tmp/5) 停止slave的状态mysql> stop slave;6) 在slave上恢复数据库 mysql> source /tmp/mysql.sql7) 设置从库同步用户 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: YesSlave_SQL_Running: Yes10)在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 '[email protected]: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 '[email protected]: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://www.qiuyue.org/essay/sql/122543.html
广告 广告

评论区