Basis-MySQL Master-Slave

要求

  • 主服务器版本 <= 从服务器版本
  • 主从服务器初始数据一致（触发器、存储过程等无要求）
  • 主从服务器版本 > 3.2
  • 检查主从服务器是否大小写敏感，确保设置相同（Linux 默认区分大小写，Windows 默认不区分）

配置 主-从复制

主服务器

  • 创建同步账户
CREATE USER 'replicate'@'%' IDENTIFIED BY '888888';
GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'%' IDENTIFIED BY '888888';
FLUSH PRIVILEGES;
  • 在从服务器上使用该账户连接从服务器，校验是否可进行主从复制
mysql -h192.168.7.36 -ureplicate -p
  • 修改 mysql 配置（/etc/my.cnf 或 %MYSQL_HOME%/my.ini)
[mysqld]
server-id = 1 # 唯一标识主从服务器
log-bin=mysql-bin   # 打开 binlog，才可以通过 I/O 写到 slave 的 relay-log，也是进行 replication 的前提
binlog-do-db = test  #数据库名称，表示需要记录二进制日志，多个用逗号分隔，或使用多个 binlog-do-db
binlog-ignore-db = mysql # 不需要记录二进制日志的数据库，多个用逗号分隔，或使用多个 binlog-ignore-db

  • 重启 mysql 服务

  • 查看服务状态

    image

返回的参数File, Position，在后续配置从服务器时都会使用到。

在主从服务器未完成同步前，最好对主服务器进行锁表操作防止新的数据插入、更新，同步完成后在进行解锁

锁表 flush tables with read lock;
解锁 unlock tables;

从服务器

  • 修改 mysql 配置 （/etc/my.cnf 或 %MYSQL_HOME%/my.ini)
[mysqld]
server-id = 2
log-bin=mysql-bin
replicate-do-db = test  #表示需要同步的数据库，多个用逗号分隔或配置多个
replicate-ignore-db = mysql,information_schema,performance_schema #表示不需要同步的数据库，多个用逗号分隔或配置多个

  • 重启 mysql 服务

  • 指定同步位置

    image

  • 查看服务状态

    image

注意检查标出的两项是否正常

测试

现在主从服务器已经同步了，可以在主服务器上对配置的数据库（此处为 test ）进行操作，查看从服务器上对应数据库是否同步改变。

  • 在主服务器上创建表并插入一条记录
image
  • 查询从服务器
image

以上主服务器上的数据已同步到从服务器，主-从双机热备配置成功。

配置 主-主双机热备（互为主从）

  • 创建账户
    同时在主从服务器创建连接账户，该账户必须授予 REPLICATION SLAVE 权限。两台服务器互为主从。
CREATE USER 'replicate'@'%' IDENTIFIED BY '888888';
GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'%' IDENTIFIED BY '888888';
FLUSH PRIVILEGES;

  • 修改 mysql 配置 （/etc/my.cnf 或 %MYSQL_HOME%/my.ini)
    A 服务器（上一步中的主服务器）

    [mysqld]
server-id = 1
log-bin=mysql-bin
binlog-do-db = test
binlog-ignore-db = mysql
#主－主形式需要多添加的部分
log-slave-updates #配置从库上的更新操作是否写入二进制文件，如果这台从库，还要做其他从库的主库，那么就需要打这个参数，以便从库的从库能够进行日志同步
sync_binlog = 1
auto_increment_offset = 1
auto_increment_increment = 2
replicate-do-db = test
replicate-ignore-db = mysql,information_schema

    B 服务器（上一步中的从服务器）

    [mysqld]
server-id = 2
log-bin=mysql-bin
master-slave need
replicate-do-db = test
replicate-ignore-db = mysql,information_schema,performance_schema
#主－主形式需要多添加的部分
binlog-do-db = test
binlog-ignore-db = mysql
log-slave-updates
sync_binlog = 1
auto_increment_offset = 2
auto_increment_increment = 2

  • 分别重启 mysql 服务

  • 分别检查主服务状态（master status)
    A 服务器

    image

    B 服务器

    image

  • 分别指定同步位置
    A 服务器

    image

    B 服务器

    image

注意B服务器之前的配置与此时的配置有差异；另外，需要停掉之前的配置，执行 stop slave

  • 分别启动服务
start slave
  • 分别查看服务状态，与之前配置主-从服务一样，检查是否配置成功
image

  • 测试主-主配置是否成功
    分别在A/B服务器操作test数据库，查看是否更新同步到另外一个服务器上。
    A 服务器创建新表并插入记录

    image

    检查 B 服务器

    image

    B 服务器创建新表并插入记录

    image

    检查 A 服务器

    image

以上数据都成功同步到另一台服务器上，主-主双机热备配置成功。

