Mysql 多实例详细配置教程

Mysql 多实例

1. 什么是多实例？

   就是一台机器上开启多个不同的服务端口，运行多个mysql服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供各自的服务
   这些mysql实例共用一套mysql安装程序，使用不同（也可以相同）的my.cnf配置文，启动程序，数据库文件，在提供服务时，多实例mysql在逻辑上看来是各自独立，

优缺点：
节约服务器资源
并发高或慢查询时，整个实例消耗更多的系统资源，影响其它实例

2. 配置mysql

   2.1. 建立数据目录
   mkdir -p /usr/local/mysql/var/{3306,3007}

   2.2 修改配置文件my.cnf
   注意：port、server-id不能重复

    	vim /usr/local/mysql/var/3306/my.cnf
   
    	[client]
    	port = 3306
    	socket = /usr/local/mysql/var/3306/mysql.sock
    	[mysqld]
    	datadir=/usr/local/mysql/var/3306/
    	port = 3306
    	socket =/usr/local/mysql/var/3306/mysql.sock
    	server-id = 1
    	skip-name-resolve
    	datadir=/data/mysql/3306/var/
    	relay-log=/data/mysql/3306/relay-bin
    	relay-log-info-file =/data/mysql/3306/relay-log.info
    	log-error=/data/mysql/3306/mysql.error
    	pid-file=/data/mysql/3306/mysql.pid
   

3. 初始化mysql

    	/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var/3306 --user=mysql
   

4. 启动mysql多实例

   4.1. 脚本启动方式：
   /usr/local/mysql/var/3306/mysqld start
   将下面命令的方式写成脚本，利用case做｛start|stop｝

    命令启动方式：
    mysqld_safe --defaults-file=/usr/local/mysql/3306/my.cnf
   
   
    4.2	创建mysql启动脚本
   
    		创建自动启动文件
    		vim /usr/local/mysql/var/3306/mysqld
    		3306的启动文件如下： 
   
    	#!/bin/bash
    	mysql_port=3306
    	mysql_username="admin"
    	mysql_password="password"
    	start_mysql()
    	{
    		echo "Starting MySQL...\n"
    		/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/dbdata_${mysql_port}/my.cnf 2>&1 > /dev/null &
    	}
    	stop_mysql()
    	{
    		echo "Stoping MySQL...\n"
    		/usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data/dbdata_${mysql_port}/mysql.sock shutdown
    	}
    	restart_mysql()
    	{
    		echo "Restarting MySQL...\n"
    		stop_mysql
    		start_mysql
    	}
    	kill_mysql()
    	{
    		kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{echo $2}')
    		kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{echo $2}')
    	}
   
    	case $1 in
    		start)
    			start_mysql
    			;;
    		stop)
    			stop_mysql
    			;;
    		kill)
    			kill_mysql
    			;;
    		restart)
    			function_stop_mysql
    			function_start_mysql
    			;;
    		*)
    			echo "Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill}"
    		;;
    	esac
    	给文件755执行权chmod 755 /usr/local/mysql/var/3306/mysqld
   

5. 停止mysql多实例

    root无密码
    mysqladmin -S /usr/local/mysql/3306/mysql.sock shutdown
   
    root有密码
    /usr/local/mysql/bin/mysqladmin -uroot -p123456 -s /usr/local/mysql/var/3306/mysql.sock shutdown
   

6.### 登录mysql多实例

		/usr/local/mysql/bin/mysql -S /usr/local/mysql/3306/mysql.sock
		mysql -uroot -p123456 -P 3306 -h 192.168.1.100

7. 修改mysql 多实例root密码

    	/usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /usr/local/mysql/var/3306/mysql.sock
   
   
    	登录测试并创建关闭mysql的帐号权限，mysqld脚本要用到！
    	/usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock
    	GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
    	flush privileges;
   
    	创建了admin帐号以后脚本的stop功能和restart功能就正常了！
   

8. 添加开机启动

    vi /etc/rc.local
    /usr/local/mysql/var/3306/mysqld start
    /usr/local/mysql/var/3307/mysqld start
    /usr/local/mysql/var/3308/mysqld start