MySQL——环境配置 - Go语言中文社区

MySQL——环境配置

修改时间 增加内容
2019-01-30 增加yum安装方式
2019-02-13 增加mycli的介绍
2019-03-13 docker安装mysql
2019-08-21 ubuntu安装mysql

ubuntu 安装

sudo apt-get update  #更新软件源
sudo apt-get install mysql-server  #安装mysql

启动

root@VM-16-7-ubuntu:/home/ubuntu# service mysql start
root@VM-16-7-ubuntu:/home/ubuntu# sudo netstat -tap |grep mysql
tcp        0      0 localhost.localdo:mysql 0.0.0.0:*               LISTEN      6148/mysqld

查看登录密码

vi  /etc/mysql/debian.cnf
[client]
host     = localhost
user     = debian-sys-maint
password = S8ZlSROTkONUH1SE
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = S8ZlSROTkONUH1SE
socket   = /var/run/mysqld/mysqld.sock

查看用户权限

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

mysql> select user,host,plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | localhost | auth_socket           |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| debian-sys-maint | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)

增加用户

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password'; #本地登录 
CREATE USER 'user_name'@'%' IDENTIFIED BY 'password'; #远程登录

修改权限

授权用户拥有test数据库的所有权限：

grant all privileges on test.* to 'user'@'localhost' identified by 'password'; 本地授权
grant all privileges on test.* to 'user'@'%' identified by 'password'; 远程授权
flush privileges; #刷新系统权限表

指定部分权限给用户:

grant select,update on test.* to 'user'@'localhost' identified by 'password'; 
flush privileges; #刷新系统权限表

赋值所有权限给用户

grant all on *.* to 'baxiang'@'%' identified by 'baxiang';

修改root密码方式和密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

远程登陆数据库的失败

出错信息 :ERROR 2003 ( HY000 ) : Can 't connect to MySQL server on ' xxx.xxx.xxx.xxx ',

没有授予相应的权限:

例如，你想root使用123456从任何主机连接到mysql服务器

mysql>GRANT ALL PRIVILEGES ON *.* TO ' root '@' % ' IDENTIFIED BY ' 123456 ' WITH GRANT OPTION;
mysql>FLUSH RIVILEGES

如果你想允许用户jack从ip为10.10.50.127的主机连接到mysql服务器，并使用654321作为密码

mysql>GRANT ALL PRIVILEGES ON *.* TO ' jack '@’10.10.50.127’ IDENTIFIED BY ' 654321 ' WITH GRANT OPTION;
mysql>FLUSH RIVILEGES</pre>

2). 修改mysql数据库中的user表使相应的用户能从某一主机登陆

<pre>mysql -u root –p
mysql>use mysql;
mysql>update user set host = ' % ' where user = ' root ';
mysql>select host, user from user;</pre>

防火墙禁止了3306端口

以ufw为例

# ufw status verbose
Status: active
Logging: on (low)
Default: deny (incoming), allow (outgoing), disabled (routed)
New profiles: skip
# ufw allow 3306 Rule added
Rule added (v6)
# ufw reload
Firewall reloaded

默认3306端口只允许本地访问

查看3306端口是否正常

# netstat -an | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

修改MySQL的配置文件(这边是在这个目录，每个人目录不同) /etc/mysql/mysql.conf.d/mysqld.cnf，
因为默认3306端口只允许本地访问的，注释掉这行

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.

#bind-address = 127.0.0.1

然后重启Mysql，

$ sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.

查看3306

$ netstat -an | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN

Docker安装Mysql

下载mysql官方镜像，选择的tag版本是5.7

$ docker pull mysql:5.7
...
Digest: sha256:de482b2b0fdbe5bb142462c07c5650a74e0daa31e501bc52448a2be10f384e6d
Status: Downloaded newer image for mysql:5.7

查看MySQL本地镜像

$ docker images |grep mysql
mysql               5.7                 ee7cbd482336        7 days ago          372MB

