MySQL数据库学习

最新推荐文章于 2022-07-26 15:48:11 发布

纵横派

最新推荐文章于 2022-07-26 15:48:11 发布

阅读量3k

点赞数

文章标签：数据库 mysql database

本文链接：https://blog.csdn.net/qq_36542576/article/details/121094459

版权

desc 表名查询表的所有字段

MariaDB [mysql]> desc user
    -> ;
+------------------------+---------------------+------+-----+----------+-------+
| Field                  | Type                | Null | Key | Default  | Extra |
+------------------------+---------------------+------+-----+----------+-------+
| Host                   | char(255)           | NO   |     |          |       |
| User                   | char(128)           | NO   |     |          |       |
| Password               | longtext            | YES  |     | NULL     |       |
| Select_priv            | varchar(1)          | YES  |     | NULL     |       |
| Insert_priv            | varchar(1)          | YES  |     | NULL     |       |
| Update_priv            | varchar(1)          | YES  |     | NULL     |       |
| Delete_priv            | varchar(1)          | YES  |     | NULL     |       |
| Create_priv            | varchar(1)          | YES  |     | NULL     |       |
| Drop_priv              | varchar(1)          | YES  |     | NULL     |       |
| Reload_priv            | varchar(1)          | YES  |     | NULL     |       |
| Shutdown_priv          | varchar(1)          | YES  |     | NULL     |       |
| Process_priv           | varchar(1)          | YES  |     | NULL     |       |
| File_priv              | varchar(1)          | YES  |     | NULL     |       |
| Grant_priv             | varchar(1)          | YES  |     | NULL     |       |
| References_priv        | varchar(1)          | YES  |     | NULL     |       |
| Index_priv             | varchar(1)          | YES  |     | NULL     |       |
| Alter_priv             | varchar(1)          | YES  |     | NULL     |       |
| Show_db_priv           | varchar(1)          | YES  |     | NULL     |       |
| Super_priv             | varchar(1)          | YES  |     | NULL     |       |
| Create_tmp_table_priv  | varchar(1)          | YES  |     | NULL     |       |
| Lock_tables_priv       | varchar(1)          | YES  |     | NULL     |       |
| Execute_priv           | varchar(1)          | YES  |     | NULL     |       |
| Repl_slave_priv        | varchar(1)          | YES  |     | NULL     |       |
| Repl_client_priv       | varchar(1)          | YES  |     | NULL     |       |
| Create_view_priv       | varchar(1)          | YES  |     | NULL     |       |
| Show_view_priv         | varchar(1)          | YES  |     | NULL     |       |
| Create_routine_priv    | varchar(1)          | YES  |     | NULL     |       |
| Alter_routine_priv     | varchar(1)          | YES  |     | NULL     |       |
| Create_user_priv       | varchar(1)          | YES  |     | NULL     |       |
| Event_priv             | varchar(1)          | YES  |     | NULL     |       |
| Trigger_priv           | varchar(1)          | YES  |     | NULL     |       |
| Create_tablespace_priv | varchar(1)          | YES  |     | NULL     |       |
| Delete_history_priv    | varchar(1)          | YES  |     | NULL     |       |
| ssl_type               | varchar(9)          | YES  |     | NULL     |       |
| ssl_cipher             | longtext            | NO   |     |          |       |
| x509_issuer            | longtext            | NO   |     |          |       |
| x509_subject           | longtext            | NO   |     |          |       |
| max_questions          | bigint(20) unsigned | NO   |     | 0        |       |
| max_updates            | bigint(20) unsigned | NO   |     | 0        |       |
| max_connections        | bigint(20) unsigned | NO   |     | 0        |       |
| max_user_connections   | bigint(21)          | NO   |     | 0        |       |
| plugin                 | longtext            | NO   |     |          |       |
| authentication_string  | longtext            | NO   |     |          |       |
| password_expired       | varchar(1)          | NO   |     |          |       |
| is_role                | varchar(1)          | YES  |     | NULL     |       |
| default_role           | longtext            | NO   |     |          |       |
| max_statement_time     | decimal(12,6)       | NO   |     | 0.000000 |       |
+------------------------+---------------------+------+-----+----------+-------+
47 rows in set (0.006 sec)

status 查看当前登录状态

MariaDB [mysql]> status
--------------
mysql  Ver 15.1 Distrib 10.6.4-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:		5
Current database:	mysql
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.6.4-MariaDB MariaDB Server
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8mb3
Conn.  characterset:	utf8mb3
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			36 min 28 sec

