search

xuzhengfu / pilot

进入编程世界的第一课
1 stars 0 forks source link

x5-mysql-setup MySQL 配置指南 #44

Open xuzhengfu opened 4 years ago

xuzhengfu commented 4 years ago

1. 安装

最新的 MySQL 服务程序可以在官网下载，官网也提供了针对不同操作系统的安装指引。

如果已经按照我们提供的指南配置好自己机器的编程环境的话，就可以用 Homebrew（macOS 下）或 Scoop（Windows 下）来安装。比较推荐这个办法。

brew install mysql@5.7
brew services start mysql@5.7
xuzhengfu at xuzhengfudeiMac in ~
$ brew install mysql@5.7

==> Installing dependencies for mysql@5.7: openssl@1.1
==> Installing mysql@5.7 dependency: openssl@1.1
==> Downloading https://homebrew.bintray.com/bottles/openssl@1.1-1.1.1f.mojave.b
==> Downloading from https://akamai.bintray.com/25/25ab844d2f14fc85c7f52958b4b89
######################################################################## 100.0%
==> Pouring openssl@1.1-1.1.1f.mojave.bottle.tar.gz
==> Caveats
A CA file has been bootstrapped using certificates from the system
keychain. To add additional certificates, place .pem files in
  /usr/local/etc/openssl@1.1/certs

and run
  /usr/local/opt/openssl@1.1/bin/c_rehash

openssl@1.1 is keg-only, which means it was not symlinked into /usr/local,
because macOS provides LibreSSL.

If you need to have openssl@1.1 first in your PATH run:
  echo 'export PATH="/usr/local/opt/openssl@1.1/bin:$PATH"' >> ~/.bash_profile

For compilers to find openssl@1.1 you may need to set:
  export LDFLAGS="-L/usr/local/opt/openssl@1.1/lib"
  export CPPFLAGS="-I/usr/local/opt/openssl@1.1/include"

==> Summary
🍺  /usr/local/Cellar/openssl@1.1/1.1.1f: 8,057 files, 18MB
==> Installing mysql@5.7
==> Downloading https://homebrew.bintray.com/bottles/mysql@5.7-5.7.29.mojave.bot
==> Downloading from https://akamai.bintray.com/88/88cf1f9f07a84694654f790507da9
########################                                                  33.4%

curl: (56) LibreSSL SSL_read: SSL_ERROR_SYSCALL, errno 60

curl: (22) The requested URL returned error: 403 Forbidden
Error: Failed to download resource "mysql@5.7"
Download failed: https://homebrew.bintray.com/bottles/mysql@5.7-5.7.29.mojave.bottle.tar.gz
Warning: Bottle installation failed: building from source.
==> Installing dependencies for mysql@5.7: cmake
==> Installing mysql@5.7 dependency: cmake
==> Downloading https://homebrew.bintray.com/bottles/cmake-3.17.1.mojave.bottle.
==> Downloading from https://akamai.bintray.com/c6/c62e8f5a02515337214c49807f55c
###############                                                           21.7%^R

######################################################################## 100.0%
==> Pouring cmake-3.17.1.mojave.bottle.tar.gz
==> Caveats
Emacs Lisp files have been installed to:
  /usr/local/share/emacs/site-lisp/cmake
==> Summary
🍺  /usr/local/Cellar/cmake/3.17.1: 6,156 files, 58.1MB
==> Downloading https://cdn.mysql.com/Downloads/MySQL-5.7/mysql-boost-5.7.29.tar.gz
########                                                                  11.2%
curl: (56) LibreSSL SSL_read: SSL_ERROR_SYSCALL, errno 60
###########################                                               38.5%
curl: (18) transfer closed with 31612960 bytes remaining to read
Error: An exception occurred within a child process:
  DownloadError: Failed to download resource "mysql@5.7"
Download failed: https://cdn.mysql.com/Downloads/MySQL-5.7/mysql-boost-5.7.29.tar.gz

再次运行：

xuzhengfu at xuzhengfudeiMac in ~
$ brew install mysql@5.7

==> Downloading https://homebrew.bintray.com/bottles/mysql@5.7-5.7.29.mojave.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/88/88cf1f9f07a84694654f790507da937909eee1b16a0
#############################################################             85.5%
curl: (56) LibreSSL SSL_read: SSL_ERROR_SYSCALL, errno 60

curl: (22) The requested URL returned error: 403 Forbidden
Error: Failed to download resource "mysql@5.7"
Download failed: https://homebrew.bintray.com/bottles/mysql@5.7-5.7.29.mojave.bottle.tar.gz
Warning: Bottle installation failed: building from source.
==> Downloading https://cdn.mysql.com/Downloads/MySQL-5.7/mysql-boost-5.7.29.tar.gz
######################################################################## 100.0%
==> cmake . -DCOMPILATION_COMMENT=Homebrew -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_gen
==> make

