一、MySQL5.6 安装和配置

697 阅读14分钟

1. MySQL简介

MySQL是一种关系型数据库,由瑞典 MySQL AB 公司开发,目前属于Oracle公司

MySQL是一种关联数据库管理系统,将数据保存在不同的表中,而不是将所有的数据都放在一个大仓库中,这样增加了灵活性。

MySQL是开源的,所以不需要付费。

MySQL支持大型数据库,可以处理上千万的记录的大型数据库。支持5000万条记录的数据仓库,32位的系统表文件最大支持4GB,64位系统最大支持的表文件为8TB。

MySQL使用标准的SQL查询语言形式。

MySQL可以允许多个系统上,并且支持多种语言。支持的语言包括 C、C++、Python、Java、PHP 等。

MySQL是可以定制的,采用GPL协议,可以修改源码来开发自己的MySQL系统。

MySQL 的高级操作:

  • MySQL内核调优

  • SQL优化工程师

  • MySQL服务器优化

  • MySQL各种参数常量设定

  • 查询语句优化

  • 主从复制

  • 软硬件升级

  • 容灾备份

  • SQL编程

2. Linux 安装 MySQL

基于MySQL 5.5.65 的 rpm 安装教程,后续会更新基于 MySQL 5.7.X 的安装教程

2.1 安装前检查

安装前检查Linux中是否已经安装MySQL,执行以下命令。

[root@izuf6h7blj36dmxbmcj15wz mysql]# rpm -qa|grep mariadb
mariadb-libs-5.5.65-1.el7.x86_64

先卸载系统中自带的 mariadb,执行以下命令。没有任何返回则证明卸载完成。

[root@izuf6h7blj36dmxbmcj15wz mysql]# rpm -e --nodeps mariadb-libs
[root@izuf6h7blj36dmxbmcj15wz mysql]# rpm -qa|grep mariadb

默认 Linux (CentOS 7) 在安装时,自带了 mariadb (MySQL 完全开源版本) 相关组件。

2.2 安装 MySQL

2.2.1 下载软件包

MySQL官网找到所需的对应版本下载,下载下图中红框中的内容。

image-20210402083716176

使用FTP软件将下载好的安装包传输到Linux中,存放到 /usr/local/mysql 目录下

上面两步骤也可以使用命令 wget 实现

切换到目录 cd /usr/local/mysql

使用以下命令下载:

wget https://downloads.mysql.com/archives/get/p/23/file/MySQL-server-5.5.48-1.linux2.6.x86_64.rpm

wget https://downloads.mysql.com/archives/get/p/23/file/MySQL-client-5.5.48-1.linux2.6.x86_64.rpm

2.2.2 使用 rpm 命令安装MySQL

安装前检查是否已经安装过MySQL rpm -qa|grep mysql,如果没安装过则不会有任何提示,如果安装过会显示安装过的版本。

安装 MySQL server,命令 rpm -ivh MySQL-server-5.5.48-1.linux2.6.x86_64.rpm

安装 MySQL client,命令 rpm -ivh MySQL-client-5.5.48-1.linux2.6.x86_64.rpm

安装过程中出现问题请参考:安装MySQL出现问题

MySQL server 安装完成后提示以下内容:

# 安装 MySQL-Server
[root@izuf6h7blj36dmxbmcj15wz mysql]# rpm -ivh MySQL-server-5.5.48-1.linux2.6.x86_64.rpm 
warning: MySQL-server-5.5.48-1.linux2.6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:MySQL-server-5.5.48-1.linux2.6   ################################# [100%]
210402 15:03:39 [Note] /usr/sbin/mysqld (mysqld 5.5.48) starting as process 3134 ...
210402 15:03:39 [Note] /usr/sbin/mysqld (mysqld 5.5.48) starting as process 3141 ...

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h izuf6h7blj36dmxbmcj15wz password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

Please report any problems at http://bugs.mysql.com/
# 安装 MySQL-Client
[root@izuf6h7blj36dmxbmcj15wz mysql]# rpm -ivh MySQL-client-5.5.48-1.linux2.6.x86_64.rpm 
warning: MySQL-client-5.5.48-1.linux2.6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:MySQL-client-5.5.48-1.linux2.6   ################################# [100%]

2.2.3 查看 MySQL 是否安装成功

[root@izuf6h7blj36dmxbmcj15wz mysql]# mysqladmin --version
mysqladmin  Ver 8.42 Distrib 5.5.48, for Linux on x86_64
[root@izuf6h7blj36dmxbmcj15wz mysql]# rpm -qa|grep MySQL
MySQL-server-5.5.48-1.linux2.6.x86_64
MySQL-client-5.5.48-1.linux2.6.x86_64

