MySQL5.7.20部署安装|多实例|主从关系搭建

 黑渐神|he-jason.com   2019-01-14 16:55   45 人阅读  MySQL5.7.20部署安装|多实例|主从关系搭建已关闭评论

最近公司需要在一台服务器上使用MySQL5.7的多实例,之前一直用的低版本的多实例,具说5.7的方式有所变化,于是研究了下,特意在这里留下记录,方便自己记忆和他人
首先我们需要安装一个libaio-devel的包,下载5.7.20的二进制包
解压到/usr/local/下也行,mv过去也可以,接着记得创建 mysql的软链接

yum install -y libaio-devel.x86_64

wget https://downloads.mysql.com/archives/get/file/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

tar xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz 
mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/
ln -s /usr/local/mysql-5.7.20-linux-glibc2.12-x86_64/ /usr/local/mysql
##看了些资料都有这一步,其实没什么用,不过看个人喜欢,我经常导库用的是/tmp
mkdir -vp /usr/local/mysql/mysql-files
#创建mysql运行帐户并给目录mysql权限
useradd -r -s /usr/sbin/nologin mysql
chown -R root.mysql /usr/local/mysql-5.7.20-linux-glibc2.12-x86_64/
#创建多实例数据目录
mkdir -p /data/mysql/330{6,7,8,9}
chown -R mysql.mysql /data/mysql

##创建多实例的配置文件
mysqld1,mysqld2,mysqldN为N个多实例,每一个mysqld里的配置数据目录必须是我们刚刚创建的,并且拥有`mysql`权限
```bash
编辑/etc/my.cnf
```conf
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin
log=/var/log/mysql_multi.log

[mysqld1]
datadir=/data/mysql/3306
socket=/data/mysql/3306/3306.sock
pid-file=/data/mysql/3306/mysql.pid
port=3306
user=mysql
performance_schema=off
innodb_buffer_pool_size=128M
bind_address=0.0.0.0
skip-name-resolve=0
character-set-server=utf8
collation-server=utf8_unicode_ci
log_error=/data/mysql/3306/mysql.log
log-bin=/data/mysql/3306/mysql-bin
server_id=3306
skip-external-locking

[mysqld2]
datadir=/data/mysql/3307
socket=/data/mysql/3307/3307.sock
pid-file=/data/mysql/3307/mysql.pid
port=3307
user=mysql
performance_schema=off
innodb_buffer_pool_size=128M
bind_address=0.0.0.0
skip-name-resolve=0
character-set-server=utf8
collation-server=utf8_unicode_ci
log_error=/data/mysql/3307/mysql.log
log-bin=/data/mysql/3307/mysql-bin
server_id=3307
skip-external-locking

[mysqld3]
datadir=/data/mysql/3308
socket=/data/mysql/3308/3308.sock
pid-file=/data/mysql/3308/mysql.pid
port=3308
user=mysql
performance_schema=off
innodb_buffer_pool_size=128M
bind_address=0.0.0.0
skip-name-resolve=0
character-set-server=utf8
collation-server=utf8_unicode_ci
log_error=/data/mysql/3308/mysql.log
log-bin=/data/mysql/3308/mysql-bin
server_id=3308
skip-external-locking

[mysqld4]
datadir=/data/mysql/3309
socket=/data/mysql/3309/3309.sock
pid-file=/data/mysql/3309/mysql.pid
port=3309
user=mysql
performance_schema=off
innodb_buffer_pool_size=128M
bind_address=0.0.0.0
skip-name-resolve=0
character-set-server=utf8
collation-server=utf8_unicode_ci
log_error=/data/mysql/3309/mysql.log
log-bin=/data/mysql/3309/mysql-bin
server_id=3309
skip-external-locking

初始化每个实例
并记录下每个实例的临时密码,因为后面我们需要修改这个密码

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306

geH+*tJsr2BV

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307

