Mysql双机热备搭建
来源:互联网 发布:如何转行软件测试 编辑:程序博客网 时间:2024/05/01 16:50
一、CentOS 6.5下源码编译安装MySQL 5.7.9
1.安装依赖包
# yum -y install gcc-c++ ncurses-devel cmake make perl gcc autoconf automake zlib libxml libgcrypt libtool bison
2. 清理环境
检查boost版本:
# rpm -qa boost*
卸载boost-*等库:
# yum -y remove boost-*
3. mysql源码包下载(不详述)
4.创建mysql用户,组及目录
# groupadd mysql
# mkdir /home/mysql
# mkdir /home/mysql/data
# useradd -r -g mysql mysql
5.注意事项
从MySQL 5.7.5开始Boost库是必需的,下载Boost库,在解压后复制到/usr/local/boost目录下,然后重新cmake并在后面的选项中加上选项-DWITH_BOOST=/usr/local/boost
(下载:http://sourceforge.net/projects/boost/files/boost/)
需求boost1.57.0
wget -c http://liquidtelecom.dl.sourceforge.net/project/boost/boost/1.57.0/boost_1_57_0.tar.gz
6. 新版本的mysq用cmake编译安装
解压mysql源码包mysql-5.7.9.tar
# tar -xzvf mysql-5.7.9.tar
以下操作在解压后的源码包的根目录执行
cmake编译
# cmake-DCMAKE_INSTALL_PREFIX=/home/mysql -DMYSQL_DATADIR=/home/mysql/data-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci-DMYSQL_TCP_PORT=3306 -DMYSQL_USER=mysql -DWITH_MYISAM_STORAGE_ENGINE=1-DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1-DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1-DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost
常用参数:
CMAKE_INSTALL_PREFIX:指定MySQL程序的安装目录,默认/usr/local/mysql
DEFAULT_CHARSET:指定服务器默认字符集,默认latin1
DEFAULT_COLLATION:指定服务器默认的校对规则,默认latin1_general_ci
ENABLED_LOCAL_INFILE:指定是否允许本地执行LOADDATA INFILE,默认OFF
WITH_COMMENT:指定编译备注信息
WITH_xxx_STORAGE_ENGINE:指定静态编译到mysql的存储引擎,MyISAM,MERGE,MEMBER以及CSV四种引擎默认即被编译至服务器,不需要特别指定。
WITHOUT_xxx_STORAGE_ENGINE:指定不编译的存储引擎
SYSCONFDIR:初始化参数文件目录
MYSQL_DATADIR:数据文件目录
MYSQL_TCP_PORT:服务端口号,默认3306
MYSQL_UNIX_ADDR:socket文件路径,默认/tmp/mysql.sock
编译安装
# make && makeinstall
出错后重新运行配置,需要删除CMakeCache.txt文件
# make clean
# rm -f CMakeCache.txt
7. 设置权限并初始化MySQL系统授权表
设置权限
# cd /home/mysql
# chown -R mysql.mysql mysql/
以root初始化操作时要加–user=mysql参数,生成一个随机密码(注意保存登录时用)
# cd /home/mysql
# bin/mysqld --initialize --user=mysql --basedir=/home/mysql--datadir=/home/mysql/data
8. 创建配置文件
将默认生成的my.cnf备份
# mv /etc/my.cnf/etc/my.cnf.bak
进入mysql的安装目录支持文件目录
# cd/home/mysql/support-files
拷贝配置文件模板为新的mysql配置文件,
# cp my-default.cnf/etc/my.cnf
可按需修改新的配置文件选项,不修改配置选项,mysql则按默认配置参数运行.
如下是我修改配置文件/etc/my.cnf,用于设置编码为utf8以防乱码
[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
[client]
default-character-set=utf8
9. 配置mysql服务开机自动启动
拷贝启动文件到/etc/init.d/下并重命令为mysqld
# cp/home/mysql/support-files/mysql.server /etc/init.d/mysqld
增加执行权限
# chmod 755/etc/init.d/mysqld
检查自启动项列表中没有mysqld这个,如果没有就添加mysqld:
# chkconfig --listmysqld
# chkconfig --add mysqld
用这个命令设置开机启动:
# chkconfig mysqld on
Vim .bashrc
最后插入两行如下
PATH=/home/mysql/bin:/home/mysql/lib:$PATH
export PATH
10. mysql服务的启动/重启/停止
启动mysql服务
# service mysqld start
重启mysql服务
# service mysqld restart
停止mysql服务
# service mysqld stop
11. 访问mysql数据库
连接mysql,输入初始化生成的随机密码
# mysql -uroot -p
修改root新密码如 123456
mysql> alter user'root'@'localhost' identified by '123456';
mysql> quit;
mysql> exit;(与上等效,都是退出mysql连接)
使用新密码重新连接mysql
# mysql-uroot -p
授权所有用户可以登录grant allprivileges on *.* to root@'%' identified by '123456' with grant option;
mysql> flush privileges;
二、主主互为同步设置
2.1 添加mysql主机器配置文件my.cnf如下
log-bin=mysql-bin
binlog_format=mixed
server-id=1
relay-log=mysql-relay-bin
log-slave-updates=ON
read-only=0
auto-increment-increment=2
auto-increment-offset=1
备机器上修改
server-id=1
auto-increment-offset=1
这两个都改成 2
Mysql调优参数如下
max_connections=1500
max_connect_errors=100000
innodb_buffer_pool_size=800M
innodb_thread_concurrency=128
binlog_cache_size=64M
max_binlog_cache_size=4096M
max_binlog_size=512M
innodb_log_file_size =256M
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
max_heap_table_size=64M
interactive_timeout=3600
wait_timeout=3600
lower_case_table_names=1
innodb_lock_wait_timeout=100
back_log=500
增大同步线程为100个
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=100
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
2.2 在主服务器上为从服务器分配一个账号,就像一把钥匙,从服务器拿着这个钥匙,才能到主服务器上来共享主服务器的日志文件。
mysql> create user admin identified by'admin';
mysql> grant all on *.* to admin@'%'identified by 'admin';
进入主服务器的mysql界面,
命令: # mysql -u root -p 123456 //我这里mysql账号是root,密码是123456
在mysql操作界面下,输入下面一行命令:
GRANT replication slave ON *.* TO 'admin'@'主机器ip' IDENTIFIED BY 'admin';
CHANGE MASTER TO
MASTER_HOST='192.168.10.26',
MASTER_USER='sync',
MASTER_PASSWORD='sync',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=1146;
2.2 的步骤在备机上同样操作
参数解释:MASTER_HOST : 设置要连接的主服务器的ip地址
MASTER_USER : 设置要连接的主服务器的用户名
MASTER_PASSWORD : 设置要连接的主服务器的密码
MASTER_LOG_FILE : 设置要连接的主服务器的bin日志的日志名称,即第3步得到的信息
MASTER_LOG_POS : 设置要连接的主服务器的bin日志的记录位置,即第3步得到的信息,(这里注意,最后一项不需要加引号。否则配置失败)
先在从服务器配置完成,启动从服务器:
命令: start slave;
5.查看是否配置成功:
命令: show slave status\G;
上面两项均为yes,说明配置成功,否则,请重复前面的步骤。
三、安装keepalived
1、yum install -ylibnl libnl-devel libnfnetlink-devel openssl-devel
2、cd /usr/local/src/
wgethttp://www.keepalived.org/software/keepalived-1.2.21.tar.gz
3、tar -zxvfkeepalived-1.2.21.tar.gz
cd keepalived-1.2.21
./configure --prefix=/usr/local/keepalived
make && make install
mkdir/etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf/etc/keepalived/
cp/usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
ln -s /usr/local/keepalived/sbin/keepalived/sbin/
cp/usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
4、得到如下配置
[root@master ~]# find / -name keepalived
/etc/sysconfig/keepalived
/etc/keepalived
/etc/rc.d/init.d/keepalived
/sbin/keepalived
/usr/local/keepalived
/usr/local/keepalived/etc/sysconfig/keepalived
/usr/local/keepalived/etc/keepalived
/usr/local/keepalived/etc/rc.d/init.d/keepalived
/usr/local/keepalived/sbin/keepalived
/usr/local/src/keepalived-1.2.21/keepalived
/usr/local/src/keepalived-1.2.21/keepalived/etc/keepalived
/usr/local/src/keepalived-1.2.21/bin/keepalived
/var/lock/subsys/keepalived
[root@master ~]# find / -name keepalived.conf
/etc/keepalived/keepalived.conf
/usr/local/keepalived/etc/keepalived/keepalived.conf
/usr/local/src/keepalived-1.2.21/keepalived/etc/keepalived/keepalived.conf
5、配置配置文件如下:
[root@master ~]# cat/etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51 #id和备机都要统一
priority 100 #备机设置为90要小于100
advert_int 1
nopreempt #不抢占模式
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
10.7.8.72/24 dev eth0 #设置vip
}
}
virtual_server 10.7.8.72 3306 {
delay_loop 2
lb_algo wrr
lb_kinf DR
persistence_timeout 50
protocol TCP
real_server 10.7.8.70 3306 { #主机器ip地址和端口
weight 1
notify_down /etc/keepalived/mysql.sh
TCP_CHECK {
connect_timeout 10
bingto 10.7.8.72
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
备master配置如下:
[root@slave ~]# cat/etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
10.7.8.72/24 dev eth0
}
}
virtual_server 10.7.8.72 3306 {
delay_loop 2
lb_algo wrr
lb_kinf DR
persistence_timeout 50
protocol TCP
real_server 10.7.8.71 3306 {
weight 1
notify_down /etc/keepalived/mysql.sh
TCP_CHECK {
connect_timeout 10
bingto 10.7.8.72
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
6、keepalived检测脚本,当其中一台MySQL服务出现故障down掉时,实现自动切换到正常的MySQL服务器继续提供服务
[root@master ~]# cat/etc/keepalived/mysql.sh
#!/bin/bash
/etc/init.d/keepalived stop
7、授权VIP的root用户权限
授权远程主机可以通过VIP登录MySQL,并测试数据复制功能
grant all on *.*to root@'master2ip' identified by '123456';
mysql> flush privileges;
备机器同样操作一遍
8、测试keepalived高可用功能
远程主机登录通过VIP192.168.1.208登录MySQL,查看MySQL连接状态
mysql> show variables like 'hostname';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| hostname | master |
+---------------+--------+
1 row in set (0.00 sec)
故障测试,停止master的MySQL服务,再次查看是否转移至slave服务器上
- Mysql双机热备搭建
- Mysql双机热备
- mysql 双机热备
- mysql双机热备
- MYSQL双机热备
- mysql 双机热备
- mysql双机热备
- MySQL 双机热备
- mysql双机热备。
- mysql双机热备
- mysql双机热备
- MySQL双机热备
- MYSQL双机热备
- MYSQL双机热备
- mysql 双机热备
- 搭建mysql主从数据库实现双机热备架构
- MySql数据库双机热备
- linux mysql双机热备
- C# DataTable 详解
- 认识Android
- 史上最简单的SpringCloud教程 | 第四篇:断路器(Hystrix)
- java 的 System.getenv() 和 System.getProperty()
- JAVAEE 知识点复习
- Mysql双机热备搭建
- 《Java编程技巧1001条》第374条 小心使用依赖日期的函数,
- MSSQL2008展开出现“值不能为空,参数名:viewInfo”
- docker初体验
- 鉴别一个人是否 js 入门的标准竟然是?!
- 10张图看懂瞎忙和高效的区别
- Python中密码加密存储
- Python学习 我帮你介绍个对象吧!
- 二周四次课