mysql mysql is a_MySQL基本操作

た入场券 2022-10-26 00:52 172阅读 0赞

一MySQL数据库的安装

在7上面使用的mariadb是5.5版本的，和MySQL的5.5版本是一样的

5.6之后版本号就和MySQL不一样了，是10版本了。

在官网上面有3种类型的，tar.gz后缀表示的是源码包。

要学会3种方式安装，源码，二进制， yum安装。最后一种最简单。

![5deb5309b34505a642f5a6d90d42e172.png][]

![791dd346bb999f0330a45a880f50a703.png][]

第二种选择不带库的二进制的包，因为我们不做开发。这个是编译过的，所以解压缩目录不能随便放的。

![884763b62ed1f8d37d5410104c91aa9e.png][]

第三种yum包安装   For best results with RPM packages, use the Repository Configuration Tool

![b36d88a6e550b9f7cf0d49a30e04b239.png][]

安装MySQL或者Mariadb的方式：

1、源代码：编译安装

2、二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用

3、程序包管理器管理的程序包CentOS安装光盘

项目官方：https://downloads.mariadb.org/mariadb/repositories/

To generate the entries select an item from each of the boxes below. Once an item is selected in each box, your customized repository configuration will appear below.

1. Choose a Distro

SLES

openSUSE

Arch Linux

Mageia

Fedora

CentOS

RedHat

Mint

Ubuntu

Debian

2. Choose a Release

CentOS 7 (ppc64le)

CentOS 7 (ppc64)

CentOS 7 (x86\_64)

CentOS 6 (x86\_64)

CentOS 6 (x86)

3. Choose a Version

10.3 \[Stable\]

10.4 \[Alpha\]

10.2 \[Stable\]

10.1 \[Stable\]

10.0 \[Stable\]

5.5 \[Stable\]

Here is your custom MariaDB YUM repository entry for CentOS. Copy and paste it into a file under /etc/yum.repos.d/ (we suggest naming the file MariaDB.repo or something similar).

\# MariaDB 10.2 CentOS repository list - created 2018-12-06 07:59 UTC \# http://downloads.mariadb.org/mariadb/repositories/

\[mariadb\] name = MariaDB

gpgcheck=1

After the file is in place, install MariaDB with:

sudo yum install MariaDB-server MariaDB-client

If you haven't already accepted the MariaDB GPG key, you will be prompted to do so. See "Installing MariaDB with yum" for detailed information.

Please see Installing OQGraph for details on additional install steps needed for that storage engine.

\# MariaDB 10.2 CentOS repository list - created 2018-12-06 07:59 UTC

\# http://downloads.mariadb.org/mariadb/repositories

只要在配置yum源的文件里面输入下面的内容就可以进行在线安装了。

我安装的是10.3，那么baseurl = http://yum.mariadb.org/10.3/centos7-amd64

\[mariadb\]

name = MariaDB

baseurl = http://yum.mariadb.org/10.2/centos7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

配置一下yum源，进行yum安装。我们要安装服务器端的，后面加上server。注意工作一般使用的是5.5，因为比较稳定

重点要掌握的是二进制和源码包安装。二进制安装好，解压缩配一下就好了。编译的话时间就会比较长

\[root@centos7 yum.repos.d\]\# yum install mariadb-server.x86\_64 -y

二MySQL和MariaDB的信息

官方网址：

https://www.mysql.com/

http://mariadb.org/

官方文档

https://dev.mysql.com/doc/

https://mariadb.com/kb/en/

版本演变：

MySQL：5.1 --> 5.5 --> 5.6 --> 5.7

MariaDB：5.5 -->10.0--> 10.1 --> 10.2 --> 10.3

三数据库的体系结构

![9f2689eb59aae1b88457285618cf1520.png][]

每一个代表的是功能模块。mysql是对外提供服务，不是本机自己访问自己，所以要有一个管理连接的模块。

MySQL是多线程的，一启动就会出现很多的线程。所以有线程管理模块。

