mariadb，

MariaDB数据库的安装与配置

一、安装

yum install mariadb-server -y ##安装
systemctl start mariadb ##开启服务

二、基本配置

一）安全初始化

1、默认情况下，数据库端口是打开的，其他主机可以通过打开的端口来进行访问，所以先将端口关闭

[root@station mysql]# netstat -antlpe | grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      27         39838      3913/mysqld
[root@station mysql]# vim /etc/my.cnf
skip-networking=1         ##添加这一行，关闭网络端口
[root@station mysql]# systemctl restart mariadb
[root@station mysql]# netstat -antlpe | grep mysql    ##此时不会有任何返回值，端口已经关闭

2、数据库的设定初始状态是不安全的，所以需要设定密码

mysQl_secure_installation ##这条命令为数据库设置密码

二）密码设置

1、更改密码

mysqladmin -uroot -plcl970618 password lcl #把原来的密码修改为lcl

2、当超级用户密码忘记，不能登陆数据库时

[root@station ~]# systemctl stop mariadb    ##先关掉数据库
[root@station ~]# mysqld_safe --skip-grant-tables &   ##跳过数据库密码验证阶段
[1] 4815
[root@station ~]# 171123 05:43:05 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
171123 05:43:05 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@station ~]# mysql   ##此时进入数据库不需要密码
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.44-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> UPDATE mysql.user SET Password=password('0618') WHERE User='root';  ##更新User为root的密码为0618
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [(none)]> quit;
Bye
[root@station ~]# ps aux|grep mysql   ##列出数据库的所有进程
root      4815  0.0  0.1 113252  1620 pts/0    S    05:43   0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql     4970  0.0  8.2 843504 84256 pts/0    Sl   05:43   0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root      5012  0.0  0.0 112644   956 pts/0    R+   05:44   0:00 grep --color=auto mysql
[root@station ~]# kill -9 4815    ##关闭数据库的所有进程
[root@station ~]# kill -9 4970
[1]+  Killed                  mysqld_safe --skip-grant-tables
[root@station ~]# systemctl start mariadb   ##开启数据库
[root@station ~]# 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.44-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>     ##成功进入

三）数据库的管理

mysql交互模式下的数据库、表都在/var/lib/mysql/目录里面，其中里面的目录是库，库目录里面的文件是表的名称。

1、建立、查询

[root@station ~]# 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.44-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW DATABASES;   ##列出当前已有的库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> CREATE DATABASE lcl;  ##创建名为lcl的库
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE lcl;
Database changed
MariaDB [lcl]> CREATE TABLE user (      ##创建名为user的表
    -> username varchar(50) not null,   ##创建字段username，字符长最大为50，不能为空
    -> password varchar(50) not null  ##创建password字段
    -> );
Query OK, 0 rows affected (0.07 sec)

MariaDB [lcl]> DESC user;     ##列出表结构
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(50) | NO   |     | NULL    |       |
| password | varchar(50) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [lcl]> INSERT INTO user VALUES ('alice','123');   ##插入数据
Query OK, 1 row affected (0.03 sec)

MariaDB [lcl]> SELECT * FROM user;  ##列出user表中的所有数据
+----------+----------+
| username | password |
+----------+----------+
| alice    | 123      |
+----------+----------+
1 row in set (0.00 sec)

MariaDB [lcl]> SELECT username FROM user    ##列出user表中的username字段的数据
    -> ;
+----------+
| username |
+----------+
| alice    |
+----------+
1 row in set (0.00 sec)

2、更改

MariaDB [lcl]> UPDATE user SET password=password('alice') WHERE username='alice';   ##将alice的密码改为加密后的‘alice’，等号后面的password表示加密
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [lcl]> SELECT * FROM user;
+----------+-------------------------------------------+
| username | password                                  |
+----------+-------------------------------------------+
| alice    | *4F5CCA657BD61D1C1127E5C4EA3B0EE4A9841B85 |
+----------+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [lcl]> ALTER TABLE user ADD class varchar(20);  ##添加class字段，最大字符为20，若没有指定位置，则默认会添加在表的字段末尾
Query OK, 1 row affected (0.41 sec)                
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [lcl]> SELECT * FROM user;
+----------+-------------------------------------------+-------+
| username | password                                  | class |
+----------+-------------------------------------------+-------+
| alice    | *4F5CCA657BD61D1C1127E5C4EA3B0EE4A9841B85 | NULL  |
+----------+-------------------------------------------+-------+
1 row in set (0.00 sec)

