mysql多实例怎么验证_mysql多实例配置使用方法

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

1.下载和安装mysql数据库

[root@node3 ~]# tar xvf mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz  -C /usr/local/

[root@node3 ~]# cd /usr/local/

[root@node3 local]# mv mysql-5.6.23-linux-glibc2.5-x86_64/ mysql-5.6.23

[root@node3 local]# chown  -R root:mysql mysql-5.6.23/

[root@node3 local]# chown  -R mysql:mysql mysql-5.6.23/data/

[root@node3 local]# cd mysql-5.6.23/

[root@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

[root@node3 mysql-5.6.23]# cp -a my.cnf  /etc/

[root@node3 mysql-5.6.23]# cp -a support-files/mysql.server  /etc/init.d/mysqld

[root@node3 mysql-5.6.23]# chkconfig --add mysql

[root@node3 mysql-5.6.23]# chkconfig  mysqld on

2.修改配置文件

[root@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

[root@node3 mysql-5.6.23]#

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

[root@node3 mysql-5.6.23]# mkdir /data{1,2,3,4}    --创建数据存放的文件夹

[root@node3 mysql-5.6.23]# chown -R mysql:mysql /data{1,2,3,4}     --添加相应的权限

[root@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 &   --初使化数据库

[root@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 &

[root@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 &

[root@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 &

[root@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    --启动数据库实例

[root@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

[root@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

[root@node3 mysql-5.6.23]#

4.登陆各实例,验证各实例是互相独立的

[root@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.

[root@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

[root@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)

[root@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.

[root@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

[root@node3 mysql-5.6.23]#

5.关闭单个实例

[root@node3 mysql-5.6.23]# /usr/local/mysql-5.6.23/bin/mysqld_multi  stop 1-2

[root@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

[root@node3 mysql-5.6.23]#