mysql 双主脑裂_mysql 双主高可用配置

最新推荐文章于 2024-02-05 10:43:39 发布

果绘鸟海凛姬希花妮

最新推荐文章于 2024-02-05 10:43:39 发布

阅读量122

点赞数

文章标签： mysql 双主脑裂

本文链接：https://blog.csdn.net/weixin_34576446/article/details/111924110

版权

本文详细介绍了如何配置MySQL双主机高可用，包括YUM安装MySQL，配置My.cnf，设置主从复制，并通过Keepalive实现故障转移。在配置中，通过设置GTID模式、binlog格式等确保数据一致性。同时，通过Keepalived的VRRP实例和权重分配，以及在Server2上设置nonpreempt防止脑裂现象。

摘要由CSDN通过智能技术生成

mysql 双主高可用配置

Mysql+ Keepalive配置

server1：10.0.0.4

server2: 10.0.0.3

Mysql安装配置

使用YUM安装：

#yum install -y mysql-community-client.x86_64 mysql-community-common.x86_64 mysql-community-devel.x86_64 mysql-community-libs.x86_64 mysql-community-libs-compat.x86_64 mysql-community-server.x86_64

############ server1: My.cnf配置文件：##################

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

server-id=202

gtid-mode=ON

binlog_format=row

log-slave-updates=true

enforce-gtid-consistency

log-bin=mysql-bin

log-slave-updates

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=4

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

character_set_server=utf8

max_connections = 500

expire_logs_days = 5

slow_query_log = 1

slow_query_log_file=/var/lib/mysql/slow.log

long_query_time = 1

sync_binlog=1

join_buffer_size = 16M

read_buffer_size = 16M

sort_buffer_size = 16M

table_open_cache = 700

max_allowed_packet = 32M

skip_name_resolve = 1

explicit_defaults_for_timestamp = 1

innodb_file_per_table = 1

innodb_fast_shutdown = 0

innodb_purge_threads = 32

innodb_buffer_pool_size = 2G

innodb_flush_log_at_trx_commit = 1

innodb_buffer_pool_instances = 4

innodb_use_native_aio = on

innodb_support_xa = 1

innodb_flush_neighbors = 1

autocommit=1

log_queries_not_using_indexes = 1

log_throttle_queries_not_using_indexes = 30

sql_mode=NO_ENGINE_SUBSTITUTION

replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=performance_schema.%

############## Server2: My.cnf配置文件 ##################

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

server-id=201

gtid-mode=ON

binlog_format=row

log-slave-updates=true

enforce-gtid-consistency

log-bin=mysql-bin

log-slave-updates

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=4

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

character_set_server=utf8

max_connections = 500

expire_logs_days = 5

slow_query_log = 1

slow_query_log_file=/var/lib/mysql/slow.log

long_query_time = 1

sync_binlog=1

join_buffer_size = 16M

read_buffer_size = 16M

sort_buffer_size = 16M

table_open_cache = 700

max_allowed_packet = 32M

skip_name_resolve = 1

explicit_defaults_for_timestamp = 1

innodb_file_per_table = 1

innodb_fast_shutdown = 0

innodb_purge_threads = 32

innodb_buffer_pool_size = 2G

innodb_flush_log_at_trx_commit = 1

innodb_buffer_pool_instances = 4

innodb_use_native_aio = on

innodb_support_xa = 1

innodb_flush_neighbors = 1

autocommit=1

log_queries_not_using_indexes = 1

log_throttle_queries_not_using_indexes = 30

sql_mode=NO_ENGINE_SUBSTITUTION

replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=performance_schema.%

################ END ###############

修改Mysql初时密码：

使用初时密码登陆Mysql

#mysql -u root

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

mysql> FLUSH PRIVILEGES;

在两台服务器上创建主从复制用户：

mysql> grant replication slave,replication client on *.* to slave@'%' identified by '123456';

mysql> FLUSH PRIVILEGES;

#### Server1操作：

mysql> change master to master_host='10.0.0.3',master_port=3306,master_user='slave',master_password='123456',master_auto_position=1;

mysql> start slave;

#### Server2操作：

mysql> change master to master_host='10.0.0.4',master_port=3306,master_user='slave',master_password='123456',master_auto_position=1;

mysql> start slave;

查看状态：

server2 上：

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.4#######

Master_User: slave #######

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 1406

Relay_Log_File: appserver-relay-bin.000007

Relay_Log_Pos: 454

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: Yes ##########

Slave_SQL_Running: Yes ##########

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 1406

Relay_Log_Space: 918

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 202 ##########

Master_UUID: 6a9d0dae-86fa-11e7-a9a6-0894ef365e0a

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 6a9d0dae-86fa-11e7-a9a6-0894ef365e0a:4-6

Executed_Gtid_Set: 6a9d0dae-86fa-11e7-a9a6-0894ef365e0a:1-6,

fcfa135e-873b-11e7-8930-0894ef364852:1-15

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

Keepalive 安装：

# yum install keepalived ipvsadm

######### 配置：10.0.0.3 ####

[root@appserver ~]# cat /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

notification_email {

wdrain@163.com

}

notification_email_from support@163.com

smtp_server localhost

smtp_connect_timeout 30

router_id LVS_DEVEL

}

vrrp_instance VI_1 {

state BACKUP ##服务器均是backup模式

interface eth0

virtual_router_id 51 ##数字相同

priority 100 ###优先级数字越大优先级越高

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

10.0.0.5/24 dev eth0

}

virtual_server 10.0.0.5 3306 {

delay_loop 5

lb_algo wrr ###算法

lb_kind DR

nat_mask 255.255.255.0

persistence_timeout 50

protocol TCP

real_server 10.0.0.3 3306 {

weight 3

notify_down /etc/keepalived/change_mysql.sh

TCP_CHECK {

connect_timeout 10

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

######### 配置：10.0.0.4 ##########

[root@dataserver ~]# cat /etc/keepalived/keepalived.conf