refirio.org

■MySQL

# yum remove mysql-* # rm -rf /var/lib/mysql/ … 必要に応じて

MySQL MariaDB 5.5 5.5 / 10.0 5.6 10.1 5.7 10.2

# yum -y install http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm

# yum -y install mysql-community-server … mysql-serverをインストール（利用できない場合は「yum -y install mysql-server」とする） # mysqld --version … MySQLのインストールを確認（エラーになる場合は「mysql --version」とする） # service mysqld start … MySQL起動 # chkconfig mysqld on … MySQL自動起動設定 # vi /var/log/mysqld.log … MySQL初期rootパスワードを確認（MySQL5.7以降） # vi /etc/my.cnf … MySQLの設定ファイルを編集（初回起動前に編集すると、起動時にエラーになることがある。詳細は後述） # service mysqld restart … MySQL再起動

# mysql_secure_installation … MySQL初期設定 Enter password for user root: … /var/log/mysqld.log に書かれた初期rootパスワードを入力（MySQL5.7以降） New password: … 新しいパスワードを入力（MySQL5.7以降） Re-enter new password: … 以降はMySQL5.7より前の内容だが、5.7以降でも基本的な流れは同じ NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MySQL to secure it, we'll need the current password for the root user. If you've just installed MySQL, 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): … 空Enter OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MySQL root user without the proper authorisation. Set root password? [Y/n] … 空Enter（rootパスワード設定） New password: … rootパスワード入力 Re-enter new password: … rootパスワード入力確認 Password updated successfully! Reloading privilege tables.. ... Success! By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL 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] … 空Enter（匿名ユーザー削除） ... 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] … 空Enter（リモートからのrootログイン禁止） ... Success! By default, MySQL 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] … 空Enter（testデータベース削除） - 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] … 空Enter（設定再読み込み） ... Success! Cleaning up... All done! If you've completed all of the above steps, your MySQL installation should now be secure. Thanks for using MySQL!

# yum -y install mariadb-server # mysqld --version … 以降はMySQLと同じ（エラーになる場合は「mysql --version」とする） # systemctl start mariadb # systemctl enable mariadb

# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash # yum -y install MariaDB-server MariaDB-client # systemctl start mariadb # systemctl enable mariadb

$ sudo mysql -u root

# mariadb-secure-installation … MariaDB初期設定 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 haven't set the root password yet, you should just press enter here. Enter current password for root (enter for none): … 空Enter（もしrootにパスワードを設定済みなら入力） OK, successfully used password, moving on... Setting the root password or using the unix_socket ensures that nobody can log into the MariaDB root user without the proper authorisation. You already have your root account protected, so you can safely answer 'n'. Switch to unix_socket authentication [Y/n] Y … Yを入力（unix_socket認証に切り替え） Enabled successfully! Reloading privilege tables.. ... Success! You already have your root account protected, so you can safely answer 'n'. Change the root password? [Y/n] Y … Yを入力（rootパスワードを変更） New password: … rootパスワード入力 Re-enter new password: … rootパスワード入力確認 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] Y … Yを入力（匿名ユーザーを削除） ... 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] Y … Yを入力（リモートからのrootログインを禁止） ... 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] Y … Yを入力（testデータベースを削除） - 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] Y … Yを入力（設定を再読み込み） ... 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!

