1.主从复制三种核心格式:
1.基于语句级别复制(statement based replication);2.基于行级复制(row based replication);3混合复制(mixed based replication);在数据库中查看当前使用的是哪个级别:show variables like "%binlog_format%";+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW |+---------------+-------+默认新版本中的复制模式默认都是行级别;2.主从复制过程图解
mysql主从复制过程图解
二.基于binlog的复制;
1.查看binlog日志所在位置.(8.0版本默认是开启binlog的状态)
查看binlog是否开启,以及当前server_id(注意server_id代表当前实例的唯一标识,不能与其他实例相同);show variables like "%log_bin%";+---------------------------------+------------------------------------+| Variable_name | Value |+---------------------------------+------------------------------------+| log_bin | ON || log_bin_basename | /usr/local/mysql/data/binlog || log_bin_index | /usr/local/mysql/data/binlog.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |+---------------------------------+------------------------------------+其中log_bin表示当前数据库已经开启bin_log日志log_bin #表示当前binlog日志是否开启log_bin_basename #表示当前binlog日志存放位置log_bin_index #我也不知道2.查看当前数据库的server_id
show variables like "%server_id%";+----------------+-------+| Variable_name | Value |+----------------+-------+| server_id | 1 || server_id_bits | 32 |+----------------+-------+3.创建用于专门主从复制用的用户.(主库创建)
create user "repl"@"192.168.15.%" identified by "mysql";丛库所在服务器链接一下.链接成功,给给用户授权.grant replication slave on *.* to "repl"@"192.168.15.%";查看权限show grants for "repl"@"192.168.15.%";5.将主数据库中的数据进行mysqldump备份,
1.直接进行数据库备份 添加参数 --all-databases -master-data=2 --single-transaction 参数参数说明:--all-databases #表示将所有的数据库全部进行数据备份-master-data=2 #将当前数据库中文件的binlog节点信息注释到备份文件中.--single-transaction #会在备份的时候添加一个瞬时的锁定,为数据库创建一个快照,然后再备份过冲中不会对数据的写入造成影响.并且不会造成数据丛库数据恢复后的同步节点与主库不一致的情况.语句如下:mysqldump --all-databases --master-data=2 --single-transaction -u root -p -P 3306 > dbdump.db6.查看已经备份好的数据中使用的binlog文件,以及master_log_pos偏移量是多少.并记录
备份文件中的前半部分显示信息中-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000026', MASTER_LOG_POS=155;表示的就是对应的binlog日志位置以及master_log_pos偏移量7.将已经备份好的数据dbdump.db文件通过操作系统将数据传输到丛库所在的系统中.
scp dbdump.db root@192.168.15.21:/zpf/表示将这个文件传输到21机器上的/zpf文件目录中.8.文件传输完成后进行文件恢复.
进入/zpf目录中进入数据库服务,然后再数据库内部操作数据恢复命令:source db.dumo.db;即可将已经备份好的数据全部恢复到数据库中,有责覆盖无则添加.
10.执行主从同步命令CHANGE MASTER TOMASTER_HOST=‘master_host_name’, ##主库的主机名MASTER_PORT=port_number, ##主库的端口号MASTER_USER=‘replication_user_name’, ##复制的数据库用户名MASTER_PASSWORD=‘replication_password’, ##复制的用户密码MASTER_LOG_FILE=‘recorded_log_file_name’, ##主库的日志文件名MASTER_LOG_POS=recorded_log_position; ##主库的日志文件位置备份完成以后查看前面30行数据就会看到change_master的信息.CHANGE MASTER TOMASTER_HOST='192.168.15.21',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='mysql',MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=388;注释如果是报错12360首先看一下日志.是不是找不到这个binlog日志.如果是的话修改一下master_log_file,使之与主节点同步.11.之所以要把这一行写的大一点是因为要警示一下自己的傻逼行为.
实验的时候遇到的坑.
之前做实验的时候将在配置文件中将链接autocommit的设置成0了.然后他大爷的就各种主从同步不成功.各种IO线程起不来.以后遇到IO线程起不来的问题,一般都是如下几种情况1.用户名不对.(创建好链接一下试试)2.密码不对(同上)3.日志不对.binlog.0000014.日志偏移量pos不对.(从备份中查看.)5.自己吧autocommit关了.12.如果主库中日志文件已经删除.又想再做一个丛库的话.
1.首先将主库flush table with read lock2.丛库停机.3.将丛库的数据目录整体打包并传送到2号丛库服务器上.4.解压数据目录到mysql数据库指定的数据目录位置.5.删除2号丛库auto.cnf修改并且server_id.6.查看主库中binlog日志偏移量以及日志名称.7.启动2号丛库.定义changemaster,8.启动2号丛库slave.查看状态.9.启动一号丛库,并启动slave.查看状态.10.主库unlock table;注释:如果需要开启级联复制那么需要将丛库中的mysqlbinlog也进开启.(8.0默认就是开启的)show variables like "%update%";+-----------------------------------------+-------+| Variable_name | Value |+-----------------------------------------+-------+| binlog_direct_non_transactional_updates | OFF || log_slave_updates | ON || low_priority_updates | OFF || sql_safe_updates | OFF |+-----------------------------------------+-------+4 rows in set (0.03 sec)
13相关参数:
1.查看relay.logmysqlbinlog -v mysql-02-relay-bin.0000032.有选择性的进行主从同步.配置文件中添加[mysqld]区块中replicate-do-db = "course"; #运行course数据库进行主从同步replicate-ignone-db =''A1' #表示将某个数据库排出同步.replicate-do-table = course.teacher #表示仅仅将某张表进行数据同步.replicate-ignone-db = course.students #表示将某个表过滤掉.在进行数据库选择性同步的时候.同步的relay-log中会记录所有的数据信息.但是sql线程会根据相应的规则选取符合条件的进行同步.所以在进行binlog同步的时候要选择行级模式.不会出现意外.语句级复制弊端:例如在A1库下修改A2数据库中表的数据.入股replicate仅仅指定复制A!数据库的话,那么在丛库中A2数据也会被顺利插入.反之如果在A2数据库中操作A1数据库中的表,那么在丛库中A1数据库中的数据并不会出现在丛库对应的表中.3.主从同步过程中出现丛库sql线程一直处于增长状态.那么可以添加相关工作线程.在配置文件中设置slave-paralle-workers=5设置多个线程同时工作.用来缓解单线程压力.4.丛库错误信息跳过slave_skip_error 跳过同步错误. 后面跟错误编码.一般不用5.查看主从同步状态信息show slave status\G14.相关监控指标
1.seconds_behind_master表示主从库延迟时间.单位s.如果在监控过程中这个时间越来越大.表示丛库写入速度追不上主库.建议添加多线程.2.SQL_Remaining_Delay显示剩余的主备延迟时间，单位秒3.SQL_Delay一个设置好的主从福字时间.是一个丛库比主库延迟多少.4.slave_IO_state:Waiting for master to send event下面我们逐个来解读一下：Slave_IO_State：等待主节点发送事件 | 表示IO线程的状态 |
Master_Host：192.168.50.3 | 主的IP地址 |
Master_User：repl | 主从复制的用户 |
Master_Port：3306 | 主机连接的端口 |
Connect_Retry：60 | IO线程重连次数 |
Master_Log_File：mysql-bin.000001 | IO线程正在读取的master上的binlog文件 |
Read_Master_Log_Pos：728 | 正在读取的主上的二进制日志文件位置 |
Relay_Log_File：relay-bin.000002 | 存放的本地relay log文件名 |
Relay_Log_Pos：941 | 存放的本地中继日志位置 |
Relay_Master_Log_File：mysql-bin.000001 | SQL线程正在执行的主binlog文件 |
Slave_IO_Running：是的 | IO线程状态 |
Slave_SQL_Running：是的 | SQL线程状态 |
Replicate_Do_DB： | 指定复制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：728 | 执行masterbinlog的位置 |
Relay_Log_Space：1142 | 所有relay log字节数总和 |
Until_Condition：无 | 指定复制条件 |
Until_Log_File： | 指定复制到某个文件 |
Until_Log_Pos：0 | 指定复制到某个位置 |
Seconds_Behind_Master：0 | 从库落后主库时间，单位秒 |
Last_IO_Errno：0 | 最近一次IO thread错误 |
Last_IO_Error | 最近一次IO thread错误详细信息 |
Last_SQL_Errno：0 | 最近一次sql thread错误 |
Last_SQL_Error： | 最近一次sql thread错误详细信息 |
Replicate_Ignore_Server_Ids： | 复制忽略SERVER_ID为XXX的实例 |
Master_Server_Id：1003306 | 主server_id |
Master_UUID：dff47046-3ad5-11e8-86fe-080027de0e0e | 掌握uuid |
Master_Info_File：/storage/mysql/data/master.info | 记录主信息文件 |
SQL_Delay：0 | SQL延迟多少时间，延迟复制会用到 |
SQL_Remaining_Delay：NULL | 当sql thread状态为等待主执行事件后的MASTER_DELAY秒，那么该值表示剩下延迟多少时间 |
Slave_SQL_Running_State：Slave已读取所有中继日志; 等待更多更新 | sql thread状态 |
Master_Retry_Count：86400 | 主从复制断开时从最多能尝试重新连接的次数 |
Master_Bind： | 绑定网卡 |
Last_IO_Error_Timestamp： | io thread最近一次的错误时间 |
Last_SQL_Error_Timestamp： | sql thread最近一次的错误时间 |
Retrieved_Gtid_Set：dff47046-3ad5-11e8-86fe-080027de0e0e：1-3 | IO线程获取到的GTID |
Executed_Gtid_Set：dff47046-3ad5-11e8-86fe-080027de0e0e：1-3 | SQL线程执行的GTID |
Auto_Position：1 | 使用auto_position，建议还是开启比较方便 |
Replicate_Rewrite_DB： | 复制DB对应关系 |
CHANNEL_NAME： | 多源复制的频道名称 |
16.如果出现数据库缓慢的情况可以首先查看一下主从同步状态.看下是sql线程缓慢还是IO线程缓慢.
如果是sql线程缓慢的话可以再物理机资源允许的情况下添加更多的sql线程来缓解丛库写入速度.如果是IO缓慢的话.可以关闭binlog日志书写.降低丛库中binlog的记录来降低IO操作次数.如果是网络传输缓慢的话.例如出现主库数据写入.一般情况是一主多从得情况下.主库出现网络压力高.可以减少一级丛库数量.然后配置级联复制.可以减少主从库的数据传输.让其他数据库进行数据传输的分担.如果出现主从库延时过高.还可以查看是否所有数据库都需要进行同步.例如部分历史记录数据库可以不再丛库中体现.或者在指定丛库中进行同步.那么就可以将丛库设置成replicate-do-db部分数据库.其余数据库不进行数据同步.如果出现主库压力过高.丛库没啥压力.查看一下1.数据库是否是周期性出现io 和负载高的话查看一下是否是因为定时任务进行的数据备份导致.建议将数据备份转移到丛库进行业务低峰期执行.如果不定时出现主库压力大.丛库没啥压力.看下是否代码层面没做读写分离.或者自己使用部分中间件做读写分离来实现.一般情况下都是sql线程需要优化.丛库配置一般都会比较低.最终级大招就是服务器升级.全部SSD解决百分之九十的问题17.主从库延迟复制
1.丛库需要首先简历主从复制.2.stop slave; #停止主从复制3.CHANGE MASTER TO MASTER_DELAY = 60; #设置组从延时为60秒.4.start slave; #开始主从复制.5.show slave status \G #查看主从复制状态.其中的SQL_Delay: 60SQL_Remaining_Delay: 57表示当前主从库延迟时间.丛库执行剩余时间.每个事务都会单独生成一个剩余时间.但是当前显示的是按照执行顺序远近一次显示.每次显示一个.18.为了防止主库误操作.建议丛库使用延迟复制.以确保主库中的数据安全.
主库数据删除后的数据恢复.1.首先停止主库写入.将数据库切换到丛库.丛库开启读取权限.不做任何写入.2.将丛库中存在但是主库中误删的数据库或者表.进行备份.3.将备份数据传到主库服务器上.4.主库恢复数据.(如果有binlog日志也需要逐一进行恢复.)5.将业务切换到主库上.6.将丛库对应数据库或者数据表删除.让两个数据库自然同步.8.丛库开启数据同步(不用延时同步.)9.数据同步完成后.丛库重新开启异步复制.主库开启读写权限.19.半同步复制.
1.要搭建主从同步首先要在主从库分别安装相应的插件.主库安装:INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';丛库安装:INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';查看插件是否安装成功:select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";2.首先主库开启办公部复制:SET GLOBAL rpl_semi_sync_slave_enabled =1；SET GLOBAL rpl_semi_sync_master_timeout = N; #N是指毫秒,默认是10000,代表10秒.3.在丛库上开启半同步复制SET GLOBAL rpl_semi_sync_slave_enabled =1；4.丛库重启主从复制.stop slave;start slave;5.验证主库数据插入正常.并在丛库查看数据同步是否正常.6.丛库关闭IO线程.验证半同步复制是否生效.stop slave IO_thread;7.主库插入数据.如果出现卡顿,数据未能及时插入.则表示半同步设置成功.8.丛库开启IO线程start slave Io_thread;9.如果主库插入数据长时间被卡主.会在设定的10000毫秒的时间后自动转换为异步复制.这个时间是在主库中设置的.10.丛库查看半同步复制是否真的转换成异步复制.show status like '%rpl_semi%'; #+----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| Rpl_semi_sync_slave_status | ON |+----------------------------+-------+19.半同步复制相关参数
1.主库查看各个参数的状态SHOW STATUS LIKE 'rpl_semi_sync%';+--------------------------------------------+-------+| Variable_name | Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients | 1 | #检查半同步slave的个数| Rpl_semi_sync_master_net_avg_wait_time | 0 || Rpl_semi_sync_master_net_wait_time | 0 || Rpl_semi_sync_master_net_waits | 4 || Rpl_semi_sync_master_no_times | 1 || Rpl_semi_sync_master_no_tx | 1 | #表示有多少个没有提交到slave的确认消息.| Rpl_semi_sync_master_status | ON | #当前半同步的状态是否开启| Rpl_semi_sync_master_timefunc_failures | 0 || Rpl_semi_sync_master_tx_avg_wait_time | 434 || Rpl_semi_sync_master_tx_wait_time | 868 || Rpl_semi_sync_master_tx_waits | 2 || Rpl_semi_sync_master_wait_pos_backtraverse | 0 || Rpl_semi_sync_master_wait_sessions | 0 || Rpl_semi_sync_master_yes_tx | 2 | #半同步成功提交到slave节点的事务的个数+--------------------------------------------+-------+20.自己试了一下将binlog日志定向到文件中以后可以直接使用文件对数据进行恢复.
21.提示
数据库中binlog日志记录的都是data change 操作.就是数据的变更操作.如果是查询的话是不会出现在binlog日志当中的.其中增删改,以及表结构的修改,添加索引.改变字段长度等等,都是属于data change操作.如果事务没有提交的话那么日志中是不会将修改的记录进行增加到日志当中.rollback 不会记录到日志当中.
