MySQLとは、厳密に言えば半分フリーのデータベースです。PostgreSQLに比べるとクラスタリング(分散DB)機能がすでに実装されており、オラクル相当DBの無料版として利用できますが、企業が提供しているので、リリースも早いのですが将来の突然の有料化が心配されます。
ちなみにソースをダウンロードするときに、ユーザー登録が必要になるようだ。
# wget http://ftp.iij.ad.jp/pub/db/mysql/Downloads/MySQL-5.6/mysql-5.6.29.tar.gz # tar zxvf mysql-5.6.29.tar.gz # cd mysql-5.6.29 # yum install cmake ncurses-devel # cmake . -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DENABLED_LOCAL_INFILE=true -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all -DWITH_READLINE=O -DINSTALL_MYSQLTESTDIR= -DINSTALL_SQLBENCHDIR= # make # make install
Cmakeオプションの説明、但し一部は、my.cnfで設定できる。詳細はこちら
コマンド | 引数 | 説明 |
-DCMAKE_INSTALL_PREFIX | /usr/local/mysql | インストール先 |
-DDEFAULT_CHARSET | utf8 | デフォルトのキャラクタセット |
-DDEFAULT_COLLATION | utf8_general_ci | キャラクタセットのデフォルト照合順序 |
-DWITH_EXTRA_CHARSETS | all | エキストラキャラクタセットの指定 |
-DMYSQL_UNIX_ADDR | /var/lib/mysql/mysql.sock(初期値) | ソケット名を指定 |
-DMYSQL_TCP_PORT | 5506(初期値) | ポートを指定 |
-DMYSQL_DATADIR | /var/lib/mysql | データディレクトリ |
-DENABLED_LOCAL_INFILE | true | LOAD DATA INFILE文の実効可否 |
-DSYSCONFDIR | /etc/my.conf(初期値) | my.cnfの指定 |
-DWITH_INNOBASE_STORAGE_ENGINE | true | データベースエンジンにInnoDBを指定 |
-DWITH_READLINE | OFF(初期値) | コマンドラインにて過去に実行したコマンドの履歴表示や編集を可能 |
-DCMAKE_INCLUDE_PATH | /usr/local/ssl/include | コンパイル時のinludeを追加したい場合 |
-DCMAKE_C_FLAGS | -I/usr/local/ssl/include | コンパイル時のコンパイルオプションを付けたい場合 |
-DWITH_SSL | system | SSLオプションの指定 |
-DOPENSSL_LIBRARIES | /usr/local/ssl/lib/libssl.so | libsslの指定 |
-DCRYPTO_LIBRARY | /usr/local/ssl/lib/libcrypto.so | libcryptoの指定 |
-DINSTALL_MYSQLTESTDIR= | 引数の指定なしで、mysql-test一式を削除できる | |
-DINSTALL_SQLBENCHDIR= | 引数の指定なしで、sql-bench一式を削除できる |
※ /usr/local/mysql/bin にpathを通すことを忘れない事。
※ --libexecdir=/usr/local/mysql/bin オプションの追加で、/usr/local/libexec が生成されないが、この作業は未検証。
※ --prifexを指定した場合、mysqlに関するファイルはこのフォルダにまとめられる。
<ユーザー追加>
# groupadd mysql # useradd -g mysql -d /usr/local/mysql -s /bin/false mysql # passwd mysql # chown -R mysql:mysql /usr/local/mysql/
<ユーザー削除>
# userdel mysql
# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
# vi /etc/ld.so.conf.d/mysql.conf /usr/local/mysql/lib # ldconfig
# cd /usr/local/mysql # mkdir run # mkdir data # mkdir log # chown mysql:mysql run data log
MySQLの定義ファイルであるmy.cnfの優先参照は、下記のリストの上から順に適用していく。
番号 | 位置 | 適用 |
1 | /etc/my.cnf | MySQL 全体的に影響する定義ファイル |
2 | <each-data-dir>/my.cnf | 特定のDBに適用するように、各データファイルの中に設定する |
3 | '~/.my.cnf | ユーザー毎に設定ファイルを記述 |
# vi /etc/my.cnf [mysqld] datadir=/usr/local/mysql socket=/usr/local/mysql/run/mysql.sock user=mysql pid-file=/usr/local/mysql/run/mysqld.pid character-set-server=utf8 explicit_defaults_for_timestamp = true <- これを指定しないとワーニングがでる。 log-bin <- バイナリログを取ることを指定 log-slave-updates server-id=1 <- 任意の識別用ID。他のサーバーと重複禁止。 #gtid-mode=ON <- GTID(Global Transaction ID)機能を有効にする #enforce-gtid-consistency #skip-character-set-client-handshake #old_passwords=1 #bind-address = 192.168.0.100 <- IPアドレスは1つだけ指定可能。指定無しだと制限無し。 #skip-grant-tables <- 有効にするとパスワードチェックなどを無視する。 [mysqld_safe] log-error=/usr/local/mysql/log/mysqld.log pid-file=/usr/local/mysql/run/mysqld.pid [client] socket=/usr/local/mysql/run/mysql.sock [mysql] #default-character-set=utf8 < 最新だとエラーに。
※[mysql] と、[mysqld] のキャラクター設定方法(character-set)は同じ名前だと問題がおこるため注意が必要。(上記設定を参照)
ローカルデーモンのソケットにアクセスする際には、MySQLではファイルを通して通信する。もちろんネットワークでは通常に処理される。そこでmysqladminなどのクライアントコマンドを利用する際には、このファイルの設定が上手く言っていないと、ネットワーク越しではアクセスできても、ローカルからアクセスができないという状態になる。
-/etc/my.cnf の socket記述の位置 -環境変数 MYSQL_UNIX_PORTを参照 -デフォルト値 /tmp/mysql.sock でアクセス ''以上の順番でソケットの位置を確認される。''
※Socketファイルは[mysqld]と[client]を合わせておく。本来場所は自由に選択できるのだが、クライアントソフトによってはこのファイルを参照せず、変更できなくなっている場合もある。
MySQLの設定ファイル、my.cnf に下記のような行を追加することで sql_mode を指定できる。これは、MySQL 5.6以降から採用された。
[mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION (これは初期値)
※設定しても変更されないときは、ほかの my.cnf に上書きされているため、 # find / | grep my.cnf などで、my.cnf を総洗いしたほうが良い。
定義名 | 概要 |
STRICT_TRANS_TABLES | トランザクションストレージエンジンの厳密モードを有効。挿入・更新におけるパラメータ及び型を厳密にチェックし、以前ならワーニングのものをエラーとする |
NO_ENGINE_SUBSTITUTION | NO_ENGINE_SUBSTITUTION を無効にすると、CREATE TABLE については、目的のエンジンが利用できない場合にデフォルトエンジンが使用されて警告が発生します。ALTER TABLE では、警告が発生してテーブルは変更されません。有効だとエラーになります |
TRADITIONAL | 組み合わせモード。STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、および NO_ENGINE_SUBSTITUTIONをまとめたもの |
※これら組み合わせや設定の詳細は、このページを参照のこと。
mysqlコマンドによるモード変更
mysql > SET GLOBAL sql_mode = '';
mysqlコマンドによるモード確認
mysql > SELECT @@GLOBAL.sql_mode;
MySQLの標準ポートである、3306番に穴を開けておく。もしポート番号を変更している場合には、この数字も合わせて変更する。但し外部からアクセスがなくローカル内だけで完結する場合、通常 linux socket を利用するため、ポートは利用しないので設定の必要は無い。
# emacs /etc/sysconfig/iptables -A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
dataフォルダが空になっていることを確認し実行
# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
※DNS解決に関するエラーが出たらとりあえず、--force で通すのもあり。
MySQLのログインに関する基本ユーザー情報は mysql.user テーブルに記録されている。このuserテーブルは、ユーザー毎のアクセス可能ホスト別にレコードが作成される。ようするに同じuser名でも、アクセス可能ホストが違う場合は2つのレコードとして登録される。(これはhost項目に複数のアドレスを登録できないため)また以下の制約が存在する。
また上記のhost項目によるアクセス制限の他、データベースやテーブルのアクセスに関するアクセス制限も設定できる。
権限レベル | レベルの説明 | 各権限の保存先 |
グローバルレベル | 特定のサーバ上にある全データベースに適用されます。GRANT ALL ON *.*は、グローバル権限の付与のみを行います。 | mysql.user |
データベースレベル | 指定したデータベースの全テーブルに適用されます。GRANT ALL ON db.*は、データベース権限の付与のみを行います。 | mysql.db |
テーブルレベル | 指定したテーブルの全カラムに適用されます。GRANT ALL ON db.tableは、テーブル権限の付与のみを行います。 | mysql.tables_priv |
カラムレベル | 指定したテーブルの特定のカラムに適用されます。 | mysql.columns_priv |
<ユーザー関係のGRANTを変更したときは以下を必ず実行>
mysql > FLUSH PRIVILEGES; # service mysqld reload
※注意!、パスワードは値がハッシュされるため、update などで直接数値を指定しないこと。
<一時的にGRANTテーブルを無視する方法 [#t6a5f425]>
# vi /etc/my.cnf [mysqld] skip-grant-tables <- これを追加
mysqldデータベースを操作するには、mysqlコマンドを利用するが、その際の設定ファイルは、mysqld の起動時に使った設定ファイルを参照しようと以下の順番で設定ファイルが検索される。
特にこの設定ファイルで指定される socket ファイルのファイル位置は、サーバー側とクライアントの設定を同じにしないと接続できなくなる。
ユーザー作成には必ず GRANT構文を利用する。またユーザー名の変更はできない。変更する場合はユーザーを削除してから再度作成する。
<パスワード無しユーザー作成>
mysql> GRANT ALL PRIVILEGES ON *.* TO user;
<ホスト制限ありユーザー作成>
mysql> GRANT ALL PRIVILEGES ON *.* TO user@localhost;
<ホスト制限+パスワード指定あり>
mysql> GRANT ALL PRIVILEGES ON *.* TO user@localhost IDENTIFIED BY 'pass';
<DB制限+パスワード指定>
mysql> GRANT ALL PRIVILEGES ON mydb.* TO user@localhost IDENTIFIED BY "pass";
<管理者ユーザー(GRANT権利の付与)の作成>
mysql> GRANT ALL PRIVILEGES ON *.* TO user@localhost IDENTIFIED BY 'pass' WITH GRANT OPTION;
<セレクトのみ許可>
mysql> GRANT SELECT ON *.* TO user;
<ユーザーを削除:ホスト名も付けること>
mysql > DROP USER user@localhost;
<特定のホストからのユーザーを削除:非推奨>
mysql > DELETE FROM mysql.user WHERE user='user' AND host='localhost';
※DROPを使わない場合、mysql.db などユーザーにひも付けされている権限情報との整合性にトラブルが発生する。
<匿名ユーザー削除> 必ずやっておくこと。
mysql > delete from mysql.user where user=''; mysql > drop database test; <-- ついでに不要なテストDBも削除
<全ユーザーの削除:※注意!rootも削除される>
mysql > TRUNCATE TABLE mysql.user;
mysql > SET PASSWORD FOR user@localst=PASSWORD('pass');
※パスワードを '' にしたら、パスワード削除
表示したい項目にあわせて項目名を追加。
mysql> SELECT user,host,password,Grant_priv FROM mysql.user;
mysql> SELECT * FROM information_schema.processlist WHERE id = CONNECTION_ID();
ユーザの権限認証は、mysql.user -> mysql.db ->mysql.tables_priv -> mysql.columns_priv の順で認証される。上位で許可されている場合は、下位の認証は無視される。またユーザーはアクセス可能ホストによって複数レコードが、mysql.user に作られるように、アクセスホストによって、細かく権限を設定することができる。
<指定データベースへのアクセス付与>
mysql > GRANT ALL PRIVILEGES ON mysql.* TO user;
<特定の操作のみの権限付与:下記の場合は参照と更新のみ許諾>
mysql> GRANT SELECT,UPDATE ON *.* TO user;
<ユーザーを管理者に変更>
mysel > GRANT ALL PRIVILEGES ON *.* TO user@localhost WITH GRANT OPTION;
※ユーザー名とホスト名が合致していないと新レコードが追加されてしまうので注意。
<すべてのアクセス権限の破棄>
mysql > REVOKE ALL PRIVILEGES ON *.* FROM user@localhost;
<特定のデータベースのアクセス権限の破棄>
mysql > REVOKE ALL PRIVILEGES ON mysql.* FROM user@localhost;
<特定の操作権限の破棄>
mysql > REVOKE SELECT,UPDATE ON *.* TO user@localhost;
※REVOKE構文は基本的に DELETE構文と同じで存在しているレコードを削除する機能であり、判断を必要とする複雑な設定はできない。
(例:DBが、db1, db2, db3 と3つある場合に)
mysql > GRANT ALL PRIVILEGES ON *.* TO user; <- 全部DBアクセス許可 mysql > REVOKE ALL PRIVILEGES ON db1.* FROM user; <- db1だけ不許可(これはエラーになる)
これは、mysql.db に user が db1へのアクセスを許可するレコードが存在しないためである。全体への許可は、上位の mysql.user で管理許可されているため、下位の権限テーブルには許可レコードが明示されないためである。上記内容を実現したい場合には、
mysql > REVOKE ALL PRIVILEGES ON *.* FROM user; <- 全DBを不許可にして mysql > GRANT ALL PRIVILEGES ON db2.* TO user; mysql > GRANT ALL PRIVILEGES ON db3.* TO user; <- 個別のDBを許可する。
ユーザーを指定しない場合はログインしているユーザーの情報表示
mysql > SHOW GRANTS FOR user;
※表示されたときに末尾に WITH GRANT OPTIONが付いている場合は権限付与が行える管理者ユーザー。
<テーブルの項目表示>
mysql > DESCRIBE db_name.tables_name;
<コメント込みの項目全部表示>
mysql > SHOW FULL COLUMNS FROM db_name.tables_name;
パスワードリセットの為には、mysql をパスワードファイルを参照させずに起動させる。やり方としては、my.cnf の設定ファイルに以下を追加する。
[mysqld] skip-grant-tables
上記のパスワードを無視する設定をして起動後
# mysql -u root mysql <- root でmysqlDBに接続する。 mysql> UPDATE user SET Password=PASSWORD('newpasswd') WHERE User='root'; <- newpasswd に新しいパスワード。 mysql> FLUSH PRIVILEGES;
# mysql mysql mysql> update user set Password=null where Host='*' and User='root'; mysql> exit;
パスワード無視の設定を解除し、通常モードで起動後
# chkconfig mysql on # service mysqld start # mysqladmin -p status <- 起動チェック
※mysql.sockが何やら言って来たら、すでにmysqlが立ち上がっている場合が多い。一度、すべてのDaemonをKillしてから再度行うと上手くいく場合がある。
登録されているデータベースの一覧表示
mysql> show databases;
操作するデータベースの変更(上記で一覧表示されたものが<database-name>となる)
mysql> use <database-name>;
操作しているデータベースに登録されているテーブルの一覧表示
mysql> show tables;
テーブルの項目名などの詳細を表示する。(2つとも同じ結果になる)
mysql> describe <table-name>; mysql> show columns from <table-name>;
テーブルの中のレコードの中身をすべて表示(件数が多いとまともに見れない)
mysql> select * from <table-name>;
mysql> SHOW MASTER LOGS;
# mysqlbinlog --no-defaults mysql-bin.000001
※注意:このログでは、画像などのバイナリデータがあると、そのまま出力するので、コマンドライン上などで文字化けしたり、停止したりする。
# vi /etc/my.cnf [mysqld] ... expire_logs_days = 5 <- 5日以前のログ削除。スレーブ同期は無いのでゆとり設定 ...
設定の確認
mysql > SHOW GLOBAL VARIABLES like 'expire_logs_days';
mysql> SHOW MASTER LOGS; <- ファイル名を確認 mysql> PURGE MASTER LOGS TO 'mysqld-bin.000014' <- 14番以前を削除
次に、サーバーが無事起動できたら、apache+phpで起動する管理者ページを作成して管理をできるように設定する。phpMyAdminの公式ページはここ
phpと、mysqlをつなげるライブラリをインストール
# yum install php-mysql
# cd /usr/local/apache2/htdocs/ <- できればSSLと認証の掛かっている場所に。 # wget http://sourceforge.net/projects/phpmyadmin/files/phpMyAdmin/4.2.10.1/phpMyAdmin-4.2.10.1-all-languages.tar.gz # tar zxvf phpMyAdmin-4.2.10.1-all-languages.tar.gz # mv phpMyAdmin-4.2.10.1 phpMyAdmin # cd phpMyAdmin # mkdir config # chown apache:apache config # mkdir /var/lib/php # mkdir /var/lib/php/session # chmod -R 777 /var/lib/php # cp config.sample.inc.php config.inc.php
インストールしたフォルダをapacheを通してアクセスし最終的な設定を完了させる。
http://localhost/phpMyAdmin/setup/
項目名 | 機能 | 設定例 |
auth_type | 認証方式の指定 | cookie, http |
host | ホストの指定 | localhost |
connect_type | MySQLへの接続方法 | tcp, socket |
compress | 通信に圧縮を利用するか | true, false |
extension | 拡張コマンドの種類 | mysqli, mysql |
port | tcp接続の場合のポート番号 | 3306 |
# mysql -u root < examples/create_tables.sql # mysql -u root mysql> grant all on phpmyadmin.* to pma@localhost identified by "password";
※この場合、config.inc.conf のパスワード名も、このパスワードに合わせておくこと。
# config.inc.php をコピー
PHPのコンパイル時に、MySQLオプションの指定が無い場合、起動しない。PHPのコンパイル時のオプションは下記の通り。
# ./configure --with-mysql=/usr/local/mysql
※ここでは他のオプションは割愛しているので、自分の環境によって他のオプションは付けられたし。
phpMyAdminはphpを利用してMySQLにアクセスするが、phpinfo(); 関数に表示されている、php.ini ファイルには、mySQLに関する設定ファイルが存在する、このファイルの
mysql.default_socket = mysqli.default_socket =
という行に、/etc/my.cnf で設定した socket ファイルと同じファイルを指定しないと正常に動作しない。
apacheのドキュメントフォルダに設置したphpMyAdminのページをブラウザを使って表示させると起動確認ができる。
例: http://localhost/phpMyAdmin/
テーブルの項目に、外部キーが設定されていると操作ができなくなるので、SQLコマンドで外部キーを削除する。まずは外部キーの名前を取得するため、以下のコマンドを実行する。
mysql> SHOW CREATE TABLE job_type; CREATE TABLE `job_type` ...... ?CONSTRAINT `job_type_ibfk_1` FOREIGN KEY (`job_type_id`) REFERENCES `job_type` (`job_type_id`) .......
ここに出てきた、job_type_ibfk_1 というのが外部キーになる。このキーを削除するため、以下のコマンドを実行。
mysql> ALTER TABLE job_type DROP FOREIGN KEY `job_type_ibfk_1`;
前のDBが残っている場合、mysql_upgradeを行えと指示が出るが、新規でのインストールであれば、データフォルダのデータは消してから起動した方が早い。
インストールしたての場合は、mysqlのrootユーザーにパスワードが設定されていない。そのため、以下の手順でパスワードを設定する。
# service mysqld stop # mysqld --skip-grant-tables & #(1回リターンキーを押すと再度プロンプトがでる) # mysql -u root mysql> use mysql; mysql> select * from user; mysql> truncate table user; mysql> flush privileges; mysql> grant all privileges on *.* to root@localhost identified by 'パスワード' with grant option; mysql> flush privileges; mysql> select host, user from user; mysql> quit;
以後、変更削除できるようになる。
SELinux を切ってみる。
auto_increment指定されている項目にデータ0を設定することはできない、auto_increment指定の場合、0は特別な数字となっており、仮に0を指定すると、一番大きい番号の後ろに新レコードとして追加される。
utf8_unicode_ci タイプにおける識別とは、「あ」に対する「ぁ」はもちろんの事、「は」に対する「ば」「ぱ」などの濁点、半濁点、「ハ」や「ハ」などのカタカナや半角カタカナも同一文字として検索時に処理される。
最初の文字列の意味 | |
utf8 | UTF-8コードとして認識する |
2番目の文字列の意味 | |
bin | バイナリーとして比較する。そのため、「A」と「a」は識別される。 |
general | 全角と半角の英数字は同じとみなさない。これはバイトコード比較のため複数バイトを持つ同一文字を認識できない。例:「A」と「a」は識別されない(_ciの場合、同一文字とみなす)が、「A」「A」は識別される。 |
unicode | 全角と半角の英数字も同じとする(_ciの場合、文字コード比較)。例:「A」「a」「A」はすべて同じ文字として処理される。 |
最後の文字列(2番目がBINの場合は存在しない) | |
ci | Case Insensitive 大文字小文字を区別しない |
cs | Case Sensitive 大文字小文字を区別する。 |
注)ドイツ語などの2文字が同じものを示すような場合において、utf8_unicode_ciでは"ß" = "ss"に対して、utf8_general_ciでは"ß" = "s"と、間違った結果になる。
型 | バイト | 最小値 | 最大値 | 備考 |
TINYINT | 1 | -128 | 127 | bool型はこれを使う |
SMALLINT | 2 | -32768 | 32767 | C言語のShort型、16bitCPU時のint型 |
MEDIUMINT | 3 | -8388608 | 8388607 | |
INT | 4 | -2147483648 | 2147483647 | いわゆる、C言語のlong や 32bitCPU時のint型 |
BIGINT | 8 | -9223372036854775808 | 9223372036854775807 | C言語のlonglong型 |
※int(11)など、括弧で数字が指定されている場合は、上記数字型の場合に限り「表示上」の桁数を指定する機能になっている。指定がなければ、その型の最大桁+1(マイナス記号用)が指定される。
型の装飾できる内容
装飾式 | 内容 |
auto_increment | レコードが作成されたときに、自動的に連番をつける項目 |
NOT NULL | NULLを指定できない項目 |
default NULL | 初期値がNULLの項目 |
unsigned | マイナスの範囲を取らない項目 |
zerofill | 括弧で指定された桁分を0で埋めるようにする項目 |
型 | 概要 |
date | 日付のみ |
datetime | 日付と時間の両方 |
timestamp | この項目を設定すると自動的にレコード作成時間が記録される。同時にデフォルト値に、CURRENT_TIMESTAMPと明記される |
time | 時間のみ |
year | 西暦の年のみ |
# mysqladmin extended-status
mysql > select current_user();
メモリ不足の為、メモリを増やすか、スワップ領域を増やす。(下記では4GB増やしている)
# dd if=/dev/zero of=/swapfile bs=1M count=4096 # mkswap /swapfile # swapon /swapfile # swapon -s <- 確認用
各プロセスのメモリ利用量
# ps -xl --sort -vsize | head
または、設定を変更
# vi /etc/my.cnf [mysqld] table_definition_cache=400 <- 1400 から 400 に。 performance_schema=off <- 基本OFFだが念のため。
# vi /etc/my.cnf [mysqld] ... ssl-ca=/etc/mysql-ssl/server.ca ssl-cert=/etc/mysql-ssl/server.crt ssl-key=/etc/mysql-ssl/server.key [client] ... ssl ssl-cipher=DHE-RSA-AES256-SHA
サーバーのkey ファイルが、rsa 形式になっていない。
# openssl rsa -in server.key -out server-rsa.key
再起動後、確認
mysql > SHOW VARIABLES LIKE '%ssl%';
utfmb4 とは、MySQL専用の文字コードで、4バイト utf にも対応した文字セット(そもそもutf8では、1-4バイトのコードなので、4バイトに対応しているのが標準だが、MySQLでは分かれている)。絵文字などが入力されると、utf8 ではエラーになって処理が止まる。そのため、互換性を考えると、MySQLで utf8 を使う場合、すべて utf8mb4 であると文字コードトラブルがない。
リプリケーションは、反復や複製の意味を持つ単語であり、MySQLでもリプリケーションサーバーとは、マスターDBを複製しスレーブDBとして稼働することを意味する。またMySQLのリプリケーション機能には下記の特徴がある。
<メリット>
<デメリット>
<結論>
[mysqld] log-bin server-id=1
上記の設定が漏れていないか確認
※但し、server-idは、各サーバー毎にユニークにする必要がある。
mysql > GRANT REPLICATION SLAVE ON *.* TO rep@'192.168.100.%' IDENTIFIED BY 'pass';
※データを丸々コピーする強力なアカウントなので極力IP制限を掛けたほうが良い。
<データベースのロック>
mysql > FLUSH TABLES WITH READ LOCK; <- コピーの為、テーブルロック。 mysql > SHOW MASTER STATUS; <- バイナリログの状態を確認(ロック後) mysql > SHOW BINLOG EVENTS; <- バイナリログの中身確認
<マスター側のデータ領域をスレーブ側にコピー>
# cd /usr/local/mysql # tar zcvf data.tar.gz data
<圧縮ファイル(スレーブDB用データフィイル)が完成後、マスターDBのロックを解除>
mysql > UNLOCK TABLES; <- テーブルのアンロック(解除)
mysql > show variables like '%gtid%';
基本的にスレーブDB側は複数のDBを起動できる。
上記のマスター設定の中でコピーしたデータファイルを、展開する。
# cd /usr/local/mysql # rm -rf data <- 注意!元のデータが全部消えます。 # tar zxvf data.tar.gz # rm -f data/auto.cnf <- 中の server-uuid がマスターと重複してしまうので削除(自動で再作成される)。
mysql > CHANGE MASTER TO MASTER_HOST='192.168.111.107', <- Masterのホスト名/IPアドレス MASTER_USER='rep', <- Master接続に使用するユーザー名 MASTER_PASSWORD='password', <- Master接続に使用するユーザーのパスワード MASTER_LOG_FILE='www-bin.000001', <- Masterで、SHOW MASTER STATUS; した時のファイル名 MASTER_LOG_POS=120; <- Masterで、SHOW MASTER STATUS; した時のPosition番号
※コピペ用:CHANGE MASTER TO MASTER_HOST='www.mydomain.com', MASTER_USER='rep', MASTER_PASSWORD='pass', MASTER_LOG_FILE='www-bin.000001', MASTER_LOG_POS=120;
mysql > START SLAVE; <- スレーブの起動 mysql > SHOW SLAVE STATUS; <- 現在のスレーブ状況の確認
MySQL 5.6.9 移行に使えるようになった。トランザクションを絶対値にすることで、トランザクションの管理が楽になる機能。作った本人のブログページ
GTIDを導入するには設定ファイルに下記の2つを追加する。
# vi /etc/my.cnf [mysqld] gtid-mode=ON enforce-gtid-consistency
mysql > SELECT @@GLOBAL.GTID_DONE;
# mysqldump -u root -phogehoge --databases mydatabase > mysql_`date +%Y%m%d-%H%M%S`.sql
option | 概要 | 例 |
-u | ログインユーザー名を指定 | -u root |
-p | パスワード指定 | -phogehoge |
-h | ホスト名指定 | -h localhost |
--databases | 対象データベース名の指定 | --databases mydatabase |
--default-character-set | キャラクターセットの指定 | --default-character-set=utf8 |
※注)-pの後にパスワード入力する場合には、スペースを入れない。
# mysql -u root -phogehoge < mysql.sql
※Database名を指定しても、ダンプファイルの先頭にダンプ時のDB名が入っていると、データベース名をコマンドラインから指定しても意味がなくなる。
mysql> grant all privileges on *.* to username@"new.server.com"; mysql> FLUSH PRIVILEGES;
OSのLVMやZFSのスナップショット機能を使ったり、InnoDB Hot Backupを使ったバックアップ方法もあります。
外部の mysql に接続
# mysql -u user -h www.mydomain.com -ppassword <- -pとパスワードの間にスペースは入れない。
データベースの表示
mysql> SHOW DATABASES; mysql> SHOW DATABASES LIKE 'ca%'; <- 頭がcaの一覧表示
データベースの切替
mysql> use データベース名
データベースの追加
mysql > CREATE DATABASE データベース名 CHARACTER SET utf8;
テーブルの一覧
mysql> SHOW TABLES; mysql> SHOW TABLE STATUS; <- 詳細データ付きの一覧データ
# mysql --local-infile -u root -p <- ローカルファイルアクセス許可 mysql > LOAD DATA LOCAL INFILE "./test.csv" REPLACE INTO TABLE 'tablename' FIELDS TERMINATED BY ',' ENCLOSED BY '"' (name, age, @dummy, address); <- 項目がcsvのどのような順番にならんでいるか。
※ファイルの改行コードは、Linux用(LF)を利用すること。文字はUTF-8で。
MySQL 公式ページ
日本MySQLユーザ会
MySQL to ODBC
i386用ダウンロードページ
MySQLの小技
mysql> SELECT item_id, SUM(price) FROM `payment` GROUP BY item_id ORDER BY sum(price) DESC;
GROUP BY と対になる、このSUMの部分にとり得るコマンド | |
SUM | 合計値 |
MAX | 最大値 |
MIN | 最小値 |
AVG | 平均値 |
COUNT | データの個数 |
※GROUP BY とORDER BYを1行にいれる場合は、ORDER BY を後ろにしないとエラーになる。
詳細説明ページ
ALTER TABLE テーブル名 ADD UNIQUE (カラム名,カラム名);
ALTER TABLE テーブル名 ADD PRIMARY KEY (カラム名,カラム名);
ALTER TABLE テーブル名 ADD FOREIGN KEY (カラム名) REFERENCES 参照テーブル名 (参照カラム名);
照合順序 | 概要 |
utf8_general_ci | アルファベットの大文字小文字は区別しない。全角・半角は区別 |
utf8_unicode_ci | 大文字・小文字・全角・全角カタカナ・半角・半角カタカナを区別しない。例:「AAA」「aaa」「AAA」「aaa」は同じ。「あああ」「アアア」「アアア」も同じ。 |
utf8_general_ci の照合順序のテーブルで、utf8_unicode_ci の全角半角の区別無し検索をさせたい時、select句に、collate utf8_unicode_ci を追加する。但し当然遅くなる。