mysql查看多实例端口_Linux下实现mysql多端口多实例管理

1.     关闭原有的默认端口

service mysqld stop

2.拷贝或创建数据文件

#拷贝现有的mysql数据库文件

#我的在/var/lib/mysql，拷贝一份至mysql_3307文件夹

cp -r /var/lib/mysql /var/lib/mysql_3307

#创建一个新的空数据库

mkdir /var/lib/mysql_3307

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

3.给数据文件赋予mysql用户与用户

chown -R mysql.mysql /var/lib/mysql_3307

4.创建multi的配置cnf文件,用于启动这个mysql实例(如3307)载入执行

touch /usr/local/my_multi.cnf

文件中写入你想要的配置，如下为典型配置

[mysqld_multi]

mysqld= /usr/bin/mysqld_safe

mysqladmin= /usr/bin/mysqladmin

user= root　　　　#用于登陆和关闭此服务password= 123456　　　#同上

[mysqld3307]

socket= /tmp/mysql_3307.sock

port= 3307pid-file = /var/lib/mysql_3307/3307.pid

datadir= /var/lib/mysql_3307/log= /var/lib/mysql_3307/3307.log

character-set-server =utf8

user=mysql

[mysqld3308]

…

[mysqld3309]

…

5.启动/关闭你的多实例

mysqld_multi --defaults-extra-file=/usr/local/my_multi.cnf start 3307mysqld_multi--defaults-extra-file=/usr/local/my_multi.cnf stop 3307

6.检查是否启动成功

[root@test-206 ~]#netstat -ntlp

tcp 0 0 :::3306 :::* LISTEN 3919/mysqld

tcp0 0 :::3307 :::* LISTEN 15027/mysqld

设置新的密码

mysqladmin -uroot -S /tmp/mysql_3307.sock password 123456

如果不设置，默认为空

7.登入你的新实例

[root@test-206 ~]#mysql -uroot -S /tmp/mysql_3307.sock -p

Enter password:

Welcome to the MySQL monitor. Commandsendwith ; or \g.

Your MySQL connection id is1Server version:5.5.20-log Distributed by The IUS Community Project

Copyright (c)2000, 2011, 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'forhelp. Type '\c' to clear the current input statement.

mysql>

再次确认下，你的这个实例，是不是用的mysql_3307这个文件夹的数据

mysql> show variables like '%datadir%';

#查用户

mysql> select user,host from mysql.user;

#给root用户设置密码，不然远程登陆会报：error.1045

选择数据库：

mysql> use mysql

设置密码：

mysql> UPDATE user SET Password=PASSWORD('XXX')where USER='root';

刷新MySQL的系统权限相关表：

mysql> flush privileges;

#如果报error.1130

mysql> update `user` set `host` = '%' where `user` ='root';

mysql> flush privileges;

#如果报error.1149

mysql >grant all privileges on *.* to root@"%" identified by "Passwd"mysql>flush privileges;

#关闭权限校验登录

mysqld_safe --socket=/tmp/mysql_3307.sock --skip-grant-table

mysqld_multi--defaults-extra-file=/usr/local/my_multi.cnf start 3307