Mariadb как подключиться к базе

Управление базами данных MySQL и MariaDB на облачном сервере

MySQL и MariaDB – реляционные системы управления базами данных. Данные инструменты используются на VPS для управления данными разных программ. Обе программы написаны на языке запросов SQL, и любая может быть использована на облачном сервере.

Данное руководство описывает создание базы данных при помощи этих инструментов – фундаментальный навык, необходимый для управления данными в среде SQL. Кроме того, статья рассматривает некоторые другие аспекты управления базами данных.

В данном руководстве используется сервер Ubuntu 12.04. Тем не менее, другие дистрибутивы будут работать подобным образом.

Создание базы данных в MySQL и MariaDB

Войдите в MySQL или MariaDB при помощи следующей команды:

Введите пароль администратора, установленный во время инсталляции MySQL/MariaDB.

Теперь можно создать базу данных, введя команду:

CREATE DATABASE new_database;
Query OK, 1 row affected (0.00 sec)

Чтобы избежать ошибок, возникающих в случае, если БД с таким именем уже существует, используйте команду:

CREATE DATABASE IF NOT EXISTS new_database;
Query OK, 1 row affected, 1 warning (0.01 sec)

Warning означает, что база данных с таким именем уже существует и новая БД не была создана.

Если же опция IF NOT EXISTS не была использована, а БД с таким именем уже существует, появится следующее уведомление об ошибке:

ERROR 1007 (HY000): Can’t create database ‘other_database’; database exists

Просмотр баз данных MySQL и MariaDB

Чтобы получить список существующих баз данных, используйте команду:

SHOW DATABASES;
+———————+
| Database |
+———————+
| information_schema |
| mysql |
| new_database |
| other_database |
| performance_schema |
+———————+
5 rows in set (0.00 sec)

Базы данных information_schema, performance_schema и mysql в большинстве случаев создаются по умолчанию, без крайней необходимости (и умения с ними работать) их лучше не трогать.

Изменение баз данных в MySQL и MariaDB

Любая операция, выполняемая без явного указания базы данных, будет выполнена на текущую БД.

Чтобы узнать, какая база данных является текущей, наберите:

Результат NULL сообщает, что на данный момент текущая база данных не выбрана.

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

USE new_database;
Database changed

Снова используйте запущенную ранее команду, чтобы узнать, какая БД является текущей:

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

Чтобы удалить базу данных в MySQL/MariaDB, используйте команду:

DROP DATABASE new_database;
Query OK, 0 rows affected (0.00 sec)

Примечание: данную операцию невозможно отменить! Убедитесь, что базу данных действительно нужно удалить, прежде чем нажать enter!

Если выполнить эту команду на БД, которой не существует, появится следующая ошибка:

DROP DATABASE new_database;
ERROR 1008 (HY000): Can’t drop database ‘new_database’; database doesn’t exist

Чтобы предотвратить эту ошибку и добиться выполнения команды вне зависимости от того, существует БД или нет, используйте опцию IF EXISTS:

DROP DATABASE IF EXISTS new_database;
Query OK, 0 rows affected, 1 warning (0.00 sec)

В данном случае warning значит, что такой базы данных не существует, но команда выполнена.

Итоги

Итак, данное руководство ознакомило с базовыми навыками, необходимыми для управления базами данных MySQL или MariaDB. Конечно, есть еще огромное множество функций, которые нужно научиться использовать.

MariaDB

MariaDB is a reliable, high performance and full-featured database server which aims to be an ‘always Free, backward compatible, drop-in’ replacement of MySQL. Since 2013 MariaDB is Arch Linux’s default implementation of MySQL.[1]

Contents

Installation

MariaDB is the default implementation of MySQL in Arch Linux, provided with the mariadb package.

• If the database (in /var/lib/mysql ) resides on a Btrfs file system, you should consider disabling Copy-on-Write for the directory before creating any database.
• If the database resides on a ZFS file system, you should consult ZFS#Databases before creating any database.

