环境
Centos 7.4
172.16.91.215 主
172.16.91.216 从
安装包 版本
mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
一 两台机器上,都安装上mysql
把 mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz 上传 到 /usr/local/src/
cd /usr/local/src/
tar -zxvf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
mkdir -p /usr/local/mysql/
mv mysql-5.7.22-linux-glibc2.12-x86_64 /usr/local/mysql/mysql-5.7.22
创建 mysql 用户组 和 用户
cd /usr/local/src/
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
chown -R mysql:mysql mysql
安装mysql
cd /usr/local/mysql/mysql-5.7.22
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/mysql-5.7.22/ --datadir=/usr/local/mysql/mysql-5.7.22/data --lc_messages_dir=/usr/local/mysql/mysql-5.7.22/share --lc_messages=en_US
注意,要记录最后生成的登录密码
修改配置
cd /usr/local/mysql/mysql-5.7.22
bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/mysql-5.7.22/data
vi /etc/my.cnf
basedir=/usr/local/mysql/mysql-5.7.22
datadir=/usr/local/mysql/mysql-5.7.22/data/
配置环境变量
vim ~/.bash_profile
将mysql的bin目录配置到环境变量中
PATH=$PATH:$HOME/bin:/usr/local/mysql/mysql-5.7.22/bin
export PATH
source ~/.bash_profile
修改密码
mysql -uroot –p
set password=password("新密码");
配置远程访问
use mysql
update user set host='%' where user='root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '......' WITH GRANT OPTION;(或者是:grant all privileges on *.* to root@'%' identified by "你的新密码";)
FLUSH PRIVILEGES;
安全启动
./mysqld_safe --user=mysql &
安全关闭
./mysqladmin -uroot -p shutdown
二 两台 mysql 配置双主
创建主从同步 replication用户
172.16.91.215
grant replication slave on *.* to 'replication'@'172.16.91.216' identified by 'replication';
flush privileges;
172.16.91.216
grant replication slave on *.* to 'replication'@'172.16.91.215' identified by
'replication';
flush privileges;
172.16.91.215
[mysqld]
datadir=/usr/local/mysql/mysql-5.7.22
datadir=/usr/local/mysql/mysql-5.7.22/data/
server-id = 215
log-bin = mysql-binlog
binlog-ignore-db = mysql
binlog-ignore-db=information_schema
binlog_do_db = guangzhou
# 主主需要加的部分
replicate-do-db = guangzhou
replicate-ignore-db=mysql,information_schema
log-slave-updates
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2
!includedir /etc/my.cnf.d
172.16.91.216
[mysqld]
basedir = /usr/local/mysql/mysql-5.7.22
datadir = /usr/local/mysql/mysql-5.7.22/data/
server-id = 216
replicate-do-db = guangzhou
#主-主需加入的部分
auto_increment_offset = 2
auto_increment_increment = 2
log-slave-updates
sync_binlog = 1
log_bin = mysql-binlog
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog_do_db = guangzhou
replicate-ignore-db = mysql,information_schema
!includedir /etc/my.cnf.d
重启两台 mysql 服务
进入 mysql cli
执行命令 show master status\G
分别记录 主的 binlog 位置,在216的角度上 215 为 主,在 215的角度上,216为主
Position: 682
设置 同步关系(两个主都要 设置)
stop slave;
change master to
master_host='peer',
master_user='user',
master_password='password',
master_log_file='mysql-bin.000004',
master_log_pos=682;
其中 master_log_file 和 master_log_pos 要登录 到 对方机器
执行 show master status\G; 去查看
start slave
使用 show slave status\G; 验证
如果显示Slave_IO_State: Waiting for master to send event,就说明同步成功
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.91.216 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-binlog.000007 Read_Master_Log_Pos: 154 Relay_Log_File: 215-centos7-relay-bin.000016 Relay_Log_Pos: 373 Relay_Master_Log_File: mysql-binlog.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: guangzhou |
常见 错误
当 show slave status\G; 不成功的时候,可能是 replication 账号权限问题,
解决问题 把 replication 账号设置为 所有主机都能访问
两台机器 都要 安装 keepalived
- 安装 所需要 的依赖包
yum install -y gcc;
yum install -y openssl-devel;
yum install -y libnl;
yum install -y libnl-devel;
yum install -y libnfnetlink-devel;
- 解压,并 放到 /usr/local /, 并 重命名为 keepalived
- ./configure --prefix=/usr/local/keepalived/ --sysconf /etc
4
cd /usr/local/keepalived/
make && make install
ln -s sbin/keepalived /sbin/
cd keepalived
cp etc/init.d/keepalived /etc/init.d/
chkconfig --add keepalived
chkconfig keepalived on
service keepalived start
215 机器上 的 keepalived 配置
! Configuration File for keepalived
global_defs { router_id LVS_DEVEL }
vrrp_instance VI_1 { state MASTER interface ens160 virtual_router_id 51 priority 100 advert_int 1
track_script { chk_nginx_service }
authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 172.16.91.101 } }
virtual_server 172.16.91.101 3306 { delay_loop 2 lb_algo rr lb_kind NAT persistence_timeout 50 protocol TCP
real_server 172.16.91.215 3306 { notify_down /usr/local/keepalived/bin/mysql.sh weight 1 TCP_CHECK { connect_timeout 10 bingto 172.16.91.101 nb_get_retry 3 delay_before_retry 3 connect_port 3306 }
} }
}
|
216 机器上的 keepalived 配置
! Configuration File for keepalived
global_defs { router_id LVS_DEVEL }
vrrp_instance VI_1 { state MASTER interface ens160 virtual_router_id 51 priority 100 advert_int 1
track_script { chk_nginx_service }
authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 172.16.91.101 } }
virtual_server 172.16.91.101 3306 { delay_loop 2 lb_algo rr lb_kind NAT persistence_timeout 50 protocol TCP
real_server 172.16.91.216 3306 { notify_down /usr/local/keepalived/bin/mysql.sh weight 1 TCP_CHECK { connect_timeout 10 bingto 172.16.91.101 nb_get_retry 3 delay_before_retry 3 connect_port 3306 }
} }
} |
执行脚本
#!/bin/bash pkill keepalived |