前面针对mysql 8的安装已经做了详细介绍，按照之前的手册是可以正常完成mysql8的安装和启动。下面我们针对mysql高可用的安装做详细的介绍。
数据库系统是21世纪最伟大的发明之一，其技术到目前为止依然没有大的改变（针对sql语言），目前主流的数据库技术只是在可用性、大数据处理及大数据存储上做了优化及处理，其底层核心技术依然没有大的变化。
数据库作为企业核心基础系统，网络、系统等可以出现故障或损坏，但数据库的数据安全、数据可用性等是企业首要考虑的，本篇文章介绍下mysql数据库技术高可用性之一-双主架构的安装，针对数据库可用性稍后在单独一篇文章进行介绍和分析。
keepalived安装；
参考keepalived安装手册
mysql数据库安装
参考mysql数据库安装手册
https://megou.life/mysql8-install/
mysql 双主配置
1、mysql主从配置；
半同步插件安装
-- 在主
install plugin rpl_semi_sync_master soname 'semisync_master.so';
-- 在每个从
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
2、配置mysql配置文件，分别为master及slave；
master节点配置文件
[client]
port = 3306
socket = /app/mysql8.0.13/logs/mysql.sock
default-character-set = utf8mb4
[mysqld]
server-id = 81353
port = 3306
user = mysql
socket = /app/mysql8.0.13/logs/mysql.sock
bind_address = 0.0.0.0
skip_name_resolve = 1
datadir = /data/mysql8.0.13/data
transaction_isolation = READ-COMMITTED
tmp_table_size = 67108864
tmpdir = /tmp
default-time_zone = '+8:00'
character-set-server = utf8mb4
collation_connection = utf8mb4_general_ci
collation_database = utf8mb4_general_ci
collation_server = utf8mb4_general_ci
default_collation_for_utf8mb4 = utf8mb4_general_ci
max_allowed_packet = 16777216
read_buffer_size = 16777216
read_rnd_buffer_size = 33554432
sort_buffer_size = 33554432
log_error = /app/mysql8.0.13/logs/error.log
slow_query_log = 1
slow_query_log_file = /app/mysql8.0.13/logs/slow.log
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
long_query_time = 5
min_examined_row_limit = 100
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_expire_logs_seconds = 2592000
log_bin = /data/mysql8.0.13/bin-logs/core-mysql8-01-bin
log-bin-index = /data/mysql8.0.13/bin-logs/core-mysql8-01.index
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = on
binlog_format = row
relay_log = /data/mysql8.0.13/bin-logs/core-mysql8-01-relay-bin
relay-log-index = /data/mysql8.0.13/bin-logs/core-mysql8-01-relay-bin.index
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 8
slave_preserve_commit_order = 1
binlog-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
innodb_file_per_table = 1
innodb_data_home_dir = /data/mysql8.0.13/data
innodb_data_file_path = ibdata1:12M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /data/mysql8.0.13/data
wait_timeout=600
interactive_timeout=600
max_connections=16000
open_files_limit =80000
innodb_buffer_pool_size = 6G
max_connect_errors=10000
innodb_flush_log_at_trx_commit=1
table_open_cache = 24762
auto_increment_increment=2
auto_increment_offset=1
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
default-character-set = utf8mb4
[mysqlhotcopy]
interactive-timeout
slave节点配置文件
[client]
port = 3306
socket = /app/mysql8.0.13/logs/mysql.sock
default-character-set = utf8mb4
[mysqld]
server-id = 81354
port = 3306
user = mysql
socket = /app/mysql8.0.13/logs/mysql.sock
bind_address = 0.0.0.0
skip_name_resolve = 1
datadir = /data/mysql8.0.13/data
transaction_isolation = READ-COMMITTED
tmp_table_size = 67108864
tmpdir = /tmp
default-time_zone = '+8:00'
character-set-server = utf8mb4
collation_connection = utf8mb4_general_ci
collation_database = utf8mb4_general_ci
collation_server = utf8mb4_general_ci
default_collation_for_utf8mb4 = utf8mb4_general_ci
max_allowed_packet = 16777216
read_buffer_size = 16777216
read_rnd_buffer_size = 33554432
sort_buffer_size = 33554432
log_error = /app/mysql8.0.13/logs/error.log
slow_query_log = 1
slow_query_log_file = /app/mysql8.0.13/logs/slow.log
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
long_query_time = 5
min_examined_row_limit = 100
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = /data/mysql8.0.13/bin-logs/core-mysql8-02-bin
log-bin-index = /data/mysql8.0.13/bin-logs/core-mysql8-02.index
binlog_expire_logs_seconds = 2592000
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = on
binlog_format = row
relay_log = /data/mysql8.0.13/bin-logs/core-mysql8-02-relay-bin
relay-log-index = /data/mysql8.0.13/bin-logs/core-mysql8-02-relay-bin.index
relay_log_recovery = 1
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 8
slave_preserve_commit_order = 1
binlog_gtid_simple_recovery = 1
binlog-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
innodb_file_per_table = 1
innodb_data_home_dir = /data/mysql8.0.13/data
innodb_data_file_path = ibdata1:12M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /data/mysql8.0.13/data
wait_timeout=600
interactive_timeout=600
max_connections=16000
open_files_limit =80000
innodb_buffer_pool_size = 6G
max_connect_errors=10000
innodb_flush_log_at_trx_commit=1
table_open_cache = 24762
auto_increment_increment=2
auto_increment_offset=2
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
default-character-set = utf8mb4
[mysqlhotcopy]
interactive-timeout
主和从mysql配置文件配置完毕后，正常启动数据库即可。
3、分别在主从数据库上创建相关复制账号，并赋权；
master节点创建主账号并赋予权限
create user 'repl'@'10.89.4.%' IDENTIFIED WITH mysql_native_password BY 'Slave';
GRANT REPLICATION SLAVE ON *.* TO `repl`@`10.89.4.%`;
slave节点创建主账号并赋予权限
create user 'repl'@'10.89.4.%' IDENTIFIED WITH mysql_native_password BY 'Slave';
GRANT REPLICATION SLAVE ON *.* TO `repl`@`10.89.4.%`;
4、开启数据库复制，需要现在master节点上查询当前数据库日志信息；
执行show master status \G;查看当前master节点数据库日志信息及位置；
mysql> show master status \G;
*************************** 1. row ***************************
File: core-mysql8-01-bin.000006
Position: 1701
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
slave节点配置复制参数信息；
CHANGE MASTER TO
MASTER_HOST='10.89.4.53',
MASTER_USER='repl',
MASTER_PASSWORD='Slave',
MASTER_LOG_FILE='core-mysql8-01-bin.000006',
MASTER_LOG_POS=1701;
执行复制命令进行复制；
Start slave;
双主则需要在主节点 执行同样命令即可，同样需要在节点上执行show master status \G；查看日志信息及位置；
CHANGE MASTER TO
MASTER_HOST='10.89.4.54',
MASTER_USER='repl',
MASTER_PASSWORD='Slave',
MASTER_LOG_FILE='core-mysql8-02-bin.000006',
MASTER_LOG_POS=526;
Start slave;
执行完成后分别在master和slave节点上执行show slave status \G；进行查看复制是否正常；
mysql 以下两个进程都为yes表示复制正常。
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.198.40.132
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqldb8-02-bin.000008
Read_Master_Log_Pos: 459638976
Relay_Log_File: mysqldb8-01-relay-bin.000010
Relay_Log_Pos: 421
Relay_Master_Log_File: mysqldb8-02-bin.000008
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: test,information_schema,performance_schema
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: 459638976
Relay_Log_Space: 728
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: 132
Master_UUID: b4c95d1e-8f82-11ea-a720-005056a0928d
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: b4c95d1e-8f82-11ea-a720-005056a0928d:1-557
Executed_Gtid_Set: 1a288557-8f7f-11ea-968b-005056a087f7:1-1395362,
b4c95d1e-8f82-11ea-a720-005056a0928d:1-557:2114-2770:3392-4058:4929-176502:179960-1608793
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.01 sec)
ERROR:
No query specified
mysql>
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.198.40.131
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqldb8-01-bin.000017
Read_Master_Log_Pos: 459507610
Relay_Log_File: mysqldb8-02-relay-bin.000018
Relay_Log_Pos: 459507796
Relay_Master_Log_File: mysqldb8-01-bin.000017
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: test,information_schema,performance_schema
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: 459507610
Relay_Log_Space: 459508103
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: 131
Master_UUID: 1a288557-8f7f-11ea-968b-005056a087f7
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: 1a288557-8f7f-11ea-968b-005056a087f7:1009522-1395363
Executed_Gtid_Set: 1a288557-8f7f-11ea-968b-005056a087f7:1-1009362:1009522-1395363,
b4c95d1e-8f82-11ea-a720-005056a0928d:1-557:2114-2770:3392-4058:4929-176502:179960-1608793
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.04 sec)
ERROR:
No query specified
mysql>
以上仅仅是完成了数据库双主同步的配置，但是采用此架构会有一个致命的问题，若是应用程序同时去对数据库里面的一条记录进行修改，并同时连接了两个节点的IP，就会出现数据不一致，
造成数据同步不一致，数据丢失的情况，为了避免以上情况，需要采用虚地址和切换脚本来确保应用程序连接到的数据库永远在一个IP地址上。
keepalived配置文件说明
主节点配置文件：
! Configuration File for keepalived
global_defs {
notification_email {
root@localhost
}
notification_email_from dreamfly@localhost
smtp_server 127.0.0.1
smtp_connect_timeout 10
router_id core-mysql8-01
}
vrrp_instance VI_1 {
state BACKUP
interface ens192
virtual_router_id 155
priority 100
nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass corem3prod
}
virtual_ipaddress {
10.89.4.55
}
}
virtual_server 10.89.4.55 3306 {
delay_loop 2
lb_algo wrr
lb_kind DR
persistence_timeout 60
protocol TCP
real_server 10.89.4.53 3306 {
weight 3
notify_down /app/keepalived-2.0.19/scripts/check_mysql.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
备用节点配置文件
! Configuration File for keepalived
global_defs {
notification_email {
root@localhost
}
notification_email_from dreamfly@localhost
smtp_server 127.0.0.1
smtp_connect_timeout 10
router_id core-mysql8-02
}
vrrp_instance VI_1 {
state BACKUP
interface ens192
virtual_router_id 155
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass corem3prod
}
virtual_ipaddress {
10.89.4.55
}
}
virtual_server 10.89.4.55 3306 {
delay_loop 2
lb_algo wrr
lb_kind DR
persistence_timeout 60
protocol TCP
real_server 10.89.4.54 3306 {
weight 3
notify_down /app/keepalived-2.0.19/scripts/check_mysql.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
创建keepalived用户用于检测数据库，两台服务器均需要配置。
create user 'keepalived'@'localhost' identified with mysql_native_password by 'keepalived';
创建keepalived脚本用于检测数据库状态，如果不通则切换keepalive虚地址，本配置文件设置的是数据库down掉用30s后进行执行杀死keepalived实现虚地址的切换。
由于我司业务实时性不是特别高，设置的较长，实际配置需结合自身业务情况进行配置。
check_mysql.sh
#! /bin/sh
basedir=/app/mysql8.0.13/
$basedir/bin/mysqladmin -ukeepalived -pkeepalived --connect-timeout=3 ping >/dev/null;RETVAL=$?
if [ $RETVAL -ne 0 ];then
alter
sleep 30
pkill keepalived
exit 0
fi
文章评论