Linux运维 --- 使用MariaDB数据库管理系统

需要注意的是如果Linux系统里面自带了MySQL或者以前安装过MySQL服务的，需要完全卸载原有的MySQL数据库再重新安装。否则安装MariaDB会报错！

[root@localhost ~]# rpm -qa | grep -i mysql mysql57-community-release-el7-8.noarch mysql-community-server-5.7.28-1.el7.x86_64 mysql-community-common-5.7.28-1.el7.x86_64 mysql-community-libs-compat-5.7.28-1.el7.x86_64 mysql-community-libs-5.7.28-1.el7.x86_64 mysql-community-client-5.7.28-1.el7.x86_64 [root@localhost ~]# rpm -ev mysql-community-client-5.7.28-1.el7.x86_64 --nodeps 软件包准备中... mysql-community-client-5.7.28-1.el7.x86_64 [root@localhost ~]# rpm -ev mysql-community-server-5.7.28-1.el7.x86_64 --nodeps 软件包准备中... mysql-community-server-5.7.28-1.el7.x86_64 警告：/etc/my.cnf 已另存为 /etc/my.cnf.rpmsave [root@localhost ~]# find / -name mysql /root/桌面/mysql-community-server-8.0.18-1.el8.x86_64/etc/logrotate.d/mysql /root/桌面/mysql-community-server-8.0.18-1.el8.x86_64/usr/lib64/mysql /root/桌面/mysql-community-server-8.0.18-1.el8.x86_64/var/lib/mysql /var/lib/mysql /var/lib/mysql/mysql /usr/lib64/mysql /usr/share/mysql [root@localhost ~]# rm -rf /var/lib/mysql [root@localhost ~]# rm -rf /var/lib/mysql/mysql [root@localhost ~]# rm -rf /usr/lib64/mysql [root@localhost ~]# rm -rf /usr/share/mysql [root@localhost ~]# rm -rf /etc/my.cnf [root@localhost ~]# rpm -qa | grep -i mysql mysql57-community-release-el7-8.noarch mysql-community-common-5.7.28-1.el7.x86_64 mysql-community-libs-compat-5.7.28-1.el7.x86_64 mysql-community-libs-5.7.28-1.el7.x86_64 [root@localhost ~]# rpm -ev mysql57-community-release-el7-8.noarch --nodeps 软件包准备中... mysql57-community-release-el7-8.noarch [root@localhost ~]# rpm -ev mysql-community-common-5.7.28-1.el7.x86_64 --nodeps 软件包准备中... mysql-community-common-5.7.28-1.el7.x86_64 [root@localhost ~]# rpm -ev mysql-community-libs-compat-5.7.28-1.el7.x86_64 --nodeps 软件包准备中... mysql-community-libs-compat-5.7.28-1.el7.x86_64 [root@localhost ~]# rpm -ev mysql-community-libs-5.7.28-1.el7.x86_64 --nodeps软件包准备中... mysql-community-libs-5.7.28-1.el7.x86_64 [root@localhost ~]# rpm -qa | grep -i mysql [root@localhost ~]#

安装MariaDB服务

[root@localhost ~]# yum install mariadb mariadb-server 已加载插件：langpacks, product-id, search-disabled-repos, subscription-manager This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register. 正在解决依赖关系 --> 正在检查事务 ---> 软件包 mariadb.x86_64.1.5.5.44-2.el7 将被 安装 --> 正在处理依赖关系 mariadb-libs(x86-64) = 1:5.5.44-2.el7，它被软件包 1:mariadb-5.5.44-2.el7.x86_64 需要 ---> 软件包 mariadb-server.x86_64.1.5.5.44-2.el7 将被 安装 --> 正在处理依赖关系 perl-DBD-MySQL，它被软件包 1:mariadb-server-5.5.44-2.el7.x86_64 需要 --> 正在检查事务 ---> 软件包 mariadb-libs.x86_64.1.5.5.44-2.el7 将被 安装 ---> 软件包 perl-DBD-MySQL.x86_64.0.4.023-5.el7 将被 安装 --> 解决依赖关系完成 依赖关系解决 ================================================================================ Package 架构 版本 源 大小 ================================================================================ 正在安装: mariadb x86_64 1:5.5.44-2.el7 yum 9.0 M mariadb-server x86_64 1:5.5.44-2.el7 yum 11 M 为依赖而安装: mariadb-libs x86_64 1:5.5.44-2.el7 yum 754 k perl-DBD-MySQL x86_64 4.023-5.el7 yum 140 k 事务概要 ================================================================================ 安装 2 软件包 (+2 依赖软件包) 总下载量：20 M 安装大小：109 M Is this ok [y/d/N]: y Downloading packages: -------------------------------------------------------------------------------- 总计 71 MB/s | 20 MB 00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction 警告：RPM 数据库已被非 yum 程序修改。 ** 发现 2 个已存在的 RPM 数据库问题， 'yum check' 输出如下： 2:postfix-2.10.1-6.el7.x86_64 有缺少的需求 libmysqlclient.so.18()(64bit) 2:postfix-2.10.1-6.el7.x86_64 有缺少的需求 libmysqlclient.so.18(libmysqlclient_18)(64bit) 正在安装 : 1:mariadb-libs-5.5.44-2.el7.x86_64 1/4 正在安装 : 1:mariadb-5.5.44-2.el7.x86_64 2/4 正在安装 : perl-DBD-MySQL-4.023-5.el7.x86_64 3/4 正在安装 : 1:mariadb-server-5.5.44-2.el7.x86_64 4/4 验证中 : 1:mariadb-libs-5.5.44-2.el7.x86_64 1/4 验证中 : 1:mariadb-5.5.44-2.el7.x86_64 2/4 验证中 : perl-DBD-MySQL-4.023-5.el7.x86_64 3/4 验证中 : 1:mariadb-server-5.5.44-2.el7.x86_64 4/4 已安装: mariadb.x86_64 1:5.5.44-2.el7 mariadb-server.x86_64 1:5.5.44-2.el7 作为依赖被安装: mariadb-libs.x86_64 1:5.5.44-2.el7 perl-DBD-MySQL.x86_64 0:4.023-5.el7 完毕！ [root@localhost ~]# systemctl start mariadb [root@localhost ~]# systemctl enable mariadb Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service. [root@localhost ~]# systemctl enable mariadb [root@localhost ~]#

