MySQL5.7 二进制安装

MySQL5.7 二进制安装文档

1、创建MySQL用户

创建MySQL所需用户，后续都以这个用户来运行MySQL

[root@mysql-master ~]# useradd -r -s /sbin/nologin mysql

2、创建目录和授权

创建日志、数据存放目录、把创建好的目录属主、属组为mysql用户

[root@mysql-master ~]# mkdir /app/mydata/{logs,data} -p
[root@mysql-master ~]# chown -R mysql.mysql /app/mydata

3、解压安装包

解压MySQL的二进制包

[root@mysql-master ~]# cd /app/soft/
[root@mysql-master soft]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

4、创建软连接修改权限

从/app/soft下mysql-5.7.22-linux-glibc2.12-x86_64的目录在/usr/local下创建一个mysql的软连接，然后把这个软连接的属组、属主都改为mysql用户、查看mysql里面文件的权限

[root@mysql-master soft]# ln -s /app/soft/mysql-5.7.22-linux-glibc2.12-x86_64/ /usr/local/mysql
[root@mysql-master soft]# chown -R mysql.mysql /usr/local/mysql/
[root@mysql-master soft]# ls -l /usr/local/mysql/
total 36
drwxr-xr-x  2 mysql mysql  4096 Apr 24 13:46 bin
-rw-r--r--  1 mysql mysql 17987 Mar  4  2018 COPYING
drwxr-xr-x  2 mysql mysql    55 Apr 24 13:46 docs
drwxr-xr-x  3 mysql mysql  4096 Apr 24 13:46 include
drwxr-xr-x  5 mysql mysql   229 Apr 24 13:46 lib
drwxr-xr-x  4 mysql mysql    30 Apr 24 13:46 man
-rw-r--r--  1 mysql mysql  2478 Mar  4  2018 README
drwxr-xr-x 28 mysql mysql  4096 Apr 24 13:46 share
drwxr-xr-x  2 mysql mysql    90 Apr 24 13:46 support-files

5、修改配置文件

编辑/etc/my.cnf配置文件

# 复制到my.cnf配置文件里前，先在vim里把自动注释功能关闭
：set paste
[root@mysql-master soft]# vim /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
server_id=1
port = 3306
user = mysql
character-set-server = utf8
default_storage_engine = innodb
log_timestamps = SYSTEM
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /app/mydata/data
pid-file = /app/mydata/data/mysql.pid
max_connections = 5000
max_connect_errors = 1000
table_open_cache = 1024
max_allowed_packet = 128M
open_files_limit = 65535
log_bin = mysql-bin
binlog_format  = MIXED
query_cache_size = 128M
query_cache_limit = 4M
sort_buffer_size = 16M
join_buffer_size = 16M
wait_timeout=31536000
interactive_timeout=31536000
#####====================================[innodb]==============================
innodb_buffer_pool_size = 1024M
innodb_file_per_table = 1
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_purge_threads = 2
innodb_flush_log_at_trx_commit = 1
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_max_dirty_pages_pct = 80
innodb_rollback_on_timeout = on
innodb_lock_wait_timeout = 28800
innodb_data_file_path=ibdata1:1024M:autoextend
innodb_undo_tablespaces=3
lower_case_table_names = 1
skip_name_resolve = on
#####====================================[log]==============================
log_error = /app/mydata/logs/mysql-error.log 
slow_query_log = 1
long_query_time = 15 
log-queries-not-using-indexes
slow_query_log_file = /app/mydata/logs/mysql-slow.log
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

6、初始化mysql

[root@mysql-master local]# cd mysql
# centos 模式下安装
[root@mysql-master local]# yum -y install libaio*
# Ubuntu 模式下安装
root@web-nginx-01:/usr/local/mysql# apt-get -y install libaio-dev  libncurses5
[root@mysql-master mysql]# ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/app/mydata/data --innodb_undo_tablespaces=3 --explicit_defaults_for_timestamp

7、配置启动文件，并启动mysql

[root@mysql-master mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@mysql-master mysql]# chkconfig --add mysqld
[root@mysql-master mysql]# chkconfig mysqld on
[root@mysql-master mysql]# service mysqld start
Starting MySQL.. SUCCESS! 

8、配置系统环境把mysql的信息添加进去，让其生效

[root@mysql-master mysql]# vim /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
[root@mysql-master mysql]# source /etc/profile.d/mysql.sh

9、查看默认密码，修改mysql的初始密码

[root@mysql-master mysql]# grep -i password /app/mydata/logs/mysql-error.log 
2019-04-24T13:58:25.617693+08:00 1 [Note] A temporary password is generated for root@localhost: Owq-O*udO0Mc

[root@mysql-master mysql]# mysql -uroot -pOwq-O*udO0Mc
mysql> set password=password('xxxxxx');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# 5.7之后没有password，取而代之的为authentication_string
mysql> SELECT User,Host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| User          | Host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *A498955BAB852BEF5B0C78584202F0326BB117A7 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

# 由于mysql 5.7之后取消了password字段，由authentication_string代替修改root密码命令
mysql> update user set authentication_string=password('xxxxx') where user='root' and host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
# mysql 8 修改root密码
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'xxxxxx';
# 更改root用户远程连接权限
mysql> update user set host = "%" where user='root';
# 更新某个用户授予服务器权限
mysql> use mysql;
Database changed
mysql> update user set Super_priv="Y" where user="xxxxx";