RedHat Interprise Linux 7下数据库-mariadb简单的管理

mofdan

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，所以在红帽企业7上的数据库支持为mariadb
实验环境为红帽企业7
服务端与客户端在同一主机上
firewalld disabled

firewalld disabled

[root@localhost Desktop]# yum search maria db #寻找  安装包由于系统自带镜像中含有mariadb的包，所以不在此处下载Loaded plugins: langpacks
rhel_dvd                                                 | 4.1 kB     00:00   
(1/2): rhel_dvd/group_gz                                   | 134 kB   00:00   
(2/2): rhel_dvd/primary_db                                 | 3.4 MB   00:00   
============================= N/S matched: mariadb =============================
mariadb-bench.x86_64 : MariaDB benchmark scripts and data
mariadb-devel.i686 : Files for development of MariaDB/MySQL applications
mariadb-devel.x86_64 : Files for development of MariaDB/MySQL applications
mariadb-libs.i686 : The shared libraries required for MariaDB/MySQL clients
mariadb-libs.x86_64 : The shared libraries required for MariaDB/MySQL clients
mariadb-server.x86_64 : The MariaDB server and related files
mariadb.x86_64 : A community developed branch of MySQL
mariadb-test.x86_64 : The test suite distributed with MariaD
[iyunv@localhost Desktop]# yum install mariadb-server.x86_64 mariadb.x86_64 -y #安装
[iyunv@localhost Desktop]# systemctl start mariadb  #启动
[iyunv@localhost Desktop]# systemctl enable mariadb #开机启动
ln -s '/usr/lib/systemd/system/mariadb.service' '/etc/systemd/system/multi-user.target.wants/mariadb.service'

[iyunv@localhost Desktop]# vim /etc/my.cnf #编辑配置文件，以使数据库的安全，在大型网络中，一般情况下是不需要数据库主机访问网络的
【skip-networking=1】#修改项
[iyunv@localhost Desktop]# systemctl restart mariadb.service  #修改后重启服务以读取修改项生效

[iyunv@localhost Desktop]# netstat -antipe | grep mysql #网络端口的查看
[iyunv@localhost Desktop]# mysql   #在未进行设定之前，用该命令可以直接登录
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[iyunv@localhost Desktop]# mysql_secure_installation   #安全设定mysql
/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]   ##为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] 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  #是否允许超级用户远程访问

... 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!
[iyunv@localhost Desktop]# mysql   #安全设定后登陆被拒绝
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[iyunv@localhost Desktop]# mysql -p123456   #带上用户和密码登陆（该处省略了-uroot）
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
用户的管理

MariaDB [(none)]> create user kiosk@localhost identified by 'kiosk';   #创建本地用户，引号内为登录密码
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for kiosk@localhost;   ##查看该用户的权限

+--------------------------------------------------------------------------------------------------------------+
| Grants for kiosk@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kiosk'@'localhost' IDENTIFIED BY PASSWORD '*510FFA9B8FDBF4D30706C4CB10100A9500F4F301' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> drop user kiosk@localhost;   #删除用户
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> create user kiosk@'%' identified by 'kiosk';  #%代表该用户既是本地也是远程用户，远程登陆必须注释【skip-networking=1】 重新启动mariadb服务 关闭火墙
[iyunv@server1 Desktop]# mysql -ukiosk -pkiosk -h 192.169.1.1  ##使用另外一台网类主机远程登陆，登录-h制定数据库的所在主机ip地址
用户权限


MariaDB [(none)]> grant insert,update,delete,select on mail.* to kiosk@localhost; #给用户添加权限update,delete在mail目录下

MariaDB [(none)]> show grants for kiosk@localhost;  #查看该用户的权限
+-------------------------------------------------------------------------+
| Grants for kiosk@localhost                                              |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kiosk'@'localhost'                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mail`.* TO 'kiosk'@'localhost' |
+-------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> revoke delete on mail.* from kiosk@localhost;  ##删除权限
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for kiosk@localhost;
+-----------------------------------------------------------------+
| Grants for kiosk@localhost                                      |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kiosk'@'localhost'                       |
| GRANT SELECT, INSERT, UPDATE ON `mail`.* TO 'kiosk'@'localhost' |
+-----------------------------------------------------------------+
MariaDB [(none)]> drop user kiosk@'%';   #删除用户
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> flush privileges; #刷新或者退出数据库重新启动，

