Database/MySQL のバックアップ(No.1)

バックアップ一覧
差分を表示
現在との差分を表示
ソースを表示
Database/MySQL へ行く。
- 1 (2023-11-14 (火) 13:53:32)

MySQL

MySQL（MariaDB）について
MariaDB導入事例
MySQL導入事例
文字コードについて
phpMyAdmin で管理
Terminal 接続で管理
- 接続と基本操作
- データベースのバックアップとリストア
MySQL(Mariadb)のデータ領域

MySQL（MariaDB）について

MySQLは、オープンソースのリレーショナル型データベース管理システム (RDBMS) の一つで、世界でもっとも普及しているものです。また、MariaDBは、MySQLをフォークして立ち上げられたプロジェクトが開発する派生版です。いずれもオープンソースとして開発されています。

MySQL：https://www.mysql.com/jp/
MariaDB：https://mariadb.com/

付記：MariaDB について
MySQLはOracle社によって開発やソースコード管理が行われているのに対し、MariaDBは、純粋なオープンソースプロジェクトです。MariaDBのシェアは、MySQLには及びませんが、多くのLinuxディストリビューションが MySQL に代わって MariaDB を採用している点、また一般ユーザーが使いやすいという点で、MariaDB は世界中で急速にシェアを拡大しています。基本的なコマンド・操作方法は同じなので、学習目的で個人で実装する場合は、MariaDBの方がいいかもしれません。

↑

ローマルマシンへのインストール

個人のPCにインストールする場合、XAMPP パッケージの導入が最も簡単です（同梱されています）。 XAMPPについては、LocalServer のページにも記載しています。DBサーバーの起動は、インストール後に、Manager ツールから MySQL の起動を選択するだけです。

↑

レンタルサーバーにインストール

レンタルサーバーでは、DBサービスそのものはほぼインストール済みです。管理画面のメニューにそってサービスを起動することになりますが、管理画面の仕様はサービスによって異なるので、データベースのセットアップに関する基本的な用語を理解していることが前提となります。

↑

プレーンなサーバーマシンにインストール

遠隔のLinuxマシン（CentOS）に Database をインストールするには、手元のPCの Terminal からSSH接続して、すべての操作をCUI（コマンド入力）で行う必要があります。

以下、MariaDBの場合と、MySQL8の２つの導入手順のメモを紹介します。
あくまでもメモです。この作業は、OS、そのバージョン、導入するDBの種類・バージョンによってそれぞれ異なります。インストール後の設定ファイル（my.cnf）の扱いや、認証方法の変更など、仕様の変更もあるので、同じ手順でうまくとは限りません

付記
説明の行頭に # があるのは rootでログインしていることを意味します。

# コマンドライン

一般ユーザでログインした場合は、コマンドの前に sudo が必要になります。

$ sudo コマンドライン

↑

MariaDB導入事例

フラットな状態で提供された Linux（CentOS7）環境に、ターミナルからSSH接続でMariaDBをインストールする方法を紹介します。SSH接続って何？という方はこちら

↑

準備

インストールの前に、サーバーの環境を確認します。

すでにMySQLが存在していないか確認
```
# yum list installed | grep mysql
```
すでに存在している場合は、例えば以下のようにして削除
```
# yum remove mysql-libs
```

同様にMariaの5系が存在していないかも確認
```
# yum list installed | grep mariadb
```
すでに存在している場合は、例えば以下のようにして削除
```
# yum remove mariadb*
```

古い情報を削除
```
# rm -rf /var/lib/mysql/
```

↑

リポジトリの追加

MariaDBの10系をインストールするためにはMariaDB専用レポジトリの追加が必要になります。

まず、以下の方法で [利用可能なパッケージ] に10系がないか確認
# yum list maria*
あればそのままインストール可能ですが、なければ以下で追加

10系のリポジトリの追加は以下の一行で可能です
# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

インストール可能になったか確認
以下のように 10系の候補が出てくればOK

# yum list maria*

読み込んだプラグイン:fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: ftp.tsukuba.wide.ad.jp
　 ：
利用可能なパッケージ
MariaDB-backup.x86_64　10.3.15-1.el7.centos　mariadb-main
MariaDB-backup-debuginfo.x86_64　10.3.15-1.el7.centos　mariadb-main
MariaDB-cassandra-engine.x86_64　10.3.15-1.el7.centos　mariadb-main
MariaDB-cassandra-engine-debuginfo.x86_64　10.3.15-1.el7.centos　mariadb-main
MariaDB-client.x86_64　10.3.15-1.el7.centos　mariadb-main
　 ：

↑

インストールとサービスの起動

MariaDBをインストール

# yum install MariaDB-server MariaDB-client

インストールされたか確認

