CentOs中的Mysql配置

用root用户安装完Mysql之后，指定了datadir为mysql用户的主目录，我这里用mysql用来启动Mysql服务。
在启动之前，先用root用户修改/etc/my.cnf文件：

[mysqld]
datadir=/var/lib/mysql/data
socket=/var/lib/mysql/mysql.sock
log-error=/var/lib/mysql/log/mysqld.log
explicit_defaults_for_timestamp=true

1. datadir目录必须为空的，因为初始化要重新生成数据库文件。
2. 每个目录必须有读写权限。
3. explicit_defaults_for_timestamp=true配置是为了禁止生成下面的警告日志：

2016-01-02T09:01:17.041119Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

然后启动Mysql，启动方式：
1. service mysqld start
2. /etc/init.d/mysqld start

第一种方式启动，如果启动用户不是root，会有很多权限问题。
我用第二种方式启动，/etc/init.d/mysqld是启动脚本，可以直接编辑。
其中，lockfile的默认配为

/var/lock/subsys/mysqld

mysql用户对subsys目录没有写的权限，为了防止出现
touch: cannot touch '/var/lock/subsys/mysqld': Permission denied
的错误，这里修改lockfile的目录：

lockfile=/var/lib/mysql/mysqld

（如果有多个my.cnf文件，想指定其中一个作为启动参数文件，修改/etc/init.d/mysqld文件中的exec="/usr/bin/mysqld_safe"，改为exec="/usr/bin/mysqld_safe --defaults-file=xxxx"，xxxx为my.cnf路径）。

开始启动Mysql：

-bash-4.1$ /etc/init.d/mysqld start
touch: cannot touch `/var/log/mysqld.log': Permission denied
chown: cannot access `/var/log/mysqld.log': No such file or directory
chmod: cannot access `/var/log/mysqld.log': No such file or directory
Starting mysqld:  [  OK  ]

报错了，果然不会让我这么快就成功。
BUT，我已经在/etc/my.cnf配置文件中修改了log-error，为什么还会出来这个原始的日志文件路径？
恩，那就用root用户创建/var/log/mysqld.log文件，看看Mysql往里面写什么东西，启动成功，没有报错：

-bash-4.1$ /etc/init.d/mysqld start
Starting mysqld:  [  OK  ]

但是/var/log/mysqld.log文件中什么也没有：

-rw-r-----  1 mysql mysql      0 Jan  2 17:01 mysqld.log

先这样吧，一会看看是哪个日志指向了这个路径。

接着登录Mysql：

-bash-4.1$ mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

记得以前用Mysql的时候，初始密码都是空的，现在怎么不行了？找不到密码了。。。
搜索解决方法，修改/etc/my.cnf，在[mysqld]下面增加一行：

skip-grant-tables

然后登录，成功：

-bash-4.1$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.10 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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> 

执行修改密码命令：

mysql> update mysql.user a set a.authentication_string = password('root'), password_expired = 'N' where user = 'root' and host = 'localhost';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

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

mysql> quit;

接下来删除/etc/my.cnf配置文件中的skip-grant-tables，
然后正常登录：

-bash-4.1$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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>