centos7 安装mysql5.7，并优化

1.下载mysql

wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

2.安装mysql

安装mysql安装源

yum -y localinstall mysql57-community-release-el7-11.noarch.rpm
安装MySQL 
yum -y install mysql-community-server

3.启动mysql

systemctl start mysqld
systemctl enable mysqld

4.从/var/log/mysqld.log获取mysql初始密码

grep password /var/log/mysqld.log

5.修改mysql密码，并开启远程访问

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Test2020@';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Test2020@' WITH GRANT OPTION;

6.防火墙开启端口

firewall-cmd --zone=public --add-port=3306/tcp --permanent

7.backup的脚本。每天晚上3点备份至一个挂载的NFS上面，备份到/data/mysql/backup目录下

垃圾太慢了！！！看下一个

1.设置yum源

vim mysql.repo

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql57-community-el7/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

2.安装mysql

yum -y install mysql-community-server

3.修改密码，开启远程访问

systemctl start mysqld
systemctl enable mysqld
awk '/temporary password/{print $NF}' /var/log/mysqld.log
mysql -uroot -p

ALTER USER 'root'@'localhost' IDENTIFIED BY 'test2020@!';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'test2020@!' WITH GRANT OPTION;

4-2.建立新的数据目录

mkdir /data/mysql/data

4-3.关闭服务

systemctl stop mysqld.service

4-4.迁移数据目录

cp -r /var/lib/mysql/* /data/mysql/data

4-5设置文件夹的属主和权限

chown -R mysql /data/mysql/data
chgrp -R mysql /data/mysql/data
chmod -R g+rw /data/mysql/data

4-6修改配置文件

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[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 = 6G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 16

# 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
skip-host-cache
skip-name-resolve
datadir=/data/mysql/data
#datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#socket=/data/mysql/data/mysql.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
port = 7846
# Innodb_file_per_table = on
default_authentication_plugin=mysql_native_password
explicit_defaults_for_timestamp = 1
back_log = 500
default-time-zone = '+08:00'

 slow-query-log = 1
 log-queries-not-using-indexes  = 1

 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
 long_query_time=1
 max_connect_errors = 20000
 max_connections = 2000
 wait_timeout = 36000
 interactive_timeout = 36000
 net_read_timeout = 36000
 net_write_timeout = 36000
 table_open_cache = 1024
 table_definition_cache = 1024
 thread_cache_size = 512
 open_files_limit = 65535
 character-set-server = utf8
 collation-server = utf8_bin
 skip_external_locking
 performance_schema = 1
 myisam_recover_options = DEFAULT
 skip-name-resolve
 local_infile = 0
 lower_case_table_names = 0
 expire-logs-days  = 7 
 sync-binlog = 1
 slave-net-timeout  = 60
 tmp-table-size                 = 32M
 max-heap-table-size            = 32M
 max-connections                = 500
 thread-cache-size              = 50
 open-files-limit               = 65535
 table-definition-cache         = 1024
 table-open-cache               = 2048
 innodb-flush-method            = O_DIRECT
 innodb-log-files-in-group      = 2
 innodb-log-file-size           = 128M
 innodb-flush-log-at-trx-commit = 1
 innodb-buffer-pool-size        = 2G
server_id = 100 
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

启动MySQL服务

systemctl start mysqld

查看错误日志

tail -n 1000 /var/log/mysqld.log -f