![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
■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]#