■MYSQLのセカンドノードを同じホストで立ち上げる■
※ mysqld_multi — 複数のMySQL サーバ管理とは違うやり方です。
単純にPortとデータディレクトリーを変更してMySQLインスタンスを立ち上げています。

前回の検証
複数MYSQLを設定

■インストール■

tarを解凍し/usr/local/mysql-5.6.7-rc-instance2としてフォルダー移動して、
mysql2としてシンボリックリンクを作成。

[root@192.168.11.36 local]# ln -s mysql-5.6.7-rc-instance2/ mysql2
[root@192.168.11.36 local]# ls -l
total 48
drwxr-xr-x  2 root   root   4096 Jun 11 15:42 bin
drwxr-xr-x  2 root   root   4096 Dec  7  2011 etc
drwxr-xr-x  2 root   root   4096 May 11  2011 games
drwxr-xr-x  2 root   root   4096 May 11  2011 include
drwxr-xr-x  2 root   root   4096 May 11  2011 lib
drwxr-xr-x  2 root   root   4096 May 11  2011 libexec
lrwxrwxrwx  1 root   root     29 Nov  2 18:15 mysql -> mysql-5.6.7-rc-linux2.6-i686/
lrwxrwxrwx  1 root   root     25 Nov  7 13:20 mysql2 -> mysql-5.6.7-rc-instance2/
drwxr-xr-x 13 root   root   4096 Nov  7 13:18 mysql-5.6.7-rc-instance2
drwxr-xr-x 13 mysql  mysql  4096 Nov  2 18:15 mysql-5.6.7-rc-linux2.6-i686
drwxrwxr-x  9 nagios nagios 4096 Aug  1 14:54 nagios
drwxr-xr-x  2 root   root   4096 May 11  2011 sbin
drwxr-xr-x  7 root   root   4096 Jun 11 15:42 share
drwxr-xr-x  2 root   root   4096 Nov  7 13:19 src
[root@192.168.11.36 local]# chown -R mysql:mysql mysql-5.6.7-rc-instance2/

■システムデータベースの作成■
※ –no-defaultsオプションを指定してOptionファイルが読まれないようにする。