命令分发器，当一个用户通过远程连接过来的时候就有一个用户管理的模块

比如有一个用户发一个指令过来，在数据库里面查询一个表。而命令分发器来决定到底如何执行这个命令

其中可能会利用缓存，就是曾经或者刚刚执行过了这个查询语句了，就不要再重新解释执行一次了，缓存里面的数据直接拿过来用就可以了。

我们平时敲外部命令会用HASH，会把命令记录到内存里面去了

下次访问就不要到磁盘上面找一圈了。

\[root@centos7 yum.repos.d\]\# vim mariadb.repo

\[root@centos7 yum.repos.d\]\# ls

dvd.repo mariadb.repo nginx.repo

\[root@centos7 yum.repos.d\]\# hash 这个查询缓存模块的作用也是一样的，执行了一遍就不需要再次执行了。

hits command

3 /usr/bin/vim

3 /usr/bin/ls

日志记录功能，做了哪些命令会有日志记录。命令解释器对用户发过来的命令进行解释执行

对表做什么操作，是查询还是表的变更。用户访问就涉及到了访问控制模块。

不同是引擎其算法机制底层是不太一样的

MySQL5.1之前默认用的是MyISAM，5.5之后默认用的是innodb。

刚学习数据库，我们掌握innodb就可以了

myisam和innodb的区别

前者不支持事务，这样会造成数据的损坏。而且有锁，并发访问性能比较差的,

也就是只能一个人修改表，因为系统会自动往这个表加锁。

很多人同时访问不能修改表

而后者是基于行级锁，也就是一个人修改一条记录，只是把一条记录锁住。

而第二个人要修改的是第10行，和第一个人不冲突的

这两个区别也说明了我们为什么要使用innodb。

注意在工作中是不升级的。只有重新安装。因为升级的问题更多

包括Linux系统也是不升级的

\[root@centos7 yum.repos.d\]\# rpm -ql mariadb

package mariadb is not installed

\[root@centos7 yum.repos.d\]\# rpm -ql M

MariaDB-client MariaDB-common MariaDB-compat MariaDB-server ModemManager-glib

\[root@centos7 yum.repos.d\]\# rpm -ql MariaDB-server

etc/my.cnf.d这个是他的配置文件。他的目录下面的文件也是配置文件，比如 /etc/my.cnf.d/server.cnf，相当于子配置文件。

服务存放位置/usr/lib/systemd/system/mariadb.service

在启动之前查看一下端口号，没有看到3306启动

\[root@centos7 ~\]\# ss -tnl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 128 \*:111 \*:\*

LISTEN 0 5 192.168.122.1:53 \*:\*

LISTEN 0 128 \*:22 \*:\*

LISTEN 0 128 127.0.0.1:6010 \*:\*

LISTEN 0 128 127.0.0.1:6011 \*:\*

LISTEN 0 128 :::111 :::\*

LISTEN 0 128 :::22 :::\*

LISTEN 0 128 ::1:6010 :::\*

LISTEN 0 128 ::1:6011

注意在mariadb的情况下服务名和包名不一样，服务名是不带server而且.service是可以忽略的，而包名是带client

\[root@centos7 ~\]\# systemctl start mariadb.service

\[root@centos7 ~\]\# ss -tnl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 128 \*:111 \*:\*

LISTEN 0 5 192.168.122.1:53 \*:\*

LISTEN 0 128 \*:22 \*:\*

LISTEN 0 128 127.0.0.1:6010 \*:\*

LISTEN 0 128 127.0.0.1:6011 \*:\*

LISTEN 0 80 :::3306 :::\*

LISTEN 0 128 :::111 :::\*

LISTEN 0 128 :::22 :::\*

LISTEN 0 128 ::1:6010 :::\*

LISTEN 0 128 ::1:6011 :::\*

如果是MySQL的话，那么客户端的包名就是MySQL了。

在安装服务器的时候就默认把客户端安装好了

![158ed3d4e1edee00b3602bfbbf13e412.png][]

![43bf59fbdf65c8e80cb5231568825a3c.png][]

