在CentOS 7下配置MySql5.7并调优教程

命令行切换图形化 init 5
图形化切换命令行 ctrl+alt+f2

安装mysql5.7
1.下载资源包，代码：

wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm 

2.添加mysql社区repo通过输入命令，代码：

sudo rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

3.使用yum方式安装mysql，代码：

yum install mysql mysql-server mysql-libs mysql-server

4.启动mysql，代码：

service mysqld start

或者

systemctl start mysqld.service

5.查看启动状态，代码：

service mysqld status

mysql远程访问连接
步骤：
1.登录mysql，初始登录不需要密码：

mysql -u root -p

2.切换数据库至mysql：

use mysql;

3.修改允许外部远程访问：

UPDATE user SET Host = '%' WHERE User= 'root' LIMIT 1;

4.输入如下命令让刚才设置的命令生效：

flush privileges;

5.最后使用exit退出mysql服务即可。

我们暂时不设置密码，因为麻烦，等会登录会报错说：Access denied for user ‘root’@‘localhost’(using password: YES)
现在Linux里面的mysql没有密码，但是设置好了允许外部远程访问
但是问题就来了，在测试连接时依旧连接失败，为什么？因为端口没有在防火墙开放。同样，在Linux里面开好防火墙对应端口
代码操作：（3306为mysql端口）8080端口也一样

firewall-cmd --permanent --zone=public --add-port=3306/tcp  
firewall-cmd --reload  
firewall-cmd --zone=public --query-port=3306/tcp 
systemctl start firewalld

修改mysql密码
1.查看 mysql 初始的密码策略
输入语句

 SHOW VARIABLES LIKE 'validate_password%';  

2.首先需要设置密码的验证强度等级，设置 validate_password_policy 的全局参数为 LOW 即可，输入设值语句

 set global validate_password_policy=LOW; 

3.当前密码长度为 8 ，如果不介意的话就不用修改了，按照通用的来讲，设置为 6 位的密码，设置 validate_password_length 的全局参数为 6 即可
输入设值语句

 set global validate_password_length=6; 

4.现在可以为 mysql 设置简单密码了，只要满足六位的长度即可
输入修改语句

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; 

可以看到修改成功，表示密码策略修改成功了。

修改配置文件

vi /etc/my.cnf

[client]
user=   
password=    

[mysqld]
########basic settings########
server-id = 11 
port = 3306
user = mysql
bind_address =  
autocommit = 0
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
datadir = /data/mysql_data
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 134217728
tmp_table_size = 67108864
tmpdir = /tmp
max_allowed_packet = 16777216
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16777216
read_rnd_buffer_size = 33554432
sort_buffer_size = 33554432
########log settings########
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
long_query_time = 2
min_examined_row_limit = 100
########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row 
relay_log = relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
########innodb settings########
innodb_page_size = 8192
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_log_group_home_dir = /redolog/
innodb_undo_directory = /undolog/
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 4G
innodb_log_buffer_size = 16777216
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 = 67108864 
########semi sync replication settings########
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

[mysqld-5.7]
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32
show_compatibility_56=on