MySQL on Ubuntu 20.04

目標

mysqlを初めて起動するところから、データベースとテーブルを作成し、値の挿入と確認までを行います。

root パスワードの設定

ubuntu:~ $ sudo -i
root:~# sudo /usr/bin/mysql_secure_installation

# root にパスワードを設定していない場合は Enter
Enter current password for root (enter for none): 

# root に新しいパスワードを設定
Set root password? [Y/n] y
New password: 
Re-enter new password: 

Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] n
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y

データベースとユーザの作成

データベース test、ユーザ ubuntu を作成します。

ubuntu:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu)
<snip>

mysql> 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> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER 'ubuntu'@'localhost' IDENTIFIED BY 'ubuntu';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON test.* TO 'ubuntu'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user,plugin FROM user;
+------------------+-----------------------+
| user             | plugin                |
+------------------+-----------------------+
| debian-sys-maint | caching_sha2_password |
| mysql.infoschema | caching_sha2_password |
| mysql.session    | caching_sha2_password |
| mysql.sys        | caching_sha2_password |
| root             | mysql_native_password |
| ubuntu           | caching_sha2_password |
+------------------+-----------------------+
6 rows in set (0.00 sec)

mysql> quit
Bye

テーブルの作成

テーブル名 table1 の、型がそれぞれ datetime, char(10), integer のテーブルを作成します。

ubuntu:~$ mysql -u ubuntu -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
<snip>

mysql> USE test;
Database changed
mysql> CREATE TABLE table1 (time DATETIME, value CHAR(10), number INTEGER );
Query OK, 0 rows affected (0.01 sec)

mysql> DESCRIBE table1;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| time   | datetime | YES  |     | NULL    |       |
| value  | char(10) | YES  |     | NULL    |       |
| number | int      | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

値を挿入する

先ほど作成した table1 に値を入れます。

mysql> INSERT INTO table1 VALUE ('2020-09-25 00:51:20','test-char',777);
Query OK, 1 row affected (0.02 sec)

テーブルを確認する

mysql> SELECT * FROM table1;
+---------------------+-----------+--------+
| time                | value     | number |
+---------------------+-----------+--------+
| 2020-09-25 00:51:20 | test-char |    777 |
+---------------------+-----------+--------+
1 row in set (0.00 sec)

環境

ubuntu:~$ uname -a
Linux ubuntu 5.4.0-48-generic #52-Ubuntu SMP Thu Sep 10 10:58:49 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

ubuntu:~$ cat /etc/issue
Ubuntu 20.04.1 LTS \n \l

ubuntu:~$ mysql --version
mysql  Ver 8.0.21-0ubuntu0.20.04.4 for Linux on x86_64 ((Ubuntu))