mysql主从复制与数据库备份、mysql主从复制主从切换

本文内容列表：

• 1.如何备份Mysql数据库
• 2、如何配置MySQL数据库主从复制
• 3、Mysql主从复制方式及可能出现的问题
• 4、mysql如何实现主从同步数据库备份？
• 5、MySQL主从，5分钟就能掌握
• 6.安全第一！ MySQL配置主从复制、主主复制

如何备份Mysql数据库

备份 Mysql 数据库的常用方法是使用 mysqldump 实用程序。其命令格式如下

　#mysqldump [options]database[tables]

其参数含义为：

options：代表mysqldump选项，可以通过mysqldump –help找到。

database：代表要备份的数据库。

tables：代表要备份的表。如果不指定表，则备份整个数据库。

使用mysqldump进行备份非常简单。要备份“phpbb_db_backup”数据库​​，请使用命令：

#mysqldump –u -p phpbb_db_backup /usr/backups/mysql/ phpbb_db_backup.2005.5.6

也可以使用 gzip 命令来压缩备份文件：

#mysqldump phpbb_db_backup | gzip /usr/backups/mysql/phpbb_db_backup.2005.5.6。 gz

使用以下命令恢复数据：

#mysql –u -p phpbb_db_backup /usr/backups/mysql/phpbb_db_backup.2005

如何配置MySQL数据库主从复制

MySQL 支持单向异步复制。在复制过程中，一台服务器充当主服务器，一台或多台其他服务器充当从服务器。主服务器将更新写入二进制日志文件并维护日志文件索引以跟踪日志循环。当从站连接到主站时，它会通知主站从站在日志中读取的最后一次成功更新的位置。从属服务器接收此后发生的任何更新，然后阻塞并等待主服务器通知下一次更新。

为什么要使用主从复制？

1。主/从设置提高了鲁棒性。当主服务器出现问题时，可以切换到辅助服务器作为备份。

2。在主服务器和从属服务器之间分割处理客户查询的负载可以提供更好的客户响应时间。但是，不要同时在master和slave上更新，因为这可能会导致冲突。

3。使用复制的另一个优点是可以使用从服务器执行备份，而不会干扰主服务器。主服务器可以在备份过程中继续处理更新。

MySQL 使用 3 个线程来执行复制功能（1 个在主服务器上，2 个在从服务器上）。当发出 START SLAVE 选项时，从服务器创建一个 I/O 线程来连接到主服务器，并让master服务器发送二进制日志到master服务器创建一个线程将二进制日志的内容发送到slave I/O Slave线程读取master Binlog Dump线程发送的内容并将数据复制到slave数据目录中本地文件，即中继日志中的第三个线程是SQL线程从服务器使用该线程读取中继日志并执行日志中包含的更新SHOW PROCESSLIST语句可以查询主服务器和从服务器上发生了什么复制信息

默认中继日志使用格式为 host_name-relay-bin.nnnnnn 的文件名，其中 host_name 是从属主机名，nnnnnn 是序列号。使用从 000001 开始的连续序列号创建连续的中继日志文件。按照来自服务器的中继日志索引文件来确定当前正在使用哪个中继日志。中继日志索引文件的默认名称为 hostname-relay-bin.index。默认情况下，这些文件是在从服务器的数据目录中创建的。中继日志与二进制日志格式相同，可以使用mysqlbinlog读取。 SQL线程执行完中继日志中的所有事件后，中继日志将被自动删除。

从服务器在数据目录中额外创建两个状态文件——master.info和relay-log.info。状态文件保存在硬盘上，当从服务器关闭时状态文件不会丢失。下次从服务器启动时，将读取这些文件以确定它从主服务器读取了多少二进制日志以及它处理自己的中继日志的情况。

设置主从复制：

1。确保主从服务器上安装的MySQL版本相同，最好是最新稳定版本的MySQL。

2。在主服务器上设置复制连接帐户。帐户必须具有 REPLICATION SLAVE 权限。如果该帐户仅用于复制（推荐），则无需分配额外的权限。

mysql 在 *.*

- 由“slavepass”标识的“replication”@“%.yourdomain.com”上授予重复复制；

3。执行FLUSH TABLES WITH READ LOCK语句清除所有表和块写入语句：

mysql FLUSH TABLES WITH READ LOCK;

防止mysql客户端关闭。打开另一个终端来拍摄主服务器数据目录的快照。

shell cd /usr/local/mysql/

shell tar -cvf /tmp/mysql-snapshot.tar ./data

