（亲测）MySQL集群搭建-主备搭建

心已赠人 2023-01-17 09:21 281阅读 0赞

文章目录

    • 前言
    • 环境准备
    • 在线搭建主从

前言

数据库在任何业务中都是最重要的环节之一，这就对数据库架构提出的较高的要求。单点数据库永远不应该出现在生产环境，我们已经目睹过太多由于单点、备份缺失造成的损失，所以，搭建高可用 MySQL 集群是非常有必要的。

搭建集群有以下几点好处:

高可用性，在主节失效时自动切换，不需要技术人员紧急处理
高吞吐，可以多个节点同时提供读取数据服务，降低主节点负载，实现高吞吐
可扩展性强，支持在线扩容
无影响备份，在备节点进行备份操作不会对业务产生影响
要说缺点，有以下几点:

架构复杂，在部署、管理方面对技术人员有要求
备节点拉取主节点日志时会对主节点服务器性能有一定影响
如果配置了半同步复制，会对事务提交有一点影响
总的说，集群是一定要搭建的，谁敢把自己的数据跑在一个随时会有风险的数据库上呢。接下来我会以几篇文章介绍怎么从简单地主备模式到高可用架构。本节主要介绍如何搭建 MySQL 主备，注重操作，不会有太多理论讲解。

环境准备

1.1 启动数据库

在两台机器分别启动 MySQL 实例, MySQL 搭建方式可以参考 MySQL 安装(二进制版)



























IP 系统 端口 MySQL版本 节点
192.168.41.83 Centos6.8 3306 5.7.20 Master
192.168.41.72 Centos6.8 3306 5.7.20 Salve

关键配置:

Master:

  1. [client]
  2. port = 3306
  3. default-character-set=utf8mb4
  4. socket = /data/mysql_db/mysql_test/mysql.sock
  6. [mysqld]
  7. datadir = /data/mysql_db/mysql_test
  8. basedir = /usr/local/mysql57
  9. tmpdir = /tmp
  10. socket = /data/mysql_db/mysql_test/mysql.sock
  11. pid-file = /data/mysql_db/mysql_test/mysql.pid
  12. skip-external-locking = 1
  13. skip-name-resolve = 1
  14. port = 3306
  15. server_id = 833306
  17. default-storage-engine = InnoDB
  18. character-set-server = utf8mb4
  19. default_password_lifetime=0
  21. #### log ####
  22. log_timestamps=system
  23. log_bin = /data/mysql_log/mysql_test/mysql-bin
  24. log_bin_index = /data/mysql_log/mysql_test/mysql-bin.index
  25. binlog_format = row
  26. relay_log_recovery=ON
  27. relay_log=/data/mysql_log/mysql_test/mysql-relay-bin
  28. relay_log_index=/data/mysql_log/mysql_test/mysql-relay-bin.index
  29. log_error = /data/mysql_log/mysql_test/mysql-error.log
  31. #### replication ####
  32. replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%
  34. #### semi sync replication settings #####
  35. plugin_dir=/usr/local/mysql57/lib/plugin
  36. plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
  37. loose_rpl_semi_sync_master_enabled = 1
  38. loose_rpl_semi_sync_slave_enabled = 1
  39. loose_rpl_semi_sync_master_timeout = 5000

Salve:

  1. [client]
  2. port = 3306
  3. default-character-set=utf8mb4
  4. socket = /data/mysql_db/mysql_test/mysql.sock
  6. [mysqld]
  7. datadir = /data/mysql_db/mysql_test
  8. basedir = /usr/local/mysql57
  9. tmpdir = /tmp
  10. socket = /data/mysql_db/mysql_test/mysql.sock
  11. pid-file = /data/mysql_db/mysql_test/mysql.pid
  12. skip-external-locking = 1
  13. skip-name-resolve = 1
  14. port = 3306
  15. server_id = 723306
  16. read_only=1
  18. default-storage-engine = InnoDB
  19. character-set-server = utf8mb4
  20. default_password_lifetime=0
  22. #### log ####
  23. log_timestamps=system
  24. log_bin = /data/mysql_log/mysql_test/mysql-bin
  25. log_bin_index = /data/mysql_log/mysql_test/mysql-bin.index
  26. binlog_format = row
  27. relay_log_recovery=ON
  28. relay_log=/data/mysql_log/mysql_test/mysql-relay-bin
  29. relay_log_index=/data/mysql_log/mysql_test/mysql-relay-bin.index
  30. log_error = /data/mysql_log/mysql_test/mysql-error.log
  32. #### replication ####
  33. replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%
  35. #### semi sync replication settings #####
  36. plugin_dir=/usr/local/mysql57/lib/plugin
  37. plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
  38. loose_rpl_semi_sync_master_enabled = 1
  39. loose_rpl_semi_sync_slave_enabled = 1
  40. loose_rpl_semi_sync_master_timeout = 5000

