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

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

[Client]

Port = 3307

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

Default-character-set=utf8

[Mysqld]

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/mysql.pid

Character-set-server=utf8

Lower_case_table_names=1

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:

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

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