启动mysql容器

$ docker run  -p 3306:3306 --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag

–name：给新创建的容器命名
-e：配置信息，此处配置mysql的root用户的登陆密码
-p：端口映射，此处映射主机3306端口到容器的3306端口
-d：成功启动容器后输出容器的完整ID，
eg:

$docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=baxiang -d mysql:5.7

查看本地容器

$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
61e7f6242c11        mysql:5.7           "docker-entrypoint.s…"   53 seconds ago      Up 52 seconds       0.0.0.0:3306->3306/tcp, 33060/tcp   mysql_57

登录mysql

docker exec -it mysql_57 bash

Ubuntu 安装

使用apt-get

sudo apt-get install mysql-server

查看安装状态

sudo netstat -tap | grep mysql
tcp6       0      0 [::]:mysql              [::]:*                  LISTEN      1313/mysqld

配置远程访问
将mysqld.cnf 中的bind-address = 127.0.0.1注释

 sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

查看端口

netstat -an|grep 3306

开启了远程访问的端口是

tcp6       0      0 :::3306                 :::*                    LISTEN

开启安全组

阿里云服务器设置安全组规则


image.png

腾讯云设置安全组规则


image.png
添加远程登录的用户

添加一个用户名是root且密码是123456的远程访问用户

grant all on *.* to root@'%' identified by '123456'; 
flush privileges; ##刷新

查看当前的用户信息

use mysql
select user,host,authentication_string from user;

重启MySQL服务

  /etc/init.d/mysql restart
卸载
sudo apt-get remove --purge mysql-* 
sudo find  / -name mysql -print  ## 查找包含的文件 逐一删除
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean

CentOS 安装

使用yum 在CentOS 7 上安装mysql-server会失败,CentOS 7 已经将MySQL数据库软件从默认的程序列表中移除。据说是因为甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险。

yum install mysql-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
No package mysql-server available.
Error: Nothing to do

查看是否安装旧版本

# rpm -qa | grep mysql
mysql-community-common-5.6.42-2.el7.x86_64
mysql-community-release-el7-5.noarch
mysql-community-libs-5.6.42-2.el7.x86_64
mysql-community-server-5.6.42-2.el7.x86_64
mysql-community-client-5.6.42-2.el7.x86_64

卸载旧版本

# rpm -e --nodeps mysql-community-common-5.6.42-2.el7.x86_64
# rpm -e --nodeps mysql-community-release-el7-5.noarch
# rpm -e --nodeps mysql-community-libs-5.6.42-2.el7.x86_64
# rpm -e --nodeps mysql-community-server-5.6.42-2.el7.x86_64
# rpm -e --nodeps mysql-community-client-5.6.42-2.el7.x86_64

安装5.6版本

#wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
#rpm -ivh mysql-community-release-el7-5.noarch.rpm
#yum install mysql-community-server
#service mysqld restart

安装5.7版本

# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar
# mkdir mysql
# tar -xvf mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar -C mysql

注意5.7的安装顺序 依次安装rpm包 依赖关系依次为common→libs→client→server

# rpm -ivh mysql-community-common-5.7.22-1.el7.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-common-5.7.22-1.e################################# [100%]
# rpm -ivh mysql-community-libs-5.7.22-1.el7.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-libs-5.7.22-1.el7################################# [100%]
# rpm -ivh mysql-community-client-5.7.22-1.el7.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-client-5.7.22-1.e################################# [100%]
# rpm -ivh mysql-community-server-5.7.22-1.el7.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-server-5.7.22-1.e################################# [100%]
# systemctl start mysqld.service

重启服务

systemctl restart mysqld.service

增加新的远程登录用户

create user 'test'@'%' identified by 'test';

yum 安装方式

yum安装地址https://dev.mysql.com/downloads/repo/yum/

