Mariadb数据库：基本语句及操作数据库及用户管理

关注

原创最新推荐文章于 2026-03-08 22:15:21 发布 1k 阅读 AI 写同款· GEO 优化 ›

MariaDB数据库安装的配置、使用、授权、增删改查以及备份与恢复添加下面内容👇[mariadb]xx库的名称name=库baseurl=下载库的地址gpgkey=密钥gpgcheck=1开启校验。阅读详情

mariadb
1.安装
[root@localhost ~]# yum install mariadb-server.x86_64 -y ##安装服务
[root@localhost ~]# systemctl start mariadb ##开启服务
2.关闭数据库接口在网络开启的端口
[root@localhost ~]# netstat -antlpe | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 66001 4185/mysqld ##数据库在网络接口上开启了端口
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
skip-networking=1 ##添加这行语句不允许网络访问
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
[root@localhost ~]# systemctl restart mariadb.service
[root@localhost ~]# netstat -antlpe | grep mysql
这里写图片描述
3.初始化数据库
[root@localhost ~]# mysql_secure_installation
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

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] 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] y ##是否删除测试数据库
- Dropping test database…
… Success!
- Removing privileges on test database…
… Success!

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@localhost ~]# mysql
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO) ##不能直接登陆
[root@localhost ~]# mysql -uroot -p #需要追加上用户和登陆密码方可登陆
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.35-MariaDB MariaDB Server

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

MariaDB [(none)]>
这里写图片描述
4.数据库sql语句基本操作
登陆
[root@localhost ~]# mysql -uroot -p
Enter password:
查询
MariaDB [(none)]> show databases; ##显示数据库
MariaDB [(none)]> use mysql; ##进入mysql库
MariaDB [mysql]> show tables; ##显示当前库中所有表的名称
MariaDB [mysql]> select * from user; ##查询user表中所有内容（*可用此表中任何字段代替）
MariaDB [mysql]> desc user; ##查询user表的结构（显示所有字段的基本信息）
MariaDB [mysql]> select Host from user; ##查询user表中Host字段下的数据
+———–+
| Host |
+———–+
| 127.0.0.1 |
| ::1 |
| localhost |
+———–+
3 rows in set (0.00 sec)

MariaDB [westos]> insert into linux values (‘user2’,’234’); ##添加数据 user2，234
Query OK, 1 row affected (0.07 sec)

MariaDB [westos]> insert into linux values (‘user2’,password(‘234’)); ##插入password字段的数据234用password加密
Query OK, 1 row affected, 1 warning (0.07 sec)

MariaDB [westos]> insert into linux values (‘user3’,password(‘345’)); ##插入password字段的数据345用password加密
Query OK, 1 row affected, 1 warning (0.07 sec)

MariaDB [westos]> delete from linux where username=’user5’; ##删除uername为user5的数据
Query OK, 1 row affected (0.05 sec)

MariaDB [westos]> alter table linux add date varchar(20); ##向linux表中添加date字段，最大为20个字符

MariaDB [westos]> alter table linux add age varchar(20) after username; ##向linux表中，在username字段之后添加age字段，最大为20个字符

MariaDB [westos]> alter table linux drop date; ##删除linux表中的date字段

这里写图片描述

7.数据库备份
[root@localhost ~]# mysqldump -u root -p –all-database ##备份所有表中的所有数据
Enter password:
这里写图片描述
[root@localhost ~]# mysqldump -u root -predhat –all-database –no-data ##备份所有的表，但不备份数据

[root@localhost ~]# mysqldump -u root -predhat westos ##备份westos库

[root@localhost ~]# mysqldump -u root -predhat westos > /mnt/westos.sql ##备份westos库并把数据保存到westos.sql
这里写图片描述
[root@localhost ~]# mysqldump -u root -predhat westos linux > /mnt/linux.sql ##备份westos库中的linux表并把数据保存到linux.sql

[root@localhost ~]# mysql -uroot -predhat -e “drop database westos;” -e “show databases;” ##删除westos库并显示目前所有数据库名称

[root@localhost ~]# mysql -uroot -predhat -e “create database westos;” -e “show databases” ##新建westos库并显示数据库

[root@localhost ~]# mysql -uroot -predhat westos < /mnt/westos.sql ##把数据导入westos库恢复westos库
这里写图片描述
8.用户授权
MariaDB [(none)]> create user leo@localhost identified by ‘leo’; ##创建leo用户，@localhost表示此用户只能通过本机登陆
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> create user leo@’%’ identified by ‘leo’; ##创建leo用户，@’%’表示此用户可以通过登陆网络

MariaDB [mysql]> show grants for leo@localhost; ##显示leo用户权限

MariaDB [mysql]> grant insert,update on westos.linux to leo@localhost; ##增加leo@localhost用户对westos库中linux表的insert和update权限
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> revoke update on westos.linux from leo@localhost; ##去除leo@localhost用户对westos库中linux表的update权限
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> show grants for leo@localhost; ##显示leo权限

MariaDB [mysql]> drop user leo@localhost; ##删除leo@localhost用户
Query OK, 0 rows affected (0.00 sec)

这里写图片描述
9.密码修改
[root@localhost ~]# mysqladmin -uroot -predhat password westos ##修改用户密码

当root密码忘记时

[root@localhost ~]# systemctl stop mariadb.service ##关闭服务
[root@localhost ~]# mysqld_safe –skip-grant-tables & ##开启MYSQL登录接口
[root@localhost ~]# mysql ##登录

MariaDB [(none)]> UPDATE mysql.user SET Password=PASSWORD(‘redhat’) WHERE User=’root’; ##设置密码
MariaDB [(none)]> quit
[root@localhost ~]# kill -9 2208 ##关闭进程
[root@localhost ~]# systemctl start mariadb ##开启服务
这里写图片描述

10.网页mysql
[root@localhost ~]# yum install httpd -y ##安装httpd服务
[root@localhost ~]# systemctl start httpd ##开启服务
[root@localhost ~]# systemctl stop firewalld.service ##关闭防火墙
[root@localhost ~]# systemctl enable httpd ##开机自启
[root@localhost ~]# systemctl disable firewalld.service ##开机不启动
[root@localhost ~]# yum install php.x86_64 -y ##安装php
[root@localhost ~]# yum install php-mysql.x86_64 ##安装php对mysql的兼容服务
lftp 172.25.254.250:/pub/docs/software> get phpMyAdmin-3.4.0-all-languages.tar.bz2
4548030 bytes transferred ##下载网页编译包
[root@localhost ~]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html/ ##解包
[root@localhost ~]# cd /var/www/html/
[root@localhost html]# mv phpMyAdmin-3.4.0-all-languages/ mysqladmin ##更改名称
[root@localhost html]# cd mysqladmin/
[root@localhost mysqladmin]# cp -p config.sample.inc.php config.inc.php
[root@localhost mysqladmin]# vim config.inc.php ##配置文件
17 $cfg[‘blowfish_secret’] = ‘mysql’; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */ ##添加
[root@localhost mysqladmin]# systemctl restart httpd.service ##重启服务