【Centos7笔记十一】mariaDB数据库的安装及初级使用，centos7mariadb

【Centos7笔记十一】mariaDB数据库的安装及初级使用，centos7mariadb

一、安装及初始化
1.install
[root@linuxprobe ~]# yum install mariadb mariadb-server
[root@linuxprobe ~]# systemctl start mariadb 
[root@linuxprobe ~]# systemctl enable mariadb

2.初始化mariadb
[root@linuxprobe ~]# mysql_secure_installation   #开启mariadb的初始化
/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: 输入要为root用户设置的数据库密码。
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(删除test数据库并取消对其的访问权限)
 - 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!

3.设置允许远程主机对本机mysql服务的访问
[root@linuxprobe ~]# firewall-cmd --permanent --add-service=mysql
success
[root@linuxprobe ~]# firewall-cmd --reload
success

二、账户权限操作
1.创建用户： CREATE USER 用户名@主机名 IDENTIFIED BY '密码';
MariaDB [(none)]> create user luke@localhost IDENTIFIED BY 'linuxprobe'; #当前luke仅仅是普通帐户而已
Query OK, 0 rows affected (0.00 sec)

2.查看该用户的信息：
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 host,user,password from user where user="luke";
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | luke | *55D9962586BE75F4B7D421E6655973DB07D6869F |
+-----------+------+-------------------------------------------+

3.给用户进行授权使用的grant命令
GRANT授权命令的常见格式如下表：

命令	                                                    作用
GRANT 权限 ON 数据库.表单名称 TO 用户名@主机名	  对某个特定数据库中的特定表单给予授权。
GRANT 权限 ON 数据库.* TO 用户名@主机名	          对某个特定数据库中的所有表单给予授权。
GRANT 权限 ON *.* TO 用户名@主机名	              对所有数据库及所有表单给予授权。
GRANT 权限1,权限2 ON 数据库.* TO 用户名@主机名	  对某个数据库中的所有表单给予多个授权。
GRANT ALL PRIVILEGES ON *.* TO 用户名@主机名	  对所有数据库及所有表单给予全部授权，（谨慎操作）。

root用户登录后可给普通用户授权：
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]> GRANT SELECT,UPDATE,DELETE,INSERT on mysql.user to luke@localhost;
Query OK, 0 rows affected (0.00 sec)

4.查看用户权限
MariaDB [(none)]> show grants for luke@localhost;
+-------------------------------------------------------------------------------------------------------------+
| Grants for luke@localhost |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'luke'@'localhost' IDENTIFIED BY PASSWORD '*55D9962586BE75F4B7D421E6655973DB07D6869F' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`user` TO 'luke'@'localhost' |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

5.撤销权限：
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 [(none)]> revoke SELECT,UPDATE,DELETE,INSERT on mysql.user from luke@localhost; 
Query OK, 0 rows affected (0.00 sec)

三、数据库常用操作
CREATE database 数据库名称。	创建新的数据库。
DESCRIBE 表单名称;	            描述表单。
USE 数据库名称;	                指定使用的数据库。
SHOW databases;	                显示当前已有的数据库。
SHOW tables;	                显示当前数据库中的表单。
SELECT * FROM 表单名称;			从表单中选中某个记录值。
DELETE FROM 表单名 WHERE attribute=值;		从表单中删除某个记录值。
UPDATE 表单名称 SET attribute=新值 WHERE attribute > 原始值;		更新表单中的数据。

四、数据库的备份
mysqldump命令用于备份数据库数据，格式为：“mysqldump [参数] [数据库名称]，其中参数与mysql命令大致相同，-u参数用于定义登陆数据库的用户名称，而-p参数代表密码提示符。
例如接下来把linuxprobe数据库内容导出成一个文件保存到root管理员用户的家目录中：

[root@linuxprobe ~]# mysqldump -u root -p linuxprobe > /root/linuxprobeDB.dump
Enter password: 

使用输入重定向符把刚刚备份的数据库文件导入到mysql命令中，命令执行成功后再登陆到MariaDB数据库中就会发现又能看到数据库以及表单了，恢复数据库内容成功！~

[root@linuxprobe ~]# mysql -u root -p linuxprobe < /root/linuxprobeDB.dump 
Enter password: 此处输入root管理员帐户在数据库中的密码值