运维笔记20 （mariaDB的基础语句）

分手后的思念是犯贱 2022-07-14 05:52 240阅读 0赞

概述：LAMP架构盛极一时，这离不开MySQL的免费与易用，但是在Oracle收购了Sun之后，很多公司开始担忧MySQL的开源前景，而最近Oracle进一步闭源的举措更是让人难以安心，众多互联网公司纷纷开始寻求MySQL的替代方案。mariaDB原是mysql的一个分支，在mysql创始人加入后，更加吸引了大众的目光。

1.安装mariaDB

我们一直使用的RHEL7的自带yum源已经有了mariadb的rpm包，我们可以使用yum非常方便的安装。

[root@localhost dhcp]# yum install mariadb-server -y

[root@localhost dhcp]# yum list all | grep mariadb
     mariadb-server.x86_64                    1:5.5.35-3.el7                rhel_dvd
    
     
     
     
    但是yum提供的mariadb的版本实在是有点低，我们去看下mariadb官网。
     
     
     官网上已经出了10.2.2的beta版本，不过人家可提示你了，不要在生产环境使用beta版。这篇博客的主要目的是对常用语句的总结，就先不尝试这个的源码安装了。
     2.使用mariaDB
     
     [root@localhost dhcp]# systemctl start mariadb打开mariadb服务
     
     
     
     skip-networking=1修改mariadb配置文件使其跳过网络使用
     
     
     [root@localhost dhcp]# 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): 
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
    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] y
    New password: 
    进入数据库
    
     [root@localhost dhcp]# mysql -uroot -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 11
    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)]> 
     显示数据库 MariaDB [(none)]> show databases
        -> ;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    +--------------------+
    3 rows in set (0.00 sec)
    创建数据库
     
     
     MariaDB [(none)]> create database mo;
    
    删除数据库
     
     
     MariaDB [(none)]> drop database mo;
    
    使用数据库
     
     
     MariaDB [(none)]> use mo
    
    创建表
     
     
     MariaDB [mo]> create table user ( username char(30) not null,password char(30) not null);
    
    显示存在的表
     
     
     MariaDB [mo]> show tables;
    +--------------+
    | Tables_in_mo |
    +--------------+
    | user         |
    +--------------+
    1 row in set (0.00 sec)
    显示某一个表的细节
     
     
     MariaDB [mo]> desc user;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | username | char(30) | NO   |     | NULL    |       |
    | password | char(30) | NO   |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    向表中插入数据
     
     
     MariaDB [mo]> insert into user values("mo","123"); 查找表中的数据 MariaDB [mo]> select * from user;
    +----------+----------+
    | username | password |
    +----------+----------+
    | mo       | 123      |
    +----------+----------+
    1 row in set (0.00 sec)修改表
     
     1）给表添加一个新的列 
     MariaDB [mo]> alter table user add age char(5);
    Query OK, 1 row affected (0.38 sec)                
    Records: 1  Duplicates: 0  Warnings: 0
    
    MariaDB [mo]> select * from user;
    +----------+----------+------+
    | username | password | age  |
    +----------+----------+------+
    | mo       | 123      | NULL |
    +----------+----------+------+
    1 row in set (0.00 sec)
    2）删除表的一列
     
     
     MariaDB [mo]> alter table user drop age;
    Query OK, 1 row affected (0.13 sec)                
    Records: 1  Duplicates: 0  Warnings: 0
    
    MariaDB [mo]> select * from user;
    +----------+----------+
    | username | password |
    +----------+----------+
    | mo       | 123      |
    +----------+----------+
    1 row in set (0.00 sec)
    3）在一个任意位置添加表的某一列
    
     MariaDB [mo]> alter table user add age char(5) after username;
    Query OK, 1 row affected (0.13 sec)                
    Records: 1  Duplicates: 0  Warnings: 0
    
    MariaDB [mo]> select * from user;
    +----------+------+----------+
    | username | age  | password |
    +----------+------+----------+
    | mo       | NULL | 123      |
    +----------+------+----------+
    1 row in set (0.00 sec)
     更新数据库的某个数据 MariaDB [mo]> update user set age=15;
    Query OK, 1 row affected (0.31 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    MariaDB [mo]> select * from user;
    +----------+------+----------+
    | username | age  | password |
    +----------+------+----------+
    | mo       | 15   | 123      |
    +----------+------+----------+
    1 row in set (0.00 sec)
    
    
    查询表的数据：
     
     
     MariaDB [mo]> select * from user;
    +----------+------+----------+
    | username | age  | password |
    +----------+------+----------+
    | mo       | 15   | 123      |
    | li       | 21   | 456      |
    +----------+------+----------+
    2 rows in set (0.00 sec)
    这条是查询某个表的所有数据，但是现在如果我想仅查找某个数据怎么办呢？观察这个表，从几何的意义考虑如果想锁定某一个数据，需要有这个数据的横纵坐标都决定才可以
    
    
    
     MariaDB [mo]> select password from user where username="li";
    +----------+
    | password |
    +----------+
    | 456      |
    +----------+
    1 row in set (0.00 sec)想查"li"的密码，上面的语句首先用select password决定了他的纵坐标，接下来用username="li"决定了横坐标，就查找到了想要的数据。
    
    
    
    删除表的某一行
     
     
     MariaDB [mo]> delete from user where username="li";
    Query OK, 1 row affected (0.07 sec)
    
    MariaDB [mo]> select * from user;
    +----------+------+----------+
    | username | age  | password |
    +----------+------+----------+
    | mo       | 15   | 123      |
    +----------+------+----------+
    1 row in set (0.00 sec)
    
    
    删除表
     
     
     MariaDB [mo]> drop table user;
    Query OK, 0 rows affected (0.07 sec)
    
    MariaDB [mo]> show tables;
    Empty set (0.00 sec)
    
    
    删库
     
     
     MariaDB [mo]> drop database mo;
    Query OK, 0 rows affected (0.01 sec)
    
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    +--------------------+
    3 rows in set (0.00 sec)
    
    
    创建数据库的用户
     
     
     MariaDB [mysql]> create user mo
        -> ;
    Query OK, 0 rows affected (0.00 sec)
    
    
    通过查询表单知道这个新建的用户没有任何权限。
     
     
     给新建用户赋予密码
     
     MariaDB [(none)]> create user mo@localhost identified by "redhat";
    
    给新建用户赋予权限
     
     
     MariaDB [(none)]> create database user;
    ERROR 1044 (42000): Access denied for user 'mo'@'localhost' to database 'user'
    新建用户尚无创建的权限
     
     既然是授权，当然只能让root用户授权了。
     
     MariaDB [(none)]> grant create on *.* to mo@localhost;
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [(none)]> show grants;
    +----------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for root@localhost                                                                                                              |
    +----------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' WITH GRANT OPTION |
    | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
    +----------------------------------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    
    这样就给mo用户授予了create权限。
     
     
     MariaDB [(none)]> revoke create on *.* from mo@localhost;
    Query OK, 0 rows affected (0.00 sec)
    
    
    去掉授予的权限。
     
     
     加入数据库的root密码忘记了怎么办。
     systemctl stop mariadb
     mysqld_safe --skip-grant-tables &
     之后就可以进入mysql不需要密码了。
     进入之后修改mysql的密码
     
     MariaDB [(none)]> update mysql.user set password=password("redhat") where user="root";
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 3  Changed: 0  Warnings: 0
    
    
    这样即可修改密码，而且密码是加密的
     
     退出mysql。一定要杀掉所有mysql的进程。再重启服务。 
     [root@localhost ~]# ps aux | grep mysql
    root      7765  0.0  0.0 113248  1556 pts/1    S    07:42   0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
    mysql     7920  0.1  4.7 859068 89572 pts/1    Sl   07:42   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      8031  0.0  0.0 112640   936 pts/1    R+   07:46   0:00 grep --color=auto mysql
    之后就能使用修改的密码登录了。
    
    
    
    有一个软件是用php写的mysql图形化软件，搜索phpmyadmin就可以下载使用。由于yum没有提供rpm包所以我们要自己编译源码包。
     
     
     [root@foundation3 mnt]# ls
    httpd.key  phpMyAdmin-4.6.5.1-all-languages  phpMyAdmin-4.6.5.1-all-languages.tar.bz2 红字处就是phpMyadmin软件的tar包，我们打开看看。
     
     
     [root@foundation3 phpMyAdmin-4.6.5.1-all-languages]# ls
    browse_foreigners.php    LICENSE                      show_config_errors.php
    build.xml                license.php                  sql
    ChangeLog                lint.php                     sql.php
    changelog.php            locale                       tbl_addfield.php
    chk_rel.php              logout.php                   tbl_change.php
    composer.json            navigation.php               tbl_chart.php
    config.sample.inc.php    normalization.php            tbl_create.php
    CONTRIBUTING.md          phpmyadmin.css.php           tbl_export.php
    db_central_columns.php   phpunit.xml.dist             tbl_find_replace.php
    db_datadict.php          phpunit.xml.hhvm             tbl_get_field.php
    db_designer.php          phpunit.xml.nocoverage       tbl_gis_visualization.php
    db_events.php            prefs_forms.php              tbl_import.php
    db_export.php            prefs_manage.php             tbl_indexes.php
    db_import.php            print.css                    tbl_operations.php
    db_operations.php        README                 对于这种源码安装首先要看一下他的README。
     
     
     phpMyAdmin is intended to handle the administration of MySQL over the web.
    For a summary of features, list of requirements, and installation instructions,
    please see the documentation in the ./doc/ folder or at https://docs.phpmyadmin.net/
    
    
    根据README的这句话，我们看下https://docs.phpmyadmin.net/这个链接。
     
     根据配置文件，我们知道，安装这个还需要一个webserver 接下来再安装诸如php-mysql，php这样的环境就可以了。