MySQL multi-instance configuration, rights management

is to run multiple instances of MySQL on a single server, each with a separate port, and the configuration of the instance can be

to be set according to actual needs

1, close The original default port 3306 of the Mysql:service mysqd stop

2. Configure MySQL Multi-instance

# VIM/ETC/MY.CNF
[Mysqld_multi]
Mysqld =/usr/local/mysql/bin/mysqld_safe
Mysqladmin =/usr/local/mysql/bin/mysqladmin
user = MySQL
Log =/database/log/mysqld-muti.log

[Mysqld1]

user = MySQL

port = 3306
datadir =/database/data1
socket =/tmp/mysql1.sock
log_error =/database/data1/error.log
log_bin =/ Database/data1/mysqld1-bin
binlog_format = row
server_id = 1
pid_file =/database/data1/mysqld1.pid
expire_logs _days =

[Mysqld2]

user = MySQL

Port = 3307
DataDir =/database/data2
Socket =/tmp/mysql2.sock
Log_error =/database/data2/error.log
Log_bin =/database/data2/mysqld2-bin
Binlog_format = statement
server_id = 2
Expire_logs_days = 30

Pid_file =/database/data2/mysqld2.pid

[MYSQLD3]

user = M Ysql

Port = 3308
DataDir =/database/data3
socket =/tmp/mysql3.sock
log_error =/database/data3/error.log
Log_bin =/database/data3/mysqld3-bin
Binlog_format = Mixed
server_id = 3
expire_logs_days =
pid_file =/database/data3/mysqld3.pid


3.Initialize the database
the search path for the MY.CNF configuration file under Linux is:/etc/my.cnf./etc/mysql/my.cnf/usr/local/mysql/my.cnf,~/my.cnf #/scripts/mysql_install_db--user=mysql--datadir=/database/data1

#./scripts/mysql_install_db--user=mysql--datadir=/database/data2

#./scripts/mysql_install_db--user=mysql--datadir=/database/data3

4 , change environment variables
650) this.width=650; "src=" Http://note.youdao.com/yws/res/19816/D405729FF6EF4634A5347B50DD462ADB "alt=" Qq20150717113805.png "/>

before starting to ensure that PATH inside includes MySQL of the bin directory. You can write the following command to /etc/profile

export path= $PATH:/usr/local/mysql/bin

or: Because the source is installed, the commands used in MySQL will be under the bin in the installation directory. You need to add an environment variable before you can use it.
If you do not add to the environment variable, it is only available in the bin directory.
650) this.width=650; "src=" http://note.youdao.com/yws/res/19789/9A5FCCCEB18E4207BD79BA89E57D4630 "alt=" Q Q20150717105454.png "/>

5.Multi-instance management

(1 ) # Mysqld_multi Report

650) this.width=650; "src=" http://note.youdao.com/yws/res/19826/B3DA798C7EBA4303B98650A4A8C4E119 "alt=" Qq20150717113420.png "/>

If it cannot be started. Remember to check the error log
# cat/etc/my.cnf

650) this.width=650; "src=" http://note.youdao.com/yws/res/19808/C5151676DBB64C80A4A2DCADF5450E81 "alt=" Qq20150717121426.png "/>

650) this.width=650; "src=" http://note.youdao.com/yws/res/19810/47430D262554448AB9B9AE4518208353 "alt=" Qq20150717121439.png "/>

650) this.width=650; "src=" http://note.youdao.com/yws/res/19812/CA7E3A5642514B5EB95DA0BC1BD8E7F9 "alt=" Qq20150717121453.png "/>

You can get the directory in which the error log exists.

Then view the error log.

      

650) this.width=650; "src=" Http://note.youdao.com/yws/res/19803/CADB4A25FBDE4EA38C204C27BC77D97B "alt=" Qq20150717113420.png "/>

(2)# Mysqld_multi Start-up

(3)# mysqld_multi start 1-3 \ \ Start instance

(4) # netstat- NTLP \ \ Check whether it started successfully

(5 )# mysqld_multi--user=root stop 1-3 \ \ Stop instance

(6) # Mysqld_multi Report

6.connect to the specified server

# mysql-u Root-s/tmp/mysql1.sock
# mysql-u Root-h 127.0.0.1-p3308
 

This article is from the "Zhanglulu" blog, make sure to keep this source http://zhanglulu913.blog.51cto.com/10592685/1684573

MySQL multi-instance configuration, rights management