MySQL主从多种架构部署及常见错误问题解析-低调大师

MySQL主从多种架构部署及常见错误问题解析

2017-11-08 477

本文的主要内容有mysql复制原理，mysql一主多从、双主架构的示例解读，以及mysql在主从复制架构实践中的常见错误问题和解决方法。

一 mysql复制原理

1 原理解读

mysql的复制(replication)是异步复制,即从一个mysql实列或端口(Master)复制到另一个mysql实列的或端口(slave)；复制操作由3个进程完成,其中2个(SQL进程和I/O进程)在Slave上，另一个在Master上；要实现复制，必须打开Master端的二进制日志(log-bin)，log-bin记录着整个数据对的操作信息，所有slave从master端获取该更新的日志，将其传送到本地并写到本地文件中，然后在读取本地文件内容执行日志中记录的更新操作；slave上已经完整拷贝master数据后，就可以连接到master上然后等待处理更新了。如果master当机或者slave连接断开，slave会定期尝试连接到master上直到能重连并且等待更新.重试的时间间隔由--master-connect-retry选项来控制,它的默认值是60秒。每个slave都记录了它关闭时的日志位置，msater也不知道有多少个slave连接上来或者哪个slave从什么时候开始更新。

2 数据同步实现步骤

（1）mysql的同步功能由三个线程来实现，master上一个，slave上两个；

（2）slave启动时，I/O线程连接到mater上，请求master发送二进制日志中的语句；

（3）mater建立一个I/O线程，把日志内容发送到salve上；

（4）slave上的I/O线程读取master上的binlog dump发送的语句，并且记录到中继日志relay logs；

（5）slave开启SQL线程，读取中继日志，然后执行这些语句来更新数据

slave上开启两个线程很有用：把读日志和执行日志分作两个线程任务处理，执行任务慢的话，读日志的任务不会跟着慢下来，而且读日志任务常常很快就完成，执行任务的sql线程往往需要很久。

二一主多从架构部署示例及常见故障解决

1 实验环境分配

192.168.1.2 master.test.com

192.168.1.3 slave1.test.com

192.168.1.4 slave2.test.com

2 创建主从复制账号，限定账户在指定服务器登录

在master上创建用户

> grant replication salve on *.* to 'slave1'@'192.168.1.3' identified by '123456';

>grant replication salve on *.* to 'slave2'@'192.168.1.4' identified by '123456';

>flush privileges;

3 备份已有数据，并复制数据到从节点（如果都是新数据库，可以略去此步）

>flush tables with read lock;

>show matser status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB |Binlog_Ignore_DB

+------------------+----------+--------------+------------------+

| mysql-bin.000001| 106 | test |

+------------------+----------+--------------+------------------+

tar zcvf /tmp/mysql.tar.gz /var/lib/mysql/

rsync -zrvz --delete /tmp/mysql.tar.gz 192.168.1.3:/tmp/

rsync -zrvz --delete /tmp/mysql.tar.gz 192.168.1.4:/tmp/

3 解锁

>unlock tables;

Query OK, 0 rows affected (0.00 sec)

4 配置master服务器，修改mysql的主配置文件

vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

symbolic-links=0

#### Master ####

server-id = 1

log-bin = mysql-bin

log-bin-index = mysql-bin.index

relay-log = mysql-relay

relay-log-index = mysql-relay.index

expire-logs-days = 10

max-binlog-size = 100M

log-slave-updates = 1

binlog-do-db = test

replicate-do-db = test

binlog-ignore-db = mysql

replicate-ignore-db = mysql

如果需要备份多个数据库,那么应该写多行

binlog-do-db=test1

binlog-do-db=test2

replicate-do-db=test1

replicate-do-db=test2

修改完成后重启mysql

/etc/init.d/mysqld restart

5 配置slave服务器，修改两个从服务器的配置文件，并重启服务(注意修改server-id)

vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

symbolic-links=0

server-id = 2

6 在两个slave上配置开启同步

> CHANGE MASTER TO

-> MASTER_HOST='192.168.1.2',

-> MASTER_USER='salve1', # 在104 上面改成slave2

-> MASTER_PASSWORD='123456',

-> MASTER_PORT=3306,

-> MASTER_LOG_FILE='mysql-bin.000001',

-> MASTER_LOG_POS=106,

-> MASTER_CONNECT_RETRY=10;

Query OK, 0 rows affected (0.02 sec)

mysql> START SLAVE;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G;

.............

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

7 查看主数据库的状态

>show matser status; ##查看主数据库状态

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB |Binlog_Ignore_DB

+------------------+----------+--------------+------------------+

| mysql-bin.000001| 106 | test |

+------------------+----------+--------------+------------------+

>show processlist; ##查看复制进程状态

8 测试验证

验证方法：在主数据库上创建数据库和在test下创建表，到从数据查看数据是否已经同步。

注意：新建的数据库不会同步，因为数据库的更新只设置了test数据库。

9 故障排除

从服务器上show slave status\G;查看slave状态

