wordpress图像调用,windows10系统优化,无锡网站 app,使用cms快速搭建商业网站Mysql主从复制模式 主从复制有一主一从、主主复制、一主多从、多主一从等多种模式. 我们可以根据它们的优缺点选择适合自身企业情况的主从复制模式进行搭建 .
一主一从 主主复制 (互为主从模式): 实现Mysql多活部署 一主多从: 提高整个集群的读能力 多主一从: 提高整个集群的…Mysql主从复制模式 主从复制有一主一从、主主复制、一主多从、多主一从等多种模式. 我们可以根据它们的优缺点选择适合自身企业情况的主从复制模式进行搭建 .
一主一从 主主复制 (互为主从模式): 实现Mysql多活部署 一主多从: 提高整个集群的读能力 多主一从: 提高整个集群的高可用能力 级联复制: 减轻主节点进行数据同步的压力 MySQL 主从复制原理 MySQL 服务的主从架构都是通过 binlog 日志文件来进行的。 具体流程如下 在主服务上打开binlog记录每一步的数据库操作 然后从服务上会有一个IO线程负责跟主服务建立一个TCP连接请求主服务将binlog传输过来 这时主库上会有一个IO dump线程负责通过这个TCP连接把binlog日志传输给从库的IO线程 主服务器MySQL服务将所有的写操作记录在 binlog 日志中并生成 log dump 线程将 binlog 日志传给从服务器MySQL服务的 I/O 线程。 接着从服务的IO线程会把读取到的binlog日志数据写入自己的relay日志文件中。 然后从服务上另外一个SQL线程会读取relay日志里的内容进行操作重演达到还原数据的目的。 注意 主从复制是异步的逻辑的 SQL 语句级的复制 复制时主库有一个 I/O 线程从库有两个线程即 I/O 和 SQL 线程 实现主从复制的必要条件是主库要开启记录 binlog 的功能 作为复制的所有 MySQL 节点的 server-id 都不能相同 binlog 文件只记录对数据内容有更改的 SQL 语句不记录任何查询语句 双方MySQL必须版本一致至少需要主服务的版本低于从服务 两节点间的时间需要同步 MySQL 主从复制方式 MySQL5.6 开始主从复制有两种方式 基于 binlog 日志的Pos进行主从复制基于 GTID全局事务标示符进行主从复制 接下来根据一主一从来配置主从复制的方式。 案例基于Pos主从复制 主服务器配置 第一步开启 binlog 日志 查询 binlog 日志是否开启 mysql show variables like log_bin%; MySQL5.7 版本中binlog默认是关闭的8.0版本默认是打开的打开binlog功能需要修改配置文件my.ini(windows)或my.cnf(linux)然后重启数据库。
在配置文件中的[mysqld]部分增加如下配置:
# binlog刷盘策略
sync_binlog1
# log-bin设置binlog的存放位置可以是绝对路径也可以是相对路径这里写的相对路径则binlog文件默认会放在data数据目录下
log-binmysql-binlog
# 其他配置
binlog_format row # 日志文件格式下面会详细解释
expire_logs_days 15 # 执行自动删除距离当前15天以前的binlog日志文件的天数 默认为0 表示不自动删除
max_binlog_size 800M # 单个binlog日志文件的大小限制默认为 1GB 再次查询 binlog 日志是否开启 第二步修改my.cnf文件 ,添加服务id
# Server Id是数据库服务器id随便写一个数都可以这个id用来在mysql集群环境中标记唯一mysql服务器集群环境中每台mysql服务器的id不能一样不加启动会报错
server-id130 第三步重启 mysql 服务 [rootxc0tfmuy0yaql06x ~]# systemctl restart mysqld 第四步主机给从机授备份权限 登陆mysql [rootxc0tfmuy0yaql06x ~]# mysql -uroot -p 登陆后,授权. mysql GRANT REPLICATION SLAVE ON *.* TO root% identified by root; 第五步刷新权限 mysql FLUSH PRIVILEGES; 第六步查询 master 的状态 mysql show master status; 从服务器配置 第一步修改 my.conf 文件 [mysqld]
server-id131 第二步重启 mysqld 服务 systemctl restart mysqld 1 第三步重启并登录到 MySQL 进行配置 Slave mysqlchange master to
master_host113.125.182.96,
master_port3306,
master_userroot,
master_passwordroot,
master_log_filemysql-binlog.000002,
master_log_pos589,
MASTER_AUTO_POSITION0; 注意 语句中间不要断开 master_port 为 MySQL 服务器端口号无引号 master_user 为 执行同步操作的数据库账户 593 无单引号此处的 1109 就是 show master status 中看到的 position 的值这里的 mysql - bin.000001 就是 file 对应的值。 第四步启动从服务器复制功能 mysqlstart slave; 第五步检查从服务器复制功能状态 mysql show slave status \G;
……………………(省略部分)
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
……………………(省略部分) 注Slave_IO及Slave_SQL进程必须正常运行即YES状态否则都是错误的状态(如其中一个NO均 属错误)。 测试 通过Pos配置的主从复制已全部完成,现在我们通过测试,看是否配置成功. 在主服务器中,选择自己已创建的数据库 mysql use gorgor; 在主服务器中, 创建表 mysql CREATE TABLE user (id int(11) NOT NULL AUTO_INCREMENT,name varchar(255) NOT NULL,age int(11) NOT NULL,create_time datetime NOT NULL,update_time datetime DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8; 在主服务器中,查看表的信息. 在从服务器中,查看表的信息 发现已同步过去从库了,代表配置成功了. 案例基于GTID的主从复制 什么是 GTID 从 MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。 GTID 即全局事务 ID Global Transaction Identifier 其保证每个主节点上提交的事务在从节点可以一致性的复制。 这种方式强化了数据库的主备一致性故障恢复以及容错能力。 GTID 在 一主一从 情况下没有优势对于两主以上 的结构优势异常明显可以在数据不丢失的情况下切换新主。 GTID 实际上是由 UUIDTID ( 即 transactionId) 组成的其中 UUID( 即 server_uuid) 产生于 auto.conf 文 件是一个 MySQL 实例的唯一标识。 TID 代表了该实例上已经提交的事务数量并且随着事务提交单调递增所以 GTID 能够保证每个 MySQL 实例事务的执行。 GTID 在一组复制中全局唯一。 通过 GTID 的UUID 可以知道这个事务在哪个实例上提交的。 server_uuid 查询命令
[rootxc0tfmuy0yaql06x ~]# cat /var/lib/mysql/auto.cnf; 查询GTID
mysql show master status;
mysql show master status;
-------------------------------------------------------------------------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-------------------------------------------------------------------------------------------------------------------------------------------------
| mysql-binlog.000006 | 4874 | | | f93b2c7b-8a01-11ef-b45c-fa163ec11a89:1-6
-------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec) Executed_Gtid_Set 就是GTID,
UUID: f93b2c7b-8a01-11ef-b45c-fa163ec11a89
TID: 1-6
TID 是在该主库上生成的事务序列号从1 开始 1 -6 代表第六个事务第 1 - n 代表 n 个事 务. GTID复制的限制 由于基于GTID的复制依赖于事务在使用GTID时有些MySQL特性则不支持。
1. 事务中混合多个存储引擎会产生多个GTID。当使用GTID时候若在同一事务中更新包括了非事务引擎如MyisAM和事务性引擎InnoDB表的操作则会导致GTID分配给同一个事务。2.主从库的表存储引擎不一致则会导致数据不一致若主从库的存储引擎不一致比如一个是事务存储引擎一个是非事务存储引擎 则会导致事务和GTID之间的一对一的关系被破坏导致基于GTID的复制不能正确运行。3.基于GTID模式复制 不支持create table .. select 语句因为使用基于行模式的复制时该语句实际上被记录为2个单独的事件一个是创建表一个是将原表中的数据插入到刚刚创建的新表中。当在事务中执行该语句时候在某些情况下这两个事务可能接受到相同的事务ID这意味着包含插入的事务将被从库跳过。因此不支持此语句.4.不支持create temporary table和drop temporary table
使用GTID复制模式时不支持create temporary table 和 drop temporary table。但是在autocommit1的情况下可以创建临时表master创建临时表不产生GTID信息所以不会同步到slave但是在删除临时表的时候会产生GTID会导致主从中断。5.不推荐在GTID模式的实例下进行mysql_upgrade:
因为mysql_upgrade的过程要创建或修改系统表(非事务引擎)所以不建议在开启GTID模式的实例上使用带有 --write-binlog选项的mysql_upgrade。 GTID 主从复制原理 1. Master 更新数据时会在事务前产生 GTID 一同记录到 binlog 日志中 2. Slave 的 IO Thread 将变更后的 binlog 写入到本地的 relaylog 中这其中含有 Master 的 GTID 3. SQL Thread 读取这个 GTID 的值并设置 GTID_NEXT 变量告诉 Slave 下一个要执行的 GTID 值然后对比 Slave 端的 binlog 是否有该 GTID 如果有说明该 GTID 的事务已经执行 Slave 会忽略 如果没有Slave 就会执行该 GTID 事务并记录该 GTID 到自身的 binlog 4. 在解析过程中会判断是否有主键如果没有就用二级索引如果没有二级索引就用全表扫描 搭建GTID同步集群 他的搭建方式跟我们上面的主从架构整体搭建方式差不多。只是需要在 my.cnf 中修改一些配置。 主服务器配置
gtid_modeon
enforce_gtid_consistencyon
从服务器配置
gtid_modeon
enforce_gtid_consistencyon
# 做级联复制的时候再开启。允许下端接入slave
log_slave_updates1
# 避免启动后还是使用老的复制协议
skip_slave_start1
在从库上使用--skip-slave-start选项启动从数据库也可以在my.cnf中加入skip-slave-start配置这样不会立即启动从数据库服务器上的复制进行方便做进一步配置。 启动 GTID 的两种情况 分别重启主服务和从服务就可以开启 GTID 同步复制启动方法有两种情况 情况一如果是新搭建的服务器直接启动就行了 情况二如果是在已经跑的服务器需要重启 mysqld 启动之前要先关闭master的写入保证所有slave端都已经和master端数据保持同步 所有slave需要加上skip_slave_start1的配置参数避免启动后还是使用老的复制协议 # 避免启动后还是使用老的复制协议
skip_slave_start1 使用 GTID 的方式 salve 重新挂载 master 端 启动以后最好不要立即执行事务先 change master 上然后在执行事务。 使用下面的 sql 切换 slave 到新的 master 。 # 停止从节点
stop slave;
# 切换主节点配置比基于pos简单不少
change master to
master_host113.125.182.96,
master_port3306,
master_userroot,
master_passwordroot,
master_auto_position1;
# 启动从节点
start slave; 检查从服务器复制功能状态 mysql show slave status \G;
……………………(省略部分)
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
……………………(省略部分) 注Slave_IO及Slave_SQL进程必须正常运行即YES状态否则都是错误的状态(如其中一个NO均 属错误)。 异常 此时,通过结果发现, mysql从Pos模式切到GTID模式后启动主从主从异常报错1236 mysql show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Master_Host: 113.125.182.96Master_User: rootMaster_Port: 3306Connect_Retry: 60Master_Log_File: Read_Master_Log_Pos: 4Relay_Log_File: VM-20-3-centos-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: Slave_IO_Running: NoSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 0Relay_Log_Space: 154Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 1236Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION 1, but the master has purged binary logs containing GTIDs that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the masters binary log expiration period. The GTID set sent by the slave is , and the missing transactions are 458fa628-89dc-11ef-809d-00163e019b7a:1.Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 130Master_UUID: f93b2c7b-8a01-11ef-b45c-fa163ec11a89Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: 241015 18:04:31Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
1 row in set (0.00 sec)ERROR:
No query specified异常信息 Got fatal error 1236 from master when reading data from binary log: The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION 1, but the master has purged binary logs containing GTIDs that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the masters binary log expiration period. The GTID set sent by the slave is , and the missing transactions are 458fa628-89dc-11ef-809d-00163e019b7a:1. 解决办法(从节点从来没有过gtid 值的时候 在主节点执行 mysql show variables like %gtid_purged%;
-------------------------------------------------------
| Variable_name | Value |
-------------------------------------------------------
| gtid_purged | 458fa628-89dc-11ef-809d-00163e019b7a:1 |
-------------------------------------------------------
1 row in set (0.00 sec)然后再在从节点设置gtid_purged为主节点gtid_purged值。 stop slave;
set global gtid_purged458fa628-89dc-11ef-809d-00163e019b7a:1;
start slave;
show slave status\G 然后通过show slave status\G,发现Slave_IO及Slave_SQL进程都正常运行即YES状态. 测试 在主服务器中,插入一条数据 INSERT INTO gorgor.user(id, name, age, create_time, update_time) VALUES (2, fairy, 28, 2024-10-15 17:47:56, NULL); 在从服务器中,查看表的数据,可以查出此数据 mysql select * from user;
---------------------------------------------------
| id | name | age | create_time | update_time |
---------------------------------------------------
| 1 | gorgor | 30 | 2024-10-15 15:56:25 | NULL |
| 2 | fairy | 28 | 2024-10-15 17:47:56 | NULL |
---------------------------------------------------
2 rows in set (0.00 sec)