配置解析:

  • datadir, basedir, tmpdir 分别为数据文件位置、数据库程序安装位置、临时文件位置
  • server_id 实例id，注意，同一集群机器的 server_id 不能相同
  • read_only 是否只读, 一般在备库设置
  • log_bin, log_bin_index 二进制日志位置、二进制日志索引文件位置
  • binlog_format 二进制日志格式，row 表示记录每条数据变化情况、statement 表示记录相关 sql 语句、mixed 表示两种混用，在搭建集群的时候建议使用 row 格式，如果是用 sql 语句来同步数据很容易出现数据不一致的情况
  • relay_log_recovery slave 宕机后，假如中继日志损坏，则重新拉取日志，为了保证中继日志完整性，建议开启
  • relay_log, relay_log_index 中继日志以及中继日志索引文件位置
  • log_error 错误日志位置
  • replicate_wild_ignore_table 同步时需要忽略的表，这里我们忽略了系统统计表，如果出现奇怪的同步失败情况，可以尝试开启
  • plugin_dir 插件位置
  • plugin_load 启动时需要加载的插件
  • loose_rpl_semi_sync_master_enabled 是否开启无损半同步复制-主库（建议主备都开启,方便主备切换）
  • loose_rpl_semi_sync_slave_enabled 是否开启无损半同步复制-备库（建议主备都开启,方便主备切换）

1.2 插入数据

我们假设 Master 是正在使用的数据库，现在要在线搭建备库，我们往 Master 节点插入一些测试数据

进入mysql客户端

  1. mysql -S /data/mysql_db/mysql_test/mysql.sock
  3. db83-3306>>show databases;
  4. +--------------------+
  5. | Database           |
  6. +--------------------+
  7. | information_schema |
  8. | mysql              |
  9. | performance_schema |
  10. | sys                |
  11. +--------------------+
  12. 4 rows in set (0.00 sec)
  14. db83-3306>>create database mytest;
  15. Query OK, 1 row affected (0.00 sec)
  17. db83-3306>>use mytest;
  18. Database changed
  19. db83-3306>>create table test1(
  20.     -> id int not null primary key auto_increment,
  21.     -> name varchar(16) not null default '',
  22.     -> age int not null default 0
  23.     -> ) engine = InnoDb charset = utf8;
  24. Query OK, 0 rows affected (0.01 sec)
  25. db83-3306>>insert into test1 values (0, 'a', 16), (0, 'b', 17), (0, 'c', 18), (0, 'd', 19);
  26. Query OK, 4 rows affected (0.00 sec)
  27. Records: 4  Duplicates: 0  Warnings: 0
  29. db83-3306>>select * from test1;
  30. +----+------+-----+
  31. | id | name | age |
  32. +----+------+-----+
  33. |  1 | a    |  16 |
  34. |  2 | b    |  17 |
  35. |  3 | c    |  18 |
  36. |  4 | d    |  19 |
  37. +----+------+-----+
  38. 4 rows in set (0.00 sec)

在线搭建主从

现在我们的环境如下

  • 192.168.41.83:3306 Master 节点，正在使用
  • 192.168.41.72:3306 新搭建数据库，要在上面做 192.168.41.83 的备库

2.1 创建同步用户

我们创建一个用户名为 repl 的用户，授予 REPLICATION SLAVE 权限专门用来同步

  1. db83-3306>>CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
  2. Query OK, 0 rows affected (5.01 sec)
  4. db83-3306>>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
  5. Query OK, 0 rows affected (0.00 sec)
  7. db83-3306>>flush privileges;
  8. Query OK, 0 rows affected (0.00 sec)

2.2 备份数据