Slave_IO_Running,为No,则说明IO_THREAD没有启动,请执行: start slave io_thread;

Slave_SQL_Running为No.则复制出错,查看Last_error字段排除错误后执行: start slave sql_thread;

三双主互为主备部署解析

1 实验环境准备

192.168.1.2 master1.test.com

192.168.1.3 master2.test.com

和一主多从的步骤是一样的,只是同样的步骤执行2次而已

2 双主配置

在masterA上新建一个账户,用户masterB同步数据

masterA操作

> GRANT REPLICATION SLAVE ON *.* TO 'master'@'192.168.1..%' IDENTIFIED BY '123456';

> FLUSH PRIVILEGES;

> FLUSH TABLES WITH READ LOCK;

mysqldump -uroot -p123456 --databases test >/tmp/testA.sql

scp /tmp/testA.sql 192.168.1.103:/tmp

> UNLOCK TABLES;

Query OK, 0 rows affected (0.00 sec)

masterB:上同样的建立复制到账户,并导入数据

mysql> GRANT REPLICATION SLAVE ON *.* TO 'master'@'192.168.1.%' IDENTIFIED BY '123456';mysql> FLUSH TABLES WITH READ LOCK;

mysqldump -uroot -p123456 --databases test >/tmp/testB.sql

scp /tmp/testB.sql 192.168.1.102:/tmp/

# 分别在master1 和 master2上分别导入对方的数据.但是存在一个问题: 因为存在主键冲突的情况,导出数据的时候,不要把主键给导出来了;如果是一方没有数据,那就直接导入数据就好了,不过也要跳过主键;

mysql -uroot -p123456 </tmp/testA.sql

mysql -uroot -p123456 </tmp/testB.sql

3 修改master1和master2 的配置文件 ,只是server-id 不同

master1:的配置文件

vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

#datadir=/mysqldata

socket=/var/lib/mysql/mysql.sock

user=mysql

symbolic-links=0

#### Master ####

server-id = 1

log-bin = mysql-bin

log-bin-index = mysql-bin.index

relay-log = mysql-relay

relay-log-index = mysql-relay.index

expire-logs-days = 10

max-binlog-size = 100M

log-slave-updates = 1

binlog-do-db = test

replicate-do-db = test

binlog-ignore-db = mysql

replicate-ignore-db = mysql

sync-binlog = 1

auto-increment-increment = 2

auto-increment-offset = 1

# master2:的配置文件

vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

symbolic-links=0

### Master ####

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

symbolic-links=0

server-id = 2

log-bin = mysql-bin

log-bin-index = mysql-bin.index

relay-log = mysql-relay

relay-log-index = mysql-relay.index

expire-logs-days = 10

max-binlog-size = 100M

log-slave-updates

skip-slave-start

slave-skip-errors = all

binlog-do-db = test

replicate-do-db = test

binlog-ignore-db = mysql

replicate-ignore-db = mysql

sync-binlog = 1

auto-increment-increment = 2

auto-increment-offset = 2

4 分别在master1和master2上获取File和Position位置

master1:

>flush tables with read lock;

>show matser status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB |Binlog_Ignore_DB

+------------------+----------+--------------+------------------+

| mysql-bin.000002| 106 | test | mysql

+------------------+----------+--------------+------------------+

>unlock tables;

master2:

>flush tables with read lock;

>show matser status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB |Binlog_Ignore_DB

+------------------+----------+--------------+------------------+

| mysql-bin.000003| 106 | test | mysql

+------------------+----------+--------------+------------------+

>unlock tables;

5 配置主从,分别在master1和 master2上配置对方的从

master1上：

> CHANGE MASTER TO

-> MASTER_HOST='192.168.1.3',

-> MASTER_USER='master',

-> MASTER_PASSWORD='123456',

-> MASTER_PORT=3306,

-> MASTER_LOG_FILE='mysql-bin.000003',

-> MASTER_LOG_POS=106,

-> MASTER_CONNECT_RETRY=10;

Query OK, 0 rows affected (0.02 sec)

mysql> START SLAVE;

Query OK, 0 rows affected (0.00 sec)

master2上：

> CHANGE MASTER TO

-> MASTER_HOST='192.168.1.2',

-> MASTER_USER='master',

-> MASTER_PASSWORD='123456',

-> MASTER_PORT=3306,

-> MASTER_LOG_FILE='mysql-bin.000002',

-> MASTER_LOG_POS=106,

-> MASTER_CONNECT_RETRY=10;

Query OK, 0 rows affected (0.02 sec)

mysql> START SLAVE;

Query OK, 0 rows affected (0.00 sec)

用>show processlist查看进程状态

6 测试，在其中一个数据库test中创建新表，插入数据，在另一个数据库中查看是否同步。

四主从复制中常见的故障及解决方法

1 从服务器上show slave status\G;查看slave状态为NO

Slave_IO_Running,为No,则说明IO_THREAD没有启动,请执行: start slave io_thread;

