当前位置 : 首页 » 文章分类 :  开发  »  RHEL/CentOS Linux中安装MySQL5.6

RHEL/CentOS Linux中安装MySQL5.6

RHEL/CentOS Linux下安装MySQL 5.6笔记
我照着自己这篇笔记安装了好多次mysql,虚拟机上,AWS EC2上,阿里云ECS上,AWS Lightsail上,各种VPS上,但每次都有各种意外。

Getting Started with MySQL - Installing and Starting MySQL
https://dev.mysql.com/doc/mysql-getting-started/en/#mysql-getting-started-installing

A Quick Guide to Using the MySQL Yum Repository
https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/


MAC brew 安装mysql

搜索 mysql 安装包

$ brew search mysql
==> Formulae
automysqlbackup          mysql++                  mysql-client@5.7         mysql-connector-c++@1.1  mysql-search-replace     mysql@5.7
mysql                    mysql-client             mysql-connector-c++      mysql-sandbox            mysql@5.6                mysqltuner
==> Casks
homebrew/cask/mysql-connector-python              homebrew/cask/mysql-utilities                     homebrew/cask/sqlpro-for-mysql
homebrew/cask/mysql-shell                         homebrew/cask/navicat-for-mysql

可以看到有多个版本的 mysql,不指定版本的话会安装最新版

1、安装 mysql 5.7
brew install mysql@5.7
安装目录
/usr/local/Cellar/mysql@5.7/5.7.29
安装后有一个无密码的 root 用户

2、设置环境变量,使全局可用 mysql 命令

vi ~/.zshrc
export PATH=${PATH}:/usr/local/Cellar/mysql@5.7/5.7.29/bin
source ~/.zshrc

3、启动mysql服务

mysql.server start

4、初始化mysql配置
执行脚本 /usr/local/Cellar/mysql@5.7/5.7.29/bin/mysql_secure_installation
这个脚本会以交互的形式帮助用户进行一些 mysql 初始化配置,比如设置 root 账号密码等等。

$ ./mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN 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 plugin?

Press y|Y for Yes, any other key for No: i # 不安装密码强度插件
Please set the password for root here.  # 设置 root 密码

New password:

Re-enter new password:
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 # 只允许 local 登录
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.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : o # 是否删除 test 库

 ... skipping.
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!

5、登录 mysql
mysql -u root -p 输入密码以后就可以正常进入了


添加MySQL Yum Repository

默认yum只能安装最新版mysql,我们可以自己配置通过yum安装5.6或者其他版本。

查看系统里面有没有mysql的repo

查看系统里面有没有mysql的repo
yum repolist all | grep mysql
如果没有的话,添加mysql yum repo

添加mysql yum repo

打开mysql yum repo下载页面 https://dev.mysql.com/downloads/repo/yum/
注意一定要根据 RHEL/CentOS 版本选择对应的repo源
RHEL/CentOS 7.x 上选择 Red Hat Enterprise Linux 7 / Oracle Linux 7 (Architecture Independent), RPM Package
RHEL/CentOS 6.x 上选择 Red Hat Enterprise Linux 6 / Oracle Linux 6 (Architecture Independent), RPM Package
选错源会导致后续yum安装的mysql和系统版本不对应而报错,比如在 RHEL/CentOS 6.x 上安装了 7.x 的源,后续安装yum时会提示找不到 systemd

1、在aws ec2 Amazon Linux 2 和 阿里云 ecs centos 7.6 上安装时,选择 7.x 版本
2、在aws Lightsail上使用 7.x的mysql源安装时出了问题,找不到 systemd,才知道对应的是centos6.x版本,只能使用6.x的源。

RHEL/CentOS 7.x源

选择 Red Hat Enterprise Linux 7 / Oracle Linux 7 (Architecture Independent), RPM Package
下载后得到一个 mysql80-community-release-el7-1.noarch.rpm 文件
使用scp命令把这个.rpm文件上传到服务器

