MySQL 5.6对已有Mysql单实例的机器，再添加mysql数据库，实现单机多实例

wang_rx

　　一、需求：
　　对已有Mysql单实例的机器，再添加两个mysql数据库，实现单机多实例。
　　一个绑定在端口3306，另外两个绑定在端口3307，3308；
　　数据分别存放在/data/mysqldata、/data/mysqldata2、/data/mysqldata3
　　三个实例均采用InnoDB作为默认的存储引擎，字符编码采用UTF-8；
　　三个实例均采用相同的性能优化配置参数；
　　MySQL的源码安装请看我的另一篇博客http://yylinux.blog.51cto.com/8831641/1677165
　　二、创建支持多实例的配置文件
　　1、创建所需目录
　　单实例时存放数据目录/data/mysqldata已经存在
　　现在创建另外两个目录
　　[root@MySQL ~]# mkdir /data/mysqldata2 mysqldata3
　　[root@MySQL ~]# cd  /app/mysql/
　　[root@MySQL mysql]# ls
　　bin  binlogs  COPYING  data  docs  include  INSTALL-BINARY  lib  log  man  my.cnf  my-new.cnf  mysql-test  README  run  scripts  share  sql-bench  support-files  tmp
　　查看 log、binlogs、tmp、run 目录是否存在，如果不存在请创建
　　编辑my.cnf 配置文件
　　[root@MySQL ~]#vim /etc/my.cnf
　　-----
　　# For advice on how to change settings please see
　　# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
　　# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
　　# *** default location during install, and will be replaced if you
　　# *** upgrade to a newer version of MySQL.
　　[client]
　　default-character-set = utf8
　　[mysqld_multi]
　　mysqld = /app/mysql/bin/mysqld_safe
　　mysqladmin = /app/mysql/bin/mysqladmin
　　log = /app/mysql/log/mysqld_multi.log
　　user = root
　　#password =
　　# This is the general purpose database.
　　# The locations are default.
　　# They are left in [mysqld] in case the server is started normally instead of by mysqld_multi.
　　[mysqld1]
　　socket = /app/mysql/run/mysqld.sock
　　port = 3306
　　pid-file = /app/mysql/run/mysqld.pid
　　datadir = /data/mysqldata
　　lc-messages-dir = /app/mysql/share/english
　　# These support master - master replication
　　#auto-increment-increment = 4
　　#auto-increment-offset = 1  # Since it is master 1
　　#log-bin = /app/mysql/binlogs/bin-log-mysqld1
　　#log-bin-index = /app/mysql/binlogs/bin-log-mysqld1.index
　　#binlog-do-db = # Leave this blank if you want to control it on slave
　　#max_binlog_size = 1024M
　　[mysqld2]
　　socket = /app/mysql/run/mysqld.sock2
　　port = 3307
　　pid-file = /app/mysql/run/mysqld.pid2
　　datadir = /data/mysqldata2
　　lc-messages-dir = /app/mysql/share/english
　　# These support master - master replication
　　#auto-increment-increment = 4
　　#auto-increment-offset = 1  # Since it is master 1
　　log-bin = /app/mysql/binlogs/bin-log-mysqld2
　　log-bin-index = /app/mysql/binlogs/bin-log-mysqld2.index
　　#binlog-do-db = # Leave this blank if you want to control it on slave
　　max_binlog_size = 1024M
　　[mysqld3]
　　socket = /app/mysql/run/mysqld.sock3
　　port = 3308
　　pid-file = /app/mysql/run/mysqld.pid3
　　datadir = /data/mysqldata3
　　lc-messages-dir = /app/mysql/share/english
　　# These support master - master replication
　　#auto-increment-increment = 4
　　#auto-increment-offset = 1  # Since it is master 1
　　log-bin = /app/mysql/binlogs/bin-log-mysqld3
　　log-bin-index = /app/mysql/binlogs/bin-log-mysqld3.index
　　#binlog-do-db = # Leave this blank if you want to control it on slave
　　max_binlog_size = 1024M
　　[mysqld]
　　basedir = /app/mysql
　　tmpdir = /app/mysql/tmp
　　socket = /app/mysql/run/mysqld.sock
　　port = 3306
　　pid-file = /app/mysql/run/mysqld.pid
　　datadir = /app/mysql/data
　　lc-messages-dir = /app/mysql/share/english
　　skip-external-locking
　　key_buffer_size = 16K
　　max_allowed_packet = 1M
　　table_open_cache = 4
　　sort_buffer_size = 64K
　　read_buffer_size = 256K
　　read_rnd_buffer_size = 256K
　　net_buffer_length = 2K
　　thread_stack = 128K
　　# Increase the max connections
　　max_connections = 2
　　# The expiration time for logs, including binlogs
　　expire_logs_days = 14
　　# Set the character as utf8
　　character-set-server = utf8
　　collation-server = utf8_unicode_ci
　　# This is usually only needed when setting up chained replication
　　#log-slave-updates
　　# Enable this to make replication more resilient against server crashes and restarts
　　# but can cause higher I/O on the server
　　#sync_binlog = 1

