Transportable Tablespace on MySQLの動作確認
MyISAMではなく、InnoDBがこれからのMySQLのコアなストレージエンジンなので、
MyISAMの時のようにファイルコピーして、データベースを他のサーバーでも
利用する機会があるかと思い、一応InnoDBで実装されたTransportable Tablespaceを確認。

Sakilaデータベースのlanguage tableを他のDBインスタンスに移動してみる。

root@localhost [(none)]>select @@hostname;
+------------+
| @@hostname |
+------------+
| CentOS01   |
+------------+
1 row in set (0.00 sec)

root@localhost [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| audit_test         |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
+--------------------+
7 rows in set (0.00 sec)

root@localhost [(none)]>

root@localhost [(none)]>use sakila
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@localhost [sakila]>show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| rental2                    |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
24 rows in set (0.00 sec)


root@localhost [sakila]>show create database sakila;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| sakila   | CREATE DATABASE `sakila` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [sakila]>


root@localhost [sakila]>show create table language\G
*************************** 1. row ***************************
       Table: language
Create Table: CREATE TABLE `language` (
  `language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`language_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root@localhost [sakila]>

root@localhost [sakila]>FLUSH TABLES language FOR EXPORT;
Query OK, 0 rows affected (0.01 sec)

root@localhost [sakila]>

FOR EXPORTで対象にしたテーブル用にcfgファイルが出来ているので、
ibdとcfgファイルをコピー先にコピーする。

[root@CentOS01 sakila]# pwd
/usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/sakila
[root@CentOS01 sakila]# ls -l language.*
-rw-rw----. 1 mysql mysql   477 11月  3 15:03 language.cfg
-rw-rw----. 1 mysql mysql  8648 10月 30 12:08 language.frm
-rw-rw----. 1 mysql mysql 98304 10月 30 12:08 language.ibd
[root@CentOS01 sakila]# 

[root@CentOS01 sakila]# scp language.{ibd,cfg} 192.168.56.102:/usr/local/mysql/data/sakila
root@192.168.56.102's password: 
language.ibd                                                                                                                                100%   96KB  96.0KB/s   00:00    
language.cfg                                                                                                                                100%  477     0.5KB/s   00:00    
[root@CentOS01 sakila]# 

コピーが終わったので、テーブルをUNLOCKして利用可能にする。

root@localhost [sakila]>UNLOCK TABLES;
Query OK, 0 rows affected (0.01 sec)

root@localhost [sakila]>

root@localhost [sakila]>select * from language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set (0.00 sec)

root@localhost [sakila]>

UNLOCKするまで、対象テーブルはSELECT出来ますが、
INSERT,UPDATE,DELETEなど変更を伴うDMLは待たされます。

lock

コピー先のインスタンスに予め空のDBとテーブルを作成しておく。
※Scriptはコピー元でSHOW CREATEコマンドで確認したSCRIPTを実行してあります。

root@localhost [(none)]> select @@hostname;
+------------+
| @@hostname |
+------------+
| CentOS02   |
+------------+
1 row in set (0.00 sec)

root@localhost [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

root@localhost [(none)]> 


root@localhost [(none)]> CREATE DATABASE `sakila` /*!40100 DEFAULT CHARACTER SET utf8 */;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use sakila;
Database changed
root@localhost [sakila]> CREATE TABLE `language` (
    ->   `language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
    ->   `name` char(20) NOT NULL,
    ->   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`language_id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.10 sec)

root@localhost [sakila]> alter table language DISCARD TABLESPACE;                                                                                                             Query OK, 0 rows affected (0.08 sec)

root@localhost [sakila]> 

上記で空テーブルを無効にしてあるので、コピー元のインスタンスからファイルコピーし終わったら、
以下のコマンドでテーブルスペースをIMPORTする。

root@localhost [sakila]> alter table language Import TABLESPACE;
Query OK, 0 rows affected (0.13 sec)

root@localhost [sakila]> 

root@localhost [sakila]> show tables;
+------------------+
| Tables_in_sakila |
+------------------+
| language         |
+------------------+
1 row in set (0.00 sec)

root@localhost [sakila]> select * from language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set (0.00 sec)

root@localhost [sakila]> 

これで、同じ内容のテーブルが2台のサーバーで利用可能になりました。
コピーしたcfgファイルはバイナリ―ファイルになります。

参考
http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html


ViewとTableで同じデータを持ったオブジェクトを作成する事が出来るが、
それぞれメリットデメリットがある。Viewは常に最新のデータが反映されるという
メリットがあるが、毎回計算が入るので速度が遅い。Tableに関しては、速度は速いが
最新のデータが常に反映されない。用途にもよるので、それぞれ最適な方法を
選択する必要がある。最適なのは、テーブルのデータが常に最新のデータで反映
される事だが、Viewを必要とする時は対外テーブルが分散されている場合が多い…

以下のテーブルとビューは同じ結果を抽出することが出来る。

CREATE VIEW V_Surface
(Name, ContinentSurface, CountryAvgSurface)
AS SELECT Continent, SUM(SurfaceArea),
AVG(SurfaceArea)
FROM Country GROUP BY Continent;


CREATE TABLE T_Surface
AS SELECT Continent AS Name,
SUM(SurfaceArea) AS ContinentSurface,
AVG(SurfaceArea) AS CountryAvgSurface
FROM Country GROUP BY Continent;

table_vs_view


CItyテーブルから首都のみを選んでCapitalテーブルを作成。

city

1) テーブル作成
CREATE TABLE Capitals LIKE City;

create_table_like

2) 首都データの投入

INSERT INTO Capitals SELECT * FROM City
where ID IN(select Capital from Country where Capital is not null);

insert_into

select * from Capitals where name like 'to%' order by name;

tokyo

=================
その他の方法(全てのデータをいれてから削除
=================


mysql> CREATE TABLE Capitals LIKE City;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO Capitals SELECT * FROM City;
Query OK, 4079 rows affected (0.17 sec)
Records: 4079 Duplicates: 0 Warnings: 0

mysql> delete from Capitals where ID NOT IN
-> (select Capital from Country where Capital is not null);
Query OK, 3847 rows affected (4.86 sec)

mysql> select count(*) from Capitals;
+----------+
| count(*) |
+----------+
| 232 |
+----------+
1 row in set (0.00 sec)

mysql> select * from Capitals where name like 'to%' order by name;
+------+---------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------+-------------+----------------+------------+
| 1532 | Tokyo | JPN | Tokyo-to | 7980230 |
| 3503 | Toskent | UZB | Toskent Shahri | 2117500 |
+------+---------+-------------+----------------+------------+
2 rows in set (0.01 sec)

mysql>

delete


SQL2000, SQL2005, SQL2008でテーブルサイズを調査するクエリー
テーブル名、行数、データサイズ(MB)


SELECT object_name(id)as 'Table Name' ,rowcnt as 'Number of Rows',
dpages as 'Number of Pages',(dpages * 8 )/1024 'サイズ(MB)'
FROM sysindexes
WHERE indid IN (1,0)
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
ORDER BY 'サイズ(MB)' DESC


ALTER TABLE で既存テーブルの構造を変更する事ができます。例えば、カラムの追加や削除、
インデックスの作成や破壊、既存カラム タイプの変更、またはカラムやテーブル自体の名前の変更
をする事ができます。テーブルや、テーブル タイプのコメントを変更する事もできます。

以下の例は、テーブルに新規の列を場所を指定して追加している例です。


mysql> desc Add_Columns;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1 | char(5) | NO | PRI | NULL | |
| col2 | char(5) | NO | PRI | | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table Add_Columns
-> add col0 int first,
-> add col3 int after col2,
-> add col4 int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc Add_Columns;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col0 | int(11) | YES | | NULL | |
| col1 | char(5) | NO | PRI | NULL | |
| col2 | char(5) | NO | PRI | | |
| col3 | int(11) | YES | | NULL | |
| col4 | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql>

alter_table_add_col

———————————————
参考サイト
———————————————

12.1.2. ALTER TABLE 構文

12.1.1. ALTER DATABASE 構文


OSによって大文字小文字の区別が違うので、大文字小文字の扱いで面倒になることも多少あると思います。
MYSQLでは扱いを楽にするために以下の設定が準備されています。

    ※データベース、テーブルなどのオブジェクトを作成する前に設定しておいた方が良い。

MySQL において、データベースはデータディレクトリ内のディレクトリに対応しています。
データベース内の各テーブルも、データベースディレクトリ内の少なくとも1つ(記憶エンジンによってはそれ以上)
のファイルに対応しています。そのため、ベースとなっているオペレーティングシステムで大文字と小文字が区別
される場合、データベース名とテーブル名でも大文字と小文字が区別されます。つまり、Windows ではデータベース
名とテーブル名で大文字と小文字は区別されず、ほとんどの種類の Unix では大文字と小文字が区別されること
になります。ただし、重要な例外が 1 つあります。Mac OS X で、Unixをベースとしているがデフォルトの
HFS+ ファイルシステムを使用している場合です。この場合は大文字と小文字が区別されません。
しかし、Mac OS X は UFS ボリュームもサポートしています。UFS ボリュームでは Unix の場合と同じように
Mac OS X でも大文字と小文字が区別されます。


mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql>

lowercase


mysql> CREATE DATABASE CaseSensitive;
Query OK, 1 row affected (0.00 sec)

mysql> use Casesensitive;
ERROR 1049 (42000): Unknown database 'Casesensitive'
mysql> use CaseSensitive;
Database changed
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| CaseSensitive |
| DB01 |
| DB02 |
| MyAdmin |
| STUDY |
| TEST |
| client_test_db |
| mysql |
+--------------------+
9 rows in set (0.01 sec)

mysql>

database_casesensitive

詳細情報
8.2.2. 識別子の大文字/小文字区別


NULLとDEFAULT値の動作確認


mysql> CREATE TABLE NULL_DEFAUL (
-> test1 INT NOT NULL,
-> test2 INT NULL,
-> test3 INT NOT NULL DEFAULT 123,
-> test4 INT NULL DEFAULT 456,
-> test5 CHAR(1) NOT NULL DEFAULT '0',
-> test6 VARCHAR(100) NOT NULL DEFAULT 'NO VALUES'
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc NULL_DEFAUL;
+-------+--------------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+-----------+-------+
| test1 | int(11) | NO | | NULL | |
| test2 | int(11) | YES | | NULL | |
| test3 | int(11) | NO | | 123 | |
| test4 | int(11) | YES | | 456 | |
| test5 | char(1) | NO | | 0 | |
| test6 | varchar(100) | NO | | NO VALUES | |
+-------+--------------+------+-----+-----------+-------+
6 rows in set (0.01 sec)

mysql>

default

動作確認


mysql> desc NULL_DEFAUL;
+-------+--------------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+-----------+-------+
| test1 | int(11) | NO | | NULL | |
| test2 | int(11) | YES | | NULL | |
| test3 | int(11) | NO | | 123 | |
| test4 | int(11) | YES | | 456 | |
| test5 | char(1) | NO | | 0 | |
| test6 | varchar(100) | NO | | NO VALUES | |
+-------+--------------+------+-----+-----------+-------+
6 rows in set (0.00 sec)

mysql> insert into NULL_DEFAUL(test1) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from NULL_DEFAUL;
+-------+-------+-------+-------+-------+-----------+
| test1 | test2 | test3 | test4 | test5 | test6 |
+-------+-------+-------+-------+-------+-----------+
| 1 | NULL | 123 | 456 | 0 | NO VALUES |
+-------+-------+-------+-------+-------+-----------+
1 row in set (0.00 sec)

mysql>

default2


MySQL Administrator と MySQL Query Browserどちらのツールも
データベース(スキーマ)作成、テーブル作成などが出来ますが、
データの変更はMySQL Query Browserのみが出来ます。

① MySQLAdministratorでのデータベースの作成

mysqladmin_cratedb

② MySQLAdministratorでのテーブルの作成
mysqladmin_cratetable

Maintenanceは…

[Optimize Table] — テーブルの最適化
[Check Table] —– テーブルのチェック
[Repair Table] —- テーブルの修復


==============================================
まとめてデータベース内のテーブルストレージタイプをリスト
==============================================


SELECT table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema = 'TEST'
ORDER BY table_name DESC;

my_info

==============================================
テーブルで利用されているインデックスの数リスト
==============================================


SELECT
t.table_schema,t.table_name
,COUNT( IF(c.column_key = 'PRI',1,NULL) ) AS PK
,COUNT( IF(c.column_key != 'PRI',1,NULL) ) AS POSSIBLE
FROM information_schema.tables t
LEFT JOIN information_schema.columns c
ON t.table_schema = c.table_schema
AND t.table_name = c.table_name
AND c.column_key != ''
WHERE t.table_type != 'VIEW'
AND t.table_schema = database()
GROUP BY t.table_name
ORDER BY table_schema, table_name;

te-buru_key

==============================================
DBオブジェクトリスト
==============================================


(SELECT
table_schema AS object_schema,
table_name AS object_name,
table_type AS object_type
FROM information_schema.tables
)
UNION ALL
(SELECT
routine_schema,
routine_name,
routine_type
FROM information_schema.routines )
UNION ALL
(SELECT
trigger_schema,
trigger_name,
'TRIGGER'
FROM information_schema.triggers )
ORDER BY object_schema, object_type, object_name;

obj


mysql> show table status like ‘TABLE007’\G
*************************** 1. row ***************************
Name: TABLE007
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 9
Avg_row_length: 1820
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 10
Create_time: 2009-02-17 11:30:45
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql>

mysql> show create table TABLE007\G
*************************** 1. row ***************************
Table: TABLE007
Create Table: CREATE TABLE `TABLE007` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

mysql> select table_name, Engine from information_schema.tables where
-> table_schema = ‘DB01’ and table_name = ‘TABLE007’;
+————+——–+
| table_name | Engine |
+————+——–+
| TABLE007 | InnoDB |
+————+——–+
1 row in set (0.00 sec)

mysql>

show_table_status

mysql> show engines;
\+————+———+—————————————————————-+————–+—–+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+————+———+—————————————————————-+————–+—–+————+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+————+———+—————————————————————-+————–+—–+————+
7 rows in set (0.00 sec)
mysql>

DEFAULT & YES = Storage Engine is compiled and enabled.
DISABLED = Compiled in but disabled.
NO = Not Compiled in when server was build.

engine

※ MYISAM, MERGE,MEMORYは常に利用可能なストレージエンジン
サポートされたストレージエンジン

『テーブルに利用されているストレージエンジンを把握する為の3つの方法。』

━━━━━━ SHOW TABLE STATUS ━━━━━━

mysql> show table status like ‘mt_author’\G
*************************** 1. row ***************************
Name: mt_author
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 1
Avg_row_length: 88
Data_length: 88
Max_data_length: 281474976710655
Index_length: 6144
Data_free: 0
Auto_increment: 2
Create_time: 2008-03-10 17:55:37
Update_time: 2008-03-10 17:55:37
Check_time: 2008-03-10 17:55:37
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

━━━━━━ SHOW CREATE ━━━━━━

mysql> show create table mt_author\G
*************************** 1. row ***************************
Table: mt_author
Create Table: CREATE TABLE `mt_author` (
`author_id` int(11) NOT NULL auto_increment,
`author_api_password` varchar(60) collate utf8_bin default NULL,
`author_can_create_blog` tinyint(4) default NULL,
`author_can_view_log` tinyint(4) default NULL,
`author_email` varchar(75) collate utf8_bin default NULL,
`author_entry_prefs` varchar(255) collate utf8_bin default NULL,
`author_hint` varchar(75) collate utf8_bin default NULL,
`author_is_superuser` tinyint(4) default NULL,
`author_name` varchar(50) collate utf8_bin NOT NULL default ”,
`author_nickname` varchar(50) collate utf8_bin default NULL,
`author_password` varchar(60) collate utf8_bin NOT NULL default ”,
`author_preferred_language` varchar(50) collate utf8_bin default NULL,
`author_public_key` mediumtext collate utf8_bin,
`author_remote_auth_token` varchar(50) collate utf8_bin default NULL,
`author_remote_auth_username` varchar(50) collate utf8_bin default NULL,
`author_type` smallint(6) NOT NULL default ‘1’,
`author_url` varchar(255) collate utf8_bin default NULL,
`author_created_on` datetime default NULL,
`author_created_by` int(11) default NULL,
`author_modified_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`author_modified_by` int(11) default NULL,
PRIMARY KEY (`author_id`),
KEY `mt_author_email` (`author_email`),
KEY `mt_author_created_on` (`author_created_on`),
KEY `mt_author_name` (`author_name`),
KEY `mt_author_type` (`author_type`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

━━━━━━ INFORMATION SCHEMA ━━━━━━

mysql> select TABLE_NAME,TABLE_TYPE,ENGINE from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA =’MT’;
+—————–+————+——–+
| TABLE_NAME | TABLE_TYPE | ENGINE |
+—————–+————+——–+
| mt_author | BASE TABLE | MyISAM |
| mt_blog | BASE TABLE | MyISAM |
| mt_category | BASE TABLE | MyISAM |
| mt_comment | BASE TABLE | MyISAM |
| mt_config | BASE TABLE | MyISAM |
| mt_entry | BASE TABLE | MyISAM |
| mt_fileinfo | BASE TABLE | MyISAM |
| mt_ipbanlist | BASE TABLE | MyISAM |
| mt_log | BASE TABLE | MyISAM |
| mt_notification | BASE TABLE | MyISAM |
| mt_objecttag | BASE TABLE | MyISAM |
| mt_permission | BASE TABLE | MyISAM |
| mt_placement | BASE TABLE | MyISAM |
| mt_plugindata | BASE TABLE | MyISAM |
| mt_rfdata | BASE TABLE | MyISAM |
| mt_session | BASE TABLE | MyISAM |
| mt_tag | BASE TABLE | MyISAM |
| mt_tbping | BASE TABLE | MyISAM |
| mt_template | BASE TABLE | MyISAM |
| mt_templatemap | BASE TABLE | MyISAM |
| mt_trackback | BASE TABLE | MyISAM |
+—————–+————+——–+
21 rows in set (0.01 sec)

mysql>