ICT Diary

Network(主にCisco系)、Server(RedHat系)、Program(適当)を気まぐれにUPしていく。

CentOS7 MariaDB(Mysql)サーバ構築

Server

手順

  1. mariadbのインストール
  2. mariadbの初期化
  3. 日本語に対応(UTF-8))
  4. テスト
  5. mysqlの覚えておくと便利なコマンド集

詳細手順

mariadbのインストール

  • インストール Command

      yum install -y mariadb-server

  • インストール確認 Command

      rpm -qa |grep maria

    結果

      rpm -qa |grep maria
  mariadb-server-5.5.56-2.el7.x86_64
  mariadb-libs-5.5.56-2.el7.x86_64
  mariadb-5.5.56-2.el7.x86_64

  • 自動起動

    Command

      systemctl enable mariadb

    結果

      systemctl enable mariadb
  Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

  • サービス起動 Command

      systemctl start mariadb

  • mariadbの状態確認 Command

      stsystemctl status mariadb

    結果

          systemctl status mariadb
      ● mariadb.service - MariaDB database server
      Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
      Active: active (running) since 木 2018-07-19 11:06:56 JST; 3min 29s ago
      Process: 19982 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
      Process: 19903 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
      Main PID: 19981 (mysqld_safe)
      CGroup: /system.slice/mariadb.service
              tq19981 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
              mq20143 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plu...

      7月 19 11:06:54 test.hoge.com mariadb-prepare-db-dir[19903]: MySQL manual for more instructions.
      7月 19 11:06:54 test.hoge.com mariadb-prepare-db-dir[19903]: Please report any problems at http://mariadb.o...ira
      7月 19 11:06:54 test.hoge.com mariadb-prepare-db-dir[19903]: The latest information about MariaDB is availa...g/.
      7月 19 11:06:54 test.hoge.com mariadb-prepare-db-dir[19903]: You can find additional information about the ...at:
      7月 19 11:06:54 test.hoge.com mariadb-prepare-db-dir[19903]: http://dev.mysql.com
      7月 19 11:06:54 test.hoge.com mariadb-prepare-db-dir[19903]: Consider joining MariaDB's strong and vibrant ...ty:
      7月 19 11:06:54 test.hoge.com mariadb-prepare-db-dir[19903]: https://mariadb.org/get-involved/
      7月 19 11:06:54 test.hoge.com mysqld_safe[19981]: 180719 11:06:54 mysqld_safe Logging to '/var/log/mariadb...og'.
      7月 19 11:06:54 test.hoge.com mysqld_safe[19化

mariadbの初期化

  • 初期化 Command

      mysql_secure_installation

    結果

      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):   #rootを入力
  OK, successfully used password, moving on...

  Setting the root password ensures that nobody can log into the MariaDB
  root user without the proper authorisation.

  You already have a root password set, so you can safely answer 'n'.

  Change the 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]
  ... 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]
  ... Success!

  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]
  - 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]
  ... 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!

日本語に対応(UTF-8)

※設定の変更後は必ずサービスを再起動して設定を反映すること

サービスの再起動

systemctl restart mariadb

  • 現在の状況を確認

    1. DBにログイン

       mysql -u root -p
  Enter password:
  Welcome to the MariaDB monitor.  Commands end with ; or \g.
  Your MariaDB connection id is 17
  Server version: 5.5.56-MariaDB MariaDB Server

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

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

  MariaDB [(none)]>

    2. sqlを実行

       MariaDB [(none)]> show variables like 'char%';
  +--------------------------+----------------------------+
  | Variable_name            | Value                      |
  +--------------------------+----------------------------+
  | character_set_client     | utf8                       |
  | character_set_connection | utf8                       |
  | character_set_database   | latin1                     |
  | character_set_filesystem | binary                     |
  | character_set_results    | utf8                       |
  | character_set_server     | latin1                     |
  | character_set_system     | utf8                       |
  | character_sets_dir       | /usr/share/mysql/charsets/ |
  +--------------------------+----------------------------+
  8 rows in set (0.00 sec)