wget https://repo.mysql.com//mysql80-community-release-el7-2.noarch.rpm
--2019-01-30 23:25:00--  https://repo.mysql.com//mysql80-community-release-el7-2.noarch.rpm
正在解析主机 repo.mysql.com (repo.mysql.com)... 23.56.25.233
正在连接 repo.mysql.com (repo.mysql.com)|23.56.25.233|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：25892 (25K) [application/x-redhat-package-manager]
正在保存至: “mysql80-community-release-el7-2.noarch.rpm”

100%[==========================================================================================================================================>] 25,892       120KB/s 用时 0.2s

2019-01-30 23:25:02 (120 KB/s) - 已保存 “mysql80-community-release-el7-2.noarch.rpm” [25892/25892])

增加yum仓库

$ sudo rpm -Uvh mysql80-community-release-el7-2.noarch.rpm
[sudo] baxiang 的密码：
警告：mysql80-community-release-el7-2.noarch.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql80-community-release-el7-2  ################################# [100%]

修改 vi /etc/yum.repos.d/mysql-community.repo,将mysql安装的默认版本由8.0修改成5.7，如果想使用5.6就把[mysql56-community]中的enabled=0改成enabled=1

# Enable to use MySQL 5.5
[mysql55-community]
name=MySQL 5.5 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.5-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.6
[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.7
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

安装mysql

sudo yum install mysql-community-server

启动mysql

systemctl start mysqld.service

查看mysql生成的临时密码

# sudo grep 'temporary password' /var/log/mysqld.log
2019-01-30T16:17:43.781115Z 1 [Note] A temporary password is generated for root@localhost: tuI%%gyUa3Sp

修改MySQL的临时密码,mysql的密码要求是数字字母大小写特殊符号组合而成。

# mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'baxiang';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Baxiang2019!';
Query OK, 0 rows affected (0.00 sec)

在Mysql5.7以上版本validate_password是默认安装的。如何验证validate_password插件是否安装呢

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.02 sec)

其中，validate_password_number_count指定了密码中数字的长度，validate_password_special_char_count指定了密码中特殊字符的长度，validate_password_mixed_case_count指定了密码中大小字母的长度。validate_password_length指定了密码的最小长度.
设置密码的总长度是4

 set global validate_password_length=4;

validate_password_policy有以下取值：

Policy Tests Performed
0 or LOW Length
1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters
2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary file

默认是1，即MEDIUM，所以刚开始设置的密码必须符合长度，且必须含有数字，小写或大写字母，特殊字符。
设置安全策略为最低，这个只适合在内部开发测试使用，生产环境是绝对不可以这样降低安全策略的

 set global validate_password_policy=0;

增加远程登录root用户 这个也是建议在测试开发中使用，远程登录生产环境不开启root

CREATE USER 'root'@'%' identified by 'baxiang';

MAC OS安装

$ brew install mysql
==> Downloading https://homebrew.bintray.com/bottles/mysql-5.7.21.sierra.bottle.tar.gz
######################################################################## 100.0%
==> Pouring mysql-5.7.21.sierra.bottle.tar.gz
==> /usr/local/Cellar/mysql/5.7.21/bin/mysqld --initialize-insecure --user=baxiang --basedir=/usr/local/Cellar/mysql/5.7.21 --datad
==> 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
版权声明：本文来源简书，感谢博主原创文章，遵循 CC 4.0 by-sa 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://www.jianshu.com/p/976a63bc7a6c
站方申明：本站部分内容来自社区用户分享，若涉及侵权，请联系站方删除。
  • 发表于 2020-01-12 13:33:23
  • 阅读 ( 1331 )
  • 分类：数据库

你可能感兴趣的文章

精选的优质文章

0 条评论

请先 登录 后评论

官方社群

GO教程

推荐文章

猜你喜欢

随便看看

来源51CTO

如果觉得我的文章对您有用，请随意打赏。你的支持将鼓励我继续创作！