高可用-mysql安装,双主模式+keepalived
mysql安装
1.添加用户
groupadd mysql
useradd -r -g mysql mysql
2.路径授权
切到mysql目录 cd /usr/local/mysql
修改当前目录拥有者为mysql用户 chown -R mysql:mysql ./
3.初始化
安装数据库 bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
（看到有的报这个错bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory解决方法：yum install -y libaio //安装后在初始化就OK了）
4.创建RSA
执行以下命令创建RSA private key bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data
5.修改权限
修改当前目录拥有者为mysql用户 chown -R mysql:mysql ./
修改当前data目录拥有者为mysql用户 chown -R mysql:mysql data
6.配置my.cnf
vim /etc/my.cnf （直接把下面内容复制上就行）
[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
#不区分大小写 (sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 这个简单来说就是sql语句是否严格)
lower_case_table_names = 1
log-error=/var/log/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid
7.开机启动
添加开机启动 cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
vi /etc/init.d/mysqld
加入开机起动 chkconfig --add mysqld
8、启动mysql service mysqld start
9、登录修改密码 mysql -uroot -p 上面初始化时的密码
如果出现错误 需要添加软连接 ln -s /usr/local/mysql/bin/mysql /usr/bin
10.修改初始化密码
mysql> alter user 'root'@'localhost' identified by 'root';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> set password='suntek@1234';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'root'@'%' identified by 'suntek@1234';
Query OK, 0 rows affected, 1 warning (0.00 sec)
11.打开防火墙
vi /etc/sysconfig/iptables
添加一行
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306-j ACCEPT
service iptables restart
二.双主配置
172.16.70.6
172.16.70.7
1.配置/etc/my.cnf，配置完后一定要先重启
172.16.70.6机器
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
port=3306
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
lower_case_table_names = 1
server-id = 1
log-bin = mysql-bin
binlog_format = mixed
relay_log = relay-bin
auto_increment_increment=2
auto_increment_offset=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
172.16.70.7机器
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
port=3306
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
lower_case_table_names = 1
server-id = 2
log-bin = mysql-bin
binlog_format = mixed
relay_log = relay-bin
auto_increment_increment=2
auto_increment_offset=2
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
黄色背景部分server-id和 auto_increment_offset不能相同，其它必须相同
部分配置项解释如下：
binlog_format= mixed：指定mysql的binlog日志的格式，mixed是混合模式。
relay-log：开启中继日志功能
relay-log-index：中继日志清单
auto-increment-increment= 2：表示自增长字段每次递增的量，其默认值是1。它的值应设为整个结构中服务器的总数，本案例用到两台服务器，所以值设为2。
auto-increment-offset= 2：用来设定数据库中自动增长的起点(即初始值)，因为这两能服务器都设定了一次自动增长值2，所以它们的起点必须得不同，这样才能避免两台服务器数据同步时出现主键冲突。
注：另外还可以在my.cnf配置文件中，添加“binlog_do_db=数据库名”配置项（可以添加多个）来指定要同步的数据库。如果配置了这个配置项，如果没添加在该配置项后面的数据库，则binlog不记录它的事件。
2.在msql1(172.16.70.6)创建一个用户给mysql2（172.16.70.7）连接使用的，用户名为suntek,密码为suntek@1234
grant replication slave on *.* to 'suntek'@'172.16.70.7' identified by 'suntek@1234';
3.msql1(172.16.70.6)查看binlog信息
show master status;
4.mysql2（172.16.70.7）将mysql1设为自已的主服务器并开启slave功能：
change master to master_host='172.16.70.6',master_user='suntek',master_password='suntek@1234',master_log_file='mysql-bin.000001',master_log_pos=154;
start slave;
5.msql2(172.16.70.7)的状态：
show slave status \G;
（\G格式化输出）
mysql1与mysql2反过来2~5步骤
6.在msql2(172.16.70.7)创建一个用户给mysql1（172.16.70.6）连接使用的，用户名为suntek,密码为suntek@1234
grant replication slave on *.* to 'suntek'@'172.16.70.6' identified by 'suntek@1234';
msql2(172.16.70.7)查看binlog信息
show master status;
7.mysql1（172.16.70.6）将mysql1设为自已的主服务器并开启slave功能：
change master to master_host='172.16.70.7',master_user='suntek',master_password='suntek@1234',master_log_file='mysql-bin.000001',master_log_pos=454;
start slave;
show slave status;
三、keepalive
vip：172.16.70.200
ip1 : 172.16.70.6、ip2 : 172.16.70.7
1.下载keepalive安装包http://www.keepalived.org/download.html
2.安装依赖
yum install -y libnl-devel libnl3-devel libnfnetlink-devel openssl-devel
3.解压
tar zxvf keepalived-2.0.10.tar.gz
4.进入解压后的目录，进行configure
cd keepalived-2.0.10
./configure --prefix=/usr/local/keepalived
5.进行编译安装
make && make install
6.拷贝执行文件
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
7.将init.d文件拷贝到etc下,加入开机启动项
cp /usr/local/keepalived-2.0.10/keepalived/etc/init.d/keepalived /etc/init.d/keepalived
8.将keepalived文件拷贝到etc下，加入网卡配置
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
9.将keepalived配置文件拷贝到etc下
mkdir -p /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf
10.添加可执行权限
chmod +x /etc/init.d/keepalived
11.加入开机启动
chkconfig --add keepalived #添加时必须保证/etc/init.d/keepalived存在
chkconfig keepalived on
#添加完可查询系统服务是否存在：
chkconfig --list
12.编辑检测脚本msyql.sh，放到/etc/keepalived/mysql.sh，并授权
这个的作用是当检测到mysql停止时，也kill了keepalived进程
#!/bin/sh
pkill keepalived
授权
chmod +x /etc/keepalived/mysql.sh
13.编辑/etc/keepalived/keepalived.cof文件
原来两台机器原来网络172.16.70.6和172.16.70.7
配置文件
四个地方不同router_id ，priority，nopreempt（prority大的要配置，另一个不用配置），real_server
172.16.70.6的/etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id mysql70-6
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.16.70.199
}
}
virtual_server 172.16.70.199 3306 {
delay_loop 2
lb_algo rr
lb_kind DR
persistence_timeout 60
protocol TCP
real_server 172.16.70.6 3306 {
weight 3
notify_down /etc/keepalived/mysql.sh
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
172.16.70.7的/etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id mysql70-7
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 50
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.16.70.199
}
}
virtual_server 172.16.70.199 3306 {
delay_loop 2
lb_algo rr
lb_kind DR
persistence_timeout 60
protocol TCP
real_server 172.16.70.7 3306 {
weight 3
notify_down /etc/keepalived/mysql.sh
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
14.启动、停止、重启keepalived服务
service keepalived start
service keepalived stop
service keepalived restart
15.查看服务
ps -ef |grep keepalived
16.查看网络结构
ip addr
17.别的机器ping 一下虚拟IP172.16.70.199
18.停止172.16.70.6机器数据库mysql测试