# mysql -u root -p … MySQLへrootでログイン Enter password: … MySQLのrootパスワードを入力 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 to server version: 4.1.12 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> GRANT ALL PRIVILEGES ON test.* TO webmaster@localhost IDENTIFIED BY 'gV0+8k6BM#z7'; … testへのすべてのアクセス権限を持った、ユーザwebmasterを作成（MySQL5.6以前） mysql> CREATE USER webmaster@localhost IDENTIFIED BY 'gV0+8k6BM#z7'; … MySQL5.7からはユーザを作成してからGRANTする必要がある mysql> GRANT ALL PRIVILEGES ON test.* TO webmaster@localhost; mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, EVENT ON test.* TO webmaster@localhost; … 環境によっては「ALL PRIVILEGES」で一括指定するとエラーになるので、必要なものを個別に指定する mysql> CREATE USER webmaster IDENTIFIED BY 'gV0+8k6BM#z7'; … 接続元を限定しない場合の指定（多くのサーバからデータベースに接続する場合など） mysql> GRANT ALL PRIVILEGES ON test.* TO webmaster; mysql> FLUSH PRIVILEGES; mysql> SET PASSWORD FOR webmaster@localhost=PASSWORD('abcd'); … webmaster@localhostのパスワードを「abcd」に変更する場合 mysql> REVOKE ALL PRIVILEGES ON test.* FROM webmaster; … webmasterユーザからtestへのアクセス権限を剥奪する場合 mysql> GRANT SELECT ON `test`.* TO viewer@localhost IDENTIFIED BY 'gV0+8k6BM#z7'; … 特定データベースのみSELECTを許可する場合 mysql> REVOKE SELECT ON `test`.* FROM viewer@localhost; … 権限を剥奪する場合 mysql> GRANT SELECT ON `test`.`sample` TO viewer@localhost IDENTIFIED BY 'gV0+8k6BM#z7'; … 特定データベースの特定テーブルのみSELECTを許可する場合 mysql> REVOKE SELECT ON `test`.`sample` FROM viewer@localhost; … 権限を剥奪する場合 mysql> SELECT host, user FROM mysql.user WHERE user='webmaster'; … webmasterユーザ登録確認 +-----------+-----------+ | host | user | +-----------+-----------+ | localhost | webmaster | +-----------+-----------+ 1 row in set (0.00 sec) mysql> SHOW GRANTS FOR 'webmaster'@'localhost'; … webmasterユーザ権限確認 +------------------------------------------------------------------------------------------------------------------+ | Grants for webmaster@localhost | +------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'webmaster'@'localhost' IDENTIFIED BY PASSWORD '*76DD0CE725E300814169889CDB512FA9DBAAB9C3' | | GRANT ALL PRIVILEGES ON `test`.* TO 'webmaster'@'localhost' | +------------------------------------------------------------------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> QUIT … ログアウト Bye # mysql -u webmaster -p … webmasterユーザでMySQLサーバーへログイン Enter password: … webmasterのパスワードを入力 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 to server version: 4.1.12 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE DATABASE test; … testデータベース作成（文字コードを指定しない場合） mysql> CREATE DATABASE test DEFAULT CHARACTER SET utf8; … testデータベース作成（文字コードを指定する場合） Query OK, 1 row affected (0.00 sec) mysql> SHOW DATABASES; … データベース作成確認 +----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.01 sec) mysql> USE test … testデータベースへ接続 Database changed mysql> QUIT … ログアウト Bye

CREATE TABLE table_test( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID', text VARCHAR(255) NOT NULL COMMENT 'テキスト', PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'テーブル操作テスト'; INSERT INTO table_test(text) VALUES('テストメッセージ1'); INSERT INTO table_test(text) VALUES('テストメッセージ2');

# yum -y install --enablerepo=remi-php73 php-mysql … MySQLドライバをインストール（RemiリポジトリからPHP7.3をインストールした場合） # yum -y install --enablerepo=remi-php73 php-pdo … PDOをインストール（RemiリポジトリからPHP7.3をインストールした場合） # yum -y install php-mysql … MySQLドライバをインストール（PHP5の場合） # yum -y install --enablerepo=webtatic-testing php70w-pdo … PODをインストール（WebtaticリポジトリからにPHP7.0をインストールした場合） # systemctl restart httpd … httpdを再起動 # vi /var/www/html/mysql.php … 動作確認用にプログラムを作成

mysql> SHOW GRANTS FOR webmaster; … webmasterユーザの権限を確認 +-----------------------------------------------------------------------+ | Grants for webmaster@% | +-----------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'webmaster'@'%' IDENTIFIED BY PASSWORD <secret> | | GRANT ALL PRIVILEGES ON `test`.* TO 'webmaster'@'%' | +-----------------------------------------------------------------------+ 2 rows in set (0.01 sec) mysql> GRANT ALL PRIVILEGES ON sample.* TO webmaster; … webmasterユーザにsampleデータベースの権限を追加 Query OK, 0 rows affected (0.02 sec) mysql> SHOW GRANTS FOR webmaster; … webmasterユーザの権限を再度確認 +-----------------------------------------------------------------------+ | Grants for webmaster@% | +-----------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'webmaster'@'%' IDENTIFIED BY PASSWORD <secret> | | GRANT ALL PRIVILEGES ON `test`.* TO 'webmaster'@'%' | | GRANT ALL PRIVILEGES ON `sample`.* TO 'webmaster'@'%' | +-----------------------------------------------------------------------+ 3 rows in set (0.01 sec)

character-set-server=utf8mb4

CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4;

CREATE TABLE char_test( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID', text VARCHAR(255) NOT NULL COMMENT 'テキスト', PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '文字コードテスト'; INSERT INTO char_test(text) VALUES('4バイト文字を含むテキスト');

CREATE TABLE char_test( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID', text VARCHAR(255) NOT NULL COMMENT 'テキスト', PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '文字コードテスト'; INSERT INTO char_test(text) VALUES('4バイト文字を含むテキスト');

# service mysqld start MySQL データベースを初期化中: 2018-02-17T06:34:54.109416Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2018-02-17T06:34:54.110978Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting. 2018-02-17T06:34:54.111088Z 0 [ERROR] Aborting [失敗]