配置参数说明

  • server-id
    ID值唯一的标识主从服务器，必须各不相同。Master_id必须为1到232－1之间的整数值，slave_id值必须为2到232－1之间的整数值。

  • log-bin
    表示打开binlog，打开该选项才可以通过I/O写到Slave的relay-log，也是进行replication的前提。

  • binlog-do-db
    表示需要记录二进制日志的数据库。多个数据库用逗号分隔，或使用多个binlog-do-dg选项。

  • binglog-ingore-db
    表示不需要记录二进制日志的数据库，多个数据库用逗号分隔，或使用多binglog-ignore-db选项。

  • replicate-do-db
    表示需要同步的数据库，多个数据库用逗号分隔，或使用多个replicate-do-db选项。

  • replicate-ignore-db
    表示不需要同步的数据库，多个数据库用逗号分隔，或使用多个replicate-ignore-db选项。

  • master-connect-retry
    表示从服务器与主服务器的连接没有成功，则等待n秒（s）后再进行管理方式（默认设置是60s）。如果从服务器存在mater.info文件，它将忽略些选项。

  • log-slave-updates
    配置从库上的更新操作是否写入二进制文件，如果这台从库，还要做其他从库的主库，那么就需要打这个参数，以便从库的从库能够进行日志同步。

  • slave-skip-errors
    在复制过程，由于各种原因导致binglo中的sql出错，默认情况下，从库会停止复制，要用户介入。可以设置 slave-skip-errors 来定义错误号，如果复制过程中遇到的错误是定义的错误号，便可以路过。如果从库是用来做备份，设置这个参数会存在数据不一致，不要使用。如果是分担主库的查询压力，可以考虑。

    sync_binlog=1 Or N
    sync_binlog 的默认值是0，这种模式下，MySQL不会同步到磁盘中去。这样的话，Mysql 依赖操作系统来刷新二进制日志 binary log，就像操作系统刷新其他文件的机制一样。因此如果操作系统或机器（不仅仅是Mysql服务器）崩溃，有可能binlog中最后的语句丢失了。要想防止这种情况，可以使用sync_binlog全局变量，使binlog在每Ｎ次binlog写入后与硬盘同步。当sync_binlog变量设置为１是最安全的，因为在crash崩溃的情况下，你的二进制日志binary log只有可能丢失最多一个语句或者一个事务。但是，这也是最慢的一种方式（除非磁盘有使用带蓄电池后备电源的缓存cache,使得同步到磁盘的操作非常快）。

    即使 sync_binlog 设置为１，出现崩溃时，也有可能表内容和 binlog内容之间存在不一致性。如果使用 InnoDB 表，Mysql服务器处理COMMIT语句，它将整个事务写入binlog并将事务提交到InnoDB中。如果在两次操作之间出现崩溃，重启时，事务被InnoDB回滚，但仍然存在binlog中。可以用-innodb-safe-binlog选项来增加InnoDB表内容和binlog之间的一致性。（注释：在Mysql 5.1版本中不需要-innodb-safe-binlog；由于引入了XA事务支持，该选项作废了），该选项可以提供更大程度的安全，使每个事务的binlog(sync_binlog=1)和（默认情况为真）InnoDB日志与硬盘同步，该选项的效果是崩溃后重启时，在滚回事务后，Mysql服务器从binlog剪切回滚的InnoDB事务。这样可以确保binlog反馈InnoDB表的确切数据等，并使从服务器保持与主服务器保持同步（不接收回滚的语句）。

  • auto_increment_offset 和 auto_increment_increment
    auto_increment_incremen t和 auto_increment_offset 用于主－主服务器（master-to-master）复制，并可以用来控制 AUTO_INCREMENT 列的操作。两个变量均可以设置为全局或局部变量，并且假定每个值都可以为1到 65,535 之间的整数值。将其中一个变量设置为0会使该变量为1。

    这两个变量影响AUTO_INCREMENT列的方式：auto_increment_increment 控制列中的值的增量值，auto_increment_offset 确定 AUTO_INCREMENT 列值的起点。

    如果 auto_increment_offset 的值大于 auto_increment_increment 的值，则 auto_increment_offset 的值被忽略。例如：表内已有一些数据，就会用现在已有的最大自增值做为初始值。

错误处理

  • 大小写敏感导致无法同步
    当 Windows 和 Linux 系统做双机热备时，可能出现 Linux 可同步到 Windows，但反过来却不能同步的情况，通过 show slave 命令，可以看到， Linux 上的同步命令无法执行，因为 Linux 上 MySQL 表面默认区分大小写，此时需要修改默认配置，重启后生效：
[mysqld]
#1=on,0=off
lower_case_table_names=1

  • master 同步位置配置错误导致无法同步
    出现错误时仔细检查主从服务器的 master 同步位置配置，确保配置无误
    A 服务器

    image

    B 服务器

    image
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容