Linux 下 MySQL 5.7安装、运行

1、安装之前先删除已有版本MySQL
先查询
rpm -qa|grep mariadb
rpm -qa|grep mysql
再删除
rpm -e --nodeps [粘贴上述命令列出的内容]

安装libaio

yum install libaio

2、下载版本
地址：https://downloads.mysql.com/archives/community/
选择版本：5.7.25
选择Linux Generic，X86_64
通过加压tar包的方式进行安装

#貌似地址失效
wget https://downloads.mysql.com/archives/get/file/mysql-5.7.25-linux-glibc2.12-x86_64.tar
#新地址
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.25-el7-x86_64.tar.gz

3、创建mysql用户、组
先确认用户、组是否存在
cat /etc/group | grep mysql
cat /etc/passwd | grep mysql
创建组
groupadd mysql
创建用户
useradd -g mysql mysql
如有必要可设置mysql用户密码

4、解压文件，目标目录/usr/local/mysql
这不是rpm等安装包，而是解压安装模式，所以这没什么好说的

5、创建相应的数据、日志目录，并更改所属的组和用户

pid-file = /usr/local/mysql/mysql.pid
basedir    = /usr/local/mysql
datadir    = /data/db/mysql/
log-error = /data/db/mysql/logs/error.log

mkdir -p /data/db/mysql
mkdir -p /usr/local/mysql/logs
更改所属的组和用户
chown -R mysql:mysql /usr/local/mysql

chown -R mysql:mysql /data/db/mysql

6、修改/etc/my.cnf
没有的话拷贝一份到/etc下

[client]
port    = 3306
socket    = /usr/local/mysql/mysql.sock
default-character-set = utf8mb4
 
[mysqld]
#skip-grant-tables
#修改时区
default-time_zone = '+8:00'

user    = mysql
port    = 3306
socket    = /usr/local/mysql/mysql.sock
server-id = 1
symbolic-links=0
local-infile=0

pid-file = /usr/local/mysql/mysql.pid
basedir    = /usr/local/mysql
datadir    = /data/db/mysql/
#log-error = /usr/local/mysql/logs/error.log

character_set_server = utf8mb4
collation_server = utf8mb4_bin
back_log = 1024
explicit_defaults_for_timestamp = ON
lower_case_table_names = 0
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 
max_connections = 2000
max_connect_errors = 1000000
table_open_cache = 1024

max_allowed_packet = 64M
max_heap_table_size = 256M
tmp_table_size = 256M

thread_stack = 256K
thread_cache_size = 384
 
skip-external-locking
 
interactive_timeout = 600
wait_timeout = 3600

default_storage_engine = InnoDB
#innodb_buffer_pool_size = 64M

#innodb_buffer_pool_size = 8G
innodb_buffer_pool_size = 4G
innodb_buffer_pool_chunk_size = 384M
innodb_buffer_pool_instances = 8
innodb_thread_concurrency = 16

innodb_purge_threads = 1
innodb_log_buffer_size = 2M
innodb_log_file_size = 128M
innodb_lock_wait_timeout = 120
 
bulk_insert_buffer_size = 32M
myisam_sort_buffer_size = 8M
#myisam_max_sort_file_size = 10G
myisam_max_sort_file_size = 4G
myisam_repair_threads = 1

#--------------log setting ------------------------------
log_timestamps = SYSTEM
log-error = /usr/local/mysql/logs/error.log

log_output=file
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/logs/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

#--------------mysql replication master ------------------------------
symbolic-links=0
skip_name_resolve = 1

log-bin = /usr/local/mysql/logs/mysql-bin.log
expire-logs-days = 14
max-binlog-size = 500M
#mixed、row
binlog_format = row
#binlog-do-db=
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log = /usr/local/mysql/logs/mysql-relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1

loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

#---------mysql replication slave---------------------------
#log-slave-updates=0
##binlog-do-db=
#replicate-ignore-db=mysql
#replicate-ignore-db=information_schema
#replicate-ignore-db=performance_schema
#replicate-ignore-db=sys
#slave-skip-errors=all
#slave-net-timeout=60
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

7、初始化MySQL
初始化mysql，会在datadir目录下生成mysql的数据文件
执行以下命令

bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/db/mysql --pid-file=/usr/local/mysql/mysql.pid
bin/mysql_ssl_rsa_setup  --datadir=/data/db/mysql

如果不成功，查找问题，重来。

8、设置MySQL开机启动
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
修改权限
chown 777 /etc/my.cnf
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld

9、运行MySQL
service mysqld start | stop | status

10、加入环境变量
vi /etc/profile

export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin/

source /etc/profile

11、其他

update user set authentication_string=password('.嘻嘻嘻嘻嘻嘻) where User='root'; 
update user set password_expired='N' where user='root';  
CREATE USER 'test'@'114.%' IDENTIFIED BY '嘻嘻嘻嘻嘻嘻';
flush privileges;

12、修改时区

set global time_zone = '+8:00';
set time_zone = '+8:00'; 
 flush privileges; 

13、MySQL表分区

# MySQL表分区
ALTER TABLE test partition by hash(year(record_date)) PARTITIONS 8;
#移除分区：
ALTER TABLE test REMOVE PARTITIONING ;
# 创建数据库
CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;

14、mysql_config_editor
mysql_config_editor允许你把登录的身份验证信息存储在一个名为.mylogin.cnf的文件里

mysql_config_editor set --login-path=login-backup --user=user_backup --password --host=localhost

15、启动慢日志
vi /etc/my.cnf

slow_query_log = 1
slow_query_log_file = /usr/local/mysql/logs/mysql-slow.log
long_query_time = 2

chown mysql:mysql /usr/local/mysql/logs/mysql-slow.log

SHOW VARIABLES LIKE 'slow_query%';
SET GLOBAL slow_query_log='ON'; 

16、mysqldump导出

导出表的数据及结构

mysqldump  mydb -u root -p --tables mytable >/data/mytable.sql

导出指定表的结构

mysqldump -d mydb -u root -p --tables mytable> /data/mytable.sql

导出指定表的数据

 mysqldump -t mydb -u root -p --tables mytable> /data/mytable.sql