最近はUTF-8のみで、すっかり忘れていたのでメモ

http://dev.mysql.com/doc/refman/5.6/en/charset-unicode-sets.html



root@localhost [(none)]>charset utf8;
Charset changed

root@localhost [(none)]>SHOW VARIABLES LIKE '%_connection';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_connection | utf8            |
| collation_connection     | utf8_general_ci |
+--------------------------+-----------------+
2 rows in set (0.01 sec)

root@localhost [(none)]>SELECT (CASE WHEN 'e'='è' THEN "同じ" ELSE "異なる" END);
+---------------------------------------------------------+
| (CASE WHEN 'e'='è' THEN "同じ" ELSE "異なる" END)       |
+---------------------------------------------------------+
| 同じ                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [(none)]>charset latin1;
Charset changed

root@localhost [(none)]>SHOW VARIABLES LIKE '%_connection';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_connection | latin1            |
| collation_connection     | latin1_swedish_ci |
+--------------------------+-------------------+
2 rows in set (0.00 sec)

root@localhost [(none)]>SELECT (CASE WHEN 'e'='è' THEN "同じ" ELSE "異なる" END);
+---------------------------------------------------------+
| (CASE WHEN 'e'='è' THEN "同じ" ELSE "異なる" END) |
+---------------------------------------------------------+
| 異なる                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [(none)]>


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