欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

一个mysql跑两个实列

程序员文章站 2022-05-07 20:34:57
...

在有些时间,我们的应用环境需要在一台机器上跑两个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跑两个实列,首发于运维者。