![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
#author("2018-08-26T16:30:07+09:00","","") #author("2018-08-26T16:40:50+09:00","","") [[CentOS7]] *データベースサーバー構築(MariaDB) [#jc1e28c8] データベースサーバーは、サーバー上のデータベースをクライアントから操作できるようにするためのサーバー。 ここでは、リレーショナル型データベースのデータベースサーバーである''MariaDB''を使用する。 *MariaDBインストール [#t6789e00] |BGCOLOR(black):COLOR(white):|c |[root@localhost ~]# yum -y install mariadb-server &color(lime){← ''mariadb-server''インストール};| *MariaDB設定 [#g476dd74] |BGCOLOR(black):COLOR(white):|c |[root@localhost ~]# vi /etc/my.cnf.d/server.cnf &color(lime){← ''MariaDB''設定ファイル編集};| |[mysqld] &br; character-set-server = utf8 &color(lime){← 追加(''MariaDB''サーバーの文字コードを''UTF-8''にする)};| *MariaDB起動 [#k90a8359] |BGCOLOR(black):COLOR(white):|c |[root@localhost ~]# systemctl start mariadb &color(lime){← ''MariaDB''起動};| |[root@localhost ~]# systemctl enable mariadb &color(lime){← ''MariaDB''自動起動設定};| *MariaDB初期設定 [#ha607fae] |BGCOLOR(black):COLOR(white):|c |[root@localhost ~]# mysql_secure_installation &color(lime){← ''MariaDB''初期設定};| |/usr/bin/mysql_secure_installation: 行 379: find_mysql_client: コマンドが見つかりません &br; &br; NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB &br; SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! &br; &br; In order to log into MariaDB to secure it, we'll need the current &br; password for the root user. If you've just installed MariaDB, and &br; you haven't set the root password yet, the password will be blank, &br; so you should just press enter here. &br; &br; Enter current password for root (enter for none): &color(lime){← 空ENTER}; &br; OK, successfully used password, moving on... &br; &br; Setting the root password ensures that nobody can log into the MariaDB &br; root user without the proper authorisation. &br; &br; Set root password? [Y/n] &color(lime){← 空ENTER(rootパスワード設定)}; &br; New password: &color(lime){← 任意の''root''パスワードを応答}; &br; Re-enter new password: &color(lime){← 上記と同じパスワードを応答(確認)}; &br; Password updated successfully! &br; Reloading privilege tables.. &br; ... Success! &br; &br; &br; By default, a MariaDB installation has an anonymous user, allowing anyone &br; to log into MariaDB without having to have a user account created for &br; them. This is intended only for testing, and to make the installation &br; go a bit smoother. You should remove them before moving into a &br; production environment. &br; &br; Remove anonymous users? [Y/n] &color(lime){← 空ENTER(匿名ユーザー削除)}; &br; ... Success! &br; &br; Normally, root should only be allowed to connect from 'localhost'. This &br; ensures that someone cannot guess at the root password from the network. &br; &br; Disallow root login remotely? [Y/n] &color(lime){← 空ENTER(リモートからの''root''ログイン禁止)}; &br; ... Success! &br; &br; By default, MariaDB comes with a database named 'test' that anyone can &br; access. This is also intended only for testing, and should be removed &br; before moving into a production environment. &br; &br; Remove test database and access to it? [Y/n] &color(lime){← 空ENTER(''test''データベース削除)}; &br; - Dropping test database... &br; ... Success! &br; - Removing privileges on test database... &br; ... Success! &br; &br; Reloading the privilege tables will ensure that all changes made so far &br; will take effect immediately. &br; &br; Reload privilege tables now? [Y/n] &color(lime){← 空ENTER}; &br; ... Success! &br; &br; Cleaning up... &br; &br; All done! If you've completed all of the above steps, your MariaDB &br; installation should now be secure. &br; &br; Thanks for using MariaDB!| *MariaDB確認 [#c1e0fb71] |BGCOLOR(black):COLOR(white):|c |[root@localhost ~]# mysql -u root -p &color(lime){← ''MariaDB''へ''root''でログイン};| |Enter password: &color(lime){← ''MariaDB''の''root''パスワード応答}; &br; Welcome to the MariaDB monitor. Commands end with ; or \g. &br; Your MariaDB connection id is 12 &br; Server version: 5.5.37-MariaDB MariaDB Server &br; &br; Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others. &br; &br; Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.| |MariaDB [(none)]> grant all privileges on test.* to hogehoge@localhost identified by 'hogehogepass'; &color(lime){← ''test''データベースへの全てのアクセス権限を持った、新規ユーザーhogehogeを登録};| |Query OK, 0 rows affected (0.00 sec)| |MariaDB [(none)]> select user from mysql.user where user='hogehoge'; &color(lime){← hogehogeユーザー登録確認}; | |+--------+ &br; | user | &br; +--------+ &br; | hogehoge | &br; +--------+ &br; 1 row in set (0.00 sec)| |MariaDB [(none)]> exit &color(lime){← ログアウト};| |Bye| |[root@localhost ~]# mysql -u hogehoge -phogehogepass &color(lime){← ''MariaDB''へhogehogeユーザーでログイン};| |Welcome to the MariaDB monitor. Commands end with ; or \g. &br; Your MariaDB connection id is 13 &br; Server version: 5.5.37-MariaDB MariaDB Server &br; &br; Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others. &br; &br; Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.| |MariaDB [(none)]> create database test; &color(lime){← ''test''データベース作成};| |Query OK, 1 row affected (0.00 sec)| |MariaDB [(none)]> show databases; &color(lime){← データベース作成確認};| |+--------------------+ &br; | Database | &br; +--------------------+ &br; | information_schema | &br; | test | &br; +--------------------+ &br; 2 rows in set (0.00 sec)| |MariaDB [(none)]> use test; &color(lime){← ''test''データベースへ接続};| |Database changed| |MariaDB [test]> create table test(num int, name varchar(50)); &color(lime){← ''test''テーブル作成};| |Query OK, 0 rows affected (0.09 sec)| |MariaDB [test]> show tables; &color(lime){← テーブル作成確認};| |+----------------+ &br; | Tables_in_test | &br; +----------------+ &br; | test | &br; +----------------+ &br; 1 row in set (0.00 sec)| |MariaDB [test]> insert into test values(1,'山田太郎'); &color(lime){← ''test''テーブルへデータ登録};| |Query OK, 1 row affected, 1 warning (0.01 sec)| |MariaDB [test]> select * from test; &color(lime){← データ登録確認};| |+------+--------------+ &br; | num | name | &br; +------+--------------+ &br; | 1 | 山田太郎 | &br; +------+--------------+ &br; 1 row in set (0.00 sec)| |MariaDB [test]> update test set name='山田次郎'; &color(lime){← ''test''テーブル内データ更新};| |Query OK, 1 row affected (0.03 sec) &br; Rows matched: 1 Changed: 1 Warnings: 0| |MariaDB [test]> select * from test; &color(lime){← データ更新確認};| |+------+--------------+ &br; | num | name | &br; +------+--------------+ &br; | 1 | 山田次郎 | &br; +------+--------------+ &br; 1 row in set (0.00 sec)| |MariaDB [test]> delete from test where num=1; &color(lime){← ''test''テーブル内データ削除};| |Query OK, 1 row affected (0.03 sec)| |MariaDB [test]> select * from test; &color(lime){← データ削除確認};| |Empty set (0.00 sec)| |MariaDB [test]> drop table test; &color(lime){← ''test''テーブル削除};| |Query OK, 0 rows affected (0.02 sec)| |MariaDB [test]> show tables; &color(lime){← テーブル削除確認};| |Empty set (0.00 sec)| |MariaDB [test]> drop database test; &color(lime){← データベースtest削除};| |Query OK, 0 rows affected (0.00 sec)| |MariaDB [(none)]> show databases; &color(lime){← データベース削除確認};| |+--------------------+ &br; | Database | &br; +--------------------+ &br; | information_schema | &br; +--------------------+ &br; 1 row in set (0.01 sec)| |MariaDB [(none)]> exit &color(lime){← ログアウト};| |Bye| |[root@localhost ~]# mysql -u root -p &color(lime){← ''MariaDB''へ''root''でログイン};| |Enter password: &color(lime){← ''MariaDB''の''root''パスワード応答}; &br; Welcome to the MariaDB monitor. Commands end with ; or \g. &br; Your MariaDB connection id is 4 &br; Server version: 5.5.37-MariaDB MariaDB Server &br; &br; Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others. &br; &br; Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.| |MariaDB [(none)]> revoke all privileges on *.* from hogehoge@localhost; &color(lime){← hogehogeユーザーから全てのデータベースへのアクセス権限を剥奪};| |Query OK, 0 rows affected (0.00 sec)| |MariaDB [(none)]> delete from mysql.user where user='hogehoge' and host='localhost'; &color(lime){← hogehogeユーザー削除};| |Query OK, 1 row affected (0.00 sec)| |MariaDB [(none)]> select user from mysql.user where user='hogehoge'; &color(lime){← hogehogeユーザー削除確認};| |Empty set (0.00 sec)| |MariaDB [(none)]> flush privileges; &color(lime){← hogehogeユーザーの削除を''MariaDB''サーバーへ反映};| |Query OK, 0 rows affected (0.00 sec)| |MariaDB [(none)]> exit &color(lime){← ログアウト};| |Bye|
