Question

图 1 MySQL 主从复制运行图

说明：

• master：master 开启二进制日志，主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。
• slave：slave 的 I/O thread 读取 master 的二进制日志，将数据放到复制日志 relay log；slave 的 SQL 线程从复制日志读取数据并写入到数据库。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更 新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

MySQL 支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。(这与 同步复制 可以进行对比，同步复制 是 MySQL 簇的一个特征—参见 第 17 章： MySQL 簇 ）。

请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

单向复制有利于健壮性、速度和系统管理：

• 主服务器/从服务器设置增加了健壮性。主服务器出现问题时，你可以切换到从服务器作为备份。
• 通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间。SELECT 查询可以发送到从服务器以降低主服务器的查询处 理负荷。但修改数据的语句仍然应发送到主服务器，以便主服务器和从服务器保持同步。如果非更新查询为主，该负载均衡策略很有效，但一般是更新查询。
• 使用复制的另一个好处是可以使用一个从服务器执行备份，而不会干扰主服务器。在备份过程中主服务器可以继续处理更新。参见 5.9.1 节，数据库备份 。
• 发扬不同表引擎的优点。目前 Myisam 表的查询速度比 innodb 略快，而写入并发 innodb 比 myIsam 要好。那么，我们可以使用 innodb 作为 master，处理高并发写入，使用 master 作为 slave，接受查询。或在 myisam slave 中建立全文索引，解决 innodb 无全文索引的弱点。

配置步骤

1. 确保主从 MySQL 服务版本兼容
2. 在主服务器上为服务器设置一个连接账户。该账户必须授予 REPLICATION SLAVE 权限。

配置用户复制权限：

mysql> create user repl;  //创建新用户
mysql> GRANT REPLICATION SLAVE ON *.*
  -> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

3. 执行 FLUSH TABLES WITH READ LOCK 语句清空所有表和块写入语句： mysql> FLUSH TABLES WITH READ LOCK;
4. 主从服务器的配置 my.cn(linux)/my.ini(windows) 。

确保主服务器主机上 my.cnf 文件的 [mysqld] 部分包括一个 log-bin 选项。该部分还应有一个 server-id=Master_id 选项，其中 master_id 必须为 1 到 232–1 之间的一个正整数值。

master 配置

[mysqld]
# master server settings
# 必选配置项：开启二进制日志，master-bin.index --> master-bin.000001
server-id=1
log-bin=master-bin
log-bin-index=master-bin.index

# 可选配置项
expire_logs_days=0 //日志永不过期
binlog-do-db=data //需要同步的二进制数据库名；
log-slave-updates //把更新的记录写到二进制文件中；
 slave-skip-errors //跳过错误，继续执行复制；
# 忽略以下 mysql 内部表
replicate-ignore-db = mysql
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema

slaver 配置

[mysqld]
# slaver server settings：必选配置项
server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index

# 可选配置项
replicate-ignore-db=mysql  //不需要备份的数据库；
 replicate-do-db=data  //需要备份的数据库

# 注：以下配置导致 mysql 不能正常启动，改在 mysql 终端用**change master to**执行
master-host=192.168.1.220
master-port=3306
master-user=
master-password=
master-connect-retry=60
report-host=192.168.1.220

5. 启动从服务器线程：

说明：可在主服务器用 show master status 获取 master_log_file 的文件名和 pos

change master to master_host='192.168.1.220',
master_port=3306,
master_user='repl',
master_password='12341234',
master_log_file='master-bin.000001',
master_log_pos=0;
master_heartbeat_period = 10; //心跳包

mysql> START SLAVE;

执行这些程序后，从服务器应连接主服务器，并补充自从快照以来发生的任何更新。

管理主从

主服务器

mysql> show master status；
+-------------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 25211325 |       |         |
+-------------------+----------+--------------+------------------+

从服务器

命令： reset slave; start slave, stop slave, change master to xxx;

mysql> show slave status；
| Slave_IO_State               | Master_Host        | Master_User   | Master_Port           | Connect_Retry    | Master_Log_File   |
| -------------------------------- | ---------------------- | ----------------- | ------------------------- | -------------------- | --------------------- |
| Waiting for master to send event | 192.168.1.220          | repl              | 3306                      | 60                   | master-bin.000001     |
| Read_Master_Log_Pos          | Relay_Log_File     | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running |
| 25211325                         | slave-relay-bin.000003 | 8755649           | master-bin.000001         | Yes                  | Yes                   |
$ cat master.info
$ cat relay-log.info

3.3.1 读写分离

1、mysql-proxy 实现读写分离

MySQL Proxy 是一个处于你的 client 端和 MySQL server 端之间的简单程序，它可以监测、分析或改变它们的通信。它使用灵活，没有限制，常见的用途包括：负载平衡，故障、查询分析，查询过滤和修改等等。

MySQL Proxy 就是这么一个中间层代理，简单的说，MySQL Proxy 就是一个连接池，负责将前台应用的连接请求转发给后台的数据库，并且通过使用 lua 脚本，可以实现复杂的连接控制和过滤，从而实现读写分离和负 载平衡。对于应用来说，MySQL Proxy 是完全透明的，应用则只需要连接到 MySQL Proxy 的监听端口即可。

当然，这样 proxy 机器可能成为单点失效，但完全可以使用多个 proxy 机器做为冗余，在应用服务器的连接池配置中配置到多个 proxy 的连接参数即可。

MySQL Proxy 更强大的一项功能是实现“读写分离”，基本原理是让主数据库处理事务性查询，让从库处理 SELECT 查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从库。

配置 mysql-proxy

创建 mysql-proxy 配置文件，配置文件中的所有选择都不能加引号

vim /usr/local/mysql-proxy/mysql-proxy.conf

[mysql-proxy]
 daemon=true      #以后台守护进程方式启动
 keepalive=true      #当进程故障后自动重启
 log-level=debug     #设置日志级别为 debug，可以在调试完成后改成 info
 log-file=/var/log/mysql-proxy.log      #设置日志文件路径
 basedir=/usr/local/mysql-proxy      #设置 mysql-proxy 的家目录
 proxy-address=192.168.1.220:4040   #指定 mysql-proxy 的监听地址
 proxy-backend-addresses=192.168.1.220:3306          #设置后台主服务器
 proxy-read-only-backend-addresses=192.168.1.222:3306     #设置后台从服务器
 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua  #设置读写分离脚本路径
 admin-address=192.168.1.220:4041  #设置 mysql-proxy 管理地址，需要 admin 插件
 admin-username=admin         #设置登录管理地址用户
 admin-password=admin         #设置管理用户密码
 admin-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua  #设置管理后台 lua 脚本路径，改脚本默认没有要自动定义

配置完 mysql-proxy.conf 后需要确保该文件的权限是 600，并确保包含个 lua 脚本
通过配置文件启动 mysql-proxy

$ /usr/local/mysql-proxy/bin/mysql-proxy --plugins=proxy --plugins=admin --defaults-file=mysql-proxy.conf

• --plugins=proxy # 指定 proxy 插件，该配置写入配置文件无法启动
• --plugins=admin # 指定 admin 插件
• --defaults-file=mysql-proxy.conf # 指定配置文件

启动测试
登录管理地址查看当前状态

$ mysql -uadmin -padmin -h192.168.1.220 -P4041

2、mysqlnd_ms 实现 mysql 读写分离

mysqlnd_ms 是 mysqlnd 的一个插件，该插件实现了连接保存和切换、负载均衡、读写分离的功能。要想使用 mysqlnd_ms 的读写分离功能必须在安装 php 时使用–with-mysqlnd。

mysqlnd 实现的功能是可以不需要在 php 服务器上安装 mysql，在 php5.3 之前编译安装 php 需要通过–with-mysql=/path/to/mysql 指定 mysql 的安装路径。

3.3.2 双主互备

MySQL 双主互备（Master-Master Replication）是一种配置，其中两个 MySQL 实例（主服务器）互为主机，彼此之间进行数据复制。这种设置允许每个主服务器都可以同时进行读写操作，提高了可用性和负载均衡。

主要步骤

1. 环境准备 ：
   • 确保两个 MySQL 实例可以相互访问。
   • 确保 MySQL 版本相同，且支持双主复制。
2. 配置文件修改 ：
   在每个 MySQL 实例的配置文件（ my.cnf ）中添加以下内容：

   主服务器 A ：

   [mysqld]
   server-id=1
   log_bin=mysql-bin
   binlog_do_db=your_database  # 需要复制的数据库
   auto_increment_increment=2
   auto_increment_offset=1
   

   主服务器 B ：

   [mysqld]
   server-id=2
   log_bin=mysql-bin
   binlog_do_db=your_database  # 需要复制的数据库
   auto_increment_increment=2
   auto_increment_offset=2
   

3. 重启 MySQL 服务 ：
   在修改配置后，重启两个 MySQL 实例使配置生效。
4. 创建复制用户 ：
   在每个主服务器上创建用于复制的用户：

   在 A 上 ：

   CREATE USER 'replicator'@'%' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
   

   在 B 上 ：

   CREATE USER 'replicator'@'%' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
   

5. 配置复制 ：
   在每个主服务器上配置对方为从服务器。

   在 A 上 ：

   CHANGE MASTER TO
       MASTER_HOST='B 的 IP 地址',
       MASTER_USER='replicator',
       MASTER_PASSWORD='password',
       MASTER_LOG_FILE='mysql-bin.000001',  -- 替换为 B 的 binlog 文件
       MASTER_LOG_POS=0;  -- 替换为 B 的 binlog 位置
   START SLAVE;
   

   在 B 上 ：

   CHANGE MASTER TO
       MASTER_HOST='A 的 IP 地址',
       MASTER_USER='replicator',
       MASTER_PASSWORD='password',
       MASTER_LOG_FILE='mysql-bin.000001',  -- 替换为 A 的 binlog 文件
       MASTER_LOG_POS=0;  -- 替换为 A 的 binlog 位置
   START SLAVE;
   

6. 检查状态 ：
   使用以下命令检查复制状态：

   在 A 上 ：

   SHOW SLAVE STATUS\G;
   

   在 B 上 ：

   SHOW SLAVE STATUS\G;
   

   确保 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes 。

注意事项

• 冲突处理 ：双主复制可能会导致数据冲突，因此在应用设计上需要谨慎处理。
• 数据一致性 ：确保应用程序在写入数据时能够处理潜在的冲突。
• 监控和备份 ：定期监控复制状态，确保系统运行正常，并进行定期备份以防数据丢失。

通过这些步骤，你可以成功设置 MySQL 双主互备配置。

3.3.3 常见问题

1.主机宕机

选择复制较全的一台从机升级为主机。