如果从服务器的用户帐号与主服务器不同，我可能会不想复制mysql数据库。在这种情况下，必须将数据库从存档中排除。此外，您不需要在存档中包含任何日志文件或 master.info 或 relay-log.info 文件。

当FLUSH TABLES WITH READ LOCK设置的读锁生效时（即mysql客户端没有关闭），读取master上当前的二进制日志名和偏移值：

mysql SHOW MAIN STATUS;

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

|文件 |位置 | Binlog_To_DB | Binlog_To_DB | Binlog_Ignore_DB |

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

| mysql-bin.003 | 73 | 73测试|手册,mysql |

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

File栏显示日志名称，位置显示偏移量。在本例中，二进制日志值为 mysql-bin.003，偏移量为 73。记录该值。稍后在设置子服务器时将需要这些值。它们代表从属服务器应从主服务器开始新更新的复制坐标。

如果主服务器运行时未启用--logs-bin，则SHOW MASTER STATUS显示的日志名称和位置值将为空。在这种情况下，指定日志文件和未来从站位置时使用的值是空字符串（''）和4。

捕获并写入日志名称和偏移量后，返回到先前的重新启用写入中间的活动：

mysql UNLOCK TABLES；

4. 确保主服务器主机上的 my.cnf 文件的 [mysqld] 部分包含 log-bin 选项。此部分还必须具有 server-id=Master_id 选项，其中 master_id 必须是 1 到 232-1 之间的正整数。例如：

[mysqld]

log-bin

server-id=1

如果这些选项不可用，您必须添加它们并重新启动服务器。

5。停止子服务器上的 mysqld 服务，并将以下行添加到 my.cnf 文件中：

[mysqld]

server-id=2

Slave_id 值与 Master_id 值相同，并且必须是 1 到 232-1 之间的正整数。另外，从服务器的ID必须与主服务器的ID不同。

6。备份目录中的数据。确保这些文件和目录的权限正确。服务器运行MySQL的用户必须能够读写文件，就像在主服务器上一样。

Shell chown -R mysql:mysql /usr/local/mysql/data

7.启动子服务器。在从服务器上执行以下语句，并将选项值替换为您系统的实际值：

mysql CHANGE MASTER TO

- MASTER_HOST='master_host_name',

- MASTER_USER= 'replication_user_name',

- MASTER_PASSWORD='replication_password',

- MASTER_LOG_FILE='recorded_log_file_name',

- MASTER_LOG_POS=recorded_log_position;

8.启动slave线程：

mysql START SLAVE;

执行这些程序后，从服务器必须连接到主服务器并完成自快照以来发生的任何更新。

9。如果发生复制错误，错误消息也会出现在子服务器的错误日志 (HOSTNAME.err) 中。

10。从服务器复制时，将在其数据目录中找到 master.info 和 HOSTNAME-relay-log.info 文件。从站使用这两个文件来跟踪主站二进制日志的处理量。除非您确切知道自己在做什么并完全理解它们的含义，否则请勿删除或编辑这些文件。但是，最好使用 CHANGE MASTER TO 语句。

Mysql主从复制方法及可能出现的问题

大致流程：主库将变化写入binlog日志，从库连接主库后，从库有IO线程，将主库的binlog复制到本地，写入到中继日志 中继日志。然后子库中有一个SQL线程，会从relay log中读取binlog，然后执行binlog日志的内容，也就是说会在本地再次执行SQL，这样就可以保证它们之间的数据和子库之间的数据是一致的。主库是一样的。

如果主库突然停止工作，并且数据尚未与从库同步，则可能有部分数据在从库中不可用。在此期间，从库成为主库，部分数据可能会丢失。

开启部分同步复制，解决主库数据丢失问题；

这个所谓的半同步复制，半同步复制，是指主库写完binlog日志后，就会强制执行。数据将立即与子库同步。从库将日志写入本地中继日志后，会向主库返回确认。主库只有在收到至少一个从库的确认后才会认为写操作完成。如果过程失败，我们的客户可以重试；

主从延迟对读写分离影响较大

这里有很重要的一点，即主库和从库数据的同步。该过程是串行化的，这意味着主库上的并行操作将在子库上顺序执行。所以这是非常重要的一点。由于从库从主库复制日志并串行执行SQL的特性，在高并发场景下，如果主库写入量很大，而从库的数据是逐条读取的，那么就会导致从库同步慢于主库，出现卡顿。因此，经常会出现刚刚写入主库的数据可能读不到，需要几十毫秒甚至上百毫秒才能读完的情况。 （主库并发率越高，从库留下的同步数据越多，延迟越大）