[root@192.168.11.36 mysql2]# ./scripts/mysql_install_db --basedir=/usr/local/mysql2 --datadir=/usr/local/mysql2/data --user=mysql --socket=/tmp/mysqld2.sock --no-defaults
Installing MySQL system tables...
121107 13:22:01 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
121107 13:22:01 InnoDB: The InnoDB memory heap is disabled
121107 13:22:01 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
121107 13:22:01 InnoDB: Compressed tables use zlib 1.2.3
121107 13:22:01 InnoDB: Using Linux native AIO
121107 13:22:01 InnoDB: CPU does not support crc32 instructions
121107 13:22:01 InnoDB: Error: Linux Native AIO is not supported on tmpdir.
InnoDB: You can either move tmpdir to a file system that supports native AIO
InnoDB: or you can set innodb_use_native_aio to FALSE to avoid this message.
121107 13:22:01 InnoDB: Error: Linux Native AIO check on tmpdir returned error[22]
121107 13:22:01 InnoDB: Warning: Linux Native AIO disabled.
121107 13:22:01 InnoDB: Initializing buffer pool, size = 128.0M
121107 13:22:01 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
121107 13:22:01 InnoDB: Setting file ./ibdata1 size to 12 MB
InnoDB: Database physically writes the file full: wait...
121107 13:22:01 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
121107 13:22:02 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
121107 13:22:02 InnoDB: Doublewrite buffer not found: creating new
121107 13:22:02 InnoDB: Doublewrite buffer created
121107 13:22:02 InnoDB: 128 rollback segment(s) are active.
121107 13:22:02 [Warning] InnoDB: Creating foreign key constraint system tables.
121107 13:22:02 [Note] InnoDB: Foreign key constraint system tables created
121107 13:22:02 [Note] InnoDB: Creating tablespace and datafile system tables.
121107 13:22:02 [Note] InnoDB: Tablespace and datafile system tables created
121107 13:22:02 InnoDB: Waiting for the background threads to start
121107 13:22:02 InnoDB: 1.2.7 started; log sequence number 0
121107 13:22:02 [Note] Binlog end
121107 13:22:02 InnoDB: FTS optimize thread exiting.
121107 13:22:02 InnoDB: Starting shutdown...
121107 13:22:03 InnoDB: Shutdown completed; log sequence number 1629432
OK
Filling help tables...
121107 13:22:03 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
121107 13:22:03 InnoDB: The InnoDB memory heap is disabled
121107 13:22:03 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
121107 13:22:03 InnoDB: Compressed tables use zlib 1.2.3
121107 13:22:03 InnoDB: Using Linux native AIO
121107 13:22:03 InnoDB: CPU does not support crc32 instructions
121107 13:22:03 InnoDB: Error: Linux Native AIO is not supported on tmpdir.
InnoDB: You can either move tmpdir to a file system that supports native AIO
InnoDB: or you can set innodb_use_native_aio to FALSE to avoid this message.
121107 13:22:03 InnoDB: Error: Linux Native AIO check on tmpdir returned error[22]
121107 13:22:03 InnoDB: Warning: Linux Native AIO disabled.
121107 13:22:03 InnoDB: Initializing buffer pool, size = 128.0M
121107 13:22:03 InnoDB: Completed initialization of buffer pool
121107 13:22:03 InnoDB: highest supported file format is Barracuda.
121107 13:22:03 InnoDB: 128 rollback segment(s) are active.
121107 13:22:03 InnoDB: 1.2.7 started; log sequence number 1629432
121107 13:22:03 [Note] Binlog end
121107 13:22:03 InnoDB: FTS optimize thread exiting.
121107 13:22:03 InnoDB: Starting shutdown...
121107 13:22:05 InnoDB: Shutdown completed; log sequence number 1629442
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/mysql2/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql2/bin/mysqladmin -u root -h 192.168.11.36.variable.jp password 'new-password'
Alternatively you can run:
/usr/local/mysql2/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr/local/mysql2 ; /usr/local/mysql2/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd /usr/local/mysql2/mysql-test ; perl mysql-test-run.pl
Please report any problems with the /usr/local/mysql2/scripts/mysqlbug script!

[root@192.168.11.36 mysql2]#

■セカンドインスタンスの起動■

[root@192.168.11.36 mysql2]# /usr/local/mysql2/bin/mysqld_safe --no-defaults --basedir=/usr/local/mysql2 --datadir=/usr/local/mysql2/data --user=mysql --socket=/tmp/mysqld2.sock --port=3308 &
[1] 27588
[root@192.168.11.36 mysql2]# 121107 13:22:43 mysqld_safe Logging to '/usr/local/mysql2/data/192.168.11.36.variable.jp.err'.
121107 13:22:43 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql2/data
[root@192.168.11.36 mysql2]#

■セカンドインスタンスへのログインとアカウント設定■
※ Socketの指定してログイン

[root@192.168.11.36 mysql2]# mysql --socket=/tmp/mysqld2.sock -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.7-rc MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

mysql> GRANT ALL PRIVILEGES  ON *.* TO root@localhost IDENTIFIED BY 'AWS-PASS-sQL2012' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES  ON *.* TO root@127.0.0.1 IDENTIFIED BY 'AWS-PASS-sQL2012' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES  ON *.* TO root@`192.168.11.36.variable.jp` IDENTIFIED BY 'AWS-PASS-sQL2012' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql>


mysql> show variables like 'port%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3308  |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

■起動状態確認■
※2つインスタンスが立ち上がっている事が確認出来る。

[root@192.168.11.36 local]# netstat -an | egrep '3306|3308'
tcp        0      0 :::3306                     :::*                        LISTEN
tcp        0      0 :::3308                     :::*                        LISTEN
[root@192.168.11.36 local]#


[root@192.168.11.36 local]# netstat -an | grep '3306\|3308'
tcp        0      0 :::3306                     :::*                        LISTEN
tcp        0      0 :::3308                     :::*                        LISTEN
[root@192.168.11.36 local]#

