MySQL接続
# mysql -u root -p -h 127.0.0.1
更新系ロック実行
更新系の処理をロックする(参照系はロックされない)。
mysql> FLUSH TABLES WITH READ LOCK;ログからテーブルスペースへ書き込み中の可能性もあるので、「Log sequence number」と「Log flushed up to」の値が同じであることを確認。
mysql> SHOW INNODB STATUS\G ～中略～ --- LOG --- Log sequence number 642420560012 Log flushed up to 642420560012 Last checkpoint at 642420560012 0 pending log writes, 0 pending chkp writes 86747663 log i/o's done, 0.08 log i/o's/second ～中略～「SHOW STATUS」を実行して、「Key_blocks_not_flushed」が「0」であることを確認。
mysql> SHOW STATUS LIKE '%Key_blocks_not_flushed%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Key_blocks_not_flushed | 0 | +------------------------+-------+ 1 row in set (0.01 sec)
ダンプ
全てのデータベースをダンプする。
# mysqldump -u root -p -h 127.0.0.1 --all-database --master-data=2 --default-character-set=binary --single-transaction --flush-logs --quick | gzip --best -c > ./all_database.sql.gz
「--all-database」 ⇒ 全てのデータベースのからデータをダンプするように設定。 「--master-data=2」 ⇒ スレーブのセットアップ用のバイナリーログのファイル名と開始位置を出力するように設定。 「--default-character-set=binary」 ⇒ UTF-8のデータベースからのダンプ文字化け回避の為に設定。 「--single-transaction」 ⇒ テーブルへの参照＆更新をブロックすることなく、mysqldumpコマンド開始時点のスナップショットを取るように設定。(MyISAMなどでは利用できない) 「--flush-logs」 ⇒ バイナリーログのローテーションを行うように設定。 「--quick」 ⇒ クエリをバッファせず、stdouに直接ダンプするように設定。
更新系ロック解除
mysql> UNLOCK TABLES;
my.cnf変更
my.cnfの設定を変更する。
[mysqld] innodb_data_file_path=ibdata1:10M:autoextend innodb_autoextend_increment=64 innodb_file_per_table
「innodb_data_file_path = ibdata1:10M:autoextend」 ⇒ デフォルト値を設定。 「innodb_autoextend_increment=64」 ⇒ 拡張時に64MBの領域を確保するように設定。 「innodb_file_per_table」 ⇒ テーブルスペースをテーブル単位で作成するように設定。
MySQL接続
# mysql -u root -p -h 127.0.0.1
アクセス状況確認
他からのアクセスがないことを確認する。
mysql> SHOW PROCESSLIST;
スレーブ停止
mysql> SLAVE STOP;
MySQL停止
# /etc/init.d/mysqld stop
既存データ退避
# mkdir /home/mysql/tmp/data/ # mv /home/mysql/data/ib_logfile0 /home/mysql/tmp/data/ib_logfile0 # mv /home/mysql/data/ib_logfile1 /home/mysql/tmp/data/ib_logfile1 # mv /home/mysql/data/ibdata1 /home/mysql/tmp/data/ibdata1
MySQL起動
# /etc/init.d/mysqld start
リストア実行
# mysql -u root -p -h 127.0.0.1 --default-character-set=utf8 < ./all_database.sql
スレーブ設定
ダンプファイルからマスタの情報を取得。
# head -100 ./all_database.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000454', MASTER_LOG_POS=106;上記のコマンドで出力されたLOG_FILEとLOG_POSの値を、以下の「CHANGE MASTER TO」の「MASTER_LOG_FILE」、「MASTER_LOG_POS」の各値に設定する。
mysql> CHANGE MASTER TO
-> MASTER_HOST = 'masterサーバーのIPアドレス',
-> MASTER_PORT = 3306,
-> MASTER_USER = 'slaveユーザーのアカウント名',
-> MASTER_PASSWORD = 'slaveユーザーのパスワード',
-> MASTER_LOG_FILE = 'mysql-bin.000454',
-> MASTER_LOG_POS = 106,
-> MASTER_CONNECT_RETRY = 10;
スレーブ開始
mysql> SLAVE START;スレーブ状況を確認。
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: masterサーバーのIPアドレス
Master_User: slaveユーザーのアカウント名
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000454
Read_Master_Log_Pos: 202859032
Relay_Log_File: mysql-relay-bin.000006
Relay_Log_Pos: 160873379
Relay_Master_Log_File: mysql-bin.000454
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: test
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: 202859032
Relay_Log_Space: 160873534
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)
ERROR:
No query specified
※また、本ページを作成するのに以下のサイトを参考にしました。
mysqlのibdataが原因でディスクフルになったときに復旧方法
MySQLのibdataファイルを複数表領域に変更する
MySQLの「innodb_buffer_pool_size」と「innodb_log_file_size」の設定
MySQLのInnoDBデータファイル「ibdata1」の最適化
ibdata1 のサイズを減らす手順
拡張され続ける InnoDB のデータファイルのサイズを小さくする方法
MySQLのバックアップと"FLUSH TABLES WITH READ LOCK"についてAdd Star
現場指向のレプリケーション詳説
MySQL innodbでのibdata1肥大問題
InnoDBのログとテーブルスペースの関係
InnoDBデータファイルのサイズを変更するには