^C

再次运行：

xuzhengfu at xuzhengfudeiMac in ~
$ brew install mysql@5.7

==> Downloading https://homebrew.bintray.com/bottles/mysql@5.7-5.7.29.mojave.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/88/88cf1f9f07a84694654f790507da937909eee1b16a0e47d473b2f
######################################################################## 100.0%
==> Pouring mysql@5.7-5.7.29.mojave.bottle.tar.gz
==> /usr/local/Cellar/mysql@5.7/5.7.29/bin/mysqld --initialize-insecure --user=xuzhengfu --basedir=/usr/
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

mysql@5.7 is keg-only, which means it was not symlinked into /usr/local,
because this is an alternate version of another formula.

If you need to have mysql@5.7 first in your PATH run:
  echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.bash_profile

For compilers to find mysql@5.7 you may need to set:
  export LDFLAGS="-L/usr/local/opt/mysql@5.7/lib"
  export CPPFLAGS="-I/usr/local/opt/mysql@5.7/include"

To have launchd start mysql@5.7 now and restart at login:
  brew services start mysql@5.7
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/mysql@5.7/bin/mysql.server start
==> Summary
🍺  /usr/local/Cellar/mysql@5.7/5.7.29: 319 files, 232MB
==> `brew cleanup` has not been run in 30 days, running now...
Removing: /Users/xuzhengfu/Library/Caches/Homebrew/git--2.25.0_1.mojave.bottle.tar.gz... (17MB)
Removing: /Users/xuzhengfu/Library/Caches/Homebrew/node--13.7.0.mojave.bottle.1.tar.gz... (16.1MB)
Removing: /usr/local/Cellar/openssl@1.1/1.1.1d... (7,983 files, 17.9MB)
Removing: /Users/xuzhengfu/Library/Caches/Homebrew/openssl@1.1--1.1.1d.mojave.bottle.tar.gz... (5.2MB)
Removing: /Users/xuzhengfu/Library/Caches/Homebrew/python--3.7.6_1.mojave.bottle.tar.gz... (14.8MB)
Removing: /Users/xuzhengfu/Library/Caches/Homebrew/readline--8.0.1.mojave.bottle.tar.gz... (517.9KB)
Removing: /Users/xuzhengfu/Library/Caches/Homebrew/sqlite--3.30.1.mojave.bottle.tar.gz... (1.9MB)
Removing: /Users/xuzhengfu/Library/Caches/Homebrew/xz--5.2.4.mojave.bottle.tar.gz... (373.5KB)
Removing: /Users/xuzhengfu/Library/Logs/Homebrew/gdbm... (64B)
Removing: /Users/xuzhengfu/Library/Logs/Homebrew/python... (3 files, 141.7KB)
Removing: /Users/xuzhengfu/Library/Logs/Homebrew/icu4c... (64B)
Removing: /Users/xuzhengfu/Library/Logs/Homebrew/readline... (64B)
Removing: /Users/xuzhengfu/Library/Logs/Homebrew/sqlite... (64B)
Removing: /Users/xuzhengfu/Library/Logs/Homebrew/xz... (64B)
Removing: /Users/xuzhengfu/Library/Logs/Homebrew/gettext... (64B)
Removing: /Users/xuzhengfu/Library/Logs/Homebrew/pcre2... (64B)
Removing: /Users/xuzhengfu/Library/Logs/Homebrew/openssl@1.1... (64B)
Removing: /Users/xuzhengfu/Library/Logs/Homebrew/node... (64B)
Removing: /Users/xuzhengfu/Library/Logs/Homebrew/git... (64B)
Pruned 0 symbolic links and 2 directories from /usr/local
xuzhengfu at xuzhengfudeiMac in ~
$ brew services start mysql@5.7
==> Tapping homebrew/services
Cloning into '/usr/local/Homebrew/Library/Taps/homebrew/homebrew-services'...
remote: Enumerating objects: 7, done.
remote: Counting objects: 100% (7/7), done.
remote: Compressing objects: 100% (7/7), done.
remote: Total 698 (delta 0), reused 3 (delta 0), pack-reused 691
Receiving objects: 100% (698/698), 193.18 KiB | 278.00 KiB/s, done.
Resolving deltas: 100% (272/272), done.
Tapped 1 command (40 files, 267KB).
==> Successfully started `mysql@5.7` (label: homebrew.mxcl.mysql@5.7)
xuzhengfu at xuzhengfudeiMac in ~
$ mysql -uroot
-bash: mysql: command not found
  1. 根据 mysql 的安装信息中提示，执行以下命令：
echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.bash_profile
export LDFLAGS="-L/usr/local/opt/mysql@5.7/lib"
export CPPFLAGS="-I/usr/local/opt/mysql@5.7/include"

依旧报错。

  1. google "mysql: command not found"

在这篇文章中找到了解决方案，正要实施之际，想到，何不在 “issues” 中搜一搜报错信息，或许有人问过呢？

  1. search “mysql: command not found” in issues

果然。

问题顺利解决。

  1. 执行命令：
xuzhengfu at xuzhengfudeiMac in ~
$ export PATH=/usr/local/opt/mysql@5.7/bin:$PATH
xuzhengfu at xuzhengfudeiMac in ~
$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.29 Homebrew

Copyright (c) 2000, 2020, 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>
  1. 遗留问题：不了解 export 命令以及 ‘环境变量’ 等知识。

正因如此，尽管以上解决方案在 “mysql 的安装信息” 中有所提示，但因为我不了解 export 命令，所以仍然做错了。

2. 初始配置

当 MySQL Server 运行起来之后，就可以通过命令行工具 mysql 来操作，下面的操作无论在 Windows 还是 macOS 下都是一样的。

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.7.26 Homebrew

Copyright (c) 2000, 2019, Oracle ...

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

在 mysql> 提示符之后可以输入任何 MySQL 支持的 SQL 语句（用半角分号 ; 结尾）。

2.1 修改 root 密码

注意我们目前是以 root 用户的身份登录到 MySQL 服务（命令行里的 -u 就是指定登录用户的参数），这是最大权限的用户，可以做任何事情，而且初始没有密码。这是个很不安全的状态，我们首先要改变这个状态。

在 mysql> 提示符之后输入（注意将 mypass 改为你选择的 root 用户密码）：

SET PASSWORD FOR root@localhost = PASSWORD('mypass');
Query OK, 0 rows affected, 1 warning (0.05 sec)
$ mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
mysql -uroot -p
Enter password: *******

输入密码后成功进入 REPL。

2.2 创建数据库和对应用户

以下操作都以 root 用户身份在 mysql 的提示符后输入执行。

mysql> CREATE DATABASE demo;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.02 sec)

mysql> USE demo
Database changed
mysql> SHOW TABLES;
Empty set (0.01 sec)
mysql> CREATE USER learn@localhost IDENTIFIED BY 'demo';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL ON demo.* TO learn@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

上面第一条命令创建了一个叫 learn 的用户，限制其只能从本地（localhost）登录，并设置其密码为 demo；第二条命令则赋予该用户对 demo 数据库的完整权限（用户缺省是什么权限都没有的）；最后一条命令是要求服务器刷新权限库，令前面的设置生效。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
+--------------------+
2 rows in set (0.00 sec)

mysql> use demo
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> CREATE TABLE users (id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, username VARCHAR(255), firstname VARCHAR(255), lastname VARCHAR(255));
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_demo |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)

mysql> DESC users;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| username  | varchar(255) | YES  |     | NULL    |                |
| firstname | varchar(255) | YES  |     | NULL    |                |
| lastname  | varchar(255) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> DROP TABLE users;
Query OK, 0 rows affected (0.01 sec)

可以看到，learn 只能访问自己有权限的数据库，并且在 demo 数据库内拥有创建和删除表的权限。

至此 MySQL 环境配置完毕。

3. 准备学习用数据库

最后我们来准备下在学习中我们会用到的数据库。这部分是可选的，如果没有完成，完全可以用自己创建的简单数据来测试，不过完成下面的操作也是对环境熟悉的过程，你完全可以试试，很多编程的经验都是折腾这些环境和数据得到的。

3.1 准备数据文件

3.2 数据导入

  1. 首先从 MySQL 官方网站下载 MySQL Workbench 并运行下载好的安装包（名字一般是 mysql-workbench-community-x.x.xx-winx64.msi）。

  2. 打开安装好的 MySQL Workbench，如果前面 MySQL 服务安装和运行无误，这里 Workbench 会检测到本地运行的服务，并显示在启动界面，点击之就可以连接开始管理本地的数据库：

  3. 如果前面的初始配置无误，在打开的界面左侧选择 Schemas 就可以看到 demo 数据库，右键选择 demo 数据库然后选择 Table Data Import Wizard 打开数据导入向导：

  1. 浏览并选择刚才我们解压并修改过的 fifa19.csv，点击 Next >；
  2. 选择 Create new table，下拉框里选择数据库 demo，表名输入 players，点击 Next >；
  3. 在 Columns 下面去掉第一行 MyUnknownColumn 那一行的选择框，点击 Next >；
  4. 点击 Next > 开始导入数据，这要一会儿。

Logging

2020-04-14 01:30:20 initialize