使用用户组查看

# 查看 MySQL 用户名信息
[root@izuf6h7blj36dmxbmcj15wz mysql]# cat /etc/passwd|grep mysql
mysql:x:994:991:MySQL server:/var/lib/mysql:/bin/bash
# 查看 MySQL 用户组信息
[root@izuf6h7blj36dmxbmcj15wz mysql]# cat /etc/group|grep mysql
mysql:x:991:

2.3 启动 MySQL 服务

启动 MySQL 服务:service mysql start

停止 MySQL 服务:service mysql stop

重启 MySQL 服务:service mysql restart

查看服务是否启动: ps -ef|grep mysql

若出现启动多个 MySQL 进程的问题则杀死 MySQL 的全部进程,然后重新启动 MySQL。

杀死进程命令 killall mysqld

2.3.1 设置 root 用户密码

[root@izuf6h7blj36dmxbmcj15wz mysql]# /usr/bin/mysqladmin -u root password
New password: 
Confirm new password: 

注意:此处输入的密码是不显示的!

测试修改后的密码是否成功

# 直接输入 mysql 不能进入数据库会提示访问失败
[root@izuf6h7blj36dmxbmcj15wz mysql]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
# 使用用户名密码登录到 mysql
[root@izuf6h7blj36dmxbmcj15wz mysql]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.48 MySQL Community Server (GPL)

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> 

2.3.2 设置开机自启动

[root@izuf6h7blj36dmxbmcj15wz mysql]# chkconfig mysql on
[root@izuf6h7blj36dmxbmcj15wz mysql]# chkconfig --list|grep mysql

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysql           0:off   1:off   2:on    3:on    4:on    5:on    6:off

数字所代表的含义:

0:关机级别;

1:单用户运行级别,运行rc.sysinit和rc1.d目录下的脚本;

2:多用户,但系统不会启动NFS,字符模式,在有些linux系统中,级别2为默认模式,具有网络功能,如ubuntu.debian

3:多用户,字符模式,系统启动具有网络功能,redhat常用运行级别;

4:用户自定义级别;

5:图形界面模式,redhat常用运行级别;

6:重启级别;

S:单用户运行级别,只运行rc.sysinit文件;

当 2、3、4、5 均为 on 时则表示 MySQL 已经设置开机自启动。

2.4 修改MySQL配置文件

MySQL 文件目录的构成:

参数路径解释备注
datadir/var/lib/mysqlMySQL数据库文件存放路径重要
basedir/usr/bin相关命令目录mysqladmin mysqldump 等命令
plugin-dir/usr/lib64/mysql/pluginmysql插件存放目录
log-error/var/lib/mysql/izuf6h7blj36dmxbmcj15wz.errmysql 错误日志存放目录
pid-file/var/lib/mysql/izuf6h7blj36dmxbmcj15wz.pidmysql 进程 pid 文件
socket/var/lib/mysql/mysql.sock本地连接时用的 unix 套接字文件
/usr/share/mysql配置文件目录mysql 脚本及配置文件,重要
/etc/init.d/mysql服务启停相关脚本

MySQL 中文件的作用:

  • 二进制日志文件:主要用于主从复制
  • 错误日志文件 log-err:默认是关闭的,记录严重警告和错误信息,每次启动和关闭的详细信息等。
  • 查询日志 log:默认是关闭的,记录查询的 SQL 语句,如果开启会降低 MySQL 的整体性能,记录日志需要消耗系统性能,主要用于记录慢查询日志。
  • 数据文件:
    • 数据库文件:
      • Windows 系统一般位于:C:/ProgramData/MySQL/MySQL Server 5.7/Data
      • Linux 系统位于:/var/lib/mysql,切换到所在目录查看所有的数据库: ls -lF|grep ^d
        【存储引擎为MyISAM】
    • .frm 文件:存放表结构,相当于图书馆里的书架;
    • .myd 文件:存放数据,相当于图书馆里的书;
    • .myi 文件:存放索引,相当于图书馆的索引板; 【存储引擎为InoDB】
    • .frm 文件:如果使用系统表空间,结构和数据都会存放在此处;使用外部表空间则只存放结构;
    • .ibd 文件:如果使用外部表空间,此文件用于存放数据;
  • 配置方式:
    • Windows 系统修改 my.ini 文件
    • Linux 系统修改 my.cnf 文件

2.4.1 修改配置文件位置

拷贝配置文件到 /etc 目录,执行命令 cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

2.4.2 修改配置文件内容

2.4.2.1 设置字符集编码