Query OK, 0 rows affected (0.00 sec)
备份和恢复 注：sql语言再此不做详细介绍

MariaDB [mail]> desc test;#此处的test表为已经创建好的
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES  |     | NULL    |       |
| passwd   | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [mail]> insert into test values('user1','passwd1'); #表内添加内容
Query OK, 1 row affected (0.09 sec)

MariaDB [mail]> select * from test; #查看表内所有内容
+----------+---------+
| username | passwd  |
+----------+---------+
| user1    | passwd1 |
+----------+---------+
1 row in set (0.01 sec)
[iyunv@localhost Desktop]# mysqldump -uroot -pwestos mail > tramisu.sql #备份，将mail备份到本地的tramisu.sql文件中，文件名自己设定
MariaDB [(none)]> drop database mail;     ##模仿mail丢失
Query OK, 1 row affected (0.35 sec)
MariaDB [(none)]> show databases; #进入数据库查看mail已经不存在
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> create database test;   ##重新创建test库以接受数据的恢复
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> quit
Bye
[iyunv@localhost Desktop]# mysql -uroot -pkiosk test < tramisu.sql   ##恢复数据
MariaDB [(none)]> use test;
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 [test]> select * from test;   ###原来的数据被恢复出来
+----------+---------+
| username | passwd  |
+----------+---------+
| user1    | passwd1 |
+----------+---------+
1 row in set (0.00 sec)
数据库快捷使用 需要phpMyAdmin-3.4.0-all-languages.tar.bz2

下载地址

http://pan.baidu.com/s/1sjlwORr


yum install php-mysql httpd php -y #其他服务的支持


cd /var/www/html/   #在apache服务的默认发布目录下解压
tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2  # 解压
rm -fr phpMyAdmin-3.4.0-all-languages.tar.bz2
mv phpMyAdmin-3.4.0-all-languages mysql # 改名称以便于访问
cd mysql/
cp config.sample.inc.php config.inc.php
vim config.inc.php  #在引号内添加任意的值
systemctl restart httpd  #重启apache服务
在防火墙通过的情况下，在其他浏览器中输入mysql主机的ip/mysql 使用用户和密码的登录可以快捷查看，创建，删除，亦可以使用该软件学习SQL语句
密码忘记即修改

[iyunv@localhost /]# systemctl stop mariadb  #停止服务
[iyunv@localhost /]# mysqld_safe --skip-grant-tables&
[1] 5405
[iyunv@localhost /]# 150201 15:25:18 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
150201 15:25:19 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
   ###此处回车
[iyunv@localhost /]# mysql ##跳过密码登陆
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

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]> update user set Password=password('redhat') where User='root';   ##为root用户修改密码
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0


MariaDB [mysql]>  select User,password from user;
+-------+-------------------------------------------+
| User  | password                                  |
+-------+-------------------------------------------+
| root  | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |
| root  | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |   ##此处与原来对比，加密后的字符已经改变，密码改变
| root  | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| kiosk |                                           |
+-------+-------------------------------------------+
4 rows in set (0.00 sec)


MariaDB [mysql]> quit
Bye
[iyunv@localhost /]# ps aux | grep mysql
root      5405  0.0  0.0 113248  1572 pts/0    S    15:25   0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql     5559  0.4  4.6 860760 88412 pts/0    Sl   15:25   0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root      5606  0.0  0.0 112640   936 pts/0    S+   15:28   0:00 grep --color=auto mysql
[iyunv@localhost /]# kill -9 5405
[1]+  Killed                  mysqld_safe --skip-grant-tables
[iyunv@localhost /]# kill -9 5559
[iyunv@localhost /]# ps aux | grep mysql
root      5626  0.0  0.0 112640   936 pts/0    R+   15:28   0:00 grep --color=auto mysql
[iyunv@localhost /]# systemctl restart mariadb
[iyunv@localhost /]# mysql -uroot -predhat #使用修改后的密码登录
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

MariaDB [(none)]> quit
Bye
正常登录进入后修改密码



[iyunv@localhost /]# mysqladmin  -uroot -predhat password kiosk    ##修改root的密码
[iyunv@localhost /]# mysql -uroot -pkiosk
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
总结：至此，我们应该掌握的内容：数据库的安装，访问，创建库、表，权限的给定和去除，远程登录，用户的添加删除，密码忘记的修改，web网页快捷方访问数据库等
                           
                                                                                                                                                        by：forsaken627