一个mysql跑两个实列

在有些时间，我们的应用环境需要在一台机器上跑两个mysql的实例，来满足开发需求和测试需求，下面是我在一个mysql下面跑的两个实例，配置文件如下： [client]port = 3306socket = /tmp/mysql.sock[mysqld_multi]mysqld = /data/mysql/bin/mysqld_safemysqlad

在有些时间，我们的应用环境需要在一台机器上跑两个mysql的实例，来满足开发需求和测试需求，下面是我在一个mysql下面跑的两个实例，配置文件如下：

[client]
port                           = 3306
socket                         = /tmp/mysql.sock
[mysqld_multi]
mysqld                         = /data/mysql/bin/mysqld_safe
mysqladmin                     = /data/mysql/bin/mysqladmin
user                           = root
password                       = **************
#DB1
[mysqld1]
port                           = 3306
socket                         = /tmp/mysql.sock
default-storage-engine         = InnoDB
pid-file                       = /data/mysql/mysql.pid
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
innodb                         = FORCE
innodb-strict-mode             = 1
datadir                        = /data/mysql/var/
log-bin                        = /data/mysqllog/binlog/mysql-bin
expire-logs-days               = 15
sync-binlog                    = 1
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 2G
binlog_format                  = row
log-error                      = /data/mysql/mysql-error.log
log-queries-not-using-indexes  = 0
slow-query-log                 = 1
slow-query-log-file            = /data/mysql/mysql-slow.log
long_query_time                = 1
#DB2
[mysqld2]
port                           = 3307
socket                         = /tmp/mysql.sock2
default-storage-engine         = InnoDB
pid-file                       = /data/mysql/mysql.pid2
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
innodb                         = FORCE
innodb-strict-mode             = 1
datadir                        = /data/mysql/var2/
log-bin                        = /data/mysqllog/binlog2/mysql-bin
expire-logs-days               = 15
sync-binlog                    = 1
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 2G
binlog_format                  = row
log-error                      = /data/mysql/mysql2-error.log
log-queries-not-using-indexes  = 0
slow-query-log                 = 1
slow-query-log-file            = /data/mysql/mysql2-slow.log
long_query_time                = 1

查看实例运行情况：

# netstat -lnpt | grep mysqld
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      12356/mysqld        
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      13204/mysqld 
# mysql -uroot -p -h192.168.1.33 -P3306
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 5.5.34-log MySQL Community Server (GPL)
Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cat_db             |
| mhac_db            |
| mysql              |
| passport_db        |
| pay_db             |
| performance_schema |
+--------------------+
7 rows in set (0.00 sec)
mysql> q
Bye
]# mysql -uroot -p -h192.168.1.33 -P3307
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 18
Server version: 5.5.34-log MySQL Community Server (GPL)
Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> q
Bye

一个mysql跑两个实列，首发于运维者。