MySQL 5.6 for existing MySQL Single instance of the machine, and then add MySQL database, the realization of single-machine multi-instance

First, demand:

For a machine that already has a single instance of MySQL, add two MySQL databases and implement multiple instances of a single computer.

One binding on port 3306, the other two bindings on port 3307,3308;

Data are stored in/data/mysqldata,/DATA/MYSQLDATA2,/DATA/MYSQLDATA3, respectively

Three examples are used InnoDB as the default storage engine, character encoding using UTF-8;

Three examples are all using the same performance optimization configuration parameters;

MySQL source installation Please see my other blog http://yylinux.blog.51cto.com/8831641/1677165

Ii. creating a configuration file that supports multiple instances

1. Create the desired directory

Single-Instance Storage data Directory/data/mysqldata already exists

Now create the other two directories

[Email protected] ~]# mkdir/data/mysqldata2 mysqldata3

[Email protected] ~]# cd/app/mysql/

[[email protected] mysql]# ls

Bin Binlogs COPYING data docs include install-binary lib log man my.cnf my-new.cnf mysql-test README Run SC Ripts Share Sql-bench support-files tmp

To see if the log, Binlogs, TMP, run directories exist, if they do not exist, create

Edit MY.CNF configuration file

[[Email protected] ~] #vim/etc/my.cnf

-----

# for advice The change settings

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

# * * * Don't EDIT this FILE. It ' s a template which'll be copied to the

# * * * default location during install, and would 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 is default.

# they is 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 was usually only needed if setting up chained replication

#log-slave-updates

# Enable replication more resilient against server crashes and restarts

# but can cause higher I/O on the server

#sync_binlog = 1

# The server ID, should is unique in same network

Server-id = 1

# Set this to force MySQL to use a particular engine/table-type for new tables

# This setting can still is 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 is 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% of buffer pool size

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

------

Third, initialize the database

[Email protected] ~]# cd/app/mysql/scripts/

[Email protected] scripts]#./mysql_install_db--basedir=/app/mysql--user=mysql--datadir=/data/mysqldata2/

[Email protected] scripts]#./mysql_install_db--basedir=/app/mysql--user=mysql--datadir=/data/mysqldata3/

Iv. Creating a Mysqld_multi.server script

[Email protected] ~]# cd/opt/mysql-5.6.23/support-files/

[Email protected] support-files]# cp-p Mysqld_multi.server/etc/init.d/mysqld_multid

Edit Mysqld_multid Script

[Email protected] ~]# 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 reliable SQL database engine.

#

# 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 "$" in

' Start ')

"$mysqld _multi"--defaults-extra-file= $conf start $

;;

' Stop ')

"$mysqld _multi"--defaults-extra-file= $conf stop $

;;

' Report ')

"$mysqld _multi"--defaults-extra-file= $conf Report

;;

' Restart ')

"$mysqld _multi"--defaults-extra-file= $conf stop $

"$mysqld _multi"--defaults-extra-file= $conf start $

;;

*)

echo "Usage: $ Start|stop|report|restart}" >&2

;;

Esac

V. MySQL instance management

Start MySQL Instance

[Email protected] ~]#/etc/init.d/mysqld_multid start

Or

[Email protected] ~]#/etc/init.d/mysqld_multid start 3306,3307,3308

[Email protected] ~]# Ps-ef | grep mysqld | Grep-v grep

Root      6528     1  0 17:44 pts/0    00:00:00/bin/sh/app/mysql/bin/mysqld_s Afe--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_s Afe--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_s Afe--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/mys QL--datadir=/data/mysqldata--plugin-dir=/app/mysql/lib/plugin--user=mysql--lc-messages-dir=/app/mysql/share/ 中文版--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/mys QL--datadir=/data/mysqldata2--plugin-dir=/app/mysql/lib/plugin--user=mysql--lc-messages-dir=/app/mysql/share/ 中文版--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--PL Ugin-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

Close MySQL Instance

[[email protected] ~]#/etc/init.d/mysqld_multid stop

Or

[Email protected] ~]#/etc/init.d/mysqld_multid stop 3306,3307,3308

Six, modify the instance password

[Email protected] ~]#/app/mysql/bin/mysqladmin-uroot-h127.0.0.1-p3307 password ' 123456 '

[Email protected] ~]#/app/mysql/bin/mysqladmin-uroot-h127.0.0.1-p3308 password ' 123456 '

Seven, login multi-instance

[Email protected] ~]# mysql-uroot-p123456-h127.0.0.1-p3306 #登录时只是端口号不一样

Warning:using a password on the command line interface can is insecure.

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 5

Server version:5.6.23 Source Distribution

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>

Eight, delete the anonymous connection empty password account

Note, each login instance [mysqld1], [mysqld2], [MYSQLD3] executes the following command:

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;

Configure MySQL to allow root telnet #登录

Mysql> Grant all privileges on * * to [e-mail protected] '% ' identified by "123456";

mysql> flush Privileges;

Mysql> select User,password,host from User;

MySQL 5.6 for existing MySQL Single instance of the machine, and then add MySQL database, the realization of single-machine multi-instance