将rpm添加到yum repo
sudo rpm -Uvh mysql80-community-release-el7-1.noarch.rpm

[root@ecs-t5 ~]# rpm -Uvh mysql80-community-release-el7-3.noarch.rpm
警告:mysql80-community-release-el7-3.noarch.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql80-community-release-el7-3  ################################# [100%]

或者直接通过网络添加:
下面这个下载地址可以在点开文件的下载页面后看到
sudo rpm -Uvh http://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

RHEL/CentOS 6.x源

选择 Red Hat Enterprise Linux 6 / Oracle Linux 6 (Architecture Independent), RPM Package
或者直接通过网络添加:
sudo rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el6-3.noarch.rpm

repo源安装完成后再次查看yum repo:

[ec2-user@ip ~]$ yum repolist all | grep mysql
mysql-cluster-7.5-community/x86_64   MySQL Cluster 7.5 Community  disabled
mysql-cluster-7.5-community-source   MySQL Cluster 7.5 Community  disabled
mysql-cluster-7.6-community/x86_64   MySQL Cluster 7.6 Community  disabled
mysql-cluster-7.6-community-source   MySQL Cluster 7.6 Community  disabled
mysql-connectors-community/x86_64    MySQL Connectors Community   enabled: 51+14
mysql-connectors-community-source    MySQL Connectors Community - disabled
mysql-tools-community/x86_64         MySQL Tools Community        enabled:    69
mysql-tools-community-source         MySQL Tools Community - Sour disabled
mysql-tools-preview/x86_64           MySQL Tools Preview          disabled
mysql-tools-preview-source           MySQL Tools Preview - Source disabled
mysql55-community/x86_64             MySQL 5.5 Community Server   disabled
mysql55-community-source             MySQL 5.5 Community Server - disabled
mysql56-community/x86_64             MySQL 5.6 Community Server   disabled
mysql56-community-source             MySQL 5.6 Community Server - disabled
mysql57-community/x86_64             MySQL 5.7 Community Server   disabled
mysql57-community-source             MySQL 5.7 Community Server - disabled
mysql80-community/x86_64             MySQL 8.0 Community Server   enabled:    33
mysql80-community-source             MySQL 8.0 Community Server - disabled

禁用yum repo中不想用的mysql版本

mysql yum repo 中,默认除了最新版,其他mysql版本是disable禁用的,编辑
/etc/yum.repos.d/mysql-community.repo
文件把自己想安装的版本设为enable,将其上的版本设为disable
默认 mysql-community.repo 文件如下:

# 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=0
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=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

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

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

[mysql-tools-preview]
name=MySQL Tools Preview
baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

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

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

比如我想安装 5.6,则把 mysql56-community 的 enabled 设为1,把 mysql80-community 的enable 设为0

查看yum repo中enabled的mysql版本

查看yum repo中enabled的mysql版本,确认刚才的修改:

[ec2-user@ip yum.repos.d]$ yum repolist enabled |grep mysql
mysql-connectors-community/x86_64     MySQL Connectors Community          51+14
mysql-tools-community/x86_64          MySQL Tools Community                  69
mysql56-community/x86_64              MySQL 5.6 Community Server            412

yum安装mysql-community-server

yum安装 mysql-community-server
sudo yum install mysql-community-server

6.x系统使用7.x的源安装mysql出错提示需要 systemd

在 aws Lightsail 上 yum 安装 mysql5.6 时遇到下面的问题,需要 systemd

---> 软件包 mysql-community-server.x86_64.0.5.6.44-2.el7 将被 安装
--> 正在处理依赖关系 systemd,它被软件包 mysql-community-server-5.6.44-2.el7.x86_64 需要
--> 正在处理依赖关系 systemd,它被软件包 mysql-community-server-5.6.44-2.el7.x86_64 需要
---> 软件包 perl-Compress-Raw-Bzip2.x86_64.0.2.061-3.11.amzn1 将被 安装
---> 软件包 perl-Compress-Raw-Zlib.x86_64.1.2.061-4.1.amzn1 将被 安装
--> 解决依赖关系完成
错误:软件包:mysql-community-server-5.6.44-2.el7.x86_64 (mysql56-community)
          需要:systemd