Threads: 1  Questions: 46  Slow queries: 0  Opens: 37  Open tables: 30  Queries per second avg: 0.021
--------------

匿名用户登录（匿名用户部分权限受限）

MariaDB [(none)]> select user,host from mysql.user;
+-------------+---------------+
| User        | Host          |
+-------------+---------------+
|             | centos7.linux |     #此处为匿名用户
|             | localhost     |     #此处为匿名用户  ，可以登录时 mysql -u随意输入任何字符
| mariadb.sys | localhost     |
| mysql       | localhost     |
| root        | localhost     |
+-------------+---------------+
5 rows in set (0.003 sec)

查看mysql命令历史记录

[root@centos7 ~]$ cat .mysql_history  #用户家目录中的隐藏文件
status
use mysql
desc user
;
status
show databases;
status
select user,host from mysql.user;

help帮助命令 (很多可以使用简写命令status=\s)

MariaDB [(none)]> help

General information about MariaDB can be found at
http://mariadb.org

List of all client 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 MariaDB server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to MariaDB 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.

For server side help, type 'help contents'

安全加固初始化脚本

[root@centos7 mysql]$ mariadb-secure-installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] y
Enabled successfully!
Reloading privilege tables..
 ... Success!


You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB 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? [Y/n] 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? [Y/n] n
 ... skipping.

By default, MariaDB 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? [Y/n] n
 ... skipping.

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

版本不同，个别版本命令不一样，询问的修改项也略有区别

[root@centos8 ~]# mysql_secure_installation 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB 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? [Y/n] 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? [Y/n] n  
 ... skipping.

By default, MariaDB 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? [Y/n] n  
 ... skipping.

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

127.0.0.1和localhost区别

MariaDB [(none)]> select user,host,password from mysql.user;
+------+---------------+-------------------------------------------+
| user | host          | password                                  |
+------+---------------+-------------------------------------------+
| root | localhost     | *AC241830FFDDC8943AB31CBD47D758E79F7953EA |
| root | centos8.linux | *AC241830FFDDC8943AB31CBD47D758E79F7953EA |
| root | 127.0.0.1     | *AC241830FFDDC8943AB31CBD47D758E79F7953EA |
| root | ::1           | *AC241830FFDDC8943AB31CBD47D758E79F7953EA |
+------+---------------+-------------------------------------------+

127.0.0.1是通过访问数据库程序的端口进入，比如与3306进行通信访问；

localhost是通过本地套接字文件访问进入，默认是/var/lib/mysql/mysql.sock

[root@centos7 mysql]$ ls /var/lib/mysql/
aria_log.00000001  centos7.pid       ib_buffer_pool  ib_logfile0  multi-master.info  mysql.sock          sys
aria_log_control   ddl_recovery.log  ibdata1         ibtmp1       mysql              performance_schema  test

