mysql的双机热备

一般主从复制是主服务器数据库可读可写，从服务器数据库只读，而双机热备是主从服务器的数据库双向复制，从服务器数据库可读也可写。

说到主从复制不得不说–读写分离，此介绍请看这一篇 一文浅谈“读写分离”技术

双机热备的概念简单说一下，就是要保持两个数据库的状态自动同步。对任何一个数据库的操作都自动应用到另外一个数据库，始终保持两个数据库数据一致。 这样做的好处多。

1. 可以做灾备，其中一个坏了可以切换到另一个。
2. 可以做负载均衡，可以将请求分摊到其中任何一台上，提高网站吞吐量。 对于异地热备，尤其适合灾备。

主从复制原理

当主服务器上的数据发生变化的时候，主服务器会把这些变化记录到一个日志文件中，然后通知从服务器，从服务器会拉取这个日志文件中的变化信息，然后做出相应的数据变化。

主从复制配置和启动

可以参考这一篇高性能Mysql主从架构的复制原理及配置详解。

实现步骤如下：

1. 确定主从：确定主从复制服务器。
2. 创建主服务器复制用户：在主服务器上创建用于复制的用户，并为其授予适当的权限，用来给从服务器复制。
3. 配置主服务器：在每台服务器上配置主服务器的 my.cnf 文件。
4. 配置从服务器：在每台服务器上配置从服务器的 my.cnf 文件。
5. 在从服务器启动复制：启动复制进程，并确保数据正常传输。

简略实现过程：

配置主从服务器的my.cnf

其中主服务器的my.cnf修改

log-bin=mysql-bin
server-id = 1
1
2

从服务器的my.cnf修改，要指定从服务器同步的博客数据库

log-bin=mysql-bin
binlog_format=mixed
server-id = 2

relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 0 # 需要读写分离则这里填1，只读
replicate-do-db = myblog_db
1
2
3
4
5
6
7
8

主从服务器都重启下mysql。

创建主服务器复制用户

在主服务器上操作

mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO backup1@'从服务器ip' IDENTIFIED BY 'ErTu2QSGzs1';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000024 | 734078947 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

在从服务器启动复制

在从服务器上操作

mysql> CHANGE MASTER TO MASTER_HOST='主服务器ip',  MASTER_USER='backup1',  MASTER_PASSWORD='ErTu2QSGzs1',  MASTER_LOG_FILE='mysql-bin.000024',  MASTER_LOG_POS=0;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G;

1
2
3
4
5
6
7

主从复制常见的问题

1、server-id重复

                Last_IO_Errno: 1593
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
1
2

2、数据库的键值重复

                   Last_Errno: 1062
                   Last_Error: Error 'Duplicate entry '166127' for key 'PRIMARY'' on query. Default database: 'xxx'. Query: 'INSERT INTO xxx'
1
2

解决方法：
1、重新导入主从数据库，保持一致，并且设置LOG_POS为最新的。

1.1 导入主从数据库
命令行（不是在 MySQL）执行导出博客数据库。
mysqldump -u your_username -p your_database_name > structure.sql

在这个命令中：
-u your_username 
是你的数据库用户名

-p 
表示需要输入密码

--no-data 
选项表示只导出结构，不包含数据

your_database_name 
是你的数据库名称
> structure.sql 
表示将导出的内容保存到 structure.sql 文件中

1.2 设置LOG_POS为最新的
首先在主库上查看

mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000022 | 372102960 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)



在从库上操作

START SLAVE;STOP SLAVE;

CHANGE MASTER TO MASTER_HOST='主服务器ip',  MASTER_USER='backup1',  MASTER_PASSWORD='ErTu2QSGzs1',  MASTER_LOG_FILE='mysql-bin.000024',  MASTER_LOG_POS=372102960;

#从372102960行后开始复制


mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

3、主服务器数据库连接失败

                    Last_IO_Errno: 2003
                    Last_IO_Error: error connecting to master ...
1
2

排查：

(1) 在配置文件中排查了与bindaddress和skip-networking与相关参数后，均没有配置；

(2) 于是排查服务器的防火墙，发现防火墙处于开启状态

(3) 关闭防火墙后，重新执行start slave;发现主从配置成功。

配置没错大多是防火墙问题，没有放行mysql的3306端口

双机热备

实现步骤如下：

1. 在两台服务器上配置主从复制：每台服务器既是主服务器又是从服务器。
2. 创建复制用户：在两台服务器上创建用于复制的用户，并为其授予适当的权限。
3. 配置主服务器：在每台服务器上配置主服务器的 my.cnf 文件。
4. 配置从服务器：在每台服务器上配置从服务器的 my.cnf 文件。
5. 启动复制：启动复制进程，并确保数据正常传输。

在主从复制的基础上，修改主服务器 my.cnf 文件（主服务器也当成从服务器，即让主服务器复制从服务器）。

relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 0 
replicate-do-db = myblog_db
1
2
3
4

配置主服务器复制从服务器

步骤跟主从复制一样

在从服务器上操作

mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO backup2@'主服务器ip' IDENTIFIED BY 'ErTu2QSGzs2';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000022 |  2747685 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

在主服务器上操作

mysql> CHANGE MASTER TO MASTER_HOST='从服务器ip',  MASTER_USER='backup2',  MASTER_PASSWORD='ErTu2QSGzs2',  MASTER_LOG_FILE='mysql-bin.000022',  MASTER_LOG_POS=0;
Query OK, 0 rows affected, 2 warnings (0.03 sec)


mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G;
1
2
3
4
5
6
7

以上配置完毕。

参考：

一文浅谈“读写分离”技术

Mysql-Master-Slave主从配置（1）

高性能Mysql主从架构的复制原理及配置详解

mysql 双主从复制原理（双机热备）