Install mariadb , and run the following command before starting the mariadb.service :

Now mariadb.service can be started and/or enabled.

To simplify administration, you might want to install a front-end.

Configuration

alt=»Tango-view-refresh-red.png» width=»48″ height=»48″ />This article or section is out of date. alt=»Tango-view-refresh-red.png» width=»48″ height=»48″ />

Once you have started the MySQL server and added a root account, you may want to change the default configuration.

To log in as root on the MySQL server, use the following command:

Add user

Creating a new user takes two steps: create the user; grant privileges. In the below example, the user monty with some_pass as password is being created, then granted full permissions to the database mydb:

Configuration files

MariaDB configuration options are read from the following files in the given order (according to mysqld —help —verbose | tail -20 output):

Depending on the scope of the changes you want to make (system-wide, user-only. ), use the corresponding file. See this entry of the Knowledge Base for more information.

Enable auto-completion

The MySQL client completion feature is disabled by default. To enable it system-wide edit /etc/my.cnf.d/mysql-clients.cnf , and add auto-rehash under mysql . Note that this must not be placed under mysqld . Completion will be enabled next time you run the MySQL client.

Using UTF8MB4

• The mariadb package already uses utf8mb4 as charset and utf8mb4_unicode_ci as collation. Users using the default (character) settings may want to skip this section.
• UTF8MB4 is recommended over UTF-8 since it allows full Unicode support [2][3].

Append the following values to the main configuration file located at /etc/my.cnf.d/my.cnf :

Restart mariadb.service to apply the changes.

See #Maintenance to optimize and check the database health.

Increase character limit

alt=»Tango-view-refresh-red.png» width=»48″ height=»48″ />This article or section is out of date. alt=»Tango-view-refresh-red.png» width=»48″ height=»48″ />

For InnoDB execute the following commands to support a higher character-limit:

Append the following lines in /etc/mysql/my.cnf to always use a higher character-limit:

Restart mariadb.service to apply the changes.

On table creating append the ROW_FORMAT as seen in the example:

Using a tmpfs for tmpdir

The directory used by MySQL for storing temporary files is named tmpdir. For example, it is used to perform disk based large sorts, as well as for internal and explicit temporary tables.

Create the directory with appropriate permissions:

Add the following tmpfs mount to your /etc/fstab file:

Add to your /etc/my.cnf.d/server.cnf file under the mysqld group:

Stop mariadb.service , mount /var/lib/mysqltmp/ and start mariadb.service .

Time zone tables

Although time zone tables are created during the installation, they are not automatically populated. They need to be populated if you are planning on using CONVERT_TZ() in SQL queries.

To populate the time zone tables with all the time zones:

Optionally, you may populate the table with specific time zone files:

Security

Improve initial security

The mysql_secure_installation command will interactively guide you through a number of recommended security measures, such as removing anonymous accounts and removing the test database:

Listen only on the loopback address

By default, MySQL will listen on the 0.0.0.0 address, which includes all network interfaces. In order to restrict MySQL to listen only to the loopback address, add the following line in /etc/my.cnf.d/server.cnf :

This will bind to both 127.0.0.1 and ::1, and enable MariaDB to receive connections both in IPv4 and IPv6.

Enable access locally only via Unix sockets

By default, MySQL is accessible via both Unix sockets and the network. If MySQL is only needed for the localhost, you can improve security by not listening on TCP port 3306, and only listening on Unix sockets instead. To do this, add the following line in /etc/my.cnf.d/server.cnf :

You will still be able to log in locally as before, but only using Unix sockets.

Grant remote access

To allow remote access to the MySQL server, ensure that MySQL has networking enabled and is listening on the appropriate interface.

Grant any MySQL user remote access (example for root):

Check current users with remote access privileged:

Now grant remote access for your user (here root):

You can change the ‘%’ wildcard to a specific host if you like. The password can be different from user’s main password.

Configure access to home directories

