mariadb数据库

1.数据库的安装和查看

数据库是一个公司的核心，也是以后工作中接触的最重要的东西，所以对于它的熟练使用和查看是我们必须要牢记在心的必备知识。

安装mariadb服务：yum install mariadb-server

启动mariadb：systemctl start mariadb
运行mariadb：systemctl enable mariadb
查看mariadb的运行状态：systemctl status mariadb

数据库的查看：SHOW DATABASES;
表查看：USE mysql;
SHOW TABLES;
表内容查看：SELECT * FROM user;

vim /etc/my.cnf
[mysqld]
skip-networking=1

mysql_secure_installation #安全初始化

2.数据库的基本管理

在shell界面查看数据库：mysql -uroot -predhat -e “SHOW DATABASES;”

1.查询：
查看表结构：DESC user;
针对字段搜索：SELECT Host,User,Select_priv FROM user;
增加条件：SELECT Host,User,Select_priv FROM user WHERE Host=‘localhost’;


2.建立：
CREATE DATABASE westos;
SHOW TABLES;

CREATE TABLE linux(
->username varchar(10) not null,
->password varchar(30) not null
->);
DESC linux;
INSERT INTO linux values (‘wsp’,‘123’);

SELECT * FROM linux;

SELECT username,password FROM linux;

3.更改：
ALTER TABLE linux ADD class varchar(10);
DESC linux;

ALTER TABLE linux ADD age varchar(4) AFTER password; #添加字段在password后面
DESC linux;

ALTER TABLE linux DROP age;

ALTER TABLE linux RENAME user; #重命名表
SELECT * FROM user;


UPDATE user SET class=‘linux’ WHERE username=‘wsp’ AND password=‘123’; #更新class的值
SELECT * FROM user;

3.删除：
DELETE FROM user WHERE username=‘wsp’;
SELECT * FROM user;

SHOW TABLES;

DROP TABLE user;
SHOW TABLES;

DROP DATABASE westos;
SHOW DATABASES;

4.用户授权：
CREATE USER westos@localhost identified by ‘wsp’; #创建用户，用户密码为wsp，此时此用户没有任何权限
SELECT * FROM mysql.user;


GRANT SELECT ON userdata.* TO westos@localhost; #授权用户westos在userdata数据库下有查询权限
FLUSH PRIVILEGES; #刷新




方法2：
GRANT SELECT ON userdata.* TO westos@localhost IDENTIFIED BY ‘wsp’;

REVOKE SELECT ON userdata.* FROM westos@localhost; #撤销权限


5.备份与恢复：
mysqldump -uroot -predhat --all-data #备份全部数据
mysqldump -uroot -predhat --all-data --no-data #只备份其中的框架，不备份其中的数据
mysqldump -uroot -predhat userdata > /mnt/userdata.sql #备份userdata数据库
mysql -uroot -predhat -e “DROP DATABASE userdata;”
mysql -uroot -predhat -e “SHOW DATABASES;”

恢复方式1：
[ root@localhost ~] # mysql -uroot -predhat < /mnt/userdata.sql
ERROR 1046(3D000) at line 22: NO database selected

vim /mnt/userdata.sql
21 CREATE DATABASE userdata;
22 USE userdata;


mysql -uroot -predhat
SHOW DATABASES;
SELECT * FROM userdata.usertab;


恢复方式2：
mysql -uroot -predhat -e “DROP DATABASE userdata;”
mysql -uroot -predhat -e “CREATE DATABASE userdata;”
vim /mnt/userdata.sql
删除21 22行
mysql -uroot -predhat userdata < /mnt/userdata.sql #指定导入到userdata库

6.密码更改和破解
mysqladmin -uroot -predhat password westos

超户密码忘记
systemctl stop mariadb
mysqld_safe --skip-grant-tables &

mysql
UPDATE mysql.user SET Password=‘redhat’ WHERE User=‘root’; #密码是明文
UPDATE mysql.user SET Password=password(‘redhat’) WHERE User=‘root’;

ps aux | grep mysql
kill -9 mysql进程

systemctl start mariadb
mysql -uroot -predhat

3.phpMyAdmin

yum install httpd
firewall-cmd --permanent --add-service=http
firewall-cmd --reload

tar zxf phpMyAdmin-4.0.10.20-all-languages.tar.gz -C /var/www/html/ #将php的压缩包解压至/var/www/html/下
mv phpMyAdmin-4.0.10.20-all-languages phpMyAdmin
yum install php
systemctl restart httpd

yum install php-mysql
yum install php-mbstring-5.4.16-21.el7.x86_64.rpm

systemctl restart httpd
浏览器访问：172.25.254.xxx/phpMyAdmin

root --> redhat