启动MraiaDB服务

[root@localhost ~]# systemctl start mariadb [root@localhost ~]# systemctl enable mariadb Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service. [root@localhost ~]# systemctl enable mariadb [root@localhost ~]#

初始化MraiaDB服务

[root@localhost ~]# mysql_secure_installation /usr/bin/mysql_secure_installation:行379: find_mysql_client: 未找到命令 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] y # 输入要为root管理员设置的数据库密码 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] y # 删除匿名账户 ... 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] y # 禁止root管理员从远程登录 ... 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] y # 删除test数据库并取消对它的访问权限 - 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] y # 刷新授权表，让初始化后的设定立即生效 ... 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 ~]#

在防火墙上设置允许对数据库服务程序的访问请求（数据库默认占用端口3306）

[root@localhost ~]# firewall-cmd --permanent --add-service=mysql [root@localhost ~]# firewall-cmd --reload

登录MariaDB数据库

创建数据库管理账号

格式：CREATE USER 用户名@主机名 IDENTIFIED BY "密码"；

[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 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)]> SET password = PASSWORD("123") # 修改数据库登录密码为：123 -> ; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> quit # 退出数据库 Bye [root@localhost ~]# mysql -u root -p # 重新登录数据库之后，输入刚才设置的新密码 Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 11 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)]> CREATE USER Anivd IDENTIFIED BY "123456"; # 创建数据库管理账号Anivd Query OK, 0 rows affected (0.00 sec) 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 #针对MySQL数据库中的user表单向账号Anivd赋予查询、更新、删除、插入等权限 MariaDB [mysql]> SELECT HOST,USER,PASSWORD FROM user WHERE USER="Anivd"; +------+-------+-------------------------------------------+ | HOST | USER | PASSWORD | +------+-------+-------------------------------------------+ | % | Anivd | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +------+-------+-------------------------------------------+ 1 row in set (0.01 sec) MariaDB [mysql]> quit Bye [root@localhost ~]#

管理用户以及授权

[root@localhost ~]# mysql -u Anivd -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 15 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 | +--------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> quit Bye [root@localhost ~]#

grant 命令详解

针对MySQL数据库中的user数据表向账号Anivd赋予查询、更新、删除、插入等权限

