2个节点 (互为主从)
1、MySQLSrv1 192.168.1.41 (MySQL主从节点)
2、MySQLSrv2 192.168.1.42 (MySQL主从节点)
VIP: 192.168.1.47
软件版本说明：
1、操作系统 CentOS6.6_x86_64
2、MySQLServer 5.6.33
一、部署MySQL主从复制
1、用户及相关目录创建
--MySQLSrv1 节点
[root@mysqlsrv1 ~]# groupadd mysql
[root@mysqlsrv1 ~]# useradd mysql -g mysql
[root@mysqlsrv1 ~]# mkdir -p /opt/mysql
[root@mysqlsrv1 ~]# mkdir /mytmp
[root@mysqlsrv1 ~]# passwd mysql
--MySQLSrv2 节点
[root@mysqlsrv2 ~]# groupadd mysql
[root@mysqlsrv2 ~]# useradd mysql -g mysql
[root@mysqlsrv2 ~]# mkdir -p /opt/mysql
[root@mysqlsrv2 ~]# mkdir /mytmp
[root@mysqlsrv2 ~]# passwd mysql
安装依赖程序包 libaio
[root@mysqlsrv1 mytmp]# yum install libaio*
[root@mysqlsrv2 mytmp]# yum install libaio*
2、设置用户操作系统资源的限制
--以避免在启动mysql服务是会报：
2017-04-11 17:27:03 3175 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)
2017-04-11 17:27:03 3175 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000)
之类的警告信息
[root@mysqlsrv1 ~]# vi /etc/security/limits.conf
在文件的最后添加如下内容
mysql soft nproc 2047
mysql hard nproc 16384
mysql soft nofile 1024
mysql hard nofile 65535
3、解压上传的二进制安装包程序并复制到目标目录下
--MySQLSrv1 节点
[root@mysqlsrv1 mytmp]# tar -xzvf mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz
[root@mysqlsrv1 mytmp]# mv mysql-5.6.33-linux-glibc2.5-x86_64 /opt/mysql/3306
--MySQLSrv2 节点
[root@mysqlsrv2 mytmp]# tar -xzvf mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz
[root@mysqlsrv2 mytmp]# mv mysql-5.6.33-linux-glibc2.5-x86_64 /opt/mysql/3306
4、便于方便管理创建单独的日志文件存放目录
--MySQLSrv1 节点
[root@mysqlsrv1 3306]# mkdir logs
--MySQLSrv2 节点
[root@mysqlsrv2 3306]# mkdir logs
5、数据库初始化安装
--MySQLSrv1 节点
[root@mysqlsrv1 ~]# cd /opt/mysql/3306
[root@mysqlsrv1 3306]# scripts/mysql_install_db --user=mysql --basedir=/opt/mysql/3306 --datadir=/opt/mysql/3306/data
--MySQLSrv2 节点
[root@mysqlsrv2 ~]$ cd /opt/mysql/3306
[root@mysqlsrv2 3306]$ scripts/mysql_install_db --user=mysql --basedir=/opt/mysql/3306 --datadir=/opt/mysql/3306/data
6、目录权限修改
--MySQLSrv1 节点
[root@mysqlsrv1 ~]# chown -R mysql:mysql /opt/mysql
--MySQLSrv2 节点
[root@mysqlsrv2 ~]# chown -R mysql:mysql /opt/mysql
注：1、为了使用方便创建mysql用户环境变量，如下所示：
[mysql@mysqlsrv1 ~]$ vi ./.bash_profile
添加如下内容：
MYSQL3306_HOME=/opt/mysql/3306
PATH=$PATH:$MYSQL3306_HOME/bin
export MYSQL3306_HOME PATH
[mysql@mysqlsrv2 ~]$ vi ./.bash_profile
添加如下内容：
MYSQL3306_HOME=/opt/mysql/3306
PATH=$PATH:$MYSQL3306_HOME/bin
export MYSQL3306_HOME PATH
7、参数文件配置
--MySQLSrv1 节点
[mysql@mysqlsrv1 ~]$ vi /opt/mysql/3306/my.cnf
[client]
default_character_set = utf8
port = 3306
socket = /tmp/mysql3306.sock
[mysqld]
basedir = /opt/mysql/3306
datadir = /opt/mysql/3306/data
user = mysql
port = 3306
server_id = 13306
character_set_server = utf8
socket = /tmp/mysql3306.sock
pid-file = /tmp/mysql3306.pid
log-bin = /opt/mysql/3306/logs/bin_log
relay-log = /opt/mysql/3306/logs/relay_log
log-error = /opt/mysql/3306/logs/mysql_error.log
explicit_defaults_for_timestamp = true
expire_logs_days = 10
max_binlog_size = 100M
binlog-do-db = testdb #复制数据库名称
binlog-ignore-db = mysql
slave-skip-errors = all
auto-increment-increment = 2
auto-increment-offset = 1
--MySQLSrv2 节点
[mysql@mysqlsrv2 ~]$ vi /opt/mysql/3306/my.cnf
[client]
default_character_set = utf8
port = 3306
socket = /tmp/mysql3306.sock
[mysqld]
basedir = /opt/mysql/3306
datadir = /opt/mysql/3306/data
user = mysql
port = 3306
server_id = 23306
character_set_server = utf8
socket = /tmp/mysql3306.sock
pid-file = /tmp/mysql3306.pid
log-bin = /opt/mysql/3306/logs/bin_log
relay-log = /opt/mysql/3306/logs/relay_log
log-error = /opt/mysql/3306/logs/mysql_error.log
explicit_defaults_for_timestamp = true
expire_logs_days = 10
max_binlog_size = 100M
binlog-do-db = testdb #复制数据库名称
binlog-ignore-db = mysql
slave-skip-errors = all
auto-increment-increment = 2
auto-increment-offset = 2
8、清理配置参数文件
在Linux平台下，mysql程序默认会按照以下顺序扫描路径寻找配置文件
/etc/my.cnf
/etc/mysql/my.cnf
SYSCONFDIR/my.cnf #通过CMake源代码编译时指定的SYSCONFDIR的参数指定的路径
$MYSQL3306_HOME/my.cnf #MYSQL_HOME环境变量所在路径，即mysql安装路径(basedir)
~/.my.cnf #~表示到当更前用户根目录下寻找
通过命令(root用户只需查询) find / -iname my.cnf 查询配置文件的存在性，仅保留 MYSQL3306_HOME/my.cnf 配置文件。
注：使用如下命令可查看mysql实例的配置文件搜索顺序
[root@mysqlsrv1 bin]# mysql --help | grep '/my.cnf'
9、配置启动脚本
--MySQLSrv1 节点实例
[root@mysqlsrv1 3306]# cp support-files/mysql.server /etc/init.d/mysql3306
[root@mysqlsrv1 3306]# vi /etc/init.d/mysql3306
将如下内容
#basedir=...
#datadir=...
修改为：
basedir=/opt/mysql/3306
datadir=/opt/mysql/3306/data
--MySQLSrv2 节点实例
[root@mysqlsrv2 3306]# cp support-files/mysql.server /etc/init.d/mysql3306
[root@mysqlsrv2 3306]# vi /etc/init.d/mysql3306
将如下内容
#basedir=...
#datadir=...
修改为：
basedir=/opt/mysql/3306
datadir=/opt/mysql/3306/data
10、数据库服务启动
--MySQLSrv1 节点
[mysql@mysqlsrv1 ~]$ service mysql3306 start
--MySQLSrv2 节点
[mysql@mysqlsrv2 ~]$ service mysql3306 start
注：启动若报如下错误
Starting MySQL.2017-04-13T07:19:57.334037Z mysqld_safe The file /usr/local/mysql/bin/mysqld
does not exist or is not executable. Please cd to the mysql installation
directory and restart this script from there as follows:
./bin/mysqld_safe&
See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information
ERROR! The server quit without updating PID file (/tmp/mysql3306.pid).
解决方法：
[root@mysqlsrv1 ~]# mkdir -p /usr/local/mysql/bin
[root@mysqlsrv1 ~]# ln -s /opt/mysql/3306/bin/mysqld /usr/local/mysql/bin/mysqld
[root@mysqlsrv2 ~]# mkdir -p /usr/local/mysql/bin
[root@mysqlsrv2 ~]# ln -s /opt/mysql/3306/bin/mysqld /usr/local/mysql/bin/mysqld
11、登录数据库以添加远程登录帐号信息
[mysql@mysqlsrv1 ~]$ mysql -u root -p -P 3306 --socket=/tmp/mysql3306.sock
[mysql@mysqlsrv2 ~]$ mysql -u root -p -P 3306 --socket=/tmp/mysql3306.sock
注：使用mysql用户执行上述命令前，先配置相关环境变量值
/home/mysql/.bash_profile
添加如下内容：
MYSQL3306_HOME=/opt/mysql/3306
PATH=$PATH:$MYSQL3306_HOME/bin
export MYSQL3306_HOME PATH
注：上述登录数据库是需有参数 --socket ,否则会报出如下错误:
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock
--为了安全起见，先删除默认生成的用户相关信息在创建一个指定名称的数据库管理员帐号
mysql> use mysql;
mysql> delete from db;
mysql> delete from user;
mysql> grant all privileges on *.* to 'sysadmin'@'localhost' identified by 'mysql' with grant option;
mysql> grant all privileges on *.* to 'sysadmin'@'127.0.0.1' identified by 'mysql' with grant option;
mysql> flush privileges;
mysql> select host,user from user where user='sysadmin';
12、在两个节点创建复制专用帐号
mysql> grant replication slave on *.* to 'repl'@'%' identified by 'repl';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
13、查看 master 主机信息
[mysql@mysqlsrv1 ~]$ mysql -u sysadmin -p -P 3306 -S /tmp/mysql3306.sock
mysql> show master status \G
*************************** 1. row ***************************
File: bin_log.000002
Position: 120
Binlog_Do_DB: testdb
Binlog_Ignore_DB: mysql
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql>
[mysql@mysqlsrv2 ~]$ mysql -u sysadmin -p -P 3306 -S /tmp/mysql3306.sock
mysql> show master status \G
*************************** 1. row ***************************
File: bin_log.000002
Position: 120
Binlog_Do_DB: testdb
Binlog_Ignore_DB: mysql
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql>
14、配置两个节点之间互为主从角色，并启动复制
--MySQLSrv1 节点
[mysql@mysqlsrv1 ~]$ mysql -u sysadmin -p -P 3306 -S /tmp/mysql3306.sock
mysql> show slave status \G
Empty set (0.00 sec)
mysql> change master to
-> master_host='192.168.1.42',
-> master_port=3306,
-> master_user='repl',
-> master_password='repl',
-> master_log_file='bin_log.000002',
-> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.17 sec)
mysql>
--MySQLSrv2 节点
[mysql@mysqlsrv2 ~]$ mysql -u sysadmin -p -P 3306 -S /tmp/mysql3306.sock
mysql> show slave status \G
Empty set (0.00 sec)
mysql> change master to
-> master_host='192.168.1.41',
-> master_port=3306,
-> master_user='repl',
-> master_password='repl',
-> master_log_file='bin_log.000002',
-> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.17 sec)
mysql>
15、复制功能测试
--MySQLSrv1 节点
mysql> create database testdb;
mysql> use testdb;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| testdb |
+------------+
1 row in set (0.00 sec)
--MySQLSrv2 节点
mysql> use testdb;
mysql> create table t1(id int, name varchar(20));
Query OK, 0 rows affected (1.71 sec)
mysql> select * from t1;
Empty set (0.00 sec)
--MySQLSrv1 节点
mysql> insert into t1 (id,name) values(1,'重庆');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | 重庆 |
--MySQLSrv2 节点
mysql> select * from t1;
Empty set (0.01 sec)
mysql> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | 重庆 |
+------+----------+
1 row in set (0.00 sec)
mysql>
-------------- 两个节点之间的互为主从的复制功能正常 ---------------------------
二、部署keepalived服务
1、安装程序文件
[root@mysqlsrv1 ~]# yum install keepalived
[root@mysqlsrv2 ~]# yum install keepalived
注：
配置文件 /etc/keepalived/keepalived.conf
日志文件 /var/log/messages
2、修改配置文件内容
--MySQLSrv1 节点
global_defs {
notification_email {
yangchaoaccp@163.com
}
notification_email_from admin@163.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL_HA #标识，双主相同
}
vrrp_instance VI_1 {
state BACKUP #两台都设置BACKUP
interface eth0
virtual_router_id 51 #主备相同
priority 100 #优先级，backup设置90
advert_int 1
nopreempt #不主动抢占资源，只在master这台优先级高的设置，backup不设置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.47
}
}
virtual_server 192.168.1.41 3306 {
delay_loop 2
#lb_algo rr #LVS算法，用不到，我们就关闭了
#lb_kind DR #LVS模式，如果不关闭，备用服务器不能通过VIP连接主MySQL
persistence_timeout 50 #同一IP的连接60秒内被分配到同一台真实服务器
protocol TCP
real_server 192.168.1.41 3306 { #检测本地mysql，backup也要写检测本地mysql
weight 3
notify_down /usr/local/keepalived/mysql_check.sh #当mysq服down时，执行此脚本，杀死keepalived实现切换
TCP_CHECK {
connect_timeout 3 #连接超时
nb_get_retry 3 #重试次数
delay_before_retry 3 #重试间隔时间
}
}
--MySQLSrv2 节点
global_defs {
notification_email {
yangchaoaccp@163.com
}
notification_email_from admin@163.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL_HA #标识，双主相同
}
vrrp_instance VI_1 {
state BACKUP #两台都设置BACKUP
interface eth0
virtual_router_id 51 #主备相同
priority 90 #优先级，backup设置90
advert_int 1
#nopreempt #不主动抢占资源，只在master这台优先级高的设置，backup不设置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.47
}
}
virtual_server 192.168.1.42 3306 {
delay_loop 2
#lb_algo rr #LVS算法，用不到，我们就关闭了
#lb_kind DR #LVS模式，如果不关闭，备用服务器不能通过VIP连接主MySQL
persistence_timeout 50 #同一IP的连接60秒内被分配到同一台真实服务器
protocol TCP
real_server 192.168.1.42 3306 { #检测本地mysql，backup也要写检测本地mysql
weight 3
notify_down /usr/local/keepalived/mysql_check.sh #当mysq服down时，执行此脚本，杀死keepalived实现切换
TCP_CHECK {
connect_timeout 3 #连接超时
nb_get_retry 3 #重试次数
delay_before_retry 3 #重试间隔时间
}
}
--mysql_check.sh 文件(两个节点相同)
#!/bin/bash
pkill keepalived
3、创建测试用户：
MySQLSrv1 节点
mysql -u sysadmin -p -P 3306 -S /tmp/mysql3306.sock
mysql> grant all privileges on *.* to 'yangchao'@'%' identified by 'yangchao';
mysql> flush privileges;
4、keepalived 测试
启动 MySQLSrv1 节点 keepalived 服务
[root@mysqlsrv1 ~]# service keepalived start
启动 MySQLSrv2 节点 keepalived 服务
[root@mysqlsrv2 ~]# service keepalived start
在 MySQLSrv1 节点查看VIP
在 MySQLSrv2 节点查看VIP
由上测试可知, keepalived 成功在节点 MySQLSrv1 上绑定VIP 192.168.1.47
在 MySQLSrv1 节点重新启动 keepalived 服务：
由上测试可知，keepalived 服务绑定的IP地址成功漂移到 MySQLSrv2 节点。
5、MySQL 服务测试
--在 MySQLSrv1 节点登录
由上测试可知，虽然在 MySQLSrv1 节点登录，但登录到的节点是VIP实际绑定的 MySQLSrv2 节点。
插入数据：
在 MySQLSrv2 节点上停止 mysql 服务
停止mysql服务后，自动停止了所在节点的keepalived服务。
在 MySQLSrv1 节点查看
keepalived 服务成功漂移到 MySQLSrv1 节点。
