Linux下MySQL安装与配置

1、下载安装包
http://dev.mysql.com/downloads/mysql/#downloads
推荐下载通用安装方法的TAR包
http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.12-linux-glibc2.5-x86_64.tar

2、检查库文件是否存在，如有删除。

[root@localhost Desktop]$ rpm -qa | grep mysql
mysql-libs-5.1.52-1.el6_0.1.x86_64
[root@localhost ~]# rpm -e mysql-libs-5.1.52.x86_64 --nodeps
[root@localhost ~]#

3、检查mysql组和用户是否存在，如无创建

[root@localhost ~]# cat /etc/group | grep mysql
mysql:x:490:
[root@localhost ~]# cat /etc/passwd | grep mysql
mysql:x:496:490::/home/mysql:/bin/bash

默认存在的情况，如无，执行添加命令：

[root@localhost ~]#groupadd mysql
[root@localhost ~]#useradd -r -g mysql mysql

useradd -r参数表示mysql用户是系统用户，不可用于登录系统。

4、解压TAR包，更改所属的组和用户

[root@localhost ~]# cd /usr/local/
[root@localhost local]# tar xvf mysql-5.7.12-linux-glibc2.5-x86_64.tar
[root@localhost local]# ls -l
total 1306432
-rwxr--r--. 1 root root  668866560 Jun  1 15:07 mysql-5.7.12-linux-glibc2.5-x86_64.tar
-rw-r--r--. 1 7161 wheel 638960236 Mar 28 12:54 mysql-5.7.12-linux-glibc2.5-x86_64.tar.gz
-rw-r--r--. 1 7161 wheel  29903372 Mar 28 12:48 mysql-test-5.7.12-linux-glibc2.5-x86_64.tar.gz
[root@localhost local]# tar xvfz mysql-5.7.12-linux-glibc2.5-x86_64.tar.gz
[root@localhost local]# mv mysql-5.7.12-linux-glibc2.5-x86_64 mysql
[root@localhost mysql]# ls -l
total 60
drwxr-xr-x  2 root root   4096 Mar 29  2016 bin
-rw-r--r--  1 root root  17987 Mar 29  2016 COPYING
drwxr-xr-x  2 root root   4096 Mar 29  2016 docs
drwxr-xr-x  3 root root   4096 Mar 29  2016 include
drwxr-xr-x  5 root root   4096 Mar 29  2016 lib
drwxr-xr-x  4 root root   4096 Mar 29  2016 man
-rw-r--r--  1 root root   2478 Mar 29  2016 README
drwxr-xr-x 28 root root   4096 Mar 29  2016 share
drwxr-xr-x  2 root root   4096 Jul  1 14:55 support-files

[root@localhost local]# cd mysql/
[root@localhost mysql]# mkdir log
[root@localhost mysql]# chown -R mysql:root ../mysql/

5、安装和初始化数据库

[root@localhost mysql]# ./bin/mysqld --no-defaults --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/