MySQL 查看默认的字符集编码:

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| 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)

默认创建的数据库的编码是 latin1,此时使用中文会出现乱码问题。

① 全局统一配置字符集编码,使用 vim 打开拷贝后的 my.cnf 文件,按照标签找到对应的位置加入以下内容:

[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
[mysql]
default-character-set=utf8

编辑完成后重启 MySQL 服务,进入 MySQL 再次查看字符编码:

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
# 修改默认字符配置后创建的数据库
mysql> use db02;
Database changed
mysql> select * from user;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    3 | 王五   |
+------+--------+
3 rows in set (0.00 sec)
# 修改默认字符配置前创建的数据库
mysql> use ab01;
Database changed
mysql> select * from user;
+------+--------+
| id   | name   |
+------+--------+
|    1 |  Bruce |
|    1 | ??     |
|    4 | ????   |
+------+--------+
3 rows in set (0.00 sec)

② 修改已经创建的数据库和表的字符集编码

# 修改数据库的字符集编码
mysql> alter database ab01 character set 'utf8';
Query OK, 1 row affected (0.00 sec)
mysql> use ab01;
Database changed
# 修改数据库表的字符集编码
mysql> alter table user convert to character set 'utf8';
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

修改数据库和表的字符集编码后插入数据:

mysql> use ab01;
Database changed
mysql> insert into user (id, name) values (5, '刘六');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+------+--------+
| id   | name   |
+------+--------+
|    1 |  Bruce |
|    1 | ??     |
|    4 | ????   |
|    5 | 刘六   |
+------+--------+
4 rows in set (0.00 sec

不管是修改 MySQL 配置文件或者修改数据库和表的字符集,都修复已经变成乱码的数据,只能删除数据重新插入或更新数据才能解决问题。因此建议完成 MySQL 安装后就进行数据库配置。

2.4.2.2 设置数据库大小写不敏感

① 查看是否大小写敏感

mysql> show variables like '%lower_case_table_names%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.00 sec

② 设置大小写不敏感:

/etc/my.cnf 文件中找到 [mysqld] 标签,加入以下内容:lower_case_table_names = 1 保存退出后重启 MySQL 服务

修改后查看配置:

mysql> show variables like '%lower_case_table_name%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.00 sec)
属性设置描述
0使用CREATE TABLE或CREATE DATABASE语句指定的大小写字母在硬盘上保存表名和数据库名。名称比较对大小写敏感。在大小写不敏感的操作系统如windows或Mac OS x上我们不能将该参数设为0,如果在大小写不敏感的文件系统上将--lowercase-table-names强制设为0,并且使用不同的大小写访问MyISAM表名,可能会导致索引破坏。
1表名在硬盘上以小写保存,名称比较对大小写不敏感。MySQL将所有表名转换为小写在存储和查找表上。该行为也适合数据库名和表的别名。该值为Windows的默认值。
2表名和数据库名在硬盘上使用CREATE TABLE或CREATE DATABASE语句指定的大小写字母进行保存,但MySQL将它们转换为小写在查找表上。名称比较对大小写不敏感,即按照大小写来保存,按照小写来比较。注释:只在对大小写不敏感的文件系统上适用! innodb表名用小写保存。

更多请参考:lower_case_table_names(大小写敏感)

4.2.2.3 sql_mode设置

作用:sql_mode 中定义了对 MySQL 中 sql 语句语法的校验规则。

sql_mode 是个容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式,同时开发和测试环境数据库也必须这样设置,这样可以在开发阶段发现问题。

sql_mode 常用值:

常用值说明
ONLY_FULL_GROUP_BY对于GROUP BY 聚合操作,如果在SELECT 中的列,没有在GROUP BY 中出现,那么这个SQL 是不合法的,因为列不在GROUP BY 从句中。
NO_AUTO_VALUE_ON_ZERO该值影响自增长列的插入。默认设置下,插入0 或NULL 代表生成下一个自增长值。如果用户希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。
STRICT_TRANS_TABLES在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制。
NO_ZERO_IN_DATE在严格模式下,不允许日期和月份为零。
NO_ZERO_DATE设置该值,mysql 数据库不允许插入零日期,插入零日期会抛出错误而不是警告。
ERROR_FOR_DIVISION_BY_ZERO在INSERT 或UPDATE 过程中,如果数据被零除,则产生错误而非警告。如果未给出该模式,那么数据被零除时MySQL 返回NULL。
NO_AUTO_CREATE_USER禁止GRANT 创建密码为空的用户。
NO_ENGINE_SUBSTITUTION如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常。
PIPES_AS_CONCAT将 `` 视为字符串的连接操作符而非或运算符,这和Oracle 数据库是一样的,也和字符串的拼接函数Concat 相类似。
ANSI_QUOTES启用ANSI_QUOTES 后,不能用双引号来引用字符串,因为它被解释为识别符。
ORACLE设置等同于 PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE,NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER

① 查看当前的 sql_mode

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

② 修改 sql_mode:

临时修改:set @@sql_mode='XXX';

永久修改:在配置文件 /etc/my.cnf 中找到标签 [mysqld] 加入以下内容:sql_mode = 'XXX',修改完成后重启服务。

3. MySQL 用户权限管理

3.1 常用命令

命令描述备注
create user test identified by '123456';创建名称为 test 用户,密码为:123456
select host,user,password,select_priv,insert_priv,drop_priv from mysql.user;查看用户和权限向相关的信息。
set password =password('123456')修改当前用户的密码
update mysql.user set password=password('123456') where user='abc';修改其他用户密码所有通过user 表的修改,必须用flush privileges; 命令才能生效
update mysql.user set user='abc' where user='test';修改用户名所有通过user 表的修改,必须用 flush privileges; 命令才能生效
drop user abc删除用户不要通过delete from user where user='li4' 进行删除,系统会有残留信息保留。

3.2 命令详解

mysql> select host,user,password,select_priv,insert_priv,drop_priv from mysql.user;
+-------------------------+-------+-------------------------------------------+-------------+-------------+-----------+
| host                    | user  | password                                  | select_priv | insert_priv | drop_priv |
+-------------------------+-------+-------------------------------------------+-------------+-------------+-----------+
| localhost               | root  | *8237AF0E782A54BBF49B598F4FD7A844D66B0B75 | Y           | Y           | Y         |
| izuf6h7blj36dmxbmcj15wz | root  |                                           | Y           | Y           | Y         |
| 127.0.0.1               | root  |                                           | Y           | Y           | Y         |
| ::1                     | root  |                                           | Y           | Y           | Y         |
| localhost               |       |                                           | N           | N           | N         |
| izuf6h7blj36dmxbmcj15wz |       |                                           | N           | N           | N         |
| %                       | bruce | *58319282EAB9E38D49CA25844B73DA62C80C2ABC | Y           | Y           | Y         |
| %                       | test  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | N           | N           | N         |
+-------------------------+-------+-------------------------------------------+-------------+-------------+-----------+
8 rows in set (0.00 sec)

**host:**表示连接类型

标识说明
%表示所有远程通过TCP方式的连接
IP地址通过指定的IP地址进行 TCP 方式的连接
机器名通过指定网络中的机器名进行的 TCP 方式的连接
::1IPv6的本地IP地址,等同于IPv4的 127.0.0.1
localhost本地方式通过命令方式的连接,如:mysql -u root -p 的方式

**user:**表示用户名,同一用户通过不同方式连接的权限是不同的。

**password:**密码

所有的明文密码串通过,MYSQLSHA1算法加密后生成的密文字符串,不可逆;

mysql 5.7 的密码保存到 authentication_string 字段中,不再使用 password 字段;

**select_priv , insert_priv等:**表示用户所拥有的权限。

3.3 MySQL权限管理

3.3.1 授予权限

命令grant 权限1,权限2,...权限n on 数据库名称.表名称 to 用户名@用户IP地址 identified by '连接密码';

描述:该权限如果发现没有该用户,则会直接新建一个用户。

  • 实例:grant select,insert,delete,drop on crud.* to test@localhost
  • 作用:授予 test 用户在本地命令行方式下,crud 数据库下所有表的增删改查权限;

命令:grant all privileges on *.* to user01@'%'identified by '123';

**描述:**授予通过网络方式登录的 user01 用户,对所有数据库所有表的全部权限,密码设置为 123。

3.3.2 收回权限

① 查看当前用户的权限

命令:show grants;

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*8237AF0E782A54BBF49B598F4FD7A844D66B0B75' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

② 收回权限

命令:revoke [权限1,权限2,...,权限n] on 库名.表名 from 用户名@用户地址;

③ 收回全库全表的所有权限

命令:revoke all privileges on *.* from user01@localhost;

④ 收回 mysql 库下所有表的增删改查权限

命令:revoke select,insert,update,delete on mysql.* from user01@localhost;

用户权限被收回后,必须重新登录后才能生效。

3.3.3 查看权限

① 查看当前用户的权限

命令:show grants;

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*8237AF0E782A54BBF49B598F4FD7A844D66B0B75' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

② 查看全部权限

命令:select * from user;