Linux-基于RHEL的Mariadb搭建与管理

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。

安装MariaDB

[root@desktop ~]# yum install mariadb-server.x86_64 -y        ##安装MariaDB数据库

Loaded plugins: langpacks
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
rhel_dvd                                                 | 4.1 kB     00:00     
Resolving Dependencies
--> Running transaction check
---> Package mariadb-server.x86_64 1:5.5.35-3.el7 will be installed
…………………………………………………………………………………………………………………………………………………………………………………………………………
Installed:
  mariadb-server.x86_64 1:5.5.35-3.el7                                          

Dependency Installed:
  mariadb.x86_64 1:5.5.35-3.el7                                                 
  perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7                                  
  perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7                                   
  perl-DBD-MySQL.x86_64 0:4.023-5.el7                                           
  perl-DBI.x86_64 0:1.627-4.el7                                                 
  perl-Data-Dumper.x86_64 0:2.145-3.el7                                         
  perl-IO-Compress.noarch 0:2.061-2.el7                                         
  perl-Net-Daemon.noarch 0:0.48-5.el7                                           
  perl-PlRPC.noarch 0:0.2020-14.el7                                             

Complete!

数据库安全配置：mysql_secure_installation

[root@desktop ~]# 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): 	##输入root用户的密码
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] 				##是否删除匿名用户访问权限
 ... 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] 			##是否限制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] 		##是否删除测试数据
 - 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] 			##是否重新加载
 ... 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@desktop ~]# mysql -u root -p			##登陆数据库,-u指定登陆用户，-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

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)]> exit
Bye

数据库配置文件：/etc/my.cnf

[root@desktop ~]# echo skip-networking=1 >> /etc/my.cnf     ##跳过开启网络接口

数据库的基本操作：

##查看##
SHOW DATABASES；		        ##查看数据库
USE database_name;		##使用数据库
SHOW TABLES；			##查看当前数据库中存在的表
DESC table_name;		##查询表中的子段及属性
SELECT * FROM table_name;	##在表中查询相应子段
##建立SQL
CREATE DATABASE database_name;    ##创建库
CREATE TABLE table_name (name VARCHAR(20),sex CHAR(1));      ##创建表
INSERT INTO table_name VALUES ('WESTOS','M');                ##插入字符
##更改
ALTER TABLE table_name RENAME table_name_new;             ##修改表名称
ALTER TABLE table_name ADD age varchar(50) AFTER name;    ##新建age字段，设置类型和长度，放在name字段后
ALTER TABLE table_name DROP age;                          ##删除age字段
UPDATE table_name SET password='abc' WHERE name='leo';    ##修改指定表中name=leo的数据中password为abc
##删除
DELETE FROM table_name WHERE name='leo'                   ##删除指定表中name=leo的数据
DROP TABLE table_name;                                    ##删除指定表
DROP DATABASE batabase_name;                              ##删除指定库

数据库用户管理

MariaDB [westos]> CREATE USER jinx@'%' identified by 'jinx';	##创建用户jinx可以在任何位置登陆，密码为jinx
MariaDB [westos]> GRANT CREATE,INSERT,UPDATE,DELETE,SELECT on westos.* to jinx@'%';	##用户授权
Query OK, 0 rows affected (0.00 sec)

MariaDB [westos]> FLUSH PRIVILEGES;				##刷新授权列表
Query OK, 0 rows affected (0.00 sec)

MariaDB [westos]> SHOW GRANTS FOR jinx@'%';			##查看用户授权
+-----------------------------------------------------------------------------------------------------+
| Grants for jinx@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jinx'@'%' IDENTIFIED BY PASSWORD '*3E3843E5EFF6318592F84BFBA6653310F96E8B06' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `westos`.* TO 'jinx'@'%'                            |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [westos]> REVOKE CREATE on westos.* from jinx@'%';	##撤销用户权限
Query OK, 0 rows affected (0.00 sec)

MariaDB [westos]> DROP USER jinx@‘%’;				##删除用户

数据库root用户密码找回

[root@desktop ~]# systemctl stop mariadb			##停止服务
[root@desktop ~]# mysqld_safe --skip-grant-tables &		##安全模式，跳过安全表
[1] 1611
[root@desktop ~]# 180526 02:45:02 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
180526 02:45:02 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@desktop ~]# mysql -u root
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)]> SELECT User,Password FROM mysql.user WHERE User='root';
+------+-------------------------------------------+
| User | Password                                  |
+------+-------------------------------------------+
| root | *54958E764CE10E50764C2EECBB71D01F08549980 |
| root | *54958E764CE10E50764C2EECBB71D01F08549980 |
| root | *54958E764CE10E50764C2EECBB71D01F08549980 |
+------+-------------------------------------------+
3 rows in set (0.01 sec)

MariaDB [(none)]> UPDATE mysql.user SET password=password('abc') WHERE user='root';##修改表中root用户密码内容
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [(none)]> exit		##退出
Bye

[root@desktop ~]# ps aux | grep mysql		##查看mysql相关进程
root      1611  0.0  0.1 113252  1568 pts/0    T    02:45   0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql     1853  0.1 10.1 859064 98244 pts/0    Sl   02:45   0:01 /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      1980  0.0  0.0 112644   936 pts/0    S+   02:56   0:00 grep --color=auto mysql

[root@desktop ~]# kill -9 1611
[root@desktop ~]# kill -9 1853

[root@desktop ~]# systemctl start mariadb	##打开服务

[root@desktop ~]# mysql -u root -pabc		##使用新密码登陆
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
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)]> exit
Bye

数据库的备份：mysqldump

mysqldump -uroot -pabc --all-databases --no-data	##--all-databases所有数据库，--no-data之备份框架不备份数据
[root@desktop ~]# mysqldump -uroot -pabc westos > /mnt/westos.sql	##备份数据库
[root@desktop ~]# mysql -uroot -pabc -e "drop database westos;"		##删除数据库
[root@desktop ~]# mysql -uroot -pabc -e "show databases;"		##查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

[root@desktop ~]# mysql -uroot -pabc -e "create database westos;"	##创建数据库
[root@desktop ~]# mysql -uroot -pabc westos < /mnt/westos.sql		##恢复数据库
[root@desktop ~]# mysql -uroot -pabc -e "select * from westos.linux;"	##查看数据库表数据
+------+----------+
| name | password |
+------+----------+
| jinx | jinx     |
| leo  | 123      |
+------+----------+

数据库的网页管理工具：phpMyAdmin

root@desktop ~]# yum install httpd php php-mysql.x86_64 -y

[root@desktop ~]# cd /var/www/html/
[root@desktop html]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 
[root@desktop html]# ls
phpMyAdmin-3.4.0-all-languages  phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@desktop html]# rm -rf *.bz2
[root@desktop html]# mv phpMyAdmin-3.4.0-all-languages/ mysqladmin
[root@desktop html]# cd mysqladmin
[root@desktop mysqladmin]# cp config.sample.inc.php config.inc.php 

访问http://IP地址/mysqladmin，就可以打开数据库的网页管理工具