原因:6.x 系统,没有 systemctl 命令,但安装了 7.x yum源
解决:删除 7.x 的mysql源后,重新安装 6.x 的mysql源

$ rpm -qa|grep mysql
mysql80-community-release-el7-1.noarch
$ sudo rpm -e mysql80-community-release-el7-1.noarch
警告:/etc/yum.repos.d/mysql-community.repo 已另存为 /etc/yum.repos.d/mysql-community.repo.rpmsave

删除yum repo缓存
一开始我删除7.x的repo后马上安装6.x的repo,结果安装的mysql版本还是7.x的,原来是yum repo有缓存,需要手动删除repo缓存,否则还会读取缓存中的7.x repo

$ cd /var/cache/yum/x86_64/latest/
$ ls
amzn-main  amzn-updates  mysql56-community  mysql-connectors-community  mysql-tools-community  nodesource  timedhosts
$ sudo rm -rf mysql*

再次安装就好了。


mysql的主要配置文件位置

默认安装的位置,如果不修改的情况下!

/etc/my.cnf 配置文件

1、mysql的主要配置文件
/etc/my.cnf

mysql 5.6 默认为:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

/var/lib/mysql 数据文件

2、mysql 数据库的数据文件存放位置
/var/lib/mysql

/var/log/mysqld.log 日志

3、mysql数据库日志输出文件
/var/log/mysqld.log

设置字符集为utf8mb4

修改 /etc/my.cnf , 增加字符集配置, 修改后要重启mysql

[mysqld]
character-set-server = utf8mb4

[client]
default-character-set = utf8mb4

[mysql]
default-character-set=utf8mb4

重启后查看字符集

mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

配置mysql绑定ip

查看mysql server绑定ip
show variables like 'bind_address';
默认是*,表示接收所有的IPv4 或 IPv6 连接请求,如没有更改过就不用配置。


我的my.cnf配置

改动:
1、指定了三个字符集
2、改小了 innodb 缓存大小,默认是128,改成64M了,否则在500M内存的机器上起不来。

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 64M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

character-set-server = utf8mb4

[client]
default-character-set = utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

启动mysqld service

RHEL/CentOS 6.x 系统上:
sudo service mysqld start 启动mysql服务
sudo service mysqld stop 停止mysql服务
sudo service mysqld restart 重启mysql服务
sudo service mysqld status 检查httpd服务状态

RHEL/CentOS 7.x 系统上:
sudo systemctl start mysqld.service

sudo systemctl start mysql.service
mysql.service 和 mysqld.service 互为别名

在aws ec2上启动时必须加sudo,否则无法启动:

[ec2-user@ip init.d]$ service mysqld start
Redirecting to /bin/systemctl start mysqld.service
Failed to start mysqld.service: The name org.freedesktop.PolicyKit1 was not provided by any .service files

内存不足导致InnoDB引擎初始化失败导致mysql启动失败

安装完成后启动 mysqld 服务失败:

$ sudo service mysqld start
MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]

查看mysql日志 /var/log/mysqld.log
发现是初始化 InnoDB 引擎时要分配128M内存失败,我机器只有500兆内存

2019-07-15 05:12:17 8636 [Note] InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
2019-07-15 05:12:17 8636 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2019-07-15 05:12:17 8636 [ERROR] Plugin 'InnoDB' init function returned error.
2019-07-15 05:12:17 8636 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2019-07-15 05:12:17 8636 [ERROR] Unknown/unsupported storage engine: InnoDB
2019-07-15 05:12:17 8636 [ERROR] Aborting

