锐客网

  1. 首页 > 生活百科 > it技术 > >

mysql主从表怎么做 mysql主从常见问题

IT技术主从

MySQL的主从配置步骤你会那几个？我和大家分享下我的几个经验一、登录Master服务器,修改my.ini
,添加如下内容：[*]#数据库ID号 ，
为1时表示为Master,其中master_id必须为1到232–1之间的一个正整数值;[*]server-id
=
1[*]#启用二进制日志；[*]log-bin=mysql-bin[*]#需要同步的二进制数据库名；[*]binlog-do-db=ultrax[*]#不同步的二进制数据库名,如果不设置可以将其注释掉;[*]binlog-ignore-db=mysql[*]#设定生成的log文件名；[*]log-bin="E:/Database/materlog"[*]#把更新的记录写到二进制文件中；[*]log-slave-updates[*]#跳过错误，继续执行复制；[*]slave-skip-errors配置完重启
mysql
MySQL 主从，5 分钟带你掌握MySQL 主从一直是面试常客，里面的知识点虽然基?。悄芑卮鹑耐Р欢?。
比如楼哥之前面试小米，就被问到过主从复制的原理，以及主从延迟的解决方案，因为回答的非常不错，给面试官留下非常好的印象 。你之前面试，有遇到过哪些 MySQL 主从的问题呢？
所谓 MySQL 主从，就是建立两个完全一样的数据库，一个是主库，一个是从库，主库对外提供读写的操作，从库对外提供读的操作，下面是一主一从模式：
对于数据库单机部署，在 4 核 8G 的机器上运行 MySQL 5.7 时，大概可以支撑 500 的 TPS 和 10000 的 QPS ， 当遇到一些活动时，查询流量骤然，就需要进行主从分离 。
大部分系统的访问模型是读多写少 ， 读写请求量的差距可能达到几个数量级 ， 所以我们可以通过一主多从的方式，主库只负责写入和部分核心逻辑的查询，多个从库只负责查询，提升查询性能，降低主库压力 。
MySQL 主从还能做到服务高可用 ， 当主库宕机时 ， 从库可以切成主库 ， 保证服务的高可用 ， 然后主库也可以做数据的容灾备份 。
整体场景总结如下：
MySQL 的主从复制是依赖于 binlog 的 ， 也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上二进制日志文件 。
主从复制就是将 binlog 中的数据从主库传输到从库上，一般这个过程是异步的，即主库上的操作不会等待 binlog 同步的完成 。
详细流程如下：
当主库和从库数据同步时，突然中断怎么办？因为主库与从库之间维持了一个长链接，主库内部有一个线程，专门服务于从库的这个长链接的 。
对于下面的情况，假如主库执行如下 SQL，其中 a 和 create_time 都是索引：
我们知道，数据选择了 a 索引和选择 create_time 索引，最后 limit 1 出来的数据一般是不一样的 。
所以就会存在这种情况：在 binlog = statement 格式时，主库在执行这条 SQL 时，使用的是索引 a，而从库在执行这条 SQL 时，使用了索引 create_time，最后主从数据不一致了 。
那么我们改如何解决呢？
可以把 binlog 格式修改为 row，row 格式的 binlog 日志记录的不是 SQL 原文，而是两个 event:Table_map 和 Delete_rows 。
Table_map event 说明要操作的表，Delete_rows event用于定义要删除的行为，记录删除的具体行数 。row 格式的 binlog 记录的就是要删除的主键 ID 信息，因此不会出现主从不一致的问题 。
但是如果 SQL 删除 10 万行数据，使用 row 格式就会很占空间的，10 万条数据都在 binlog 里面 ， 写 binlog 的时候也很耗 IO 。但是 statement 格式的 binlog 可能会导致数据不一致 。
设计 MySQL 的大叔想了一个折中的方案，mixed 格式的 binlog，其实就是 row 和 statement 格式混合使用，当 MySQL 判断可能数据不一致时 ， 就用 row 格式 ， 否则使用就用 statement 格式 。
有时候我们遇到从数据库中获取不到信息的诡异问题时，会纠结于代码中是否有一些逻辑会把之前写入的内容删除，但是你又会发现，过了一段时间再去查询时又可以读到数据了，这基本上就是主从延迟在作怪 。
主从延迟，其实就是“从库回放” 完成的时间 ， 与 “主库写 binlog” 完成时间的差值，会导致从库查询的数据，和主库的不一致。
谈到 MySQL 数据库主从同步延迟原理，得从 MySQL 的主从复制原理说起：
总结一下主从延迟的主要原因：主从延迟主要是出现在 “relay log 回放” 这一步 ， 当主库的 TPS 并发较高，产生的 DDL 数量超过从库一个 SQL 线程所能承受的范围，那么延时就产生了 ， 当然还有就是可能与从库的大型 query 语句产生了锁等待 。
我们一般会把从库落后的时间作为一个重点的数据库指标做监控和报警，正常的时间是在毫秒级别 ， 一旦落后的时间达到了秒级别就需要告警了 。
解决该问题的方法，除了缩短主从延迟的时间，还有一些其它的方法，基本原理都是尽量不查询从库 。
具体解决方案如下：
在实际应用场景中，对于一些非常核心的场景，比如库存，支付订单等 ， 需要直接查询从库，其它非核心场景，就不要去查主库了 。
两台机器 A 和 B，A 为主库，负责读写，B 为从库 ， 负责读数据 。
如果 A 库发生故障，B 库成为主库负责读写，修复故障后，A 成为从库，主库 B 同步数据到从库 A 。
一台主库多台从库 ， A 为主库，负责读写，B、C、D为从库，负责读数据 。
如果 A 库发生故障，B 库成为主库负责读写，C、D负责读 ， 修复故障后，A 也成为从库，主库 B 同步数据到从库 A 。
如何在一台windows主机上搭建mysql主从配置先在主数据库中创建新数据库rep_test 。
然后编辑主数据库的my.ini文件
在[mysqld]节点中增加如下内容：
server-id=1
#指定唯一的ID，1至32 ， 必须的
log-bin=mysql-log-bin
#指定二进制日志存放路径，必须的
binlog-do-db=rep_test
#指定要同步的数据库，必须的
#binlog-ignore-db=mysql
#指定不要同步的数据库，如果指定了binlog-do-db就不用再指定该项
重启主数据库，然后在主数据库中建立一个备份账户
mysqlgrant
replication
slave
on
*.*
to slave@192.168.1.128
identified
by
'slave'
;
mysqlflush
privileges;
PS:identified
by
指定的slave是账号slave@192.168.1.128
的密码
显示主服务器的状态信息，并且找到File
和
Position
的值记录下来；
mysqlshow
master
status;
在从数据库中创建新的数据库rep_test 。
然后编辑从数据库的my.ini文件
在[mysqld]节点中增加如下内容：
server-id=2
#指定唯一的ID，2至32，必须的，并且不能跟主数据库一样
replicate-do-db=rep_test
#指定要同步的数据库，必须的
#replicate-ignore-db=mysql
#指定不要同步的数据库，
重启从数据库，设置登录主数据库的账号和密码等信息，然后启动slave
mysqlchange
master
to
master_host='192.168.1.2',master_user='slave',master_password='slave',
master_log_file='mysql-bin.000002',master_log_pos=120;
mysqlstart
slave;
查看从数据库的信息
mysqlshow
slave
status
\G;
如果出现： Slave_IO_Running:
YesSlave_SQL_Running:
Yes以上两项都为Yes，那说明没问题了
测试主从复制是否有效果
在主数据库中创建一个新的数据库 ， 然后再切换到从数据库查看是否同样多出通名的数据库
配置旧数据库的主从复制
如果一开始数据库的架构不是主从复制，并且运行一段时间后已经有数据存在，那配置的方式略有不同 。
编辑主数据库的my.ini文件，加上一下内容：
binlog-do-db=landclash
重启主数据库 ， 然后在主数据库中锁定所有的表
mysqlflush
tables
with
read
lock；
显示主服务器的状态信息，并且找到File
和
Position
的值记录下来；
mysqlshow
master
status;
将主数据库data目录下需要做主从复制的数据库的同名目录拷贝到从数据库的data目录下
编辑从数据库的my.ini文件 ， 加上一下内容：
replicate-do-db=landclash
重启从数据库，因为主数据库在重新配置my.ini后，日志文件变成新的文件，所以需要再次设置登录主数据库的账号和密码等信息
mysqlstop
slave;
mysqlchange
master
to
master_host='192.168.1.2',master_user='slave',master_password='slave',
master_log_file='mysql-bin.000003',master_log_pos=120;
mysqlstart
slave;
再次输入查看从数据库状态的命令
mysqlshow
slave
status
\G;
完成上述配置后，回到主数据库，将表解锁
mysqlunlock
tables;
之后在主数据库的修改就能同步到从数据库上了 。
如何在一台windows主机上搭建mysql主从复制1、首先要在本地建立两个mysql服务（参考这里） ， 指定不同的端口 。我这里一个主（3306），一个从（3307） 。
2、然后修改主配置文件:
[mysqld]
server-id = 1
binlog-do-db=test #要同步的数据库
#binlog-ignore-db=mysql #不同步的数据库,如果指定了binlog-do-db这里应该可以不用指定的
log-bin=mysql-bin #要生成的二进制日记文件名称
修改从配置文件:
[mysqld]
server-id = 2
log-bin = mysql-bin
replicate-do-db=test
3、在主库添加一个用户 repl 并指定replication权限
create user 'repl'@'127.0.0.1' identified by 'asdf';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; -- --这里我指定数据库（test.*）时报错 ， 而指定全库（*.*）时会成功 。
4、保持主从mysql的test数据库初始状态一致 。
一般是先将所有的表加读锁 ， 然后copy磁盘上的数据库文件夹 。我这里直接停止服务 ， 然后将数据文件拷贝过去 。
5、在主数据库里面运行show master status;记下file和position字段对应的参数 。
mysql show master status;
------------------ ---------- -------------- ------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
------------------ ---------- -------------- ------------------
| mysql-bin.000001 | 107 | test | |
------------------ ---------- -------------- ------------------
1 row in set (0.00 sec)
6、在从库设置它的master：
mysql change master to master_host='127.0.0.1',master_port=3306,master_user='repl',master_password='asdf',master_log_file='mysql-bin.000001',master_log_pos=107;
Query OK, 0 rows affected (0.19 sec)
这里的master_log_file和master_log_pos对应刚才show master status记下的参数 。
7、在从库开启从数据库复制功能 。
slave start;
mysql slave start;
Query OK, 0 rows affected (0.00 sec)
在从库可以通过show slave status来查看一些参数 。
8. 此时在主库创建表或插入数据，在从库就会很快也能看到了 。
-- 主库
mysql create table tianyc_02(b int);
Query OK, 0 rows affected (0.16 sec)
mysql insert into tianyc_02 values(2013);
Query OK, 1 row affected (0.13 sec)
-- 从库
mysql show tables;
----------------
| Tables_in_test |
----------------
| tianyc_01 |
| tianyc_02 |
----------------
2 rows in set (0.00 sec)
mysql select * from tianyc_02;
------
| b |
------
| 2013 |
------
1 row in set (0.00 sec)
同理，可以搭建第二、第三个从节点 。
参考：
备注：两个服务的serve_id必须不同，否则在开启复制功能时会提示错误
mysql slave start;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
【mysql主从表怎么做 mysql主从常见问题】mysql主从表怎么做的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于mysql主从常见问题、mysql主从表怎么做的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：html5表格的拖拽控件，html表格拖动

    下一篇：如何在移动办公中登录服务器？ 移动办公怎么登录服务器