2--MARIADB Multi-instance installation of primary knowledge MARIADB

First, Background introduction

MARIADB Multi-instance is to open a number of different service ports on a single machine, running multiple MySQL service processes, through different sockets to listen to different service ports to provide their own, non-interference services, each instance can be isolated according to different configuration file settings

Second, the Operation procedure

1. Install mariadb (slightly)

2. Initialize the database (take 3306 as an example)

/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql--datadir=/multidata/3306--user=mysql

3. Modify the configuration file (3306 for example)

Server-id cannot be the same when there are more than one MARIADB server

4. Start the service (take 3306 as an example)

/usr/local/mysql/bin/mysqld_safe--defaults-file=/multidata/3306/my.cnf 2>&1 >/dev/null &

If you forget your password, the startup mode is:

/usr/local/mysql/bin/mysqld_safe--defaults-file=/multidata/3307/my.cnf--skip-grant-tables 2>&1 >/dev/ Null &

Note that the--defaults-file option should be placed before--skip-grant-tables

5. Login database (take 3306 for example)

/usr/local/mysql/bin/mysql-s/multidata/3306/mysql.sock

If you forget your password, skip starts with a blank password to log in:

Mysql-uroot-p-s/multidata/3307/mysql.sock

6. Set the database root account password (3306 for example)

/usr/local/mysql/bin/mysqladmin-uroot password ' 123456 '-s/multidata/3306/mysql.sock

If the password is modified, the command is:

/usr/local/mysql/bin/mysqladmin-uroot-p123456 password ' new '-s/multidata/3306/mysql.sock

7. Close the service (take 3306 as an example)

Mysqladmin-uroot-p123456-s/multidata/3306/mysql.sock shutdown

Third, write the startup script

To make it easy to start and close, you can write a script yourself and give execution permissions to replace the original mysqld execution file, the script content is as follows:

#!/bin/bash

. /etc/init.d/functions

Port=$2

User=root

passwd=123456

Mysqlbin= '/usr/local/mysql/bin '

Socketfile= "/multidata/${port}/mysql.sock"

Pidfile= "/multidata/${port}/mysql.pid"

mycnf= "/MULTIDATA/${PORT}/MY.CNF"

[[$#-eq 2] | | {

echo "Usage: $ {start|stop|restart|reload} {PORT}"

Exit 1

}

Mysql_start () {

[-E $SOCKETFILE "]] && {

Action "MySQL Port: $PORT is already running"/bin/false

Exit 0

} || {

Action "Starting MySQL ... please wait"/bin/true

$MYSQLBIN/mysqld_safe--defaults-file= $MYCNF &>/dev/null &

}

[["$?" = = "0"]] && {

Action "MySQL has been Started"/bin/true

} || {

Action "MySQL Started"/bin/false

}

}

Mysql_stop () {

[[!-E "$SOCKETFILE"]] && {

Action "MySQL Port: $PORT was already"/bin/false

} || {

$MYSQLBIN/mysqladmin-u $USER-p$passwd-s $SOCKETFILE shutdown &>/dev/null

}

[["$?" = = 0]] && {

Action "MySQL Port: $PORT has been Stopped"/bin/true

}

}

Case "$" in

' Start ')

Mysql_start

;;

' Stop ')

Mysql_stop

;;

' Restart ' | ' Reload ')

Mysql_stop

Sleep 3

Mysql_start

;;

*)

echo "Usage: $ {start|stop|restart|reload} {PORT}"

Esac

Note : This script has the same password as the database root account (2 server passwords), so it is recommended to change the permissions to 700 in the production environment to reduce the security risk

Supplemental NOTE 1: Multiple instances share the same my.cnf configuration file

In a production environment, it is recommended to separate the configuration files for multiple instances so that when the password is forgotten it is easy to specify the configuration file to retrieve via the Skip-grant-tables option. As another method of multi-instance, you can also use the same profile to centrally manage, the following on the previous basis to create a 3308,3309 instance for instructions, the steps are as follows:

1. Create a 3308,3309 Data directory

mkdir-pv/unidata/{3308,3309}

Chown-r Mysql.mysql/unidata

CP Mysqld_multi.server/etc/init.d/mysqld

CP MY-HUGE.CNF/ETC/MY.CNF

2. Initialize data (take 3308 for example)

/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql--datadir=/unidata/3308--user=mysql

3. Modify the/ETC/MY.CNF configuration file

[Mysqld_multi]

Mysqld=/usr/local/mysql/bin/mysqld_safe

Mysqladmin=/usr/local/mysql/bin/mysqladmin

Log =/unidata/mysql_multi.log

user = root

#password =

If the location is not a root user, you need to add shutdown permissions to the database for that user, or mysqld_multi will not be able to close the instance

[mysqld3308]

Socket=/unidata/3308/mysql.sock

port=3308

Server-id=1

Pid-file=/unidata/3308/mysql.pid

datadir=/unidata/3308

user=mysql# must specify 1 users or the instance will not start

[mysqld3309]

Socket=/unidata/3309/mysql.sock

port=3309

server-id=2

Pid-file=/unidata/3309/mysql.pid

datadir=/unidata/3309

User=mysql

Note: multiple instances of PID, port, socket, and server-id within each instance are not identical except for the mysqld number

4. Check whether the service starts and closes normally

Cp/usr/local/mysql/bin/mysqld_multi/etc/init.d/mysqld_multi

/etc/init.d/mysqld_multi Start 3308,3309

/etc/init.d/mysqld_multi Report

/etc/init.d/mysqld_multi Stop 3308,3309

Supplemental NOTE 2: View the database character set

MARIADB You can specify the default character set when compiling the installation, view the database character set command:

Show CREATE DATABASE MySQL \g

2--MARIADB Multi-instance installation of primary knowledge MARIADB