mysql数据库的基本管理

mariadb
字段 ：表格中的表头
表 ：表格
库 ：存放表格的目录
查询 ：对表格中的指定内容进行查看

dnf install mariadb-server.x86_64 -y
systemctl enable --now mariadb
mysql
USE mysql;
SHOW TABLES;

对mysql进行安全初始化

mysql_secure_installation
密码
y
y
y
y

mysql -u root -pwestos
netstat -antlupe | grep mysql
rpm -qc mariadb-server
vim /etc/my.cng.d/meriadb-server.cnf
skip-networking=1
systemctl restart mariadb
netstat -antlupe | grep mysql

mysql -uroot -p
westos
SHOW DATABASES;
USE mysql;
SELECT * FROM user; #查询所有信息
SELECT User,Host FROM mysql.user; #查询指定字段
SELECT Password,Host FROM mysql.user WHERE User='root'; #AND

CREATE DATABASE westos;
SHOW DATABASES;
USE westos;
SHOW TABLES;
CREATE TABLE linux (
username varchar (10) not null,
password varchar (50) not null
);
DESC linux;#显示表格的属性,结构
INSERT INTO linux VALUES ('user1','123');
INSERT INTO linux VALUES ('user2','123'),('user3','123');

SHOW DATABASES;
USE westos;
SHOW TABLES;
ALTER TABLE linux RENAME redhat;
SHOW TABLES;
DESC linux;
ALTER TABLE linux ADD age varchar(4);
DESC linux;
ALTER TABLE linux DROP age; #删除某一列
DESC linux;
ALTER TABLE linux ADD age varchar(4) AFTER username;
SELECT * FROM linux;
UPDATE linux SET age='20' WHERE username='user1';
SELECT * FROM linux;
UPDATE linux SET age='20' WHERE username='user1' and password='123';

DELETE FROM linux WHERE username='user2' and age='18';
DROP TABLE linux;
DROP DATABASE westos;

数据库的密码管理
在已知密码的情况下

mysqladmin -uroot -p password lee

不知密码的情况下

systemctl stop mariadb
mysqld_safe --skip-grant-tables & #在启动程序的时候，跳过认证表
mysql -uroot
SELECT * FROM mysql.user;
如果数据库密码从来没有被更改过 那么修改字段的时候修改的是password字段；如果密码被修改过，修的就是authentication_string字段

UPDATE mysql.user SET authentication_string='westos'
WHERE User='root';
UPDATE mysql.user SET authentication_string=password('westos') WHERE User='root';
SELECT * FROM mysql.user;
ps aux | grep mysql
kill -9 PID
ps aux | grep mysql
systemctl start mariadb

mysql的数据备份及恢复

SHOW DATABASES;
USE westos;
SHOW TABLES;
SELECT * FROM linux ;

mysqldump -uroot -p westos #数据的复制
mysqldump -uroot -p westos > /mnt/westos.sql #数据的备份
mysqldump -uroot -p --all-databases #备份所有库的信息
mysqldump -uroot -p --all-databases --no-data #只要表格不要数据
mysql -uroot -p -e "DROP DATABASE westos;"
mysql -uroot -p -e "SHOW DATABASES;"
第一种、数据恢复方法
mysql -uroot -p -e "CREATE DATABASE westos;" #e表示不进入服务，直接执行命令。
mysql -uroot -pwestos westos < /mnt/westos.sql
mysql -uroot -pwestos -e "SELECT * FROM westos.linux;"
第二数据恢复方法
vim /mnt/westos.sql
CREATE DATABASE westos;
USE westos;#这两行要放在DROP前面
mysql -uroot -pwestos < /mnt/westos.sql

mysql数据库中用户的授权

mysql -uroot -pwestos
SELECT * FROM mysql.user;
建立用户
CREATE USER westos@'%' identified by 'westos';
CREATE USER lee@localhost identified by 'westos';
#%表示可以在任意的客户端通过网络登录。localhost 表示本机
DROP USER lee@localhost; #删除用户

vim /etc/my.cnf.d/mariadb-server.cnf
注释掉#skip-networking=1
systemctl restart mariadb-service
systemctl stop firewalld

本机
mysql -westos -p -h172.25.254.150
SHOW DATABASES;

虚拟机
SELECT * FROM mysql.user;
SHOW GRANTS FOR lee@localhost;
SHOW GRANTS FOR westos@localhost;

mysql -uroot -pwestos
GRANT SELECT ON westos.* TO lee@localhost;#建立授权
SHOW GRANTS FOR lee@localhost;
REVOKE SELECT ON westos.* FROM lee@localhost;
SHOW DATABASES;

mysql数据库的web管理插件
要删掉squid，否则会占用80端口

dnf info php #查看php的信息
dnf install httpd php -y
systemctl enable --now httpd
firewall-cmd --permanent --add-service=http
firewall-cmd --reload
#systemctl stop firewalld

本地仓库：172.25.254.50/software/
phpmyadmin-3.4.0-

wget 链接
tar jxf 压缩包 -C /var/www/html/
cd /var/www/html/
ls
mv 名字/ mysqladmin
ls
cd mysqladmin/
ls
less README
less Documentation.txt
cp config.sample.inc.php config.inc.php
vim config.inc.php #加上机械
systemctl restart httpd
dnf install php-mysqlnd.x86-64 -y
php -m