Shulou(Shulou.com)06/01 Report--

First, background introduction

MariaDB multi-instance is to open multiple different service ports on a machine and run multiple MySQL service processes. Different socket listens to different service ports to provide their own and non-interference services. Each instance can be isolated from each other according to different configuration files.

II. Operation steps

1. Install MariaDB (abbreviated)

two。 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 (take 3306 as an example)

Server-id cannot be the same when there are multiple MariaDB servers

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 method is:

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

5. Log in to the database (take 3306 as an example)

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

If you forget your password, log in with an empty password after skip starts:

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

6. Set the database root account password (take 3306 as an example)

If you are changing the password, the command is:

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

7. Shut down the service (take 3306 as an example)

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

Third, write startup scripts

To facilitate startup and shutdown, you can write a script and give the permission to execute instead of the original mysqld execution file, as shown below:

#! / 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: $0 {start | stop | restart | reload} {PORT}"

Exit 1

}

Mysql_start () {

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 down" / bin/false

} | | {

}

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

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

}

}

Case "$1" in

'start')

Mysql_start

'stop')

Mysql_stop

'restart' |' reload')

Mysql_stop

Sleep 3

Mysql_start

*)

Esac

Note: in this script, the password of the database root account is the same (the password of the two servers is the same), so it is recommended to change the permissions to 700in the production environment to reduce security risks.

Supplementary note 1: multiple instances share the same my.cnf configuration file

It is recommended to separate the configuration files of multiple instances in a production environment, so that if you forget your password, it is easy to specify that the configuration files can be retrieved through the skip-grant-tables option. As another method of multiple instances, you can also use the same configuration file to manage it centrally. The following steps are shown below: create an instance of 3308 and 3309 based on the previous example:

1. Create a data directory of 3308 and 3309

Mkdir-pv / unidata/ {3308pm 3309}

Chown-R mysql.mysql / unidata

Cp mysqld_multi.server / etc/init.d/mysqld

Cp my-huge.cnf / etc/my.cnf

two。 Initialization data (take 3308 as an example)

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

3. Modify / 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 place is not a root user, you need to add shutdown permissions for the user in the database, otherwise mysqld_multi will not be able to shut down 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 user, otherwise the instance cannot be started

[mysqld3309]

Socket=/unidata/3309/mysql.sock

Port=3309

Server-id=2

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

Datadir=/unidata/3309

User=mysql

Note: except for the different mysqld numbers, the pid, port, socket and server-id of multiple instances cannot be the same.

4. Check whether the service can be started and shut down properly

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

Supplementary note 2: view database character set

MariaDB can specify the default character set when compiling and installing, and view the database character set command:

Show create database mysql\ G

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.