1. MySQL软件下载
下载地址：
http://mysql.mirror.kangaroot.net/Downloads/MySQL-5.7/
http://mirrors.163.com/mysql/Downloads/MySQL-5.7/
wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-community-client-5.7.26-1.el7.x86_64.rpm wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-community-common-5.7.26-1.el7.x86_64.rpm wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-community-devel-5.7.26-1.el7.x86_64.rpm wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-community-libs-5.7.26-1.el7.x86_64.rpm wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-community-libs-compat-5.7.26-1.el7.x86_64.rpm wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-community-server-5.7.30-1.el7.x86_64.rpm
2. MySQL安装
yum localinstall *.rpm
3. MySQL服务启动
语法：
systemctl start | stop | restart | status mysqld
# 启动MySQL服务，并将服务加入开机启动
systemctl enable mysqld
systemctl start mysqld
systemctl status mysqld
4. 登录并配置MySQL
说明：mysql在安装完成，启动mysqld服务后，MySQL会自动生成一个随机的root用户密码，可以到日志里面查看。
[root@cn-prom ~]# vim /var/log/mysqld.log 2020-09-16T01:48:30.469772Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-09-16T01:48:30.760416Z 0 [Warning] InnoDB: New log files created, LSN=45790 2020-09-16T01:48:30.809246Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2020-09-16T01:48:30.868591Z 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: b90cb3b5-f7be-11ea-8843-00505687264b. 2020-09-16T01:48:30.869372Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2020-09-16T01:48:31.540430Z 0 [Warning] CA certificate ca.pem is self signed. 2020-09-16T01:48:31.716721Z 1 [Note] A temporary password is generated for root@localhost: l5s;PyK%(odX 2020-09-16T01:48:34.315475Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-09-16T01:48:34.317738Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.30) starting as process 14208 ...
可以看到类似如下信息：
2020-03-03T06:24:40.573789Z 1 [Note] A temporary password is generated for root@localhost: l5s;PyK%(odX
说明：用该临时密码登录mysql后，必须要先修改密码
[root@mydb-server001 opt]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 10 Server version: 5.7.26 Copyright (c) 2000, 2019, 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> show databases; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> set password = PASSWORD('cbMiu3@db.com'); ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
说明：如果不符合MySQL密码策略要求，会出现上述错误。
MySQL密码复杂度配置是通过参数validate_password_policy来控制的，你可以去修改，但是建议你不要修改（如果要修改，参考下文描述）。
validate_password_policy 有以下取值：
mysql> SHOW VARIABLES LIKE 'validate_password%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | 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 | +--------------------------------------+--------+ 7 rows in set (0.00 sec)
Policy Tests Performed
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
默认是1，即MEDIUM，所以设置MySQL的密码必须不小于8位，且必须含有数字，小写或大写字母，特殊字符。
5. 修改mysql密码
mysql> set password = PASSWORD('NMm#t87TO2JL&Zq2'); mysql> set password = PASSWORD('cbMiu936@mydb.com'); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql>
6. 设置mysql的中文编码支持
# 修改/etc/my.cnf
vim /etc/my.cnf
在[mysqld]中添加参数，使得mariadb服务端支持中文
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
character_set_server = utf8
collation-server = utf8_bin
# 重启mariadb服务，读取my.cnf新配置
systemctl restart mysqld
# 登录数据库，查看字符编码
mysql -uroot -p
输入 s 查看编码
7. mysql常用命令
desc #查看表结构 create database #数据库名 create table #表名 # 查看如何创建db的 show create database #库名 # 查看如何创建table结构的 show create table 表名; # 修改mysql的密码 set password = PASSWORD('mysqlrootuser686'); # 创建mysql的普通用户，默认权限非常低 create user mcb@'%' identified by 'changbin.miao'; # 查询mysql数据库中的用户信息 use mysql; select host,user,password from user; --模糊查询匹配 show status like 'Thread_%'; SHOW VARIABLES LIKE 'validate_password%'; 如果我们的show status语句中不包含统计范围关键字，则默认统计范围为SESSION，也就是只统计当前连接的状态信息。 如果我们需要查询自当前MySQL启动后所有连接执行的SELECT语句总数，我们可以执行如下语句： show global status like 'com_select'; --查看MySQL本次启动后的运行时间(单位：秒) show status like 'uptime'; --查看select语句的执行数 show [global] status like 'com_select'; --查看insert语句的执行数 show [global] status like 'com_insert'; --查看update语句的执行数 show [global] status like 'com_update'; --查看delete语句的执行数 show [global] status like 'com_delete'; --查看试图连接到MySQL(不管是否连接成功)的连接数 show status like 'connections'; --查看线程缓存内的线程的数量。 show status like 'threads_cached'; --查看当前打开的连接的数量。 show status like 'threads_connected'; --查看当前打开的连接的数量。 show status like 'threads_connected'; --查看创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。 show status like 'threads_created'; --查看激活的(非睡眠状态)线程数。 show status like 'threads_running'; --查看立即获得的表的锁的次数。 show status like 'table_locks_immediate'; --查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制。 show status like 'table_locks_waited'; --查看创建时间超过slow_launch_time秒的线程数。 show status like 'slow_launch_threads'; --查看查询时间超过long_query_time秒的查询的个数。 show status like 'slow_queries';
8. 给用户添加权限
# 对所有库和所有表授权所有权限
grant all privileges on *.* to 账户@主机名
# 给mcb用户授予所有权限
grant all privileges on *.* to mcb@'%';
# 刷新授权表
flush privileges;
9. 授予远程登录权限
# 给apollo用户授予所有权限
grant all privileges on *.* to mcb@'%';
# 给与root权限授予远程登录的命令
# centos这是密码随意设置
grant all privileges on *.* to root@'%' identified by 'centos';
# 此时可以在windows登录linux的数据库
# 连接服务器的mysql
mysql -umcb -p -h 服务器的地址