Innodb 存储引擎的缓存机制和 MyISAM 的最大区别就在于 Innodb 不仅仅缓存索引,同时还会缓存实际的数据。所以,完全相同的数据库,使用 Innodb 存储引擎可以使用更多的内存来缓存数据库相关的信息,当然前提是要有足够的物理内存。
innodb_buffer_pool_size 参数用来设置 Innodb 最主要的 Buffer(Innodb_Buffer_Pool)的大小,也 就是缓存用户表及索引数据的最主要缓存空间,对 Innodb 整体性能影响也最大。这个参数设置成内存的50%-80%,当然具体要结合实际情况而定,考虑别的存储引擎占用的内存,考虑服务器是不是还提供其他服务等等。

修改 /etc/my.cnf 配置文件,将 innodb_buffer_pool_size 改小为 32M后来又改为16M,重启mysql,还是报同样的错误。同时我free -m看了下可用内存,还有300多M,不知为啥就是无法启动。
后来考虑了下,应该是500M内存怎么都不够,即使改小 innodb缓存还有其他地方需要的内存总量大于500M,只能给linux加个swap空间,即添加虚拟内存。

Mysql cannot allocate memory for the buffer pool 解决方法
https://blog.csdn.net/Mr_OOO/article/details/78653523

给linux添加swap交换文件(即虚拟内存)

我在 500M 内存的机器上安装mysql时遇到无法启动才需要设置虚拟内存,在1G内存机器上安装不需要此步骤。
可以先 freeswapon -s 查看系统是否有虚拟内存,没有的话按如下步骤增加。

1、创建大小为 1G 的交换文件:

sudo dd if=/dev/zero of=/swapfile1 bs=1M count=1024
记录了1024+0 的读入
记录了1024+0 的写出
1073741824字节(1.1 GB)已复制,14.7397 秒,72.8 MB/秒

2、修改文件的权限,避免其他用户对这个文件进行误操作:
sudo chmod 600 /swapfile1

3、使用mkswap命令来设置交换文件:

sudo mkswap /swapfile1
正在设置交换空间版本 1,大小 = 1048572 KiB
无标签,UUID=b30e9371-0e0b-40b4-bd85-624aeb173aa1

4、启用交换文件:
sudo swapon /swapfile1

5、写入/etc/fstab,以便在系统启动时自动加载交换文件:
/swapfile1 none swap defaults 0 0

$ cat /etc/fstab
#
LABEL=/     /           ext4    defaults,noatime  1   1
tmpfs       /dev/shm    tmpfs   defaults        0   0
devpts      /dev/pts    devpts  gid=5,mode=620  0   0
sysfs       /sys        sysfs   defaults        0   0
proc        /proc       proc    defaults        0   0
/swapfile1 none swap defaults 0 0

新添了交换分区并启用它之后,请查看 cat /proc/swapsfreeswapon -s 命令的输出来确保交换分区已被启用了。

$ free -m
             total       used       free     shared    buffers     cached
Mem:           481        469         12          0         44        353
-/+ buffers/cache:         71        410
Swap:         1023          0       1023
$ swapon -s
文件名                类型        大小    已用    权限
/swapfile1  file    1048572    0    -2

设置mysql开机启动(chkconfig)

查看mysql是否自启动,chkconfig 是在linux中设置开启自启动的命令

# chkconfig --list | grep mysqld
mysqld          0:off   1:off   2:off   3:on    4:on    5:on    6:off

设置开机启动

# chkconfig mysqld on
mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off

在linux中安装mysql5.6,设置开机自启,设置utf-8编码
https://blog.csdn.net/lu1171901273/article/details/81987469

设置mysql开机启动(systemctl)

在 RHEL7 或 centos7 系统上使用 systemctl 代替 chkconfig 来管理服务
chkconfig mysqld on 会自动转为 systemctrl 命令,或者直接使用 systemctrl 来 设置 mysql 开机启动
sudo systemctl enable mysqld.service

在centos7上chkconfig --list命令无法看到全部系统服务, 会提示:

[ec2-user@ip bin]$ chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

netconsole      0:off   1:off   2:off   3:off   4:off   5:off   6:off
network         0:off   1:off   2:on    3:on    4:on    5:on    6:off

