mysql和分支mariadb

最新推荐文章于 2024-09-09 19:06:39 发布

sarauo

最新推荐文章于 2024-09-09 19:06:39 发布

阅读量486

点赞数

文章标签： mysql 数据库 maria db

本文链接：https://blog.csdn.net/sarauo/article/details/77124475

版权

mariadb简介

这里写图片描述
安装并且测试mariadb

匿名登陆mysql

这里写图片描述

为了数据库的安全起几见，我们应该给数据库加入密码。
这里写图片描述

默认回车，除过输入自己的密码以及新密码

再次登陆时需要密码，
这里写图片描述
为了数据库的安全防止远程登陆，所以需要修改 vim /etc/my.cnf下的配置文件。

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
 Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
Settings user and group are ignored when systemd is used.
 If you need to run mysqld under a different user or group,
 customize your systemd unit file for mariadb according to the
 instructions in http://fedoraproject.org/wiki/Systemd
skip-networking=1   **添加此命令更改配置文件。**
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
 include all files from the config directory
#
!includedir /etc/my.cnf.d

数据库中的查看命令

数据库中用show databases;来查看库的种类，如下只有一个mysql库

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql  **库**            |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> use mysql;切换到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]> show tables;查看mysql库中文件的种类
+---------------------------+
| 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)

MariaDB [mysql]> desc user;查看user表的数据结构
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |

MariaDB [mysql]> select * from user;查询mysql库下的user表中的
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host      | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |

MariaDB [mysql]> select * from user where Host='127.0.0.1';**查询mysql库下的地址为127.0.0.1的user</span></pre>**  
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host      | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| 127.0.0.1 | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------

数据库中的创建命令

MariaDB [(none)]> create database westos;**创建一个名字为westos的库**
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use westos;切换到westos库中，什么都没有
Database changed
MariaDB [westos]> create table linux ( 在库中建立一个linux表格
    -> username varchar(50) not null, 表头username自定义
    -> password varchar(50) not null, password自定义
    -> age varchar(4) );  
Query OK, 0 rows affected (0.16 sec)

MariaDB [westos]> show tables; **查看建立出来的表linux**
+------------------+
| Tables_in_westos |
+------------------+
| linux            |
+------------------+
1 row in set (0.00 sec)

MariaDB [westos]> insert into linux values ('sa','123','20'); **在表中写入对应的信息**
Query OK, 1 row affected (0.03 sec)

MariaDB [westos]> select * from user;
ERROR 1146 (42S02): Table 'westos.user' doesn't exist
MariaDB [westos]> select * from linux;**查看表中的内容**
+----------+----------+------+
| username | password | age  |
+----------+----------+------+
| sa       | 123      | 20   |
+----------+----------+------+
1 row in set (0.00 sec)

数据库中内容修改

MariaDB [westos]> alter table linux rename message;修改表的名字为message，linux是原名。
Query OK, 0 rows affected (0.05 sec)

MariaDB [westos]> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| message          |修改成功
+------------------+
1 row in set (0.00 sec)

在linux表中添加一个class栏，为50长度

MariaDB [westos]> alter table linux add class varchar(50);
Query OK, 1 row affected (0.12 sec)                
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+----------+------+-------+
| username | password | age  | class |
+----------+----------+------+-------+
| sa       | 123      | 20   | NULL  |
+----------+----------+------+-------+
1 row in set (0.00 sec)

将class栏放在password栏之后

MariaDB [westos]> alter table linux add class varchar(50) after password;
Query OK, 1 row affected (0.22 sec)                
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+----------+-------+------+
| username | password | class | age  |
+----------+----------+-------+------+
| sa       | 123      | NULL  | 20   |
+----------+----------+-------+------+
1 row in set (0.00 sec)

添加一个新的用户信息

MariaDB [westos]> insert into linux values ('zhuo','456',' ','19');
Query OK, 1 row affected (1.78 sec)

MariaDB [westos]> select * from linux;
+----------+----------+-------+------+
| username | password | class | age  |
+----------+----------+-------+------+
| sa       | 123      | NULL  | 20   |
| zhuo     | 456      |       | 19   |
+----------+----------+-------+------+
2 rows in set (0.00 sec)

更改class栏的用户所属信息全部为linux

MariaDB [westos]> update linux set class='linux';
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

将用户zhuo的从class更改为java

