本文共 5835 字,大约阅读时间需要 19 分钟。
1.什么是数据库
数据库就是个高级的表格软件2.常见数据库
Mysql Oracle mongodb db2 sqlite sqlserver …3.Mysql (SUN -----> Oracle)
4.mariadb
[root@d ~]# dnf install mariadb-server.x86_64 -y
mariadb.service ##启动服务
3306 ##默认端口号 /etc/my.cnf ##主配置文件 /var/lib/mysql ##数据目录,当需要重新安装mariadb时需要清理此目录或备份[root@d ~]# systemctl enable --now mariadb
[root@d ~]# vim /etc/my.cnf.d/mariadb-server.cnf [mysqld]skip-networking=1[root@d ~]# systemctl restart mariadb.service [root@d ~]# netstat -antlupe | grep mysql #此命令查询不到端口
[root@d ~]# mysql_secure_installation Remove anonymous users? [Y/n] yDisallow root login remotely? [Y/n] yRemove test database and access to it? [Y/n] y Reload privilege tables now? [Y/n] y[root@d ~]# mysql ##默认不需要密码,初始化完毕后需要ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)[root@d ~]# mysql -uroot -p ## -u 指定登陆用户 -p 密码MariaDB [(none)]> exit
[root@d ~]# mysql -uroot -pSHOW DATABASES; ##显示库名称USE mysql; ##进入mysql库SHOW TABLES; ##显示库中的所有表SELECT * FROM user; ##查询所有数据SELECT Host,User,Password FROM user; ##查询指定字段
[root@d ~]# mysql -uroot -pMariaDB [(none)]> CREATE DATABASE westos; ##新建库MariaDB [(none)]> SHOW DATABASES;MariaDB [(none)]> USE westos;MariaDB [westos]> SHOW TABLES;MariaDB [westos]> CREATE TABLE westos ( username varchar(6) not null,password varchar(30) not null); ##新建表MariaDB [westos]> DESC westos; ##显示表结构MariaDB [westos]> INSERT INTO westos VALUES('user1','123'),('user2','123'); #插入数据MariaDB [westos]> FLUSH PRIVILEGES; #刷新数据库MariaDB [westos]> SELECT * FROM westos;
尽量不要更改库的名字,否则可能会导致数据的MariaDB [westos]> ALTER TABLE westos RENAME user;+----------+----------+| username | password |+----------+----------+| user1 | 123 || user2 | 123 |+----------+----------+MariaDB [westos]> ALTER TABLE westos ADD age varchar(4) AFTER password;+----------+----------+------+| username | password | age |+----------+----------+------+| user1 | 123 | NULL || user2 | 123 | NULL |+----------+----------+------+MariaDB [westos]> SELECT * FROM westos;+----------+----------+| username | password |+----------+----------+| user1 | 123 || user2 | 123 |+----------+----------+MariaDB [westos]> UPDATE westos SET age='18',class='linux' WHERE username='user1';+----------+----------+------+-------+| username | password | age | class |+----------+----------+------+-------+| user1 | 123 | 18 | linux || user2 | 123 | NULL | NULL |+----------+----------+------+-------+
删除数据之前我们先将已有的数据做一下备份。MariaDB [westos]> DELETE from westos where username='user1' and age='18';MariaDB [westos]> DROP TABLE westos;MariaDB [westos]> DROP DATABASE westos;
[root@d mnt]# mysqladmin -uroot -p password westos[root@d mnt]# mysql -uroot -p #输入新的密码。
[root@d mnt]# systemctl stop mariadb.service [root@d mnt]# mysqld_safe --skip-grant-tables &[root@d mnt]# mysqlMariaDB [(none)]> UPDATE mysql.user set authentication_string=password('lee') WHERE User='root'; #RHEL8UPDATE mysql.user set Password=password('lee') WHERE User='root'; ##RHEL7MariaDB [(none)]> flush privileges;[root@d mnt]# ps aux | grep mysql[root@d mnt]# kill -9 32684[root@d mnt]# kill -9 32778[root@d mnt]# ps aux | grep mysqlroot 32872 0.0 0.0 12108 1056 pts/0 S+ 11:30 0:00 grep --color=auto mysql[root@d mnt]# systemctl restart mariadb.service [root@d mnt]# mysql -uroot -p
MariaDB [(none)]> CREATE USER lee@localhost identified by 'lee'; ##只能用localhost登陆MariaDB [(none)]> SELECT User,Host FROM mysql.user;+------+-----------+| User | Host |+------+-----------+| root | 127.0.0.1 || root | ::1 || lee | localhost || root | localhost |+------+-----------+MariaDB [(none)]> GRANT INSERT,SELECT ON westos.* TO lee@localhost;MariaDB [(none)]> SHOW GRANTS for lee@localhost;| GRANT SELECT, INSERT ON `westos`.* TO 'lee'@'localhost' REVOKE SELECT ON westos.* FROM lee@localhost; #移除什么权力DROP user lee@localhost; #删除用户远程登陆数据库 #但是尽量不要使用远程登陆对数据库不安全。[root@d mnt]# vim /etc/my.cnf.d/mariadb-server.cnf # skip-networking=1 注释 ##开启开放端口。[root@d mnt]# systemctl restart mariadb.service [root@d mnt]# netstat -antlupe | grep mysqltcp6 0 0 :::3306 :::* LISTEN 27 201233 33509/mysqld MariaDB [(none)]> CREATE USER lee@'%' identified by 'lee';MariaDB [(none)]> SELECT User,Host FROM mysql.user;+------+-----------+| User | Host |+------+-----------+| lee | % || root | 127.0.0.1 || root | ::1 || root | localhost |+------+-----------+[root@d mnt]# systemctl restart mariadb.service[root@d mnt]# mysql -ulee -p -h ip号 #可能需要关火墙。MariaDB [(none)]> DROP user lee@'%';
[root@d mnt]# mysqldump -uroot -p --all-databases[root@d mnt]# mysqldump -uroot -p --all-databases --no-data[root@d mnt]# mysqldump -uroot -p westos > /mnt/westos.sql两种重新倒入数据库的方式:1.[root@d mnt]# vim westos.sql 19 -- Table structure for table `westos` 20 -- 21 CREATE DATABASE westos; 22 USE westos; 23 DROP TABLE IF EXISTS `westos`;[root@d mnt]# mysql -uroot -p < /mnt/westos.sql #就将数据重新倒入了数据库中。2.[root@d mnt]# mysql -uroot -p -e "create database westos;"[root@d mnt]# mysql -uroot -p westos < /mnt/westos.sql #需要注意把写入westos.sql的21和22行删去先。
用代码来管理数据库比较麻烦,下面介绍一种网页图形化管理数据库,简单又方便,还可以学习具体操作的代码,very nice.
[root@d ~]# dnf install httpd php php-mysqlnd -y[root@d ~]# systemctl enable --now httpd[root@d Desktop]# cp phpMyAdmin-3.4.0-all-languages.tar.bz2 /var/www/html/[root@d Desktop]# cd /var/www/html/[root@d html]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2[root@d html]# mv phpMyAdmin-3.4.0-all-languages myadmin[root@d html]# cd myadmin/[root@d myadmin]# cp config.sample.inc.php config.inc.php[root@d myadmin]# systemctl restart httpd
转载地址:http://znhzi.baihongyu.com/