2017-07-01T06:41:07.892996Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-07-01T06:41:08.194570Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-07-01T06:41:08.253981Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-07-01T06:41:08.313863Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 438d7d5f-5e28-11e7-adcf-005056aa1271.
2017-07-01T06:41:08.315698Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-07-01T06:41:08.316486Z 1 [Note] A temporary password is generated for root@localhost: tM:_oqoh(8hx

6、创建mysqld起动和配置文件

[root@localhost mysql]#
[root@localhost mysql]# cp -a ./support-files/my-default.cnf /etc/my.cnf
[root@localhost mysql]# cp -a ./support-files/mysql.server  /etc/init.d/mysqld
//修改配置
[root@localhost mysql]# vim /etc/init.d/mysqld
//修改以下两项
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
//修改自己的配置文件，比如日志什么的
[root@localhost mysql]# vim /etc/my.cnf
//开启SSL连接
[root@localhost mysql]# ./bin/mysql_ssl_rsa_setup  --datadir=/usr/local/mysql/data
//设置开机启动
[root@localhost bin]# chkconfig --level 35 mysqld on
[root@localhost bin]#

另一种很好的设置MySQL自启动的方式：

[root@localhost bin]# echo "service mysqld start" >> /etc/rc.local

或者进入/etc/目录，直接vim rc.local编辑rc.local文件，在最后一行添加“service mysqld start”,保存退出

有时会遇到权限问题：
bash: /etc/rc.local: Permission denied
分析：
bash 返回 /etc/rc.local: Permission denied
    这是因为重定向符号 “>” 也是 bash 的命令。sudo 只是让 echo 命令具有了 root 权限，
    但是没有让 “>” 命令也具有root 权限，所以 bash 会认为这个命令没有写入信息的权限。

解决：
使用 bash -c 参数
[root@localhost bin]# sudo bash -c "echo "service mysqld start" >> /etc/rc.local"

6.初始化密码
mysql5.7会生成一个初始化密码，而在之前的版本首次登陆不需要登录。

[root@localhost bin]# cat /root/.mysql_secret 
# Password set for user 'root@localhost' at 2016-06-01 15:23:25 
,xxxxxR5H9
[root@localhost bin]# ./mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

上面一步中，如果出现了提示密码过期，可用如下方法解决：

[root@localhost bin]# /usr/local/mysql/bin/mysqladmin -u root -p password
Enter password:
New password:
Confirm new password:
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
或者：
/usr/local/mysql/bin/mysqladmin -u root -p'<your temp password>' password '<your new password>'

7.添加远程访问权限

mysql> use mysql; 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select host, user from user;
+-----------+-----------+
| host      | user      |
+-----------+-----------+
| %         | root      |
| localhost | mysql.sys |
+-----------+-----------+

9、更改配置文件和服务的权限
此步一定要改，我当初没改就一直提示说是有一个服务绑定了3306，死都找不出来问题。
先添加环境变量：

[root@localhost bin]# vim /etc/profile
最后一行填加：
MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin 
让修改立即生效：
[root@localhost bin]# source /etc/profile 

再修改两个文件的权限：

[root@localhost bin]# 
[root@localhost bin]# service mysqld stop 
[root@localhost bin]# chown -R root:root /etc/init.d/mysqld
[root@localhost bin]# chown -R root:root /etc/my.cnf

10、修改配置文件

[root@localhost bin]# vim /etc/my.cnf
我的配置文件如下：
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
#default-character-set=utf8
#loose-default-character-set=utf8
#避免MySQL的外部锁定，减少出错几率增强稳定性。
socket = /tmp/mysql.sock

[mysql]
local-infile=1
loose-local-infile=1

[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 = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3017
server_id = 1
#default-character-set=utf8
character_set_server=utf8
init_connect='SET NAMES utf8'
#skip-grant-tables

#打开federated存储引擎
federated
#每次重启MySQL之后EVENT自动启动
event_scheduler=1
#主从复制相关变量
gtid-mode = on #打开基于gitd复制的功能
enforce-gtid-consistency = on
skip_slave_start = 1 #数据库起来后不能直接开启复制
log-slave-updates = ON
rpl_semi_sync_master_enabled=1   #打开半同步复制 
rpl_semi_sync_master_timeout=10000   #此单位是毫秒

lower_case_table_names=1
#避免MySQL的外部锁定，减少出错几率增强稳定性。
socket = /tmp/mysql.sock
skip-external-locking
skip-name-resolve
skip-show-database

innodb_buffer_pool_size=256M #InnoDB引擎缓冲区
query_cache_size=16M #查询缓存
tmp_table_size=64M #临时表大小
key_buffer_size=32M
max_allowed_packet = 64M
max-connections=1800 
max_user_connections=0 #不限制单用户的最大连接数，其最大连接值可以等于max_connections值

###########Log Setting#############
log_bin=/usr/local/mysql/log/master-bin.log
binlog_format = MIXED
log_error=/usr/local/mysql/log/error.log
long_query_time=0.3 #300毫秒
#是否记录为使用索引的SQL
log_queries_not_using_indexes = ON 
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/log/slowquery.log
general_log=ON
general_log_file=/usr/local/mysql/log/general.log
expire_logs_days = 10
#binlog-do-db=db1
#binlog-do-db=db2
#binlog-do-db=db3
binlog-ignore-db=mysql
# socket = .....

innodb_flush_log_at_trx_commit = 2
# 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 

sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE

修改后保存退出

11、重启生效

[root@localhost bin]# /etc/init.d/mysqld restart
[root@localhost bin]# netstat -na | grep 3306，如果看到有监听说明服务启动了