MySQL 安装手册(5.7.20)
0. 安装摘要
- 清除 Linux 环境自身 MYSQL 或者 MariaDB 等;
- 下载 MYSQL RPM包;
- 安装、配置、启动;
- 修改 root 密码;
- 创建数据库、用户和授权等;
1. 清除 Linux 环境自身 MYSQL 或者 MariaDB
检测系统是否自带安装 mysql:
如果你系统有安装,那可以选择进行卸载:
-- 普通删除模式
rpm -e mysql
-- 强制删除模式(不考虑依赖关系)
rpm -e --nodeps mysql
2. 下载
本次安装版本为:mysql-5.7.20-1.el6.x86_64.rpm-bundle.tar
- 方式一:Download MySQL Community Server 官方页面自行下载
- 方式二:wget 或下载工具下载
- cdn.mysql.com
https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.20-1.el6.x86_64.rpm-bundle.tar
解压:
[root@cdhslave5 mysql]# tar xvf mysql-5.7.20-1.el6.x86_64.rpm-bundle.tar
解压后当前目录包含:
-rw-r--r-- 1 root root 472381440 Jan 10 09:23 mysql-5.7.20-1.el6.x86_64.rpm-bundle.tar
-rw-r--r-- 1 7155 31415 23814860 Sep 14 23:44 mysql-community-client-5.7.20-1.el6.x86_64.rpm
-rw-r--r-- 1 7155 31415 336476 Sep 14 23:44 mysql-community-common-5.7.20-1.el6.x86_64.rpm
-rw-r--r-- 1 7155 31415 3748072 Sep 14 23:44 mysql-community-devel-5.7.20-1.el6.x86_64.rpm
-rw-r--r-- 1 7155 31415 39278972 Sep 14 23:44 mysql-community-embedded-5.7.20-1.el6.x86_64.rpm
-rw-r--r-- 1 7155 31415 136172856 Sep 14 23:44 mysql-community-embedded-devel-5.7.20-1.el6.x86_64.rpm
-rw-r--r-- 1 7155 31415 2177676 Sep 14 23:44 mysql-community-libs-5.7.20-1.el6.x86_64.rpm
-rw-r--r-- 1 7155 31415 1723320 Sep 14 23:44 mysql-community-libs-compat-5.7.20-1.el6.x86_64.rpm
-rw-r--r-- 1 7155 31415 159637208 Sep 14 23:45 mysql-community-server-5.7.20-1.el6.x86_64.rpm
-rw-r--r-- 1 7155 31415 105476736 Sep 14 23:45 mysql-community-test-5.7.20-1.el6.x86_64.rpm
另附 yum 方式安装
yum install mysql
yum install mysql-server
yum install mysql-devel
3. 安装、配置、启动
3.1 安装
-- 需使用root权限安装
# rpm -ivh mysql-community-libs-5.7.20-1.el6.x86_64.rpm mysql-community-common-5.7.20-1.el6.x86_64.rpm
# rpm -ivh mysql-community-server-5.7.20-1.el6.x86_64.rpm mysql-community-client-5.7.20-1.el6.x86_64.rpm mysql-community-devel-5.7.20-1.el6.x86_64.rpm
-- 默认将安装在 /var/lib/mysql 目录
-- 默认mysqld 日志将在 /var/log/mysqld.log
3.2 配置
-- 备份配置文件
# cp /etc/my.cnf /etc/my.cnf.bak
-- 修改配置文件
# vim /etc/my.cnf
-- 增加以下配置
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
lower_case_table_names = 1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
max_connections=5000
default-time_zone = '+8:00'
完整示例见附录一:my.cnf 完整示例。
3.3 启动
-- 1. 启动服务
# service mysqld start
-- 2. 设置开机自启动
# chkconfig --list | grep mysqld
mysqld 0:off 1:off 2:off 3:on 4:on 5:on 6:off
-- 如果列表中没有mysqld,先添加,在设置开启自启动:
chkconfig add mysqld
chkconfig mysqld on
-- 检测列表,如果对应mysqld对应6项中有3项是on则可认为设置成功
-- 其他支持的操作
# service mysqld start/stop/status
如果报/tmp/mysql.sock
相关错,关闭服务后尝试建立一下软连接:
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
4. 修改 root 密码
MySQL 5.7.20 版本启动后会自动生成root的临时密码,需要立即修改。
密码生成在 /var/log/mysqld.log 文件中。
4.1 获取 root 临时密码
# grep 'temporary password' /var/log/mysqld.log
2018-01-10T01:32:00.763478Z 1 [Note] A temporary password is generated for root@localhost: rP/,Fox)h9pn
4.2 修改 root 密码
-- 密码有安全等级要求
mysql> alter user 'root'@'localhost' IDENTIFIED by 'you-root-password';
-- 可正常操作
-- 查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
-- 查看字符编码
mysql> show variables like '%char%';
+--------------------------------------+----------------------------+
| Variable_name | Value |
+--------------------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| validate_password_special_char_count | 1 |
+--------------------------------------+----------------------------+
-- 查看大小写是否敏感
mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 1 |
+------------------------+-------+
5. 创建数据库、用户和授权等
5.1 创建数据库
-- UTF8
CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
-- GBK
CREATE DATABASE test2 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
示例:
CREATE DATABASE sentry DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE hive DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE rcr_dev DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE rcr_test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE rcr_demo DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
5.2 创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
如果让该用户可以从任意远程主机登录,可以使用通配符%
. 另密码可以为空。
示例:
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';
CREATE USER 'sentry'@'%' IDENTIFIED BY 'your-password';
CREATE USER 'hive'@'%' IDENTIFIED BY 'your-password';
CREATE USER 'rcr'@'%' IDENTIFIED BY 'your-password';
** 注意禁止使用root做应用连接**
5.3 授权
GRANT ALL ON sentry.* TO 'sentry'@'%';
GRANT ALL ON hive.* TO 'sentry'@'%';
GRANT ALL ON sentry.* TO 'rcr'@'%';
GRANT ALL ON hive.* TO 'rcr'@'%';
GRANT ALL ON rcr_dev.* TO 'rcr'@'%';
GRANT ALL ON rcr_test.* TO 'rcr'@'%';
GRANT ALL ON rcr_demo.* TO 'rcr'@'%';
FLUSH PRIVILEGES
-- 测试访问
[futeng@FTRMBP ~]$ mysql -h 192.168.129.115 -urcr -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hive |
| rcr_demo |
| rcr_dev |
| rcr_test |
| sentry |
+--------------------+
附录一:my.cnf 完整示例
[root@cdhslave5 futeng]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character_set_server=utf8
init_connect='SET NAMES utf8'
lower_case_table_names = 1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
max_connections=5000
default-time_zone = '+8:00'
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid