MySQL多实例配置(一)，mysql实例配置
MySQL数据库的集中化运维，可以通过在一台MySQL数据库服务器上，部署多个MySQL实例。该功能是通过mysqld_multi来实现。mysqld_multi用于管理多个mysqld的服务进程，这些mysqld服务进程程序可以用不同的socket或是监听于不同的端口，同时将数据文件分布到不同的磁盘以分散IO。mysqld_multi提供简单的命令用于启动，关闭和报告所管理的服务器的状态。从而减少生产环境的维护成本，方便后续的迁移和清理等工作，借助多实例绑定的方式提高服务器的整体资源利用率。对于多实例的配置有2种方式，一种是在my.cnf为所有实例提供配置，一种是使用每一个实例一个配置文件。本文主要描述第一种方式。
第二种多实例配置方式请参考：MySQL多实例配置(二)
有关MySQL单实例的安装请参考：Linux 下MySQL源码安装完整版
1、各数据库多实例的差异
MSSQL
MSSQL中的实例指的是一个SQL server服务器上仅有一个缺省实例。缺省实例名即为机器名ServerName(或IP)。
如果在同一台机器上再安装SQL server，我们可以对实例命名如ServerName/InstanceName。
即一台SQL server服务器上可以存在多个不同的实例。一个实例下可以存在多个不同的数据库。
对于不同实例下的数据库的访问，使用ServerName/InstanceName：PortNo即可实现访问，缺省实例为ServerName：PortNo。
对不同的实例配置IP地址，相关的访问协议，端口等等。
实例的可访问性需要启动该实例对应的相关服务。此处需要注意的是实例名和实例的服务名并不是相同的。
缺省的实例的服务名为MSSQLSERVER，而命名实例的服务名为MSSQL$INSTANCE_NAME。
Oracle
一个Oracle Server由一个Oracle实例和一个Oracle数据库组成。即：Oracle Server = Oracle Instance + Oracle Database
在Oracle的实例主要是由SGA，PGA以及一堆的后台进程来组成，此称之为实例。
一系列物理文件的集合包括控制文件、数据文件、联机日志文件、参数文件、密码文件等称之为数据库。
一个实例只能访问一个数据库，一个数据库可以被多个实例访问。
MySQL
MySQL实例的概念与MSSQL差不多，一个MySQL实例下可以存在或访问N个数据库。
不同的实例间可以用不同的端口号来区分，各个实例的数据可以使用不同的磁盘目录。
MySQL多实例通过mysqld_multi工具来进行管理。
2、现有的环境
mysql安装路径: /u01/app/mysql
mysql数据路径: /u01/app/mysqldata/data3306
mysql端口号: 3306
mysql 版本：5.6.12 Source distribution
OS 环境：SUSE Linux Enterprise Server 11 SP3 (x86_64)
3、初始化实例
#为新实例创建数据目录并赋权
suse11:~ # mkdir -p /u01/app/mysqldata/data3406
suse11:~ # mkdir -p /u01/app/mysqldata/data3506
suse11:~ # chown mysql:mysql -R /u01/app/mysqldata/data3406
suse11:~ # chown mysql:mysql -R /u01/app/mysqldata/data3506
#初始化实例
suse11:~ # cd /u01/app/mysql
suse11:/u01/app/mysql # ./scripts/mysql_install_db --user=mysql --ldata=/u01/app/mysqldata/data3406/
suse11:/u01/app/mysql # ./scripts/mysql_install_db --user=mysql --ldata=/u01/app/mysqldata/data3506/
# Author : Leshami
# Blog : http://blog.csdn.net/leshami
4、修改配置文件
suse11:~ # more /etc/my.cnf #本配置文件中仅提供了多实例的基本参数，生产环境根据情形自行添加
[mysqld_multi]
mysqld = /u01/app/mysql/bin/mysqld_safe
mysqladmin = /u01/app/mysql/bin/mysqladmin
user = admin #此帐户用于多实例关闭时使用，需要在每个实例上创建并授权
password = xxx #使用统一的密码便于管理
[mysqld3306]
socket = /tmp/mysql3306.sock
port = 3306
pid-file = /u01/app/mysqldata/data3306/mysql3306.pid
datadir = /u01/app/mysqldata/data3306
basedir = /u01/app/mysql
user = mysql
server-id=3306
[mysqld3406]
socket = /tmp/mysql3406.sock
port = 3406
pid-file = /u01/app/mysqldata/data3406/mysql3406.pid
datadir = /u01/app/mysqldata/data3406
basedir = /u01/app/mysql
user = mysql
server-id=3406
[mysqld3506]
socket = /tmp/mysql3506.sock
port = 3506
pid-file = /u01/app/mysqldata/data3506/mysql3506.pid
datadir = /u01/app/mysqldata/data3506
basedir = /u01/app/mysql
user = mysql
server-id=3506
5、启动关闭多实例
suse11:~ # mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3406 is not running
MySQL server from group: mysqld3506 is not running
suse11:~ # mysqld_multi start 3306
suse11:~ # mysqld_multi start 3406,3506 #可以同时启动多个实例
suse11:~ # netstat -ntlp | grep mysql
tcp 0 0 :::3306 :::* LISTEN 14786/mysqld
tcp 0 0 :::3406 :::* LISTEN 15103/mysqld
tcp 0 0 :::3506 :::* LISTEN 15371/mysqld
suse11:/tmp # ls *.sock
mysql3306.sock mysql3406.sock mysql3506.sock
#为新实例修改密码及创建账户
suse11:/tmp # mysql -uroot -pxxx -S ./mysql3306.sock #3306已经有初始密码
root@localhost[(none)]> grant shutdown on *.* to 'admin'@'localhost' identified by 'xxx' with grant option;
suse11:/tmp # mysql -uroot -p -S ./mysql3406.sock
Enter password: #此时密码为空
root@localhost[(none)]> set password for 'root'@'localhost'=password('xxx');
root@localhost[(none)]> grant shutdown on *.* to 'admin'@'localhost' identified by 'xxx' with grant option;
suse11:/tmp # mysql -uroot -p -S ./mysql3506.sock
Enter password: #此时密码为空
root@localhost[(none)]> set password for 'root'@'localhost'=password('xxx');
root@localhost[(none)]> grant shutdown on *.* to 'admin'@'localhost' identified by 'xxx' with grant option;
#使用TCP方式登录测试
suse11:/tmp # mysql -uroot -pxxx -P3506
root@localhost[(none)]>
#检查多实例的状态
suse11:/tmp # mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3406 is running
MySQL server from group: mysqld3506 is running
#停止多实例服务器
suse11:~ # mysqld_multi stop 3306
suse11:~ # mysqld_multi report 3306
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
suse11:~ # mysqld_multi stop 3406
suse11:~ # mysqld_multi stop 3506
#多实例服务器的日志
suse11:~ # tail /u01/app/mysql/share/mysqld_multi.log
Stopping MySQL servers
Warning: Using a password on the command line interface can be insecure.
141017 23:40:09 mysqld_safe mysqld from pid file /u01/app/mysqldata/data3406/mysql3406.pid ended
mysqld_multi log file version 2.16; run: Fri Oct 17 23:40:09 2014
Stopping MySQL servers
Warning: Using a password on the command line interface can be insecure.
141017 23:40:11 mysqld_safe mysqld from pid file /u01/app/mysqldata/data3506/mysql3506.pid ended
#一次启动多个实例
suse11:~ # mysqld_multi start 3306-3506
suse11:~ # mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3406 is running
MySQL server from group: mysqld3506 is running
6、配置多实例的自启动
# echo "/u01/app/mysql/bin/mysqld_multi start 3306-3506" >>/etc/init.d/boot.local
配置文件还是一个，只不过mysql的数据库存放目录需要多个，这根据你的实例来配置数据库存放目录。
给个多实例配置案例：
初始化数据库
[mysql@localhost ~]$ mkdir /mydata/mysql_db/data_1001 #创建数据库存放目录
[mysql@localhost ~]$ mkdir /mydata/mysql_db/data_1002
[mysql@localhost ~]$ mkdir /mydata/mysql_db/data_1003
[mysql@localhost mysql_db]$ ./bin/mysql_install_db –basedir=/mydata/mysql_db –datadir=/mydata/mysql_db/data_1001 –user=mysql #数据库初始化
Installing MySQL system tables…
OK
Filling help tables…
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:
/mydata/mysql_db/bin/mysqladmin -u root password ‘new-password’
/mydata/mysql_db/bin/mysqladmin -u root -h localhost password ‘new-password’
Alternatively you can run:
/mydata/mysql_db/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 /mydata/mysql_db ; /mydata/mysql_db/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd /mydata/mysql_db/mysql-test ; perl mysql-test-run.pl
Please report any problems with the /mydata/mysql_db&......余下全文>>
给你个linux的配置 my.cnf的内容
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
[client]
socket = /tmp/mysql.sock
default-character-set=utf8
[mysqld1]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
character-set-server=utf8
default-storage-engine=innodb
port = 3306
table_open_cache = 64
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_buffer_pool_size = 50M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqld2]
datadir=/usr/local/mysql/data1
basedir=/usr/local/mysql
character-set-server=utf8
default-storage-engine=innodb
port = 3307
table_open_cache = 64
innodb_data_home_dir = /usr/local/mysql/data1
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data1
innodb_buffer_pool_size = 50M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
----【mysqld】中的配置和单实例的mysqld配置一致，多实例中每个[mysqld]需要有不同的端口号，sock，datadir
然后是启动：/usr/local/mysql/bin/mysqld_mulit --defaults-extra-file=/etc/my.cnf start 1
----这里的数字1 对应[mysqld1] ，关闭就是 把start 改......余下全文>>