mysql主主，生产
2020-10-30 本文已影响0人
小小的小帅
- Linux中关闭SELinux的方法
1、临时关闭：输入命令setenforce 0，重启系统后还会开启。
2、永久关闭：输入命令vi /etc/selinux/config，将SELINUX=enforcing改为SELINUX=disabled，然后保存退出
1.安装配置
添加用户：adduser mysql
设定密码：passwd mysql
创建目录：(注意目录、用户)
cd /usr/local/
mkdir mysql
添加权限：
chown -R mysql /usr/local/mysql
解压
tar -xvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql/mysql-5.7
cd /usr/local/mysql
mkdir data
配置my.cnf: (root权限)
vim /etc/my.cnf
- 主节点配置
[client]
socket=/usr/local/mysql/mysql.sock
[mysqld]
# 每个节点的server-id一定要设置不同 节点1-75，节点2-163
server-id = 78
port = 3306
user = mysql
bind_address = 0.0.0.0
skip_name_resolve = 1
basedir=/usr/local/mysql/mysql-5.7
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/mysql.sock
log-error=/usr/local/mysql/mysql.err
pid-file=/usr/local/mysql/mysql.pid
explicit_defaults_for_timestamp=true
character-set-server=utf8
lower_case_table_names=1
max_connections=1000
max_connect_errors=10000
#根据服务器内存调整
innodb_buffer_pool_size=16G
back_log=900
open_files_limit=102400
thread_cache_size=128
table_open_cache=1024
innodb_buffer_pool_instances=4
innodb_flush_method=O_DIRECT
innodb_log_file_size=1073741824
#######replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
# MySQL复制是基于binlog日志的
log_bin = master-bin
sync_binlog = 1
log_slave_updates
binlog_format = row
relay_log = relay.log
relay_log_recovery = 1
slave_skip_errors = ddl_exist_errors
######semi sync replication settings########
# 设置插件目录路径
plugin_dir=/usr/local/mysql/mysql-5.7/lib/plugin
# 加载插件
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
# 开启master semi sync replication
rpl_semi_sync_master_enabled = 1
# 开启slave semi sync replication
rpl_semi_sync_slave_enabled = 1
# 等待5秒无ack应答自动切换为异步模式
rpl_semi_sync_master_timeout = 5000
# 开启lossless replication
rpl_semi_sync_master_wait_point= AFTER_SYNC
# 至少有1个slave接收到日志
rpl_semi_sync_master_wait_for_slave_count = 1
innodb_buffer_pool_size根据情况设置，server-id切记一点要修改
[root@localhost mysql]# find / -name "semisync_master.so"
find: ‘/run/user/1000/gvfs’: Permission denied
/usr/local/mysql/mysql-5.7/lib/plugin/debug/semisync_master.so
/usr/local/mysql/mysql-5.7/lib/plugin/semisync_master.so
[root@localhost mysql]# find / -name "semisync_slave.so"
find: ‘/run/user/1000/gvfs’: Permission denied
/usr/local/mysql/mysql-5.7/lib/plugin/debug/semisync_slave.so
/usr/local/mysql/mysql-5.7/lib/plugin/semisync_slave.so
初始化mysql (注意目录、用户)
cd /usr/local/mysql/mysql-5.7/bin
./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/mysql-5.7/ --datadir=/usr/local/mysql/data/ --user=mysql --initialize
提示报错的话
yum install libaio-devel.x86_64
yum -y install numactl
启动
cp /usr/local/mysql/mysql-5.7/support-files/mysql.server /etc/init.d/mysql
启动，mysql启动需要使用root权限，否则配置文件my.cnf会读取不到
service mysql start
登录
cat /usr/local/mysql/mysql.err
cd /usr/local/mysql/mysql-5.7/bin
./mysql -u root -p
#修改密码
SET PASSWORD = PASSWORD('handhand123');
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
FLUSH PRIVILEGES;
修改远程可访问
use mysql; #访问mysql库
update user set host = '%' where user = 'root'; #使root能再任何host访问
FLUSH PRIVILEGES;
创建hips账号和数据库
create user 'hips'@'%' identified by 'handhand123';
GRANT all on hips.* TO 'hips'@'%' ;
配置主主同步
创建同步账号
create user 'slave'@'%' identified by 'HandSlave#2020';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' ;
记录两边binlog位置，执行
show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 | 1901 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在192.168.100.181上执行
change master to master_host='192.168.100.182', master_port=3306, master_user='slave', master_password='HandSlave#2020', master_log_file='master-bin.000003', master_log_pos=1901;
在192.168.100.182上执行
change master to master_host='192.168.100.181', master_port=3306, master_user='slave', master_password='HandSlave#2020', master_log_file='master-bin.000004', master_log_pos=154;
两台Mysql开户同步
start slave;
两个mysql查看同步状态
show slave status;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
keepalived 高可用
- yum安装：
yum install keepalived -y
vim /etc/keepalived/keepalived.conf
182
global_defs {
router_id hips01
script_user root
}
vrrp_script chkmysqld {
script "/etc/keepalived/checkmysqld.sh"
interval 5
fall 3
rise 1
}
vrrp_instance VI_1 {
#MASTER或者BACKUP,如果MASTER初始的权重没有BACKUP高，会被主动切换到BACKUP；
state MASTER
#优先级高的设置nopreempt解决异常恢复后再次抢占的问题。
nopreempt
#采用的接口网卡，注意后续所有的虚拟IP都需要在网卡支持的网段范围内，
#比如centos默认是ens33，树莓派是eth0，用ifconfig查看网卡网段，如果网卡只有192.168的内网网段那么虚拟IP也必须要用这个网段
interface eno16777736
#设置VRID标记，不同服务集群内不能重复
virtual_router_id 71
#权重，越高越容易被选为主
priority 101
authentication {
auth_type PASS
auth_pass 2222
}
track_script {
chkmysqld
}
virtual_ipaddress {
192.168.100.183/16
}
}
182
global_defs {
router_id hips02
script_user root
}
vrrp_script chkmysqld {
script "/etc/keepalived/checkmysqld.sh"
interval 5
fall 3
rise 1
}
vrrp_instance VI_1 {
state BACKUP
interface eno16777736
virtual_router_id 71
priority 100
mcast_src_ip 192.168.100.182
advert_int 1
authentication {
auth_type PASS
auth_pass 2222
}
track_script {
chkmysqld
}
virtual_ipaddress {
192.168.100.83
}
}
181
global_defs {
router_id hips01
script_user root
}
vrrp_script chkmysqld {
script "/etc/keepalived/checkmysqld.sh"
interval 5
fall 3
rise 1
}
vrrp_instance VI_1 {
state MASTER
interface eno16777736
virtual_router_id 71
priority 101
mcast_src_ip 192.168.100.181
advert_int 1
authentication {
auth_type PASS
auth_pass 2222
}
track_script {
chkmysqld
}
virtual_ipaddress {
192.168.100.83
}
}
- mysql检查脚本
vim /etc/keepalived/checkmysqld.sh
#!/bin/bash
mysqlhost=localhost
user=slave
password='HandSlave#2020'
RESULT=$(/usr/local/mysql/mysql-5.7/bin/mysql -h $mysqlhost -u $user -p$password -N -s -e "select 1")
echo $RESULT
if [ "${RESULT}" == "1" ]; then
exit 0
else
service mysql start
exit 1
fi
- 启动keepalived
chmod a+x /etc/keepalived/checkmysqld.sh
systemctl enable keepalived
systemctl start keepalived
查看VIP
ip a
测试使用VIP连接数据库
mysql -h192.168.100.83 -uroot -p
keepalived主、从：
image.png
keepalived的日志文件路径：
tail -f /var/log/messages
Nov 1 03:26:54 ip-192-168-100-181 Keepalived_vrrp[14150]: Sending gratuitous ARP on ens5 for 192.168.100.83