For security reasons, the systemd service file contains ProtectHome=true , which prevents MariaDB from accessing files under the /home , /root and /run/user hierarchies. The datadir has to be in an accessible location and owned by the mysql user and group.

You can modify this behavior by creating a supplementary service file as described here.

Maintenance

Upgrade databases on major releases

Upon a major version release of mariadb (for example mariadb-10.1.10-1 to mariadb-10.1.18-1), it is wise to upgrade databases:

To upgrade from 10.1.x to 10.3.x:

• keep the 10.1.x database daemon running
• upgrade the package
• run mysql_upgrade (from the new package version) against the old still-running daemon. This will produce some error messages; however, the upgrade will succeed.
• restart the daemon, so the 10.3.x daemon runs.

Alternatively, stop the (old) daemon, run the (new) daemon in safe mode, run mysql_upgrade against that, and then start the (new) daemon as described in #Unable to run mysql_upgrade because MySQL cannot start.

Checking, optimizing and repairing databases

mariadb ships with mysqlcheck which can be used to check, repair, and optimize tables within databases from the shell. See the mysqlcheck man page for more. Several command tasks are shown:

To check all tables in all databases:

To analyze all tables in all databases:

To repair all tables in all databases:

To optimize all tables in all databases:

Backup

There are various tools and strategies to back up your databases.

If you are using the default InnoDB storage engine, a suggested way of backing up all your bases online while provisioning for point-in-time recovery (also known as “roll-forward,” when you need to restore an old backup and replay the changes that happened since that backup) is to execute the following command:

This will prompt for MariaDB’s root user’s password, which was defined during database #Configuration.

Specifying the password on the command line is strongly discouraged, as it exposes it to discovery by other users through the use of ps aux or other techniques. Instead, the aforementioned command will prompt for the specified user’s password, concealing it away.

Compression

As SQL tables can get pretty large, it is recommended to pipe the output of the aforementioned command in a compression utility like gzip :

Decompressing the backup thus created and reloading it in the server is achieved by doing:

This will recreate and repopulate all the databases previously backed up (see this or this).

Non-interactive

If you want to setup non-interactive backup script for use in cron jobs or systemd timers, see option files and this illustration for mysqldump.

Basically you should add the following section to the relevant configuration file:

Mentioning a user here is optional, but doing so will free you from having to mention it on the command line. If you want to set this for all tools, including mysql , use the [client] group.

Example script

The database can be dumped to a file for easy backup. The following shell script will do this for you, creating a db_backup.gz file in the same directory as the script, containing your database dump:

See also the official mysqldump page in the MySQL and MariaDB manuals.

Holland Backup

A python-based software package named Holland Backup is available in AUR to automate all of the backup work. It supports direct mysqldump, LVM snapshots to tar files (mysqllvm), LVM snapshots with mysqldump (mysqldump-lvm), and xtrabackup methods to extract the data. The Holland framework supports a multitude of options and is highly configurable to address almost any backup situation.