​[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 16 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 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 # 下面操作是给账户Anivd针对user表单赋予增删查改的权限！ MariaDB [mysql]> GRANT SELECT,UPDATE,DELETE,INSERT ON mysql.user TO Anivd@localhost; Query OK, 0 rows affected (0.00 sec) # 赋予账户Anivd权限之后可以查看一下Anivd用户的权限！ MariaDB [mysql]> SHOW GRANTS FOR Anivd@localhost; +-------------------------------------------------------------------------------+ | Grants for Anivd@localhost | +-------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'Anivd'@'localhost' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`user` TO 'Anivd'@'localhost' | +-------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) ​ MariaDB [mysql]> quit Bye [root@localhost ~]#

使用Anivd用户登录数据库，此时就能够看到MySQL数据库和user数据表了

[root@localhost ~]# mysql -u Anivd -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 20 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 | +--------------------+ 2 rows in set (0.00 sec) 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]> show tables; +-----------------+ | Tables_in_mysql | +-----------------+ | user | +-----------------+ 1 row in set (0.00 sec) MariaDB [mysql]> quit Bye [root@localhost ~]#

revoke命令  移除Anivd用户针对user数据表的增删查改的权限

[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 21 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 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 # 下面操作是给账户Anivd移除增删查改的权限！ MariaDB [mysql]> REVOKE SELECT,UPDATE,DELETE,INSERT ON mysql.user FROM Anivd@localhost; Query OK, 0 rows affected (0.00 sec) # 查看用户权限 MariaDB [mysql]> SHOW GRANTS FOR Anivd@localhost; +-------------------------------------------+ | Grants for Anivd@localhost | +-------------------------------------------+ | GRANT USAGE ON *.* TO 'Anivd'@'localhost' | +-------------------------------------------+ 1 row in set (0.00 sec) MariaDB [mysql]> quit Bye [root@localhost ~]#

创建数据库和数据表

创建一个名为chd的数据库

[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 22 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)]> CREATE DATABASE chd; Query OK, 1 row affected (0.00 sec) ​ MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | Anivd | | chd | | mysql | | performance_schema | +--------------------+ 5 rows in set (0.00 sec) ​ MariaDB [(none)]>

在数据库中创建数据表chuid

MariaDB [(none)]> use chd; 切换指定的chd数据库中，在chd数据库中创建表单chuidDatabase changedMariaDB [chd]> CREATE TABLE chuid (name char(15),height int,age int);Query OK, 0 rows affected (0.05 sec) ​MariaDB [chd]> DESCRIBE chuid;    查看数据表的结构信息+--------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+----------+------+-----+---------+-------+| name | char(15) | YES | | NULL | || height | int(11) | YES | | NULL | || age | int(11) | YES | | NULL | |+--------+----------+------+-----+---------+-------+3 rows in set (0.00 sec)MariaDB [chd]>

管理数据表和数据

MariaDB [chd]> INSERT INTO chuid(name,height,age) VALUES('chenhuidong','171','27'); Query OK, 1 row affected (0.00 sec) MariaDB [chd]> select * from chuid; +-------------+--------+------+ | name | height | age | +-------------+--------+------+ | chen | 171 | 27 | +-------------+--------+------+ 1 row in set (0.00 sec) MariaDB [chd]> UPDATE chuid SET age=26 ; # 修改数据表，将chuid表中的年龄改为26 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [chd]> SELECT name,age FROM chuid; # 使用select命令查看该表的名称和年龄信息 +-------------+------+ | name | age | +-------------+------+ | chen | 26 | +-------------+------+ 1 row in set (0.00 sec) MariaDB [chd]>

删除chuid数据表

MariaDB [chd]> DELETE FROM chuid; Query OK, 1 row affected (0.00 sec) MariaDB [chd]> SELECT * FROM chuid; Empty set (0.00 sec)

在数据表中插入多条数据

MariaDB [chd]> INSERT INTO chuid(name,age,height) VALUES('ZHANG','25','170'); Query OK, 1 row affected (0.00 sec) MariaDB [chd]> INSERT INTO chuid(name,age,height) VALUES('li','25','165'); Query OK, 1 row affected (0.01 sec) MariaDB [chd]> INSERT INTO chuid(name,age,height) VALUES('xia','24','163'); Query OK, 1 row affected (0.01 sec) MariaDB [chd]>

要想让查询结果更加精准，就需要结合使用select与where命令了。其中，where命令是在数据库中进行匹配查询的条件命令。通过设置查询条件，就可以仅查找出符合该条件的数据。

MariaDB [chd]> SELECT * FROM chuid WHERE age>25; # 查找数据表中年龄大于25岁的人 +------+------+--------+ | name | age | height | +------+------+--------+ | chen | 26 | 171 | +------+------+--------+ 1 row in set (0.00 sec) ​ MariaDB [chd]> SELECT * FROM chuid WHERE age!=27; #查找数据表中年龄不等于27岁的人 +-------+------+--------+ | name | age | height | +-------+------+--------+ | chen | 26 | 171 | | ZHANG | 25 | 170 | | li | 25 | 165 | | xia | 24 | 163 | +-------+------+--------+ 4 rows in set (0.00 sec) ​ MariaDB [chd]> quit Bye [root@localhost ~]#

数据库的备份与恢复

备份数据库的使用mysqldump命令

mysqldump [参数] [数据库名称]

[root@localhost ~]# mysqldump -u root -p chd > /root/chuidDB.dump #将chd数据库中的内容导出成一个文件，并保存到root管理员的家目录chuidDB.dump中 Enter password: # 输入root管理员的数据库登录密码 [root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 18 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)]> DROP DATABASE chd; # 删除已创建的数据库 Query OK, 1 row affected (0.00 sec) ​ MariaDB [(none)]> SHOW databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) ​ MariaDB [(none)]> CREATE DATABASE chd; # 创建数据库chd Query OK, 1 row affected (0.00 sec) ​ MariaDB [(none)]> quit Bye [root@localhost ~]# mysql -u root -p chd < /root/chuidDB.dump # 使用输入重定向符将root家目录中的chuidDB.dump数据库备份文件导入到chd数据库中 Enter password: # 输入root管理员的数据库登录密码 [root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 20 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 chd; 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 [chd]> SHOW tables; +---------------+ | Tables_in_chd | +---------------+ | chuid | +---------------+ 1 row in set (0.00 sec) ​ MariaDB [chd]> quit Bye [root@localhost ~]#

登录到MariaDB数据库之后，又能看到chd数据库以及chuid数据表了

数据库恢复成功！