MySQL安装及配置

yum安装

本人是在centos7上使用yum安装，因为使用了国内的yum镜像，使用 yum list | grep 查看到的版本非常低。因此先添加mysql官方repo,这里使用的是centos7的，更多版本可以在http://dev.mysql.com/downloads/repo/yum/查看下载。

1. 下载repo安装包： wget http://repo.mysql.com//mysql57-community-release-el7-8.noarch.rpm
2. 安装repo包： yum -y localinstall mysql57-community-release-el7-8.noarch.rpm
3. 安装mysql： yum -y install mysql-community-server
4. 启动mysql： systemctl start mysqld.service
5. 设置开机自启动： systemctl enable mysqld.service
6. 初次启动时，mysql会自动创建密码，查看默认密码： grep 'temporary password' /var/log/mysqld.log
7. 修改mysql默认密码：

   shell> mysql -u root -p
   mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
   

8. 修改远程连接权限，此时从远程机器上连接将会提示ERROR 1130 – Host ‘localhost’ is not allowed to connect to this MySQL server，需要使用如下方法修改权限，该方法设置允许任何远程ip通过密码连接该数据库：

   shell> mysql -u root -p
   mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;
   mysql> FLUSH PRIVILEGES;
   mysql> quit;
   shell> systemctl restart  mysqld.service
   

至此，安装及配置完毕。

二进制包安装

修改配置文件

vim conf/mysql_conf.cnf

[mysqld]
basedir=/usr/local/bigdata/mysql-5.7.27-linux-glibc2.12-x86_64
datadir=/mnt/disk1/bigdata/mysql-5.7.27/data
port=3307
server-id=2
socket=/usr/local/bigdata/mysql-5.7.27-linux-glibc2.12-x86_64/sock/mysql.sock
binlog_format=mixed
gtid-mode=off
#忽略域名解析
skip-name-resolve
#设置默认编码
character-set-server=utf8
#设置最大连接数
max_connections=1000
#gtid-mode=on  ##GTID type
#enforce-gtid-consistency=on  ##GTID type
#log_slave_updates  ##GTID type
log_timestamps=SYSTEM
log-bin=/mnt/disk1/bigdata/mysql-5.7.27/binlog
tmpdir=/mnt/disk1/bigdata/mysql-5.7.27/tmp
log-error=/mnt/disk1/bigdata/mysql-5.7.27/log/mysql.log
log-output=file
max_allowed_packet=100M
#默认开启事件
event_scheduler=ON
#链接等待时间(秒)
wait_timeout=1728000
interactive_timeout=1728000
[client]
#设置默认编码
default-character-set=utf8
socket=/usr/local/bigdata/mysql-5.7.27-linux-glibc2.12-x86_64/sock/mysql.sock
[mysql]
socket=/usr/local/bigdata/mysql-5.7.27-linux-glibc2.12-x86_64/sock/mysql.sock

配置启动脚本

vim sbin/start-mysql.sh

#!/bin/bash
nohup /usr/local/bigdata/mysql-5.7.27-linux-glibc2.12-x86_64/bin/mysqld --defaults-file=/usr/local/bigdata/mysql-5.7.27-linux-glibc2.12-x86_64/conf/mysql_conf.cnf --user=mysql > /dev/null 2>&1 &
echo "MySQL started"

配置停止脚本

vim sbin/stop-mysql.sh

#!/usr/bin/env bash
command="ps -ef | grep mysqld | grep /usr/local/bigdata/mysql-5.7.27-linux-glibc2.12-x86_64 | grep -v grep"
if [ "X`eval ${command} -c`" != "X0" ]; then
   eval ${command} | awk '{print $2}' | xargs kill > /dev/null 2>&1
fi
for ((i=1;i<=10;i++)); do
  if [ "X`eval ${command} -c`" == "X0" ]; then
    break
  fi
  sleep 0.5
  if [ $i -ge "10" ]; then
    eval ${command}  | awk '{print $2}' | xargs kill -9 > /dev/null 2>&1
    break
  fi
done

创建并修改目录权限

useradd mysql
mkdir /usr/local/bigdata/mysql-5.7.27-linux-glibc2.12-x86_64/sock
chown -R mysql:mysql /usr/local/bigdata/mysql-5.7.27-linux-glibc2.12-x86_64
mkdir -p /mnt/disk1/bigdata/mysql-5.7.27/log
mkdir -p /mnt/disk1/bigdata/mysql-5.7.27/data
mkdir -p /mnt/disk1/bigdata/mysql-5.7.27/tmp
mkdir -p /mnt/disk1/bigdata/mysql-5.7.27/binlog
chown -R mysql:mysql /mnt/disk1/bigdata/mysql-5.7.27

初始化mysql

bin/mysqld --defaults-file=/usr/local/bigdata/mysql-5.7.27-linux-glibc2.12-x86_64/conf/mysql_conf.cnf --initialize --user=mysql

bin/mysql_ssl_rsa_setup --basedir=/usr/local/bigdata/mysql-5.7.27-linux-glibc2.12-x86_64 --datadir=/mnt/disk1/bigdata/mysql-5.7.27/data

获取随机密码

cat /mnt/disk1/bigdata/mysql-5.7.27/log/mysql.log | grep "temporary password" | awk -F ": " '{print $NF}'

启动mysql

sh sbin/start-mysql.sh

# 输入获取的密码进入
bin/mysql -S /usr/local/bigdata/mysql-5.7.27-linux-glibc2.12-x86_64/sock/mysql.sock -uroot -p

执行如下SQL语句进行root密码修改

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'root'@'%' IDENTIFIED BY '123456';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT ALL ON *.* TO 'root'@'%';

完成

#使用如下命令进行连接测试
bin/mysql -h 127.0.0.1 -P 3307 -uroot -p123456