keepalived实现Mysql高可用

最新推荐文章于 2024-05-22 15:20:28 发布

从头再来而已

最新推荐文章于 2024-05-22 15:20:28 发布

阅读量221

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_43647217/article/details/118052995

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、环境信息

10.3.39.220 mysql5.7（主）	keepalived
10.3.39.221 mysql5.7（备)	keepalived

Mysql5.7.33下载地址：

https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz

二、安装准备

确定数据库的安装路径：
/home/mysql
数据文件位置
/home/mysql/data

三、下载mysql并且安装mysql详细步骤：

下载mysql

wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz

2.解压缩mysql软件包到/home/目录下并取名叫mysql

[root@localhost ~]# tar -zxf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /home/
[root@localhost ~]# cd /home/
[root@localhost home]# mv mysql-5.7.33-linux-glibc2.12-x86_64/  mysql

3.创建mysql数据目录、创建mysql用户和用户组

[root@localhost home]# mkdir /home/mysql/data
[root@localhost home]# groupadd mysql
[root@localhost home]# useradd -r -g mysql mysql

修改/home/mysql的属组和属主，并修改文件权限

[root@localhost home]# chown -R mysql.mysql /home/mysql/
[root@localhost home]# chmod -R 755 /home/mysql/

安装libaio依赖包

 [root@localhost home]# yum search libaio
 如果没安装，可以用下面命令安装
 [root@localhost home]# yum -y install libaio

6.备份并修改mysql.server文件并复制到/etc/init.d/下

[root@localhost home]# cd /home/mysql/support-files/
[root@localhost support-files]# cp mysql.server mysql.server_bak
[root@localhost support-files]# sed -i "s:/usr/local/mysql:/home/mysql:g"  mysql.server
[root@localhost support-files]# cp mysql.server /etc/init.d/mysqld

7.给/etc/init.d/mysqld设置755的文件权限，让系统可以执行

[root@localhost support-files]# chmod 755 /etc/init.d/mysqld

8.修改mysql的my.cnf文件如果my.cnf文件存在，先备份my.cnf之后请空文件里面所有内容之后添加内容。。

如果my.cnf文件不存在，就创建一个新的my.cnf文件并添加内容。

[root@localhost bin]# vim /etc/my.cnf
[client]
no-beep
socket =/home/mysql/mysql.sock
# pipe
# socket=0.0
port=3306

[mysql]
default-character-set=utf8

[mysqld]
basedir=/home/mysql
datadir=/home/mysql/data
port=3306
pid-file=/home/mysql/mysqld.pid

#skip-grant-tables
skip-name-resolve
socket = /home/mysql/mysql.sock
character-set-server=utf8
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true

log-bin=/home/mysql/data/mysql-bin.log

# Server Id.
server-id=2
max_connections=2000
query_cache_size=0
table_open_cache=2000
tmp_table_size=246M
thread_cache_size=300
#限定用于每个数据库线程的栈大小。默认设置足以满足大多数应用
thread_stack = 192k
key_buffer_size=512M
read_buffer_size=4M
read_rnd_buffer_size=32M
innodb_data_home_dir = /home/mysql/data/
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=16M
innodb_buffer_pool_size=256M
innodb_log_file_size=128M
innodb_thread_concurrency=128
innodb_autoextend_increment=1000
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=128M
max_allowed_packet=1024M
max_connect_errors=2000
open_files_limit=4161
query_cache_type=0
sort_buffer_size=32M
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
#批量插入数据缓存大小，可以有效提高插入效率，默认为8M
bulk_insert_buffer_size = 64M
interactive_timeout = 120
wait_timeout = 120
log-bin-trust-function-creators=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 10000
rpl_semi_sync_master_trace_level = 32
rpl_semi_sync_master_wait_for_slave_count = 1
rpl_semi_sync_master_wait_no_slave = on
rpl_semi_sync_master_wait_point = AFTER_SYNC
#skip-grant-tables
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

初始化Mysql

[root@localhost bin]# cd /home/mysql/bin/
[root@localhost bin]# ./mysqld --user=mysql --basedir=/home/mysql --datadir=/home/mysql/data  --initialize
执行初始化命令时出现这个报错：
mysqld: Can't read dir of '/etc/my.cnf.d' (Errcode: 2 - No such file or directory)
mysqld: [ERROR] Fatal error in defaults handling. Program aborted!
说明是没有/etc/my.cnf.d目录
创建一个/etc/my.cnf.d空目录即可 
创建完成后重新执行初始化命令即可
[root@localhost bin]# mkdir /etc/my.cnf.d
[root@localhost bin]# ./mysqld --user=mysql --basedir=/home/mysql --datadir=/home/mysql/data  --initialize
root@localhost: RiDYhLfZY7,q

记录后面显示的root@localhost:后面的密码登陆数据库，显示的乱码是数据库登陆密码

11.创建软连接到本地启动路径（因为数据库默认启动位置是/usr/local/mysql,需要创建软连接）

[root@localhost mysql]# ln -s /home/mysql/bin/mysql /usr/bin/
[root@localhost mysql]# ln -s /home/mysql/bin/mysqld  mysqld
[root@localhost mysql]# ln -s /home/mysql/bin/mysqld /usr/local/mysqld

