一、MariaDB数据库的安装及其基本配置
1.[root@server101 ]# yum install mariadb mariadb-server ##安装mariadb和maridb-server组件
2.[root@server101 ]# systemctl start mariadb ##启动mariadb服务
3.[root@server101 ]# netstat -antlpe | grep mysql ##查看mariadb的监听端口
LISTEN 0 50 *:3306 *:* users:(("mysqld",4125,14))
4.[root@server101]# vim /etc/my.cnf ##修改mysql的配置文件
添加 此时只允许通过套接字文件进行本地连接,阻断所有来自网络的tcp/ip连接
5.改完之后重启mariadb服务
[root@server101]# systemctl start mariadb
现在我们可以再次查看mariadb的监听端口
[root@server101]# netstat -antlpe | grep mysql
可以发现mysql的端口没有开放
二、MariaDB数据库安全设置
[root@server101]# mysql_secure_installation
只有下面这个需要我们输入密码,其他的都直接回车
Set root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!Reloading privilege tables..
... Success!
设置过密码后,还可以用这个命令来修改root用户在数据库的密码
测试:[root@server101]# MySQL -uroot -p
三、数据库的基本操作
首先进入数据库:
[root@server101]# mysql -uroot -p
1.SHOW DATABASES; 显示现在所有的数据库
MariaDB [(none)]> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || wuwu |+--------------------+4 rows in set (0.00 sec)
2.CREATE DATABASE database_name; 创建新的数据库
MariaDB [(none)]> CREATE DATABASE westos;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| westos |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
3.USE database_name; 进入某个数据库
MariaDB [(none)]> USE mysql;
Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A
Database changed
4.SHOW tables; 显示库里的表
MariaDB [mysql]> SHOW tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
5.CREATE TABLE table_name (name VARCHAR(20), sex CHAR(1)); 创建表
MariaDB [mysql]> CREATE TABLE happy (name VARCHAR(20), sex CHAR(1));Query OK, 0 rows affected (0.46 sec)6.SELECT * FROM table_name; 查看某一表中的所有列
7.INSERT INTO table_name VALUES ('wxh','M'); 向某一表中插入以行,插入内容时括号中的
MariaDB [mysql]> SELECT * FROM happy;Empty set (0.00 sec)MariaDB [mysql]> INSERT INTO happy VALUES ('wxh','M');Query OK, 1 row affected (0.39 sec)MariaDB [mysql]> SELECT * FROM happy;+------+------+| name | sex |+------+------+| wxh | M |+------+------+1 row in set (0.00 sec)8.UPDATE table_name SET attribute=value WHERE attribute > value; 更新表中信息
MariaDB [mysql]> UPDATE happy SET sex='man' WHERE name='wxh';Query OK, 1 row affected, 1 warning (0.44 sec)Rows matched: 1 Changed: 1 Warnings: 19.DELETE FROM table_name WHERE attribute = value; 删除表中的某些行
MariaDB [mysql]> DELETE FROM happy WHERE name='wxh';Query OK, 1 row affected (0.37 sec)10.DROP TABLE table_name;
10.DROP TABLE table_name; 删除表
MariaDB [mysql]> DROP TABLE happy;Query OK, 0 rows affected (0.03 sec)
11.DROP DATABASE database_name; 删除数据库
MariaDB [mysql]> DROP DATABASE haha;Query OK, 0 rows affected (0.00 sec)
12.DESCRIBE table_name; 查看表结构
MariaDB [wuwu]> DESCRIBE happy;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| name | varchar(20) | YES | | NULL | || age | char(10) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.01 sec)
13.ALTER TABLE table_name add sex CHAR(1)##添加字段age
MariaDB [wuwu]> ALTER TABLE happy add sex CHAR(1);Query OK, 0 rows affected (0.46 sec) Records: 0 Duplicates: 0 Warnings: 0
还可以不进入数据库就可以对他进行操作:
#mysql -uroot -predhat -e "DROP DATABASES westos"
#mysql -uroot -predhat -e "SHOW DATABASES"
################# 基本操作整理 ################
创建:
create database westos;
use westos;
create table linux (
username varchar(50) not null,
password varchar(50) not null,
age varchar(4) );
insert into linux values ( 'haoge' , '1234' , '21' );
查询:
show databases;
use mysql;
show tables;
desc user;
select * from user;
select * from user where HOST= ' 127.0.0.1 ' ;
修改:
alter table linux rename hahaha;
alter table linux add class varchar(50);
alter table linux add class varchar(50) after password
alter table linux drop class;
update linux set class= ' linux ' ;
update linux set class= ' web ' where username= ' hao ' ;
删除:
delete from linux where username= ‘ hao ’ and class= ‘ web ’ ;
drop table linux;
drop database westos;
1.创建用户
MariaDB [(none)]> CREATE USER hao@localhost identified by 'redhat';
[root@server101]# mysql -uhao -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
2.用户授权
MariaDB [(none)]> SHOW GRANTS FOR hao@localhost; ##查看用户权限
MariaDB [(none)]> GRANT SELECT on *.* to hao@localhost; ##给hao授权,root用户才可以
3.重载授权表
MariaDB [(none)]>FLUSH PRIVILEGES;
4.撤销用户权限
MariaDB [(none)]>REVOKE DELETE,UPDATE,INSERT ON *.* from hao@localhost
五、数据库的备份与恢复
1.备份数据库:
[root@server101]# mysqldump -uroot -predhat --all-database ##备份数据库里的所有内容
[root@server101]# mysqldump -uroot -predhat --no-data --all-database ##仅仅备份数据库结构
[root@server101]# mysqldump -uroot -predhat mysql > /mnt/mysql.sql ##备份某一个库到一个指定目录下
2.恢复数据库:
# mysqladmin -uroot -predhat create db2# mysql -uroot -predhat db2 < westos.dump
[root@server101]# mysqladmin -uroot -predhat create wuwu ##恢复数据库
[root@server101]# mysqladmin -uroot -predhat password lee ##修改用户密码
用户密码忘记怎么办?
1.[root@server101]# systemctl stop mariadb.service
2.[root@server101]# mysqld_safe --skip-grants-tables &
进入数据库更新密码,更新完之后 kill -9 数据库进程
然后重启数据库,就可以使用你新设定的密码进入了。
六. 数据库图形管理工具