mysql 架构安装部署及操作
目录
搭建流程
1、安装mysql5726
| 实现 功能 |
10.0.0.65 | 10.0.0.66 | 10.0.0.67 |
|---|---|---|---|
| name | 65 | 66 | 67 |
| host | 10.0.0.65 65 10.0.0.66 66 10.0.0.67 67 |
10.0.0.65 65 10.0.0.66 66 10.0.0.67 67 |
10.0.0.65 65 10.0.0.66 66 10.0.0.67 67 |
| gtid复制 | 主库 | 从库 | 从库 |
| 高可用 | vip | - | mha |
| 邮箱报警 | 读写分离 | - |
65\66\67
curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
curl -o /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum install -y libaio-devel
mkdir -p /server/tools
cd /server/tools/
mkdir /application
# wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.26-linux-glibc2.12-x86_64 /application/mysql
yum remove mariadb-libs-5.5.60-1.el7_5.x86_64 -y
rpm -qa |grep mariadb
useradd -s /sbin/nologin mysql
#设置环境变量
echo "export PATH=/application/mysql/bin:$PATH" >>/etc/profile
source /etc/profile
mysql -V
#创建数据路径并授权
#1. 添加一块新磁盘模拟数据盘
#2. 格式化并挂载磁盘
# mkfs.xfs /dev/sdb
# mkdir /data
# blkid
# vim /etc/fstab
# UUID="b21ec3e0-e251-4ded-bc12-2d940f938dd5" /data xfs defaults 0 0
# mount -a
# df -h
chown -R mysql.mysql /application/*
#初始化数据（创建系统数据）
# 5.6 版本 初始化命令 /application/mysql/scripts/mysql_install_db
# 5.7 版本
mkdir /data/mysql/data -p
chown -R mysql.mysql /data
mysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data #自动创建密码
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data #无密码
cat >/etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
mkdir /data/mysql/data/binlog
chown -R mysql.mysql /data
65
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=65
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/mysql/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=65 [\\\d]>
EOF
systemctl restart mysqld
systemctl status mysqld
66
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=66
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/mysql/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=66 [\\\d]>
EOF
systemctl restart mysqld
systemctl status mysqld
67
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=67
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/mysql/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=67 [\\\d]>
EOF
systemctl restart mysqld
systemctl status mysqld
gtid主从复制
65
mysql -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"
66\67
mysql -e "change master to master_host='10.0.0.65',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1;start slave; "
mysql -e "show slave status \G"|grep Yes
高可用
65\66\67
ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /application/mysql/bin/mysql /usr/bin/mysql
65
rm -rf /root/.ssh
/bin/ssh-keygen -t rsa -f /root/.ssh/id_rsa -P ""
sshpass -p123456 ssh-copy-id -i ~/.ssh/id_rsa.pub 10.0.0.66 -o StrictHostKeyChecking=no
sshpass -p123456 ssh-copy-id -i ~/.ssh/id_rsa.pub 10.0.0.67 -o StrictHostKeyChecking=no
mysql -e "grant all privileges on *.* to mha@'10.0.0.%' identified by 'mha';"
65\66\67
ssh 10.0.0.65 date
ssh 10.0.0.66 date
ssh 10.0.0.67 date
yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
67
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
#创建配置文件目录
mkdir -p /etc/mha
#创建日志目录
mkdir -p /var/log/mha/app1
#编辑mha配置文件
cat > /etc/mha/app1.cnf<<EOF
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/mysql/data/binlog
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root
[server1]
hostname=10.0.0.65
port=3306
[server2]
hostname=10.0.0.66
port=3306
[server3]
hostname=10.0.0.67
port=3306
EOF
masterha_check_ssh --conf=/etc/mha/app1.cnf
masterha_check_repl --conf=/etc/mha/app1.cnf
#开启MHA(db03)：
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
#查看MHA状态
masterha_check_status --conf=/etc/mha/app1.cnf
mha的VIP功能
67
cd /usr/local/bin/
rz master_ip_failover
dos2unix /usr/local/bin/master_ip_failover
chmod +x /usr/local/bin/master_ip_failover
vim /usr/local/bin/master_ip_failover
my $vip = '10.0.0.55/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
#
vim /etc/mha/app1.cnf
添加：
master_ip_failover_script=/usr/local/bin/master_ip_failover
65
ifconfig eth0:1 10.0.0.55/24
67
masterha_stop --conf=/etc/mha/app1.cnf
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
邮件报警
67
cd /usr/local/bin/
rz send sendEmail testpl
vim testpl #修改报警邮箱
vim /etc/mha/app1.cnf
# 添加一行
report_script=/usr/local/bin/send
#重启mha
masterha_stop --conf=/etc/mha/app1.cnf
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
2.读写分离
主库
yum install -y Atlas*
cd /usr/local/mysql-proxy/conf
mv test.cnf test.cnf.bak
vi test.cnf
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 10.0.0.55:3306
proxy-read-only-backend-addresses = 10.0.0.66:3306,10.0.0.67:3306
pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=
daemon = true
keepalive = true
event-threads = 8
log-level = message
log-path = /usr/local/mysql-proxy/log
sql-log=ON
proxy-address = 0.0.0.0:33060
admin-address = 0.0.0.0:2345
charset=utf8
# 启动atlas
/usr/local/mysql-proxy/bin/mysql-proxyd test start
ps -ef |grep proxy
# 3. Atlas功能测试
# 测试读操作：
mysql -umha -pmha -h 10.0.0.55 -P 33060
66 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 67 |
+-------------+
1 row in set (0.00 sec)
66 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 65 |
+-------------+
1 row in set (0.00 sec)
# 测试写操作：
66 [(none)]>begin;select @@server_id;commit;
Query OK, 0 rows affected (0.00 sec)
+-------------+
| @@server_id |
+-------------+
| 66 |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
66 [(none)]>begin;select @@server_id;commit;
Query OK, 0 rows affected (0.00 sec)
+-------------+
| @@server_id |
+-------------+
| 66 |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
测试ip漂移
查看vip
[root@65 /server/tools]# ip a |grep eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
inet 10.0.0.65/24 brd 10.0.0.255 scope global noprefixroute eth0
inet 10.0.0.55/24 brd 10.0.0.255 scope global secondary eth0:1
#登录66
mysql -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.65 //主库是51
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 191
Relay_Log_File: mysql-db02-relay-bin.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#停掉主库 65
systemctl stop mysqld //停掉主库Mysql测试
# 切换到从库67上查看
mysql -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.66 //主库切换到52
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 191
Relay_Log_File: mysql-db03-relay-bin.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#在65上查看vip信息
ip a |grep eth0 //vip没有了
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
inet 10.0.0.65/24 brd 10.0.0.255 scope global noprefixroute eth0
#在66上查看vip信息
[root@mysql-db02 ~]# ip a |grep eth0 //db02出现vip55
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
inet 10.0.0.52/24 brd 10.0.0.255 scope global eth0
inet 10.0.0.55/24 brd 10.0.0.255 scope global secondary eth0:0
# VIP漂移测试成功
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
//后台运行mha
masterha_check_repl --conf=/etc/mha/app1.cnf //测试mha复制
MySQL Replication Health is OK
[root@db03 mha]# masterha_check_status --conf=/etc/mha/app1.cnf //检测mha状态，db03查看主库是否切换66
app1 (pid:9849) is running(0:PING_OK), master:10.0.0.66
# mha工作正常
菜鸟9528号，请求开炮。
