Linux中的mariadb服务

安装：yum install -y mariadb.server

systemctl start mariadb
systemctl enable mariadb
systemctl status mariadb

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

vim /etc/my.cnf
  [mysqld]
  skip-networking=1
mysql_secure_installation             /安全初始化

数据库基本管理

mysql -uroot -predhat -e "SHOW DATABASES;"

查询
查看表的结构：DESC user;

针对字段搜索：SELECT Host,User,Select_priv FROM user;
增加条件：SELECT Host,User,Select_priv FROM user WHERE Host='localhost';

建立

1.CREATE DATABASE westos;
2.SHOW DATATABLES;
3.CREATE TABLE linux(
    - > username varchar(10) not null,
    - > password varchar(20) not null
    - > );
4.DESC linux;
5.INSERT INTO linux values ('wsp','123');
6.SELECT * FROM linux;
7.SELECT username,password FROM linux;

更改

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';

更新值

SELECT * FROM user;

INSERT INTO user VALUES('wsp1','456','python');
SELECT * FROM user;

删除

DELETE FROM user WHERE username='wsp';
SELECT * FROM user;

SHOW TABLES;
DROP TABLE user;
SHOW TABLES;

DROP DATABASE westos;
SHOW DATABASES;

用户授权

方法一：

CREATE USER westos@localhost identified by 'wsp';     /创建用户；
SELECT * FROM mysql.user;

GRANT SELECT ON userdata. * TO westos@localhost;      /授权；
FLUSH PRIVILEGES；

方法二：

GRAANT SELECT ON userdata. * TO westos@localhost IDENTIFIED BY 'wsp';
REVOKE SELECT ON userdata. * FROM westos@localhost;                       /撤销权限；

备份与恢复

mysqldump -uroot -predhat --all-data
mysqldump -uroot -predhat --all-data --no-data

mysql -uroot -predhat userdata > /mnt/userdata.sql
mysql -uroot -predhat -e "DROP DATABASE userdata;"
mysql -uroot -predhat -e "SHOW DATABASE;"

恢复方法一：

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 DATABASE;
  SELECT * FROM userdata.usertab;

恢复方法二：

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库中

密码更改和破解

mysqladmin -uroot -predhat password westos

超户密码忘记

systemctl stop mariadb
mysqld_safe --skip-grant-tables &

mysql
UPDATE mysql.user SET Password='redhat' WHERE User='root';    /密码是明文
SELECT * FROM mysql.user;

UPDATE mysql.user SET Password=password('redhat') WHERE User='root';
ps aux | grep mysql
kill -9 mysql进程
systemctl start mariadb
mysql -uroot -predhat

phpMyAdmin以图形化方式展示数据库

apache 80端口
默认访问页面 index.html
安装图形化界面步骤：

yum install httpd -y
firewall-cmd --permanent --add-service=http
firewall-cmd --reload
firewall-cmd --list-all
lftp 172.25.254.250
cd pub/software/
get phpMyAdmin-4.0.10.20-all-lanages.tar.gz 
get php-mbstring-5.4.16-21.el7.x86_64.rpm 
tar zxf phpMyAdmin-4.0.10.20-all-lanages.tar.gz  -C /var/www/html
mv phpMyAdmin-4.0.10.20-all-lanages phpMyAdmin

php 动态页面 ，但此时在firefox中查看是文本形式
yum install -y php
systemctl restart httpd
yum search php 
yum install php-mysql.x86_64 -y
rpm -ivh php-mbstring-5.4.16-21.el7.x86_64.rpm
systemctl restart httpd
浏览器访问：http://172.25.254.197/phpMyAdmin
root--->redhat