スライド 1

Size: px

Start display at page:

Download "スライド 1"

きみつぐますはら
5 years ago
Views:

1 (MATSUNOBU Yoshinori

2 MySQL mysqldump FLUSH TABLES WITH READ LOCK

3 SQL (InnoDB Linux,Solaris,HP-UX,AIX,Windows C/C++( SQL92 SQL99 Core InnoDB MyISAM ( B-Tree ( (InnoDB ( (InnoDB (4.1 ( SSL SSL VPN(SSH

4 mysqld MyISAM MyISAM InnoDB MyISAM InnoDB InnoDB InnoDB

5 [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

6 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

7 (

8 t1 t2 t3 t3 t1 t2 t2 t3 t1 ( ( ( ( MySQL t1

9 MySQL mysqldump FLUSH TABLES WITH READ LOCK

10 MySQL ( SQL mysqlbinlog SQL use db1; SET TIMESTAMP= ; CREATE TABLE tbl1 (col1 INTEGER, col2 DATETIME ENGINE=InnoDB; SET TIMESTAMP= ; INSERT INTO tbl1 VALUES(100,SYSDATE(; SET TIMESTAMP= ; DROP TABLE tbl1;

11 [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 log-bin ( innodb_safe_binlog

13 FLUSH LOGS; (SQL mysqladmin --flush-logs (OS mysqld (max_binlog_size 1GB 1GB

14 MySQL mysqldump FLUSH TABLES WITH READ LOCK

15 mysqld OS mysqld $mysqladmin shutdown --user=root --password=( --socket=( #cp -rp ( ( mysqld $cd <MySQL > $./bin/mysqld_safe --defaults-file=(

16 t1 t2 t3 t3 t1 t2 t2 t3 t1 (mysql-host (mysql-host (mysql-host

17 mysqld #cp -rp (

18 mysqld ( mysqlbinlog SQL #cp p mysql-host ( SQL $mysqlbinlog --disable-log-bin mysql-host mysql-host mysql-host > ( SQL mysqld $cd (MySQL $./bin/mysqld_safe --defaults-file=( --skip-networking SQL $mysql --user=root --password=( --socket=( --default-character-set=sjis < ( SQL

19 mysqld mysqld $mysqladmin shutdown --user=root --password=( --socket=( mysqld $cd (MySQL $./bin/mysqld_safe --defaults-file=(

20 MySQL mysqldump FLUSH TABLES WITH READ LOCK

21 mysqldump MySQL SELECT SQL InnoDB $mysqldump --user=root --password=( --socket=( --single-transaction --master-data --flush-logs --default-character-set=sjis --all-databases > (

22 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

23 t1 t2 t3 t3 t1 t2 t2 t3 t1 (mysql-host (mysql-host (mysql-host mysqldump (

24 mysqld SQL mysql #cp p mysql-host ( 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 mysql-host-relay-bin.index relay-log.info

25 mysqld mysqlbinlog SQL SQL $mysqlbinlog --disable-log-bin mysql-host mysql-host mysql-host > ( SQL SQL $mysql --user=root --password=( --socket=( --default-character-set=sjis < ( SQL

26 MySQL mysqldump FLUSH TABLES WITH READ LOCK

27 FLUSH TABLES WITH READ LOCK (= SQL SQL COMMIT MyISAM

28 mysql> FLUSH TABLES WITH READ LOCK ; #cp -rp ( ( RAID / mysql> FLUSH LOGS ; LVM Win2003 VSS mysql> UNLOCK TABLES ;

29 t1 t2 t3 t3 t1 t2 t2 t3 t1 (mysql-host (mysql-host (mysql-host FLUSH TABLES WITH READ LOCK

30 mysqld InnoDB InnoDB mysqld #cp -rp (

31 InnoDB mysqld InnoDB InnoDB mysqld mysqld FLUSH TABLES WITH READ LOCK InnoDB col tbl1(innodb ( ( 1.InnoDB 2.InnoDB ( ( ( InnoDB InnoDB

32 ( mysqld ( mysqlbinlog SQL #cp p mysql-host ( SQL $mysqlbinlog --disable-log-bin mysql-host mysql-host mysql-host > ( SQL mysqld ( $cd (MySQL $./bin/mysqld_safe --defaults-file=( --skip-networking SQL $mysql --user=root --password=( --socket=( --default-character-set=sjis < ( SQL

33 MySQL mysqldump FLUSH TABLES WITH READ LOCK

34 (InnoDB InnoDB frm MYD MYI

35 (InnoDB InnoDB frm MYD MYI

36 mysqlbinlog --stop-datetime DATETIME/TIMESTAMP SQL 3/26 13:30 SQL $ mysqlbinlog --stop-datetime= :30:00 (

37 $ 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 tbl1(innodb tbl1(innodb tbl2(myisam 2 6 ( tbl1(innodb InnoDB MyISAM 7 SELECT 2000 tbl2(myisam tbl2(myisam

38 ( SQL col1 SQL (SQL 100 COMMIT 200 (innodb_lock_wait_timeout tbl1(innodb 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; (

39 InnoDB MyISAM 1 START TRANSACTION; MyISAM 2 InnoDB ( 3 MyISAM COMMIT; 3 tbl1(innodb tbl2(myisam tbl2(myisam tbl1(innodb tbl2(myisam tbl1(innodb 4 tbl1(innodb : tbl2(myisam :

40 ( 4.1 InnoDB MyISAM

41 mysqldump FLUSH TABLES WITH READ LOCK +OS InnoDB Hot Backup ( ( InnoDB InnoDB frm (

42 DB Magazine MySQL ( MySQL The mysqldump Database Backup Program InnoDB Hot Backup Online Manual MySQL MySQL (

Microsoft PowerPoint - MySQL-backup.ppt

Microsoft PowerPoint - MySQL-backup.ppt MySQL バックアップリカバリ概要オープンソースコンピテンシコンピテンシセンター日本ヒューレットパッカードヒューレットパッカード株式会社 2006 年 12 月 6 日 2006 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice