Mariadb数据库

什么是Mariadb

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

Mariadb的安装

[root@mariadb ~]# yum install mariadb-server -y  ####下载marisdb
[root@mariadb ~]# systemctl start mariadb ####开启mariadb服务

Mariadb数据库的基本操作SQL

/etc/my.cnf ———– mariadb的配置文件

数据库初始化

[root@mariadb ~]# netstat -antlpe | grep mysql  ####查看mysql网络端口是开启的
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 75409 3232/mysqld

[root@mariadb ~]# vim /etc/my.cnf  ####编辑配置文件
10 skip-networking=1  ####跳过网络接口

[root@mariadb ~]# systemctl restart mariadb  ####重启marisdb
[root@mariadb ~]# netstat -antlpe | grep mysql

[root@mariadb ~]# 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):
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: ———输入新密码
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] ####禁止超级用户远程访问
… 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!

1.登陆

mysql -uusername -ppasswd
mysql -uusername -p
——–> -u表示用户名 -p表示密码
[root@mariadb ~]# mysql ####匿名用户无法登陆
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)
[root@mariadb ~]# mysql -uroot -p ####使用刚刚设置的密码进入
Enter password: ———–>输入密码

2.查询

[root@mariadb ~]# mysql -uroot -p
MariaDB [(none)]> show databases; ####显示数据库

MariaDB [(none)]> use mysql; ####进入mysql数据库
MariaDB [mysql]> show tables; ####显示mysql数据库的所有表格

MariaDB [mysql]> select * from user; ####显示user表格的所有内容，*可以被替换为user表格的字段名

MariaDB [mysql]> desc user； ####显示user表格的所有字段

3.数据库及表的建立

———建立数据库

MariaDB [(none)]> create database westos; ####建立westos库

———建立表
MariaDB [(none)]> use westos; ####进入westos库
MariaDB [westos]> create table linux( ####不加;号，创建linux表，并编辑出两个字段
-> username varchar(15) not null, ####username字段，最多可容纳15字符，并且不能为空
-> password varchar(15) not null); ####password字段
Query OK, 0 rows affected (0.07 sec)

———–向表添加内容
MariaDB [westos]> insert into linux values (‘user1’,’123’); ####向linux表插入数据，username字段的数据是user1，password字段的数据是123
MariaDB [westos]> insert into linux values (‘user2’,password(‘123’)); ####对password字段的数据加密

MariaDB [westos]> select * from linux;
+———-+—————–+
| username | password |
+———-+—————–+
| user1 | 123 |
| user2 | *23AE809DDACAF9 | ####由此看出user2该行的password字段是加密的
+———-+—————–+
2 rows in set (0.00 sec)

4.更新数据库信息

MariaDB [westos]> select * from linux;####查看那linux表的所有内容

(1)更新一个密码
update linux set password=password(‘123’) where username=’user1’;####更新user1的密码，并使其显示是加密的

(2)同时更新两个密码
update linux set password=password(‘123’) where ( username=’user1’ or username=’user3’ ); ####同时更新user1和user3的密码，并使其显示是加密的

(3)删除表格信息
delete from linux where username=’user1’; ####删除user1的信息

(4)添加表格字段
alter table linux add age varchar(4);####添加age字段

(5)添加表格字段定义位置
alter table linux add date varchar(5) not null after password; ####添加date字段到password字段后

(6)删除字段
MariaDB [westos]> alter table linux drop age; ####删除age字段

5.删除数据库

(1)删除表格信息
delete from linux where username=’user1’; ####删除username为user1的信息

(2)删除表
drop table linux； ####删除表linux

(3)删除数据库
drop database westos; ####删除库westos

6.备份数据库

(1)备份所有
mysqldump -u root -pwestos –all-databases ####备份所有表的所有数据

(2)备份所有表
mysqldump -u root -pwestos –all-databases –no-data ####备份所有表，但不备份数据

(3)备份单一库
mysqldump -u root -pwestos westos ####备份westos库

