●MyISAMとInnoDB
参考URL：MySQLの「InnoDB」と「MyISAM」についての易しめな違い
参考URL：WordPressにはMyISAMとInnoDBどちらが良いのか？
MySQLデータベース（又はMariaDB。）のストレージエンジンには「MyISAM」と「InnoDB」があります。
MySQLのバージョン5.5より前ではMyISAM、MySQLのバージョン5.5以降ではInnoDBがデフォルトとなっています。
まとめると下記のような特徴があります。
つまり、上記ではストレージエンジンに「MyISAM」を使用している、ということになります。
●MyISAMストレージエンジン
参考URL：13.4. MyISAM ストレージエンジン
参考URL：InnoDBを含む代表的なストレージエンジンの主な特徴
各 MyISAM テーブルはディスク上に3つのファイルとして保管されます。そのファイル名はテーブル名で始まり、ファイルタイプを指示する拡張子が付きます。.frmファイルはテーブルフォーマットを格納します。データファイルには.MYD（MYData）拡張子が付きます。インデックスファイルには.MYI（MYIndex）拡張子が付きます。
●初期設定（CentOS 8）
必要なソフトをインストールします（インストール済みの場合、該当作業は不要です）。
●初期設定（旧）
サービスを起動します。
●タイムゾーンの設定について
タイムゾーンをインポートします。
上記のタイムゾーンデータをインポートしていない場合は起動時にエラーになります。
設定内容を確認します。
●パスワードの設定
MySQL用rootのパスワードを設定します。
mysqladminでパスワードを設定することも可能です。
●テストデータベースの確認
root権限でMySQLにログインします。
これでもエラーが表示される場合は、下記のように対応します。
参考URL：Mysqlで「Access denied for user ‘root’@’localhost’ (using password: NO)」
test用データベースを参照できるか確認します。
●MySQL文字化けを防ぐ、文字コードの確認と設定
文字化けを防ぐために、文字コードの確認と設定を行います。
現在の文字コードの設定を調べる
デフォルトの文字コードの設定を確認します。
特定のデータベースの文字コードの設定を確認します。
「latin1」という値がある場合は、文字化けする可能性が大なので、十分注意する必要があります。
文字コードを設定する
/etc/my.cnf を編集します。
utf8： UTF-8を指定するとき。
sjis： Shift JIS を指定するとき。
ujis： EUC JP を指定するとき。
skip-character-set-client-handshakeの指定は、MySQLが勝手に内部変換しないようにするためです。/etc/my.cnfが無いときは、インストールしたMySQLの中のサンプルをコピーします。
データベースの文字コード設定を調べる
データベースごとに文字コードを設定できるので、現在の文字コードを調べます（テーブルごとではなく、データベースごと）。
文字コードを指定してデータベースを作るには、
テーブルの文字コード設定を調べる
テーブルごとに文字コードを設定できるので、現在の文字コードを調べる。
●Apacheとの連携（PHP使用）
PHPとMySQLの連携を可能にするため，以下のコマンドを実行して， php-mysqlをインストールします。
ソースからのインストールはPHPの設定を参照してください。
MySQLサーバへの接続に成功した場合「Success MySQL First Test」、失敗した場合「Error MySQL First Test」と表示されます。
もし、Webブラウザが何も応答もなく真っ白の場合は下記を試してください。
「Error MySQL First Test」が表示される場合はスクリプトに問題があるか、MySQLが停止している可能性がありますので確認してください。
下記は、●データベースの作成で作成したデータベース「auth」への接続用PHPスクリプトです。
●PerlとMySQLの連携
PerlからMySQLにアクセスするには、DBIとDBD::mysqlを使用します。
DBIは、さまざまなデータベース向けの汎用インタフェースです。これは、何も変更しなくても、複数の異なるデータベースで動作するスクリプトを作成できることを意味します。データベースの種類ごとにデータベースドライバ（DBD）を定義する必要があります。MySQLの場合、このドライバはDBD::mysqlと呼ばれます。
DBDは、Oracleなど、各種データベース用のものが配布されており、DBIは各DBDに共通で使えます。階層的にあらわすと、「DBMS(MySQL等) ⇔ DBD ⇔ DBI ⇔ Perl プログラム」というようになっており、つまり、DBMSとDBIの間をとりもち、DBMS毎の差異を吸収するのがDBDということになります。 これを用いることにより、DBMSが例えばOracleからMySQLに変わっても、理論的にはプログラムの修正が不要（SQLレベルの変換は当然行われませんので、DBMS依存のSQLが含まれている場合は修正が必要です）となります。
CPANからモジュールダウンロードに関する環境設定はPerlの設定を参照してください。
MySQLを利用しますので、ソースからインストールする場合はCPANからDBIとDBD::mysqlをダウンロードします。
・yumコマンドを使用する場合
cpanコマンドを利用するか、下記の方法によりインストールします。
サンプルデータベースの作成
今回は、氏名と電話番号および電子メールアドレスを管理する簡易アドレス帳のサンプルデータベースを作成します。
まず、MySQLのプロンプトでcreate table文を発行して、テーブルを定義します。
Perlのプログラム（表示用） DBI + DBD版
PerlでMySQLに作成したデータベーステーブルの表示を行うプログラムを作成してみます。
PerlでMySQLに接続するには、下記のような手法を用います。
Perlのプログラム（追記用） DBI + DBD版
PerlでMySQLに作成したデータベーステーブルに追記を行うプログラムを作成してみます。以下は、先ほど作成したテーブルの内容に追記する簡単なプログラムのリストです。
●RubyとMySQLの連携
RubyとMySQLを接続するためには，
1. MySQL/Ruby
2. Ruby/MySQL
3. Ruby/DBI
のいずれかをインストールする必要があります。
Ruby/DBI https://rubygems.org/gems/dbi
DBD::Pg https://rubygems.org/gems/dbd-pg
pg https://rubygems.org/gems/pg
しかし、開発者によるとdbiはRuby 1.8までしかサポートされていないようです。
https://github.com/erikh/ruby-dbi/issues/5
インストールは、下記のように実施します。
※下記はruby 1.8までの話として残しておきます。
ーーーーー ここから ーーーーー
ーーーーー ここまで ーーーーー
サンプルデータベースの作成
データベースは、PerlとMySQLとの連携で作成したものを使用します。
Rubyのプログラム（表示用） MySQL/Ruby版
RubyでMySQLに作成したデータベーステーブルの表示を行うプログラムを作成してみます。以下は、先ほど作成したテーブルの内容を表示する簡単なプログラムのリストです。
Rubyのプログラム（追加用） MySQL/Ruby版
データを追加するプログラムを紹介します。
SQL文の記載方法は、
Rubyのプログラム（表示用） DBI + DBD版
MySQLで作成したデータベーステーブルの表示を行うプログラムを作成してみます。以下は、先ほど作成したテーブルの内容を表示する簡単なプログラムのリストです。
Rubyのプログラム（追加用） DBI + DBD版
PerlでMySQLに作成したデータベーステーブルの表示を行うプログラムを作成してみます。以下は、先ほど作成したテーブルの内容を表示する簡単なプログラムのリストです。
●データベースの作成
【MySQL基本設定値】
【cityテーブルスキーマ】
【cityテーブル挿入データ】
上記データベースを作成します。
●データベースのバックアップ・リストア
データベースをコピーするには下記のようにします。
●テーブルのバックアップ・リストア
テーブルをコピーするには下記のようにします。
●バックアップしたデータベースから特定のテーブルをリストアする方法
参考URL：MySQLのダンプファイルから任意のテーブルのみリストアする
バックアップしたダンプファイルが全体のデータベース、または、特定のデータベースである場合、その中から任意のテーブルのみをリストアする場合は下記のように実施します。
いずれもコマンドを実行しているカレントディレクトリに、csplitにより、「xxNNN」（NNNはゼロ埋めされた連番）のようなファイルに分割されるので、
ダンプファイルのサイズが大きい場合やメモリに余裕がない場合などは、以下のようなエラーが出力されファイルを分割できないことがあります。
●データベース・テーブルのサイズ確認方法
参考URL：MySQLでDBとテーブルのサイズを確認するSQL
データベースサイズの確認方法は下記のとおりです。
テーブルサイズの確認方法は下記のとおりです。
●レプリケーションの設定
バージョン10.3系
参考URL：MariaDB 10.3 : レプリケーション
参考URL：Pacemaker/Corosyncを用いた冗長化Zabbix Serverの構築
参考URL：Pacemaker で Zabbixサーバ をHAクラスタ化してみた (リソース登録編)
レプリケーション用ユーザ（repl）を両ホストで作成します。
バイナリログは更新系のログが記録されますが、放っておくと際限なく増えますので必ず保存日数を指定します。
参考URL：MariaDB(10.3.23)でレプリケーション
参考URL：MySQLのバイナリログ削除タイミング
参考URL：バイナリログの削除
参考URL：【Zabbix】MySQLのバイナリファイルが肥大化してDBが立ち上がらなかった
スレーブ側のDBデータを削除します。
取得したデータは sftp や rsync 等を使用して、レプリカサーバー側へ転送します。
正常に確認できれば正常に動作しています。
※Pacemakerの制御によるMaster/Slaveで運用する場合の作業はここまで
クラスタを設定します。
「pacemakerでZabbix-Server及びMariaDBのMaster/Slaveを制御する」を参照してください。
Zabbixを設定するため「PacemakerでZabbix-Serverを制御するための準備（MariaDBはMaster/Slave方式）」を参照してください。
バージョン5.1系
参考URL：MySQL レプリケーション
マスタースレーブ方式の冗長構成を構築します。
MySQLサーバはマスタ、スレーブとも基本的な作業は完了し利用できる状態になっているものとします。
また、iptables等を利用している場合は、必要なポートを開放するよう設定してください。
マスターサーバ側の設定を一部変更します。その際、レプリケーションに必要なユーザを作成します。
マスター側でデータベースを作成する等してレプリケーションが行われていることを確認してください。
レプリケーション設定を無効にする
一度構築したレプリケーションを解除するには下記のようにします。
スレーブ側で以下を実行します。
マスタ側で、レプリケーション権限のユーザを削除。
master、slaveの手動切り替え
参考URL：MySQLのmasterとslaveの手動切り替え
●MariaDB（MySQL）のデータベースのフォルダを変更するには（pacemaker、DRBD対応）
参考URL：MariaDB(MySQL)のデータベースのフォルダを変更する
MariaDBのサービスを停止します。
●ERROR 1146 (42S02): Table ‘mysql.servers’ doesn’t exist
FLUSH PRIVILEGESを実行したところ、ERROR 1146 (42S02): Table ‘mysql.servers’ doesn’t existと出力されました。どうやら権限管理に必要なテーブルがないらしい。下記のテーブルを作成します。
●Fedora 15にアップグレード後、Mysqlが起動しなくなった
調べてみると下記のようなエラーの記録がありました。
「#default-character-set=utf8」の設定後、データベース「test1」を試験的に作成し確認しました。
●Please use mysql_upgrade to fix this error.
Mysqlのログに下記のようなエラーが記録されていました。
●Fedora 20で「require "dbi"」で作成しているスクリプトで接続できない
どうも使用しているDBIの違いによるものらしい。
したがって、Fedora 20で動作するスクリプトに変更しました。
●CGI（Ruby + MySQL、DBI:MySQL、require "mysql"） / ruby_dbi2_mysql.rb
●接続失敗：Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)
phpえmariadbに接続してデータベース結果を表示させようとしたところ下記のようなエラーが表示されました。
●`connect': Host '*****' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' (Mysql::Error)
これまで正常に動作していたあるCGIをWeb経由で実行したらエラーとなってしまった。同じCGIをコマンドレベルで実行したところエラーが出力された。
https://dev.mysql.com/doc/refman/5.6/ja/blocked-host.html
root権限でmysqladmin flush-hostsを実行すれば問題は解消されます。システム変数のmax_connect_errorsの値以上連続で接続失敗すると、ブロックされてしまうようだった。この値はデフォルト100（MySQL 5.6.6以前は10）であり、Zabbixやxymonによる監視で100回連続で接続失敗したことによるものと思われる。
admin権限を持ったユーザを使用することができない場合、別の（ブロックされていない）ホストからMySQLに接続し、下記のSQLを流せば問題は解消されます。
ちなみに現在のコネクション数、最大コネクション数を確認するには下記のようにします。
根本的な原因は解決する必要がありますが・・・（XymonやZabbix等の監視の影響かな〜）。
my.cnfに設定する場合の例
●全てのデータベースをバックアップするとエラーが表示されてバックアップできない
参考URL：日々の覚書 - ついに#mysql50#lost+foundデータベースとさよならするのだ
参考URL：Upgrade of MySQL schema with mysql_upgrade fails with the error: Error: Incorrect database name '#mysql50#.ssh'
mysqldumpで--all-databasesオプションを付加してバックアップを実行すると、
これは、MySQLインストールが古いバージョンからMySQL 5.1以降にアップグレードされると、サーバではa-b-cなどの（古い形式の）名前が #mysql50#a-b-cとして表示され、名前を参照するために#mysql50#プリフィクスを使用するらしい。
このデータベースを削除してしまうとMySQL自体がエラーになってしまいそうなので、とりあえず存在が見えないようにすることにしました。
さて、このエラーの元になっている#mysql50#.cacheや#mysql50#.configですが、/var/lib/mysqlの下に隠しフォルダとして存在していました。
●レプリケーションの同期が取れなくなった場合の対処方法（その1）
まず、スレーブ側でレプリケーションの状態を確認します。
下記のようにSlave_IO_RunningとSlave_SQL_Runningが両方ともYesでない場合、レプリケーションに失敗しています。
●レプリケーションの同期が取れなくなった場合の対処方法（その2）
MariaDBのログに記録されていたエラーは下記のとおりです。
下記のようにSlave_IO_RunningとSlave_SQL_Runningが両方ともYesでない場合、レプリケーションに失敗しています。
復旧手順は次のとおりです。
まず、スレーブ側でスレーブ機能を停止し、マスター側で作業します。
●ロックしているプロセスを特定し強制終了させる方法
参考URL：MySQLでロックを特定し、強制終了する
ロック状態の確認方法
ロックされている件数の確認方法は下記のとおりです。
ロックされている件数とスレッドIDの確認方法は下記のとおりです。
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
この部分 => 1 row lock(s),
ロックが発生しているスレッドの特定
実際にロックが発生しているスレッドのIDは、その下に出ています。
MySQL thread id 5, OS thread handle 0x7f6560475700, query id 190 localhost 127.0.0.1 rkojima
この部分 => MySQL thread id 5
このthread id は show processlist;のIdと同じです。
スレッドの強制終了
Id (processlist_id) の特定ができましたので、強制終了します。
動作確認
最初に動作確認用のテーブル等を作成します。
スレッドB
スレッド C
●断片化
参考URL：mysqlの断片化対策
参考URL：［MySQLウォッチ］第24回 台風の目になったMySQLの現状とバックアップ・ファイルの活用
大量の行削除や行追加等を実施すると、データが格納されていない空白の部分が発生しやすくなり、テーブルやインデックスが断片化されてパフォーマンスの劣化につながります。
MySQLにはいくつかのフラグメンテーション対象方法があります。
しかし、最も確実な解消策は、mysqldumpでバックアップし、テーブルを削除して、リストアすることです。MyISAMはテーブルごとにファイルを３つ作成しますので、即座にフラグメンテーションが解消されます。
テーブルのバックアップ及びリストア方法は、「●テーブルのバックアップ・リストア」を参照してください。
ストレージエンジンがInnoDBの場合は、下記の方法でも可能です。
とData_lengthを比較します。
179,544,539,136 ≒ 179,513,085,487
断片化が解消されたことがわかります。
●レプリケーションの解除方法
参考URL：MySQLのレプリケーションを完全にやめる方法
スレーブ側で下記を実行します。
マスター側で、レプリケーション権限のユーザを削除します。
参考URL：MySQLの「InnoDB」と「MyISAM」についての易しめな違い
参考URL：WordPressにはMyISAMとInnoDBどちらが良いのか？
MySQLデータベース（又はMariaDB。）のストレージエンジンには「MyISAM」と「InnoDB」があります。
MySQLのバージョン5.5より前ではMyISAM、MySQLのバージョン5.5以降ではInnoDBがデフォルトとなっています。
まとめると下記のような特徴があります。
- データの読み書きを行う基盤のことを「ストレージエンジン」と呼ぶ
- ストレージエンジンには「InnoDB」や「MyISAM」など様々な種類がある
- MySQL5.5以降でのデフォルトのストレージエンジンはInnoDB
- InnoDBは対象のレコードだけをロックする（行ロック）
- MyISAMは対象のテーブル自体をロックする（テーブルロック）
- InnoDBにはトランザクション機能がある
- MyISAMにはトランザクション機能がない
- 更新処理が多いアプリケーションの場合はInnoDBを選ぶと良さそう。そうではない場合もInnoDBのが色々と楽かも？
mysql> show engines\G
*************************** 1. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 5. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
5 rows in set (0.04 sec)
サポートカラムの値は、エンジンを使用できるかどうかを示しています。YES、NOまたはDEFAULTの値は、エンジンが「使用可能」、「使用可能でない」または「デフォルトのストレージエンジンとして使用可能であり、現在設定されている」を表しています。
つまり、上記ではストレージエンジンに「MyISAM」を使用している、ということになります。
●MyISAMストレージエンジン
参考URL：13.4. MyISAM ストレージエンジン
参考URL：InnoDBを含む代表的なストレージエンジンの主な特徴
各 MyISAM テーブルはディスク上に3つのファイルとして保管されます。そのファイル名はテーブル名で始まり、ファイルタイプを指示する拡張子が付きます。.frmファイルはテーブルフォーマットを格納します。データファイルには.MYD（MYData）拡張子が付きます。インデックスファイルには.MYI（MYIndex）拡張子が付きます。
●初期設定（CentOS 8）
必要なソフトをインストールします（インストール済みの場合、該当作業は不要です）。
# dnf install php php-fpm -y # vi /etc/php.ini expose_php = On ↓ expose_php = Off ;date.timezone = ↓ date.timezone = 'Asia/Tokyo' error_reporting = E_ALL & ~E_DEPRECATED & ~E_STRICT ↓ error_reporting = E_ALL & ~E_NOTICE # dnf install httpd # dnf install mariadb mariadb-server -y # vi /etc/my.cnf.d/charset.cnf # 新規作成 # デフォルトの文字コードを設定 # 未設定の場合のデフォルトは [latin1] # 絵文字等 4 バイト長の文字を扱う場合は [utf8mb4] [mysqld] character-set-server = utf8mb4 [client] default-character-set = utf8mb4 # vi /etc/my.cnf.d/mariadb-server.cnf [mysqld]に下記を追加 slow_query_log log_output=FILE slow_query_log_file=/var/log/mariadb/slow-queries.log long_query_time=5.0 # systemctl enable --now mariadb # 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): 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! 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 ... 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 ... 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 - 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 ... 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!MariaDBに接続し、テストデータベースを作成します。
# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.3.27-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root | 127.0.0.1 | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root | ::1 | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
+------+-----------+-------------------------------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)
MariaDB [(none)]> create database test_database;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> create table test_database.test_table (id int, name varchar(50), address varchar(50), primary key (id));
Query OK, 0 rows affected (0.004 sec)
MariaDB [(none)]> insert into test_database.test_table(id, name, address) values("001", "CentOS", "Sapporo");
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> select * from test_database.test_table;
+----+--------+-----------+
| id | name | address |
+----+--------+-----------+
| 1 | CentOS | Hiroshima |
+----+--------+-----------+
1 row in set (0.000 sec)
MariaDB [(none)]> drop database test_database;
Query OK, 1 row affected (0.005 sec)
MariaDB [(none)]> status;
--------------
mysql Ver 15.1 Distrib 10.3.27-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 17
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.3.27-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 25 min 9 sec
Threads: 7 Questions: 29 Slow queries: 0 Opens: 22 Flush tables: 1 Open tables: 15 Queries per second avg: 0.019
--------------
MariaDB [(none)]> quit
Bye
●初期設定（旧）
サービスを起動します。
# service mysqld start ← MySQLの起動 MySQL データベースを初期化中: Installing MySQL system tables... 081224 15:43:11 [Warning] option 'max_join_size': \ unsigned value 18446744073709551615 adjusted to 4294967295 081224 15:43:11 [Warning] option 'max_join_size': \ unsigned value 18446744073709551615 adjusted to 4294967295 OK Filling help tables... 081224 15:43:11 [Warning] option 'max_join_size': \ unsigned value 18446744073709551615 adjusted to 4294967295 081224 15:43:11 [Warning] option 'max_join_size': \ unsigned value 18446744073709551615 adjusted to 4294967295 OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h host1.domain.org password 'new-password' Alternatively you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com [ OK ] MySQL を起動中: [ OK ]psコマンドで確認します。動作していれば、下記のように表示されます。
# ps -ef | grep mysql root 20619 1 0 15:43 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe \ --datadir=/var/lib/mysql \ --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log \ --pid-file=/var/run/mysqld/mysqld.pid --user=mysql mysql 20669 20619 0 15:43 pts/1 00:00:00 /usr/libexec/mysqld \ --basedir=/usr --datadir=/var/lib/mysql --user=mysql \ --pid-file=/var/run/mysqld/mysqld.pid \ --skip-external-locking --socket=/var/lib/mysql/mysql.sockMySQLがシステム起動時に、自動起動するようにします。
# chkconfig mysqld on ← MySQL自動起動設定 # chkconfig --list mysqld ← MySQL自動起動設定確認 mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off ↑ ランレベル2〜5のonを確認
●タイムゾーンの設定について
タイムゾーンをインポートします。
$ /usr/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo > ~/timezone.sql $ mysql -u root -p -Dmysql < ~/timezone.sqlmy.cnfまたはmariadb-server.cnfを変更します。
# vi /etc/my.cnf or # vi /etc/my.cnf.d/mariadb-server.cnf [mysqld] default-time-zone = 'Asia/Tokyo'MySQLを再起動すると設定が反映されます。
上記のタイムゾーンデータをインポートしていない場合は起動時にエラーになります。
設定内容を確認します。
MariaDB [(none)]> show variables like '%time_zone%'; +------------------+------------+ | Variable_name | Value | +------------------+------------+ | system_time_zone | JST | | time_zone | Asia/Tokyo | +------------------+------------+ 2 rows in set (0.00 sec)
●パスワードの設定
MySQL用rootのパスワードを設定します。
# mysql -u root
mysql> update mysql.user set password=password('root用の任意パスワード') where user = 'root';
mysql> flush privileges;
mysql> exit;
設定したパスワードでログインできるかを確認します。
# mysql -u root -p Enter password:ログイン出来れば作業は終わりです。
mysqladminでパスワードを設定することも可能です。
# mysqladmin -u root password '（root用の任意パスワード）'
こちらの方法は簡単なのですが、コマンド履歴にパスワードが残ってしまうことが欠点です。したがって、セキュリティーを重視する場合はあまり使用しないほうが無難です。
●テストデータベースの確認
root権限でMySQLにログインします。
# mysql -u root ← rootでMySQLにログイン Enter password: ← 空欄のまま「Enter」押下 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.0.67 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> ← ログインできると、左のようなプロンプトが応答下記のようなメッセージが表示されてログインできない事があります。
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)この場合は、
# rm -Rf /var/lib/mysql ← mysqlディレクトリ以下すべて削除 ※この作業は全てのデータベースが削除されるため、 事前にデータベースのバックアップを実施しておくこと。 # service mysqld stop ← MySQLの停止 # mysql_install_db ← mysql_install_dbを実行 # chown -R mysql:mysql /var/lib/mysql # service mysqld start ← MySQLの起動としてから、再度、root権限でMySQLにログインします。
これでもエラーが表示される場合は、下記のように対応します。
参考URL：Mysqlで「Access denied for user ‘root’@’localhost’ (using password: NO)」
$ mysqld_safe --skip-grant-tables & [1] 102846 # 2016-09-23T07:08:04.774962Z mysqld_safe Logging to '/var/log/mysqld.log'. 2016-09-23T07:08:04.799768Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql リターンキーを押下する $ mysql -u root mysql> use mysql; mysql> truncate table user; mysql> flush privileges; mysql> grant all privileges on *.* to root@localhost identified by 'password' with grant option; mysql> flush privileges; mysql> quit; $ mysql -u root -p passwordログイン出来ればOKです。
test用データベースを参照できるか確認します。
mysql> show databases; ← 「show databases;」と入力 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.00 sec) mysql> exit ← ログアウトする Bye
●MySQL文字化けを防ぐ、文字コードの確認と設定
文字化けを防ぐために、文字コードの確認と設定を行います。
現在の文字コードの設定を調べる
デフォルトの文字コードの設定を確認します。
mysql> status; ： Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 ：上記のようになっていると、日本語を使用すると文字化けします。特に「Db characterset:」行の値には注意してください。他の全ての行の値が「utf8」の場合でも、「Db characterset:」行の値が「latin1」の場合は文字化けします。このような場合は、デフォルトの文字コードをデータベースを作成する前に作成しておくか、または、データベース作成時に意識的に文字コードを指定します。
特定のデータベースの文字コードの設定を確認します。
「latin1」という値がある場合は、文字化けする可能性が大なので、十分注意する必要があります。
文字コードを設定する
/etc/my.cnf を編集します。
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
default-character-set = utf8
↑ Fedora 15は設定の必要無し。Fedora 20の場合、設定すると起動しない。
skip-character-set-client-handshake
character-set-server = utf8
collation-server = utf8_general_ci
init-connect = SET NAMES utf8
[client]と[mysqld]の設定に、上記の記述を加えます。
utf8： UTF-8を指定するとき。
sjis： Shift JIS を指定するとき。
ujis： EUC JP を指定するとき。
skip-character-set-client-handshakeの指定は、MySQLが勝手に内部変換しないようにするためです。/etc/my.cnfが無いときは、インストールしたMySQLの中のサンプルをコピーします。
# cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf再起動すると有効になります。
データベースの文字コード設定を調べる
データベースごとに文字コードを設定できるので、現在の文字コードを調べます（テーブルごとではなく、データベースごと）。
mysql> use ******; ← 調べたいデータベースに接続 Database changed mysql> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | latin1 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+「latin1」という値がある場合は、文字化けする可能性が大なので、十分注意する必要があります。
文字コードを指定してデータベースを作るには、
mysql> create database xxxdb default character set utf8;
テーブルの文字コード設定を調べる
テーブルごとに文字コードを設定できるので、現在の文字コードを調べる。
mysql> show create table テーブル名 \G
●Apacheとの連携（PHP使用）
PHPとMySQLの連携を可能にするため，以下のコマンドを実行して， php-mysqlをインストールします。
ソースからのインストールはPHPの設定を参照してください。
# yum -y install php-mysqlApacheを再起動します。
# service apache restart/var/www/html/test_mysql.phpを以下のように作成します。
# vi /var/www/html/test_mysql.php ※PHP 5の場合 <?php mysql_connect("localhost","root","") or die("Error MySQL First Test"); echo "Success MySQL First Test"; ?> ※PHP 7の場合 <?php mysqli_connect("localhost","root","") or die("Error MySQL First Test"); echo "Success MySQL First Test"; ?>http://（Webサーバ名）/test_mysql.phpにアクセスすることにより、DBへの接続状況を確認することができます。
MySQLサーバへの接続に成功した場合「Success MySQL First Test」、失敗した場合「Error MySQL First Test」と表示されます。
もし、Webブラウザが何も応答もなく真っ白の場合は下記を試してください。
# php /var/www/html/test_mysql.phpこれで「Success MySQL First Test」と表示されればスクリプトは問題ありません。この場合は、php-mysqlをインストール後httpdを再起動していない可能性がありますので、再起動してください。
「Error MySQL First Test」が表示される場合はスクリプトに問題があるか、MySQLが停止している可能性がありますので確認してください。
下記は、●データベースの作成で作成したデータベース「auth」への接続用PHPスクリプトです。
# vi /var/www/html/test_mysql2.php ← test_mysql2.phpの作成 ※PHP 5の場合 <html> <head> <meta http-equiv=content-type content="text/html; charset=UTF-8"> <title>PHPのテスト</title> </head> <body> <?php //初期設定 $username = "myuser"; ← データベース「auth」へアクセス可能なユーザ $password = "mypass"; ← 上記ユーザ用のパスワード $dbname = "auth"; $sql = "select * from city"; // サーバ接続 $con = mysql_connect("localhost","$username","$password") or die("接続失敗：" . mysql_error()); // データベースを選択 mysql_select_db($dbname,$con) or die("DBがありません：" . mysql_error()); // 文字化け防止のおまじない // $strsql = "SET CHARACTER SET UTF8"; // SQLの実行 $res = mysql_query($sql,$con); print "照会件数= " . mysql_num_rows($res) . "<br />"; // 展開 while ($item = mysql_fetch_array($res)) { print $item[0]." ".$item[1]."<br />"; } // 接続をクローズ mysql_close($con) or die("MySQL切断に失敗しました。"); ?> </body> </html> ※PHP 7の場合 <html> <head> <meta http-equiv=content-type content="text/html; charset=UTF-8"> <title>PHPのテスト</title> </head> <body> <?php //初期設定 $username = "myuser"; ← データベース「auth」へアクセス可能なユーザ $password = "mypass"; ← 上記ユーザ用のパスワード $dbname = "auth"; $sql = "select * from city"; // サーバ接続 $con = mysqli_connect("localhost","$username","$password") or die("接続失敗：" . mysqli_error()); // データベースを選択 mysqli_select_db($con,$dbname) or die("DBがありません：" . mysqli_error()); // 文字化け防止のおまじない // $strsql = "SET CHARACTER SET UTF8"; // SQLの実行 $res = mysqli_query($con,$sql); print "照会件数= " . mysqli_num_rows($res) . "<br />"; // 展開 while ($item = mysqli_fetch_array($res)) { print $item[0]." ".$item[1]."<br />"; } // 接続をクローズ mysqli_close($con) or die("MySQL切断に失敗しました。"); ?> </body> </html>
●PerlとMySQLの連携
PerlからMySQLにアクセスするには、DBIとDBD::mysqlを使用します。
DBIは、さまざまなデータベース向けの汎用インタフェースです。これは、何も変更しなくても、複数の異なるデータベースで動作するスクリプトを作成できることを意味します。データベースの種類ごとにデータベースドライバ（DBD）を定義する必要があります。MySQLの場合、このドライバはDBD::mysqlと呼ばれます。
DBDは、Oracleなど、各種データベース用のものが配布されており、DBIは各DBDに共通で使えます。階層的にあらわすと、「DBMS(MySQL等) ⇔ DBD ⇔ DBI ⇔ Perl プログラム」というようになっており、つまり、DBMSとDBIの間をとりもち、DBMS毎の差異を吸収するのがDBDということになります。 これを用いることにより、DBMSが例えばOracleからMySQLに変わっても、理論的にはプログラムの修正が不要（SQLレベルの変換は当然行われませんので、DBMS依存のSQLが含まれている場合は修正が必要です）となります。
CPANからモジュールダウンロードに関する環境設定はPerlの設定を参照してください。
MySQLを利用しますので、ソースからインストールする場合はCPANからDBIとDBD::mysqlをダウンロードします。
・yumコマンドを使用する場合
# yum -y install perl-DBI # yum -y install perl-DBD-mysql・cpanコマンドを使用する場合
# cpan DBI # cpan DBD::mysql・ソースからインストールする場合
cpanコマンドを利用するか、下記の方法によりインストールします。
$ cd /usr/local/src $ tar zxvf DBI-1.608.tar.gz $ cd DBI-1.608 $ perl Makefile.PL $ make $ su # make install $ cd /usr/local/src $ tar zxvf DBD-mysql-4.012.tar.gz $ cd DBD-mysql-4.011 $ perl Makefile.PL $ make $ su # make install
サンプルデータベースの作成
今回は、氏名と電話番号および電子メールアドレスを管理する簡易アドレス帳のサンプルデータベースを作成します。
まず、MySQLのプロンプトでcreate table文を発行して、テーブルを定義します。
# mysql -u root -p ← rootでMySQLにログイン mysql> create database addressdb; ← データベース「addressdb」の作成 mysql> show databases; ← 作成したデータベースの確認 mysql> use addressdb; ← データベース「addressdb」に接続 mysql> create table addrbook (name varchar(50), telno varchar(50), email varchar(50)); ↑ テーブル「addrbook」の作成 mysql> show tables; ← テーブル「addrbook」が作成されているかどうかの確認 mysql> \q ← mysqlモードから抜ける以下の内容のテキストファイルを用意し、addrbook.txtというファイル名で保存します。スペースの部分はTABにしてください。
# su - mysql --shell=/bin/bash $ vi addrbook.txt R.Nomura 090-2298-**** nomura.r@mail.com D.Tanaka 080-****-**** dtanaka@post.co.jp I.Yanagida 090-1987-**** ichi.yanagida@elect.netこのファイルを「mysqlimport」コマンドを使ってインポートします。
$ mysqlimport --local addressdb addrbook.txt -u rootコピーされた内容を確認します。
$ mysql -u root -p ← rootでMySQLにログイン mysql> grant select,insert,update,delete on addressdb.* to myuser@localhost identified by 'mypass'; mysql> flush privileges; mysql> \q $ mysql -u myuser -p ← myuserでMySQLにログイン mysql> use addressdb; mysql> select * from addrbook; mysql> \q以上でサンプルデータベースの用意ができました。
Perlのプログラム（表示用） DBI + DBD版
PerlでMySQLに作成したデータベーステーブルの表示を行うプログラムを作成してみます。
PerlでMySQLに接続するには、下記のような手法を用います。
use DBI;
my $dbh = DBI->connect("DBI:mysql:$database:$hostname:$port",$user, $password);
以下は、先ほど作成したテーブルの内容を表示する簡単なプログラムのリストです。
$ vi test1_mysql.pl ← test1_mysql.plの編集 #!/usr/bin/perl ← 環境に合わせる use DBI; $dbname = "addressdb"; $hostname = "localhost"; $username = "addressdb_user"; ← データベース接続ユーザ名 $password= "addrbook_pass"; ← 接続ユーザのパスワード $tblname = "addrbook"; $sql = "select * from $tblname"; $conn = DBI->connect("DBI:mysql:$dbname:$hostname", $username, $password); $res = $conn->prepare($sql); $st = $res->execute(); print "name telno email\n"; print "--------------------------------------\n"; while(@item = $res->fetchrow_array()) { print "@item[0] @item[1] @item[2]"; print ("\n"); } $res->finish; $conn->disconnect; $ chmod +x test1_mysql.pl ← test1_mysql.plに実行権限の設定SQL文を用意し（大文字・小文字の区別はない）、「prepare()」でセットして、「execute()」で実行します。取得したデータは「fetchrow_array()」で1行ずつ取得します。戻り値に指定した配列に1行文のデータが格納されているので、取得できなくなるまでループさせます。データが1行しかないと分かっている場合は、ループさせる必要はありません。
Perlのプログラム（追記用） DBI + DBD版
PerlでMySQLに作成したデータベーステーブルに追記を行うプログラムを作成してみます。以下は、先ほど作成したテーブルの内容に追記する簡単なプログラムのリストです。
$ vi test2_mysql.pl ← test2_mysql.plの編集 #!/usr/bin/perl ← 環境に合わせる use DBI; $dbname = "addressdb"; $hostname = "localhost"; $username = "addressdb_user"; ← データベース接続ユーザ名 $password= "addrbook_pass"; ← 接続ユーザのパスワード $tblname = "addrbook"; $sql = "insert into $tblname values('S.Shirakawa','090-0000-6542','Shibakawa\@mailserver.net')"; $conn = DBI->connect("DBI:mysql:$dbname:$hostname", $username, $password); $res = $conn->prepare($sql); $st = $res->execute(); #以下、表示用 $sql2 = "select * from $tblname"; $res2 = $conn->prepare($sql2); $st = $res2->execute(); print "name telno email\n"; print "--------------------------------------\n"; while(@item = $res2->fetchrow_array()) { print "@item[0] @item[1] @item[2]"; print ("\n"); } $res->finish; $conn->disconnect; $ chmod +x test2_mysql.pl ← test2_mysql.plに実行権限の設定
●RubyとMySQLの連携
RubyとMySQLを接続するためには，
1. MySQL/Ruby
2. Ruby/MySQL
3. Ruby/DBI
のいずれかをインストールする必要があります。
Ruby/DBI https://rubygems.org/gems/dbi
DBD::Pg https://rubygems.org/gems/dbd-pg
pg https://rubygems.org/gems/pg
しかし、開発者によるとdbiはRuby 1.8までしかサポートされていないようです。
https://github.com/erikh/ruby-dbi/issues/5
インストールは、下記のように実施します。
$ gem install pg $ gem install rdbi $ gem install rdbi-driver-postgresql上記では、同開発者がRuby/DBIの"Version2"として作成したRDBIの使用を推奨している。
※下記はruby 1.8までの話として残しておきます。
ーーーーー ここから ーーーーー
RubyからMySQLを使うには、MySQL/RubyまたはRuby/MySQLライブラリが必要です。MySQL/RubyはCで書かれているのでコンパイラが必要だけど高速、Ruby/MySQLはRubyで書かれていますので低速だけどコンパイラ不要という特徴があります。
ただし、このライブラリを直接使うとデータベースを変えることになったときに書換量が莫大になってしまいますので、データベースを変える可能性があるならばRuby/DBIで抽象化したほうがよい。Ruby/DBIを使用すると、Rubyプログラム → DBI（データベース・インターフェース）→ DBD（データベース・ドライバー）→ データベース・クライアント・ライブラリ → データベースというスタックになり、データベースに依存する部分がプログラムからは隠蔽される。なお、RubyのDBDはアダプターであってクライアント・ライブラリ自身ではないので、クライアント・ライブラリであるMySQL/RubyまたはRuby/MySQLが別途必要になります。
1.を使用するには、ソースからインストールしたMySQL/Rubyをコンパイルするにあたって必要なRPM、ruby-develとmysql-develを入れておく必要があります。
Mysql及びRubyともバイナリ（RPM）でインストールしている場合は、ruby-mysqlのバイナリ（RPM）をインストールすることで連携が可能となります。
ソースはRubyForgeプロジェクトから、最新版の「mysql-ruby-*.*.*.tar.gz」をダウンロードし、インストールする方法があります。
・yumコマンドを利用する場合# yum -y install ruby-mysql・ソースからインストールする場合
# yum -y install ruby-devel mysql-devel $ cd /usr/local/src $ wget http://rubyforge.org/frs/download.php/51087/mysql-ruby-2.8.1.tar.gz $ tar zxvf mysql-ruby-2.8.1.tar.gz $ cd mysql-ruby-2.8.1 $ ruby extconf.rb --with-mysql-include=/usr/include/mysql --with-mysql-lib=/usr/lib/mysql $ makeここまでいったら、簡単なテストを実行します。$ ruby test.rb hostname username password問題がなければ、スーパーユーザでインストールします。$ su # make install実際の状況は、下記のとおりです。# make install ： gcc -shared -o mysql.so mysql.o -L. -L/usr/local/lib -Wl,-R/usr/local/lib -L/usr/lib/mysql -Wl, -R/usr/lib/mysql -L/usr/local/lib -Wl,\ -R/usr/local/lib -L. -rdynamic -Wl, -export-dynamic -lmysqlclient -lrt -ldl -lcrypt -lm -lc /usr/bin/install -c -m 0755 mysql.so /usr/local/lib/ruby/site_ruby/1.8/i686-linux2.については、省略します。
3.を使用するには、RubyForgeプロジェクトのRuby/DBIからダウンロードし、インストールします。Ruby DBIモジュールを使うが参考になります。
Ruby DBIモジュールのRPM版は無いようだ・・。$ cd /usr/local/src $ wget http://rubyforge.org/frs/download.php/47537/dbi-0.4.1.tar.gz $ tar zxvf dbi-0.4.1.tar.gz $ cd dbi-0.4.1.tar.gz $ ruby setup.rb config $ ruby setup.rb setup $ su # ruby setup.rb install上記の方法でDBIをインストールしてみましたが、下記のようなエラーが表示されてDBIを利用してデータベースに接続できませんでした。/usr/local/lib/ruby/site_ruby/1.8/dbi.rb:48:in `require': no such file to load -- deprecated (LoadError)そこで、DBI（dbi-0.1.1.tar.gz）は，パッチを当てて普通にインストールします。バージョン0.4.1より古く、DBD:Pgも同梱されているらしい。$ cd /usr/local/src $ tar zxvf dbi-0.1.1.tar.gz $ cd ruby-dbi $ patch lib/dbi/sql.rb < /usr/local/src/dbi-0.1.1_sql.rb.patch ↑ patchを適用しなくても動作する $ ruby setup.rb config ↑ このように実行すると、デフォルトですべてのドライバがインストール対象となります。 インストールするドライバを選択したい場合、--with オプションにドライバの名前を列挙します。 例えば、DBI のメイン部分と MySQL の DBD レベルのドライバのみをインストールする ときは、次のコマンドを発行します。 （$ ruby setup.rb config --with=dbi,dbd_mysql） $ ruby setup.rb setup $ su # ruby setup.rb install実際の状況は下記のようになります。$ ruby setup.rb config --with=dbi,dbd_mysql entering config phase... config done. $ ruby setup.rb setup entering setup phase... setting #! line to "#!/usr/bin/ruby" setup.rb: skip bin/proxyserver(dbd_proxy) by user option setup.rb: skip ext/dbd_sqlite(dbd_sqlite) by user option setup.rb: skip ext/dbd_sybase(dbd_sybase) by user option setup done. $ su # ruby setup.rb install entering install phase... mkdir -p /usr/bin install sqlsh.rb /usr/bin setup.rb: skip bin/proxyserver(dbd_proxy) by user option mkdir -p /usr/lib/ruby/site_ruby/1.8/DBD/Mysql install Mysql.rb /usr/lib/ruby/site_ruby/1.8/DBD/Mysql/Mysql.rb mkdir -p /usr/lib/ruby/site_ruby/1.8/dbi install utils.rb /usr/lib/ruby/site_ruby/1.8/dbi install row.rb /usr/lib/ruby/site_ruby/1.8/dbi install columninfo.rb /usr/lib/ruby/site_ruby/1.8/dbi install trace.rb /usr/lib/ruby/site_ruby/1.8/dbi install sql.rb /usr/lib/ruby/site_ruby/1.8/dbi install version.rb /usr/lib/ruby/site_ruby/1.8/dbi install dbi.rb /usr/lib/ruby/site_ruby/1.8 setup.rb: skip ext/dbd_sqlite(dbd_sqlite) by user option setup.rb: skip ext/dbd_sybase(dbd_sybase) by user option install done.
ーーーーー ここまで ーーーーー
サンプルデータベースの作成
データベースは、PerlとMySQLとの連携で作成したものを使用します。
Rubyのプログラム（表示用） MySQL/Ruby版
RubyでMySQLに作成したデータベーステーブルの表示を行うプログラムを作成してみます。以下は、先ほど作成したテーブルの内容を表示する簡単なプログラムのリストです。
$ vi test1_mysql.rb #!/usr/bin/ruby ← 環境に合わせる require "mysql" $dbname = "addressdb"; $hostname = "localhost"; $username = "addressdb_user"; ← データベース接続ユーザ名 $password= "addrbook_pass"; ← 接続ユーザのパスワード ↑ 上記ユーザ名・パスワードはMySQL用です $tblname = "addrbook"; $sql = "select * from " + $tblname + ";" ← 注意 conn = Mysql.new($hostname, $username, $password, $dbname) ↑ 「Mysql」の「M」は大文字の必要あり ↑ 「new」は「connect」でも可能 # 表示メソッド def view(conn) res = conn.query($sql) res.each{|items| print items[0] (items.size-1).times{|i| print ",", items[i+1] } print "\n" } end view(conn) #上記の方法がスマート #res = conn.query($sql) #res.each do |row| # puts row.join ", " #end conn.close実行すると、下記のような警告が表示される事があります（Ruby1.8.6で表示され、Ruby1.8.7では表示されませんでした）。
・・・warning: parenthesize arugument(s) for future versionこの場合は、「puts row.join ", "」を「puts row.join(", ")」に変更してください。
Rubyのプログラム（追加用） MySQL/Ruby版
データを追加するプログラムを紹介します。
$ vi test2_mysql.rb #!/usr/bin/ruby ← 環境に合わせる require "mysql" $dbname = "addressdb"; $hostname = "localhost"; $username = "addressdb_user"; ← データベース接続ユーザ名 $password= "addrbook_pass"; ← 接続ユーザのパスワード ↑ 上記ユーザ名・パスワードはMySQL用です $tblname = "addrbook"; $sql = "select * from " + $tblname + ";" conn = Mysql.new($hostname, $username, $password, $dbname) # 表示メソッド def view(conn) res = conn.query($sql) res.each{|items| print items[0] (items.size-1).times{|i| print ",", items[i+1] } print "\n" } end begin # データの挿入 conn.query("insert into " + $tblname + " (name,telno,email) values ('user1','user1','pass');") view(conn) # データの更新 #conn.query("update " + $tblname + " set email = 'user1@mail1.dot.com' where name = 'user1';") #view(conn) # データの削除 #conn.query("delete from " + $tblname + " where name = 'user1';") #view(conn) rescue => ex conn.query("ROLLBACK;") print ex.message, "\n" ensure conn.close() end注意：
SQL文の記載方法は、
$sql = "select * from $tblname;"ではなく
$sql = "select * from " + $tblname + ";"のようにしなければエラーが表示されます。
Rubyのプログラム（表示用） DBI + DBD版
MySQLで作成したデータベーステーブルの表示を行うプログラムを作成してみます。以下は、先ほど作成したテーブルの内容を表示する簡単なプログラムのリストです。
$ vi test3_mysql.rb #!/usr/local/bin/ruby ← 環境に合わせる require "dbi" $dbname = "addressdb"; $hostname = "localhost"; $username = "addressdb_user"; ← データベース接続ユーザ名 $password= "addrbook_pass"; ← 接続ユーザのパスワード ↑ 上記ユーザ名・パスワードはMySQL用です $tblname = "addrbook"; $sql = "select * from " + $tblname + ";" ← 注意 dbh = DBI.connect("dbi:Mysql:" + $dbname + ":" + $hostname, $username, $password) sth = dbh.prepare($sql) sth.execute sth.each do |row| puts row.join ", " end sth.finish dbh.disconnect実行すると、下記のような警告が表示される事があります（Ruby1.8.6で表示され、Ruby1.8.7では表示されませんでした）。
・・・warning: parenthesize arugument(s) for future versionこの場合は、「puts row.join ", "」を「puts row.join(", ")」に変更してください。
Rubyのプログラム（追加用） DBI + DBD版
PerlでMySQLに作成したデータベーステーブルの表示を行うプログラムを作成してみます。以下は、先ほど作成したテーブルの内容を表示する簡単なプログラムのリストです。
$ vi test4_mysql.rb #!/usr/local/bin/ruby ← 環境に合わせる require "dbi" $dbname = "addressdb"; $hostname = "localhost"; $username = "addressdb_user"; ← データベース接続ユーザ名 $password= "addrbook_pass"; ← 接続ユーザのパスワード ↑ 上記ユーザ名・パスワードはMySQL用です $tblname = "addrbook"; $sql = "select * from " + $tblname + ";" ← 注意 dbh = DBI.connect("dbi:Mysql:" + $dbname + ":" + $hostname, $username, $password) sth = dbh.prepare($sql) sth.execute begin # データの挿入 dbh.do("insert into " + $tblname + " (name,telno,email) values ('user1','user1','pass');") # データの更新 # dbh.do("update " + $tblname + " set email = 'user1@mail1.dot.com' where name = 'user1';") # データの削除 # dbh.do("delete from " + $tblname + " where name = 'user1';") sth.each do |row| puts row.join ", " end rescue puts "transaction failed" dbh.rollback ensure sth.finish dbh.disconnect end
●データベースの作成
【MySQL基本設定値】
| 設定項目 | 設定値 |
|---|---|
| データベース名 | auth |
| ユーザID | myuser |
| パスワード | mypass |
| テーブル名 | city |
【cityテーブルスキーマ】
| カラム名 | データ型 |
|---|---|
| country | varchar(20) |
| capital | varchar(20) |
【cityテーブル挿入データ】
| country | capital |
|---|---|
| Japan | Tokyo |
| USA | NewYork |
上記データベースを作成します。
# mysql -u root -p Enter password: ← 設定したパスワードを入力 補足：MySQLサーバに接続する $ mysql -uユーザ名 -p データベース名 Enter password: [パスワード] デフォルトの状態であれば、下記の方法でログイン可能。 # mysql -u root -p test 補足：MySQLサーバから切断方法 「\q」、「exit」、「quit」、「Control-D」のいずれかを入力します。 補足：接続に関する情報を知りたい コネクションIDや、データベース名、ユーザ名、バージョンなどを知ることができます。 mysql> \s mysql> create database auth; ← データベース「auth」を作成 補足：データベースの削除方法 mysql> drop database データベース名; mysql> show databases; ← データベース「auth」が作成されているか確認 mysql> use auth; ← データベース「auth」に接続 Database changed mysql> create table city (country varchar(20),capital varchar(20)); ↑ テーブル「city」の作成 mysql> show tables; ← テーブル「city」が作成されているか確認 +----------------+ | Tables_in_auth | +----------------+ | city | +----------------+ 1 row in set (0.00 sec) 補足：テーブルの削除方法 mysql> drop table テーブル名; mysql> describe city; ← テーブル「city」のカラム情報の確認 +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | country | varchar(20) | YES | | NULL | | | capital | varchar(20) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 2 rows in set (0.02 sec) mysql> insert into city (country,capital) values('JAPAN','TOKYO'),('USA','NewYork'); ← データの作成 Query OK, 2 rows affected (0.00 sec) Records: 2 Duplidates: 0 Warnings: 0 mysql> select * from city; ← テーブル「city」の内容確認 補足：テキストデータのインポート方法 $ mysqlimport --local addressdb addrbook.txt -u root -p ↑ データベース「auth」に、区切り文字が「TAB」で作成された「addrbook.txt」のインポート 補足：レコードの削除方法 mysql> delete from city where country='USA'; ← 「USA」レコードの削除 mysql> delete from city; ← テーブル「city」の全レコード削除 mysql> grant select on auth.* to myuser@localhost identified by 'mypass'; ↑ データベース「auth」に対し、ユーザー名：myuser パスワード：mypass で参照のみ可能なユーザーを作成 mysql> flush privileges; 補足：通常のデータ操作が行えるユーザを登録する mysql> grant select,insert,update,delete -> on データベース名.* -> to ユーザ名@localhost -> identified by 'パスワード'; mysql> flush privileges; ・1つのテーブルに対して権限を持つユーザを登録するときには、「データベース名.*」を 「データベース名.テーブル名」とします。 ・他のホストから接続するユーザを登録するときには、「localhost」を 「ホスト名（IPアドレス）」とします。 補足：管理者権限を持つユーザを登録する MySQLのユーザ「root」と同じ、管理者権限を持つユーザ「user1」を登録するには、 次のような2つのGRANT構文を実行します。 MySQLのユーザ「user1」にはパスワードを設定しています。 例：管理者権限を持つユーザを登録する mysql> grant all privileges on *.* to user1@"%" identified by 'パスワード' with grant option; mysql> grant all privileges on *.* to user1@localhost identified by 'パスワード' with grant option; mysql> flush privileges; どのホストからでもサーバに接続できるようにするために、「user1@"%"」と 「user1@localhost」を指定した、2つのGRANT構文を実行しています。 （localhostから接続できるようにするためには、「user1@localhost」を指定する必要があります） また、「WITH GRANT OPTION」によりGRANT構文の実行権限を与えています。 ・管理者権限を与えるパラメータについて 権限：ALL PRIVILEGES（すべての権限） データベース名.テーブル名：*.*（すべてのデータベースのすべてのテーブル） ユーザ名：user1@"%" （すべてのホスト「"%"」の、ユーザ「user1」） user1@localhost（ローカルホストの、ユーザ「user1」） GRANT実行権限：（GRANT構文を実行する権限） 補足：ユーザの権限を確認するには mysql> select * from mysql.user; or ← すべてのユーザの権限を確認 mysql> select * from mysql.user \G * \Gを付けると結果が1レコードごとに垂直に表示されます。 mysql> select * from mysql.user where user='ユーザ名'; ↑ 特定のユーザの権限を確認 mysql> select * from mysql.user where user like '%a%'; ↑ 文字「a」がユーザ名に含まれるユーザの権限を確認 補足：登録されているユーザを確認するには mysql> select host, user, password from mysql.user; or mysql> select host, user, password from mysql.user \G \Gを付けると結果が1レコードごとに垂直に表示されます。 補足：登録されているホストとユーザを確認するには mysql> select host,user from mysql.user; +-------------------------+-----------+ | host | user | +-------------------------+-----------+ | 127.0.0.1 | root | | ::1 | root | | localhost | cacti | | localhost | | | localhost | hoge | | localhost | root | | hoge.bigbang.mydns.jp | | | hoge.bigbang.mydns.jp | root | | hoge2 | root | +-------------------------+-----------+ 9 rows in set (0.00 sec) 補足：ユーザを削除するには mysql> drop user ユーザ名; 補足：ユーザや権限を管理するテーブルについて データベース「mysql」には、ユーザや権限を管理するための5つのテーブルがあります。 5つのテーブルには、4つの権限レベルに対応した権限が格納されます。 ・user：Globalレベル ユーザの基本的な定義を管理するテーブルです。 サーバ上のすべてのデータベースに適用される権限について格納します。 ・host：Databaseレベル 接続してきたクライアントのホストに対する権限の定義を管理するテーブルです。 データベース内のすべてのテーブルに適用される権限について格納します。 ・db：Databaseレベル データベースに対するアクセスの権限の定義を管理するテーブルです。 データベース内のすべてのテーブルに適用される権限について格納します。 mysql> select * from mysql.db where Db='auth' \G ↑ データベース「auth」に対するアクセス権限の表示 ・tables_priv：Tableレベル テーブルに対する権限の定義を管理するテーブル テーブル内のすべてのフィールドに適用される権限について格納します。 ・columns_priv：Columnレベル フィールドに対する権限の定義を管理するテーブルです。 テーブル内の一つのフィールドに適用される権限について格納します。 これらのテーブルでは、ホスト、ユーザ、パスワード、データベース、 カラム、権限などについて記録しています。 権限を記録するカラム名は権限と対応しています。（Y：権限がある、N：権限がない）
●データベースのバックアップ・リストア
データベースをコピーするには下記のようにします。
データベース「addressdb」をカレントディレクトリに「addressdb.sql」としてバックアップ
$ mysqldump -u root -p --default-character-set=utf8 addressdb > addressdb.sql
Enter password:
$ ls -l addressdb.sql
-rw-rw-r-- 1 user user 2221 7月 6 15:08 addressdb.sql
データベース「addressdb2」を作成し、「addressdb.sql」をリストア
$ mysqladmin -u root -p create addressdb2
Enter password:
$ mysql -u root -p addressdb2 < addressdb.sql
Enter password:
$ mysql -u root -p
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 320492
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| addressdb |
| addressdb2 |
| cacti |
| mysql |
| performance_schema |
| test |
| zabbix |
+--------------------+
11 rows in set (0.00 sec)
MariaDB [(none)]> use addressdb2;
Database changed
MariaDB [addressdb2]> show tables;
+----------------------+
| Tables_in_addressdb2 |
+----------------------+
| addrbook |
+----------------------+
1 row in set (0.00 sec)
MariaDB [addressdb2]> select * from addrbook;
テーブルが参照出来れば問題なくコピーできています。
●テーブルのバックアップ・リストア
テーブルをコピーするには下記のようにします。
データベース「addressdb」内のテーブル「addrbook」をカレントディレクトリに「addrbook.sql」としてバックアップ
$ mysqldump -u root -p --default-character-set=utf8 addressdb addrbook.sql > addrbook.sql
Enter password:
$ ls -l addrbook.sql
-rw-rw-r-- 1 user user 1546 7月 6 15:28 addrbook.sql
データベース「addressdb2」を作成し、テーブル「addrbook.sql」をリストア
$ mysqladmin -u root -p create addressdb2
Enter password:
$ mysql -u root -p addressdb2 < addrbook.sql
Enter password:
$ mysql -u root -p
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 320492
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| addressdb |
| addressdb2 |
| cacti |
| mysql |
| performance_schema |
| test |
| zabbix |
+--------------------+
11 rows in set (0.00 sec)
MariaDB [(none)]> use addressdb2;
Database changed
MariaDB [addressdb2]> show tables;
+----------------------+
| Tables_in_addressdb2 |
+----------------------+
| addrbook |
+----------------------+
1 row in set (0.00 sec)
MariaDB [addressdb2]> select * from addrbook;
テーブルが参照出来れば問題なくコピーできています。
●バックアップしたデータベースから特定のテーブルをリストアする方法
参考URL：MySQLのダンプファイルから任意のテーブルのみリストアする
バックアップしたダンプファイルが全体のデータベース、または、特定のデータベースである場合、その中から任意のテーブルのみをリストアする場合は下記のように実施します。
$ csplit backup_mysql/zabbix_dump.db_20181203 '/DROP TABLE IF EXISTS/' {*}
973
114834321
131706177
56268
1664480379
：
：
ダンプファイルがgzipで圧縮されている場合は下記のように実施します。
$ zcat /path/to/dumpfile | csplit - '/DROP TABLE IF EXISTS/' {*}
標準入力から読み込む場合は、対象ファイル名に 「-」 に指定します。
いずれもコマンドを実行しているカレントディレクトリに、csplitにより、「xxNNN」（NNNはゼロ埋めされた連番）のようなファイルに分割されるので、
$ grep -il 'create table `tablename`'復元したいテーブルが含まれるファイルを特定して、
$ mysql -u ユーザ名 -p データベース名 < xxNNN Enter password:特定のテーブルのみリストアすることができます。
ダンプファイルのサイズが大きい場合やメモリに余裕がない場合などは、以下のようなエラーが出力されファイルを分割できないことがあります。
csplit: memory exhausted
●データベース・テーブルのサイズ確認方法
参考URL：MySQLでDBとテーブルのサイズを確認するSQL
データベースサイズの確認方法は下記のとおりです。
SELECT table_schema, sum(data_length) /1024/1024 AS Mbyte FROM information_schema.tables GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC;
テーブルサイズの確認方法は下記のとおりです。
use 'データベース名'; #調査対象データベースを選択 SELECT table_name, engine, table_rows AS tbl_rows, avg_row_length AS rlen, floor((data_length+index_length)/1024/1024) AS allMbyte, #総容量 floor((data_length)/1024/1024) AS dataMbyte, #データ容量 floor((index_length)/1024/1024) AS indexMbyte #インデックス容量 FROM information_schema.tables WHERE table_schema=database() ORDER BY (data_length+index_length) DESC;
●レプリケーションの設定
バージョン10.3系
参考URL：MariaDB 10.3 : レプリケーション
参考URL：Pacemaker/Corosyncを用いた冗長化Zabbix Serverの構築
参考URL：Pacemaker で Zabbixサーバ をHAクラスタ化してみた (リソース登録編)
レプリケーション用ユーザ（repl）を両ホストで作成します。
※通常のレプリケーション（Master/Slave）で運用する場合 # mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 18 Server version: 10.3.27-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> grant replication slave on *.* to 'repl'@'%' identified BY '[パスワード]'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> quit Bye ※Pacemakerの制御によるMaster/Slaveで運用する場合 # mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 18 Server version: 10.3.27-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> create user 'repl'@'%' identified by '[パスワード]'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> create user 'repl'@'localhost' identified by '[パスワード]'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> grant replication slave on *.* to 'repl'@'%'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> grant process, super, replication slave, replication client, reload on *.* to 'repl'@'localhost'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> quit;レプリケーション（マスター側で設定）を設定します。
バイナリログは更新系のログが記録されますが、放っておくと際限なく増えますので必ず保存日数を指定します。
参考URL：MariaDB(10.3.23)でレプリケーション
参考URL：MySQLのバイナリログ削除タイミング
参考URL：バイナリログの削除
参考URL：【Zabbix】MySQLのバイナリファイルが肥大化してDBが立ち上がらなかった
[root@centos8-str3 ~]# vi /etc/my.cnf.d/mariadb-server.cnf [mysqld] # [mysqld] セクション内に追記 : バイナリログを取得 log-bin=mysql-bin # サーバーID を定義 server-id=101 # バイナリログ（おおよその）最大サイズ max_binlog_size=100M # バイナリログ保存日数 expire_logs_days=10 [root@centos8-str3 ~]# systemctl restart mariadb [root@centos8-str3 ~]# firewall-cmd --add-service=mysql --permanent [root@centos8-str3 ~]# firewall-cmd --reload [root@centos8-str3 ~]# firewall-cmd --list-serviceレプリケーション（スレーブ側で設定）を設定します。
[root@centos8-str4 ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
# [mysqld] セクション内に追記 : バイナリログを取得
log-bin=mysql-bin
# サーバーID を定義 (マスター側とは異なる ID)
server-id=102
# リードオンリー（※Pacemakerの制御によるMaster/Slaveで運用する場合は設定不要）
read_only=1
# 自身のホスト名を定義
report-host=centos8-str4.bigbang.mydns.jp
# バイナリログ（おおよその）最大サイズ
max_binlog_size=100M
# バイナリログ保存日数
expire_logs_days=10
[root@centos8-str4 ~]# systemctl restart mariadb
[root@centos8-str4 ~]# firewall-cmd --add-service=mysql --permanent
[root@centos8-str4 ~]# firewall-cmd --reload
[root@centos8-str4 ~]# firewall-cmd --list-service
マスター側のDBデータをスレーブ側に移行します。
スレーブ側のDBデータを削除します。
[root@centos8-str4 ~]# systemctl stop mariadb [root@centos8-str4 ~]# rm -rf /var/lib/mysql次に、マスターのDBデータをスレーブ側に転送します。
[root@centos8-str3 ~]# systemctl stop mariadb [root@centos8-str3 ~]# tar cf - -C /var/lib mysql | ssh root@centos8-str4 tar xpf - -C /var/lib root@172.17.0.44's password: [root@centos8-str3 ~]# systemctl start mariadbプライマリーサーバー側でダンプデータを取得します。
取得したデータは sftp や rsync 等を使用して、レプリカサーバー側へ転送します。
[root@centos8-str3 ~]# mkdir /home/mariadb_backup
[root@centos8-str3 ~]# systemctl start mariadb
[root@centos8-str3 ~]# mariabackup --backup --target-dir /home/mariadb_backup -u root -p [パスワード]
[00] 2021-04-05 08:58:46 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
[00] 2021-04-05 08:58:46 Using server version 10.3.27-MariaDB-log
mariabackup based on MariaDB server 10.3.27-MariaDB Linux (x86_64)
[00] 2021-04-05 08:58:46 uses posix_fadvise().
[00] 2021-04-05 08:58:46 cd to /var/lib/mysql/
[00] 2021-04-05 08:58:46 open files limit requested 0, set to 1024
[00] 2021-04-05 08:58:46 mariabackup: using the following InnoDB configuration:
[00] 2021-04-05 08:58:46 innodb_data_home_dir =
[00] 2021-04-05 08:58:46 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2021-04-05 08:58:46 innodb_log_group_home_dir = ./
[00] 2021-04-05 08:58:46 InnoDB: Using Linux native AIO
2021-04-05 8:58:46 0 [Note] InnoDB: Number of pools: 1
[00] 2021-04-05 08:58:46 mariabackup: Generating a list of tablespaces
2021-04-05 8:58:46 0 [Warning] InnoDB: Allocated tablespace ID 1 for mysql/innodb_table_stats, old maximum was 0
[00] 2021-04-05 08:58:46 >> log scanned up to (1632655)
[01] 2021-04-05 08:58:46 Copying ibdata1 to /home/mariadb_backup/ibdata1
[01] 2021-04-05 08:58:46 ...done
.....
.....
[01] 2021-04-05 08:58:47 Copying ./aria_log.00000001 to /home/mariadb_backup/aria_log.00000001
[01] 2021-04-05 08:58:47 ...done
[00] 2021-04-05 08:58:47 Waiting for log copy thread to read lsn 1632655
[00] 2021-04-05 08:58:48 >> log scanned up to (1632655)
[00] 2021-04-05 08:58:48 Writing xtrabackup_binlog_info
[00] 2021-04-05 08:58:48 ...done
[00] 2021-04-05 08:58:48 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
[00] 2021-04-05 08:58:48 mariabackup: The latest check point (for incremental): '1632646'
mariabackup: Stopping log copying thread.[00] 2021-04-05 08:58:48 >> log scanned up to (1632655)
[00] 2021-04-05 08:58:48 >> log scanned up to (1632655)
[00] 2021-04-05 08:58:48 Executing UNLOCK TABLES
[00] 2021-04-05 08:58:48 All tables unlocked
[00] 2021-04-05 08:58:48 Copying ib_buffer_pool to /home/mariadb_backup/ib_buffer_pool
[00] 2021-04-05 08:58:48 ...done
[00] 2021-04-05 08:58:48 Backup created in directory '/home/mariadb_backup/'
[00] 2021-04-05 08:58:48 MySQL binlog position: filename 'mysql-bin.000001', position '328', GTID of the last change ''
[00] 2021-04-05 08:58:48 Writing backup-my.cnf
[00] 2021-04-05 08:58:48 ...done
[00] 2021-04-05 08:58:48 Writing xtrabackup_info
[00] 2021-04-05 08:58:48 ...done
[00] 2021-04-05 08:58:48 Redo log (from LSN 1632646 to 1632655) was copied.
[00] 2021-04-05 08:58:49 completed OK!
[root@centos8-str3 ~]# tar cvzf /tmp/mariadb_backup.tar.gz /home/mariadb_backup/
[root@centos8-str3 ~]# scp /tmp/mariadb_backup.tar.gz centos8-str4:/tmp
レプリカサーバ側で作業します。
# MariaDB を停止して既存データは削除
[root@centos8-str4 ~]# systemctl stop mariadb
[root@centos8-str4 ~]# rm -rf /var/lib/mysql/*
# 転送したバックアップデータ
[root@centos8-str4 ~]# ll /tmp/mariadb_backup.tar.gz
-rw-r--r-- 1 root root 211427 4月 5 09:06 /tmp/mariadb_backup.tar.gz
[root@centos8-str4 ~]# tar zxvf /tmp/mariadb_backup.tar.gz
# リストア事前タスク実行 ([completed OK] であれば OK)
[root@centos8-str4 ~]# mariabackup --prepare --target-dir /root/home/mariadb_backup
mariabackup based on MariaDB server 10.3.27-MariaDB Linux (x86_64)
[00] 2021-04-05 09:15:02 cd to /root/home/mariadb_backup/
[00] 2021-04-05 09:15:02 This target seems to be not prepared yet.
[00] 2021-04-05 09:15:02 mariabackup: using the following InnoDB configuration for recovery:
[00] 2021-04-05 09:15:02 innodb_data_home_dir = .
[00] 2021-04-05 09:15:02 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2021-04-05 09:15:02 innodb_log_group_home_dir = .
[00] 2021-04-05 09:15:02 InnoDB: Using Linux native AIO
[00] 2021-04-05 09:15:02 Starting InnoDB instance for recovery.
[00] 2021-04-05 09:15:02 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2021-04-05 9:15:02 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-04-05 9:15:02 0 [Note] InnoDB: Uses event mutexes
2021-04-05 9:15:02 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-04-05 9:15:02 0 [Note] InnoDB: Number of pools: 1
2021-04-05 9:15:02 0 [Note] InnoDB: Using SSE2 crc32 instructions
2021-04-05 9:15:02 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
2021-04-05 9:15:02 0 [Note] InnoDB: Completed initialization of buffer pool
2021-04-05 9:15:02 0 [Note] InnoDB: page_cleaner coordinator priority: -20
2021-04-05 9:15:02 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=1632646
[00] 2021-04-05 09:15:02 Last binlog file , position 0
[00] 2021-04-05 09:15:03 completed OK!
# リストア実行
[root@centos8-str4 ~]# mariabackup --copy-back --target-dir /root/home/mariadb_backup
mariabackup based on MariaDB server 10.3.27-MariaDB Linux (x86_64)
[01] 2021-04-05 09:15:50 Copying ibdata1 to /var/lib/mysql/ibdata1
[01] 2021-04-05 09:15:50 ...done
.....
.....
[01] 2021-04-05 09:15:50 ...done
[00] 2021-04-05 09:15:50 completed OK!
[root@centos8-str4 ~]# chown -R mysql. /var/lib/mysql
[root@centos8-str4 ~]# cat /root/home/mariadb_backup/xtrabackup_binlog_info
mysql-bin.000001 328
[root@centos8-str3 ~]# systemctl start mariadb
[root@centos8-str3 ~]# mysql -u root -p
Enter password:
MariaDB [(none)]> show master status;
[root@centos8-str4 ~]# systemctl start mariadb
[root@centos8-str4 ~]# mysql -u root -p
Enter password:
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 328 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> quit
# master_host=(マスターサーバーの IP アドレス)
# master_user=(レプリケーション用 ID)
# master_password=(レプリケーション用 ID のパスワード)
# master_log_file=(確認したマスターログの File 値)
# master_log_pos=(確認したマスターログの Position 値)
MariaDB [(none)]> change master to
-> master_host='172.17.0.43',
-> master_user='repl',
-> master_password='password',
-> master_log_file='mysql-bin.000001', ← show master status;で表示されるFile名
-> master_log_pos=328; ← show master status;で表示されるPosition番号
Query OK, 0 rows affected (0.005 sec)
# レプリケーション開始
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
# ステータス確認
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.43
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 328
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 328
Relay_Log_Space: 866
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 101
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
MariaDB [(none)]> quit
Bye
プライマリサーバー側で任意のデータベースを作成及び削除を実施し、レプリカサーバー側でデータベースが作成及び削除されることを確認してください。
正常に確認できれば正常に動作しています。
※Pacemakerの制御によるMaster/Slaveで運用する場合の作業はここまで
クラスタを設定します。
「pacemakerでZabbix-Server及びMariaDBのMaster/Slaveを制御する」を参照してください。
Zabbixを設定するため「PacemakerでZabbix-Serverを制御するための準備（MariaDBはMaster/Slave方式）」を参照してください。
バージョン5.1系
参考URL：MySQL レプリケーション
マスタースレーブ方式の冗長構成を構築します。
MySQLサーバはマスタ、スレーブとも基本的な作業は完了し利用できる状態になっているものとします。
また、iptables等を利用している場合は、必要なポートを開放するよう設定してください。
マスターサーバ側の設定を一部変更します。その際、レプリケーションに必要なユーザを作成します。
# vi /etc/my.cnf.d/mariadb-server.cnf [mysqld] # [mysqld] セクション内に追記 : バイナリログを取得 log-bin=mysql-bin # サーバID を定義 server-id=11 # service mysqld restart mysqld を停止中: [ OK ] mysqld を起動中: [ OK ] # mysql -u root -p Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.73-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. # レプリケーション用のユーザを作成（'password' は任意のパスワードを設定） mysql> grant replication slave on *.* to replica@'%' identified by 'password'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit Byeスレーブサーバの設定をレプリケーション用に変更します。
# vi /etc/my.cnf.d/mariadb-server.cnf [mysqld] # バイナリログを取得 log-bin=mysql-bin # サーバID を定義 (マスター側とは異なるID) server-id=12 # リードオンリー（設定したい場合のみ記載） read_only=1 # 自身のホスト名を定義 report-host=centos6-1.bigbang.mydns.jp # service mysqld restart mysqld を停止中: [ OK ] mysqld を起動中: [ OK ]マスター側でダンプデータを取得します。
# mysql -u root -p # 全テーブルをロック mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) # ステータスを表示（File, Position の値を控えておく） mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 325 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) # 上記端末は残したまま、別端末を追加で開き、ダンプを実行 # cd /tmp # mysqldump -u root -p --all-databases --lock-all-tables --events > mysql_dump.sql Enter password: # 元の端末に戻って、ロック解除 mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye # 取得したダンプをスレーブサーバーへ転送 # scp mysql_dump.sql centos6-1:/tmp/ mysql_dump.sql 100% 515KB 515.3KB/s 00:00スレーブサーバ側でレプリケーションの設定をします。
# mysql -u root -p < /tmp/mysql_dump.sql
# mysql -u root -p
mysql> change master to
-> master_host='192.168.0.33', ← マスターサーバのIP
-> master_user='replica', ← レプリケーション用ID
-> master_password='password', ← レプリケーション用IDのパスワード
-> master_log_file='mysql-bin.000001', ← マスターサーバで確認したFile値
-> master_log_pos=325; ← マスターサーバで確認したPosition値
Query OK, 0 rows affected (0.02 sec)
# レプリケーション開始
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
# ステータス確認
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.33
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1064
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 990
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1064
Relay_Log_Space: 1146
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
以上で設定は完了です。
マスター側でデータベースを作成する等してレプリケーションが行われていることを確認してください。
レプリケーション設定を無効にする
一度構築したレプリケーションを解除するには下記のようにします。
スレーブ側で以下を実行します。
# mysql -u root -p mysql> stop slave; mysql> change master to master_host=''; または、 mysql> stop slave; mysql> reset slave; また、下記の作業も必要 # vi /etc/my.cnf or # vi /etc/my.cnf.d/mariadb-server.cnf # リードオンリー # read_only=1 ← コメントアウトその後、mysqlを再起動。
マスタ側で、レプリケーション権限のユーザを削除。
master、slaveの手動切り替え
参考URL：MySQLのmasterとslaveの手動切り替え
●MariaDB（MySQL）のデータベースのフォルダを変更するには（pacemaker、DRBD対応）
参考URL：MariaDB(MySQL)のデータベースのフォルダを変更する
MariaDBのサービスを停止します。
# systemctl stop mariadb変更先のディレクトリを作成して、所有者・パーミッションを変更します
# mkdir /mnt/mysql # chmod 755 /mnt/mysql/ # chown mysql:mysql /mnt/mysql/既存のデータベースが格納されているディレクトリをコピーします
# cp -pr /var/lib/mysql/* /mnt/mysql/
# systemctl start mariadb
# mysql_install_db --datadir=/mnt/mysql/ --user=mysql
Installing MariaDB/MySQL system tables in '/mnt/mysql' ...
Installation of system tables failed! Examine the logs in
/var/log/mariadb/mariadb.log or /mnt/mysql for more information.
The problem could be conflicting information in an external
my.cnf files. You can ignore these by doing:
shell> /usr/bin/mysql_install_db --defaults-file=~/.my.cnf
You can also try to start the mysqld daemon with:
shell> /usr/libexec/mysqld --skip-grant-tables --general-log &
and use the command line tool /usr/bin/mysql
to connect to the mysql database and look at the grant tables:
shell> /usr/bin/mysql -u root mysql
mysql> show tables;
Try 'mysqld --help' if you have problems with paths. Using
--general-log gives you a log in /mnt/mysql that may be helpful.
The latest information about mysql_install_db is available at
https://mariadb.com/kb/en/installing-system-tables-mysql_install_db
You can find the latest source at https://downloads.mariadb.org and
the maria-discuss email list at https://launchpad.net/~maria-discuss
Please check all of the above before submitting a bug report
at http://mariadb.org/jira
/etc/my.cnf.dの datadir , socket の箇所を新しいディレクトリに変更します。
# cp -p /etc/my.cnf /etc/my.cnf_bk # cp -p /etc/my.cnf.d/mariadb-server.cnf /etc/my.cnf.d/mariadb-server.cnf_bk # cp -p /etc/my.cnf.d/client.cnf /etc/my.cnf.d/client.cnf_bk # vi /etc/my.cnf.d/mariadb-server.cnf 下記のように変更 [mysqld] #datadir=/var/lib/mysql #socket=/var/lib/mysql/mysql.sock datadir=/mnt/mysql socket=/mnt/mysql/mysql.sock # vi /etc/my.cnf.d/client.cnf 下記行を追加 [client] socket=/mnt/mysql/mysql.sockPHPを使用している場合、php.iniを変更します。
# vi /etc/php.ini mysqli.default_socket = /mnt/mysql/mysql.sock以上で終了です。
●ERROR 1146 (42S02): Table ‘mysql.servers’ doesn’t exist
FLUSH PRIVILEGESを実行したところ、ERROR 1146 (42S02): Table ‘mysql.servers’ doesn’t existと出力されました。どうやら権限管理に必要なテーブルがないらしい。下記のテーブルを作成します。
mysql> create database servers; ← データベース「servers」を作成示 mysql> show databases; ← データベース「servers」が作成されているか確認示 mysql> use servers; ← データベース「servers」をに接続 create table `servers` ( `Server_name` char(64) NOT NULL, `Host` char(64) NOT NULL, `Db` char(64) NOT NULL, `Username` char(64) NOT NULL, `Password` char(64) NOT NULL, `Port` int(4) DEFAULT NULL, `Socket` char(64) DEFAULT NULL, `Wrapper` char(64) NOT NULL, `Owner` char(64) NOT NULL, PRIMARY KEY (`Server_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table';
●Fedora 15にアップグレード後、Mysqlが起動しなくなった
調べてみると下記のようなエラーの記録がありました。
110909 00:39:10 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 110909 0:39:10 [Note] Plugin 'FEDERATED' is disabled. /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 110909 0:39:10 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 110909 0:39:10 InnoDB: The InnoDB memory heap is disabled 110909 0:39:10 InnoDB: Mutexes and rw_locks use GCC atomic builtins 110909 0:39:10 InnoDB: Compressed tables use zlib 1.2.5 110909 0:39:10 InnoDB: Using Linux native AIO 110909 0:39:10 InnoDB: Initializing buffer pool, size = 128.0M 110909 0:39:10 InnoDB: Completed initialization of buffer pool 110909 0:39:10 InnoDB: highest supported file format is Barracuda. 110909 0:39:10 InnoDB: Waiting for the background threads to start 110909 0:39:11 InnoDB: 1.1.8 started; log sequence number 120704057419 110909 0:39:11 [ERROR] /usr/libexec/mysqld: unknown variable 'default-character-set=utf8' 110909 0:39:11 [ERROR] Aborting 110909 0:39:11 InnoDB: Starting shutdown... 110909 0:39:12 InnoDB: Shutdown completed; log sequence number 120704057419 110909 0:39:12 [Note] /usr/libexec/mysqld: Shutdown complete 110909 00:39:12 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended上記の場合の解決方法は下記のようにします。
# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# To allow mysqld to connect to a MySQL Cluster management daemon, uncomment
# these lines and adjust the connectstring as needed.
#ndbcluster
#ndb-connectstring="nodeid=4;host=localhost:1186"
#default-character-set=utf8 ← この行をコメントアウトしました。
character-set-server=utf8
skip-character-set-client-handshake
collation-server=utf8_general_ci
init-connect=SET NAMES utf8
しかし、上記の行はMysqlでデータベース作成時のデフォルトの文字コードを設定する箇所だったので今後問題が発生する可能性があります。
「#default-character-set=utf8」の設定後、データベース「test1」を試験的に作成し確認しました。
# mysql -u root -p mysql> create database test1; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | addressdb | | addressdb2 | | addressdb_java | | auth | | mails | | mysql | | test | | test1 | ← データベースtest1が作成されています。 | zabbix | +--------------------+ 10 rows in set (0.09 sec) mysql> use test1; Database changed mysql> show variables like 'char%'; ← データベースtest1の文字コード確認 +--------------------------+----------------------------+ | 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)結果としては、問題ないようです。
●Please use mysql_upgrade to fix this error.
Mysqlのログに下記のようなエラーが記録されていました。
110909 9:59:26 [ERROR] Column count of mysql.proc is wrong. Expected 20, found 16. \ Created with MySQL 50067, now running 50514. Please use mysql_upgrade to fix this error.表示にしたがい下記を実行。
# mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck with default connection arguments Running 'mysqlcheck with default connection arguments addressdb.addrbook OK addressdb2.addrbook OK addressdb_java.addrbook OK auth.city OK mails.expires OK mysql.columns_priv OK mysql.db OK mysql.func OK mysql.help_category error : Table upgrade required. Please do "REPAIR TABLE `help_category`" or dump/reload to fix it! mysql.help_keyword error : Table upgrade required. Please do "REPAIR TABLE `help_keyword`" or dump/reload to fix it! mysql.help_relation OK mysql.help_topic error : Table upgrade required. Please do "REPAIR TABLE `help_topic`" or dump/reload to fix it! mysql.host OK mysql.proc error : Table upgrade required. Please do "REPAIR TABLE `proc`" or dump/reload to fix it! mysql.procs_priv OK mysql.servers OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name error : Table upgrade required. Please do "REPAIR TABLE `time_zone_name`" or dump/reload to fix it! mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK zabbix.acknowledges OK zabbix.actions OK zabbix.alerts OK zabbix.applications OK zabbix.auditlog OK zabbix.auditlog_details OK zabbix.autoreg_host OK zabbix.conditions OK zabbix.config OK zabbix.dchecks OK zabbix.dhosts OK zabbix.drules OK zabbix.dservices OK zabbix.escalations OK zabbix.events OK zabbix.expressions OK zabbix.functions OK zabbix.globalmacro OK zabbix.graph_theme OK zabbix.graphs OK zabbix.graphs_items OK zabbix.groups OK zabbix.help_items OK zabbix.history OK zabbix.history_log OK zabbix.history_str OK zabbix.history_str_sync OK zabbix.history_sync OK zabbix.history_text OK zabbix.history_uint OK zabbix.history_uint_sync OK zabbix.hostmacro OK zabbix.hosts OK zabbix.hosts_groups OK zabbix.hosts_profiles OK zabbix.hosts_profiles_ext OK zabbix.hosts_templates OK zabbix.housekeeper OK zabbix.httpstep OK zabbix.httpstepitem OK zabbix.httptest OK zabbix.httptestitem OK zabbix.ids OK zabbix.images OK zabbix.items OK zabbix.items_applications OK zabbix.maintenances OK zabbix.maintenances_groups OK zabbix.maintenances_hosts OK zabbix.maintenances_windows OK zabbix.mappings OK zabbix.media OK zabbix.media_type OK zabbix.node_cksum OK zabbix.nodes OK zabbix.opconditions OK zabbix.operations OK zabbix.opmediatypes OK zabbix.profiles OK zabbix.proxy_autoreg_host OK zabbix.proxy_dhistory OK zabbix.proxy_history OK zabbix.regexps OK zabbix.rights OK zabbix.screens OK zabbix.screens_items OK zabbix.scripts OK zabbix.service_alarms OK zabbix.services OK zabbix.services_links OK zabbix.services_times OK zabbix.sessions OK zabbix.slides OK zabbix.slideshows OK zabbix.sysmaps OK zabbix.sysmaps_elements OK zabbix.sysmaps_link_triggers OK zabbix.sysmaps_links OK zabbix.timeperiods OK zabbix.trends OK zabbix.trends_uint OK zabbix.trigger_depends OK zabbix.triggers OK zabbix.user_history OK zabbix.users OK zabbix.users_groups OK zabbix.usrgrp OK zabbix.valuemaps OK Repairing tables mysql.help_category OK mysql.help_keyword OK mysql.help_topic OK mysql.proc OK mysql.time_zone_name OK Running 'mysql_fix_privilege_tables'... OK念のため、Mysqlを再起動してみました。
110909 10:23:18 InnoDB: Starting shutdown... 110909 10:23:19 InnoDB: Shutdown completed; log sequence number 120787052742 110909 10:23:19 [Note] /usr/libexec/mysqld: Shutdown complete 110909 10:23:19 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 110909 10:23:21 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 110909 10:23:21 [Note] Plugin 'FEDERATED' is disabled. 110909 10:23:21 InnoDB: The InnoDB memory heap is disabled 110909 10:23:21 InnoDB: Mutexes and rw_locks use GCC atomic builtins 110909 10:23:21 InnoDB: Compressed tables use zlib 1.2.5 110909 10:23:21 InnoDB: Using Linux native AIO 110909 10:23:21 InnoDB: Initializing buffer pool, size = 128.0M 110909 10:23:21 InnoDB: Completed initialization of buffer pool 110909 10:23:21 InnoDB: highest supported file format is Barracuda. 110909 10:23:21 InnoDB: Waiting for the background threads to start 110909 10:23:22 InnoDB: 1.1.8 started; log sequence number 120787052742 110909 10:23:22 [Note] Event Scheduler: Loaded 0 events 110909 10:23:22 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.14' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)特段の問題はないようです。
●Fedora 20で「require "dbi"」で作成しているスクリプトで接続できない
どうも使用しているDBIの違いによるものらしい。
したがって、Fedora 20で動作するスクリプトに変更しました。
●CGI（Ruby + MySQL、DBI:MySQL、require "mysql"） / ruby_dbi2_mysql.rb
●接続失敗：Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)
phpえmariadbに接続してデータベース結果を表示させようとしたところ下記のようなエラーが表示されました。
接続失敗：Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)どうも/var/lib/mysql/mysql.sockが存在しないことが原因のようです。下記のように対処しました。
# touch /var/lib/mysql/mysql.sock # chown mysql:mysql /var/lib/mysql # chmod 755 /var/lib/mysql # systemctl restart mariadbこれで問題解決です。
●`connect': Host '*****' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' (Mysql::Error)
これまで正常に動作していたあるCGIをWeb経由で実行したらエラーとなってしまった。同じCGIをコマンドレベルで実行したところエラーが出力された。
`connect': Host 'hostname' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' (Mysql::Error)詳細は、下記URLを参照してください。
https://dev.mysql.com/doc/refman/5.6/ja/blocked-host.html
root権限でmysqladmin flush-hostsを実行すれば問題は解消されます。システム変数のmax_connect_errorsの値以上連続で接続失敗すると、ブロックされてしまうようだった。この値はデフォルト100（MySQL 5.6.6以前は10）であり、Zabbixやxymonによる監視で100回連続で接続失敗したことによるものと思われる。
admin権限を持ったユーザを使用することができない場合、別の（ブロックされていない）ホストからMySQLに接続し、下記のSQLを流せば問題は解消されます。
MariaDB [(none)]> FLUSH HOSTS;また、CGIのスクリプトで接続するホストをブロックされていないホスト名あるいはIPアドレスにすることにより接続できます。
ちなみに現在のコネクション数、最大コネクション数を確認するには下記のようにします。
MariaDB [(none)]> show status like 'Conn%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Connections | 10461 | +---------------+-------+ 1 row in set (0.21 sec) MariaDB [(none)]> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.00 sec)それ以降は、max_connectionsを余裕のある値に変更して対応します。
MariaDB [(none)]> set global max_connections = <設定値>;再起動できる環境であれば、mysqld起動時に「max_connect_errors」の回数を指定して起動することも可能です。
$ mysqld_safe -O max_connect_errors=10000 &ただし、再起動するたびに指定するのは面倒なので、set globalでなくmy.cnfに記述するの方がよいでしょう。
根本的な原因は解決する必要がありますが・・・（XymonやZabbix等の監視の影響かな〜）。
my.cnfに設定する場合の例
vi /etc/my.cnf [mysqld] zabbmax_connect_errors=999999999 service mysqld restart動作中のサーバに設定
$ mysql -u root -p mysql> show global variables like 'max_connect_errors'; mysql> set global max_connect_errors=999999999; mysql> quit
●全てのデータベースをバックアップするとエラーが表示されてバックアップできない
参考URL：日々の覚書 - ついに#mysql50#lost+foundデータベースとさよならするのだ
参考URL：Upgrade of MySQL schema with mysql_upgrade fails with the error: Error: Incorrect database name '#mysql50#.ssh'
mysqldumpで--all-databasesオプションを付加してバックアップを実行すると、
mysqldump: Got error: 1102: "Incorrect database name '#mysql50#.cache'" when selecting the databaseというようなエラーが表示されバックアップできませんでした。
これは、MySQLインストールが古いバージョンからMySQL 5.1以降にアップグレードされると、サーバではa-b-cなどの（古い形式の）名前が #mysql50#a-b-cとして表示され、名前を参照するために#mysql50#プリフィクスを使用するらしい。
このデータベースを削除してしまうとMySQL自体がエラーになってしまいそうなので、とりあえず存在が見えないようにすることにしました。
# vi /etc/my.cnf [mysqld] ignore-db-dir = .cache ← 追加 ignore-db-dir = .config ← 追加 # systemctl restart mariadbこの後、mysqldumpで---all-databasesオプションを付加して実行したところ、問題なくバックアップすることができました。
さて、このエラーの元になっている#mysql50#.cacheや#mysql50#.configですが、/var/lib/mysqlの下に隠しフォルダとして存在していました。
$ ls -a /var/lib/mysql/ . .bash_history .cache .config ： ：さて、この対策として問題のフォルダを別フォルダに移動し、mysql_upgradeを実行すると問題が解消しました。
# mv /var/lib/mysql/.cache var_lib_mysql/ # mv /var/lib/mysql/.config/ var_lib_mysql/ mysql_upgrade -u root -p Enter password: Phase 1/4: Fixing views Phase 2/4: Fixing table and database names Phase 3/4: Checking and upgrading tables Processing databases ： ： Phase 4/4: Running 'mysql_fix_privilege_tables' OK # vi /etc/my.cnf #ignore-db-dir = .cache ← コメントアウト #ignore-db-dir = .config ← コメントアウト # systemctl restart mariadb $ mysql -u root -p Enter password: > show databases; #mysql50#.cacheや#mysql50#.configが存在しないことを確認ようやくこれで#mysql50#・・・の魔の手から逃れることができました。
●レプリケーションの同期が取れなくなった場合の対処方法（その1）
まず、スレーブ側でレプリケーションの状態を確認します。
下記のようにSlave_IO_RunningとSlave_SQL_Runningが両方ともYesでない場合、レプリケーションに失敗しています。
mysql> show slave status\G; : Slave_IO_Running: Yes Slave_SQL_Running: No :復旧手順は次のとおりです。スレーブ側でスレーブ機能を停止し、マスタでダンプしたデータをscp等でスレーブに送り込み、それを再度importすれば正常な状態に戻ります。
※マスター側での作業
# mysql -u root -p
# 全テーブルをロック
mysql> flush tables with read lock;
# ステータスを表示（File, Position の値を控えておく）
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 3957 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
# 上記端末は残したまま、別端末を追加で開き、ダンプを実行
# cd /tmp
# mysqldump -u root -p --all-databases --lock-all-tables --events > mysql_dump.sql
Enter password:
# 元の端末に戻って、ロック解除
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
# 取得したダンプをスレーブサーバーへ転送
# scp mysql_dump.sql centos6-1:/tmp/
mysql_dump.sql 100% 515KB 515.3KB/s 00:00
※スレーブサーバ側でレプリケーションの設定をします。
# mysql -u root -p < /tmp/mysql_dump.sql
# mysql -u root -p
mysql> change master to
-> master_host='192.168.0.33', ← マスターサーバのIP
-> master_user='replica', ← レプリケーション用ID
-> master_password='password', ← レプリケーション用IDのパスワード
-> master_log_file='mysql-bin.000004', ← マスターサーバで確認したFile値
-> master_log_pos=3957; ← マスターサーバで確認したPosition値
Query OK, 0 rows affected (0.02 sec)
# レプリケーション開始
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
# ステータス確認
mysql> show slave status\G;
:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
:
このような状態になっていればレプリケーションは正常に動作しています。
●レプリケーションの同期が取れなくなった場合の対処方法（その2）
MariaDBのログに記録されていたエラーは下記のとおりです。
[ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: \ 'Could not find first log file name in binary log index file', Internal MariaDB error code: 1236まず、スレーブ側でレプリケーションの状態を確認します。
下記のようにSlave_IO_RunningとSlave_SQL_Runningが両方ともYesでない場合、レプリケーションに失敗しています。
mysql> show slave status\G; : Slave_IO_Running: Yes Slave_SQL_Running: No :どうもバイナリログに異常があるようで連携がとれていないようです。
復旧手順は次のとおりです。
まず、スレーブ側でスレーブ機能を停止し、マスター側で作業します。
※スレーブ側 [root@centos8-str4 ~]# systemctl stop mariadb [root@centos8-str4 ~]# rm -rf /var/lib/mysql/* 隠しファイル及び隠しフォルダがないことを確認 [root@centos8-str4 ~]# ls -asl /var/lib/mysql/ 合計 4 0 drwxr-xr-x 2 mysql mysql 6 4月 14 08:59 . 4 drwxr-xr-x. 74 root root 4096 4月 12 11:11 .. ※マスター側 レプリケーションに関する設定を解除 [root@centos8-str3 ~]# mysql -uroot-p Enter password: MariaDB [(none)]> drop user repl; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> drop user 'repl'@'localhost'; Query OK, 0 rows affected (0.000 sec) 該当ユーザが消えていることを確認 MariaDB [(none)]> select user,host,password from mysql.user; +--------+-----------+-------------------------------------------+ | user | host | password | +--------+-----------+-------------------------------------------+ | root | localhost | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | | root | 127.0.0.1 | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | | root | ::1 | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | | zabbix | localhost | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | +--------+-----------+-------------------------------------------+ 4 rows in set (0.001 sec) mariadb-server.cnfのレプリケーション設定部分を削除 [root@centos8-str3 ~]# vi /etc/my.cnf.d/mariadb-server.cnf マスタ側のMariaDBを再起動 [root@centos8-str3 ~]# systemctl restart mariadb [root@centos8-str3 ~]# mysql -uroot -p Enter password: バイナリログが無いことを確認 MariaDB [(none)]> show binary logs; ERROR 1381 (HY000): You are not using binary logging レプリケーションユーザの再作成 MariaDB [(none)]> create user 'repl'@'%' identified by '[パスワード]'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> create user 'repl'@'localhost' identified by '[パスワード]'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> grant replication slave on *.* to 'repl'@'%'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> grant process, super, replication slave, replication client, reload on *.* to 'repl'@'localhost'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> select user,host,password from mysql.user; +--------+-----------+-------------------------------------------+ | user | host | password | +--------+-----------+-------------------------------------------+ | root | localhost | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | | repl | localhost | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | | root | 127.0.0.1 | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | | root | ::1 | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | | zabbix | localhost | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | | repl | % | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | +--------+-----------+-------------------------------------------+ 6 rows in set (0.000 sec) MariaDB [(none)]> quit Bye レプリケーション設定箇所を元に戻します。 [root@centos8-str3 ~]# vi /etc/my.cnf.d/mariadb-server.cnf [root@centos8-str3 ~]# systemctl restart mariadb [root@centos8-str3 ~]# mysql -uroot -p Enter password:マスターからデータベースをスレーブにコピーします。
[root@centos8-str3 ~]# mkdir /home/mariadb_backup [root@centos8-str3 ~]# mariabackup --backup --target-dir /home/mariadb_backup -u root -p [パスワード] [root@centos8-str3 ~]# tar cvzf /tmp/mariadb_backup.tar.gz /home/mariadb_backup/ [root@centos8-str3 ~]# scp /tmp/mariadb_backup.tar.gz centos8-str4:/tmpスレーブで転送されたファイルを展開します。
[root@centos8-str4 ~]# tar zxvf /tmp/mariadb_backup.tar.gz MariaDBへの展開前確認（completed OK!が表示されればOK） [root@centos8-str4 ~]# mariabackup --prepare --target-dir /root/home/mariadb_backup mariabackup based on MariaDB server 10.3.27-MariaDB Linux (x86_64) [00] 2021-04-14 08:59:58 cd to /root/home/mariadb_backup/ [00] 2021-04-14 08:59:58 This target seems to be not prepared yet. [00] 2021-04-14 08:59:58 mariabackup: using the following InnoDB configuration for recovery: [00] 2021-04-14 08:59:58 innodb_data_home_dir = . [00] 2021-04-14 08:59:58 innodb_data_file_path = ibdata1:12M:autoextend [00] 2021-04-14 08:59:58 innodb_log_group_home_dir = . [00] 2021-04-14 08:59:58 InnoDB: Using Linux native AIO [00] 2021-04-14 08:59:58 Starting InnoDB instance for recovery. ： ： [00] 2021-04-14 08:59:59 completed OK!スレーブで転送されたデータベースを適用します。
[root@centos8-str4 ~]# mariabackup --copy-back --target-dir /root/home/mariadb_backup
[00] 2021-04-14 09:00:16 completed OK!
[root@centos8-str4 ~]# chown -R mysql. /var/lib/mysql
[root@centos8-str4 ~]# systemctl start mariadb
[root@centos8-str4 ~]# cat /root/home/mariadb_backup/xtrabackup_binlog_info
mysql-bin.000009 342 0-101-443288
[root@centos8-str4 ~]# mysql -uroot -p
Enter password:
MariaDB [(none)]> change master to master_host='1.0.0.43', master_user='repl', master_password='[パスワード]',
\master_log_file='mysql-bin.000009', master_log_pos=342;
Query OK, 0 rows affected (0.006 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show slave status \G;
：
：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
：
：
Slave_IO_RunningとSlave_SQL_Runningが両方ともYesであれば、マスター・スレーブで連携しています。
●ロックしているプロセスを特定し強制終了させる方法
参考URL：MySQLでロックを特定し、強制終了する
ロック状態の確認方法
ロックされている件数の確認方法は下記のとおりです。
mysql> SELECT trx_rows_locked FROM information_schema.INNODB_TRX; +-----------------+ | trx_rows_locked | +-----------------+ | 1 | +-----------------+ 1 row in set (0.00 sec)表示されている数字がロックされている行数です。
ロックされている件数とスレッドIDの確認方法は下記のとおりです。
mysql> SHOW ENGINE INNODB STATUS\G ... ------------ TRANSACTIONS ------------ Trx id counter 709 Purge done for trx's n:o < 708 undo n:o < 0 History list length 1 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 55, OS thread handle 0x7f6560413700, query id 202 localhost 127.0.0.1 rkojima SHOW ENGINE INNODB STATUS ---TRANSACTION 705, ACTIVE 254 sec 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1 MySQL thread id 5, OS thread handle 0x7f6560475700, query id 190 localhost 127.0.0.1 rkojima ...下記の行で確認することができます。
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
この部分 => 1 row lock(s),
ロックが発生しているスレッドの特定
実際にロックが発生しているスレッドのIDは、その下に出ています。
MySQL thread id 5, OS thread handle 0x7f6560475700, query id 190 localhost 127.0.0.1 rkojima
この部分 => MySQL thread id 5
このthread id は show processlist;のIdと同じです。
mysql> show processlist; +----+------+-----------------+-------------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+-------------+---------+------+-------+------------------+ | 5 | root | localhost:55238 | lock_sample | Sleep | 170 | | NULL | | 55 | root | localhost:42366 | lock_sample | Query | 0 | NULL | show processlist | +----+------+-----------------+-------------+---------+------+-------+------------------+ 2 rows in set (0.00 sec)
スレッドの強制終了
Id (processlist_id) の特定ができましたので、強制終了します。
KILL 5;
動作確認
最初に動作確認用のテーブル等を作成します。
MariaDB [(none)]> CREATE DATABASE `lock_sample` DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use lock_sample;
Database changed
MariaDB [lock_sample]> CREATE TABLE `User` (
-> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
-> `name` varchar(64) NOT NULL COMMENT '名前',
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
MariaDB [lock_sample]> INSERT INTO `User` (`name`) VALUES ("sato"), ("suzuki"), ("takahashi");
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [lock_sample]> SELECT * FROM `User`;
+----+-----------+
| id | name |
+----+-----------+
| 1 | sato |
| 2 | suzuki |
| 3 | takahashi |
+----+-----------+
3 rows in set (0.00 sec)
スレッドA
# 最初に、ロックがないことを確認します。 MariaDB [lock_sample]> SELECT trx_rows_locked FROM information_schema.INNODB_TRX; Empty set (0.00 sec) # ロック状態にします。 MariaDB [lock_sample]> start transaction; Query OK, 0 rows affected (0.00 sec) MariaDB [lock_sample]> UPDATE `User` SET `name` = "saito" WHERE `id` = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # もう一度ロック件数を表示し、１件になっていることを確認します。 MariaDB [lock_sample]> SELECT trx_rows_locked FROM information_schema.INNODB_TRX; +-----------------+ | trx_rows_locked | +-----------------+ | 1 | +-----------------+ 1 row in set (0.00 sec)もう一つコンソールを開きます。
スレッドB
MariaDB [(none)]> use lock_sample; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed # こちらの端末からもロック件数が１であることを確認します。 MariaDB [lock_sample]> SELECT trx_rows_locked FROM information_schema.INNODB_TRX; +-----------------+ | trx_rows_locked | +-----------------+ | 1 | +-----------------+ 1 row in set (0.00 sec) # どのスレッドでロックがかかっているのか確認します。 MariaDB [lock_sample]> SHOW ENGINE INNODB STATUS\G （途中省略） ------------ TRANSACTIONS ------------ （途中省略） 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1 MySQL thread id 397094, OS thread handle 0x7f346d170700, query id 17760433 localhost root ---------------------------- （途中省略） 1 row in set (0.02 sec) MariaDB [lock_sample]> show processlist; +--------+--------+-----------+-------------+---------+------+-------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +--------+--------+-----------+-------------+---------+------+-------+------------------+----------+ | 397094 | root | localhost | lock_sample | Sleep | 523 | | NULL | 0.000 | | 400995 | root | localhost | lock_sample | Query | 0 | NULL | show processlist | 0.000 | +--------+--------+-----------+-------------+---------+------+-------+------------------+----------+ 25 rows in set (0.00 sec) MariaDB [lock_sample]> UPDATE `User` SET `name` = "susuki" WHERE `id` = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [lock_sample]> UPDATE `User` SET `name` = "saito" WHERE `id` = 1; # 待ち状態なので、応答なしもう一つコンソールを開いて確認してみます。
スレッド C
MariaDB [lock_sample]> SELECT trx_rows_locked FROM information_schema.INNODB_TRX; +-----------------+ | trx_rows_locked | +-----------------+ | 1 | | 1 | +-----------------+ 2 rows in set (0.00 sec) MariaDB [lock_sample]> SHOW ENGINE INNODB STATUS\G （途中省略） ------------ TRANSACTIONS ------------ Trx id counter B3DB636 Purge done for trx's n:o < B3DACD5 undo n:o < 0 History list length 514 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION B3D6072, not started （途中省略） ---TRANSACTION B3DB5A7, ACTIVE 20 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 402001, OS thread handle 0x7f346d454700, query id 17814433 localhost root Updating UPDATE `User` SET `name` = "saito" WHERE `id` = 1 ------- TRX HAS BEEN WAITING 20 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1590 page no 3 n bits 72 index `PRIMARY` of table `lock_sample`.\ `User` trx id B3DB5A7 lock_mode X locks rec but not gap waiting ------------------ ---TRANSACTION B3D4919, ACTIVE 2715 sec 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1 MySQL thread id 397094, OS thread handle 0x7f346d170700, query id 17812031 localhost root Trx read view will not see trx with id >= B3DAC95, sees < B3DAC95 （途中省略） MariaDB [lock_sample]> show processlist; +--------+--------+-----------+-------------+---------+------+----------+---------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +--------+--------+-----------+-------------+---------+------+----------+---------------------------------------------------+----------+ | 397094 | root | localhost | lock_sample | Sleep | 165 | | NULL | 0.000 | | 400995 | root | localhost | lock_sample | Query | 0 | NULL | show processlist | 0.000 | | 402001 | root | localhost | lock_sample | Query | 38 | Updating | UPDATE `User` SET `name` = "saito" WHERE `id` = 1 | 0.000 | +--------+--------+-----------+-------------+---------+------+----------+---------------------------------------------------+----------+ 26 rows in set (0.00 sec)ここでトランザクションをはったままのセッションを強制終了させます。
MariaDB [lock_sample]> kill 397094; Query OK, 0 rows affected (0.00 sec)この直後、スレッドBの画面に切り替えたところ、UPDATEが実行されたことが確認できました。
MariaDB [lock_sample]> UPDATE `User` SET `name` = "saito" WHERE `id` = 1; Query OK, 1 row affected (18.91 sec) Rows matched: 1 Changed: 1 Warnings: 0
●断片化
参考URL：mysqlの断片化対策
参考URL：［MySQLウォッチ］第24回 台風の目になったMySQLの現状とバックアップ・ファイルの活用
大量の行削除や行追加等を実施すると、データが格納されていない空白の部分が発生しやすくなり、テーブルやインデックスが断片化されてパフォーマンスの劣化につながります。
MySQLにはいくつかのフラグメンテーション対象方法があります。
しかし、最も確実な解消策は、mysqldumpでバックアップし、テーブルを削除して、リストアすることです。MyISAMはテーブルごとにファイルを３つ作成しますので、即座にフラグメンテーションが解消されます。
テーブルのバックアップ及びリストア方法は、「●テーブルのバックアップ・リストア」を参照してください。
ストレージエンジンがInnoDBの場合は、下記の方法でも可能です。
mysql> alter table "テーブル名" engine InnoDB;ちなみに断片化しているかどうかは、下記コマンドにより確認できます。
mysql> show table status like 'tbl1'; Rows | Avg_row_length | Data_length 37158577 | 4831 | 179544539136 1 row in set (0.04 sec)37158577（Rows） x 4831（Avg_row_length） ＝ 179,544,539,136
とData_lengthを比較します。
179,544,539,136 ≒ 179,513,085,487
断片化が解消されたことがわかります。
●レプリケーションの解除方法
参考URL：MySQLのレプリケーションを完全にやめる方法
スレーブ側で下記を実行します。
stop slave; change master to master_host='';または、
stop slave; reset slave;その後、mysqlを再起動します。
マスター側で、レプリケーション権限のユーザを削除します。