mysql 主从备份 + keepalived实现高可用

最新推荐文章于 2024-03-17 19:42:26 发布

原创

最新推荐文章于 2024-03-17 19:42:26 发布 · 605 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#keepalived #linux #数据库 #mysql

本文介绍了如何使用MySQL主从复制实现数据备份，并通过Keepalived搭建高可用集群。详细步骤包括主从数据库配置、主从复制的脚本执行，以及Keepalived的安装和配置，确保在异常情况下能自动切换主备节点。

前提：

主数据库：172.16.9.11:3307

备数据库：172.16.9.12:3307

集群IP: 172.16.9.13

一：实现主从复制

1.修改主数据库my.cnf配置文件(mysqld配置切记房在client上面)

[mysqld]
basedir = /usr/local/mysql3307
datadir = /usr/local/mysql3307/data
socket = /usr/local/mysql3307/tmp/mysql.sock
port = 3307

general_log_file=/usr/local/mysql3307/mysql-log/mysql.log
general_log=1
slow_query_log=on
slow-query-log-file=/usr/local/mysql3307/mysql-log/mysql-slow.log
long_query_time=1
lower_case_table_names=1

server-id=1
log-bin=/usr/local/mysql3307/logbin/mysql_master
log-slave-updates=on
expire_logs_days = 10

replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=sys.%

[client]
socket = /usr/local/mysql3307/tmp/mysql.sock
default-character-set=utf8

[mysqld_safe]
log-error=/usr/local/mysql3307/mysql-log/error.log
pid-file=/usr/local/mysql3307/mysql.pid

2.修改从数据库my.cnf配置文件(mysqld配置切记放在client上面)

[mysqld]
basedir = /usr/local/mysql3307
datadir = /usr/local/mysql3307/data
socket = /usr/local/mysql3307/tmp/mysql.sock
port = 3307

general_log_file=/usr/local/mysql3307/mysql-log/mysql.log
general_log=1
slow_query_log=on
slow-query-log-file=/usr/local/mysql3307/mysql-log/mysql-slow.log
long_query_time=1
lower_case_table_names=1

server-id=1
log-bin=/usr/local/mysql3307/logbin/mysql_master
log-slave-updates=on
expire_logs_days = 10

replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=sys.%

[client]
socket = /usr/local/mysql3307/tmp/mysql.sock
default-character-set=utf8

[mysqld_safe]
log-error=/usr/local/mysql3307/mysql-log/error.log
pid-file=/usr/local/mysql3307/mysql.pid