(4)备份库与恢复库
mysqldump -u root -pwestos westos > /mnt/westos.sql ####备份westos库并把数据保存到/mnt*/westos.sq1
mysql -u root -pwestos -e “drop database westos;” ####删除westos库，-e表示操作
mysql -u root -pwestos -e “create databases westos” ####建立westos库
mysql -u root -pwestos westos < /mnt/westos.sql ####将westos.sql备份文件导入到westos库中
mysql -u root -pwestos -e “show databases;” ####显示数据库

(5)备份表与恢复表
mysqldump -u root -pwestos westos linux > /mnt/linux.sql####备份westos库的linux表到/mnt/linux.sql中
mysql -u root -pwestos -e “drop table westos.linux;”####删除westos库中的linux表
mysql -u root -pwestos westos < /mnt/linux.sql ####恢复
mysql -uroot -pwestos westos -e “show tables from westos”####查看westos库的表格

7.用户授权

[root@mariadb ~]# vim /etc/my.cnf ####编辑配置文件
10 skip-networking=0 ####修改可以使用网络登陆

[root@mariadb ~]# systemctl restart mariadb.service ####重启mariadb服务

(1)建立用户
create user lee@localhost identified by ‘123’; ####创建用户lee，只能在本机登陆，密码为123
create user lee@’%’ identified by ‘123’; ####创建用户lee，只能通过网络登陆，密码为123

(2)查看用户表
select User,Host from mysql.user ####查看用户表
MariaDB [(none)]> select User,Host from mysql.user
-> ;
+——+———–+
| User | Host |
+——+———–+
| lee | % | ####网络登陆
| root | 127.0.0.1 |
| root | ::1 |
| lee | localhost | ####本地登陆
| root | localhost |
+——+———–+
5 rows in set (0.00 sec)

(3)用户授权
grant insert,update,delete,select on westos.* to lee@localhost;####用户lee@localhost具有对westos库的所有表格有写入，更新，删除，查看的权限
grant select on westos.test to lee@’%’; ####用户lee@%具有对westos的test表格有查看的权限

(4)用户授权的查看
show grants for lee@’%’; ####查看lee@%的用户授权

(5)用户授权的更改
revoke delete on westos.* from lee@localhost ####删除lee@localhost对westos库所有表格的delete权限

(6)用户的删除
drop user lee@’%’ ####删除用户

8.密码修改

(1)知道密码
mysqladmin -uroot -pwestos password lee

(2)忘记密码
—————–修改密码
systemctl stop mariadb.service ####停止mariadb服务
mysqld_safe –skip-grant-tables & ####开启mysql登陆接口并忽略授权表
mysql ####直接进入数据库不需要输入密码
update mysql.user set Password=password(‘123’) where User=’root’;####更新超级用户密码

—————恢复服务
ps aux | grep mysql ####过滤出mysql进程
kill -9 ####强制结束
[root@mariadb ~]# systemctl start mariadb ####重启mariadb服务
[root@mariadb ~]# mysql -uroot -p123 ####用新密码登陆

数据库的网页管理工具

1.安装

[root@mariadb ~]# yum install httpd php php-mysql lftp -y ##下载httpd，php，php-mysql，即下载httpd服务，后台语言，mysql的后台语言辅助
[root@mariadb ~]# systemctl start httpd ##开启httpd服务
[root@mariadb ~]# systemctl enable httpd ##设置httpd开机自动启动
[root@mariadb ~]# systemctl stop firewalld.service ##关闭防火墙服务
[root@mariadb ~]# systemctl disable firewalld.service ##设置防火墙开机不启动

2.配置

[root@mariadb ~]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html/ ##解压包到/var/www/html/，使可以网络访问

[root@mariadb ~]# cd /var/www/html/
[root@mariadb html]# ls
phpMyAdmin-3.4.0-all-languages
[root@mariadb html]# mv phpMyAdmin-3.4.0-all-languages/ mysqladmin ##重命名
[root@mariadb html]# cd mysqladmin/
[root@mariadb mysqladmin]# cp -p config.sample.inc.php config.inc.php ##复制文件

[root@mariadb mysqladmin]# vim config.inc.php ##编辑配置文件
17 $cfg[‘blowfish_secret’] = ‘mysql’; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

[root@mariadb mysqladmin]# systemctl restart httpd.service

3.测试

访问 网页
http://172.25.254.133/mysqladmin