■Optionファイル指定■
2ndインスタンスのMySQLをKillして停止してから、
Socketやポートを指定してあるOptionファイルを指定してMYSQLを起動してみる。

[root@192.168.11.36 mysql2]# /usr/local/mysql2/bin/mysqld_safe --defaults-file=/etc/my2.cnf --basedir=/usr/local/mysql2 --datadir=/usr/local/mysql2/data --user=mysql --socket=/tmp/mysqld2.sock &
[1] 3320
[root@192.168.11.36 mysql2]# 121107 15:39:11 mysqld_safe Logging to '/usr/local/mysql2/data/192.168.11.36.variable.jp.err'.
121107 15:39:11 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql2/data
[root@192.168.11.36 mysql2]#


■ログインし動作確認■

※1st,2nd共にログイン出来るか確認。

[root@192.168.11.36 mysql2]# mysql --socket=/tmp/mysqld2.sock -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.7-rc MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'port%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3308  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> exit
Bye

[root@192.168.11.36 mysql2]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.7-rc-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'port%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

■ログファイルサイズ変更■
追加なので、マルチインスタンスとは関係無いです。

[root@192.168.11.36 mysql2]# vi /etc/my2.cnf
[root@192.168.11.36 mysql2]# cd data/
[root@192.168.11.36 data]# ls -l
total 22648
-rw-rw---- 1 mysql root     60642 Nov  7 15:43 192.168.11.36.variable.jp.err
-rw-rw---- 1 mysql mysql       56 Nov  7 13:25 auto.cnf
-rw-rw---- 1 mysql mysql 12582912 Nov  7 15:43 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Nov  7 15:43 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Nov  7 13:22 ib_logfile1
drwxr-xr-x 2 mysql mysql     4096 Nov  7 13:22 mysql
-rw-rw---- 1 mysql mysql        0 Nov  7 13:22 mysql-bin.index
drwx------ 2 mysql mysql     4096 Nov  7 13:22 performance_schema
drwxr-xr-x 2 mysql mysql     4096 Nov  7 13:18 test
[root@192.168.11.36 data]# mv ib_logfile0 ib_logfile0.bk
[root@192.168.11.36 data]# mv ib_logfile1 ib_logfile1.bk
[root@192.168.11.36 data]# /usr/local/mysql2/bin/mysqld_safe --defaults-file=/etc/my2.cnf --basedir=/usr/local/mysql2 --datadir=/usr/local/mysql2/data --user=mysql --socket=/tmp/mysqld2.sock &
[1] 3960
[root@192.168.11.36 data]# 121107 15:44:53 mysqld_safe Logging to '/usr/local/mysql2/data/192.168.11.36.variable.jp.err'.
121107 15:44:53 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql2/data
[root@192.168.11.36 data]# ls -lh
total 43M
-rw-rw---- 1 mysql root   63K Nov  7 15:44 192.168.11.36.variable.jp.err
-rw-rw---- 1 mysql mysql    5 Nov  7 15:44 192.168.11.36.variable.jp.pid
-rw-rw---- 1 mysql mysql   56 Nov  7 13:25 auto.cnf
-rw-rw---- 1 mysql mysql  12M Nov  7 15:44 ibdata1
-rw-rw---- 1 mysql mysql  10M Nov  7 15:44 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Nov  7 15:43 ib_logfile0.bk
-rw-rw---- 1 mysql mysql  10M Nov  7 15:44 ib_logfile1
-rw-rw---- 1 mysql mysql 5.0M Nov  7 13:22 ib_logfile1.bk
drwxr-xr-x 2 mysql mysql 4.0K Nov  7 13:22 mysql
-rw-rw---- 1 mysql mysql    0 Nov  7 13:22 mysql-bin.index
drwx------ 2 mysql mysql 4.0K Nov  7 13:22 performance_schema
drwxr-xr-x 2 mysql mysql 4.0K Nov  7 13:18 test
[root@192.168.11.36 data]#