一、Mariadb的介绍
数据库管理系统（英语：Database Management System，简称DBMS）是为管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能。
MariaDB虽然被视为MySQL数据库的替代品，但它在扩展功能、存储引擎以及一些新的功能改进方面都强过MySQL。而且从MySQL迁移到MariaDB也是非常简单的：
1、数据和表定义文件（.frm）是二进制兼容的
2、所有客户端API、协议和结构都是完全一致的
3、所有文件名、二进制、路径、端口等都是一致的
4、所有的MySQL连接器，比如PHP、Perl、Python、Java、.NET、MyODBC、Ruby以及MySQL C connector等在MariaDB中都保持不变
5、mysql-client包在MariaDB服务器中也能够正常运行
6、共享的客户端库与MySQL也是二进制兼容的
也就是说，在大多数情况下，你完全可以卸载MySQL然后安装MariaDB，然后就可以像之前一样正常的运行。
二、Mariadb的安装
1. 安装并开启服务
yum install mariadb-server.x86_64 -y
systemctl start mariadb
2.安全初始化
[root@localhost ~]# netstat -antlpe| grep mysql //查看所开启的网络接口，3306接口是打开的
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
27 97563 3638/mysqld
[root@localhost ~]# vim /etc/my.cnf //编写配置文件关闭网络接口
10 skip-networking=1
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# netstat -antlpe |grep mysql //此时没有接口打开
[root@localhost ~]# 
增加一行参数：skip-networking=1
[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): //为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用户的密码，输入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 //是否删除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!
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
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)]> //成功登陆
三、数据库的命令介绍
mysql; ##无密码登陆数据库
mysql -uroot -p; ##root用户登陆数据库
USE mysql; ##进入mysql数据库
SHOW DATABASES; ##显示databases中所有的库，任何命令必须以“;”结尾
SHOW TABLES; ##显示当前数据库的所有表
SELECT * FROM user; ##显示所有字段的数据（字段指列）
SELECT Host，User FROM user; ##显示user表中Host和User字段的数据
SELECT Host，User FROM user Where Host='localhost'; ##显示user表中Host='localhost'的Host和User字段的数据
DESC user; ##显示当前数据库的所有字段，查看user表的数据结构
quit; ##退出数据库
四、数据库的使用
1. 数据库的查询
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> USE mysql //进入到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]> SHOW TABLES; //查看表格列表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
MariaDB [mysql]> SELECT User,Host,Password FROM user; //查看user表格里的指定数据
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *ABCDBDDA833FA5726A2C9AEC6690A696F5DB8969 |
| root | 127.0.0.1 | *ABCDBDDA833FA5726A2C9AEC6690A696F5DB8969 |
| root | ::1 | *ABCDBDDA833FA5726A2C9AEC6690A696F5DB8969 |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
MariaDB [mysql]>
2.数据库的建立，及字符插入
[root@localhost ~]# mysql -uroot -p
MariaDB [(none)]> CREATE DATABASE westos; ##新建一个westos库；
MariaDB [(none)]> SHOW DATABASES; ##查看库，已经建立
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> USE westos ##进入库；
Database changed
MariaDB [westos]> SHOW TABLES;##查看库中的表格；
Empty set (0.00 sec)
MariaDB [westos]> CREATE TABLE linux ( ##新建表格；
-> username varchar(6) not null, ##有效字符长度为6，不能为空；
-> password varchar(20) not null); ##有效字符长度为20，不能为空；
Query OK, 0 rows affected (0.04 sec)
MariaDB [westos]> SHOW TABLES; ##查看表格，已经建立；
| Tables_in_westos |
+------------------+
| linux |
+------------------+
1 row in set (0.01 sec)
MariaDB [westos]> DESC linux; ##查看表格的字段；
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(6) | NO | | NULL | |
| password | varchar(20) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MariaDB [westos]> INSERT INTO linux values ('gao','123'); ##插入信息；
Query OK, 1 row affected (0.05 sec)
MariaDB [westos]> SELECT * FROM linux; ##显示linux表格中的所有信息；
+----------+----------+
| username | password |
+----------+----------+
| gao | 123 |
+----------+----------+
1 row in set (0.00 sec)
MariaDB [westos]> INSERT INTO linux values ('tom','234'); ##插入信息；
Query OK, 1 row affected (0.03 sec)
MariaDB [westos]> SELECT * FROM linux; ##显示linux表格中的所有信息；
+----------+----------+
| username | password |
+----------+----------+
| gao | 123 |
| tom | 234 |
+----------+----------+
2 rows in set (0.00 sec)
3.数据库内容的修改及删除
[root@localhost ~]# mysql -uroot -p
MariaDB [(none)]> USE westos ##进入库；
Database changed
MariaDB [westos]> ALTER TABLE linux RENAME messages; ##对表格名称的修改；
Query OK, 0 rows affected (0.05 sec)
MariaDB [westos]> SHOW TABLES;
+------------------+
| Tables_in_westos |
+------------------+
| messages |
+------------------+
1 row in set (0.00 sec)
MariaDB [westos]> ALTER TABLE messages ADD age varchar(4); ##在表格中新添age字段；
Query OK, 2 rows affected (0.10 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> SELECT * FROM messages; ##显示表格中的所有信息；
+----------+----------+------+
| username | password | age |
+----------+----------+------+
| gao | 123 | NULL |
| tom | 234 | NULL |
+----------+----------+------+
2 rows in set (0.00 sec)
MariaDB [westos]> ALTER TABLE messages DROP age; ##删除字段
Query OK, 2 rows affected (0.10 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> SELECT * FROM messages; ##显示表格中的所有信息；
+----------+----------+
| username | password |
+----------+----------+
| gao | 123 |
| tom | 234 |
+----------+----------+
2 rows in set (0.00 sec)
MariaDB [westos]> ALTER TABLE messages ADD age varchar(4) after username; ##在表格中固定位置添加age字段；
Query OK, 2 rows affected (0.16 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> SELECT * FROM messages; ##显示表格中的所有信息；
+----------+------+----------+
| username | age | password |
+----------+------+----------+
| gao | NULL | 123 |
| tom | NULL | 234 |
+----------+------+----------+
MariaDB [westos]> UPDATE messages SET password='456' WHERE username='gao'; ##对表格内容更新；
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [westos]> SELECT * FROM messages; ##显示表格中的所有信息；
+----------+------+----------+
| username | age | password |
+----------+------+----------+
| gao | NULL | 456 |
| tom | NULL | 234 |
+----------+------+----------+
2 rows in set (0.00 sec)
MariaDB [westos]> DELETE from messages WHERE username='gao'; ##删除表格内容；
Query OK, 1 row affected (0.02 sec)
MariaDB [westos]> SELECT * FROM messages; ##显示表格中的所有信息；
+----------+------+----------+
| username | age | password |
+----------+------+----------+
| tom | NULL | 234 |
+----------+------+----------+
1 row in set (0.00 sec)
MariaDB [westos]> DROP TABLE messages; ##删除表格；
Query OK, 0 rows affected (0.03 sec)
MariaDB [westos]> DROP DATABASE westos; ##删除数据库
Query OK, 0 rows affected (0.00 sec)
五、创建用户和访问权限的设置
1.创建数据库用户
[root@localhost ~]# mysql -uroot -p
MariaDB [(none)]> CREATE USER linux@'localhost' identified by'123'; ##创建linux用户，密码123；
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT USER FROM mysql.user; ##用户添加成功
+------+
| USER |
+------+
| root |
| root |
| linux|
| root |
+------+
4 rows in set (0.00 sec)2.用户权限的设定
[root@localhost ~]# mysql -uroot -p
MariaDB [(none)]> GRANT SELECT ON westos.* to linux@localhost; ##给一个可看的权限；
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT UPDATE ON westos.* to linux@localhost; ##给一个可以更新的权限；
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GRANTS FOR linux@localhost; ##显示linux用户的权限；
3.用户权限的删除
MariaDB [(none)]> REVOKE UPDATE ON westos.* from gao@localhost; ##删除可更新权限
Query OK, 0 rows affected (0.00 sec)4.删除用户
MariaDB [(none)]> DROP USER gao@localhost; ##删除用户gao
Query OK, 0 rows affected (0.00 sec)
六、数据库忘记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('hello') Where User='root'; ##重新设定root密码，（'hello'）为root用户的新密码；
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
[root@localhost ~]# ps aux |grep mysql ##查看mysql的相关进程；
[root@localhost ~]# kill -9 pid ##结束相关进程；
[root@localhost ~]# systemctl restart mariadb ##重启服务；
七、数据库的备份
1.数据库的备份
mysqldump -uroot -phello nba >/mnt/nba.sql ##数据库的备份
2.数据库的恢复
root@localhost ~]# mysql -uroot -phello -e "drop database linux;" ##删除nba库；
[root@localhost ~]# mysql -uroot -phello linux </mnt/linux.sql ##此时没有nba库，无法恢复
ERROR 1049 (42000): Unknown database 'linux'方法一：
vim /mnt/linux.sql ##对库文件进行修改
CREATE DATABASE linux；
USE linux；
root@localhost ~]# mysql -uroot -phello </mnt/linux.sql ##恢复数据库
[root@localhost ~]# mysql -uroot -phello -e "select * from linux.player;" ##恢复成功
+------+-----+
| name | age |
+------+-----+
| kobe | 36 |
+------+-----+方法二：（命令创建nba数据库进行恢复）
root@localhost ~]#mysql -uroot -phello -e "CREATE DATABASE linux;"
[root@localhost ~]#mysql -uroot -phello linux < /mnt/linux.sql八、phpmyadmin数据库图形化管理
[root@localhost ~]# yum install php -y ##下载php
[root@localhost ~]# yum install httpd -y ##下载安装httpd服务器
[root@localhost ~]# systemctl start httpd ##开启httpd 服务
[root@localhost ~]# cd /var/www/html/ ##下载phpMyAdmin 并解压到/var/www/html下
[root@localhost html]# ls phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@localhost html]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 ##对安装包解压
[root@localhost html]# ls
phpMyAdmin-3.4.0-all-languages phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@localhost html]# rm -rf phpMyAdmin-3.4.0-all-languages.tar.bz2 ##删除安装包
[root@localhost html]# mv phpMyAdmin-3.4.0-all-languages/ mysqladmin ##重命名
[root@localhost html]# cd mysqladmin/
[root@localhost mysqladmin]# cp config.sample.inc.php config.inc.php ##重命名文件为config.inc.php
修改config.inc.php文件
cat Documentation.txt
vim config.inc.php 将Documentation.txt 文件中的字符串放入config.inc.php文件
下载安装php-mysql
并重启httpd服务 systemctl restart httpd
测试：
打开网页浏览 7
172.25.254.110/mysqladmin
root密码为数据库的初始化密码，输入可以开始使用
扫一扫
118
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
