CentOS7 64位下MySQL5.7安装与配置（YUM）
1、配置YUM源
在MySQL官网中下载YUM源rpm安装包：http://dev.mysql.com/downloads/repo/yum/
# 下载mysql源安装包 shell> wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm # 安装mysql源 shell> yum localinstall mysql57-community-release-el7-8.noarch.rpm 检查mysql源是否安装成功 shell> yum repolist enabled | grep "mysql.*-community.*"
2、安装MySQL
shell> yum install mysql-community-server
3、启动MySQL服务
shell> systemctl start mysqld
查看MySQL的启动状态
[root@localhost ~]# systemctl status mysqld ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Tue 2018-03-27 08:45:51 CST; 2h 29min ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 911 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 861 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 972 (mysqld) CGroup: /system.slice/mysqld.service └─972 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid Mar 27 08:45:50 zabbix02 systemd[1]: Starting MySQL Server... Mar 27 08:45:51 zabbix02 systemd[1]: Started MySQL Server.
4、开机启动
shell> systemctl enable mysqld
shell> systemctl daemon-reload
5、修改root本地登录密码
mysql安装完成之后，在/var/log/mysqld.log文件中给root生成了一个默认密码。通过下面的方式找到root默认密码，然后登录mysql进行修改：
[root@zabbix02 ~]# grep 'temporary password' /var/log/mysqld.log 2018-03-26T09:30:39.349554Z 1 [Note] A temporary password is generated for root@localhost: oF#JhzeTb7Wx
shell> mysql -uroot -p mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!'; 或者 mysql> set password for 'root'@'localhost'=password('MyNewPass4!');
注意：mysql5.7默认安装了密码安全检查插件（validate_password），默认密码检查策略要求密码必须包含：大小写字母、数字和特殊符号，并且长度不能少于8位。否则会提示ERROR 1819 (HY000): Your password does not satisfy the current policy requirements错误，如下
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '111111'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
登录mysql后必须先设置有效的密码，否则在mysql的任何操作都会出现以下错误提示：
mysql> show databases; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
密码有效设置：
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Aa111111#'; Query OK, 0 rows affected (0.02 sec)
通过msyql环境变量可以查看密码策略的相关信息：
mysql> show variables like '%password%'; +---------------------------------------+--------+ | Variable_name | Value | +---------------------------------------+--------+ | default_password_lifetime | 0 | | disconnect_on_expired_password | ON | | log_builtin_as_identified_by_password | OFF | | mysql_native_password_proxy_users | OFF | | old_passwords | 0 | | report_password | | | sha256_password_proxy_users | OFF | | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | MEDIUM | | validate_password_special_char_count | 1 | +---------------------------------------+--------+ 14 rows in set (0.04 sec)
validate_password_policy：密码策略，默认为MEDIUM策略
validate_password_dictionary_file：密码策略文件，策略为STRONG才需要
validate_password_length：密码最少长度
validate_password_mixed_case_count：大小写字符长度，至少1个
validate_password_number_count ：数字至少1个
validate_password_special_char_count：特殊字符至少1个
上述参数是默认策略MEDIUM的密码检查规则。
共有以下几种密码策略：
| 策略 | 检查规则 |
|---|---|
| 0 or LOW | Length |
| 1 or MEDIUM | Length; numeric, lowercase/uppercase, and special characters |
| 2 or STRONG | Length; numeric, lowercase/uppercase, and special characters; dictionary file |
MySQL官网密码策略详细说明：http://dev.mysql.com/doc/refman/5.7/en/validate-password-options-variables.html#sysvar_validate_password_policy
修改密码策略
在/etc/my.cnf文件添加validate_password_policy配置(源文件没有此项)，指定密码策略
[root@localhost ~]# cat /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 [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 # # 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 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
# 选择0（LOW），1（MEDIUM），2（STRONG）其中一种，选择2需要提供密码字典文件
validate_password_policy=0如果不需要密码策略，添加my.cnf文件中添加如下配置禁用即可：
validate_password = off重新启动mysql服务使配置生效：
systemctl restart mysqld
6、添加远程登录用户
默认只允许root帐户在本地登录，如果要在其它机器上连接mysql，必须修改root允许远程连接，或者添加一个允许远程连接的帐户，为了安全起见，我添加一个新的帐户：
mysql> GRANT ALL PRIVILEGES ON *.* TO 'tijun'@'%' IDENTIFIED BY 'TiJun11#' WITH GRANT OPTION; Query OK, 0 rows affected, 1 warning (0.00 sec)
切换到mysql数据库，查看权限
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.03 sec) 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> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 31 rows in set (0.00 sec)
user表中查看权限
mysql> select * from user; +-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ | Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | +-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ | localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *877298E1461C92921D92A3D98972F614A2B1A8BA | N | 2018-03-27 11:28:03 | NULL | N | | localhost | mysql.session | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2018-03-26 17:30:40 | NULL | Y | | localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2018-03-26 17:30:40 | NULL | Y | | % | tijun | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *A88B08CC1688B23EC0EC102BE254970315F67236 | N | 2018-03-27 11:40:24 | NULL | N | +-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ 4 rows in set (0.00 sec)
mysql> select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | tijun |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
4 rows in set (0.00 sec)
7、配置默认编码为utf8
修改/etc/my.cnf配置文件，在[mysqld]下添加编码配置，如下所示：
[mysqld] character_set_server=utf8 init_connect='SET NAMES utf8'
重新启动mysql服务，查看数据库默认编码如下所示：
mysql> show variables like '%character%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)
默认配置文件路径：
配置文件：/etc/my.cnf
日志文件：/var/log//var/log/mysqld.log
服务启动脚本：/usr/lib/systemd/system/mysqld.service
socket文件：/var/run/mysqld/mysqld.pid
