MySQL/Maria安装以及配置

MySQL/Maria安装以及配置

Arch Linux

目前Arch Linux主要维护Maria来代替MySQL，在主要仓库中已经找不到MySQL的安装方式

安装Maria

  1. 执行：

    sudo pacman -S mysql

    选择安装包并下载安装

  2. 执行：

    sudo mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

    该命令声明用户mysql来管理Maria数据库系统，数据存储路径在/var/lib/mysql下，不建议对上面的参数进行自定义操作。
    成功执行完毕，将有提示：

    Installing MariaDB/MySQL system tables in '/var/lib/mysql' ...
OK
To start mariadbd at boot time you have to copy
support-files/mariadb.service to the right place for your system
Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is mysql@localhost, it has no password either, but
you need to be the system 'mysql' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo
See the MariaDB Knowledgebase at https://mariadb.com/kb
You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mariadbd-safe --datadir='/var/lib/mysql'
You can test the MariaDB daemon with mariadb-test-run.pl
cd '/usr/mariadb-test' ; perl mariadb-test-run.pl
Please report any problems at https://mariadb.org/jira
The latest information about MariaDB is available at https://mariadb.org/.
Consider joining MariaDB''s strong and vibrant community:
https://mariadb.org/get-involved/

  3. 启动并检查MariaDB

    sudo systemctl start mariadb
sudo systemctl status mariadb

    成功启动，输出应包含active

    一些其他相关systemctl命令：

    • sudo systemctl stop mariadb：停止Maria服务
    • sudo systemctl restart mariadb: 重启Maria服务

配置Maria

  1. 配置Maria安全选项

    sudo mariadb-secure-installation

    该命令将设置root密码、设置匿名用户、设置测试用户以及处理远程连接配置。具体流程如下：

    NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we will need the current
password for the root user. If you have just installed MariaDB, and
have not set the root password yet, you should just press enter here.

Enter current password for root (enter for none):  // 此处不输入，初始没有密码
OK, successfully used password, moving on...
Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.
You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] y
Enabled successfully!
Reloading privilege tables..
 ... Success!

You already have your root account protected, so you can safely answer 'n'.
Change the root password? [Y/n] y  // 输入y设置root密码
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y  // 删除匿名用户
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y  // 禁止远程登陆root用户
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y  // 删除测试数据库
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y  // 重载数据库
 ... Success!

Cleaning up...
All done!  If you have completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!

  2. 写my配置文件
    以系统root权限修改/etc/my.cnf配置文件，此处使用kakoune：

    sudo kak /etc/my.cnf

    新增如下内容

    [mysqld]
bind-address = 127.0.0.1    # 仅允许本地访问（默认）
port = 3306                 # 默认端口
character-set-server = utf8mb4  # 支持中文和表情符号
collation-server = utf8mb4_unicode_ci

  3. 登录Maria

    mariadb -u root -p
Enter password:

    建议创建普通用户用于管理数据库，但是无用户、赋权操作权限：

    CREATE USER '<user_name>'@'localhost' IDENTIFIED BY '<password>';
GRANT
	SELECT, INSERT, UPDATE, DELETE,
	CREATE, DROP, ALTER, INDEX,
	TRIGGER, CREATE VIEW, SHOW VIEW,
	CREATE ROUTINE, ALTER ROUTINE, EXECUTE,
	EVENT, LOCK TABLES, REFERENCES,
	REPLICATION CLIENT, REPLICATION SLAVE,
	FILE, PROCESS, SHOW DATABASES,
	CREATE TEMPORARY TABLES, CREATE TABLESPACE
ON *.*
TO '<user_name>'@'localhost'
WITH GRANT OPTION;

Windows

安装

下载链接: https://dev.mysql.com/downloads/windows/

  1. 将安装包解压到本地文件夹，假设为D:/Program Files/MySQL。
  2. 新增系统环境变量：
    # 直接新建。
MYSQL_HOME: D:\Program Files\MySQL
# 在原Path上新增。
Path: %MYSQL_HOME%\bin
  3. 在D:/Program Files/MySQL下新建文件mysql.ini，并添加如下内容（参考官方文档）:
    [mysqld]
# 设置3306端口
port = 3306
# 设置mysql安装目录
basedir=D:\Program Files\MySQL
# 设置mysql数据存放目录
datadir=D:\Program Files\MySQL\data
# 默认字符集
character-set-server=utf8

启动并初始化

  1. 启动命令行，输入命令：
    mysqld --initialize --console
    生成临时密码，此时命令行应该有：
    [时间] 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: [临时密码]
    复制临时密码。
  2. 输入命令mysqld --install，成功则有
    Service successfully installed.
  3. 启动MySQL服务。
    net start mysql

    关闭MySQL服务：

    net stop mysql
  4. 连接MySQL：
    mysql -uroot -p
    输入临时密码，进入mysql服务界面。
  5. 修改临时密码：
    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '[新密码]';

下载Workbench

  1. 从https://dev.mysql.com/downloads/workbench/下载镜像文件，并安装。
  2. 可能弹出错误窗口，提示缺少正确版本的C++依赖，则先下载C++依赖：
    系统 下载链接
    X86 https://aka.ms/vs/17/release/vc_redist.x86.exe
    X64 https://aka.ms/vs/17/release/vc_redist.x64.exe
posted @ 2023-10-23 12:33  皮皮罴  阅读(100)  评论(0)    收藏  举报