mysql多实例配置使用方法

wfds

    mysql多实例是在一台服务器安装mysql数据库软件,配置不同的监听端口,应用程序可以根据端口连接不同的数据库,库与库互不影响.

1.下载和安装mysql数据库

[iyunv@node3 ~]# wget http://mirrors.sohu.com/mysql/My ... bc2.5-x86_64.tar.gz

[iyunv@node3 ~]# tar xvf mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz  -C /usr/local/
[iyunv@node3 ~]# cd /usr/local/
[iyunv@node3 local]# mv mysql-5.6.23-linux-glibc2.5-x86_64/ mysql-5.6.23
[iyunv@node3 local]# chown  -R root:mysql mysql-5.6.23/
[iyunv@node3 local]# chown  -R mysql:mysql mysql-5.6.23/data/
[iyunv@node3 local]# cd mysql-5.6.23/
[iyunv@node3 mysql-5.6.23]# ./scripts/mysql_install_db --user=mysql --group=mysql --basedir=/usr/local/mysql-5.6.23 --datadir=/usr/local/mysql-5.6.23/data
[iyunv@node3 mysql-5.6.23]# cp -a my.cnf  /etc/
[iyunv@node3 mysql-5.6.23]# cp -a support-files/mysql.server  /etc/init.d/mysqld
[iyunv@node3 mysql-5.6.23]# chkconfig --add mysql
[iyunv@node3 mysql-5.6.23]# chkconfig  mysqld on

2.修改配置文件
[iyunv@node3 mysql-5.6.23]# vim /etc/my.cnf     --其它的参数可以删除,如果需要加参数可以在各实例选项中添加

[mysqld_multi]
mysqld=/usr/local/mysql-5.6.23/bin/mysqld_safe
mysqladmin=/usr/local/mysql-5.6.23/bin/mysqladmin
log=/var/log/mysql.log

[mysqld1]
socket=/tmp/mysql1.sock
port=3306
server-id=1
pid-file=/data/mysql1.pid
datadir=/data1
user=mysql

[mysqld2]
socket=/tmp/mysql2.sock
port=3307
server-id=2
pid-file=/data/mysql2.pid
datadir=/data2
user=mysql

[mysqld3]
socket=/tmp/mysql3.sock
port=3308
server-id=3
pid-file=/data/mysql3.pid
datadir=/data3
user=mysql

[mysqld4]
socket=/tmp/mysql4.sock
port=3309
server-id=4
pid-file=/data/mysql4.pid
datadir=/data4
user=mysql
[iyunv@node3 mysql-5.6.23]#

3.创建数据目录和初使化数据库

[iyunv@node3 mysql-5.6.23]# mkdir /data{1,2,3,4}    --创建数据存放的文件夹
[iyunv@node3 mysql-5.6.23]# chown -R mysql:mysql /data{1,2,3,4}     --添加相应的权限

[iyunv@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/scripts/mysql_install_db  --user=mysql --group=mysql --basedir=/usr/local/mysql-5.6.23 --datadir=/data1 &   --初使化数据库

[iyunv@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/scripts/mysql_install_db  --user=mysql --group=mysql --basedir=/usr/local/mysql-5.6.23 --datadir=/data2 &
[iyunv@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/scripts/mysql_install_db  --user=mysql --group=mysql --basedir=/usr/local/mysql-5.6.23 --datadir=/data3 &
[iyunv@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/scripts/mysql_install_db  --user=mysql --group=mysql --basedir=/usr/local/mysql-5.6.23 --datadir=/data4 &
[iyunv@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/bin/mysqld_multi --defaults-extra-file=/etc/my.cnf start 1,2,3,4    --启动数据库实例
[iyunv@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/bin/mysqld_multi  report  --查看实例是否启动
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
[iyunv@node3 mysql-5.6.23]# netstat -antup | grep mysql
tcp        0      0 :::3307      :::*                        LISTEN      22774/mysqld     
tcp        0      0 :::3308      :::*                        LISTEN      22811/mysqld      
tcp        0      0 :::3309      :::*                        LISTEN      22801/mysqld      
tcp        0      0 :::3306      :::*                        LISTEN      22810/mysqld      
[iyunv@node3 mysql-5.6.23]#


4.登陆各实例,验证各实例是互相独立的
[iyunv@node3 mysql-5.6.23]# mysqladmin -u root password 'system' -S /tmp/mysql1.sock  --修改初使密码
Warning: Using a password on the command line interface can be insecure.
[iyunv@node3 mysql-5.6.23]# mysql -u root -p  -S /tmp/mysql1.sock   --登陆实例1
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> create database tong;
Query OK, 1 row affected (0.02 sec)

mysql> \u tong
Database changed
mysql> create table t(a int);
Query OK, 0 rows affected (0.29 sec)

mysql> insert into t values(1);
Query OK, 1 row affected (0.05 sec)

mysql> exit
Bye
[iyunv@node3 mysql-5.6.23]# mysql -u root -p  -S /tmp/mysql2.sock   --实例2不能用实例1的密码
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[iyunv@node3 mysql-5.6.23]# mysqladmin -u root password 'system2' -S /tmp/mysql2.sock
Warning: Using a password on the command line interface can be insecure.
[iyunv@node3 mysql-5.6.23]# mysql -u root -p  -S /tmp/mysql2.sock   --实例2可以登陆了
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> \u tong
ERROR 1049 (42000): Unknown database 'tong'
mysql> exit
Bye
[iyunv@node3 mysql-5.6.23]#

5.关闭单个实例
[iyunv@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/bin/mysqld_multi  stop 1-2
[iyunv@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/bin/mysqld_multi  report  
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
[iyunv@node3 mysql-5.6.23]#