1、安装MySQL
以ubuntu为例:
#apt-get install mysql-server mysql-client
默认端口为3306
2、创建新实例
创建一个端口为3307的新实例,
第1步mysql安装完成后,默认在/var/lib/mysql中有一个实例,
我们把新实例放在/var/lib/mysql2中,
先新建目录并赋予mysql权限:
#cd /var/lib/
#mkdir mysql2
#
chown mysql:mysql /var/lib/mysql2
再将mysql等系统数据库安装到实例准备运行的目录。
#bin/mysql_install_db --user=mysql --datadir=/var/lib/mysql2
这一步可能会报错,如遇错请看第6步
3、修改配置
#vi /etc/mysql/my.cnf
修改配置文件 my.cnf,这也是最重要的一步。主要是重新设置每个实例的pid-file,socket,port等属性,如下:
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root
# The MySQL server
[mysqld1]
user =mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
log-bin = master-bin
log-bin-index = master-bin.index
server-id = 1
[mysqld2]
user = mysql
pid-file = /var/run/mysqld2/mysqld.pid
socket = /var/run/mysqld2/mysqld.sock
port = 3307
basedir = /usr
datadir = /var/lib/mysql2
tmpdir = /tmp
log-bin = master-bin
log-bin-index = master-bin.index
server-id = 2
4、启动mysql服务
启动:/usr/local/mysql/bin/mysqld_multi start 1,2
停止:/usr/local/mysql/bin/mysqld_multi stop 1,2
也可以启动和停止其中的某个实例
/usr/local/mysql/bin/mysqld_multi start 2
/usr/local/mysql/bin/mysqld_multi stop 2
5、给新实例分配权限
#mysql -u root -p //
这一句 登录的是 3306的实例
#mysql -u root -p
-S
/var/run/mysqld2
/mysqld.sock
//这一句 登录的是 3307的实例
允许远程访问:
grant all privileges on *.* to 'myuser'@'%' identified by 'mypassword' with grant option;
flush privileges;
6、可能遇到的问题
(1)#bin/mysql_install_db --user=mysql --datadir=/var/lib/mysql2 执行出错
140905 17:54:19 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Table 'mysql.plugin' doesn't exist
140905 17:54:19 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
140905 17:54:19 InnoDB: The InnoDB memory heap is disabled
140905 17:54:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140905 17:54:19 InnoDB: Compressed tables use zlib 1.2.8
140905 17:54:19 InnoDB: Using Linux native AIO
140905 17:54:19 InnoDB: Initializing buffer pool, size = 100.0M
140905 17:54:19 InnoDB: Completed initialization of buffer pool
140905 17:54:19 InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.
这是
“apparmor”
MySQL profile security issue,解决方法是编辑
/etc/apparmor.d/usr.sbin.mysqld,加入下面几行:
#Allow data dir access
/var/lib/mysql2 r,
/var/lib/mysql2/** rwk,
加入之后记得重启
apparmor
/etc/init.d/apparmor restart
(2)启动mysql后,发现只有3306起来了,3307没有,/var/log/mysql/error.log中出现错误日志:
can't start server : Bind on unix socket: Permission denied
do you already have another mysqld server running on socket: /var/run/mysqld2/mysqld.sock?
这个问题跟上面一样,
编辑
/etc/apparmor.d/usr.sbin.mysqld,加入下面几行:
#Allow pid and socket file access
/run/mysqld2/mysqld.pid rw,
/run/mysqld2/mysqld.socket rw,
加入之后记得重启
apparmor
/etc/init.d/apparmor restart
(3)3306可以远程访问;3307只能本地访问,不能远程访问,报
ERROR 1130 (HY000): Host '192.168.0.1' is not allowed to connect to this MySQL server
十有八九是你的3307远程访问权限没有分配到位,而给3306分配了两次。
造成这种情况的原因是#mysql -u root -p 默认登录3306实例,
如果在一台机子上起多个MySQL实例, 比如端口号为 3306, 3307, 3308
登录时候要选择不同的 mysql.sock文件
mysql -uroot -p123456 这一句 登录的是 3306的实例
mysql -uroot -p123456 -S
/var/run/
mysqld2
/mysqld.sock
这一句 登录的是 3307的实例
这时候 用 -P 3307 这个选项是不管用的。 这个-P 的选项和 -h 应该是一起用的。
本机登录用的是这个sock文件。
参考: