mysql5.7 url优化_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