Month: May 2019

ibdata* is a shared table space (It managed all data)
ib_logfile* seems to be a log file.

Data is not updated directory to the table space, but once the update contents are written to the log file.
After that, it seems that the flow is reflected in table space.
Tablespace updates are expensive and are not reflected immediately.
By writing to the log file, the writing performance is improved.

中身を見てみましょう

cd /var/lib/mysql
ls
sud cat ib_logfile0

なんじゃこりゃーー

– Default storage engine from MySQL5.5
– General purpose storage engine with good balance of reliability and performance.
– DML operations process transactions according to the ACID model, such as commit rollback and crash recovery
– Data is aligned on disk so that queries are optimized based on primary key
– Operations that call different storage engine tables can be mixed in one query
– Foreign key constraints are also supported
– InnoDB stores in tablespaces whereas MyISAM always stores data in file
– The good thing about MyISAM is that it can be easily backed up and copied in units of tables, but innoDB is still poorly managed in this respect.
– Table spaces can be stored in files, but can also be written directly to partitions.
– There is unique buffer pool for caching data and indexes in many mamory, which helps high speed data access.

う～ん、。。。

$ sudo cat /var/lib/mysql/ibdata1
ん？

# ./innochecksum /var/lib/mysql/ibdata1
0 bad checksum
13 FIL_PAGE_INDEX
19272 FIL_PAGE_UNDO_LOG
230 FIL_PAGE_INODE
1 FIL_PAGE_IBUF_FREE_LIST
892 FIL_PAGE_TYPE_ALLOCATED
2 FIL_PAGE_IBUF_BITMAP
195 FIL_PAGE_TYPE_SYS
1 FIL_PAGE_TYPE_TRX_SYS
1 FIL_PAGE_TYPE_FSP_HDR
1 FIL_PAGE_TYPE_XDES
0 FIL_PAGE_TYPE_BLOB
0 FIL_PAGE_TYPE_ZBLOB
0 other
3 max index_id

checksum以外は、FIL_PAGE_*だな。FILって何の略だろうか？

ibdata1には何が入っているのか？
– データディクショナリ(InnoDBテーブル)
– チェンジバッファ
– ダブルライトバッファ
– UNDOログ

んん？？

The binary log has not been output yet.
[vagrant@localhost ~]$ ls /var/lib/mysql/
addb count equity ibdata1 mysql.sock zeus
auto.cnf demo ib_logfile0 laravel57 performance_schema
click dev ib_logfile1 mysql test

Add log-bin and configure to output binary log.
log-binを足します。
[vagrant@localhost ~]$ sudo vi /etc/my.cnf
[vagrant@localhost ~]$ cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

character_set_server=utf8
default-storage-engine=InnoDB
innodb_file_per_table

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Settings user and group are ignored when systemd is used (fedora >= 15).
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
user=mysql

# Semisynchronous Replication
# http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
# uncomment next line on MASTER
;plugin-load=rpl_semi_sync_master=semisync_master.so
# uncomment next line on SLAVE
;plugin-load=rpl_semi_sync_slave=semisync_slave.so

# Others options for Semisynchronous Replication
;rpl_semi_sync_master_enabled=1
;rpl_semi_sync_master_timeout=10
;rpl_semi_sync_slave_enabled=1

# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
;performance_schema

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
log-bin

[vagrant@localhost ~]$ sudo service mysqld restart
mysqld を停止中: [ OK ]
mysqld を起動中: [ OK ]
[vagrant@localhost ~]$ ls /var/lib/mysql/
addb count equity ibdata1 mysql.sock zeus
auto.cnf demo ib_logfile0 laravel57 performance_schema
click dev ib_logfile1 mysql test

あれ、、、、、、、、、、、、　bin-log出てないやんけ。おかしいな。。

mysql> create database sample;
Query OK, 1 row affected (0.02 sec)

mysql> quit
Bye
[vagrant@localhost ~]$ ls /var/lib/mysql/

log-binの追加する位置が悪いか。。