我们可以通过显示状态来显示Seconds_Behind_Master参数。可以看到子库在复制主库的数据方面落后了。几毫秒，但这并不完全准确。可以看Seconds_Behind_Master的

解决主从延迟可以从以下几个方面考虑解决方案

mysql是如何实现主从同步的数据库备份的？

1。主服务器：

#主开始

log-bin="d:/log/mysql/mysql_log_bin"

server-id=1

　#主结束

2.从服务器：

#从启动

log-bin="D:/log/mysql2/log-bin.log"

relay_log="D:/log/mysql2/relay-log-bin"

#从机 id 与主机 id 不同

server-id=2

#主机IP，用于slave连接主机

#master-host=localhost

#主机端口

#master-port=3300

#刚刚为slave创建的账户，用于复制主机数据

#master-user=slave

#新建从机密码，用于复制主机数据

#master-password=654321

#重试间隔为10秒

# master-connect- retry= 10 ?只读，0表示读写，1表示只读

read-only=1

#只复制指定表

#replicate-do-table=表名

#只复制部分表rows (可用匹配字符)

#replicate-wild-do-table=tablename%

#仅复制指定数据库

#replicate-do-db=dbname

#不复制指定表

#replicate-ignore-table=tablename

#不复制指定的表

#replicate-wild-ignore-table=tablename%

#不复制指定的库

#replicate-ignore -db =dbname

#从端

3.使用CHANGE MASTER语句为从服务器指定主服务器

注意：1.在主服务器上创建一个可以进行复制的用户

2.这个用户在从服务器上的名称可以用来登录远程连接到主服务器。

3.启用MySQL的log-bin日志功能

MySQL主从，让你5分钟掌握它

MySQL Master 和 Slave 一直是面试的常客。虽然里面的知识点很基础，但是很少有学生能够全部答对。

比如楼哥之前面试小米的时候，被问到主从复制原理和主从延时解决方案。由于他的回答非常好，给面试官留下了很好的印象。之前面试中遇到过哪些MySQL主从问题？

所谓主从MySQL 建立两个相同的数据库，一主一从。主库提供外部读写操作，从库提供外部读操作。接下来是主人和奴隶。方法：

对于独立数据库部署，在4核8G计算机上运行MySQL 5.7时，可以支持约500 TPS和10,000 QPS。当遇到一些活动和突发的查询流量时，主从分离是必要的。 。

大多数系统的访问模型是多读少写。读写需求的差异可以达到多个大小，因此我们可以使用一主多从。主库只负责按照一些基本逻辑进行写入和查询。 ，多个子库只负责查询，提高查询性能，减轻主库的压力。

MySQL主从也能实现高服务可用性。当主库宕机时，可以将从库拆分为主库，保证服务的高可用性。那么主库也可以备份恢复后的数据。

整个场景总结如下：

MySQL主从复制依赖于binlog，binlog记录了MySQL的所有更改，并以二进制格式存储在磁盘上的二进制日志文件中。

主从复制是指将数据从主库的binlog传输到从库。一般来说，这个过程是异步的，也就是说对master数据库的操作不会等待binlog同步完成。

具体流程如下：

主从数据库数据同步突然中断怎么办？由于主库和子库之间维护着一个长链接，因此主库内部有一个线程专门为子库的这个长链接服务。

对于以下情况，如果主库执行如下SQL，其中a和create_time均为索引：

我们知道，如果数据选择索引a和create_time，则数据输出边界一般为1不同的。 。

所以会出现这种情况：binlog=语句的格式中，主库在执行这条SQL时使用索引a，子库在执行这条SQL时使用索引create_time。最后，主库数据不一致。

我们该如何解决这个问题？

您可以将binlog格式更改为line。 binlog日志不记录行形式的原始SQL文本，而是记录两个事件：Table_map和Delete_rows。

Table_map 事件描述要管理的表。 Delete_rows 事件用于定义删除行为并记录指定数量的已删除行。 Binlog以行格式记录要删除的主键ID信息，这样就不会出现主从不一致的问题。

但是，如果SQL删除10万行数据，使用行记录会占用大量空间。 10万条数据全部都在binlog中，写binlog也消耗大量IO。但binlog语句的格式会导致数据不一致。

设计MySQL的大叔找到了一个折中的解决方案。混合binlog格式实际上是行格式和句子格式的混合。当MySQL检测到数据可能不一致时，使用字符串格式，否则使用语句格式。

