MySQL Cluster tutorial (iii) MySQL multi-instance (multiple database) setup

Source: Internet
Author: User
Tags mysql command line

MySQL Cluster tutorial (iii)

MySQL multi-instance (multiple database) setup

Multi-Instance Overview:

MySQL multi-instance refers to the installation of MySQL, we can start a Linux server at the same time multiple MySQL database (instance), do not need to install multiple MySQL;

If there are more than one Linux server, then we need each server to install MySQL separately;

Start multiple MySQL databases (instances) on a single Linux server by configuring separate configuration files for each DB instance, i.e. each DB instance has its own separate configuration file;

Multi-instance configuration:

1, in the MySQL installation master directory created/data/3307,/data/3308/,/data/3309,/data/3310 four directories;

2. Perform the database initialization and execute the command in the MySQL/usr/local/mysql-5.7.18/bin directory:

./mysqld--initialize-insecure--basedir=/usr/local/mysql-5.7.18--datadir=/usr/local/mysql-5.7.18/data/3307-- User=mysql

./mysqld--initialize-insecure--basedir=/usr/local/mysql-5.7.18--datadir=/usr/local/mysql-5.7.18/data/3308-- User=mysql

./mysqld--initialize-insecure--basedir=/usr/local/mysql-5.7.18--datadir=/usr/local/mysql-5.7.18/data/3309-- User=mysql

./mysqld--initialize-insecure--basedir=/usr/local/mysql-5.7.18--datadir=/usr/local/mysql-5.7.18/data/3310-- User=mysql

Where initialize-insecure indicates that the MySQL database root user's random password is not generated, that is, the root password is empty;

3, in/data/3307,/data/3308/,/data/3309,/data/3310 four directories to create a my.cnf file;

4. Configure the my.cnf file for four MySQL database services

Here's an example: other 3308 3309 3310 only need to modify the port number


Port = 3307

Socket =/usr/local/mysql-5.7.18/data/3307/mysql.sock



Port = 3307

Socket =/usr/local/mysql-5.7.18/data/3307/mysql.sock

DataDir =/usr/local/mysql-5.7.18/data/3307

Log-error =/usr/local/mysql-5.7.18/data/3307/error.log

Pid-file =/usr/local/mysql-5.7.18/data/3307/



Autocommit = 1

Multi-instance Startup:

To switch to the/usr/local/mysql-5.7.18/bin directory, use the Msyqld_safe command to specify the configuration file and start the MySQL service:

./mysqld_safe--DEFAULTS-FILE=/USR/LOCAL/MYSQL-5.7.18/DATA/3307/MY.CNF &

./mysqld_safe--DEFAULTS-FILE=/USR/LOCAL/MYSQL-5.7.18/DATA/3308/MY.CNF &

./mysqld_safe--DEFAULTS-FILE=/USR/LOCAL/MYSQL-5.7.18/DATA/3309/MY.CNF &

./mysqld_safe--DEFAULTS-FILE=/USR/LOCAL/MYSQL-5.7.18/DATA/3310/MY.CNF &

Where--defaults-file is the specified configuration file,& conforms to the background boot;

Post-Boot configuration: (3308 3309 3310 Don't forget to change your password and remote permissions)

1. Log in to MySQL and execute the command in the Mysql-5.7.18/bin directory:


Where-P is the specified password, if no password can not write-p,-s is the specified sock file, Mysql.sock file is the server and the native Client to communicate the IP and port files, or use port, host login./mysql-uroot-p-p3307- h127.0.0.1 login into MySQL

2. Modify the MySQL password to perform:

Alter user ' root ' @ ' localhost ' identified by ' 123456 ';

(where 123456 is the password we set)

3. Authorized remote Access, execute command: (so remote client can access)

Grant all privileges on * * to [email protected] '% ' identified by ' 123456 ';

where * * The first * represents all database names, the second * represents all database tables;

[email protected] '% ' root indicates user name,% means IP address,% can also specify specific IP address, such as [email protected],[email protected]

4, perform the following command to refresh permissions:

Flush privileges;

Multi-instance shutdown:

Switch to the/usr/local/mysql-5.7.18/bin directory and use the mysqladmin command shutdown

./mysqladmin-uroot-p-s/usr/local/mysql-5.7.18/data/3307/mysql.sock Shutdown

Or:./mysqladmin-uroot-p-p3307-h127.0.0.1 shutdown

If you have entered the command line of MySQL, you can execute the shutdown directly;

Exit MySQL command line, execute: Exit

MySQL Cluster tutorial (iii) MySQL multi-instance (multiple database) setup

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.