Raspberry PI 4BでMariaDB(mysql)を使う
ちょっとラズパイ環境でDBを使いたくなった。 入れた覚えはないのだけど何故かインストールされていたMariaDBを使ってみる。
とりあえず現状されているものを確認する。
$ apt list --installed | grep mariadb
WARNING: apt does not have a stable CLI interface. Use with caution in scripts.
libmariadb3/oldstable,oldstable,oldstable,now 1:10.3.36-0+deb10u2 armhf [インストール済み、自動]
mariadb-client-10.3/oldstable,oldstable,oldstable,now 1:10.3.36-0+deb10u2 armhf [インストール済み、自動]
mariadb-client-core-10.3/oldstable,oldstable,oldstable,now 1:10.3.36-0+deb10u2 armhf [インストール済み、自動]
mariadb-common/oldstable,oldstable,oldstable,now 1:10.3.36-0+deb10u2 all [インストール済み、自動]
mariadb-server-10.3/oldstable,oldstable,oldstable,now 1:10.3.36-0+deb10u2 armhf [インストール済み、自動]
mariadb-server-core-10.3/oldstable,oldstable,oldstable,now 1:10.3.36-0+deb10u2 armhf [インストール済み、自動]
mariadb-server/oldstable,oldstable,oldstable,now 1:10.3.36-0+deb10u2 all [インストール済み]
$ mariadb --version
mariadb Ver 15.1 Distrib 10.3.36-MariaDB, for debian-linux-gnueabihf (armv8l) using readline 5.2
MariaDBの実体はMySQLであり、mariadbコマンドはmysqlに対するSynbolic linkである。なので入力の手間やネット情報のコピペ()などを考慮して通常はmysqlを使うのが楽そうだ。
topコマンドを見ると既にmysqldが動いているのだけど、念のため最初からセットアップを行う。
$ sudo mysql_secure_installation
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): [enter押下]
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: [適当に]
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] n
... skipping.
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
- 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!
CLIを起動してみる。
$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
$ mysql -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
設定したはずのパスワードを入力しても弾かれる…どういうことなの。 調べてみるとパスワードなしでアクセス可能にする方法があるようなので試してみる。
まず設定ファイルを開く。
$ sudo find / -name "my.cnf"
/var/lib/dpkg/alternatives/my.cnf
/etc/mysql/my.cnf
/etc/alternatives/my.cnf
$ sudo vi /etc/mysql/my.cnf
以下を追加する
[mysqld]
skip-grant-tables
mysqldを再起動する。
$ sudo service mysql restart
再度CLIを試すと上手くいった。
$ mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.3.36-MariaDB-0+deb10u2 Raspbian 10
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 [(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
念のためflush privilegesを実行しメモリ上で参照用にキャッシュしている情報を更新する。
情報更新後にこれを怠るとユーザーや権限情報などを書き換えても操作に反映されないらしい。
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
現在登録されているユーザーを確認する。
MariaDB [mysql]> select Host,User,Password from user;
+-----------+------+-------------------------------------------+
| Host | User | Password |
+-----------+------+-------------------------------------------+
| localhost | root | *A4E6EBBAB1020D17CFD6687958B4ED3878A11DC2 |
+-----------+------+-------------------------------------------+
何らかのパスワードは設定されている。念のため再設定する。
MariaDB [mysql]> ALTER USER 'root'@'localhost' IDENTIFIED BY '*******';
MariaDB [mysql]> exit
Bye
設定ファイルからskip-grant-tablesを削除しDBを再起動。再設定したパスワードで改めてログインを試すとちゃんとログインできた。
後はSQLの話なので割愛。
その他管理系の操作
新しいユーザーを作成する
> CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'user_password';
ユーザーに権限を付与する
> GRANT ALL ON 'db_name or *'.'table_name or *' TO 'user_name'@'localhost' IDENTIFIED BY 'user_password';
ユーザーの権限を確認する
> SHOW GRANTS FOR user_name@localhost;
ユーザーから権限を剥奪する
> REVOKE ALL ON 'db_name or *'.'table_name or *' FROM 'user_name'@'localhost';
現在接続中のユーザー
SELECT user(), current_user();
+--------+----------------+
| user() | current_user() |
+--------+----------------+
| root@ | @ |
+--------+----------------+
テーブルのスキーマを確認する
> desc user;
+------------------------+-----------------------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+----------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(80) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| Delete_history_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) | NO | | 0 | |
| plugin | char(64) | NO | | | |
| authentication_string | text | NO | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| is_role | enum('N','Y') | NO | | N | |
| default_role | char(80) | NO | | | |
| max_statement_time | decimal(12,6) | NO | | 0.000000 | |
+------------------------+-----------------------------------+------+-----+----------+-------+