\[root@centos7 ~\]\# rpm -ql mariadb

package mariadb is not installed

\[root@centos7 ~\]\# rpm -ql MariDB

package MariDB is not installed

\[root@centos7 ~\]\# whichmysql有个客户端工具叫做mysql，也是客户端的命令。

/usr/bin/mysql

\[root@centos7 ~\]\# rpm -qf /usr/bin/mysql

MariaDB-client-10.3.11-1.el7.centos.x86\_64注意在mariadb里面不存在5.6版本，而是10版本了。在MySQL里面才叫5.5，5.6了。

\[root@centos7 yum.repos.d\]\# rpm -ql MariaDB-server

/etc/init.d/mysql

/etc/logrotate.d/mysql

/etc/my.cnf.d配置文件

\[root@centos7 ~\]\# yum infomariadb

Loaded plugins: fastestmirror, langpacks

Loading mirror speeds from cached hostfile

Available Packages

Name : mariadb 注意不带server的是客户端。

Arch : x86\_64

Epoch : 1Version : 5.5.56Release : 2.el7

Size : 8.7M

Repo : dvd

Summary : A community developed branch of MySQL

URL : http://mariadb.org

License : GPLv2 with exceptions and LGPLv2 and BSD

Description : MariaDB is a community developed branch of MySQL.

: MariaDB is a multi-user, multi-threaded SQL database server.

: It is a client/server implementation consisting of a server daemon (mysqld)

: and many different client programs and libraries. The base package

: contains the standard MariaDB/MySQL client programs and generic MySQL files.

查看MySQL的官网。

![a313cce497e080c720f627cf00a8d9f9.png][]

这个是自己连自己，以交互式的方式执行一些命令.

\[root@centos7 ~\]\# mysql

Welcome to the MariaDB monitor. Commands end with ; or \\g.

Your MariaDB connection id is 8

Server version: 10.3.11-MariaDB MariaDB Server

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

查看帮助

MariaDB \[(none)\]> \\h

General information about MariaDB can be found at

http://mariadb.org

List of all MySQL commands:

Note that all text commands must be first on line and end with ';'

