はじめに

過去に設定経験がある人向けの、MySQLの基本的操作の個人的な備忘録です。
いろいろなデータベースの構築を行う事があるため、よく混乱するのでごく簡単にまとめます。

環境

amazonLinux2 : 4.14.214-160.339.amzn2.x86_64 にあわせて追記修正

インストール

mariaDBとは共存できないので、インストールされていれば削除する。


# rm -rf /var/lib/mysql/

(CentOS7の場合)

インストールするバージョンを確認

yum info mysql-community-server

(CentOS7の場合)

インストールしたいバージョンでなければ、該当バージョンのリポジトリをインストールする。

(CentOS7に、MySQL-5.7を入れる場合)
yum localinstall http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm

インストール実行

sudo yum install mysql-community-server

(Ubuntuの場合)
sudo apt-get install mysql-server

起動と停止

sudo systemctl enable mysqld.service    // 自動起動設定
sudo systemctl start mysqld.service     // 起動
sudo systemctl stop mysqld.service      // 停止

MySQL基本設定

ルートパスワードの設定

　Ubuntu-16.04 LTS 等では、aptでインストールする時にrootのパスワードを設定させられる。
もし root のパスワードが未設定ならば、なにはともあれ最優先で root のパスワードをつける。このときは
mysql_secure_installation で設定するのが一番手っ取り早い。

$ mysql_secure_installation

mysqlコマンドからNEWPASSWORDを設定する場合

$ mysql -h localhost -u root

mysql> update mysql.user set password=password('NEWPASSWORD') where user = 'root';
mysql> flush privileges;   
mysql> exit;

（参考）環境によっては予めパスワードが設定されている場合がある。たとえば、DBユーザrootのパスワードとして、ログインユーザrootの初期ログインパスワードが設定されている場合がある。

（追記）mysql-5.7の初期パスワードはインストール時にランダムで設定されているので、下のようにログから確認する

sudo grep 'temporary password' /var/log/mysqld.log

登録済ユーザ一覧取得

mysql> SELECT host,user FROM mysql.user;

ユーザ追加

# 基本書式：GRANT 権限 ON *.* TO ユーザ名 IDENTIFIED BY 'パスワード'　WITH GRANT OPTION;
# 全権限ユーザの追加
mysql> GRANT ALL PRIVILEGES ON *.* TO [newuser]@[host] IDENTIFIED BY '[password]' WITH GRANT OPTION;
# リードオンリー（selectのみ）権限ユーザの追加
mysql? GRANT SELECT ON *.* TO <ユーザ名>@<接続元> IDENTIFIED BY '[passwowd]";

ユーザ[user]の権限確認

mysql> SHOW GRANTS FOR [user];

文字コード設定

設定ファイル my.cnf のデフォルト文字コードを確認（下記はutf8にする場合）

[mysqld]
character_set_server=utf8  #mysqldセクションに追加
skip-character-set-client-handshake
collation-server = utf8_general_ci
init-connect = SET NAMES utf8

[client]
default-character-set=utf8  #clientセクションを追加

[mysqldump]
default-character-set=utf8  #mysqldumpセクションを追加

MySQL基本操作

データベースにアクセス

mysql -h HOST --port PORT -u USER -p DBName

HOST／PORTを省略したらlocalhost:3306 にアクセスする。パスワード設定していなければ –p はなしだが、この状態は非常に危険なのですぐに対処すること。
DBNameを省略するとデータベース指定なしで接続する。

データベースのダンプ

$ mysqldump -h HOST -u USER -p DBName > dumpfile

・ DBName の代わりに –all-databases を指定すると全データベースをダンプする。
・-–no-data –skip-dump-date 定義のみを出力する。
・--routines (-R)でストアドルーチン (プロシージャーおよび関数) を出力に含める。
・ –no-data –skip-dump-date –no-create-info –routines でプロシージャのみを出力

データベースのレストア

$ mysql -h HOST -u root -p DBName < dumpfile   # DNNameデータベースをリストア
$ mysql -h HOST -u root -p < dumpfile　        # 全データベースのバックアップを復元

外部参照キーを設定している場合は、mysqldumpで作ったファイルそのままではエラーになるので、一時的に外部参照キーを無視してダンプしたファイルを読み込む。

$ mysql -h HOST -u root -p DBName
MySQL>  SET foreign_key_checks = 0;
MySQL>  SOURCE dumpfile;
MySQL>  SET foreign_key_checks = 1;

プロシージャ／ファンクション

mysql> show procedure status;   # ストアドプロシージャの一覧
mysql> show function status;    # ファンクションの一覧

文字コード設定変更

文字化けが発生した場合は、文字コード設定を確認する。

全体の文字設定を確認

mysql> show global variables like 'character%';
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+

utf8の場合はこのようになっていればOK。latin1等がある場合は /etc/my.cnf の設定を見直し、再起動で反映させる。

データベースの文字コード設定を確認

データベースを指定し、クライアント、接続、データベースの文字コードを確認する。

mysql> use exampleDB;
mysql> show variables like "chara%";
       :

データベースの文字コード設定の変更

ALTER DATABASE `exampleDB` default character set utf8mb4;

既に作成されているテーブルの文字コードも変更する必要があるので、状況次第ではデータベースごと再作成したほうが早い。

テーブル/カラムの文字コードの確認

mysql> show create table exampleTable \G;

データベースの比較

mysqldiffコマンドでデータベースの構成が比較できる

$ mysqldiff -h
Usage: mysqldiff --server1=user:pass@host:port:socket --server2=user:pass@host:port:socket db1.object1:db2.object1 db3:db4

データベースの初期化

バージョン依存の解決やMariaDBへの移行する場合などは、データベースを初期化したほうが早い場合がある。必要なバックアップを取得後、mysqlを停止し、以下のコマンドを実行。

cd /var/lib/mysql; rm -rf *   # mysql/ の中身を消す
sudo -u mysql mysql_install_db --datadir=/var/lib/mysql  # 初期化

mysql 超基本備忘録