2020/06/03 安装mysql5.7.30

1.检测系统是否自带mysql

[root@localhost /]# rpm -qa | grep mysql
[root@localhost /]# rpm -qa | grep mariadb

2.如果是，则使用下面命令进行删除：

[root@localhost /]# rpm -e --nodeps ‘上一步查找的名称’  　// 强力删除模式，如果使用上面命令删除时，提示有依赖的其它文件，则用该命令可以对其进行强力删除

3.删除成功后，查询所有Mysql对应的文件夹,删除查找的所有文件夹

[root@localhost ~]# whereis mysql
mysql: /usr/bin/mysql /usr/local/mysql
[root@localhost ~]# find / -name mysql
/run/lock/subsys/mysql
/etc/rc.d/init.d/mysql
/usr/bin/mysql
/usr/local/mysql
/usr/local/mysql/bin/mysql
/usr/local/mysql/include/mysql
/usr/local/mysql/data/mysql

4.下载安装包，一般放在/usr/local/src下

https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
tar xvf mysql-5.7.30-linux-glibc2.12-x86_64\ \(1\).tar.gz  

5.创建mysql用户

[root@node5 ~]# groupadd mysql
[root@node5 ~]# useradd -r -g mysql -s /bin/false mysql

6.修改解压目录名称

[root@node5 ~]# mv mysql-5.7.30-linux-glibc2.12-x86_64 /usr/local/mysql
[root@node5 mysql]# chown -R mysql.mysql /usr/local/mysql/    #可有可无

7.加入path变量

[root@localhost ~]# vim /etc/profile
#添加下面一行
export PATH=$PATH:/usr/local/mysql/bin
[root@localhost ~]# source /etc/profile
[root@node5 ~]# chkconfig --add mysql 
[root@node5 ~]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysql           0:off   1:off   2:on    3:on    4:on    5:on    6:off
netconsole      0:off   1:off   2:off   3:off   4:off   5:off   6:off
network         0:off   1:off   2:on    3:on    4:on    5:on    6:off

8.目录下创建data目录和basedir，和其他准备好的目录

[root@node5 mysql]# mkdir -p /data/mysql/data
[root@node5 mysql]# mkdir -p /data/mysql/logs
[root@node5 mysql]# mkdir -p /data/mysql/mysql/tmp
[root@node5 mysql]# chown -R  mysql.mysql /data/mysql

9.编译安装并初始化mysql,确保/etc/my.cnf文件不存在，否则会出现问题

[root@node5 mysql]# ./bin/mysqld --initialize --user=mysql --datadir=/data/mysql/data --basedir=/usr/local/mysql
2020-06-03T02:35:19.965501Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-06-03T02:35:21.957991Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-06-03T02:35:22.081331Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-06-03T02:35:22.338628Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: df713c56-a542-11ea-93bd-000c29d94b11.
2020-06-03T02:35:22.339895Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-06-03T02:35:22.654337Z 0 [Warning] CA certificate ca.pem is self signed.
2020-06-03T02:35:22.778157Z 1 [Note] A temporary password is generated for root@localhost: (MeW%o3==Fsl  #这段是密码

10.开启ssl连接：

[root@localhost mysql]# bin/mysql_ssl_rsa_setup --datadir=/data/mysql/data

11.编辑配置文件my.cnf，添加配置如下

[root@node5 mysql]# vim /etc/my.cnf

[client]
port= 3306
socket  = /tmp/mysql.sock
[mysql]
prompt="\\u@\\h [\\d]>"
#pager="less -i -n -S"
#tee=/opt/mysql/query.log
no-auto-rehash

[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/data
#socket=/data/mysql/data/mysql.sock
user=mysql
port=3306
default-storage-engine=InnoDB
#skip-grant-tables

socket = /tmp/mysql.sock
event_scheduler = 0

tmpdir = /data/mysql/mysql/tmp
#timeout
interactive_timeout = 300
wait_timeout = 300

##character set
character-set-server = utf8

open_files_limit = 65535
max_connections = 1000
max_connect_errors = 100000
lower_case_table_names =1
#file
#@secure-file-priv=/tmp
#symi replication

#rp;-semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000 # 1 second
##rpl_semi_sync_slave_enabled=1

#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = /data/mysql/data/mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
##log-queries-not-using-indexes = 1
log-slow-slave-statements = 1

#binlog
#binlog_format = STATEMENT
binlog_format = row
server-id = 1003306
log-bin = /data/mysql/logs/mysql-bin
max_binlog_size = 256M
sync_binlog = 0
expire_logs_days = 10
#procedure
log_bin_trust_function_creators=1

##file
secure_file_priv="/tmp"
#
gtid-mode = on
enforce-gtid-consistency=1


#relay log
skip_slave_start = 1
max_relay_log_size = 128M
relay_log_purge = 1
relay_log_recovery = 1
relay-log=relay-bin
relay-log-index=relay-bin.index
log_slave_updates

#slave-skip-errors=1032,1053,1062
#skip-grant-tables

#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 128K
join_buffer_size = 128K
thread_cache_size = 200
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
#innodb_data_file_path = ibdata1:100M:autoextend  有时候会出现问题,所以注释
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 100M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

[root@node5 mysql]# chown mysql.mysql /etc/my.cnf  #可有可无

12.添加软连接，并重启mysql服务

[root@node5 mysql]# cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysql
[root@node5 mysql]# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql  
[root@node5 mysql]# service mysql  start
Starting MySQL.Logging to '/data/mysql/data/mysql-error.log'.
 SUCCESS! 

可以直接选择跑安全脚本

[root@node5 mysql]# /usr/local/mysql/bin/mysql_secure_installation 
mysql_secure_installation: [ERROR] unknown variable 'prompt=\u@\h [\d]>'

Securing the MySQL server deployment.

Enter password for user root: 

The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password: 

13.连接mysql

[root@node5 mysql]# mysql -uroot -puLsjLa.aO1Tm
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.30

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databses;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

14.出现这样的错误，在配置文件加上skip-grant-tables，修改root密码

[root@node5 mysql]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql/data
socket=/data/mysql/data/mysql.sock
user=mysql
default-storage-engine=InnoDB
skip-grant-tables  

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

[client]
socket=/data/mysql/data/mysql.sock

[root@node5 ~]# service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

[root@node5 mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> 
mysql> update mysql.user set authentication_string=password('root') where user= 'root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

15.注释掉skip-grant-tables ，跑一下安全脚本

[root@node5 ~]# service mysql restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@node5 ~]#/usr/local/mysql/bin/mysql_secure_installation 

16.现在就可以正常使用

[root@node5 mysql]# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database abc;
Query OK, 1 row affected (0.00 sec)

安装中遇到的问题

在第10步如果直接启动

[root@node5 mysql]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
[root@node5 mysql]# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
[root@node5 mysql]# service mysql start
Starting MySQL.Logging to ‘/data/mysql/data/error.log’.
. ERROR! The server quit without updating PID file (/data/mysql/data/mysql.pid).
[root@node5 mysql]# cat /data/mysql/data/error.log
2020-06-03T02:53:15.908837Z 0 [Warning] The syntax ‘–log_warnings/-W’ is deprecated and will be removed in a future release. Please use ‘–log_error_verbosity’ instead.
2020-06-03T02:53:15.947581Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-06-03T02:53:15.949565Z 0 [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
2020-06-03T02:53:15.949601Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.30-log) starting as process 14568 …
mysqld: File ‘/data/mysql/logs/mysql-bin.index’ not found (Errcode: 2 - No such file or directory)
2020-06-03T02:53:15.969589Z 0 [ERROR] Aborting

2020-06-03T02:53:15.969602Z 0 [Note] Binlog end
2020-06-03T02:53:15.969745Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

解决方法 ：

[root@node5 mysql]# chmod -R 755 /data/mysql

2.遇到启用#innodb_data_file_path = ibdata1:100M:autoextend ，启动服务遇到
Starting MySQL. ERROR! The server quit without updating PID file (/data/mysql/data/mysql.pid).

查看日志

[root@baiduyun-test mysql]# cat /data/mysql/data/error.log   
 [ERROR] InnoDB: The Auto-extending innodb_system data file './ibdata1' is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 6400 pages, max 0 (relevant if non-zero) pages! 有这一段

解决方法

#cd /var/lib/mysql
#rm -rf /var/lib/mysql/ib*

主要删除：ibdata1、ib_logfile0、ib_logfile1文件
root cause
之前有安装mysql-server5.1删除文件重构后，异常解决

重启验证

#/etc/init.d/mysqld restart

**所以一般注释这条innodb_data_file_path = ibdata1:100M:autoextend，具体问题还在查找** 

update mysql.user set authentication_string=password(‘root’) where user= ‘root’;