The main holland AUR and holland-common AUR packages provide the core framework; one of the sub-packages ( holland-mysqldump AUR , holland-mysqllvm AUR and/or holland-xtrabackup AUR must be installed for full operation. Example configurations for each method are in the /usr/share/doc/holland/examples/ directory and can be copied to /etc/holland/backupsets/ , as well as using the holland mk-config command to generate a base configuration for a named provider.

Troubleshooting

Unable to run mysql_upgrade because MySQL cannot start

Try run MySQL in safemode:

Reset the root password

1. Stop mariadb.service .
2. Start the mysqld server with safety features:
3. Connect to it:
4. Change root password:
5. Kill running mysqld* processes:
6. Start mariadb.service .

Check and repair all tables

Check and auto repair all tables in all databases, see more:

Optimize all tables

Forcefully optimize all tables, automatically fixing table errors that may come up.

OS error 22 when running on ZFS

If using MySQL databases on ZFS, the error InnoDB: Operating system error number 22 in a file operation may occur.

A workaround is to disable aio_writes in /etc/my.cnf.d/my.cnf :

Cannot login through CLI, but phpmyadmin works well

This may happen if you are using a long (>70-75) password. As for 5.5.36, for some reason, mysql CLI cannot handle that many characters in readline mode. So, if you are planning to use the recommended password input mode:

Consider changing the password to smaller one.

MySQL binary logs are taking up huge disk space

alt=»Tango-view-refresh-red.png» width=»48″ height=»48″ />This article or section is out of date. alt=»Tango-view-refresh-red.png» width=»48″ height=»48″ />

By default, mysqld creates binary log files in /var/lib/mysql . This is useful for replication master server or data recovery. But these binary logs can eat up your disk space. If you do not plan to use replication or data recovery features, you may disable binary logging by commenting out these lines in /etc/my.cnf.d/my.cnf :

Or you could limit the size of the logfile like this:

Alternatively, you can purge some binary logs in /var/lib/mysql to free up disk space with this command:

OpenRC fails to start MySQL

To use MySQL with OpenRC you need to add the following lines to the [mysqld] section in the MySQL configuration file, located at /etc/my.cnf.d/my.cnf .

You should now be able to start MySQL using:

Specified key was too long

Changed limits warning on max_open_files/table_open_cache

Increase the number of file descriptors by creating a systemd drop-in, e.g.:

10.4 to 10.5 upgrade crash: «InnoDB: Upgrade after a crash is not supported. The redo log was created with MariaDB 10.4.x»

Before MariaDB 10.5, redo log was unnecessarily split into multiple files.[7]

Move the old binary logs /var/lib/mysql/ib_logfile* out of the way, thus letting MariaDB 10.5 create new ones. Then restart mariadb.service and upgrade your tables with mysql_upgrade .

Table ‘mysql.xxx’ doesn’t exist in engine

Symptom: When running mysql_upgrade or mysqlcheck, it return one or more error like these:

�� Настройка удаленного доступа MySQL и MariaDB в Linux Ubuntu

По умолчанию сервер MySQL настроен таким образом, что к нему разрешены подключения только с локальной машины, следовательно, подключиться из-вне (по интернет или локальной сети) не получится.

Убедиться в этом можно набрав на сервере команду:

В результате получите что-то типа этого:

Отсюда видно, что mysql слушает только интерфейс localhost (127.0.0.1). Это не всегда удобно, особенно когда есть необходимость выделить под сервер mysql отдельный сервер. А в рамках корпоративной локальной сети такое бывает очень часто.

Чтобы разрешить серверу MySQL принимать запросы из-вне необходимо предпринять несколько несложных шагов:

1. Поменять одну строчку в конфигурационном файле MySQL;
2. Создать сетевого пользователя с необходимыми правами.

Разрешаем MySQL слушать интерфейс, который смотрит во внешнюю сеть

Открываем конфигурационный файл любимы редактором, например nano, из под привилегированного пользователя:

Если у вас установлен сервер mariaDB, то конфигурационный файл находится в другом месте:

и меняем 127.0.0.1 на 0.0.0.0 — тогда сервер будет слушать все интерфейсы компьютера, либо задаем конкретный ip-адрес локального интерфейса, который смотрит в локальную сеть. Например — 192.168.122.10.

Теперь остается только перезапустить сервис MySQL:

Теперь осталось только завести пользователя, которому разрешено обращаться к серверу MySQL извне.

Создание внешнего пользователя MySQL

Теперь нужной подключиться к MySql с паролем суперпользователя системы (системы. а не MySQL):

После подключения к MySQL можно создать пользователя и дать привилегию, например:

Здесь дается полный доступ к базе данных userdata пользователю с логином user и паролем password, подключающемуся с любого ip.

Можно ограничить права пользователя, разрешив ему подключаться к базе только с определенного ip. Для этого меняем % на конкретный ip-адрес, например 192.168.122.16

А можно и разрешить пользователю всё — подключаться ко всем базам с любого ip-адреса