# yum list installed | grep MariaDB
MariaDB-client.x86_64　10.3.15-1.el7.centos　 @mariadb   
MariaDB-common.x86_64　10.3.15-1.el7.centos　@mariadb   
MariaDB-compat.x86_64　10.3.15-1.el7.centos　@mariadb   
MariaDB-server.x86_64　10.3.15-1.el7.centos　@mariadb

インストールされた場所の確認
```
# which mysqld
/usr/sbin/mysqld
```

サービスの開始
```
# systemctl start mariadb
```
参考：一般的なコマンド形式は以下
```
systemctl [Unitコマンド] [Unit名]
```

psコマンドでMariaDBの本体 mysqldが起動していることを確認
```
# ps -aux | grep mysqld
```

自動起動の設定
サーバーの再起動や停止の復旧時に、MariaDBが自動的に起動するように設定
```
# systemctl enable mariadb
```

↑

初期パスワードの設定

MariaDBは、インストール直後、root での起動は No Pass で可能です。
```
# mysql  ← これで起動
MariaDB [(none)] >   となる
```
一旦 quit
```
MariaDB [(none)] > quit
Bye
```

以下のコマンドでパスワード設定可能です

# mysql_secure_installation
：
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 MariaDB
root user without the proper authorisation.
Set root password? [Y/n]

ここで　Y

New password:  新しいパスワードを入力
Re-enter new password: もういちど入力
Password updated successfully!

以下、いろいろ尋ねられるが、基本すべて Y で完了
一旦 quit します。

以下で PW の有効性を確認
```
# mysql -u root -p
```
参考：データベース一覧表示
```
MariaDB[(none)]>show databases;
```

↑

参考：rootユーザーのパスワード初期化手順

MariaDB停止
```
# systemctl stop mariadb
```
環境変数セット “–skip-grant-tables”という環境編集オプションを設定
```
# systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
```
環境変数がセット出来たら、MariaDBを起動
```
# systemctl start mariadb
```

その後、上記の手順でパスワードをセット

rootパスワードが変更出来たら、MariaDB停止
```
# systemctl stop mariadb
```
先ほど設定した、”–skip-grant-tables”オプション解除。
```
# systemctl unset-environment MYSQLD_OPTS
```
MariaDB起動
```
# systemctl start mariadb
```
ログインの確認
```
# mysql -u root -p
```
以上

↑

接続と動作確認

接続（MariaDBの場合も、接続コマンドは mysql です）

# mysql -u root -p

以下のように表示されれば接続OKです。

Welcome to the MariaDB monitor.  Commands end with ; or \g.   
Your MariaDB connection id is 8                    
・・途中略・・  

MariaDB [(none)]>

ユーザーの確認

MariaDB [(none)]> SELECT Host,User,Password FROM mysql.user;

以下、DB作成の事例
- データベース（DB名：wordpress）をクリエイト
```
MariaDB [(none)]> create database wordpress;
```
- できたか確認
```
MariaDB [(none)]> show databases;
```

接続を切る
```
MariaDB [(none)] > quit
Bye
```

↑

MySQL導入事例

SSH接続で遠隔のCentOSに MySQL 8 をインストール

↑

準備

インストールの前に、サーバーの環境を確認します。

MariaDBがいないか確認＞存在した場合は削除する
- MariaDBの存在確認
```
# yum list installed | grep MariaDB
```
- 何かあれば、例えば以下のようにして削除
```
# yum remove mariadb-libs
```

古いものがあれば削除
```
# rm -rf /var/lib/mysql/
```

↑

リポジトリの追加

単にインストールコマンドを打っただけでは、欲しいバージョンのものが入らない場合があります。バージョンによってリポジトリ情報の追加が必要です。

# rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

リポジトリリストの確認

# yum repolist all | grep mysql

リスト中で「有効」のものが採用される前提

mysql80-community/x86_64  MySQL 8.0 Community Server 有効: 113

↑

インストールとサービスの起動

MySQL8 をインストール
```
# yum install mysql-community-server
```

MySQLを起動
```
# systemctl start mysqld.service
```

サービスの永続化（再起動時に自動起動します）
```
# systemctl enable mysqld.service
```

↑

初期パスワードの変更

MySQL8 は、インストール直後、No Pass での root の起動はできません。
```
# mysql  ← これでは起動できません。
```
初期パスワードを変更して設定する作業が必要になります。

初期パスワードがつくられているので以下で確認
```
# cat /var/log/mysqld.log | grep 'password is generated'
```
＞最後の部分に初期パスワードが表示される

パスワードの変更
以下のコマンドから初期設定を行う
```
# mysql_secure_installation
```

