跳至主要內容

安装使用及维护

荒流2024年1月21日大约 10 分钟约 2905 字

1. 数据库的使用

MySQL 是一种开源的、高性能的 DBMS（即它是一种数据库软件），被许多非常重要和有声望的公司用来处理自己的重要数据。使用 MySQL 的方式有：

  1. MySQL Client 命令行：命令用;或\g结束，换句话说，仅按 Enter 不执行命令；输入help或\h获得帮助；输入quit或exit退出命令行程序；

  2. MySQL Workbench：可视化图形界面，可以用可视化的方式查询、创建和修改数据库表

本质上，MySQL Workbench 和 MySQL Client 命令行都是客户端，和 MySQL 交互，唯一的接口就是 SQL。

通常说到“MySQL 服务器”这个概念的时候，是指硬件+软件的集合。

1.1 卸载

MySQL 在 windows 系统中的卸载流程：

  1. 去 MySQL 的安装目录找到my.ini文件，复制其中的datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"内容；

  2. 在控制面板的“程序和功能”中卸载 MySQL；

  3. 删除datadir对应目录下（如这里的C:ProgramData/）的 MySQL 文件夹。

1.2 启动

启动 MySQL 服务（所谓 服务（service） ，是指没有界面的、在后台运行的应用程序）：

  1. 手动：桌面右击“此电脑”——管理——服务和应用程序——服务——MySQL——右键选择启动
  2. cmd：services.msc（打开服务的窗口）
  3. 使用管理员权限打开 cmd：
    • net start mysql：启动 mysql 的服务
    • net stop mysql：关闭 mysql 的服务

1.3 登陆

登陆 MySQL，需要以下信息：

  1. 主机名（计算机名）；
  2. 端口：默认端口为3306；
  3. 合法的用户名和密码。
示例说明
mysql -u root -p ****cmd 下的登陆命令（默认登录本地 MySQL）
mysql -h ip -u root -p ****连接特定 ip 地址的 MySQL 服务

1.4 MySQL 的目录结构

  • 安装目录：注意配置文件my.ini
  • 数据目录
    • 数据库：文件夹
    • 表：文件
    • 数据：具体文件中的数据

1.5 MySQL 服务器架构的逻辑视图

image-20240129205932597
  1. 默认情况下，每个客户端连接都会在服务器进程中拥有一个线程，该连接的查询只会在这个单独的线程中执行，该线程驻留在一个内核或者 CPU 上。服务器维护了一个缓存区，用于存放已就绪的线程，因此不需要为每个新的连接创建或者销毁线程。
  2. MySQL 解析查询以创建内部数据结构（解析树），然后对其进行各种优化。

2. 权限管理

2.1 概念说明

权限管理——给用户提供且仅提供他们需要的访问权。

权限管理需要创建和管理用户账号。

名为root的账号对整个 MySQL 服务器具有完全控制权。在日常工作中，决不能使用root，应该创建一系列特定功能的账号。

MySQL 用户账号和信息存储在名为 mysql 的 MySQL 数据库中，mysql 数据库有一个名为user的表，它包含所有用户账号。可如下访问：

USE mysql;
SELECT user FROM user;

2.2 相关命令

  • CREATE USER：创建用户账号

    CREATE USER ben IDENTIFIED BY 'p@$$wOrd';
    • 创建用户账号（创建时可以指定口令，也可以不指定）
    • IDENTIFIED BY指定的口令为纯文本，MySQL 将在保存到user表之前对其进行加密
    • 为了作为散列值指定口令，使用IDENTIFIED BY PASSWORD
    • GRANT语句也可以创建用户账号，不过没有CREATE USER清楚直接
    • 可以通过INSERT直接插入行到user表来增加用户，不过这样不安全

  • RENAME USER：重命名账号

    RENAME USER ben TO bforta;

  • DROP USER：删除账号

    DROP USER bforta;

  • GRANT/REVOKE：授予/撤销权限

    • GRANT：授予权限，要求至少给出以下信息：

      • 要授予的权限（左为SELECT权限）
      • 被授予的访问权限的数据库或表（左为crashcourse数据库的所有表）
      • 用户名
      GRANT SELECT ON crashcourse.* TO bforta;

    • REVOKE：撤销权限，GTANT的反操作

      REVOKE SELECT ON crashcourse.* FROM bforta;

  • SHOW GTANTS FOR：显示用户权限：

    1. USAGE表示根本没有权限
    2. 新创建的用户没有访问权限
    3. MySQL 的权限用用户名和主机名结合定义，即user@host。如果不指定主机名，则使用默认的主机名（授予用户访问权限而不管主机名）
    SHOW GRANTS FOR bforta;
