当前位置 : 首页 » 文章分类 :  开发  »  MySQL-Linux安装

MySQL-Linux安装

Linux下安装mysql笔记

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/


添加MySQL Yum Repository

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

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

yum repolist all | grep mysql
如果没有的话,添加mysql yum repo

添加mysql yum repo

打开mysql yum repo下载页面 https://dev.mysql.com/downloads/repo/yum/
我用的是aws ec2 Amazon Linux,不知道对应的是哪个linux发行版的repo,就选了个 Red Hat Enterprise Linux 7 / Oracle Linux 7 (Architecture Independent), RPM Package
下载后得到一个 mysql80-community-release-el7-1.noarch.rpm 文件
使用scp命令把这个.rpm文件上传到aws ec2

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

或者直接通过网络添加:
sudo rpm -Uvh http://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

再次查看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
aws ec2速度好快,安装瞬间完成。


mysql的主要配置文件位置

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

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

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

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


启动mysql service

sudo service mysqld start

在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

设置mysql开机启动

查看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

aws ec2设置mysql开机启动

aws ec2 Amazon linux上的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 list-unit-files命令查看:

[ec2-user@ip bin]$ 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 -u root -p
Enter password:(输入密码)

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

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

设置root用户密码

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

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

用SET PASSWORD命令

方法1: 用SET PASSWORD命令
格式: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 -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 |
+--------------+
| mysql  |
| test     |
+--------------+
2 rows in set (0.04 sec)

Mysql刚安装完有两个数据库:mysql和test。
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 '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 权限 on 数据库.表 to 用户名@登录主机 identified by "密码"

使用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账号只允许本地连接。

如果让某个指定的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访问。

上一篇 VMware虚拟机

下一篇 SVN

阅读
3,165
阅读预计15分钟
创建日期 2015-04-01
修改日期 2018-09-09
类别
百度推荐