Работа с базой данных MariaDB

Опубликовано admin - вс, 09/03/2023 - 17:03

Практические примеры работы с базой данных MariaDB в терминале Linux: создание, архивирование, удаление, очистка, восстановление, поиск и замена строк.

Примеры работы с базой данных MariaDB в терминале Linux

Сервер сообщества MariaDB гарантированно имеет открытый исходный код, навсегда и бесплатно. Сейчас он очень популярен.

Содержание

Введение

Работа выполняется на удалённом сервере в командной строке с помощью mysql, представляющую собой простую оболочку для команд SQL, и использованием базы данных этого сайта. Приведены простые, но крайне необходимые команды.

Версия базы данных:

mensh@139-162-132-20:~$ sudo mariadb -v
[sudo] password for mensh: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5703
Server version: 10.3.38-MariaDB-0ubuntu0.20.04.1-log Ubuntu 20.04

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

Reading history-file /root/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Основные команды mysql для работы с базой данных

Вход на удалённый сервер

Для входа на удалённый сервер используется следующая команда:

oleg@mobile:~:$ ssh mensh@139.162.132.20
mensh@139.162.132.20's password: 
Welcome to Ubuntu 20.04.6 LTS (GNU/Linux 5.4.0-125-generic x86_64)
...
mensh@139-162-132-20:~$ 

Здесь, конечно, следует указывать свои логин, пароль и IP сервера.

Вход в MariaDB

Входим на сервер MariaDB, используя команду mysql с опциями -u и -p (логин и пароль):

mensh@139-162-132-20:~$ mysql -u mensh -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5776
Server version: 10.3.38-MariaDB-0ubuntu0.20.04.1-log Ubuntu 20.04

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

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

MariaDB [(none)]> 

Вот теперь мы можем работать с базами данных сервера MariaDB.

Обратите внимание на то, что все текстовые команды должны заканчиваться символом ;.

Вывод существующих баз данных

Здесь мы используем следующую команду:

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linuxcookbookru    |
| menshru            |
| mysql              |
| olegmensheninru    |
| performance_schema |
| svastiru           |
+--------------------+
8 rows in set (0.006 sec)

MariaDB [(none)]> 

Выведен результат.

Создание базы данных

Экспериментировать с существующими базами данных, конечно, не следует. Создаём базу данных, например, example:

MariaDB [(none)]> CREATE DATABASE example;
Query OK, 1 row affected (0.003 sec)

MariaDB [(none)]> 

Можно было создать базу данных и так:

mensh@139-162-132-20:~$mysqladmin -u mensh -p create example
Enter password: 
mensh@139-162-132-20:~$ 

Переходим в созданную нами базу данных:

MariaDB [(none)]> USE example; Database changed MariaDB [example]>

Она пока пуста:

MariaDB [example]> SHOW TABLES;
Empty set (0.001 sec)

MariaDB [example]> 

Наполнение базы

Наполним базу из дампа данных базы linuxcookbookru.

Для перехода в каталог используется новая вкладка терминала, либо осуществляется выход из сервера баз данных:

MariaDB [example]> SHOW TABLES;
Empty set (0.001 sec)

MariaDB [example]> exit;
Bye
mensh@139-162-132-20:~$ 

Переходим в каталог с дампами баз данных:

mensh@139-162-132-20:~$ cd www/backup/
mensh@139-162-132-20:~/www/backup$ 

Просматриваем содержимое:

mensh@139-162-132-20:~/www/backup$ ls -l
total 122220
-rwxr-xr-x 1 mensh users      312 Oct  1  2021 linuxcookbookru_dump.sh
-rw-r--r-- 1 mensh users 28380598 Sep  1 00:10 linuxcookbookru_linode_20230901.sql.bz2
-rw-r--r-- 1 mensh users 30272311 Sep  2 00:11 linuxcookbookru_linode_20230902.sql.bz2
-rw-r--r-- 1 mensh users 19791345 Sep  3 00:10 linuxcookbookru_linode_20230903.sql.bz2
-rwxr-xr-x 1 mensh users      272 Oct  1  2021 menshru_dump.sh
-rw-r--r-- 1 mensh users 12284665 Sep  1 01:10 menshru_linode_20230901.sql.bz2
-rw-r--r-- 1 mensh users 12553957 Sep  2 01:10 menshru_linode_20230902.sql.bz2
-rw-r--r-- 1 mensh users 14371513 Sep  3 01:10 menshru_linode_20230903.sql.bz2
-rwxr-xr-x 1 mensh users    39054 Jun  8  2010 mysqltuner.pl
-rwxr-xr-x 1 mensh users      844 Sep 21  2021 n2dissite
-rwxr-xr-x 1 mensh users     1080 Sep 21  2021 n2ensite
-rw-r--r-- 1 mensh users  7417454 Jun 16 17:05 svastiru.sql.bz2
-rwxr-xr-x 1 mensh users      277 Aug 25  2012 svastiru_dump.sh
-rwxr-xr-x 1 mensh users      895 Jul  6  2021 truncate_caches.sh
mensh@139-162-132-20:~/www/backup$ 

Разархивируем самый свежий дамп:

mensh@139-162-132-20:~/www/backup$ tar xjvf linuxcookbookru_linode_20230903.sql.bz2
linuxcookbookru_linode_20230903.sql
mensh@139-162-132-20:~/www/backup$ 

Заливаем его в нашу новую базу данных:

mensh@139-162-132-20:~/www/backup$ mysql -u mensh -p example < linuxcookbookru_linode_20230903.sql
Enter password: 
mensh@139-162-132-20:~/www/backup$ 

Просмотр таблиц

Если мы вышли из новой базы данных, то входим в неё снова и просматриваем таблицы:

MariaDB [example]> SHOW TABLES;
+---------------------------------------+
| Tables_in_example                     |
+---------------------------------------+
| batch                                 |
...
| node                                  |
| node__body                            |
| node__comment                         |
| node__comment_node_article            |
| node__comment_node_book               |
| node__comment_node_page               |
| node__field_category                  |
...
| watchdog                              |
| xmlsitemap                            |
+---------------------------------------+
103 rows in set (0.014 sec)

MariaDB [example]> 

Работать будем с таблицей body_value. В ней мы произведём замену строк iron_image на example_image.

Поиск строки

Для поиска нужных нам строк воспользуемся парой командой.

Выводим первую строку таблицы body_value:

MariaDB [example]> SELECT * FROM node__body LIMIT 0,1;
+---------+---------+-----------+-------------+----------+-------+------------+--------------+-------------+
| bundle  | deleted | entity_id | revision_id | langcode | delta | body_value | body_summary | body_format |
+---------+---------+-----------+-------------+----------+-------+------------+--------------+-------------+
| article |       0 |         1 |           1 | und      |     0 |            | NULL         | full_html   |
+---------+---------+-----------+-------------+----------+-------+------------+--------------+-------------+
1 row in set (0.001 sec)

MariaDB [example]> 

Здесь мы вывели все колонки * таблицы установив лимит на строки. Если лимит не устанавливать, то будут выведены все строки таблицы, то есть в данном случае — куча ненужного нам мусора.

Все колонки нам не нужны. Изменения будем вносить в колонку body_value, но выведем и bundle, так как необходимый материал является статьёй, а выводить лишние строки нет резона:

SELECT bundle, body_value FROM node__body WHERE bundle = 'article' AND body_value LIKE '%iron_image%';
...

... 210 rows in set (0.013 sec) MariaDB [example]>

Итак, видим то, что нам нужно — iron_image. Таких строк в таблице 210.

Замена строки

Для замены iron_image на example_image используем следующую команду:

MariaDB [example]> UPDATE node__body SET body_value = REPLACE(body_value, 'iron_image', 'example_image') WHERE bundle = 'article';
Query OK, 210 rows affected (0.058 sec)
Rows matched: 272  Changed: 210  Warnings: 0

MariaDB [example]> 

Проверяем результат:

MariaDB [example]> SELECT bundle, body_value FROM node__body WHERE bundle = 'article' AND body_value LIKE '%iron_image%';
Empty set (0.015 sec)

MariaDB [example]> 

Всё отлично. Работа завешена.

Очистка таблицы

Иногда возникает необходимость очистки таблицы. Например, используется Antibot и после 3 неудачных наборов пароля он заблокировал административный вход. В этой ситуации используется команда, очищающая таблицу flood:

MariaDB [example]> TRUNCATE flood;
Query OK, 0 rows affected (0.027 sec)

MariaDB [example]> 

Удаление базы данных

Если в чём-либо «удалось» напортачить, то для перезаливки базы данных из дампа удалять таблицы командой DROP более чем «геморройно». В этой ситуации следует удалить всю базу данных и создать её заново.

Удаляем базу данных, с которой работали:

MariaDB [(none)]> DROP DATABASE example;
Query OK, 103 rows affected (0.508 sec)

MariaDB [(none)]> 

Выход из сеанса

Для завершения сеанса mysql используется следующая команда:

MariaDB [(none)]> exit;
Bye
mensh@139-162-132-20:~$ 

Архивирование

Обязательно следует создавать резервные копии всех баз данных и, конечно, не «врукопашную».

Для этой цели я использую небольшой скрипт:

mensh@139-162-132-20:~/www/backup$ vim linuxcookbookru_dump.sh 

#!/bin/bash

date=%%
cd /home/mensh/www/backup && \
mysqldump --opt -u[my_login] -p[mysq_password] \
linuxcookbookru > linuxcookbookru_linode_`date '+%Y%m%d'`.sql && \
tar cjvf linuxcookbookru_linode_`date '+%Y%m%d'`.sql.bz2 linuxcookbookru_linode_`date '+%Y%m%d'`.sql && \
rm linuxcookbookru_linode_`date '+%Y%m%d'`.sql

Не забывайте о том, что он должен быть исполняемым.

Скрипт, естественно, не запускаю вручную. Его я прописал в crontab:

mensh@139-162-132-20:~$ crontab -l
...
#
10 0 * * *  /bin/bash /home/mensh/www/backup/linuxcookbookru_dump.sh
#
...

Заключение

Как видим, работа с базами данных MariaDB в командной строке Linux доступна всем. Нужен лишь небольшой навык.

Надеюсь, что эта статья будет полезна многим начинающим администраторам. Дерзайте!

Поделиться с друзьями