　　# The server>　　server-id = 1
　　# Set this to force MySQL to use a particular engine/table-type for new tables
　　# This setting can still be overridden by specifying the engine explicitly
　　# in the CREATE TABLE statement
　　default-storage-engine = INNODB
　　# Enable Per Table Data for InnoDB to shrink ibdata1
　　innodb_file_per_table = 1
　　# Uncomment the following if you are using InnoDB tables
　　#innodb_data_home_dir = /data/mysqldata
　　#innodb_data_file_path = ibdata1:10M:autoextend
　　#innodb_log_group_home_dir = /data/mysqldata
　　# You can set .._buffer_pool_size up to 50 - 80 % of RAM
　　# but beware of setting memory usage too high
　　innodb_buffer_pool_size = 16M
　　innodb_additional_mem_pool_size = 2M

　　# Set .._log_file_size to 25 % of buffer pool>　　innodb_log_file_size = 5M
　　innodb_log_buffer_size = 8M
　　innodb_flush_log_at_trx_commit = 1
　　innodb_lock_wait_timeout = 50
　　[mysqldump]
　　quick
　　max_allowed_packet = 16M
　　[mysql]
　　no-auto-rehash
　　[myisamchk]
　　key_buffer_size = 8M
　　sort_buffer_size = 8M
　　[mysqlhotcopy]
　　interactive-timeout
　　[mysql.server]
　　user = mysql
　　[mysqld_safe]
　　log-error = /app/mysql/log/mysqld.log
　　pid-file = /app/mysql/run/mysqld.pid
　　open-files-limit = 8192
　　------
　　三、初始化数据库
　　[root@MySQL ~]# cd /app/mysql/scripts/
　　[root@MySQL scripts]#./mysql_install_db --basedir=/app/mysql --user=mysql --datadir=/data/mysqldata2/
　　[root@MySQL scripts]#./mysql_install_db --basedir=/app/mysql --user=mysql --datadir=/data/mysqldata3/
　　四、创建mysqld_multi.server脚本
　　[root@MySQL ~]# cd /opt/mysql-5.6.23/support-files/
　　[root@MySQL support-files]# cp -p mysqld_multi.server /etc/init.d/mysqld_multid
　　编辑mysqld_multid脚本
　　[root@MySQL ~]# vim /etc/init.d/mysqld_multid
　　#!/bin/sh
　　#
　　# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.
　　# This script assumes that my.cnf file exists either in /etc/my.cnf or
　　# /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the
　　# mysqld_multi documentation for detailed instructions.
　　#
　　# This script can be used as /etc/init.d/mysql.server
　　#
　　# Comments to support chkconfig on RedHat Linux
　　# chkconfig: 2345 64 36