以下の順で入力を求められますので、順番に入力
- rootパスワードの入力。先に調べたデフォルトのrootユーザーのパスワードを入力
- 新しく設定するrootユーザーのパスワードを入力
- パスワードの確認＞もう１度入力
- 匿名ユーザーを削除するか＞そのまま [ enter ]（匿名ユーザーを削除）
- リモートからのrootログインを禁止するか聞かれる > お好きな方
- testデータベースを削除するか　＞　削除でOK
- 権限の変更を更新するか聴かれるので、そのまま [ enter ]（不要）

↑

接続と動作確認

接続（ルートのパスワードで）

# mysql -u root -p
[Enter password:  パスワードを入力

ユーザーの確認

mysql> SELECT Host,User,Password FROM mysql.user;

以下、DB作成の事例
- データベース（DB名：wordpress）をクリエイト
```
mysql> create database wordpress;
```
- できたか確認
```
mysql> show databases;
```

接続を切る
```
mysql> quit
Bye
```

↑

文字コードについて

日本語文字化け回避のため、DBが使用する文字コードは正しく設定されていることが必要です。以下、文字コードを確認・変更する方法です。

↑

文字コードの確認

mysql> show variables like "chara%";

+--------------------------+--------------------------------+
| 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-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.10 sec)

クライアント側の設定
- character_set_client
- character_set_connection
- character_set_results
サーバ側の設定
- character_set_database
- character_set_server
- character_set_system

↑

設定ファイルの変更

文字コードの変更を行うには、設定ファイルに指定を加える必要があります。
Linuxの場合 /etc/my.cnf が設定ファイルです。vi が使えない場合は、FTP でDLして、手元で編集して戻す・・という作業になります。
　my.cnf をエディタで開いて、[mysqld] [client] [mysql]それぞれ「default-character-set」の文字コードを変更、あるいは追記を行います。

サーバ側の文字コード設定

[mysqld]
   ：
default-character-set=文字コード

クライアント側の文字コード設定

[client]
   ：
default-character-set=文字コード

コマンドラインクライアントの文字コード設定
```
[mysql]
   ：
default-character-set=文字コード
```

↑

確認作業

MySQLを再起動

mysql> RESTART　（MySQL８からこれができるようになりました）

コードが変更されたか再確認
```
mysql> show variables like "chara%"; 
```

↑

phpMyAdmin で管理

phpMyAdmin は MySQL サーバーをブラウザから管理するためのDB接続クライアントツールで、PHPで書かれています。SQL の文法を知らなくてもデータベースに対する様々な操作が可能です（もちろん SQL を記述して実行することもできます）。

↑

ダウンロードとセットアップ

ローカルで XAMPP を利用しているケース
すでに同梱されています。以下のアドレスで確認できるはずです。
http://localhost/phpmyadmin/

レンタルサーバーを利用するケース
すでに置いてあるケースが大半です。サーバーの管理ページのメニュー項目に phpMyAdmin がないか確認してください。

プレーンな外部サーバーを利用しているケース
自分で設置する必要があります。
- 以下の公式サイトから phpMyAdmin をダウンロード
  https://www.phpmyadmin.net/
- FTP接続で phpMyAdmin フォルダをドキュメントルートにアップロード
  公開サーバーの場合、ドキュメントルートの phpMyAdmin というフォルダ名は攻撃を受けやすいので、別名に変更する方がよいでしょう。
- 設定ファイルは　config.inc.php（以下参考）
  必要であれば、conig.sample.inc.phpをコピーして作成。

↑

参考：phpMyAdmin の configファイル（必要に応じて変更）

<?php
/* vim: set expandtab sw=4 ts=4 sts=4: */
/**
 * phpMyAdmin sample configuration, you can use it as base for
 * manual configuration. For easier setup you can use scripts/setup.php
 *
 * All directives are explained in Documentation.html and on phpMyAdmin
 * wiki <http://wiki.phpmyadmin.net>.
 *
 * @version $Id$
 */

/*
 * This is needed for cookie based authentication to encrypt password in
 * cookie
 */
$cfg['blowfish_secret'] = 'ここには適当な文字列を入れる'; 
 /* YOU MUST FILL IN THIS FOR COOKIE  UTH! */

/*
 * Servers configuration
 */
$i = 0;

/*
 * First server
 */
$i++;

/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost または　サーバーアドレス';　←これ
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';
/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = '********';　　←これ
$cfg['Servers'][$i]['controlpass'] = '********';　　←これ
/* Advanced phpMyAdmin features */
以下コメントアウト 

/*
 * End of servers configuration
 */

/*
 * Directories for saving/loading files from server
 */
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';

?>

↑

ローカルで XAMPP を利用している場合の DBへの接続と操作

以下のアドレスにアクセスします。
http://localhost/phpmyadmin/
- データベースユーザー：root
- パスワード：なし

データベースの新規作成
- 左袖のメニューから New をクリック
- データベース名を（半角英数）で書きます。
- 文字コードには utf8-general-ci を選択します。
- 作成ボタンをクリック　＞　以上
  WordPress 等のCMSで利用する場合は、データベースを作成するだけでOKです。あとは CMS が操作します。

↑

レンタルサーバー・外部サーバのDBへの接続と操作

サーバー管理画面にメニューがある場合
当該ボタンをクリック

自分で設置した場合
http://（url）/（phpMyAdminを置いたパス）/

以下、上記と同様です。

↑

ERR：mysqli 拡張がありません・・について

MySQLiとは MySQL improved extension の略で、PHPからMySQL/MariaDBを操作するインターフェースを提供するPHPの拡張機能です。システムにこれが無い場合は、追加でインストールする必要があります。

# yum -y install php-mysql

失敗するときは、以下のように正しいリポジトリを見に行くように指示。

# yum install --enablerepo=remi,remi-php73 php-mysql

有効化のため再起動が必要です。

# systemctl restart httpd.service

参考：リポジトリ選択の重要性
リポジトリを指定せずにインストールしようとすると、デフォルトで有効化されていないリポジトリからのインストールは行われません。リポジトリ情報は /etc/yum.repos.d にあって、それぞれ有効/無効が記載されています。有効化したリポジトリの xxxx.repo ファイルを編集し、enabled=1 にして実行することで、それができるようになります。

↑

MySQL 8 で phpMyAdmin にログインできない場合

MySQL 8 はそのままでは phpMyAdmin でログインできません。認証方法が変わっていて、phpMyAdminが対応していないので、前提としてMySQLの認証方法を以下のように書き換える必要があります。コンフィグファイルは /etc/my.cnf です。以下の行、コメントを外して、mysql_native_password を有効にします。
```
default_authentication_plugin=mysql_native_password
```

すでにできてしまっているものは、mysql で認証方式の変更が必要です。まずは状態を確認します。

mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+

phpMyAdmin に root でアクセスしようとしても、 caching_sha2_password になっているので、できません。そこで変更します。
```
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '（ここにパスワード）';
```

変更されたことを確認します。

mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+

↑

Terminal 接続で管理

ローカルマシンの場合も、リモートにSSH接続した場合も同様の操作です。

↑

接続と基本操作

データベースへの接続
XAMPP 等でインストールした場合は、以下のコマンドで接続可能です
```
$ mysql
```
パスワードが設定されている場合は、以下のように入力し、その後、パスワードを入力することで接続が完了します。
```
$ mysql -u root -p
```

以下のように表示されて、以後 DBコマンドが入力できます。
```
MariaDB [(none)] > 
```

データベースの新規作成

MariaDB [(none)] >create database [DB名];

既存のデータベース一覧の表示
```
MariaDB [(none)] >show databases;
```

データベースユーザーの追加

MariaDB [(none)] > create user [ユーザー名] identified by [パスワード];
MariaDB [(none)] > grant all on [DB名].* to [ユーザー名];

データベースの削除

MariaDB [(none)] > drop database [DB名];

接続を切る
```
MariaDB [(none)] > quit
Bye
```

↑

データベースのバックアップとリストア

データベースのバックアップ

$ mysqldump -u  [ユーザ名] -p  [DB名] > [バックアップ名（拡張子 .sql）]

データベースのリストア（コマンドの違いに注意：こちらは mysql）
```
$ mysql -u  [ユーザ名] -p [DB名] < [バックアップ名（拡張子 .sql）]
```
注）上記、いずれも要求されるパスワードは DB利用者のパスワードです。

↑

MySQL(Mariadb)のデータ領域

デフォルトデータ領域は以下です。
```
/var/lib/mysql
```
Macにインストールした XAMPP でローカル環境を作っている場合は以下。
```
/Application/XAMPP/xamppfiles/var/mysql/
```

MySQL

CONTENTS

MySQL（MariaDB）について

ローマルマシンへのインストール

レンタルサーバーにインストール

プレーンなサーバーマシンにインストール

MariaDB導入事例

準備

リポジトリの追加

インストールとサービスの起動

初期パスワードの設定

参考：rootユーザーのパスワード初期化手順

接続と動作確認

MySQL導入事例

準備

リポジトリの追加

インストールとサービスの起動

初期パスワードの変更

接続と動作確認

文字コードについて

文字コードの確認

設定ファイルの変更

確認作業

phpMyAdmin で 管理

ダウンロードとセットアップ

参考：phpMyAdmin の configファイル（必要に応じて変更）

ローカルで XAMPP を利用している場合の DBへの接続と操作

レンタルサーバー・外部サーバのDBへの接続と操作

ERR：mysqli 拡張がありません・・について

MySQL 8 で phpMyAdmin にログインできない場合

Terminal 接続で管理

接続と基本操作

データベースのバックアップとリストア

MySQL(Mariadb)のデータ領域

phpMyAdmin で管理