Mysql安装和使用

1.mysql安装

• 使用命令行brew install mysql

$ brew install mysql 
==> Downloading https://homebrew.bintray.com/bottles/mysql-8.0.17_1.mojave.bottl
Already downloaded: /Users/qxw7463/Library/Caches/Homebrew/downloads/8ff724df95a1a4afa869b72ece0b47d5b5783fc4160c4e34c6fda2c74f35d799--mysql-8.0.17_1.mojave.bottle.1.tar.gz
==> Pouring mysql-8.0.17_1.mojave.bottle.1.tar.gz
==> 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

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start
==> Summary
🍺  /usr/local/Cellar/mysql/8.0.17_1: 284 files, 272.4MB

• 说明

  其中运行mysql_secure_installation 可以设置密码
  运行brew service start mysql 可以后台启动mysql 运行mysql.server start 前台启动mysql(关闭控制台，服务停止)

• 设置密码

$ mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file
// 这里提示选一个密码强度等级
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Please set the password for root here.
// 然后按照所选的密码强度要求设定密码
New password: 

Re-enter new password: 

Estimated strength of the password: 25 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
 ... Failed! Error: Your password does not satisfy the current policy requirements

New password: 

Re-enter new password: 

Estimated strength of the password: 25 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
 ... Failed! Error: Your password does not satisfy the current policy requirements

New password: 

Re-enter new password: 

Estimated strength of the password: 50 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL 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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL 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.

// 这里删除默认自带的test数据库
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 

2.mysql常用命令

• 连接数据库 mysql -uroot
• 连接数据库 mysql -u root -p
• 启动mysql服务 brew services start mysql
• 关闭mysql服务 brew services stop mysql
• 启动mysql服务(后台不运行) mysql.server start
• 查看mysql进程 ps aux | grep mysql
• 杀死进程 kill -9 pid
• 退出mysql mysql> exit;
• 查看版本号命令 mysql> status;

3.常见错误

1.ERROR 2002 (HY000)

$ mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

• 介绍

  • mysql有两种连接方式
    1、TCP/IP
    2、socket

  • mysql.sock的作用
    mysql.sock的作用是server和client在同一台服务器，并且使用localhost进行链接的时候，就会使用socket来进行连接。
    也就是：为主机名为localhost建立的MySQL连接，该连接过程通过一个套接字文件mysql.socket实现的。所以该文件被删后，用localhost用户是连接不到MySQL服务器的。
    必须建立一条tcp/ip连接，即使用127.0.0.1而不是localhost作为-h的参数去连接MySQL服务器，如：mysqladmin -h 127.0.0.1 -u root -p shutdown，强制地建立一条tcp/ip连接；
    关闭MySQL服务器，再重新以localhost为主机名启动mysql服务器，它就会重新创建一个套接字文件。

• 原因
  mysql.sock是一个临时文件，启动mysql后才会生成，mysql将其放在/tmp目录，而MacOS将其放在/var/mysql目录。所以我们只需要创建一个软链接，输入以下两个命令即可：
  如果/var/下没有mysql目录，则通过下面的解决方法

• 解决方法
  创建目录：sudo mkdir /var/mysql
  创建软链接：

  sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock
  

• 注意
  如果提示： ln: creating symbolic link /data/mysqldata/mysql.sock to `/tmp/mysql.sock': File exists
  删除之前的mysql.sock文件