数据库简介
数据库由库组成，库由表组成
数据库种类：sqlserver mysql(sun--oracle) db2
MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语：XtraDB）来代替MySQL的InnoDB。 MariaDB由MySQL的创始人Michael Widenius（英语：Michael Widenius）主导开发，他早前曾以10亿美元的价格，将自己创建的公司MySQL AB卖给了SUN，此后，随着SUN被甲骨文收购，MySQL的所有权也落入Oracle的手中。MariaDB名称来自Michael Widenius的女儿Maria的名字。
mariadb 安装
[root@localhost ~]# yum install mariadb-server -y #安装mariadb [root@localhost ~]# systemctl start mariadb #开启mariadb服务
[root@localhost ~]# netstat -antlpe |grep mysql #查看端口状态
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 83161 2708/mysqld
mysql初始化
1.关闭对外开放接口
[root@localhost ~]# vim /etc/my.cnf
skip-networking=1 #数据库在网络上开启的接口跳过
[root@localhost ~]# systemctl stop firewalld #关闭火墙
[root@localhost ~]# netstat -antlpe |grep mysql #网络接口已关闭
2.数据库安全初始化
测试：[root@localhost ~]# mysql
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)]> Bye
操作：
[root@localhost ~]# 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!
（除了密码，其余全部回车）
测试：
[root@localhost ~]# mysql -uroot -p #也可以写“-p密码”直接登录，但是不安全，不建议使用
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
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)]> Bye
查询
查询语言大小写字母都可以使用，企业中为了规范需要使用大写
列在数据库里列叫字段SHOW DATABASES; #显示当前库的名字，相当于查看目录下的子目录的名字
USE database_name; #进入数据库，相当于进入目录的子目录里面
SHOW TABLES; #查看库里面有哪些表，相当于查看子目录里面有哪些文件
SELECT * FROM table_name; #查询所有内容从哪个库里面查
DESC table_name; #查询表结构
[root@localhost ~]# mysql -uroot -p #登陆
Enter password:Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 21
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)]> SHOW DATABASES; #显示数据库
MariaDB [(none)]> USE mysql; #进入mysql库
Database changed
MariaDB [mysql]> SHOW TABLES; #显示当前库中表的名称
24 rows in set (0.01 sec)
MariaDB [mysql]> SELECT * FROM user; #查询user表中的所有内容
3 rows in set (0.00 sec)
MariaDB [mysql]> SELECT User,Host,Password FROM user; #加上查询条件
MariaDB [mysql]> SELECT User,Host,Password,Select_priv FROM user;
MariaDB [mysql]> DESC user; #查询user表的结构（显示所有字段的名称）
MariaDB [mysql]> SELECT User,Host,Password,Select_priv FROM user Where User='root';
数据库及表的建立
CREATE DATABASE database_name; #创建数据库CREATE TABLE table_name (name VARCHAR(20),sex CHAR(1)); #创建表格
INSERT INTO table_name VALUES (XXX,’M’); #给表格写入name为XXX，sex为M的内容
[root@localhost ~]# mysql -uroot -p
Enter password:Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 22
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)]> SHOW DATABASES;
MariaDB [(none)]> CREATE DATABASE westos; #建立数据库westos库
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES; #查看数据库
MariaDB [(none)]> USE westos; #进入到westos这个目录，cd westos
Database changed
MariaDB [westos]> SHOW TABLES; # ls，查看westos目录里的内容
Empty set (0.00 sec)
MariaDB [westos]> CREATE TABLE linux( #创建linux表，表的内容包括用户和密码
-> username varchar(6) not null,
-> password varchar(50) not null);
Query OK, 0 rows affected (0.03 sec)
MariaDB [westos]> SHOW TABLES;
MariaDB [westos]> DESC linux;
MariaDB [westos]> INSERT INTO linux values ('wwy','123'); #添加linux表中的信息
Query OK, 1 row affected (0.01 sec)
MariaDB [westos]> INSERT INTO linux values ('jcl','456');
Query OK, 1 row affected (0.01 sec)
MariaDB [westos]> SELECT * FROM linux; #查看表的内容
数据库的修改
一般情况下库的名字不做修改
/var/lib/mysql/ #可以查看到数据库的名字，修改名字使用 mv
1、ALTER TABLE newtable_name RENAME oldtable_name; #修改表的名字
2、ALTER TABLE table_name ADD style varchar(5); #添加表的内容
3、ALTER TABLE table_name ADD style varchar(5) AFTER username; #指定添加位置添加表的内容
4、ALTER TABLE table_name DROP style; #指定删除表的字段内容
7、 UPDATE table_name SET password=’333’ WHERE username=’yi’; #修改表格的内容
8、DELETE FROM table_name WHERE username=’yi’; #指定删除表格的内容
9、DROP TABLE table_name; #删除表
10、DROP DATABASE database_name; #删除库
1.更改数据库名称
cd /var/lib/mysqlls
mv westos/hello
systemctl restart mariadb
数据库中刷新: flush privileges;
2.更改表的结构
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 23
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)]> SHOW DATABASES;
MariaDB [(none)]> USE westos;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
MariaDB [westos]> SHOW TABLES;
MariaDB [westos]> ALTER TABLE linux RENAME messages;
Query OK, 0 rows affected (0.01 sec)
MariaDB [westos]> SHOW TABLES;
MariaDB [westos]> ALTER TABLE messages RENAME linux;
Query OK, 0 rows affected (0.01 sec)
MariaDB [westos]> SHOW TABLES;
MariaDB [westos]> SELECT * FROM linux;
MariaDB [westos]> ALTER TABLE linux ADD age varchar(4); #插入age，4个字节（默认插入在最后一个字段）
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> SELECT * FROM linux;
MariaDB [westos]> ALTER TABLE linux DROP age; #删除age字段
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> SELECT * FROM linux;
MariaDB [westos]> ALTER TABLE linux ADD age varchar(4) AFTER username; #将age字段插入在username后
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> SELECT * FROM linux;
MariaDB [westos]> ALTER TABLE linux DROP age; #删除age字段
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
注意：最多只能插入在第二个字段，即第一个字段之后
3.更改表的数据
MariaDB [westos]> UPDATE linux SET username='xlp' WHERE password='123'; #将linux表中密码为123的用户名改为xlp
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [westos]> SELECT * FROM linux; #查看linux表的信息
MariaDB [westos]> DELETE from linux WHERE username='xlp'; #将username为xlp的字段删掉
Query OK, 1 row affected (0.01 sec)
MariaDB [westos]> SELECT * FROM linux;
MariaDB [westos]> DROP DATABASE westos;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> SHOW DATABASES;
数据库的网页管理工具
[root@localhost ~]# yum install http php -y #下载http、php服务器
[root@localhost ~]# systemctl start httpd #开启http服务
[root@localhost ~]# systemctl stop firewalld #关闭火墙
根据php、mariadb的版本，选择phpmyadmin版本
[root@localhost ~]# rpm -qa | grep php #查看php安装版本
[root@localhost ~]# cd /var/www/html/
[root@localhost html]# ls
phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@localhost html]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 #解压
[root@localhost html]# ls
phpMyAdmin-3.4.0-all-languages phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@localhost html]# rm -fr phpMyAdmin-3.4.0-all-languages.tar.bz2 #删除安装包
[root@localhost html]# ls
phpMyAdmin-3.4.0-all-languages
[root@localhost html]# mv phpMyAdmin-3.4.0-all-languages mysqladmin #修改文件名为mysqladmin
[root@localhost html]# ls
mysqladmin
[root@localhost html]# cd mysqladmin
[root@localhost mysqladmin]# ls
config.sample.inc.php
[root@localhost mysqladmin]# cp config.sample.inc.php config.inc.php
[root@localhost mysqladmin]# yum search php
Loaded plugins: langpacks
php-mysql.x86_64 : A module for PHP applications that use MySQL databases
[root@localhost mysqladmin]# yum install php-mysql.x86_64 -y #安装php支持的mysql软件
[root@localhost mysqladmin]# getenforce #该实验环境SElinux一定要在开启状态
浏览器测试：
172.25.254.102/mysqladmin/
可以在浏览器中对数据库进行操作！
用户授权
建立用户：CREATE USER user@localhost identified by ‘haha’;
CREATE USER user@’%’ identified by ‘haha’;
localhost：本地用户，%：任何客户端都可以登录，identified by 后加用户密码
用户授权：GRANT INSERT,UPDATE,DELETE,SELECT on haha.* to user@localhost;
GRANT SELECT on mariadb.* haha@’%’;
查看用户授权：
SHOW GRANTS FOR user@localhost;
重载授权表：
FLUSH PRIVILEGES;
撤销用户授权：
REVOKE UPDATE on haha.* from user@localhost;
删除用户：
DROP USER user@localhost;
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 53
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)]> CREATE USER lee@'localhost' identified by 'lee'; #建立用户lee，密码lee，此用户只能通过本机登陆
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> SELECT User FROM mysql.user; #显示所有用户
MariaDB [(none)]> GRANT SELECT on westos.* to lee@localhost; #用户授权，lee可以通过本机查看本机westos数据库所有
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GRANTS FOR lee@localhost; #查看用户授权
MariaDB [(none)]> GRANT UPDATE on westos.* to lee@localhost; #用户授权，用lee登陆后可以执行所有
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GRANTS FOR lee@localhost; #查看用户权限
MariaDB [(none)]> REVOKE UPDATE on westos.* from lee@localhost; #撤销lee用户权限
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GRANTS FOR lee@localhost; #查看用户授权
测试：在浏览器中用lee登陆无法在westos数据库进行任何操作
MariaDB [(none)]> DROP USER lee@localhost; #删除lee用户
Query OK, 0 rows affected (0.00 sec)
忘记数据库密码
[root@localhost ~]# systemctl stop mariadb.service #关闭数据库服务
[root@localhost ~]# mysqld_safe --skip-grant-tables & #开启mysql登陆接口并忽略授权表
[1] 2095
[root@localhost ~]# 180526 02:46:48 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
180526 02:46:48 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@localhost ~]# mysql #免密登陆
MariaDB [(none)]> select * from mysql.user; #查询user表中的所有内容
MariaDB [(none)]> update mysql.user set Password='123' where User='root'; #更新超级用户密码信息，设置用户密码为123
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
MariaDB [(none)]> select * from mysql.user; #看到是明文
MariaDB [(none)]> update mysql.user set Password=password('123') where User='root'; #将root的密码改为123，不显示
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
MariaDB [(none)]> select * from mysql.user;
MariaDB [(none)]> quit
[root@localhost ~]# fg #把后台程序调用到前台
mysqld_safe --skip-grant-tables
^Z
[1]+ Stopped mysqld_safe --skip-grant-tables
[root@localhost ~]# killall -9 mysqld_safe #清掉mysqld_safe所有进程
[1]+ Killed mysqld_safe --skip-grant-tables
[root@localhost ~]# ps axu | grep mysql #过滤mysql进程
[root@localhost ~]# kill -9 19420 #清除mysql所有进程
[root@localhost ~]# ps axu | grep mysql
root 19537 0.0 0.0 112640 932 pts/1 S+ 14:36 0:00 grep --color=auto mysql
[root@localhost ~]# mysql -uroot -p123
可以登录表示修改成功！
备份与恢复
备份
[root@localhost ~]# mysql -uroot -p123 -e "show databases;" #显示数据库信息
[root@localhost ~]# mysqldump -uroot -plee --all-database #备份所有数据库
[root@localhost ~]# mysqldump -uroot -plee --all-database --no-data #备份数据库框架，不备份数据
[root@localhost ~]# mysqldump -uroot -plee westos >/mnt/westos.sql #备份westos数据库，并导入/mnt下
[root@localhost ~]# ls /mnt
westos.sql
恢复方法一
vim /mnt/westos.sql #编辑备份过来的文件
文件内容：
USE westos;
mysql -uroot -plee < /mnt/westos.sql #将文件导入
恢复方法二
[root@localhost ~]# mysql -uroot -plee -e "drop database westos;" #做实验前先删除数据库
[root@localhost ~]# mysql -uroot -plee -e "show databases" #显示数据库信息
[root@localhost ~]# mysql -uroot -plee -e "CREATE DATABASE westos;" #创建数据库westos
[root@localhost ~]# mysql -uroot -plee westos < /mnt/westos.sql
[root@localhost ~]# mysql -uroot -plee -e "show databases" #显示数据库信息
+----------+----------+-------+----------+
| username | password | class | obj |
+----------+----------+-------+----------+
| wwy | 1234 | linux | database |
+----------+----------+-------+----------+
可以查看到数据库信息，恢复成功！