多个mysql实例意味着我们可以同时运行多个mysql服务器。
如果还没有安装mysql服务器，可以使用命令apt-get install mysql-server进行安装

在Ubuntu中配置多个mysql实例步骤：

新建mysql实例的mysql配置目录

cp -prvf /etc/mysql/ /etc/mysql2

新建实例的数据目录，赋予新目录权限和所有权。

mkdir -p /var/lib/mysql2
chown --reference /var/lib/mysql /var/lib/mysql2
chmod --reference /var/lib/mysql /var/lib/mysql2

新建实例日志目录

mkdir -p /var/log/mysql2
chown --reference /var/log/mysql /var/log/mysql2
chmod --reference /var/log/mysql /var/log/mysql2

编辑/etc/mysql2/my.cnf文件,如下所示。

每个实例的port不能冲突。

vi /etc/mysql2/my.cnf

[client]
port		= 3337
socket		= /var/run/mysqld/mysqld2.sock
[mysqld_safe]
socket		= /var/run/mysqld/mysqld2.sock
nice		= 0
[mysqld]
user		= mysql
pid-file	= /var/run/mysqld/mysqld2.pid
socket		= /var/run/mysqld/mysqld2.sock
port		= 3337
basedir		= /usr
datadir		= /var/lib/mysql2
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
bind-address		= 127.0.0.1
key_buffer		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit	= 1M
query_cache_size        = 16M
log_error = /var/log/mysql2/error.log
expire_logs_days	= 10
max_binlog_size         = 100M
[mysqldump]
quick
quote-names
max_allowed_packet	= 16M
[mysql]
[isamchk]
key_buffer		= 16M
!includedir /etc/mysql2/conf.d/

编辑/etc/mysql2/debian.cnf文件

只需要修改两处socket的值

In section [client]

socket   = /var/run/mysqld/mysqld2.sock

In section [mysql_upgrade]

socket   = /var/run/mysqld/mysqld2.sock

/etc/mysql2/debian.cnf 配置参考：

[client]
host = localhost
user = debian-sys-maint
password = s5ozLSHeoO4cRaDi
socket = /var/run/mysqld/mysqld2.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = s5ozLSHeoO4cRaDi
socket = /var/run/mysqld/mysqld2.sock
basedir = /usr

编辑mysql的apparmor文件

这是重要的一步。

在/etc/apparmor.d/usr.sbin.mysqld文件中添加下面配置：

vi /etc/apparmor.d/usr.sbin.mysqld

### secondary a.k.a mysql2 ####

/etc/mysql2/*.pem r,
/etc/mysql2/conf.d/ r,
/etc/mysql2/conf.d/* r,
/etc/mysql2/*.cnf r,
/var/lib/mysql2/ r,
/var/lib/mysql2/** rwk,
/var/log/mysql2/ r,
/var/log/mysql2/* rw,
/{,var/}run/mysqld/mysqld2.pid w,
/{,var/}run/mysqld/mysqld2.sock w,

########################

重启apparmor服务

/etc/init.d/apparmor restart

在新的mysql数据目录下启动新的数据库

mysql_install_db --user=mysql --datadir=/var/lib/mysql2

第一次运行，可能看到两个 警告信息：

root@ubuntu:~# mysql_install_db --user=mysql --datadir=/var/lib/mysql2
Installing MySQL system tables...
131204 21:07:39 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
OK
Filling help tables...
131204 21:07:39 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
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/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h ubuntu password 'new-password'

Alternatively you can run:
/usr/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 ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/scripts/mysqlbug script!

root@ubuntu:~#

首先解决这2个警告。

sed -i 's/key_buffer/key_buffer_size/' /etc/mysql2/my.cnf

根据提示，将/etc/mysql2/my.cnf中的key_buffer更改为key_buffer_size。

131204 21:07:39 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
OK
Filling help tables…
131204 21:07:39 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
OK

通过下面的命令启动新的mysql实例。

启动新mysql实例

nohup mysqld_safe --defaults-file=/etc/mysql2/my.cnf &

检查新mysql实例是否正在侦听的端口3337。

root@ubuntu:~# netstat -tanp|grep mysql
tcp        0      0 127.0.0.1:3337          0.0.0.0:*               LISTEN      4257/mysqld     
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      3151/mysqld     
root@ubuntu:~#

连接到新的mysql实例。

mysql -u root -P 3337 -h 127.0.0.1

其中

1. -u=用户
2. -P=端口号(这里我们使用的是3337)
3. -h=主机

设置mysql的root密码

mysql > update mysql.user set password=PASSWORD("NEW-PASSWORD") where User='root';
mysql > flush privileges;
mysql > exit

停止新的mysql实例的命令

停止新mysql实例

mysqladmin -S /var/lib/mysql/mysql2.sock shutdown -p

1. -p 指定密码
2. -S MySQL套接字文件的路径

使用相同的方法，我们可以在同一台服务器上创建其他MySQL实例。