常用的备份数据的方式有 innobackupex 和 mysqldump，这里数据量少，我们用 mysqldump 进行全备

  1. [mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysqldump -S /data/mysql_db/mysql_test/mysql.sock -F --opt -R --single-transaction --master-data=2 --default-character-set=utf8 -A > mysql_backup_full.sql

参数解析:

  • -S 选择 socket 文件，本机连接数据库可以用这种方法，也可以指定 ip、端口进行连接
  • -F 开始导出之前刷新日志
  • --opt 如果有这个参数表示同时激活了 mysqldump 命令的 quick，add-drop-table，add-locks，extended-insert，lock-tables 参数
  • --quick 代表忽略缓冲输出，mysqldump 命令直接将数据导出到指定的SQL文件
  • --add-drop-table 就是在每个 CREATE TABEL 命令之前增加 DROP-TABLE IF EXISTS 语句，防止数据表重名
  • --add-locks 在INSERT数据之前和之后锁定和解锁对应的数据表
    --extended-insert 表示可以多行插入
  • -R 导出存储过程以及自定义函数, 如果有用到存储过程, 需要加这个参数
  • --single-transaction (innodb)设置事务的隔离级别为可重复读，即 REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据, 如果全部表都为 InnoDB 就带上这个参数，保证数据一致性，备份时不会锁表。如果有 MyISAM 的表，需要锁表备份才能保证数据的一致性
  • --lock-all-tables 备份过程加读锁, single-transaction 选项和 lock-all-tables 选项是二选一的
  • --master-data=2 记录当前二进制日志位置, master_data取1和取2的区别，只是后者把 change master … 命令注释起来了
  • --default-character-set 选择编码, 这个选项非常重要， 编码选不对或者没有设置很容易造成乱码
  • -A 代表备份所有的库

数据备份完毕后，把数据文件直接传输到 Slave 机器上

  1. [mysql@mysql-test-83 ~]$ ll
  2. total 772
  3. -rw-rw-r-- 1 mysql mysql 786921 Nov 17 10:38 mysql_backup_full.sql
  4. [mysql@mysql-test-83 ~]$ rsync -avzP mysql_backup_full.sql 192.168.41.72:/home/mysql/

2.3 数据恢复

在 Slave 机器上直接执行 sql 文件导入数据

  1. [mysql@mysql-test-72 ~]$ ll mysql_backup_full.sql 
  2. -rw-rw-r-- 1 mysql mysql 786921 Nov 17 10:38 mysql_backup_full.sql
  3. [mysql@mysql-test-72 ~]$ /usr/local/mysql57/bin/mysql -S /data/mysql_db/mysql_test/mysql.sock < mysql_backup_full.sql

导入完毕，我们可以看到数据和 Master 的备份数据一致

  1. db72-3306>>show databases;
  2. +--------------------+
  3. | Database           |
  4. +--------------------+
  5. | information_schema |
  6. | mysql              |
  7. | mytest             |
  8. | performance_schema |
  9. | sys                |
  10. +--------------------+
  11. 5 rows in set (0.00 sec)
  13. db72-3306>>use mytest;
  14. Reading table information for completion of table and column names
  15. You can turn off this feature to get a quicker startup with -A
  17. Database changed
  18. db72-3306>>show tables;
  19. +------------------+
  20. | Tables_in_mytest |
  21. +------------------+
  22. | test1            |
  23. +------------------+
  24. 1 row in set (0.00 sec)
  26. db72-3306>>select * from test1;
  27. +----+------+-----+
  28. | id | name | age |
  29. +----+------+-----+
  30. |  1 | a    |  16 |
  31. |  2 | b    |  17 |
  32. |  3 | c    |  18 |
  33. |  4 | d    |  19 |
  34. +----+------+-----+
  35. 4 rows in set (0.00 sec)

2.4 开启同步

回到备份文件，我们从头部找到 Master 备份时间点的二进制日志位置

  1. [mysql@mysql-test-72 ~]$ head -30 mysql_backup_full.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'
  3. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154;

MASTER_LOG_FILE 和 MASTER_LOG_POS 就是在 Master 执行 show master status 得到的二进制位置信息。现在，我们执行同步命令

  1. -- 同步配置
  2. CHANGE MASTER TO
  3. MASTER_HOST='192.168.41.83',
  4. MASTER_PORT=3306,
  5. MASTER_USER='repl',
  6. MASTER_PASSWORD='repl',
  7. MASTER_LOG_FILE='mysql-bin.000002',
  8. MASTER_LOG_POS=154;
  10. -- 开启同步
  11. start slave

实际执行结果如下：

  1. db72-3306>>CHANGE MASTER TO
  2.     -> MASTER_HOST='192.168.41.83',
  3.     -> MASTER_PORT=3306,
  4.     -> MASTER_USER='repl',
  5.     -> MASTER_PASSWORD='repl',
  6.     -> MASTER_LOG_FILE='mysql-bin.000002',
  7.     -> MASTER_LOG_POS=154;
  8. Query OK, 0 rows affected, 2 warnings (0.20 sec)
  10. db72-3306>>start slave;
  11. Query OK, 0 rows affected (0.01 sec)

查看同步状态

  1. db72-3306>>show slave status \G;
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: 192.168.41.83
  5.                   Master_User: repl
  6.                   Master_Port: 3306
  7.                 Connect_Retry: 60
  8.               Master_Log_File: mysql-bin.000004
  9.           Read_Master_Log_Pos: 154
  10.                Relay_Log_File: mysql-relay-bin.000002
  11.                 Relay_Log_Pos: 320
  12.         Relay_Master_Log_File: mysql-bin.000004
  13.              Slave_IO_Running: Yes
  14.             Slave_SQL_Running: Yes
  15.               Replicate_Do_DB: 
  16.           Replicate_Ignore_DB: 
  17.            Replicate_Do_Table: 
  18.        Replicate_Ignore_Table: 
  19.       Replicate_Wild_Do_Table: 
  20.   Replicate_Wild_Ignore_Table: 
  21.                    Last_Errno: 0
  22.                    Last_Error: 
  23.                  Skip_Counter: 0
  24.           Exec_Master_Log_Pos: 154
  25.               Relay_Log_Space: 527
  26.               Until_Condition: None
  27.                Until_Log_File: 
  28.                 Until_Log_Pos: 0
  29.            Master_SSL_Allowed: No
  30.            Master_SSL_CA_File: 
  31.            Master_SSL_CA_Path: 
  32.               Master_SSL_Cert: 
  33.             Master_SSL_Cipher: 
  34.                Master_SSL_Key: 
  35.         Seconds_Behind_Master: 0
  36. Master_SSL_Verify_Server_Cert: No
  37.                 Last_IO_Errno: 0
  38.                 Last_IO_Error: 
  39.                Last_SQL_Errno: 0
  40.                Last_SQL_Error: 
  41.   Replicate_Ignore_Server_Ids: 
  42.              Master_Server_Id: 833306
  43.                   Master_UUID: 15958368-e9a0-11e8-a98c-ecb1d77febe4
  44.              Master_Info_File: /data/mysql_db/mysql_seg_3306/master.info
  45.                     SQL_Delay: 0
  46.           SQL_Remaining_Delay: NULL
  47.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  48.            Master_Retry_Count: 86400
  49.                   Master_Bind: 
  50.       Last_IO_Error_Timestamp: 
  51.      Last_SQL_Error_Timestamp: 
  52.                Master_SSL_Crl: 
  53.            Master_SSL_Crlpath: 
  54.            Retrieved_Gtid_Set: 
  55.             Executed_Gtid_Set: 
  56.                 Auto_Position: 0
  57.          Replicate_Rewrite_DB: 
  58.                  Channel_Name: 
  59.            Master_TLS_Version: 
  60. 1 row in set (0.00 sec)
  62. ERROR: 
  63. No query specified

我们可以从 Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master 这三个参数可以判断出同步状态是否正常

  • Slave_IO_Running 取 Master 日志的线程, Yes 为正在运行
  • Slave_SQL_Running 从日志恢复数据的线程, Yes 为正在运行
  • Seconds_Behind_Master 当前数据库相对于主库的数据延迟， 这个值是根据二进制日志的时间戳计算得到的(秒)

从输出结果可以看到我们的同步是正常的，下面我们来测试一下

2.5 同步测试

在 Master 节点插入新数据

  1. db83-3306>>insert into test1 values(0, 'chengqm', 24);
  2. Query OK, 1 row affected (0.00 sec)
  4. db83-3306>>select * from test1;
  5. +----+---------+-----+
  6. | id | name    | age |
  7. +----+---------+-----+
  8. |  1 | a       |  16 |
  9. |  2 | b       |  17 |
  10. |  3 | c       |  18 |
  11. |  4 | d       |  19 |
  12. |  5 | chengqm |  24 |
  13. +----+---------+-----+
  14. 5 rows in set (0.00 sec)

备节点检查数据同步状态

  1. db72-3306>>select * from test1;
  2. +----+---------+-----+
  3. | id | name    | age |
  4. +----+---------+-----+
  5. |  1 | a       |  16 |
  6. |  2 | b       |  17 |
  7. |  3 | c       |  18 |
  8. |  4 | d       |  19 |
  9. |  5 | chengqm |  24 |
  10. +----+---------+-----+
  11. 5 rows in set (0.00 sec)

可以看到数据已经同步到备节点，本次主备搭建完成

发表评论

表情：
评论列表 （有 0 条评论，281人围观）

还没有评论，来说两句吧...

相关阅读

    相关 mysql一主多从集群搭建

    主从复制原理 当对主服务器进行写的操作时，主服务器将自己的操作记录到一个二进制日志文件中，从服务器有一个心跳机制，定时的去读取主服务器的日志文件，当对比自己的日志文件有差

    ゝ一世哀愁。ゝ一世哀愁。/ 0 赞/ 183 阅读