Sjqf/l9T;k>g

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3308

!>SjhdOn?4Sx

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3309

E+flG?Y.=5;k

给每个实例开启ssl连接

/usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306

/usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307

/usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3308

/usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3309

复制多实例脚本到服务管理目录下

cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

添加执行权限

chmod +x /etc/init.d/mysqld_multi

查检状态(这里需要先设置环境)

export PATH=/usr/local/mysql/bin:$PATH

如果不先设置环境的话,会报错找不到
ABORT: Can't find command 'my_print_defaults'.

启动实例

## 启动单个实例
/etc/init.d/mysql_multi start 1
## 启动多个实例
/etc/init.d/mysql_multi start 1-4
## 停止单个或多个实例
/etc/init.d/mysql_multi stop 1
/etc/init.d/mysql_multi stop 1,4
## 检查实例状态
/etc/init.d/mysql_multi report

修改实例密码

mysqladmin -S /data/mysql/3306/3306.sock -uroot -p'geH+*tJsr2BV' password Aa123321

mysqladmin -S /data/mysql/3307/3307.sock -uroot -p'Sjqf/l9T;k>g' password Aa123321

mysqladmin -S /data/mysql/3308/3308.sock -uroot -p'vS*agS_t8-.V' password Aa123321

mysqladmin -S /data/mysql/3309/3309.sock -uroot -p'E+flG?Y.=5;k' password Aa123321

创建多实例快捷脚本

#!/usr/bin/sh

#define loginface

port=$1
state=$2
if [ $# -gt 2 ]
  then
    echo "Invalid parameter or too long parameter!"
    exit 1
elif [ $# -eq 0 ]
  then
    echo "Please enter valid parameters!"
    echo "For example: mysql_multi 3306"
    echo "3306=/data/mysql/3306/3306.sock"
    exit 2
elif [ ! -e /data/mysql/${port}/${port}.sock ]
  then
    echo "Please check /data/mysql/${port}/${port}.scok"
    exit 3
fi
arg_N=$#
#difine fuction for command
comm(){
  if [ $arg_N -eq 2 ]
    then
      mysql -uroot -p'Aa123321' -S /data/mysql/${port}/${port}.sock -e "$state" 2>/dev/null
  else [ $arg_N -eq 1 ]
      mysql -uroot -p'Aa123321' -S /data/mysql/${port}/${port}.sock 2>/dev/null
  fi
}
comm

创建主从关系

检查bin-log配置

清除一下bin_log文件
mysql_multi 3306 "reset master"
mysql_multi 3307 "reset master"
mysql_multi 3308 "reset master"
mysql_multi 3309 "reset master"

### 显示bin_log文件pos点
mysql_multi 3306 "show master status"
mysql_multi 3307 "show master status"
mysql_multi 3308 "show master status"
mysql_multi 3309 "show master status"

创建主从复制帐户

mysql_multi 3306 "grant replication slave on *.* to slave@'127.0.0.1' identified by 'Test123..'"

同步数据库

mysqldump -S /data/mysql/3306/3306.sock -uroot -pAa123321 -A >/tmp/3306.sql

给从库添加主库信息

添加完后在数据目录下,产生master.info文件,存放主库信息

mysql_multi 3307
mysql>set sql_log_bin=0;
mysql>source /tmp/3306.sql;
mysql>change master to
mysql>master_host='127.0.0.1',
mysql>master_port=3306,
mysql>master_user='slave',
mysql>master_password='Test123..',
mysql>master_log_file='mysql-bin.000001',
mysql>master_log_pos=598;

mysql>start slave;
mysql>show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

到此为止,Mysql5.7.20 多实例部署成功

本文地址:https://www.he-jason.com/he-jason/talkops/757.html
版权声明:本文为原创文章,版权归 黑渐神|he-jason.com 所有,欢迎分享本文,转载请保留出处!
双11拉新分百万红包

评论已关闭!