二进制免安装方式，配置mysql

mysql 5.7.22版本 二进制包安装方法

环境标准化采样：

检查系统内是否有其他mysql
rpm -qa | grep mysql

是否存在mysql用户和用户组
grep mysql /etc/group

查看linux系统版本
[root@fp-web-112 src]# cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)
内存基本页大小
[root@fp-web-112 src]# getconf PAGE_SIZE
4096 //4m
//内核版本
[root@fp-web-112 src]# uname -a
Linux fp-web-112 3.10.0-327.el7.x86_64 #1 SMP Thu Nov 19 22:10:57 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

查看linux是32位还是64位（下载包的时候要注意）
[root@fp-web-112 src]# getconf LONG_BIT
64

查看系统负载磁盘和分区
[root@fp-web-112 src]# cat /proc/loadavg
0.32 0.26 0.14 1/581 51903

查看挂载情况(以后可能会面临磁盘空间问题)
[root@fp-web-112 src]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root 96G 22G 75G 23% /
devtmpfs 7.9G 0 7.9G 0% /dev
tmpfs 7.9G 0 7.9G 0% /dev/shm
tmpfs 7.9G 532K 7.9G 1% /run
tmpfs 7.9G 0 7.9G 0% /sys/fs/cgroup
/dev/sda1 497M 109M 388M 22% /boot
tmpfs 1.6G 0 1.6G 0% /run/user/0
//查看内存空闲,运行时间异常，或者机器混装大量应用这个时候就需要注意
[root@fp-web-112 src]# free -g
total used free shared buff/cache available
Mem: 15 0 2 0 12 14
Swap: 37 0 37

[root@fp-web-112 src]# fdisk -l //信息比较多忽略.

//查看磁盘文件格式
[root@fp-web-112 src]# lsblk -f
NAME FSTYPE LABEL UUID MOUNTPOINT
sda 

├─sda1 xfs 301c75d3-73ce-4342-befa-c8f1f788bcdf /boot
└─sda2 LVM2_member dJcKYm-10WN-Vsy8-n11y-B8wY-vsFt-N76qpy
├─centos-root xfs c73e2c36-261b-4f51-9131-4faf79d86a7d /
└─centos-swap swap b28efdea-e1a7-425d-bca0-23eac0dd22e0 [SWAP]
sdb
├─sdb1
├─sdb2
└─sdb5 swap 849e0cfd-13a3-4acf-98c3-793ddd0ccd30 [SWAP]
sr0
loop0 xfs 4f434d0b-6292-44f1-af05-ed40c74ba683
└─docker-253:0-402851692-pool
loop1
└─docker-253:0-402851692-pool

//打开文件数量
[root@fp-web-112 src]# ulimit -a

core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 64052
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 64052
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
// linux资源限制配置文件

[root@fp-web-112 src]# cat /etc/security/limits.conf

 

简单准备：

netstat -s
netstat -lntp                      # 查看所有监听端口
netstat -lntup | grep 3306         #mysql端口
ps -ef | grep mysqld               #进程端口
tcpdump -i ens192 -s 0 -l -w - dst port 3306 | strings | perl -e #抓包
netstat -anp | grep 3306 | wc -l   #当前 Mysql连接数

 

一、目录规划：

程序目录：/usr/local/mysql
数据目录：/data/mysql
PID和SOCKET文件位置：/tmp

 

二、文件下载：
官方默认只提供最新版本的，历史版本的下载地址：
https://downloads.mysql.com/archives/

5.7.22版本下载：

[root@fp-web-112 var]# wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
[root@fp-web-112 var]#  md5sum mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
[root@fp-web-112 var]# tar -xzf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@fp-web-112 var]# mv /usr/local/mysql-5.7.22-linux-glibc2.12-x86_64/ /usr/local/mysql
[root@fp-web-112 var]# echo "export PATH=$PATH:/usr/local/mysql/bin" >/etc/profile.d/mysql.sh
[root@fp-web-112 var]# source /etc/profile.d/mysql.sh

 

三、创建用户和授权：

注：-g：指定新用户所属的用户组(group)； -M：不建立根目录；-s：定义其使用的shell,/sbin/nologin代表用户不能登录系统。
useradd -u 用户id -g 组id/组名 用户名

[root@fp-web-112 var]#groupadd mysql //创建mysql组
groupadd -g 27 mysql
[root@fp-web-112 var]#useradd mysql //创建mysql用户
useradd -g mysql -s /bin/false mysql
useradd mysql -g mysql -M -s /sbin/nologin
[root@fp-web-112 var]#passwd mysql
[root@fp-web-112 var]#mkdir -p /data/mysql
[root@fp-web-112 var]#chown -R mysql:mysql /data/mysql
[root@fp-web-112 var]#chmod -R 755 /data/mysql

四、设置参数：my.cnf

[mysqld]
#basic
#skip-grant-tables
#validate_password =OFF
datadir = /data/mysql
port = 3306
pid-file = /tmp/mysql.pid
socket = /tmp/mysql.sock
log_error = error.log
slow-query-log = 1
slow-query-log-file = slow.log
long_query_time = 0.2
log-bin = mysql_bin.log
binlog_format = ROW
expire-logs-days = 1
relay-log = mysql_relay.log
relay_log_recovery = 1
general_log = on
general_log_file = general.log
lower_case_table_names = 1
server-id = 1
character_set_client_handshake = FALSE
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
init_connect ='SET NAMES utf8mb4'
interactive_timeout = 172800
wait_timeout = 172800
max_prepared_stmt_count =1048576
max_connections = 8000
#connections
max_connections=4000
key_buffer_size=200M
low_priority_updates=1
table_open_cache = 8000
back_log=1500
query_cache_type=0
table_open_cache_instances=16
#files
innodb_file_per_table =on
innodb_log_file_size=1024M
innodb_log_files_in_group = 3
innodb_open_files=4000
#buffers
innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=32
#innodb_additional_mem_pool_size=20M
innodb_log_buffer_size=64M
join_buffer_size=32K
sort_buffer_size=32K
#innodb
innodb_checksums=0
innodb_doublewrite=0
innodb_support_xa=0
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=2
innodb_max_dirty_pages_pct=50
innodb_use_native_aio=1
innodb_stats_persistent = 1
#innodb_spin_wait_delay= 6 / 96
#performance
innodb_adaptive_flushing = 1
innodb_flush_neighbors = 0
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_io_capacity = 4000
innodb_purge_threads=1
innodb_adaptive_hash_index=0
#monitor
innodb_monitor_enable = '%'
performance_schema=OFF
[mysql]
user = root

 

五、初始化数据库实例，安装mysql权限表，解压之后你最好对比data目录不同，这样你会更深刻，一旦初始化会生成很多文件。

/usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql --initialize-insecure

六、启动：

# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
# /etc/init.d/mysql start
Starting MySQL... SUCCESS!

 

七、验证和设置远程访问：

[root@node1 soft]# mysql -p -S /tmp/mysql.sock
mysql> grant all privileges on *.* to root@'%' identified by 'jinzs' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

然后重启mysql 服务就配置完成了