mysql 5.7 安装笔记

本文为 centos 7 环境下 MySQL 5.7.20 数据库安装步骤。

windows下安装步骤参考： Windows安装mysql详细步骤（通俗易懂，简单上手）

1. 环境准备

#以root用户登录

#1、建立安装目录
mkdir -p /app/mysql/mysql-5.7.20
mkdir -p /app/mysql/mydata/testdb

#2、建立mysql用户
groupadd mysql
useradd -g mysql -d /app/mysql/ -s /bin/bash mysql
chown -R mysql:mysql /app/mysql/mydata/

#3、上传mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
cd /app/mysql/
tar zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.20-linux-glibc2.12-x86_64 mysql-5.7.20

#4、配置环境变量
cd
vi .bash_profile
# 增加以下内容后保存退出：
PATH=$PATH:$HOME/bin:/app/mysql/mysql-5.7.20/bin:/app/mysql/mysql-5.7.20/lib
# 使配置文件生效：
source .bash_profile

2. 修改配置文件

5.7之后，mysql不再提供my-default.cnf，自己新建一个（改配置文件主要用于初始化db时的一些参数）

#以root用户登录 
cd /mysql/mysql-5.7.20
cp support-files/my-default.cnf /mydata/logfile/testdb/testdb.cnf
vi /app/mysql/mydata/logfile/testdb/testdb.cnf

文件内容如下：

[mysql]
port = 3306
socket = /app/mysql/mydata/testdb/testdb.sock
default-character-set=utf8

[mysqld]
#######Basic Settings#########

##### 0 is OFF, 1 is ON ######
lower_case_table_names=1
server-id = 10669
port=3306
performance_schema = ON
basedir = /app/mysql/mysql-5.7.20
datadir = /app/mysql/mydata/testdb
tmpdir  = /app/mysql/mydata/logfile/testdb/mysqltmp
socket= /app/mysql/mydata/testdb/testdb.sock
character-set-server=utf8
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
collation-server=utf8_general_ci
skip_name_resolve = 1
max_connections = 10000
max_user_connections = 1000
tmp_table_size = 128M
max_allowed_packet = 128M
sort_buffer_size = 64M
join_buffer_size = 128M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M
autocommit = 1
net_read_timeout = 300
net_write_timeout = 1000
######Innodb Settings##########
innodb_page_size = 16384
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 16
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 10
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_flush_method=O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_log_group_home_dir = /app/mysql/mydata/logfile/testdb/redologs
innodb_undo_directory = /app/mysql/mydata/logfile/testdb/undologs
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_log_files_in_group = 3
innodb_file_per_table = 1
innodb_data_file_path=ibdata1:1024M;ibdata2:1024M:autoextend
######Log Settings#############
log_error = /app/mysql/mydata/testdb/testdb.err
pid_file=/app/mysql/mydata/testdb/testdb.pid
expire_logs_days = 14
long_query_time = 5
min_examined_row_limit = 100
######Replication Settings#####
gtid-mode=on
enforce_gtid_consistency=on
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin_trust_function_creators= 1 
log_bin = binlog
sync_binlog = 1
max_binlog_size = 128M
binlog_cache_size = 2M
log_slave_updates = 1
binlog_format = row
relay_log = relay.log
relay_log_recovery = 1
log-slave-updates=true
slave-parallel-workers=4
skip_slave_start=1
#read_only=1

说明：

1. 需注意[innodb_log_group_home_dir]& [innodb_undo_directory]目录需提前建立。
2. 参数log_bin_trust_function_creators = 1，确保MySQL能创建函数、存储等过程。

3. 初始化DB

#以root用户登录
#1、建立数据文件目录
mkdir -p /app/mysql/mydata/testdb
mkdir -p /app/mysql/mydata/logfile/testdb/mysqltmp
mkdir -p /app/mysql/mydata/logfile/testdb/redologs/
mkdir -p /app/mysql/mydata/logfile/testdb/undologs/
chown -R mysql:mysql /app/mysql/mydata/testdb
chown -R mysql:mysql /app/mysql/mydata/logfile

#2、初始化DB
/app/mysql/mysql-5.7.20/bin/mysqld --defaults-file=/app/mysql/mydata/logfile/testdb/testdb.cnf --initialize --basedir=/app/mysql/mysql-5.7.20 --datadir=/app/mysql/mydata/testdb --user=mysql

4. 启停实例

#1、启动mysql实例
/app/mysql/mysql-5.7.20/bin/mysqld_safe --defaults-file=/app/mysql/mydata/logfile/testdb/testdb.cnf --ledir=/app/mysql/mysql-5.7.20/bin --datadir=/app/mysql/mydata/testdb --pid-file=/app/mysql/mydata/testdb/testdb.pid --user=mysql &

#2、关闭MySQL实例
/app/mysql/mysql-5.7.20/bin/mysqladmin --socket=/app/mysql/mydata/testdb/testdb.sock --port=3306 shutdown -h localhost -p

5. 用户配置

查看/mydata/testdb/testdb.err。得到root@localhost的密码

#以root用户登录
#1、登录
/app/mysql/mysql-5.7.20/bin/mysql -uroot -p --socket=/app/mysql/mydata/testdb/testdb.sock --port=3306
MYSQL>set password=password("root1234");

#2、创建用户 root用户不允许远程链接，使用appdbuser远程链接
grant all privileges on 库名.表名 to '用户名'@'IP地址' identified by '密码' with grant option;

# 如果需要放开，使用以下命令
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'youpassword' WITH GRANT OPTION;
mysql>FLUSH PRIVILEGES;

mysql -u root -p
#2.1创建应用用户 appusr
mysql>create user appusr identified by 'appusr123';
mysql>grant all privileges on *.* to 'appusr'@'%';
mysql>flush privileges;
#2.2创建应用查询用户appsel
mysql>create user appsel identified by 'QAZ2wsx';
mysql>grant select on schema.* to 'appsel'@'%';
mysql>flush privileges;
#2.3创建系统查询用户dbsel
mysql>create user dbsel identified by 'QAZ2wsx';
mysql>grant select on *.* to 'dbsel'@'%';
mysql>grant process on *.* to 'dbsel'@'%';
mysql>grant replication client on *.* to 'dbsel'@'%';
mysql>flush privileges;

#3、查询用户
mysql> select host, user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | appdbuser     |
| %         | appsel        |
| %         | dbsel         |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+

#4、删除用户
drop user appsel;
#默认删除的是'XXX'@'%'这个用户，如果还有其他的用户如'XXX'@'localhost'等，不会一起被删除。如果要删除'XXX'@'localhost'，使用drop删除时需要加上host即drop user 'XXX'@'localhost'。

#5、修改用户
#命令行修改root密码：
mysql> UPDATE mysql.user SET password=PASSWORD('新密码') WHERE User='root';
mysql> FLUSH PRIVILEGES;