CentOS7.X源码安装MySQL-5.7.18-阿里云开发者社区

CentOS7.X源码安装MySQL-5.7.18

2018-06-11 2210

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介： CentOS7.X源码安装MySQL-5.7.18

+关注继续查看

mysql数据库安装及配置

安装前的准备

yum install \
vim \
git \
gcc \
gcc-c++ \
wget \
make \
cmake \
automake \
autoconf \
libaio \
libtool \
net-tools \
bison-devel \
libaio-devel \
ncurses-devel \
perl-Data-Dumper \
-y

下载boost_1_59_0

cd /usr/local
wget http://downloads.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz

下载mysql-5.7.18

cd /root
wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18.tar.gz
tar -zxvf mysql-5.7.18.tar.gz
cd mysql-5.7.18

运行cmake

cmake . \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DWITH_SYSTEMD=1 \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/usr/local \
-DDEFAULT_CHARSET=utf8mb4 \

make
make install

添加mysql用户
```
useradd -s /sbin/nologin -M mysql
```

创建数据文件目录、所属组、用户改为mysql

mkdir /data/
mkdir /data/mysql/
chown -R mysql:mysql /etc/my.cnf
chown -R mysql:mysql /data/mysql/
chown -R mysql:mysql /var/*/mysql*
chown -R mysql:mysql /usr/local/mysql/

修改SELinux

vim /etc/sysconfig/selinux

SELINUX=permissive

ESC
:wq

setenforce 0

my.cnf详细配置

vim /etc/my.cnf

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
datadir=/data/mysql
character-set-server=utf8mb4
init_connect='SET NAMES utf8mb4'
collation-server=utf8mb4_unicode_ci
character-set-client-handshake=FALSE

symbolic-links=0
log-error=/var/log/mysqld.log
socket=/var/lib/mysql/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid
secure_file_priv=/var/lib/mysql-files

server-id=1
binlog-format=Row
log-bin=/data/mysql/mysql-bin
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

ESC
:wq

初始化数据库配置

# --initialize-insecure参数为不安全的初始化方案，建议使用--initialize参数
# 以下代码执行完之后,会在/data/mysql/mysql-error.log保存初始密码

su mysql
su mysql !!!
su mysql !!!

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

# 查看初始密码
cat /var/lib/mysql/mysql.log|grep password

# ssh连接mysql,如果对ssh不了解,不要这样做
/usr/local/mysql/bin/mysql_ssl_rsa_setup \
--initialize \
--user=mysql \

vim /etc/my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
slow_query_log = 1
long_query_time = 1
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
log_error = /data/mysql/mysql-error.log
slow_query_log_file = /data/mysql/mysql-slow.log
default_storage_engine = InnoDB

[mysqld_safe]
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysqld.pid

ESC
:wq

添加mysqld.service

exit
cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/

mysqld.service详细配置

vim /usr/lib/systemd/system/mysqld.service

# 将下面的配置覆盖整个文件内容
[Unit]
Description=开机启动MySQL.
After=default.target network.target syslog.target

[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/data/mysql/mysqld.pid
TimeoutSec=0
PermissionsStartOnly=true
ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd
ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/mysql/mysql.pid $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILE = 5000
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false

[Install]
WantedBy=multi-user.target

ESC
:wq

启用mysqld.service

systemctl enable mysqld
systemctl start  mysqld
systemctl daemon-reload

测试配置是否正确，没有任何输出则表示一切正常
```
/usr/local/mysql/bin/mysqld_pre_systemd
```
数据文件所属用户修改为mysql（如果‘初始化数据库配置’时完全按文档进行，可不执行本步骤，不确定则执行）
```
chown -R mysql:mysql /data/mysql/
```

启动mysql

systemctl enable mysqld
systemctl start  mysqld

添加mysql到mysql用户环境变量

su mysql
vim /home/mysql/.bash_profile

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/local/mysql/bin

ESC
:wq

source /home/mysql/.bash_profile

尝试连接mysql

# mysql默认用户为root
mysql -u root -p
password:**************
#修改root[DB用户]初始密码
SET PASSWORD=PASSWORD('**************');
show databases;
use mysql;
show tables;
select * from user \G;

测试启动、停止、重启mysql

#启用并启动
systemctl enable mysqld
systemctl start mysqld
#禁用并关闭
systemctl disable mysqld
systemctl stop mysqld
#启用并重新启动
systemctl enable mysqld
systemctl restart mysqld

设置外部访问密码

# 数据库名-通配符及格式:
    *[允许操作所有数据库]
    %pay%[允许操作名字包含'pay'的数据库]
    mydatabase[仅允许操作'mydatabase'数据库]
# 表名-通配符及格式:
    *[允许操作所有表]
    %pay%[允许操作名字包含'pay'的表]
    mytable[仅允许操作'mytable'表]
# ip地址-通配符及格式:
    %/0.0.0.0[允许任意IP访问]
    xxx.xxx.xxx.xxx[仅限公网访问]
    192.168.x.xxx[仅限局域网访问]
    localhost/127.0.0.1/::1[仅限本机访问]

# 数据库授权格式
GRANT 权限名 ON 数据库名.数据表名 TO '数据用户'@'访问IP' IDENTIFIED BY '用户密码' WITH GRANT OPTION;

# 为任意数据库表 任意操作 分配root用户,仅限本地访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '**************' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY '**************' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' IDENTIFIED BY '**************' WITH GRANT OPTION;

# 为任意数据库表 任意操作 分配mysql用户,任意IP访问(安装调试阶段、本地开发使用,用后即删)
GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'%' IDENTIFIED BY '**************' WITH GRANT OPTION;

# 为任意数据库表 增删改查 分配单独用户,为不同的业务分配专属用户
GRANT INSERT ON *.* TO 'insert1'@'127.0.0.1' IDENTIFIED BY '**************' WITH GRANT OPTION;
GRANT DELETE ON *.* TO 'delete1'@'127.0.0.1' IDENTIFIED BY '**************' WITH GRANT OPTION;
GRANT UPDATE ON *.* TO 'update1'@'127.0.0.1' IDENTIFIED BY '**************' WITH GRANT OPTION;
GRANT SELECT ON *.* TO 'select1'@'127.0.0.1' IDENTIFIED BY '**************' WITH GRANT OPTION;

# 刷新权限
FLUSH PRIVILEGES;

领支付宝红包支持作者