
[mariadb]
name=mariadb
baseurl=https://mirrors.ustc.edu.cn/mariadb/yum/10.10/centos7-amd64
gpgkey=https://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB 
gpgcheck=1

[mariadb]                              （某个库的名称）

name= （库）

baseurl=                                 (下载库的具体地址)

gpgkey=                               （密钥）

gpgcheck=1                           (开启效验)

1.2 清空缓存

yum clean all

yum makecache

1.3 安装MariaDB

yum -y install mariadb-server mariadb-client

2 MariaDB的基本配置

2.1 启动MariaDB

systemctl start mariadb

设置开机自启

systemctl enable mariadb

2.2 MariaDB进程查看

先安装好net工具

yum -y install net-tools

netstat -ntlp | grep 3306

2.3 MariaDB数据库初始化

为了确保数据库的安全性和正常运转，需要先对数据库程序进行初始化操作。这个初始化操作涉及下面5个步骤。

设置root管理员在数据库中的密码值(注意，该密码并非root管理员在系统中的密码，这里的密码值默认应该为空，可直接按回车键)。
设置root管理员在数据库中的专有密码。
随后删除匿名账户，并使用root管理员从远程登录数据库，以确保数据库上运行的业务的安全性。
删除默认的测试数据库，取消测试数据库的一系列访问权限。
刷新授权列表，让初始化的设定立即生效。

2.3.1 数据库初始化

mysql_secure_installation

Enter current password for root (enter for none):          初次运行直接回车

设置密码

Switch to unix_socket authentication [Y/n]                     输入y并回车

Change the root password? [Y/n]                                   输入y并回车

New password:                                                            设置root用户密码

Re-enter new password:                                               再次输入密码

Remove anonymous users? [Y/n]                               是否删除匿名用户，输入y回车

Disallow root login remotely? [Y/n]                             是否禁止root远程登录，这里我们输入n

Remove test database and access to it? [Y/n]                是否删除test数据库，输入y回车

Reload privilege tables now? [Y/n]                                 是否重新加载表权限，输入y回车

详细版如下：


[root@localhost ~]# 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
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] 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!

2.3.2 初始化测试登录

mysql -uroot -p'刚才设置的密码'

3 MariaDB的使用

【和mysql语句完全一样的】

3.1 查看数据库

show databases;

3.2 修改密码

set password = PASSWORD(‘新密码’);

3.3 创建数据库test

create database test;

3.4 进入数据库

use test;

3.5 创建mortal数据表

create table mortal(id int,name char(32));

3.6 查看数据表

show tables;

3.7 查看表结构

desc mortal;

3.8 退出

\q 或者 quit 或者exit

\q

quit

exit

4 MariaDB增删改查

4.1 增

给mortal表增加两条数据

insert into mortal(id,name) values(1,"zero"),(2,"one");

4.2 查

查看id，name字段mortal表的数据

select id,name from mortal;

4.3 删

删除mortal表中id=2的数据

delete from mortal where id=2;

查看一下是否删除成功

select id,name from mortal;

4.4 改

更改id=1的字段name=yi

update mortal set name="yi" where id=1;

查看一下是否修改成功

select * from mortal;

5 授权

grant 权限 on 数据库.表名 to 账户@主机名        对特定数据库的特定表授权

grant 权限 on 数据库.* to 账户@主机名                对特定数据库的所有表给予授权

grant 权限一,权限2,权限3 on *.* to 账户@主机名对所有数据库中的所有表给予多个权限

grant all privileges on *.* to 账户@主机名           对所有数据库和表授权所有权限

（1）创建用户zero和密码zero

create user zero@’localhost’ identified by ‘zero’;

（2）授予用户所有的权限

grant all privileges on *.* to username@’localhost’ identified by ‘password’;

（3）授予zero用户创建test数据库的权限

grant create on test.* to zero@’localhost’ identified by ‘zero’;

（4）查询zero用户的数据库

mysql -uzero -pzero

（5）授予one创建的权限，对于所有的库表生效

grant create on *.* to one@’localhost’ identified by ‘one’;

（6）删除one用户

drop user one;

（7）刷新权限

flush privileges;

6 数据库备份与恢复

6.1 数据库备份

6.1.1 准备

删除/tmp目录下所有内容

cd /tmp/

rm -rf *

6.1.2 备份所有数据库

mysqldump -uroot -p --all-databases > /tmp/db.dump

出现Enter password: 时输入数据库密码回车即可。

6.1.3 单独备份某个库

比如说我们备份test库。

mysqldump -uroot -p test > /tmp/test.sql

6.2 数据库恢复

6.2.1 准备

（1）删除数据库test

进入数据库中使用下面命令删除test数据库并退出。

drop database test;

（2）查看是否删除

show databases;

没有test数据库，删除成功，接下来开始恢复数据库。

6.2.2 恢复数据库

mysql -uroot -p < /tmp/db.dump

出现Enter password: 时输入数据库密码回车即可。

6.2.3 查看

show databases;

test数据库恢复成功！

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/码创造者/article/detail/772463