基础操作
查看服务和端口
[root@CentOS8 ~]# more /etc/services | grep 3306
mysql 3306/tcp # MySQL
mysql 3306/udp # MySQL
mysqlx 33060/tcp # MySQL Database Extended Interface
#服务
[root@CentOS8 ~]# netstat -nat | grep 3306
tcp6 0 0 :::3306 :::* LISTEN
#显示端口信息
[root@CentOS8 ~]# ss -antp | column -t | grep mysql
LISTEN 0 80 *:3306 *:* users:(("mysqld",pid=63473,fd=22))
#显示端口信息更详细点初始化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 #设置root密码
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] n #移除匿名用户
... skipping.
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] y #禁用root远程登录
... Success!
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] n #重新载入特殊表格
... skipping.
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 -u root -h localhost -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.27-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)]> exit
Bye重新设置root密码
[root@CentOS8 ~]# mysqladmin -u root -h localhost -p'123456' password '654321'
#注意‘-p’后面不要加‘空格’ ↑用户名 ↑主机名 ↑旧密码 ↑新密码常用命令
数据库部分
查询数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)打开数据库
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]> create database customer;
Query OK, 1 row affected (0.001 sec)删除数据库
MariaDB [mysql]> drop database customer;
Query OK, 0 rows affected (0.002 sec)数据表部分
显示数据表的结构
MariaDB [mysql]> describe host;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
20 rows in set (0.003 sec)显示数据表的记录
MariaDB [mysql]> select Host,User,Password from user;
+-----------+------+-------------------------------------------+
| Host | User | Password |
+-----------+------+-------------------------------------------+
| localhost | root | *2A032F7C5BA932872F0F045E0CF6B53CF702F2C5 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
3 rows in set (0.001 sec)查询数据库内的数据表
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| transaction_registry |
| user |
+---------------------------+
31 rows in set (0.001 sec)创建数据表
MariaDB [students]> create table member(name varchar(9),sex char(2),class int (10), tuition double(9,2));
Query OK, 0 rows affected (0.009 sec)删除数据表
MariaDB [students]> drop table member;
Query OK, 0 rows affected (0.008 sec)向数据表添加记录
MariaDB [students]> insert into member values('zhangsan','na',5,8000);
Query OK, 1 row affected (0.004 sec)删除数据表中记录
MariaDB [students]> delete from member where name='zhangsan';
Query OK, 1 row affected (0.003 sec)
#删除名为张三的那一行记录
MariaDB [students]> delete from member;
Query OK, 3 rows affected (0.002 sec)
#删除全部记录修改数据表中记录
MariaDB [students]> update member set sex='nv' where name='zhangsan';
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [students]> select * from member;
+----------+------+-------+---------+
| name | sex | class | tuition |
+----------+------+-------+---------+
| lisi | nv | 5 | 8000.00 |
| wangwu | na | 8 | 4000.00 |
| gangdan | na | 6 | 3000.00 |
| zhangsan | na | 5 | 8000.00 |
+----------+------+-------+---------+
4 rows in set (0.001 sec)数据库的备份与恢复
[root@CentOS8 ~]# mysqldump -u root -h localhost -p students > students.bak
Enter password:
#备份
[root@CentOS8 ~]# mysqldump -u root -h localhost -p students < students.bak
Enter password:
-- MySQL dump 10.18 Distrib 10.3.27-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: students
-- ------------------------------------------------------
-- Server version 10.3.27-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `member`
--
DROP TABLE IF EXISTS `member`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `member` (
`name` varchar(9) DEFAULT NULL,
`sex` char(2) DEFAULT NULL,
`class` int(10) DEFAULT NULL,
`tuition` double(9,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `member`
--
LOCK TABLES `member` WRITE;
/*!40000 ALTER TABLE `member` DISABLE KEYS */;
INSERT INTO `member` VALUES ('lisi','nv',5,8000.00),('wangwu','na',8,4000.00),('gangdan','na',6,3000.00),('zhangsan','nv',5,8000.00);
/*!40000 ALTER TABLE `member` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2022-02-20 15:20:42
#恢复用户的管理与权限的分配
创建
创建拥有对students数据库的全部权限并可以将权限赋予其他用户的用户teacher
MariaDB [(none)]> GRANT ALL ON students.* TO teacher@localhost IDENTIFIED by '114514' WITH GRANT OPTION;
Query OK, 0 rows affected (0.003 sec)创建拥有对students数据库的只读权限的用户jessica
MariaDB [(none)]> GRANT Select ON students.* TO jessica@localhost IDENTIFIED BY 'gugugu';
Query OK, 0 rows affected (0.001 sec)创建拥有对所有数据库查询、插入、修改、删除操作权限的用户lihua
MariaDB [students]> GRANT Select,Insert,Update,Delete ON *.* TO lihua@localhost IDENTIFIED BY 'cykablyat';
Query OK, 0 rows affected (0.001 sec)更改
更改teacher用户的密码
MariaDB [(none)]> GRANT USAGE ON *.* TO teacher@localhost IDENTIFIED BY '1919810';
Query OK, 0 rows affected (0.001 sec)更改jessica的权限为对students数据库里的member表内的name,sex,class有UPDATE操作权限
MariaDB [students]> GRANT Update (name,sex,class) ON students.member TO jessica@localhost;
Query OK, 0 rows affected (0.002 sec)查询
查看当前登录用户权限
MariaDB [(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*2A032F7C5BA932872F0F045E0CF6B53CF702F2C5' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)查看用户lihua的权限
MariaDB [(none)]> show grants for lihua@localhost;
+---------------------------------------------------------------------------------------------------------------------------------------+
| Grants for lihua@localhost |
+---------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `lihua`@`localhost` IDENTIFIED BY PASSWORD '*0F7FFBD2367E99DE472CD5870E0693DFD1D55CDD' |
+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)查询当前已存在的用户
MariaDB [mysql]> Select User,Host,Password FROM user;
+---------+-----------+-------------------------------------------+
| user | host | password |
+---------+-----------+-------------------------------------------+
| root | localhost | *2A032F7C5BA932872F0F045E0CF6B53CF702F2C5 |
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | ::1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| teacher | localhost | *3212DEDFC9900EAF17356B48E9E78C06AB60EC3B |
| jessica | localhost | *3EC16D534E4627251B366C75D4F699464A882C95 |
| lihua | localhost | *0F7FFBD2367E99DE472CD5870E0693DFD1D55CDD |
+---------+-----------+-------------------------------------------+
6 rows in set (0.001 sec)撤销
撤销用户lihua在所有数据库上的修改和删除权限
MariaDB [(none)]> REVOKE Update,Delete ON *.* FROM lihua@localhost;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show grants for lihua@localhost;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for lihua@localhost |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `lihua`@`localhost` IDENTIFIED BY PASSWORD '*0F7FFBD2367E99DE472CD5870E0693DFD1D55CDD' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)删除
删除用户lihua但保留权限
MariaDB [mysql]> delete from user where user='lihua' and host='localhost';
Query OK, 1 row affected (0.002 sec)彻底删除用户teacher
MariaDB [mysql]> drop user teacher@localhost;
Query OK, 0 rows affected (0.001 sec)注意事项
- 安装完成后第一次启动mysql不需要身份验证，只需输入mysql。
- 切换数据库的时候不需要加分号
- 更改用户权限后用户需要重新登录连接数据库才会拥有新分配的权限
- 正常情况下不会用到with grant option特性，数据库权限最好由管理员统一分配
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