Slave_SQL_Running为No.则复制出错,查看Last_error字段排除错误后执行: start slave sql_thread;

2 主服务器宕机了,如何把从服务器提升会主服务器

在一主多从的环境中，必须选择数据最新的从服务器做新的主服务器，以保证数据的完整可靠。在一主两从的环境中，Server1宕机后，等到Server1和Server2把宕机前同步到的日志都执行完，比较Master_Log_File和Read_Master_Log_Pos就可以判断出谁快谁慢，因为Server2从 Server1同步的数据（1589）比Server3从Server1同步的数据（1293）新，所以应该提升Server2为新的主服务器。

3 主从复制突然停止了,该怎么处理

复制错误多半是因为日志错误引起的,所以首先要搞清楚是主日志错误还是中继日志错误，从错误信息里就能判断，如果不能判断，则使用下面的mysqlbinlog命令：

shell> mysqlbinlog <MASTER_BINLOG_FILE> > /dev/null

shell> mysqlbinlog <SLAVE_BINLOG_FILE> > /dev/null

# 如果没有错误,则不会有任何输出,反之如果有错误,则会显示出来.

如果是主日志错误,则需要在从服务器设置SET GLOBAL sql_slave_skip_counter

mysql> SET GLOBAL sql_slave_skip_counter = 1;

mysql> START SLAVE;

注：如果有多个错误,可能需要执行多次（提醒：主从服务器数据可能因此不一致）

如果是中继日志错误,只要在从服务器使用CHANGE MASTER TO即可,系统会抛弃当前的中继日志,重新下载。

mysql> CHANGE MASTER TO

MASTER_LOG_FILE=’<Relay_Master_Log_File>’,

MASTER_LOG_POS=<Exec_Master_Log_Pos>;

mysql> START SLAVE;

另外，不同的应用环境系统总会出现各种各样的错误，处理错误的最重要依据是根据日志中错误信息分析定位故障所在，建议在出现故障时候，不要忙着百度谷歌找答案，先去日志上看看，大部分的应用故障都会有相应的日志输出，通过日志分析错误是基本的”救火之道“。

本文转自super李导51CTO博客，原文链接： http://blog.51cto.com/superleedo/1897681，如需转载请自行联系原作者

微信关注我们

原文链接：https://yq.aliyun.com/articles/507942

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

由红旗inMini管窥云计算时代的个人终端

2010年3月31日，中科红旗软件技术有限公司（以下简称“红旗”）在北京香格里拉饭店举办了红旗inMini尚巧版的产品推介会。此次会议上，红旗软件隆重发布了最新移动互联网操作系统——红旗inMini尚巧版，这一面向“云端设备”的产品基于MeeGo平台设计，冀望以便携性，个性化，订制性以及友好的操控性为消费者带来完美的“冲浪”体验。CIOAge记者应邀参加了此次活动。inMini尚巧版其实“大有来头” 在产品介绍环节，红旗专门负责个人版操作系统产品线的产品经理魏思冰阐述了inMini的设计理念——以上网本为例，传统的操作系统运行起来会有一定困难，而目前以桌面端Linux为代表的非传统操作系统，往往过分考虑了用户操作的快捷性，忽略了操作系统本身可定制和灵活的应用调整能力。从现场演示视频来看，inMini尚巧版不仅非常注重用户的操作体验，而且对于常用的视频播放、网络支付、IM等工具已经实现了较为良好的支持。作为基于MeeGo平台的首批市场化产品，inMini尚巧版的问世无疑得到了英特尔的大力支持，英特尔中国开源技术中心北京研发总监杨继国在产品推介会上对MeeGo平台的特点与愿景亦做了详细讲...

2017-11-09

496

在SQL Server的日常管理中，让SQL Server高效运行，且性能良好，是DBA需要做的事。DBA需要了解日常的性能运行情况，对性能进行分析和调优，需要对线上环境部署监控。监视（Monitoring）是SQL Server数据库引擎的一大主题，了解整个数据库引擎的监视架构，如何做好全面的监控，是很必要的。笔者结合MSDN的介绍和自己的理解来谈谈监视（Monitoring）和监视事件（Monitoring Events）的体系架构。监视（Monitoring）监视（Monitoring）主题包括：在SQL Server实用工具中监视SQL Server的实例（SQL Server Utility）描述 SQL Server 实用工具中的监视功能。日志文件查看器（Log File Viewer）介绍显示有关 SQL Server 组件的记录信息的方法。监视资源使用情况（Monitoring Resource Usage）包含有关使用 Windows 系统监视器跟踪 SQL Server 中的资源使用情况的信息。监视事件（Monitoring Events）包...

2017-11-09

440

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

Eclipse

Eclipse 是一个开放源代码的、基于Java的可扩展开发平台。就其本身而言，它只是一个框架和一组服务，用于通过插件组件构建开发环境。幸运的是，Eclipse 附带了一个标准的插件集，包括Java开发工具（Java Development Kit，JDK）。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。