「character_set_server」と「character_set_database」の項目がutf8でないと文字化けする可能性がある

  • 設定を変更

    Command

       vi /etc/my.cnf

    変更前

      [mysqld]
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  # Disabling symbolic-links is recommended to prevent assorted security risks
  symbolic-links=0
  # Settings user and group are ignored when systemd is used.
  # If you need to run mysqld under a different user or group,
  # customize your systemd unit file for mariadb according to the
  # instructions in http://fedoraproject.org/wiki/Systemd

  [mysqld_safe]
  log-error=/var/log/mariadb/mariadb.log
  pid-file=/var/run/mariadb/mariadb.pid

  #
  # include all files from the config directory
  #
  !includedir /etc/my.cnf.d

    変更後

      [client]                        #変更点：追記
  default-character-set = utf8    #変更点：追記
  [mysqld]
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  character-set-server = utf8     #変更点：追記
  # Disabling symbolic-links is recommended to prevent assorted security risks
  symbolic-links=0
  # Settings user and group are ignored when systemd is used.
  # If you need to run mysqld under a different user or group,
  # customize your systemd unit file for mariadb according to the
  # instructions in http://fedoraproject.org/wiki/Systemd

  [mysqld_safe]
  log-error=/var/log/mariadb/mariadb.log
  pid-file=/var/run/mariadb/mariadb.pid

  #
  # include all files from the config directory
  #
  !includedir /etc/my.cnf.d

  • 変更の確認

      MariaDB [(none)]> show variables like 'char%';
  +--------------------------+----------------------------+
  | Variable_name            | Value                      |
  +--------------------------+----------------------------+
  | character_set_client     | utf8                       |
  | character_set_connection | utf8                       |
  | character_set_database   | utf8                       |
  | character_set_filesystem | binary                     |
  | character_set_results    | utf8                       |
  | character_set_server     | utf8                       |
  | character_set_system     | utf8                       |
  | character_sets_dir       | /usr/share/mysql/charsets/ |
  +--------------------------+----------------------------+
  8 rows in set (0.00 sec)

  • おまけ 「utf8」は現在は古い型で、今は「utf8mb4」新しい型があるこちらの方が多くの日本語に対応している

      - 設定例

          [client]                        #変更点：追記
          default-character-set = utf8mb4 #変更点：追記
          [mysqld]
          datadir=/var/lib/mysql
          socket=/var/lib/mysql/mysql.sock
          character-set-server = utf8mb4  #変更点：追記
          # Disabling symbolic-links is recommended to prevent assorted security risks
          symbolic-links=0
          # Settings user and group are ignored when systemd is used.
          # If you need to run mysqld under a different user or group,
          # customize your systemd unit file for mariadb according to the
          # instructions in http://fedoraproject.org/wiki/Systemd

          [mysqld_safe]
          log-error=/var/log/mariadb/mariadb.log
          pid-file=/var/run/mariadb/mariadb.pid

          #
          # include all files from the config directory
          #
          !includedir /etc/my.cnf.d

  - 設定確認

          MariaDB [(none)]> show variables like 'char%';
          +--------------------------+----------------------------+
          | Variable_name            | Value                      |
          +--------------------------+----------------------------+
          | character_set_client     | utf8mb4                    |
          | character_set_connection | utf8mb4                    |
          | character_set_database   | utf8mb4                    |
          | character_set_filesystem | binary                     |
          | character_set_results    | utf8mb4                    |
          | character_set_server     | utf8mb4                    |
          | character_set_system     | utf8                       |
          | character_sets_dir       | /usr/share/mysql/charsets/ |
          +--------------------------+----------------------------+
          8 rows in set (0.00 sec)

テスト

  1. DBの作成 「hoge」という名前のデータベースを作成

     MariaDB [(none)]>create database hoge;
 Query OK, 1 row affected (0.00 sec)

  2. DBの切り替え 使用するDBを指定する

     MariaDB [(none)]>use hoge
 Database changed

  3. テーブルの作成

    テーブル名:sample |列名|型|主キー| |:-:|:-:|:-:| |code|int(5)|○| |name|char(10)|| ||||

     MariaDB [hoge]>create table sample(code int(5) primary key, name char(10));
 Query OK, 0 rows affected (0.00 sec)

  4. データの挿入

     MariaDB [hoge]>insert into sample(code,name) values(1,'山田');
 Query OK, 1 row affected (0.00 sec)

  5. データの確認

     MariaDB [hoge]> select * from sample;
 +------+--------+
 | code | name   |
 +------+--------+
 |    1 | 山田   |   ←正常に漢字が格納されている
 +------+--------+
 1 row in set (0.00 sec)

mysqlの覚えておくと便利なコマンド集

  • show variables like 'char%'; データベースの文字コードにおける設定を確認

    • 使用条件 DBの切り替え後、切り替え前共に使用可能

        show variables like 'char%';
  +--------------------------+----------------------------+
  | Variable_name            | Value                      |
  +--------------------------+----------------------------+
  | character_set_client     | utf8mb4                    |
  | character_set_connection | utf8mb4                    |
  | character_set_database   | utf8mb4                    |
  | character_set_filesystem | binary                     |
  | character_set_results    | utf8mb4                    |
  | character_set_server     | utf8mb4                    |
  | character_set_system     | utf8                       |
  | character_sets_dir       | /usr/share/mysql/charsets/ |
  +--------------------------+----------------------------+

  • show databases; DBサーバに作成されたDB一覧を表示

    • 使用条件 DBの切り替え後、切り替え前共に使用可能

        MariaDB [hoge]> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | hoge               |
  | mysql              |
  | performance_schema |
  +--------------------+

  • show tables; DBに作成されたテーブルを一覧表示

    • 使用条件 DBの切り替え後、切り替え前共に使用可能

         MariaDB [hoge]> show tables;
   +----------------+
   | Tables_in_hoge |
   +----------------+
   | sample         |
   +----------------+

  • show columns from [テーブル名] {from [DB名]}; 指定したテーブルの列構造を表示

    • 使用条件 DBの切り替え後、切り替え前共に使用可能 {}のオプションを使用することにより切り替え前でも使用可能

    • 切り替え前使用

        MariaDB [(none)]> show columns from sample from hoge;
  +-------+----------+------+-----+---------+-------+
  | Field | Type     | Null | Key | Default | Extra |
  +-------+----------+------+-----+---------+-------+
  | code  | int(5)   | NO   | PRI | NULL    |       |
  | name  | char(10) | YES  |     | NULL    |       |
  +-------+----------+------+-----+---------+-------+

    • 切り替え後使用

        MariaDB [hoge]> show columns from sample;
  +-------+----------+------+-----+---------+-------+
  | Field | Type     | Null | Key | Default | Extra |
  +-------+----------+------+-----+---------+-------+
  | code  | int(5)   | NO   | PRI | NULL    |       |
  | name  | char(10) | YES  |     | NULL    |       |
  +-------+----------+------+-----+---------+-------+