　　# description: A very fast and>　　#
　　# Version 1.0
　　#
　　basedir=/app/mysql
　　bindir=/app/mysql/bin
　　conf=/etc/my.cnf
　　export PATH=$PATH:$bindir
　　if test -x $bindir/mysqld_multi
　　then
　　mysqld_multi="$bindir/mysqld_multi";
　　else
　　echo "Can't execute $bindir/mysqld_multi from dir $basedir";
　　exit;
　　fi
　　case "$1" in
　　'start' )
　　"$mysqld_multi" --defaults-extra-file=$conf start $2
　　;;
　　'stop' )
　　"$mysqld_multi" --defaults-extra-file=$conf stop $2
　　;;
　　'report' )
　　"$mysqld_multi" --defaults-extra-file=$conf report $2
　　;;
　　'restart' )
　　"$mysqld_multi" --defaults-extra-file=$conf stop $2
　　"$mysqld_multi" --defaults-extra-file=$conf start $2
　　;;
　　*)
　　echo "Usage: $0 {start|stop|report|restart}" >&2
　　;;
　　esac
　　五、mysql实例管理
　　启动mysql实例
　　[root@MySQL ~]# /etc/init.d/mysqld_multid start 1,2,3
　　或
　　[root@MySQL ~]# /etc/init.d/mysqld_multid start 3306,3307,3308
　　[root@MySQL ~]# ps -ef | grep mysqld | grep -v grep
　　root      6528     1  0 17:44 pts/0    00:00:00 /bin/sh /app/mysql/bin/mysqld_safe --socket=/app/mysql/run/mysqld.sock --port=3306 --pid-file=/app/mysql/run/mysqld.pid --datadir=/data/mysqldata --lc-messages-dir=/app/mysql/share/english --socket=/app/mysql/run/mysqld.sock --port=3306 --pid-file=/app/mysql/run/mysqld.pid --datadir=/data/mysqldata --lc-messages-dir=/app/mysql/share/english
　　root      6534     1  0 17:44 pts/0    00:00:00 /bin/sh /app/mysql/bin/mysqld_safe --socket=/app/mysql/run/mysqld.sock2 --port=3307 --pid-file=/app/mysql/run/mysqld.pid2 --datadir=/data/mysqldata2 --lc-messages-dir=/app/mysql/share/english --log-bin=/app/mysql/binlogs/bin-log-mysqld2 --log-bin-index=/app/mysql/binlogs/bin-log-mysqld2.index --max_binlog_size=1024M --socket=/app/mysql/run/mysqld.sock2 --port=3307 --pid-file=/app/mysql/run/mysqld.pid2 --datadir=/data/mysqldata2 --lc-messages-dir=/app/mysql/share/english --log-bin=/app/mysql/binlogs/bin-log-mysqld2 --log-bin-index=/app/mysql/binlogs/bin-log-mysqld2.index --max_binlog_size=1024M
　　root      6544     1  0 17:44 pts/0    00:00:00 /bin/sh /app/mysql/bin/mysqld_safe --socket=/app/mysql/run/mysqld.sock3 --port=3308 --pid-file=/app/mysql/run/mysqld.pid3 --datadir=/data/mysqldata3 --lc-messages-dir=/app/mysql/share/english --log-bin=/app/mysql/binlogs/bin-log-mysqld3 --log-bin-index=/app/mysql/binlogs/bin-log-mysqld3.index --max_binlog_size=1024M --socket=/app/mysql/run/mysqld.sock3 --port=3308 --pid-file=/app/mysql/run/mysqld.pid3 --datadir=/data/mysqldata3 --lc-messages-dir=/app/mysql/share/english --log-bin=/app/mysql/binlogs/bin-log-mysqld3 --log-bin-index=/app/mysql/binlogs/bin-log-mysqld3.index --max_binlog_size=1024M
　　mysql     8326  6528  0 17:44 pts/0    00:00:00 /app/mysql/bin/mysqld --basedir=/app/mysql --datadir=/data/mysqldata --plugin-dir=/app/mysql/lib/plugin --user=mysql --lc-messages-dir=/app/mysql/share/english --lc-messages-dir=/app/mysql/share/english --log-error=/app/mysql/log/mysqld.log --open-files-limit=8192 --pid-file=/app/mysql/run/mysqld.pid --socket=/app/mysql/run/mysqld.sock --port=3306
　　mysql     8487  6534  0 17:44 pts/0    00:00:00 /app/mysql/bin/mysqld --basedir=/app/mysql --datadir=/data/mysqldata2 --plugin-dir=/app/mysql/lib/plugin --user=mysql --lc-messages-dir=/app/mysql/share/english --log-bin=/app/mysql/binlogs/bin-log-mysqld2 --log-bin-index=/app/mysql/binlogs/bin-log-mysqld2.index --max-binlog-size=1024M --lc-messages-dir=/app/mysql/share/english --log-bin=/app/mysql/binlogs/bin-log-mysqld2 --log-bin-index=/app/mysql/binlogs/bin-log-mysqld2.index --max-binlog-size=1024M --log-error=/app/mysql/log/mysqld.log --open-files-limit=8192 --pid-file=/app/mysql/run/mysqld.pid2 --socket=/app/mysql/run/mysqld.sock2 --port=3307
　　mysql     8506  6544  0 17:44 pts/0    00:00:00 /app/mysql/bin/mysqld --basedir=/app/mysql --datadir=/data/mysqldata3 --plugin-dir=/app/mysql/lib/plugin --user=mysql --lc-messages-dir=/app/mysql/share/english --log-bin=/app/mysql/binlogs/bin-log-mysqld3 --log-bin-index=/app/mysql/binlogs/bin-log-mysqld3.index --max-binlog-size=1024M --lc-messages-dir=/app/mysql/share/english --log-bin=/app/mysql/binlogs/bin-log-mysqld3 --log-bin-index=/app/mysql/binlogs/bin-log-mysqld3.index --max-binlog-size=1024M --log-error=/app/mysql/log/mysqld.log --open-files-limit=8192 --pid-file=/app/mysql/run/mysqld.pid3 --socket=/app/mysql/run/mysqld.sock3 --port=3308
　　关闭mysql实例
　　[root@MySQL ~]# /etc/init.d/mysqld_multid stop 1,2,3
　　或
　　[root@MySQL ~]# /etc/init.d/mysqld_multid stop 3306,3307,3308
　　六、修改实例密码
　　[root@MySQL ~]# /app/mysql/bin/mysqladmin -uroot -h127.0.0.1 -P3307 password '123456'
　　[root@MySQL ~]# /app/mysql/bin/mysqladmin -uroot -h127.0.0.1 -P3308 password '123456'
　　七、登录多实例
　　[root@MySQL ~]# mysql -uroot -p123456 -h127.0.0.1 -P3306  #登录时只是端口号不一样
　　Warning: Using a password on the command line interface can be insecure.
　　Welcome to the MySQL monitor.  Commands end with ; or \g.

　　Your MySQL connection>　　Server version: 5.6.23 Source distribution
　　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>
　　八、删除匿名连接的空密码帐号
　　注，分别登录实例[mysqld1]、[mysqld2]、[mysqld3]执行以下命令：
　　mysql>use mysql; #选择系统数据库mysql
　　mysql>select Host,User,Password from user; #查看所有用户
　　mysql>delete from user where password="";#删除无密码账户
　　mysql>flush privileges; #刷新权限
　　mysql>select Host,User,Password from user; #确认密码为空的用户是否已全部删除
　　mysql>exit;
　　配置mysql允许root远程登录                                  #登录

　　mysql> grant all privileges on *.* to root@'%'>　　mysql> flush privileges;
　　mysql> select User,Password,Host from user;