untitled

Size: px

Start display at page:

Download "untitled"

やすもりそめや
5 years ago
Views:

2 2

3 3

4 4

5 5

6 6

7 READ PK POINT InnoDB MyISAM InnoDB MyISAM 7

8 READ_KEY_POINT_LIMIT InnoDB MyISAM InnoDB MyISAM 8

9 READ PK RANGE InnoDB MyISAM InnoDB MyISAM 9

10 READ PK RANGE INDEX InnoDB MyISAM InnoDB MyISAM 10

11 READ_KEY_RANGE_LIMIT InnoDB MyISAM InnoDB MyISAM 11

12 InnoDB MyISAM InnoDB MyISAM READ FTS 12

13 13

http://conferences.oreillynet.com/presentations/mysql07/innodb_kinoshita.

14 USMySQLPercona 14

15 (B-tree) I/O MySQL5.1 MySQL5.0 15

16 MySQL Enterprise3 16

17 MySQL LAMPOSLinuxWebApacheDBMSMySQLPerlPHPPythonWeb OSSPC1000 mixi miximysqlweb 31000mixi MySQL 17

http://bloghackers.net/~naoya/ppt/081108huge_data.

18 CRM MySQL SugarCRM 90% 18

19 19

20 20 MySQL Oracle MyISAM InnoDB Redo Ctl SP my.cnf

21 MySQL Oracle 21

22 MySQL MySQL MySQL ClusterMySQL Cluseter InnoDB MyISAM OracleDB LINK MyISAM 22

23 MyISAMMyISAM InnoDB Index MyISAMInnoDB 23

24 24

25 25

26 26

27 27

28 28

29 29

30 MySQL 30

31 ➁ ➁ LVM Snapshot LVM Snapshot 31

32 ~]$mysql -u root prootpass Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 3 Server version: enterprise-gpl-log MySQL Enterprise Server (GPL) Type 'help;' or ' h' for help. Type ' c' to clear the buffer. mysql>flush tables with read lock; flush tables with read lockmysql [mysql@server ~]$ sudo /usr/sbin/lvcreate --snapshot -L4G -n snaplv02 /dev/volgroup00/logvol02 Logical volume "snaplv02" created /dev/volgroup00/logvol02 32

33 mysql>flush LOGS; mysql>show MASTER STATUS; FLUSH LOGS3-5 SHOW MASTER STATUS mysql>unlock tables; Unlock tables ~]$ 33

34 ~]$ sudo /bin/mount -o ro /dev/volgroup00/snaplv02 /snaplv02 ~]$ cd /snaplv02/mysql/;tar cvf /backup/mysql/hotbk tar./* /snaplv2/mysql/ /backup/mysql ~]$ sudo /bin/umount /dev/volgroup00/snaplv02 ~]$ sudo /usr/sbin/lvremove -f /dev/volgroup00/snaplv02 Logical volume "snaplv02" successfully removed mysql>purge MASTER LOGS TO mysql-bin

35 ~]$ sudo /sbin/service mysql stop Shutting down MySQL.. [ OK ] ➁ [mysql@server ~]$ cp p /data/mysql/blog/mysql-bin.* /backup/mysql/blog/ [mysql@server ~]$ tar -C /data/mysql/ -xvf Hotbk tar [mysql@server ~]$ vi /data/mysql/cnf/my.cnf # Replication Master Server (default) # binary logging is required for replication #log-bin=/data/mysql/blog/mysql-bin [mysql@server ~]$ sudo /sbin/service mysql start --skip-networking Password: Starting MySQL [ OK ] 35

36 ~]$ mysqlbinlog --disable-log-bin mysql-bin mysql-bin > recover _2.sql mysql-bin mysql-bin disable-log-binsql recover _2.sql SQL ~]$ mysqlbinlog --disable-log-bin --stop-datetime=" :38:15" mysql-bin mysql-bin > recover sql SQL ~]$ mysql -u root -prootpass --default-character-set=cp932 < recover _2.sql 36

37 ~]$ vi /data/mysql/cnf/my.cnf # Replication Master Server (default) # binary logging is required for replication log-bin=/data/mysql/blog/mysql-bin [mysql@server ~]$ sudo /sbin/service mysql restart LINUX LVM Snapshot sync-split 37

38 38 Mysqldump DRBD 510%DRBDOS 4.5GB 900InnoDB) 100InnoDB)100MyISAM)

39 39

40 40

41 41

42 ➁ 42

43 Heartbeat Heartbeat 43

44 Heartbeat Heartbeat 44

45 45

46 46

47 47

48 48

49 49

50 READ PK POINT Non Tune InnoDB Non Tune MyISAM Tuned InnoDB Tuned MyISAM READ KEY POINT_LIMIT Non Tune InnoDB Non Tune MyISAM Tuned InnoDB Tuned MyISAM 50

51 51

52 52

53 53

54 MySQL4.1 54

55 55

56 56

57 57

58 58

59 Priority Priority Emergency Priority Highest Priority High Priority Warning Priority notice 59

60 60

61 61

62 WriteOracle RAC MySQL 62

63 63

64 64

65 65

66 66

67 67

68 68

69 69

70 Web Basic, Silver, Gold, Plutinum 70

スライド 1

スライド 1 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