数据库MARIADB，
mariadb 简介
MariaDB 数据库管理系统是 MySQL 的一个分支，主要由开源社区在维护，采用 GPL 授权许可 MariaDB 的目的是完全兼容MySQL ，包括 API 和命令行，是 MySQL 的代替品
MariaDB的特性：插件式存储引擎，存储管理器有多种实现版本，彼此间的功能和特性可能略有不同，
Mariadb 数据库的安装
1.安装软件包
[root@shareserver westos]# yum install mariadb-server -y
2.开启mariadb
systemctl start mariadb
注意: 数据库的起始状态时不安全的，所以要进行初始化安全设置
3.数据库的安全初始化设置
[root@shareserver westos]# mysql_secure_installation
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n]
New password: ##设置登陆密码
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] ##移除匿名用户
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] ##不允许超级用户远程登录
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] ##移除测试用户
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] ##刷新数据库
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
4.关闭mysql开放的接口
[root@shareserver westos]# netstat -antlupe |grep mysql 查看数据库端口，校验mariadb的监听端口
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 76440 4564/mysqld
编辑/etc/my.cnf文件
[root@shareserver westos]# vim /etc/my.cnf
10 skip-networking=1 ##默认情况下，数据库的接口时打开的，这个语句关闭接口
5，重启mariadb，并登录数据库
[root@shareserver westos]# systemctl restart mariadb
[root@shareserver westos]# netstat -antlupe |grep mysql
[root@shareserver westos]# mysql -uroot -p ##用户身份登陆
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> quit
Bye
数据库管理
建立
1.显示出数据库
MariaDB [westos]> SHOW DATADASE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DATADASE' at line 1
MariaDB [westos]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
2.进入数据库
MariaDB [mysql]> USE mysql;
Database changed
3.显示表
MariaDB [mysql]> SHOW TABLES;
4.查看某个表中的内容
MariaDB [mysql]> SELECT Host,Password FROM user;
5.新建数据库，并显示数据库
MariaDB [mysql]> CREATE DATABASE westos;
Query OK, 1 row affected (0.00 sec)
6.进入数据库，建立表
注意：建立表时，表不能为空，表中至少有一个元素
MariaDB [westos]> CREATE TABLE linux(
-> username varchar(10) not null,
-> password varchar(10) not null
-> );
Query OK, 0 rows affected (0.01 sec)
7.查看表格的结构
MariaDB [westos]> DESC linux;
8.在表中插入内容
MariaDB [westos]> INSERT INTO linux VALUES('red','123');
Query OK, 1 row affected (0.01 sec)
MariaDB [westos]> INSERT INTO linux VALUES('yello','123');
Query OK, 1 row affected (0.00 sec)
查看表中所添加的内容
更改
注意：
ALTER表结构的更改
UPDATE 表数据的更改
1.更改表的名字
MariaDB [westos]> ALTER TABLE userdata RENAME linux; ##更改表的名字
Query OK, 0 rows affected (0.34 sec)
2.添加表的列
MariaDB [westos]> ALTER TABLE linux ADD age varchar(4); ##添加表的列
Query OK, 2 rows affected (0.45 sec)
Records: 2 Duplicates: 0 Warnings: 0
3.更新表中的数据
MariaDB [westos]> UPDATE linux SET age='18' WHERE username='westos';
Query OK, 1 row affected (0.34 sec)
Rows matched: 1 Changed: 1 Warnings: 0
SET ##设置
WHERE ##条件
删除
注意：
DELETE 表中数据的删除
DROP 删除表和数据库
1.删除表中的元素
MariaDB [westos]> DELETE FROM userdata WHERE username='red';
Query OK, 1 row affected (0.01 sec)
2.删除表中的列
MariaDB [westos]> ALTER TABLE userdata DROP age;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
3.删除表
MariaDB [westos]> DROP TABLE linux;
Query OK, 0 rows affected (0.35 sec)
用户授权
1.创建用户
MariaDB [(none)]> CREATE USER lee@localhost identified by ‘123’;
Query OK, 0 rows affected (0.01 sec)
2.给用户授权
GRANT SELECT,INSERT,DROP,DELETE,CREATE ON westos.* TO lee@localhost; ##授权
SHOW GRANTS FOR lee@localhost ##显示授权
3.撤销用户权限
REVOKE DELETE,UPDATE,INSERT on mariadb.* from wxh@localhost;
备份数据库
将数据库备份到文件中
mysqldump -uroot -plee --all-databases > /mnt/alldata.sql ##备份全部数据到/mnt/allgata.sql文件
mysqldump -uroot -plee --all-databases --no-data > /mnt/nodata.sql ##备份数据库结构
mysqldump -uroot -plee westos > /mnt/westos.sql ##备份数据库westos的内容
实验：如何恢复数据库？？？？
为了模拟实验，我们首先删除数据库
mysql -uroot -plee -e "DROP DATABASE westos;"
mysql -uroot -plee -e "SHOW DATABASES;"
恢复数据库有两种方式
第一种方式：执行命令新建数据库，并把备份的文件内容导入
mysql -uroot -plee -e "CREATE DATABASE westos;"
mysql -uroot -plee westos < /mnt/westsos.sql
第二种方式：编辑 /mnt/westos.sql文件，在文件中添加westos的信息
26 CREATE DATABASE westos;
27 USE westos;
然后将备份的文件导入
mysql -uroot -plee < /mnt/westos.sql
mysql -uroot -plee -e "SHOW DATABASES;"
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。