本文共 7507 字，大约阅读时间需要 25 分钟。
一、环境信息
| 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
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-beepsocket =/home/mysql/mysql.sock# pipe# socket=0.0port=3306[mysql]default-character-set=utf8[mysqld]basedir=/home/mysqldatadir=/home/mysql/dataport=3306pid-file=/home/mysql/mysqld.pid#skip-grant-tablesskip-name-resolvesocket = /home/mysql/mysql.sockcharacter-set-server=utf8default-storage-engine=INNODBexplicit_defaults_for_timestamp = truelog-bin=/home/mysql/data/mysql-bin.log# Server Id.server-id=2max_connections=2000query_cache_size=0table_open_cache=2000tmp_table_size=246Mthread_cache_size=300#限定用于每个数据库线程的栈大小。默认设置足以满足大多数应用thread_stack = 192kkey_buffer_size=512Mread_buffer_size=4Mread_rnd_buffer_size=32Minnodb_data_home_dir = /home/mysql/data/innodb_flush_log_at_trx_commit=0innodb_log_buffer_size=16Minnodb_buffer_pool_size=256Minnodb_log_file_size=128Minnodb_thread_concurrency=128innodb_autoextend_increment=1000innodb_buffer_pool_instances=8innodb_concurrency_tickets=5000innodb_old_blocks_time=1000innodb_open_files=300innodb_stats_on_metadata=0innodb_file_per_table=1innodb_checksum_algorithm=0back_log=80flush_time=0join_buffer_size=128Mmax_allowed_packet=1024Mmax_connect_errors=2000open_files_limit=4161query_cache_type=0sort_buffer_size=32Mtable_definition_cache=1400binlog_row_event_max_size=8Ksync_master_info=10000sync_relay_log=10000sync_relay_log_info=10000#批量插入数据缓存大小，可以有效提高插入效率，默认为8Mbulk_insert_buffer_size = 64Minteractive_timeout = 120wait_timeout = 120log-bin-trust-function-creators=1sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESplugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"rpl_semi_sync_master_enabled = 1rpl_semi_sync_master_timeout = 10000rpl_semi_sync_master_trace_level = 32rpl_semi_sync_master_wait_for_slave_count = 1rpl_semi_sync_master_wait_no_slave = onrpl_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 --initializeroot@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.cnflog-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.cnflog-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
这样的步骤第二台同样执行操作
keepliaved需要的脚本：
- 下载keeplaived软件包并解压
[root@localhost ~]# tar -zxf keepalived-1.2.13.tar.gz -C /home/[root@localhost ~]# cd /home/[root@localhost home]# lskeepalived-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.confvrrp_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.完成
转载地址：http://ziqwi.baihongyu.com/