/*
+--------------------------------------+
|  Grants for bforta@%                 |
+--------------------------------------+
|  GRANT USAGE ON *.* TO 'bforta'@'%'  |
+--------------------------------------+
*/

SHOW GRANTS FOR bforta;
/*
+----------------------------------------------------+
|  Grants for bforta@%                               |
+----------------------------------------------------+
|  GRANT USAGE ON *.* TO 'bforta'@'%'                |
|  GRANT SELECT ON 'crashcourse'.* TO 'beforta'@'%'  |
+----------------------------------------------------+
*/

  • SET PASSWORD：更改口令/密码

    SET PASSWORD FOR bforta = Password('n3w p@$$wOrd');

    在不给定用户名时，其更新当前登录用户的口令。

    SET PASSWORD = Password('n3w p@$$wOrd');

2.3 权限说明

权限说明
ALL除GRANT OPTION外的所有权限
ALTER使用ALTER TABLE
ALTER ROUTINE使用ALTER PROCEDURE和DROP PROCEDURE
CREATE使用CREATE TABLE
CREATE ROUTINE使用CREATE PROCEDURE
CREATE TEMPORARY TABLES使用CREATE TEMPORARY TABLE
CREATE USER使用CREATE USER, DROP USER, RENAME USER, REVOKE ALL PRIVILEGES
CREATE VIEW使用CREATE VIEW
DELETE使用DELETE
DROP使用DROP TABLE
EXECUTE使用CALL和存储过程
FILE使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION使用GRANT和REVOKE
INDEX使用CREATE INDEX和DROP INDEX
INSERT使用INSERT
LOCK TABLES使用LOCK TABLES
PROCESS使用SHOW FULL PROCESSLIST
RELOAD使用FLUSH
REPLICATION CLIENT服务器位置的访问
REPLICATION SLAVE由复制从属使用
SELECT使用SELECT
SHOW DATABASES使用SHOW DATABASES
SHOW VIEW使用SHOW CREATE VIEW
SHUTDOWN使用mysqladmin shutdown（用来关闭 MySQL）
SUPER使用CHANGE MASTER, KILL, LOGS, PURGE, MASTER, SET GLOBAL。还允许mysqladmin调试登录
UPDATE使用UPDATE
USAGE无访问权限

2.4 示例

  • grant all privileges on *.* to jack@'localhost' identified by "jack" with grant option;
  • flush privileges;
  • show grants;
  • show grants for 'jack'@'%‘;
  • revoke delete on *.* from 'jack'@'localhost';
  • drop user 'jack'@'localhost';
  • rename user 'jack'@'%' to 'jim'@'%';
  • SET PASSWORD FOR 'root'@'localhost' = PASSWORD(’root');

2.5 忘记密码怎么办

  1. 方法 2：用SET PASSWORD命令

    • 格式：mysql> set password for username@localhost = password('new-password');
    • 例子：mysql> set password for root@localhost = password('123');

  2. 方法 2：用mysqladmin

    • 格式：mysqladmin -u用户名 -p旧密码 password 新密码
    • 例子：mysqladmin -uroot -p123456 password 123

  3. 方法 3：用UPDATE直接编辑user表

    mysql> use mysql; 
mysql> update user set password=password('123') where user='root' and host='localhost'; 
mysql> flush privileges;

  4. 方法 4：在忘记root密码的时候，以 windows 为例：

    1. 关闭正在运行的 MySQL 服务
    2. 打开 DOS 窗口，转到mysql\bin目录
    3. 输入mysqld --skip-grant-tables 回车。--skip-grant-tables 的意思是启动 MySQL 服务的时候跳过权限表认证
    4. 再开一个 DOS 窗口（因为刚才那个 DOS 窗口已经不能动了），转到mysql\bin目录
    5. 输入mysql回车，如果成功，将出现 MySQL 提示符 >
    6. 连接权限数据库：use mysql;
    7. 改密码：update user set password=password("123") where user="root";
    8. 刷新权限（必须步骤）：flush privileges;
    9. 退出quit;
    10. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录

3. 备份与还原

3.1 备份

由于 MySQL 数据库是基于磁盘的文件，因此普通的备份系统和例程就能备份 MySQL 的数据。但由于这些文件总是处于打开和使用状态，普通的备份不一定有效。

有两种主要类型的备份：

  • 裸文件备份：有时也称物理备份，指文件系统中的文件副本。此法产生的备份文件通常比相应的逻辑备份要大得多。

  • 逻辑备份：指重建数据所需的 SQL 语句。此法最大的缺点是从 MySQL 中导出数据和通过 SQL 语句将其加载回去的庞大开销。

    对于逻辑备份，《高性能 MySQL》一书中推荐首选第三方的 mydumper 命令，而非 MySQL 原生提供的 mysqldump 命令，因为前者内置了并行机制而后者是单线程的，后者可能导致备份和恢复的时间非常长。

一些备份数据的方法：

  • 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个程序应该正常运行，以便能正确地备份转储文件。
  • 可用命令行程序mysqlhotcopy从一个数据库复制所有数据（并非所有数据库引擎都支持这个实用程序）。
  • 可以使用 MySQL 的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都扔到将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用RESTORE TABLE来复原。

为保证所有数据被写到磁盘（包括索引数据），可能需要在备份前使用FLUSH TABLES语句。

备份语句示例：

  • 数据库备份

    mysqldump -u root -h localhost -p testbackup > c:\mysql.sql

  • 备份单个数据表

    mysqldump -u root -h localhost -p testbackup t_user > c:\mysql.sql

  • 备份多个数据库

    mysqldump -u root -h localhost -p --database testbackup db > c:\mysql.sql

  • 备份所有数据库

    mysqldump -u root -h localhost -p --all-databases> c:\mysql.sql

3.2 还原

在还原过程中，需要确保 MySQL 除了恢复进程外不接受其他访问，这一点往往比较重要。

方法一：

mysql -u root -h localhost -p testbackup< C:\mysql.sql

注意：备份前应先创建数据库名

方法二：

也可以登录到数据库中，使用source命令

source C:\mysql.sql

4. 数据库维护

数据库维护的一些语句：

语句说明
ANALYZE TABLE检查表键是否正确
CHECK TABLE用来针对许多问题对表进行检查
REPAIR TABLE修复相应的表
OPTIMIZE TABLE从一个表中删除了大量数据时用来收回所用的空间，从而优化表的性能

MySQL 服务器自身通过在命令行上执行mysqld启动，mysqld有几个重要的命令行选项：

命令行选项说明
--help显示帮助
--safe-mode装载减去某些最佳配置的服务器
--verbose显示全文本详细，常与--help联合使用
--version显示版本信息然后退出

5. 日志管理

MySQL 维护管理员主要依赖以下日志：

  1. 错误日志
  2. 查询日志
  3. 二进制日志
  4. 缓慢查询日志

BinLog配置：

log-bin="C:\Mysql\log"
expire_logs_days=10
max_binlog_size=100M
# 重启 MySQL 服务。

通过变量查询可以查看配置的日志是否开启：

show VARIABLES LIKE '%log_%';

查看二进制文件命令：

SHOW BINARY LOGS;

查看二进制操作记录，insert，update语句

show binlog events;

6. 优化服务器配置

MySQL 的默认设置是有充分理由的，在不了解其影响的情况下进行更改可能会导致崩溃、卡顿或性能下降。

MySQL 可以从命令行参数和配置文件中的设置项来读取配置信息。

  • 在类 UNIX 系统上，配置文件通常位于/etc/my.cnf或/etc/mysql/my.cnf。

  • 如果不知道服务器会读取哪些文件，可以通过如下命令查询：

    $ which mysqld
/usr/sbin/mysqld
$ /user/sbin/mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from following files in the given order:
/etc/mysql/my.conf ~/.mycnf /usr/etc/my.cnf

  • 配置文件采用标准 INI 格式，被分为多个部分。

配置设置全部用小写字母书写，单词之间以下划线或短横线分隔。下面两种写法是等效的，但还是建议你选择一种风格并始终如一地使用它。

  • /usr/sbin/mysqld --auto-increment-offset=5
  • /usr/sbin/mysqld --auto_increment_offset=5

配置可以有多个作用域：

  • 全局作用域：服务器范围的
  • 会话作用域：对于每个连接都不同
  • 基于每个对象的
编辑此页
上次编辑于: 2024/5/5 14:30:28
贡献者: chua-n
应无所住，而生其心
Copyright © 2024 荒流