12.备份Mysqld_safe文件并使用sed命令修改Mysqld_safe文件内容

[root@localhost mysql]# cp /home/mysql/bin/mysqld_safe  /home/mysql/bin/mysqld_safe_bak
[root@localhost mysql]# sed -i "s:/usr/local/mysql:/home/mysql:g"  /home/mysql/bin/mysqld_safe

13.启动数据库

[root@localhost mysql]# /etc/init.d/mysqld start
或者：./bin/mysqld_safe &

14.登陆数据库，使用之前记录的默认密码进入

[root@localhost mysql]# mysql -uroot -p'RiDYhLfZY7,q'
 首次登陆数据库需要更换新密码才能使用

15.修改mysql管理员密码

mysql> set password=password('root');
mysql> grant all privileges on *.* to root@'%' identified by 'root';
mysql> flush privileges;

数据库搭建完毕！！！！

第二台数据库跟着以上步骤进行安装

搭建Mysql主主架构执行以下操作

修改my.cnf文件

修改内容：
[root@localhost mysql]# vim /etc/my.cnf
log-bin=/home/mysql/data/mysql-bin.log   主主同步文件
server-id=1   数据库编号两台不能一致
修改完成后保存退出并重启数据库
[root@localhost mysql]# /etc/init.d/mysqld restart

17.进入数据库授权连接用户以及查看master编号

    mysql> grant replication slave on *.* to sync@'%' identified by '123456';
mysql> show master status;

18.切换到第二台数据库

同样修改my.cnf文件
修改内容：
[root@localhost mysql]# vim /etc/my.cnf
log-bin=/home/mysql/data/mysql-bin.log   主主同步文件
server-id=1   数据库编号两台不能一致

19.保存退出并进入数据库请求同步

mysql> CHANGE MASTER TO MASTER_HOST='10.3.39.220',MASTER_USER='sync',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=154;
mysql> start slave;

20 查看master同步情况

mysql> show slave status\G

这样的步骤第二台同样执行操作

搭建keepalived

keepliaved需要的脚本：

下载keeplaived软件包并解压

[root@localhost ~]# tar -zxf keepalived-1.2.13.tar.gz -C /home/
[root@localhost ~]# cd /home/
[root@localhost home]# ls
keepalived-1.2.13  lgk  mysql
[root@localhost home]# cd keepalived-1.2.13/

[root@localhost keepalived-1.2.13]# ./configure --prefix=/home/keepalived && make && make install

2.创建启动项，创建软连接

[root@localhost keepalived-1.2.13]# cp /home/keepalived/etc/rc.d/init.d/keepalived  /etc/rc.d/init.d/
[root@localhost keepalived-1.2.13]# cp /home/keepalived/etc/sysconfig/keepalived  /etc/sysconfig/
[root@localhost keepalived-1.2.13]# mkdir /etc/keepalived
[root@localhostkeepalived-1.2.13]#cp  /home/keepalived/etc/keepalived/keepalived.conf  /etc/keepalived/
[root@localhost keepalived-1.2.13]# cp /home/keepalived/sbin/keepalived  /usr/sbin/
[root@localhost keepalived-1.2.13]# chkconfig --add keepalived
[root@localhost keepalived-1.2.13]# chkconfig --level 315 keepalived on
[root@localhost keepalived-1.2.13]# chkconfig --list keepalived

编辑keepalived文件，设置VIP

  [root@localhost keepalived-1.2.13]# cd /etc/keepalived/
  [root@localhost keepalived]# vim keepalived.conf
vrrp_script check_run {
 script "/home/keepalived/shell/mysql_check.sh"
 interval 60
}

vrrp_sync_group VG1 {
 group {
 VI_1
}
}

vrrp_instance VI_1 {
     state BACKUP
     interface ens192
     virtual_router_id 51
     priority 100
     advert_int 1
     authentication {
         auth_type PASS
         auth_pass 1234
     }
     virtual_ipaddress {
        10.3.39.231
     }
}

编写脚本并移动到keepalived指定目录下

[root@localhost shell]# mkdir /home/keepalived/shell/
[root@localhost shell]# vim master.sh
#/home/mysql/master.sh的作用是状态改为master以后执行的脚本。首先判断复制是否有延迟，如果有延迟，等1分钟后，不论是否有延迟，都并停止复制，并且记录binlog和pos点



 /home/mysql/stop.sh表示Keepalived停止以后需要执行的脚本。检查是否还有复制写入操作，最后无论是否执行完毕都退出。
 
[root@localhost shell]# vim stop.sh




[root@localhost shell]# vim mysql_check.sh

5.编写完成脚本后，重启keepalived

  [root@localhost shell]# /etc/init.d/keepalived restart

6.完成

从头再来而已

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
keepalived实现Mysql高可用

一、环境信息10.3.39.220 mysql5.7（主）keepalived10.3.39.221 mysql5.7（备)keepalivedMysql5.7.33下载地址：https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz二、安装准备确定数据库的安装路径：/home/mysql数据文件位置/home/mysql/data三、下载.
复制链接

扫一扫