MariaDB [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 10.6.4-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:		18
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.6.4-MariaDB MariaDB Server
Protocol version:	10
Connection:		Localhost via UNIX socket  #此处可以看出
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8mb3
Conn.  characterset:	utf8mb3
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			2 hours 30 sec

使用127.0.0.1登录看一下

[root@centos7 mysql]$ mysql -uroot -p123.com -h127.0.0.1 #此处
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.6.4-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 10.6.4-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:		19
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.6.4-MariaDB MariaDB Server
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP  #此处看出是通过网络协议访问
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8mb3
Conn.  characterset:	utf8mb3
TCP port:		3306
Uptime:			2 hours 6 min 8 sec

Threads: 1  Questions: 80  Slow queries: 0  Opens: 37  Open tables: 30  Queries per second avg: 0.010
--------------

客户端命令与服务器命令

客户端命令的结尾可以不加";" help显示的命令都是客户端命令

服务端命令的结尾必须加";"

system命令可以在数据库命令行中调用系统命令

MariaDB [(none)]> system ls
aria_log.00000001  centos7.pid	     ib_buffer_pool  ib_logfile0  multi-master.info  mysql.sock		 sys
aria_log_control   ddl_recovery.log  ibdata1	     ibtmp1	  mysql		     performance_schema  test
MariaDB [(none)]> system cd /
MariaDB [(none)]> system ls
aria_log.00000001  centos7.pid	     ib_buffer_pool  ib_logfile0  multi-master.info  mysql.sock		 sys
aria_log_control   ddl_recovery.log  ibdata1	     ibtmp1	  mysql		     performance_schema  test
MariaDB [(none)]> system pwd
/var/lib/mysql

脚本方式运行sql

source命令可以执行sql脚本

创建脚本文件 vim test.sql,如下内容

use mysql
select user,host from user;
~

MariaDB [(none)]> source test.sql 
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
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+
3 rows in set (0.001 sec)

登录mysql使用source命令读取脚本文件，sql脚本文件不在当前路径时需要知道绝对路径;

使用重定向非交互式执行 sql脚本文件 (不显示表格边框，不容易看清）

[root@centos7 mysql]$ mysql -uroot -p123.com < ./test.sql
User	Host
mariadb.sys	localhost
mysql	localhost
root	localhost
[root@centos7 mysql]$ cat ./test.sql |mysql -uroot -p123.com 
User	Host
mariadb.sys	localhost
mysql	localhost
root	localhost

使用mysql -e选项(不登录调用mysql命令执行）


[root@centos7 mysql]$ mysql -uroot -p"123.com" -e "source test.sql"
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+

--print-defaults 选项

显示mysql登录时的默认配置

--prompt=format_str

修改当前客户端mysql登录提示符类似linux终端的PS1变量

临时修改，临时生效

shell> mysql --prompt="(\u@\h) [\d]> "
(user@host) [database]>

配置文件中修改永久生效

[mysql]
prompt=(\\u@\\h) [\\d]>\\_

各提示符参数对应的意思

┌───────┬──────────────────────────────────────┐
       │Option │ Description                          │
       ├───────┼──────────────────────────────────────┤
       │\c     │ A counter that increments for each   │
       │       │ statement you issue                  │
       ├───────┼──────────────────────────────────────┤
       │\D     │ The full current date                │
       ├───────┼──────────────────────────────────────┤
       │\d     │ The default database                 │
       ├───────┼──────────────────────────────────────┤
       │\h     │ The server host                      │
       ├───────┼──────────────────────────────────────┤
       │\l     │ The current delimiter (new in        │
       │       │ 5.1.12)                              │
       ├───────┼──────────────────────────────────────┤
       │\m     │ Minutes of the current time          │
       ├───────┼──────────────────────────────────────┤
       │\n     │ A newline character                  │
       ├───────┼──────────────────────────────────────┤
       │\O     │ The current month in three-letter    │
       │       │ format (Jan, Feb, ...)               │
       ├───────┼──────────────────────────────────────┤
       │\o     │ The current month in numeric format  │
       ├───────┼──────────────────────────────────────┤
       │\P     │ am/pm                                │
       ├───────┼──────────────────────────────────────┤
       │\p     │ The current TCP/IP port or socket    │
       │       │ file                                 │
       ├───────┼──────────────────────────────────────┤
       │\R     │ The current time, in 24-hour         │
       │       │ military time (0–23)                 │
       ├───────┼──────────────────────────────────────┤
       │\r     │ The current time, standard 12-hour   │
       │       │ time (1–12)                          │
       ├───────┼──────────────────────────────────────┤
       │\S     │ Semicolon                            │
       ├───────┼──────────────────────────────────────┤
       │\s     │ Seconds of the current time          │
       ├───────┼──────────────────────────────────────┤
       │\t     │ A tab character                      │
       ├───────┼──────────────────────────────────────┤
       │\U     │                                      │
       │       │        Your full user_name@host_name │
       │       │        account name                  │
       ├───────┼──────────────────────────────────────┤
       │\u     │ Your user name                       │
       ├───────┼──────────────────────────────────────┤
       │\v     │ The server version                   │
       ├───────┼──────────────────────────────────────┤
       │\w     │ The current day of the week in       │
       │       │ three-letter format (Mon, Tue, ...)  │
       ├───────┼──────────────────────────────────────┤
       │\Y     │ The current year, four digits        │
       ├───────┼──────────────────────────────────────┤
       │\y     │ The current year, two digits         │
       ├───────┼──────────────────────────────────────┤
       │\_     │ A space                              │
       ├───────┼──────────────────────────────────────┤
       │\      │ A space (a space follows the         │
       │       │ backslash)                           │
       ├───────┼──────────────────────────────────────┤
       │\´     │ Single quote                         │
       ├───────┼──────────────────────────────────────┤
       │\"     │ Double quote                         │
       ├───────┼──────────────────────────────────────┤
       │\\     │ A literal “\” backslash character    │
       ├───────┼──────────────────────────────────────┤
       │\x     │                                      │
       │       │        x, for any “x” not listed     │
       │       │        above                         │
       └───────┴──────────────────────────────────────┘

查看版本号大写V

[root@centos7 ~]$ mysql -V
mysql  Ver 15.1 Distrib 10.6.4-MariaDB, for Linux (x86_64) using readline 5.1

客户端程序命令

mysql: 交互式的CLI工具

mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成 insert等写操作语句保存文本文件中

mysqladmin：基于mysql协议管理

mysqld mysqlimport：数据导入工具 MyISAM存储引擎的管理工具：

myisamchk：检查MyISAM库

myisampack：打包MyISAM表，只读

sock文件是服务启动后自动生成的，关闭服务后消失

二进制包安装

官网下载二进制包，就是已经编译过的安装包，特征是文件名带有操作系统名称

准备mysql用户和mysql组

准备数据目录，推荐使用逻辑卷并挂载，比如创建/data/mysql并挂载逻辑卷，并将此目录属主属组递归修改成mysql用户和组

mkdir -p /data/mysql  #创建数据存放目录
group -r -g306 mysql    #创建组
useradd -r -g 306 -u 306 -d /data/mysql mysql   #创建用户
chown -R mysql.mysql /data/mysql    
vim /etc/fstab    #将新创建的逻辑卷配置开机挂载 r!blkid /dev/vg0/mysql 读取 UUID
mv mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz /usr/local/ #将包移动到安装目录
tar xvf mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz 解压缩
[root@centos7 local]$ du -sh mysql-5.6.51-linux-glibc2.12-x86_64
1.2G	mysql-5.6.51-linux-glibc2.12-x86_64   #解压缩后的大小
ln -s mysql-5.6.51-linux-glibc2.12-x86_64 mysql #创建软链接
chown -R root.root mysql/ #递归修改目录内属组属主为root
[root@centos7 mysql]$ ls support-files/
binary-configure  magic  my-default.cnf  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@centos7 mysql]$ ls scripts/
mysql_install_db     #support-files和scripts目录一会使用
[root@centos7 support-files]$ cp my-default.cnf /etc/my.cnf 
vim /etc/my.cnf #编辑配置文件，可以用模板修改后使用，
#至少填写以下内容
[mysqld]
datadir = /data/mysql
innodb_file_per_table = on #在mariadb5.5以上版的是默认值，可不加
skip_name_resolve = on    #禁止主机名解析，建议使用
保存退出
创建数据库文件
[root@centos7 mysql]$ ./scripts/mysql_install_db --datadir=/data/mysql --user=mysql
#执行mysql_install_db脚本   注意必须在mysql目录下执行，所有找不到bin目录下得文件报错
[root@centos7 mysql]$ ls /data/mysql/ -l   #查看是否生成数据
total 110620
-rw-rw----. 1 mysql mysql 12582912 Nov  5 15:31 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Nov  5 15:31 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Nov  5 15:31 ib_logfile1
drwx------. 2 root  root     16384 Nov  5 14:04 lost+found
drwx------. 2 mysql mysql     4096 Nov  5 15:31 mysql
drwx------. 2 mysql mysql     4096 Nov  5 15:31 performance_schema
drwx------. 2 mysql mysql     4096 Nov  5 15:31 test
[root@centos7 mysql]$ cp ./support-files/mysql.server /etc/init.d/mysqld  # 复制启动脚本文件并修改名字
[root@centos7 mysql]$ chkconfig --add mysqld #添加服务启动项，centos8上不能用此操作（#service mysqld start centos8用这个方式可以先启动）
[root@c[root@centos7 mysql]$ systemctl start mysqld #启动服务
[root@centos7 mysql]$ vim /etc/rc.d/rc.local  #添加/etc/init.d/mysqld start 开机启动

解压二进制包至编译指定的目录，一般是/usr/local目录，将解压后目录软链接成/usr/local/mysql

配置开机启动，高版本的压缩包里自带service文件，和一些配置文件，放到标准目录下就行，

手动创建开机启动服务

纵横派

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库学习

desc 表名查询表的所有字段MariaDB [mysql]> desc user -> ;+------------------------+---------------------+------+-----+----------+-------+| Field | Type | Null | Key | Default | Extra |+------------------------+------.
复制链接

扫一扫