Return to Tech/database

Memo

とりあえずSELECTを試してみたい

$ mysql -u root -p パスワード データベース名 -e "SELECT * FROM テーブル名"

パスワード不要な場合
$ mysql -u root データベース名 -e "SELECT * FROM テーブル名"


テキストデータをインポートしたい

$ cat insert_sample.txt
insert into testtable values(1, 'test001');
insert into testtable values(2, 'test002');
insert into testtable values(3, 'test003');


上記ファイルを投入します
$ mysql -u root -p パスワード データベース名 < insert_sample.txt
MySQL interpreter 
MySQLユーザを作成したい

mysql> create user 'username'@'192.168.1.254';
Query OK, 0 rows affected (0.32 sec)

mysql> select user, host from user where user = 'username';
+----------+---------------+
| user     | host          |
+----------+---------------+
| username | 192.168.1.254 |
+----------+---------------+
1 row in set (0.03 sec)

ユーザの権限を確認したい

mysql> show grants for 'username'@'localhost';
+---------------------------------------------------+
| Grants for username@192.168.1.254                 |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `username`@`192.168.1.254` |
+---------------------------------------------------+
1 row in set (0.00 sec)


権限調整
mysql> grant all privileges on database01.* to 'username'@'192.168.1.254';

mysql> show grants for 'username'@'192.168.1.254';
+-----------------------------------------------------------------------+
| Grants for username@192.168.1.254                                     |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `username`@`192.168.1.254`                     |
| GRANT ALL PRIVILEGES ON `database01`.* TO `username`@`192.168.1.254` |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

ユーザーのパスワードを変更したい
mysql> set password for 'username'@'192.168.1.254' = 'password';
Query OK, 0 rows affected (0.13 sec)

設定反映(flush)

mysql> flush privileges;
Query OK, 0 rows affected (0.12 sec)

mysqlクライアントを用いた遠隔DBサーバへの接続例

# mysql -u username -h 192.168.1.253 -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.21 Source distribution

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

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

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| database01         |
+--------------------+
2 rows in set (0.051 sec)
Return to Tech/database