有时候，当我们遇到无法从数据库中检索信息的奇怪问题时，我们会去摆弄代码中是否有一些逻辑会删除以前写入的内容，但过了一段时间你就会发现它再次尝试 查询期间可以再次读取数据。这基本上是由于主从延迟造成的。

主从延迟其实就是“打从库”完成时间和“写主库binlog”完成时间的差值，会导致从库查询到的数据与从库不一致来自主库的数据。

当我们谈论MySQL数据库中的主子同步延迟原理时，我们必须从MySQL的主子复制原理开始：

我们来总结一下主子延迟的主要原因：主要是父级-子延迟发生在“中继日志回放”步骤中。 ，当主库TPS并发较高，生成的DDL数量超过从库单个SQL线程可以处理的时候，就会出现延迟。当然，大型子库查询语句也可能发生锁等待。

我们通常使用从库滞后时间作为监控和报警的关键数据库指标。通常的时间是毫秒量级。当滞后时间达到二级时，需要报警。

除了减少主从延迟时间之外，还有其他方法可以解决这个问题。基本原则是尽量不要查询子数据库。

特殊解决方案如下：

在实际应用场景中，一些非常必要的场景比如库存、支付订单等，需要直接查询子库。对于其他非核心场景，不需要查询主库。

有两台机器A、B，A为主数据库负责读写，B为从数据库负责读取数据。

如果 A 库出现故障，B 库成为主库，负责读写。错误修复后，A 成为从库，主库 B 同步数据到从库 A。

一个主库，多个从库。 A为主数据库，负责读写。 B、C、D是子数据库，负责读取数据。

如果A库出现故障，B库成为主库负责读写，C、D负责读取。当bug修复后，A也成为子库，主库B将数据同步到子库A。

安全第一！ MySQL配置主从复制、主主复制

为了保证数据安全稳定，我们通常采用主从复制、主主数据库复制。主从复制是从从设备实时复制主机数据。当主机数据改变时，从机数据也随之改变。当从机数据改变时，主机数据保持不变。主主复制类似：在多台主机之间，只要一台主机上的数据发生变化，其他主机上的数据也会相应发生变化。

添加以下内容

如果你使用我之前使用的方法启动MySQL，那么你只需要到你连接的主机的配置文件夹中创建my.cnf并编写上面的类即可。如果您能容纳的话那就太好了。

比如：我的启动命令如下（不应该被换行，所以我把它进行了分支，方便查看）

那么我只需要在 /docker/mysql_master/conf 目录下创建 my.cnf 文件。美好的。

该命令必须在容器中执行。

Docker重启mysql会关闭容器，我们需要重启它。

确保主服务器上的skip_networking 选项处于关闭状态（默认设置）。如果启用，从站无法与主站通信并且复制失败。 ？如下：

从服务器线程启动

为了测试，可以在主服务器中创建一个数据库，发现在从服务器中也可用，就成功了。

如果你还想要一个从服务器，只需按照上面的配置再配置一个从服务器即可。新创建的从服务器会自动保存主服务器之前的数据。 （测试结果）如果你已经完成了上面的主从复制，那么这个主主复制将会非常简单。我们也将上面的从服务器改为主服务器

1）将上面从服务器的my.cnf文件更改为与主服务器相同（注意服务器ID不能相同），然后重启服务器2）在从服务器中，用同样的命令在服务器中创建复制用户（这里将用户名改为repl2） 3）在之前的主服务器中运行以下代码

上面主要教你如何搭建MySQL集群，但这里还有很多其他问题。这也是我在学习过程中思考的一个问题。有的朋友可能上来看到一篇长文就受不了了。他们只是想实现这样一个连续的聚类功能，所以我写了下面的问题。

1）MySQL Replication 和 pxc MySQL 的集群解决方案包括复制和 pxc。以上是在复制的基础上完成的。

复制：异步复制，速度快，但无法保证数据一致性。 pxc：同步复制，速度慢，多个集群之间发送事务的数据一致性强。

2）MySQL复制数据同步原理。我们在配置期间启用了其二进制日志，每次管理数据库时都会更新到该日志。主从通过同步这条日志来保证数据的一致性。

3）是否无法同步所有数据？您可以配置应同步哪些数据库甚至哪些表。

4）如何关闭和启动同步

5）根据我的理解，画出了主从、主从、主主和复制图。

以往推荐：

使用Docker仅需1分钟安装MySQL

Linux下MySQL 5.7的离线和在线安装（图文）

Linux下安装MySQL 8.0（流行！）