MariaDB学習中です。少しずつ更新します。
Contents
導入
MariaDBインストール
takk~$ sudo apt update takk~$ sudo apt install mariadb-server
どこにインストールされたのでしょうか。mariaで検索してみます。
takk~$ sudo find / -name '*maria*' /sys/fs/cgroup/devices/system.slice/mariadb.service /sys/fs/cgroup/pids/system.slice/mariadb.service /sys/fs/cgroup/memory/system.slice/mariadb.service /sys/fs/cgroup/systemd/system.slice/mariadb.service /sys/fs/cgroup/unified/system.slice/mariadb.service /etc/systemd/system/multi-user.target.wants/mariadb.service /etc/mysql/mariadb.cnf /etc/mysql/mariadb.conf.d /etc/logcheck/ignore.d.paranoid/mariadb-server-10_3 /etc/logcheck/ignore.d.workstation/mariadb-server-10_3 /etc/logcheck/ignore.d.server/mariadb-server-10_3 /etc/insserv.conf.d/mariadb /usr/lib/systemd/system/mariadb@.service /usr/lib/systemd/system/mariadb@bootstrap.service.d /usr/lib/systemd/system/mariadb.service /usr/lib/x86_64-linux-gnu/mariadb19 /usr/lib/x86_64-linux-gnu/libmariadb.so.3 /usr/share/lintian/overrides/mariadb-server-10.3 /usr/share/mysql/systemd/mariadb@.service /usr/share/mysql/systemd/mariadb.service /usr/share/mysql/policy/selinux/mariadb.te /usr/share/mysql/policy/selinux/mariadb-server.fc /usr/share/mysql/policy/selinux/mariadb-server.te /usr/share/mysql/maria_add_gis_sp_bootstrap.sql /usr/share/mysql/maria_add_gis_sp.sql /usr/share/mysql/mysql_to_mariadb.sql /usr/share/man/man1/mariadb-service-convert.1.gz /usr/share/man/man1/mariadb-check.1.gz /usr/share/man/man1/mariadb.1.gz /usr/share/man/man1/wsrep_sst_mariabackup.1.gz /usr/share/apport/package-hooks/source_mariadb-10.3.py /usr/share/menu/mariadb-client-10.3 /usr/share/doc/mariadb-server-core-10.3 /usr/share/doc/libmariadb3 /usr/share/doc/mariadb-server /usr/share/doc/mariadb-server-10.3 /usr/share/doc/mariadb-common /usr/share/doc/mariadb-client-10.3 /usr/share/doc/mariadb-client-core-10.3 /usr/bin/mariadb-service-convert /usr/bin/wsrep_sst_mariabackup /usr/bin/mariadb /usr/bin/mariadb-check /run/systemd/units/invocation:mariadb.service /var/lib/dpkg/info/mariadb-common.list /var/lib/dpkg/info/mariadb-common.postinst /var/lib/dpkg/info/mariadb-server-10.3.md5sums /var/lib/dpkg/info/libmariadb3:amd64.shlibs /var/lib/dpkg/info/mariadb-client-10.3.postinst /var/lib/dpkg/info/mariadb-server-10.3.postrm /var/lib/dpkg/info/mariadb-common.postrm /var/lib/dpkg/info/mariadb-client-10.3.conffiles /var/lib/dpkg/info/mariadb-server-core-10.3.md5sums /var/lib/dpkg/info/mariadb-client-core-10.3.md5sums /var/lib/dpkg/info/libmariadb3:amd64.symbols /var/lib/dpkg/info/mariadb-server-10.3.preinst /var/lib/dpkg/info/mariadb-client-10.3.list /var/lib/dpkg/info/mariadb-server-10.3.list /var/lib/dpkg/info/mariadb-server.list /var/lib/dpkg/info/libmariadb3:amd64.list /var/lib/dpkg/info/mariadb-server-10.3.config /var/lib/dpkg/info/mariadb-client-core-10.3.list /var/lib/dpkg/info/mariadb-server-core-10.3.list /var/lib/dpkg/info/mariadb-server-10.3.conffiles /var/lib/dpkg/info/mariadb-client-10.3.postrm /var/lib/dpkg/info/mariadb-server-10.3.prerm /var/lib/dpkg/info/mariadb-client-10.3.md5sums /var/lib/dpkg/info/libmariadb3:amd64.triggers /var/lib/dpkg/info/mariadb-common.preinst /var/lib/dpkg/info/mariadb-common.md5sums /var/lib/dpkg/info/mariadb-server.md5sums /var/lib/dpkg/info/mariadb-server-10.3.templates /var/lib/dpkg/info/mariadb-server-10.3.postinst /var/lib/dpkg/info/libmariadb3:amd64.md5sums /var/lib/dpkg/info/mariadb-common.conffiles /var/lib/systemd/deb-systemd-helper-enabled/mariadb.service.dsh-also /var/lib/systemd/deb-systemd-helper-enabled/multi-user.target.wants/mariadb.service takk~$
起動中かどうか確認してみます。
takk~$ systemctl list-unit-files | grep maria mariadb.service enabled mariadb@.service disabled takk~$
設定ファイル
設定ファイルを読んでみます。
takk~$ head /etc/mysql/mariadb.cnf # The MariaDB configuration file # # The MariaDB/MySQL tools read configuration files in the following order: # 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults, # 2. "/etc/mysql/conf.d/*.cnf" to set global options. # 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options. # 4. "~/.my.cnf" to set user-specific options. # # If the same option is defined multiple times, the last one will apply. # takk~$
番号が読み込み順ですね。
データベースユーザ
まずはrootユーザでログイン。
takk~$ mysql -uroot ERROR 1698 (28000): Access denied for user 'root'@'localhost' takk~$
現在rootユーザでないと拒否させるので注意。
sudoをつけてmysqlコマンドでrootユーザを指定します。
takk~$ sudo mysql -uroot Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 52 Server version: 10.3.22-MariaDB-0+deb10u1 Debian 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)]> select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | localhost | +------+-----------+ 1 row in set (0.000 sec) MariaDB [(none)]>
ユーザの追加
データベースユーザの追加は、権限を与えるデータベースと、ユーザアカウントとパスワードを指定します。
MariaDB [(none)]> grant all on mysql.* to takk@localhost identified by '1234'; Query OK, 0 rows affected (0.100 sec) MariaDB [(none)]>
これでtakkというユーザでログインできるようになりました。
takk~$ mysql -utakk -p1234 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 57 Server version: 10.3.22-MariaDB-0+deb10u1 Debian 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)]>
権限の表示
ユーザが保持している権限を表示するには、show grantsを使います。
MariaDB [(none)]> show grants for takk@localhost; +--------------------------------------------------------------------- | Grants for takk@localhost +--------------------------------------------------------------------- | GRANT USAGE ON *.* TO 'takk'@'localhost' IDENTIFIED BY PASSWORD '*A4 | GRANT ALL PRIVILEGES ON `mysql`.* TO 'takk'@'localhost' +--------------------------------------------------------------------- 2 rows in set (0.000 sec) MariaDB [(none)]>
ユーザの削除
takkユーザを削除してみます。
MariaDB [(none)]> select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | localhost | | takk | localhost | +------+-----------+ 2 rows in set (0.000 sec) MariaDB [(none)]>
ユーザの権限を無効化します。
MariaDB [(none)]> revoke all on mysql.* from takk@localhost; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]>
mysql.userからユーザを削除します。
MariaDB [(none)]> delete from mysql.user where user = 'takk' and host='localhost'; Query OK, 1 row affected (0.000 sec) MariaDB [(none)]>
mysql.userを確認します。
MariaDB [(none)]> select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | localhost | +------+-----------+ 1 row in set (0.000 sec) MariaDB [(none)]>
DB上は、ユーザtakkは削除されました。
まだメモリ上のユーザは有効になっていますので、以下のコマンドで確定させます。
MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]>
これでログインできなくなりました。
takk~$ mysql -utakk -p1234 ERROR 1698 (28000): Access denied for user 'takk'@'localhost' takk~$
データベース管理
データベースの一覧表示
SHOW DATABASESでデータベースの一覧を表示します。
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.000 sec) MariaDB [(none)]>
データベースの新規作成
データベースの作成には、CREATE DATABASEを使います。
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.000 sec) MariaDB [(none)]>
MariaDB [(none)]> create database my_db; Query OK, 1 row affected (0.000 sec) MariaDB [(none)]>
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | my_db | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.000 sec) MariaDB [(none)]>
データベースの削除
データベースを削除するには、DROP DATBASEを使います。
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | my_db | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.000 sec) MariaDB [(none)]>
MariaDB [(none)]> drop database my_db; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.000 sec) MariaDB [(none)]>
使用データベースの切替
USEを使ってデータベースを切り替えできます。
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | my_db1 | | my_db2 | | mysql | | performance_schema | +--------------------+ 5 rows in set (0.000 sec) MariaDB [(none)]>
MariaDB [(none)]> use my_db1; Database changed MariaDB [my_db1]>
テーブル操作
テーブルの作成
CREATE TABLE テーブル名 (カラム 型, …)
MariaDB [my_db1]> CREATE TABLE tbl_a (id int, name text); Query OK, 0 rows affected (0.006 sec) MariaDB [my_db1]>
テーブルの削除
DROP TABLE テーブル名
MariaDB [my_db1]> DROP TABLE tbl_a; Query OK, 0 rows affected (0.003 sec) MariaDB [my_db1]>
テーブルスキーマ表示
SHOW CREATE TABLE テーブル名
MariaDB [my_db1]> SHOW CREATE TABLE tbl_a; +-------+------------------------------------- | Table | Create Table +-------+------------------------------------- | tbl_a | CREATE TABLE `tbl_a` ( `id` int(11) DEFAULT NULL, `name` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+------------------------------------- 1 row in set (0.000 sec) MariaDB [my_db1]>
カラム操作
カラムの追加
ALTER TABLE テーブル名 ADD COLUMN カラム名 型
MariaDB [my_db1]> show create table tbl_a
-> ;
+-------+---------------------------------------
| Table | Create Table
+-------+---------------------------------------
| tbl_a | CREATE TABLE `tbl_a` (
`id` int(11) DEFAULT NULL,
`name` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+---------------------------------------
1 row in set (0.000 sec)
MariaDB [my_db1]> ALTER TABLE tbl_a ADD COLUMN kind int;
Query OK, 0 rows affected (0.002 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [my_db1]>
MariaDB [my_db1]> show create table tbl_a; +-------+-------------------------------------- | Table | Create Table +-------+-------------------------------------- | tbl_a | CREATE TABLE `tbl_a` ( `id` int(11) DEFAULT NULL, `name` text DEFAULT NULL, `kind` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+-------------------------------------- 1 row in set (0.000 sec) MariaDB [my_db1]>
カラムの削除
ALTER TABLE テーブル名 DROP COLUMN カラム名
MariaDB [my_db1]> show create table tbl_a; +-------+------------------------------------- | Table | Create Table +-------+------------------------------------- | tbl_a | CREATE TABLE `tbl_a` ( `id` int(11) DEFAULT NULL, `name` text DEFAULT NULL, `kind` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+------------------------------------- 1 row in set (0.000 sec) MariaDB [my_db1]>
MariaDB [my_db1]> ALTER TABLE tbl_a DROP COLUMN kind; Query OK, 0 rows affected (0.013 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [my_db1]> SHOW CREATE TABLE tbl_a; +-------+------------------------------------ | Table | Create Table +-------+------------------------------------ | tbl_a | CREATE TABLE `tbl_a` ( `id` int(11) DEFAULT NULL, `name` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+------------------------------------ 1 row in set (0.000 sec) MariaDB [my_db1]>
主キーの追加
ALTER TABLE テーブル名 ADD PRIMARY KEY(カラム名)
MariaDB [my_db1]> SHOW CREATE TABLE tbl_a; +-------+----------------------------------- | Table | Create Table +-------+----------------------------------- | tbl_a | CREATE TABLE `tbl_a` ( `id` int(11) DEFAULT NULL, `name` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+----------------------------------- 1 row in set (0.000 sec) MariaDB [my_db1]>
MariaDB [my_db1]> ALTER TABLE tbl_a ADD PRIMARY KEY(id); Query OK, 0 rows affected (0.010 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [my_db1]> SHOW CREATE TABLE tbl_a; +-------+---------------------------------- | Table | Create Table +-------+---------------------------------- | tbl_a | CREATE TABLE `tbl_a` ( `id` int(11) NOT NULL, `name` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+---------------------------------- 1 row in set (0.000 sec) MariaDB [my_db1]>
主キーの削除
ALTER TABLE テーブル名 DROP PRIMARY KEY
MariaDB [my_db1]> ALTER TABLE tbl_a DROP PRIMARY KEY; Query OK, 0 rows affected (0.017 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [my_db1]>
レコード抽出
レコード抽出
MariaDB [my_db1]> SELECT * FROM tbl_a WHERE id=1; +----+------+ | id | name | +----+------+ | 1 | AAA | +----+------+ 1 row in set (0.001 sec) MariaDB [my_db1]>
レコード操作
レコード追加
INSERT INTO テーブル名 (カラム名,…) VALUES (値,…)
MariaDB [my_db1]> INSERT INTO tbl_a (id,name) VALUES (1,'AAA'); Query OK, 1 row affected (0.001 sec) MariaDB [my_db1]> MariaDB [my_db1]> SELECT * FROM tbl_a; +----+------+ | id | name | +----+------+ | 1 | AAA | +----+------+ 1 row in set (0.000 sec) MariaDB [my_db1]>
レコード削除
MariaDB [my_db1]> SELECT * FROM tbl_a; +----+------+ | id | name | +----+------+ | 1 | AAA | | 2 | BBB | +----+------+ 2 rows in set (0.000 sec) MariaDB [my_db1]> SELECT * FROM tbl_a; +----+------+ | id | name | +----+------+ | 2 | BBB | +----+------+ 1 row in set (0.000 sec) MariaDB [my_db1]>
全レコード削除
TRUNCATE TABLE テーブル名
MariaDB [my_db1]> SELECT * FROM tbl_a; +----+------+ | id | name | +----+------+ | 2 | BBB | | 1 | AAA | | 3 | CCC | +----+------+ 3 rows in set (0.000 sec) MariaDB [my_db1]> TRUNCATE TABLE tbl_a; Query OK, 0 rows affected (0.011 sec) MariaDB [my_db1]> SELECT * FROM tbl_a; Empty set (0.000 sec) MariaDB [my_db1]>
バックアップとリストア
データベースのバックアップ
MariaDB [my_db1]> SELECT * FROM tbl_a; +----+------+ | id | name | +----+------+ | 1 | AAA | | 2 | BBB | | 3 | CCC | +----+------+ 3 rows in set (0.000 sec) MariaDB [my_db1]> exit Bye takk~$ mysqldump -uroot -p my_db1 > backup.sql Enter password: mysqldump: Got error: 1698: "Access denied for user 'root'@'localhost'" when trying to connect takk~$
takk~$ cat backup.sql -- MySQL dump 10.17 Distrib 10.3.22-MariaDB, for debian-linux-gnu (x86_64) -- -- Host: localhost Database: my_db1 -- ------------------------------------------------------ -- Server version 10.3.22-MariaDB-0+deb10u1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `tbl_a` -- DROP TABLE IF EXISTS `tbl_a`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_a` ( `id` int(11) NOT NULL, `name` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_a` -- LOCK TABLES `tbl_a` WRITE; /*!40000 ALTER TABLE `tbl_a` DISABLE KEYS */; INSERT INTO `tbl_a` VALUES (1,'AAA'),(2,'BBB'),(3,'CCC'); /*!40000 ALTER TABLE `tbl_a` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2020-06-14 18:35:24 takk~$
データベースのリストア
リストアは別のデータベースを指定してみます。
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | my_db1 | | my_db2 | | mysql | | performance_schema | +--------------------+ 5 rows in set (0.000 sec) MariaDB [(none)]> use my_db2; Database changed MariaDB [my_db2]> show tables; Empty set (0.000 sec) MariaDB [my_db2]> exit Bye takk~$ mysql my_db2 < backup.sql takk~$
MariaDB [my_db2]> SELECT * FROM tbl_a; +----+------+ | id | name | +----+------+ | 1 | AAA | | 2 | BBB | | 3 | CCC | +----+------+ 3 rows in set (0.000 sec) MariaDB [my_db2]>
コメント