MariaDB [westos]> update linux set class='java' where username='zhuo'
    -> 
    -> update linux set class='java' where username='zhuo';
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 'update linux set class='java' where username='zhuo'' at line 3
MariaDB [westos]> update linux set class='java' where username='zhuo';
Query OK, 1 row affected (0.37 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+----------+-------+------+
| username | password | class | age  |
+----------+----------+-------+------+
| sa       | 123      | linux | 20   |
| zhuo     | 456      | java  | 19   |
+----------+----------+-------+------+

数据库中数据的备份

[root@localhost ~]# mysqldump -uroot -pwestos westos > /mnt/westos.sql **将数据备份到mnt下的sql中**
[root@localhost ~]# cd /mnt/
[root@localhost mnt]# ls
westos.sql
[root@localhost mnt]# cat westos.sql 
MySQL dump 10.14  Distrib 5.5.44-MariaDB, for Linux (x86_64)

-- Host: localhost    Database: westos
-- ------------------------------------------------------
-- Server version   5.5.44-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

删除westos数据库

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 -A

Database changed
MariaDB [westos]> drop westos;
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 'westos' at line 1
MariaDB [westos]> drop database westos;删除westos数据库
Query OK, 1 row affected (0.03 sec)

MariaDB [(none)]> use westos;**已经删除所以没有westos这个库**
ERROR 1049 (42000): Unknown database 'westos'
MariaDB [(none)]> quit

将备份好的数据库导入到mysql中
这里写图片描述
查看westos库中的信息和完整度

[root@localhost ~]# mysql -uroot -p
Enter password: 
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)]> use westos;
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 [westos]> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| linux            |
+------------------+
1 row in set (0.00 sec)

数据库中信息的删除

删除linux表格中的用户信息

MariaDB [westos]> delete from linux where username='zhuo' and class='java';
Query OK, 1 row affected (0.36 sec)

MariaDB [westos]> select * from linux;
+----------+----------+-------+------+
| username | password | class | age  |
+----------+----------+-------+------+
| sa       | 123      | linux | 20   |
+----------+----------+-------+------+

删除linux表格

MariaDB [westos]> drop table linux;
Query OK, 0 rows affected (0.04 sec)
MariaDB [westos]> select * from linux;
ERROR 1146 (42S02): Table 'westos.linux' doesn't exist

删除库westos

MariaDB [westos]> drop database westos;
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)

数据库密码的更改（丢失密码）

先停止mariadb服务，执行命令来更改密码。

[root@localhost ~]# systemctl stop mariadb.service 
[root@localhost ~]# mysqld_safe --skip-grant-tables &

这里写图片描述
执行命令更改密码

MariaDB [(none)]> UPDATE mysql.user set Password=password ('sazhuo') WHERE USER='root'; Query OK, 3 rows affected (0.00 sec) Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [(none)]> quit Bye

关掉与mysql相关的进程

[root@localhost ~]# ps aux | grep mysql

这里写图片描述

重新启动mariadb服务验证密码

[root@localhost ~]# systemctl restart mariadb.service
[root@localhost ~]# mysql -uroot -p
Enter password:

这里写图片描述

数据库中用户访问授权

MariaDB [(none)]> create user sz@localhost identified by '123'; ##新建用户
MariaDB [(none)]> show grants for sz@localhost;  ##查看用户信息；
MariaDB [(none)]> grant select on linux.* to sz@localhost; ##给sz用户可以查看linux库的权限。
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant delete on linux.* to sz@localhost;  #给sz用户删除表格内容权限
MariaDB [(none)]> grant drop on linux.* to sz@localhost;     ##给用户szdrop权限
Query OK, 0 rows affected (0.00 sec)               
MariaDB [(none)]> flush privileges;                         ##重载授权表
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> revoke delete on linux.* from zpy@localhost  ##撤销用户权限
MariaDB [(none)]> drop user sz@localhost;               ##删除用户sz
Query OK, 0 rows affected (0.00 sec)

利用网页图形界面建立自己的数据库

1.先在网上下一个phpMyAdmin-3.4.0-all-languages.tar.bz2这个软件包
2.再在yum安装PHP php-mysql http 这3款软件
3.在/var/www/html下解压缩phpMyAdmin
4.在phpMyAdmin-3.4.0-all-languages这个目录中的config.sample.inc.PHP下找个
$cfg['blowfish_secret'] = ‘随便填个值’ 并将config.sample.inc.php    改名为config.inc.php并且关闭火墙