MariaDB [lcl]> ALTER TABLE user ADD age varchar(10) AFTER username; ##指定将age字段添加在username字段后面，默认只能添加在某个字段之后，而不能用before
Query OK, 1 row affected (0.16 sec)                
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [lcl]> SELECT * FROM user;
+----------+------+-------------------------------------------+-------+
| username | age  | password                                  | class |
+----------+------+-------------------------------------------+-------+
| alice    | NULL | *4F5CCA657BD61D1C1127E5C4EA3B0EE4A9841B85 | NULL  |
+----------+------+-------------------------------------------+-------+
1 row in set (0.00 sec)

MariaDB [lcl]> UPDATE user SET password='123' WHERE username='alice';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [lcl]> SELECT * FROM user;    ##密码没有经过加密
+----------+------+----------+-------+
| username | age  | password | class |
+----------+------+----------+-------+
| alice    | NULL | 123      | NULL  |
+----------+------+----------+-------+
1 row in set (0.00 sec)

MariaDB [lcl]> ALTER TABLE user RENAME redhat;  ##更改表的名字为redhat
Query OK, 0 rows affected (0.04 sec)

MariaDB [lcl]> SELECT * FROM redhat;
+----------+------+----------+-------+
| username | age  | password | class |
+----------+------+----------+-------+
| alice    | NULL | 123      | NULL  |
+----------+------+----------+-------+
1 row in set (0.00 sec)

3、删除

MariaDB [lcl]> DELETE FROM redhat WHERE username='alice'; ##删除alice数据
Query OK, 1 row affected (0.02 sec)

MariaDB [lcl]> SELECT * FROM redhat;
Empty set (0.00 sec)

MariaDB [lcl]> DROP TABLE redhat;   ##删除表redhat
Query OK, 0 rows affected (0.03 sec)

MariaDB [lcl]> DROP DATABASE lcl;   ##删除库lcl
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW DATABASES;   ##可以看出已经删除
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

4、用户的授权

MariaDB [(none)]> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> CREATE user lcl@localhost identified by 'redhat';  ##创建lcl用户，并且设置密码为redhat,@localhost表示只能在本地登陆
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> GRANT SELECT,INSERT on lcl.* to lcl@localhost;   ##授予lcl用户SELECT,INSERT权限
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> SHOW GRANTS for lcl@localhost; ##列出lcl用户的权限，和设置的相符合
+------------------------------------------------------------------------------------------------------------+
| Grants for lcl@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lcl'@'localhost' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
| GRANT SELECT, INSERT ON `lcl`.* TO 'lcl'@'localhost'                                                       |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [mysql]> REVOKE INSERT on lcl.* from lcl@localhost; ##去掉lcl用户的insert权限
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> SHOW GRANTS for lcl@localhost; ##lcl用户此时只剩下SELECT权限
+------------------------------------------------------------------------------------------------------------+
| Grants for lcl@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lcl'@'localhost' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
| GRANT SELECT ON `lcl`.* TO 'lcl'@'localhost'                                                               |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

5、数据库的备份与恢复

备份
[root@station ~]# mysqldump -uroot -p0618 lcl > /mnt/redhat.sql #将lcl库备份到/mnt/redhat.sql文件中
mysqldump -uroot -plcl redhat –no-data ##将redhat库的框架进行备份，不备份数据
mysqldump -uroot -plcl –all-database ##将所有数据库的内容和框架进行备份
mysqldump -uroot -plcl –all-database –no-data ##将所有数据库的框架进行备份，不备份数据
恢复

方法一：
[root@station ~]# mysql -uroot -p0618 -e "CREATE DATABASE redhat;"  ##进入数据库并创建redhat库
[root@station ~]# mysql -uroot -p0618 redhat < /mnt/redhat.sql  ##从/mnt/redhat.sql文件中将文件内容导入redhat库中
[root@station ~]# mysql -uroot -p0618
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.44-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lcl                |
| mysql              |
| performance_schema |
| redhat             |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> USE redhat;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [redhat]> SHOW TABLES;
+------------------+
| Tables_in_redhat |
+------------------+
| redhat           |
+------------------+
1 row in set (0.00 sec)

MariaDB [redhat]> SELECT * FROM redhat;
+----------+----------+
| username | password |
+----------+----------+
| alice    | 123      |
+----------+----------+
1 row in set (0.00 sec)

此时恢复成功

方法二：
MariaDB [redhat]> DROP TABLE redhat;    ##先将表删除
Query OK, 0 rows affected (0.04 sec)

MariaDB [redhat]> DROP DATABASE redhat; ##删除库
Query OK, 0 rows affected (0.00 sec)

[root@station ~]# vim /mnt/redhat.sql   ##修改备份文件
 21 CREATE DATABASE redhat;             ##创建库
 22 USE redhat;                         ##使用库
 23 DROP TABLE IF EXISTS `redhat`;      ##这行本身就有，是想说明在哪里添加
[root@station ~]# mysql -uroot -p0618 < /mnt/redhat.sql
##此时进入数据库查看就会发现恢复成功

四）phpmyadmin数据库图形化管理

测试