1.服务器: 192.168.9.24 Mha-master 192.168.9.25 Mha-slave 192.168.9.26 Mha-bak 2.布置开启Gtid功能mysql5.7.20并建立主从复制 2.1三台安装mysql5.7.20
#!/bin/bash
#author:pan
#切换到相应文件夹
cd
#二进制安装mysql5.7.20
tar xvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz -C /usr/local
cd /usr/local/
mv mysql-5.7.20-linux-glibc2.12-x86_64/ mysql
cd /usr/local/mysql/
mkdir -p data log binlogs run
cd /usr/local/
#添加用户
useradd -M -s /sbin/nologin mysql
#更改属主
chown -R mysql.mysql mysql
#添加路径
ln -s /usr/local/mysql/bin/* /usr/local/bin/
#删除原my.cnf
rm -f /etc/my.cnf
#创建my.cnf
cat > /etc/my.cnf << EOF
[client]
port = 3306
socket = /usr/local/mysql/run/mysql.sock
[mysqld]
port = 3306
socket = /usr/local/mysql/run/mysql.sock
pid_file = /usr/local/mysql/run/mysql.pid
datadir = /usr/local/mysql/data
default_storage_engine = InnoDB
max_allowed_packet = 512M
max_connections = 2048
open_files_limit = 65535
skip-name-resolve
lower_case_table_names=1
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 2048M
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
key_buffer_size = 64M
log-error = /usr/local/mysql/log/mysql_error.log
log-bin = /usr/local/mysql/binlogs/mysql-bin
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/log/mysql_slow_query.log
long_query_time = 5
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
server-id=1
EOF
#初始化数据库
mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
#设置启动项
cat > /usr/lib/systemd/system/mysqld.service << EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev./doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/usr/local/mysql/run/mysqld.pid
#Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0
#Execute pre and post scripts as root
PermissionsStartOnly=true
#Needed to create system tables
#ExecStartPre=/usr/bin/mysqld_pre_systemd
#Start main service
ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/run/mysqld.pid $MYSQLD_OPTS
#Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql
#Sets open_files_limit
LimitNOFILE = 65535
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false
EOF
#重新加载daemon
systemctl daemon-reload
systemctl enable mysqld.service
systemctl start mysqld
#启动后第一次登陆密码
#grep 'temporary password' /usr/local/mysql/log/mysql_error.log
#修改初始密码
#mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '000000';
#安装完成
2.2开启gtid功能,修改配置文件 Mha-master主机: [mysqld] gtid_mode = ON log_slave_updates 增加此三行调整server-id的值 enforce_gtid_consistency
server-id=1 Mha-slave主机: [mysqld] read_only = 1 设置只读
gtid_mode = ON log_slave_updates 增加此三行调整server-id的值 enforce_gtid_consistency
server-id=2
relay_log_purge = 0 禁用自动删除relay log功能
重启mysql,并查看 [root@Mha-master ~]# systemctl restart mysqld [root@Mha-master ~]# mysql -uroot -p000000 mysql> show global variables like '%gtid%'; ---------------------------------- ------- | Variable_name | Value | ---------------------------------- ------- | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | ---------------------------------- ------- 8 rows in set (0.00 sec)
2.3配置主从 2.3.1在Mha-master上创建主从复制账号 mysql> grant replication slave on . to rep@'192.168.9.%' identified by '000000'; Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show grants for rep@'192.168.9.%' ; ------------------------------------------------------- | Grants for rep@192.168.9.% | ------------------------------------------------------- | GRANT REPLICATION SLAVE ON . TO 'rep'@'192.168.9.%' | ------------------------------------------------------- 1 row in set (0.00 sec) 2.3.2在两个从上进行主从同步 [root@Mha-slave ~]# mysql -uroot -p000000 mysql> change master to master_host='192.168.9.24',master_user='rep',master_password='000000',master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave; Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G; 查看是否成功,如果不成功查看是否是防火墙的问题
3.三台都做服务器ssh [root@Mha-master ~]# ssh-keygen -t rsa -P "" -f ~/.ssh/id_rsa [root@Mha-master ~]# sshpass -p 102110504 ssh-copy-id -i ~/.ssh/id_rsa.pub -o StrictHostKeyChecking=no root@192.168.9.25
4.部署MHA 来源:http://www./content-2-26141.html
|