? (\\?) Synonym for \`help'.

clear (\\c) Clear the current input statement.

connect (\\r) Reconnect to the server. Optional arguments are db and host.

delimiter (\\d) Set statement delimiter.

edit (\\e) Edit command with $EDITOR.

ego (\\G) Send command to mysql server, display result vertically.

exit (\\q) Exit mysql. Same as quit.

go (\\g) Send command to mysql server.

help (\\h) Display this help.

nopager (\\n) Disable pager, print to stdout.

notee (\\t) Don't write into outfile.

pager (\\P) Set PAGER \[to\_pager\]. Print the query results via PAGER.

print (\\p) Print current command.

prompt (\\R) Change your mysql prompt.

quit (\\q) Quit mysql.

rehash (\\\#) Rebuild completion hash.

source (\\.) Execute an SQL script file. Takes a file name as an argument.

status (\\s) Get status information from the server.

system (\\!) Execute a system shell command.

tee (\\T) Set outfile \[to\_outfile\]. Append everything into given outfile.

use (\\u) Use another database. Takes database name as argument.

charset (\\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.

warnings (\\W) Show warnings after every statement.

nowarning (\\w) Don't show warnings after every statement.

查看数据库的基本信息

MariaDB \[(none)\]> \\s

\--------------

mysql Ver 15.1 Distrib 10.3.11-MariaDB, for Linux (x86\_64) using readline 5.1

Connection id: 10

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server: MariaDB

Server version: 10.3.11-MariaDB MariaDB Server

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db characterset: latin1

Client characterset: utf8

Conn. characterset: utf8

UNIX socket: /var/lib/mysql/mysql.sock

Uptime: 7 min 13 sec

Threads: 7 Questions: 8 Slow queries: 0 Opens: 16 Flush tables: 1 Open tables: 9 Queries per second avg: 0.018

\--------------

更改提示符

prompt (\\R) Change your mysql prompt.

MariaDB \[(none)\]> 中的none表示我们没有在任何的数据库里面

查看数据库的列表

MariaDB \[(none)\]> show databases;

\+--------------------+

| Database |

\+--------------------+

| information\_schema |

| mydb |

| mysql |

| performance\_schema |

| test |

| testdb |

\+--------------------+

6 rows in set (0.035 sec)

数据库是放在 /var/lib/mysql

有6个数据库，但是 information\_schema是系统内置的数据库没有显示出。有些是虚拟数据库，不一定是真实存在的。

\[root@centos7 yum.repos.d\]\# cd /var/lib/mysql/

\[root@centos7 mysql\]\# ls

aria\_log.00000001 centos7.pid ibdata1 ib\_logfile1 multi-master.info mysql performance\_schema test

aria\_log\_control ib\_buffer\_pool ib\_logfile0 ibtmp1 mydb mysql.sock tc.log testdb

数据库是一个文件夹，我们可以打开看到里面相应的文件

![a73b6b18806871840ef3e88f0b5c83df.png][]

![61750cc98cdd0f78b37633230ade81b0.png][]

查询表

查看所有的字段\*，要指定数据库

MariaDB \[(none)\]> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB \[mysql\]> select \* from user;

\+-----------------------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+

MariaDB \[mysql\]> select \*from user\\G\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*Host: localhost

User: root

Password:\*A4B6157319038724E3560894F7F932C8886EBFCF

Select\_priv: Y

Insert\_priv: Y

Update\_priv: Y

Delete\_priv: Y

Create\_priv: Y

Drop\_priv: Y

Reload\_priv: Y

Shutdown\_priv: Y

Process\_priv: Y

File\_priv: Y

Grant\_priv: Y

References\_priv: Y

Index\_priv: Y

Alter\_priv: Y

Show\_db\_priv: Y

Super\_priv: Y

Create\_tmp\_table\_priv: Y

Lock\_tables\_priv: Y

Execute\_priv: Y

Repl\_slave\_priv: Y

Repl\_client\_priv: Y

Create\_view\_priv: Y

Show\_view\_priv: Y

Create\_routine\_priv: Y

Alter\_routine\_priv: Y

Create\_user\_priv: Y

Event\_priv: Y

Trigger\_priv: Y

Create\_tablespace\_priv: Y

ssl\_type:

ssl\_cipher:

x509\_issuer:

x509\_subject:

max\_questions:0max\_updates:0max\_connections:0max\_user\_connections:0plugin:

authentication\_string:

password\_expired: N

is\_role: N

default\_role:

max\_statement\_time:0.000000

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*Host: centos72.huawei.com

User: root

Password:\*23AE809DDACAF96AF0FD78ED04B6A265E05AA257

Select\_priv: Y

Insert\_priv: Y

Update\_priv: Y

Delete\_priv: Y

Create\_priv: Y

Drop\_priv: Y

Reload\_priv: Y

Shutdown\_priv: Y

Process\_priv: Y

File\_priv: Y

Grant\_priv: Y

References\_priv: Y

Index\_priv: Y

Alter\_priv: Y

Show\_db\_priv: Y

Super\_priv: Y

Create\_tmp\_table\_priv: Y

Lock\_tables\_priv: Y

Execute\_priv: Y

Repl\_slave\_priv: Y

Repl\_client\_priv: Y

Create\_view\_priv: Y

Show\_view\_priv: Y

Create\_routine\_priv: Y

Alter\_routine\_priv: Y

Create\_user\_priv: Y

Event\_priv: Y

Trigger\_priv: Y

Create\_tablespace\_priv: Y

ssl\_type:

ssl\_cipher:

x509\_issuer:

x509\_subject:

max\_questions:0max\_updates:0max\_connections:0max\_user\_connections:0plugin:

authentication\_string:

password\_expired: N

is\_role: N

default\_role:

max\_statement\_time:0.000000

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*Host:127.0.0.1User: root

Password:\*23AE809DDACAF96AF0FD78ED04B6A265E05AA257

Select\_priv: Y

Insert\_priv: Y

Update\_priv: Y

Delete\_priv: Y

Create\_priv: Y

Drop\_priv: Y

Reload\_priv: Y

Shutdown\_priv: Y

Process\_priv: Y

File\_priv: Y

Grant\_priv: Y

References\_priv: Y

Index\_priv: Y

Alter\_priv: Y

Show\_db\_priv: Y

Super\_priv: Y

Create\_tmp\_table\_priv: Y

Lock\_tables\_priv: Y

Execute\_priv: Y

Repl\_slave\_priv: Y

Repl\_client\_priv: Y

Create\_view\_priv: Y

Show\_view\_priv: Y

Create\_routine\_priv: Y

Alter\_routine\_priv: Y

Create\_user\_priv: Y

Event\_priv: Y

Trigger\_priv: Y

Create\_tablespace\_priv: Y

ssl\_type:

ssl\_cipher:

x509\_issuer:

x509\_subject:

max\_questions:0max\_updates:0max\_connections:0max\_user\_connections:0plugin:

authentication\_string:

password\_expired: N

is\_role: N

default\_role:

max\_statement\_time:0.000000

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 4. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*Host: ::1User: root

Password:\*23AE809DDACAF96AF0FD78ED04B6A265E05AA257

Select\_priv: Y

Insert\_priv: Y

Update\_priv: Y

Delete\_priv: Y

Create\_priv: Y

Drop\_priv: Y

Reload\_priv: Y

Shutdown\_priv: Y

Process\_priv: Y

File\_priv: Y

Grant\_priv: Y

References\_priv: Y

Index\_priv: Y

Alter\_priv: Y

Show\_db\_priv: Y

Super\_priv: Y

Create\_tmp\_table\_priv: Y

Lock\_tables\_priv: Y

Execute\_priv: Y

Repl\_slave\_priv: Y

Repl\_client\_priv: Y

Create\_view\_priv: Y

Show\_view\_priv: Y

Create\_routine\_priv: Y

Alter\_routine\_priv: Y

Create\_user\_priv: Y

Event\_priv: Y

Trigger\_priv: Y

Create\_tablespace\_priv: Y

ssl\_type:

ssl\_cipher:

x509\_issuer:

x509\_subject:

max\_questions:0max\_updates:0max\_connections:0max\_user\_connections:0plugin:

authentication\_string:

password\_expired: N

is\_role: N

default\_role:

max\_statement\_time:0.000000

4 rows in set (0.00 sec)

[5deb5309b34505a642f5a6d90d42e172.png]: /images/20221024/17e4c1ec3f3742e8ad68d0a9c7dd6340.png
[791dd346bb999f0330a45a880f50a703.png]: /images/20221024/0c4b30cb6cf5447797e61feb0a3913c7.png
[884763b62ed1f8d37d5410104c91aa9e.png]: /images/20221024/72eb83a08e954f3cbae62eb39a711e5d.png
[b36d88a6e550b9f7cf0d49a30e04b239.png]: /images/20221024/071cfad7be8344dc9c5fdf4a531a2f23.png
[9f2689eb59aae1b88457285618cf1520.png]: /images/20221024/3ef27b5efada401caf9abe3e22044826.png
[158ed3d4e1edee00b3602bfbbf13e412.png]: /images/20221024/2f99a4bba9954dc2bda04f798a260599.png
[43bf59fbdf65c8e80cb5231568825a3c.png]: /images/20221024/7d0aed92ecf04c398593be18c1114ec2.png
[a313cce497e080c720f627cf00a8d9f9.png]: /images/20221024/8cf8b09ec16c442ab803599d6f249c9d.png
[a73b6b18806871840ef3e88f0b5c83df.png]: /images/20221024/11afd055cab44ecba8834bc34b39cfc8.png
[61750cc98cdd0f78b37633230ade81b0.png]: /images/20221024/50c2f64968f74f33ab572f198cb5ab78.png