分享程序员开发的那些事...
更新时间：2022-08-22 09:50:44
mysql多实例是在一台服务器安装mysql数据库软件,配置不同的监听端口,应用程序可以根据端口连接不同的数据库,库与库互不影响.
1.下载和安装mysql数据库
[root@node3 ~]# wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
[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]#