2005 3 26 (MATSUNOBU Yoshinori
MySQL mysqldump FLUSH TABLES WITH READ LOCK
SQL (InnoDB Linux,Solaris,HP-UX,AIX,Windows C/C++( SQL92 SQL99 Core 4.1 5.0 InnoDB MyISAM ( B-Tree ( 5.0 4 (InnoDB ( (InnoDB (4.1 ( SSL SSL VPN(SSH
mysqld MyISAM MyISAM InnoDB MyISAM InnoDB InnoDB InnoDB
[mysqld] basedir=/usr/mysql4110 datadir=/data/mysql4110 InnoDB innodb_data_home_dir=/data/mysql4110/idata InnoDB innodb_data_file_path= ibdata1:1000m:autoextend MyISAM innodb_log_group_home_dir=/data/mysql4110/ilog MyISAM socket=/data/mysql4110/mysql.sock log-bin=/data/mysql4110/blog/mysql-host (my.cnf PID
A C InnoDB D F MyISAM InnoDB InnoDB InnoDB A.frm B.frm Ib_logfile0 Ib_logfile1 A ibdata1 InnoDB B C ibdata2 InnoDB C.frm MyISAM D.MYD D.MYI D.frm E.MYD E.MYI E.frm F.MYD F.MYI F.frm
(
t1 t2 t3 t3 t1 t2 t2 t3 t1 ( ( ( ( MySQL t1
MySQL mysqldump FLUSH TABLES WITH READ LOCK
MySQL ( SQL mysqlbinlog SQL use db1; SET TIMESTAMP=1108825248; CREATE TABLE tbl1 (col1 INTEGER, col2 DATETIME ENGINE=InnoDB; SET TIMESTAMP=1108825248; INSERT INTO tbl1 VALUES(100,SYSDATE(; SET TIMESTAMP=1108825250; DROP TABLE tbl1;
[mysqld] basedir=/usr/mysql4110 datadir=/data/mysql4110 innodb_data_home_dir=/data/mysql4110/idata innodb_data_file_path= ibdata1:100m:autoextend innodb_log_group_home_dir=/data/mysql4110/ilog pid-file=/data/mysql4110/mysql-host.pid socket=/data/mysql4110/mysql.sock log-bin=/data/mysql4110/blog/mysql-host innodb_safe_binlog 6 (4.0 3 mysql-host.000001 log-bin ( innodb_safe_binlog
.000001.000001.000002
FLUSH LOGS; (SQL mysqladmin --flush-logs (OS mysqld (max_binlog_size 1GB 1GB
MySQL mysqldump FLUSH TABLES WITH READ LOCK
mysqld OS mysqld $mysqladmin shutdown --user=root --password=( --socket=( #cp -rp ( ( mysqld $cd <MySQL > $./bin/mysqld_safe --defaults-file=(
t1 t2 t3 t3 t1 t2 t2 t3 t1 (mysql-host.000013 (mysql-host.000011 (mysql-host.000012
mysqld #cp -rp (
mysqld ( mysqlbinlog SQL #cp p mysql-host.000013 ( SQL $mysqlbinlog --disable-log-bin mysql-host.000011 mysql-host.000012 mysql-host.000013 > ( SQL mysqld $cd (MySQL $./bin/mysqld_safe --defaults-file=( --skip-networking SQL $mysql --user=root --password=( --socket=( --default-character-set=sjis < ( SQL
mysqld mysqld $mysqladmin shutdown --user=root --password=( --socket=( mysqld $cd (MySQL $./bin/mysqld_safe --defaults-file=(
MySQL mysqldump FLUSH TABLES WITH READ LOCK
mysqldump MySQL SELECT SQL 4.1.11 InnoDB $mysqldump --user=root --password=( --socket=( --single-transaction --master-data --flush-logs --default-character-set=sjis --all-databases > (
1 A A mysqldump B B mysqldump t3 t5 t1 t2 t4 t6 t7 t8 2 B A MyISAM SELECT A t2 B t6 InnoDB START TRANSACTION WITH CONSISTENT SNAPSHOT mysqldump t1 A B InnoDB mysqldump --lock-all-tables
t1 t2 t3 t3 t1 t2 t2 t3 t1 (mysql-host.000013 (mysql-host.000011 (mysql-host.000012 mysqldump (
mysqld SQL mysql #cp p mysql-host.000013 ( mysqld $cd (MySQL $./bin/mysqld_safe --defaults-file=( --skip-networking --skip-grant-tables SQL $ mysql --user=root --password=( --socket=( --default-character-set=sjis < ( $ cd ( $ rm master.info mysql-host-relay-bin.000001 mysql-host-relay-bin.index relay-log.info
mysqld mysqlbinlog SQL SQL $mysqlbinlog --disable-log-bin mysql-host.000011 mysql-host.000012 mysql-host.000013 > ( SQL SQL $mysql --user=root --password=( --socket=( --default-character-set=sjis < ( SQL
MySQL mysqldump FLUSH TABLES WITH READ LOCK
FLUSH TABLES WITH READ LOCK (= SQL SQL COMMIT MyISAM 4.1.11
mysql> FLUSH TABLES WITH READ LOCK ; #cp -rp ( ( RAID / mysql> FLUSH LOGS ; LVM Win2003 VSS mysql> UNLOCK TABLES ;
t1 t2 t3 t3 t1 t2 t2 t3 t1 (mysql-host.000013 (mysql-host.000011 (mysql-host.000012 FLUSH TABLES WITH READ LOCK
mysqld InnoDB InnoDB mysqld #cp -rp (
InnoDB mysqld InnoDB InnoDB mysqld mysqld FLUSH TABLES WITH READ LOCK InnoDB col1 100 200 tbl1(innodb 100 1000( 200 300( 1.InnoDB 2.InnoDB 100 1000 200 300( ( 100 1000 300 200 ( InnoDB InnoDB
( mysqld ( mysqlbinlog SQL #cp p mysql-host.000013 ( SQL $mysqlbinlog --disable-log-bin mysql-host.000011 mysql-host.000012 mysql-host.000013 > ( SQL mysqld ( $cd (MySQL $./bin/mysqld_safe --defaults-file=( --skip-networking SQL $mysql --user=root --password=( --socket=( --default-character-set=sjis < ( SQL
MySQL mysqldump FLUSH TABLES WITH READ LOCK
(InnoDB InnoDB frm MYD MYI
(InnoDB InnoDB frm MYD MYI
mysqlbinlog --stop-datetime DATETIME/TIMESTAMP SQL 3/26 13:30 SQL $ mysqlbinlog --stop-datetime= 2005-03-26 13:30:00 (
$ mysqldump --single-transaciton --master-data --flush-logs --all-databases 1 mysqldump 2 FLUSH TABLES WITH READ LOCK; 3 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 4 START TRANSACTION WITH CONSISTENT SNAPSHOT; 5 FLUSH LOGS; 6 UNLOCK TABLES; 7 SELECT 8 mysqldump 1 2 6 7 8 100 200 tbl1(innodb 300 200 tbl1(innodb 1000 2000 tbl2(myisam 2 6 ( 100 200 tbl1(innodb InnoDB 2 6 5 3000 MyISAM 7 SELECT 2000 tbl2(myisam 3000 2000 tbl2(myisam
1 2 3 4 5 6.1 6.2 6.3 7 ( SQL col1 SQL (SQL 100 COMMIT 200 (innodb_lock_wait_timeout tbl1(innodb 3 4.1.11 1 START TRANSACTION; UPDATE tbl1 SET col1=300; COMMIT; 2 START TRANSACTION; UPDATE ( 3 UPDATE tbl1 SET col1=400; ( COMMIT; ( FLUSH TABLES WITH READ LOCK; (1 2 FLUSH TABLES WITH READ LOCK 4 CREATE TABLE tbl2 (col1 INTEGER; (
InnoDB MyISAM 1 START TRANSACTION; MyISAM 2 InnoDB ( 3 MyISAM 4-- 1 100 200 1000 2000 5 COMMIT; 3 tbl1(innodb tbl2(myisam 3000 2000 tbl2(myisam 100 200 tbl1(innodb 3000 2000 tbl2(myisam 4 5 300 200 tbl1(innodb 4 tbl1(innodb : 100 300 tbl2(myisam : 1000 3000
( 4.1 InnoDB MyISAM
mysqldump FLUSH TABLES WITH READ LOCK +OS InnoDB Hot Backup ( ( InnoDB 4.1.11 4.1.11 InnoDB frm (
DB Magazine MySQL 2005 6 7 ( MySQL The mysqldump Database Backup Program http://dev.mysql.com/doc/mysql/en/mysqldump.html InnoDB Hot Backup Online Manual http://www.innodb.com/manual.php MySQL MySQL ( http://www.mysql.gr.jp/mysqlml/mysql/msg/10758