使用 systemctl 命令来查看开机启动设置
systemctl list-unit-files

[root@ecs-t5 ~]# systemctl list-unit-files |grep mysql
mysql.service                                 enabled
mysqld.service                                enabled

enabled 表示已设为开机启动


登录mysql

mysql登录命令:
mysql [-h host] [-P port] -u {user} -p{password} {database}
-h 表示远程主机名或ip地址
-P 大写P表示数据库端口号
-u 表示用户名
-p 小写p表示密码
命令最后如果加数据库名,则登录后直接切换为命令中的数据库,否则登录后的当前数据库为none
注意user、host、port前可以有空格也可以没有空格,但是如果-p后带有用户密码,那么-p与密码之间必须没有空格,否则让你重新输入密码

mysql -h mysql.myhost.com -u root -p
Enter password:(输入密码)

登录后进入mysql命令行
或者-p后直接跟密码登录:

mysql -h mysql.myhost.com -u root -p123456 database

MySQL安装完成后会自动提供一个不带密码的root用户,使用root账号登录mysql:
mysql -u root

设置root用户密码

MySQL安装完成后会自动提供一个不带密码的root用户,为了安全起见给root设置密码123456。

修改root密码的三种办法
首先命令行登录MySQL。

用SET PASSWORD命令

方法1: 用SET PASSWORD命令
首先使用无密码的root账号登陆mysql
格式:mysql> set password for 用户名@localhost = password(‘新密码’);
例子:mysql> set password for root@localhost = password('123456');

mysql> set password for root@localhost = password('123456');
Query OK, 0 rows affected (0.00 sec)

13.7.1.7 SET PASSWORD Syntax
https://dev.mysql.com/doc/refman/5.6/en/set-password.html

用mysqladmin命令

方法2:用mysqladmin命令
注意 mysqladmin 命令是在mysql外使用的
格式:mysqladmin -u用户名 -p旧密码 password 新密码
例子:
如果root还没设置过密码:mysqladmin -u root password 123456
如果root已设置过密码:mysqladmin -u root -p 123456 password 123

直接update user表

方法3:用UPDATE直接编辑user表

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

设置密码后登入时需要输入密码:

mysql -u root -p (-u 后跟登入的用户名,-p 提示要密码登入)
Enter password:(输入密码)

数据库操作

显示数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

Mysql刚安装完有三个数据库:mysql, information_schema, performance_schema
mysql库非常重要,它里面有MySQL的系统信息,我们改密码和新增用户,实际上就是用这个库中的相关表进行操作。

13.7.5.15 SHOW DATABASES Syntax
https://dev.mysql.com/doc/refman/5.6/en/show-databases.html

打开数据库

mysql> use mysql;
Database changed

对哪个数据库进行操作就要先打开哪个库,打开后会显示Database changed

13.8.4 USE Syntax
https://dev.mysql.com/doc/refman/5.6/en/use.html

显示数据库中的表

首先打开相应数据库,然后

mysql> show tables;
+------------------------+
| Tables_in_mysql |
+------------------------+
| columns_priv  |
| db        |
| func        |
| host        |
| tables_priv      |
| user        |
+------------------------+
6 rows in set (0.01 sec)

13.7.5.38 SHOW TABLES Syntax
https://dev.mysql.com/doc/refman/5.6/en/show-tables.html

创建数据库

例如:创建一个名字位SMS_RTMP的库

mysql> create database SMS_RTMP;

13.1.10 CREATE DATABASE Syntax
https://dev.mysql.com/doc/refman/5.6/en/create-database.html

修改数据库名

Mysql没有直接更改数据库名称的命令。

注:RENAME DATABASE这条命令在MySQL 5.1.7中被加入,但很快就发现这条命令所带来的危险,于是在MySQL 5.1.23中这条命令被移除。

假如现在我们想把数据库名由 Hwei 更改为 hwei。

我们可以在数据库外执行以下命令:

mysqladmin -u root -p create hwei
mysqldump Hwei | mysql -u root -p hwei
当你确定原数据库中的数据都被复制到了新数据库中,就可以把原数据库删掉了。
drop database Hwei


创建用户

create user命令创建用户

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
如果没有@host参数,默认是%,即所有主机,例如:
create user usrname identified by 'password';

13.7.1.2 CREATE USER Syntax
https://dev.mysql.com/doc/refman/5.6/en/create-user.html

直接插入mysql.user表

以root用户登入,在mysql中有一张mysql.user表是存储MySQL中所有用户的信息表,所以可以直接增加删除这个表的记录就可增加和删除用户;
直接对mysql.user添加一条记录

insert into mysql.user(Host,User,Password) values("localhost","username",password("123"));
flush privileges;

这样就创建了一个名为:username 密码为:123 (密码是经过加密的 ) 的用户,不过这样没有权限因为只添加了三个字段,也可通过grant添加权限:

mysql创建普通用户后无法登陆(删除匿名用户)

新建了一个root用户外的普通用户,但始终无法登陆,总是提示密码错误,如下:

[ec2-user@ip- ~]$ mysql -u masikkk -p
Enter password:
ERROR 1045 (28000): Access denied for user 'masikkk'@'localhost' (using password: YES)

解决方法:
增加普通用户后,执行:

mysql> use mysql
mysql> delete from user where user='';
mysql> flush privileges;

意思是删除匿名用户。
注意:删除之后必须刷新系统权限表使生效

删除前:

mysql> select User,Host,Password from user;
+---------+--------------------------------------------+-------------------------------------------+
| User    | Host                                       | Password                                  |
+---------+--------------------------------------------+-------------------------------------------+
| root    | localhost                                  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root    | ip-172-31-31-59.us-west-2.compute.internal |                                           |
| root    | 127.0.0.1                                  |                                           |
| root    | ::1                                        |                                           |
|         | localhost                                  |                                           |
|         | ip-172-31-31-59.us-west-2.compute.internal |                                           |
| masikkk | %                                          | *A4B81B2C357B4AE327A5217C543A151B7522CF98 |
+---------+--------------------------------------------+-------------------------------------------+
7 rows in set (0.00 sec)

删除后:

mysql> select User,Host,Password from user;
+---------+--------------------------------------------+-------------------------------------------+
| User    | Host                                       | Password                                  |
+---------+--------------------------------------------+-------------------------------------------+
| root    | localhost                                  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root    | ip-172-31-31-59.us-west-2.compute.internal |                                           |
| root    | 127.0.0.1                                  |                                           |
| root    | ::1                                        |                                           |
| masikkk | %                                          | *A4B81B2C357B4AE327A5217C543A151B7522CF98 |
+---------+--------------------------------------------+-------------------------------------------+
5 rows in set (0.00 sec)

然后使用新建的用户登录成功。
之前真没注意有匿名用户,后来想了想,直接在linux命令行输入mysql命令也能登录,原来这种没指定用户名的情况下用的就是匿名用户,登录后只能看到一个information_schema库
删除匿名用户后,直接输入mysql命令就是用linux的用户名登陆了,如果没在mysql中创建这个用户,就会登录失败。

删除用户

DROP USER user [, user]

13.7.1.3 DROP USER Syntax
https://dev.mysql.com/doc/refman/5.6/en/drop-user.html

grant给新建用户授权

新创建的用户还没有任何表的读写权限,可以通过grant语句给用户赋权。
grant 权限 on 数据库.表 to 用户名@登录主机 identified by "密码"

使用root账号登录(注意只有root账号登录才行),执行:

grant all on *.* to username@localhost;
flush privileges;

all表示所有权限(包括增 删 改 查等权限), *.* 表示所有数据库的所有表,username为添加的用户名,
localhost为匹配的主机,如果没有@host参数,默认是%,即所有主机,例如:

grant all on *.* to username;
flush privileges;

如果直接给一个不存在的用户名赋权,也就直接创建了这个用户,比如:

grant all on *.* to username identified by "password";
flush privileges;

执行完会在mysql.user表插入一条记录。

注意:授权之后必须刷新系统权限表使赋权生效

13.7.1.4 GRANT Syntax
https://dev.mysql.com/doc/refman/5.6/en/grant.html

远程访问

现在新版的mysql一般默认都不允许远程连接的。需要建立远程连接账号才可以。
查看user表中默认的账号和host:

mysql> select Host , User, Password from user;
+--------------------------------------------+------+-------------------------------------------+
| Host                                       | User | Password                                  |
+--------------------------------------------+------+-------------------------------------------+
| localhost                                  | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ip-xxxxx.us-west-2.compute.internal        | root |                                           |
| 127.0.0.1                                  | root |                                           |
| ::1                                        | root |                                           |
| localhost                                  |      |                                           |
| ip-xxxxx.us-west-2.compute.internal        |      |                                           |
+--------------------------------------------+------+-------------------------------------------+
6 rows in set (0.00 sec)

可以看到,root账号只允许本地连接。
为了方便远程操作数据库,我们可以给新创建的账号开启远程连接,这样就可以远程连接mysql:
mysql -h mysql.myhost.com -u username -pxxxx
或者使用 Navicat 或 datagrip 连接,也可以在应用服务器上使用此账号连接。
为了安全,推荐专门开一个可远程连接的账号,root账号不开远程连接

如果让某个指定的ip可以远程使用root账号登录,可以update user表:
UPDATEuserSETHost='192.192.192.192' WHEREUser='root' and Host='localhost';
这样192.192.192.192这台web服务器就可以远程连接到这个数据库服务器了。
假如你想让任何远程机器都可以连接这个数据库,就将192.192.192.192换为%

使账号可以远程访问

UPDATE `user` SET `Host`='%' WHERE `User`='username';
flush privileges;

表示使 username 账号可被所有ip访问。

或者新创建一个账号,Host为%,即允许所有主机连接,并给账号授权。

防火墙导致无法远程连接

mysql安装并配置可远程连接的账号后还是会连接失败,报错:

$ mysql -u username -h xx.xx.xx.xx -ppassword;
ERROR 2002 (HY000): Can't connect to MySQL server on 'xx.xx.xx.xx' (36)

可能的一个原因是,服务器的防火墙阻断了 mysql 的 3306 端口
比如我使用过的 aws ec2, aws Lightsail 以及 阿里云 ecs,默认都只开放了 ssh 的22端口,最多也只再开放了http的80端口,所以如果不手动打开 3306 端口是无法远程访问的。

修改用户密码

使用root账号登录,切换到mysql数据库

set password for 'username'@'%' = password('password');

Access denied for user username@ip

指定的 username 已开启远程登录和授权

grant all on *.* to 'username'@'%';

在远端通过终端登录 mysql -h mysql.myhost.com -u username -pxxxx 一切都ok,能查询能修改,但在 datagrip 或 idea 客户端中始终无法登录,提示:
The specified user/password combination is rejected: [28000][1045] Access denied for user ‘username’@’ip’ (using password: YES)

在网上查解决方法,都说的是没把host设为%导致无法远程登录,或者没有赋权,但我都弄了的。

还有的说 mysql 5.7 会有密码自动过期策略,但我看了下我用的5.6啊,而且 user表中最后一列的 password_expired 字段值为 N,密码没过期啊。再说如果密码过期了我在命令行中可定也无法登录的。

最后试了下改密码,修改密码后竟然可以在客户端登录了,好奇怪。但是只是第一次可以,后来我在 datagrip 中删除连接,再重连,又不行了。

上一篇 VMware虚拟机

下一篇 SVN

阅读
评论
6,529
阅读预计31分钟
创建日期 2015-04-01
修改日期 2020-07-07
类别

页面信息

location:
protocol:
host:
hostname:
origin:
pathname:
href:
document:
referrer:
navigator:
platform:
userAgent:

评论