MySQL multi-instance configuration (1), mysql instance Configuration

MySQL multi-instance configuration (1), mysql instance Configuration

For centralized O & M of MySQL databases, you can deploy multiple MySQL instances on one MySQL database server. This function is implemented through mysqld_multi. Mysqld_multi is used to manage multiple mysqld service processes. These mysqld service process programs can use different sockets or listen to different ports, and distribute data files to different disks to distribute IO. Mysqld_multi provides simple commands for starting, disabling, and reporting the status of the managed server. This reduces the maintenance cost of the production environment, facilitates subsequent migration and cleaning, and improves the overall resource utilization of the server by binding multiple instances. There are two ways to configure multiple instances. One is to configure all instances in my. cnf, and the other is to use a configuration file for each instance. This article describes the first method.
For more information about how to configure multiple instances, see MySQL multi-instance configuration (2)
For installation of a single MySQL instance, see install the full version of MySQL source code in Linux.

1. Differences between multiple databases and Instances
MSSQL
An instance in MSSQL indicates that there is only one default instance on an SQL server. The default Instance name is ServerName (or IP ).
If you install SQL server on the same machine, you can name the instance as ServerName/InstanceName.
That is, multiple different instances can exist on an SQL server. A single instance can have multiple different databases.
You can use ServerName/InstanceName: PortNo to access databases under different instances. The default instance is ServerName: PortNo.
Configure IP addresses, related access protocols, and ports for different instances.
To enable the instance's accessibility, you must start the corresponding services of the instance. Note that the Instance name and service name of the instance are not the same.
The service name of the default instance is MSSQLSERVER, and the service name of the instance is MSSQL $ INSTANCE_NAME.

Oracle
An Oracle Server consists of an Oracle instance and an Oracle database. That is, Oracle Server = Oracle Instance + Oracle Database
Oracle instances are mainly composed of SGA, PGA, and a bunch of background processes, which are called instances.
A collection of physical files includes control files, data files, online log files, parameter files, and password files.
One instance can only access one database, and one database can be accessed by multiple instances.

MySQL
The concept of a MySQL instance is similar to that of MSSQL. A MySQL instance can have or access N databases.
Different instances can be distinguished by different port numbers, and the data of each instance can use different disk directories.
MySQL multi-instance management through mysqld_multi tool.

 

2. Existing Environment
Mysql installation path:/u01/app/mysql
Mysql DATA path:/u01/app/mysqldata/data3306
Mysql port: 3306
Mysql version: 5.6.12 Source distribution
OS environment: SUSE Linux Enterprise Server 11 SP3 (x86_64)

3. initialize the instance
# Create a data directory for the new instance and grant permissions
Suse11 :~ # Mkdir-p/u01/app/mysqldata/data3406
Suse11 :~ # Mkdir-p/u01/app/mysqldata/data3506
Suse11 :~ # Chown mysql: mysql-R/u01/app/mysqldata/data3406
Suse11 :~ # Chown mysql: mysql-R/u01/app/mysqldata/data3506

# Initializing an instance
Suse11 :~ # Cd/u01/app/mysql
Suse11:/u01/app/mysql #./scripts/mysql_install_db -- user = mysql -- ldata =/u01/app/mysqldata/data3406/
Suse11:/u01/app/mysql #./scripts/mysql_install_db -- user = mysql -- ldata =/u01/app/mysqldata/data3506/
# Author: Leshami
# Blog: http://blog.csdn.net/leshami

 

4. modify the configuration file
Suse11 :~ # More/etc/my. cnf # This configuration file only provides basic parameters for multiple instances. You can add the parameters in the production environment as needed.
[Mysqld_multi]
Mysqld =/u01/app/mysql/bin/mysqld_safe
Mysqladmin =/u01/app/mysql/bin/mysqladmin
User = admin # This account is used when multiple instances are closed. You must create and authorize the account on each instance.
Password = xxx # Easy to manage with a unified password

[Mysqld3306]
Socket =/tmp/mysql3306.sock
Port = 3306
Pid-file =/u01/app/mysqldata/data3306/mysql3306.pid
Datadir =/u01/app/mysqldata/data3306
Basedir =/u01/app/mysql
User = mysql
Servers-id = 3306

[Mysqld3406]
Socket =/tmp/mysql3406.sock
Port = 3406
Pid-file =/u01/app/mysqldata/data3406/mysql3406.pid
Datadir =/u01/app/mysqldata/data3406
Basedir =/u01/app/mysql
User = mysql
Servers-id = 3406

[Mysqld3506]
Socket =/tmp/mysql3506.sock
Port = 3506
Pid-file =/u01/app/mysqldata/data3506/mysql3506.pid
Datadir =/u01/app/mysqldata/data3506
Basedir =/u01/app/mysql
User = mysql
Servers-id = 3506

 

5. Start and close multiple instances
Suse11 :~ # Mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3406 is not running
MySQL server from group: mysqld3506 is not running

Suse11 :~ # Mysqld_multi start 3306
Suse11 :~ # Mysqld_multi start 3406,3506 # multiple instances can be started at the same time
Suse11 :~ # Netstat-ntlp | grep mysql
Tcp 0 0: 3306: * LISTEN 14786/mysqld
Tcp 0 0: 3406: * LISTEN 15103/mysqld
Tcp 0 0: 3506: * LISTEN 15371/mysqld

Suse11:/tmp # ls *. sock
Mysql3306.sock mysql3406.sock mysql3506.sock

# Changing passwords and creating accounts for new instances
Suse11:/tmp # mysql-uroot-pxxx-S./mysql3306.sock #3306 has an initial password
Root @ localhost [(none)]> grant shutdown on *. * to 'admin' @ 'localhost' identified by 'xxx' with grant option;

Suse11:/tmp # mysql-uroot-p-S./mysql3406.sock
Enter password: # The password is empty.
Root @ localhost [(none)]> set password for 'root' @ 'localhost' = password ('xxx ');

Root @ localhost [(none)]> grant shutdown on *. * to 'admin' @ 'localhost' identified by 'xxx' with grant option;

Suse11:/tmp # mysql-uroot-p-S./mysql3506.sock
Enter password: # The password is empty.
Root @ localhost [(none)]> set password for 'root' @ 'localhost' = password ('xxx ');

Root @ localhost [(none)]> grant shutdown on *. * to 'admin' @ 'localhost' identified by 'xxx' with grant option;

# TCP login Test
Suse11:/tmp # mysql-uroot-pxxx-P3506
Root @ localhost [(none)]>

# Check the status of multiple instances
Suse11:/tmp # mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3406 is running
MySQL server from group: mysqld3506 is running

# Stop a multi-instance Server
Suse11 :~ # Mysqld_multi stop 3306
Suse11 :~ # Mysqld_multi report 3306
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
Suse11 :~ # Mysqld_multi stop 3406
Suse11 :~ # Mysqld_multi stop 3506

# Logs of multiple instance servers
Suse11 :~ # Tail/u01/app/mysql/share/mysqld_multi.log
Stopping MySQL servers

Warning: Using a password on the command line interface can be insecure.
141017 23:40:09 mysqld_safe mysqld from pid file/u01/app/mysqldata/data3406/mysql3406.pid ended
Mysqld_multi log file version 2.16; run: Fri Oct 17 23:40:09 2014

Stopping MySQL servers

Warning: Using a password on the command line interface can be insecure.
141017 23:40:11 mysqld_safe mysqld from pid file/u01/app/mysqldata/data3506/mysql3506.pid ended

# Start Multiple instances at a time
Suse11 :~ # Mysqld_multi start 3306-3506
Suse11 :~ # Mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3406 is running
MySQL server from group: mysqld3506 is running

 

6. Configure Automatic startup for multiple instances
# Echo "/u01/app/mysql/bin/mysqld_multi start 3306-3506">/etc/init. d/boot. local

How many mycnf configuration files does mysql need to create multiple instances?

The configuration file is still one, but the mysql database stores multiple directories, which configure the database storage directory based on your instance.
Configure multiple instances:
Initialize Database
[Mysql @ localhost ~] $ Mkdir/mydata/mysql_db/data_1001 # create a database storage directory
[Mysql @ localhost ~] $ Mkdir/mydata/mysql_db/data_1002
[Mysql @ localhost ~] $ Mkdir/mydata/mysql_db/data_1003
[Mysql @ localhost mysql_db] $./bin/mysql_install_db-basedir =/mydata/mysql_db-datadir =/mydata/mysql_db/data_1001-user = mysql # Database Initialization
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
Support-files/mysql. server to the right place for your system

Please remember to set a password for the MySQL root USER!
To do so, start the server, then issue the following commands:

/Mydata/mysql_db/bin/mysqladmin-u root password 'new-password'
/Mydata/mysql_db/bin/mysqladmin-u root-h localhost password 'new-password'

Alternatively you can run:
/Mydata/mysql_db/bin/mysql_secure_installation

Which will also give you the option of removing the test
Databases and anonymous user created by default. This is
Stronugly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon:
Cd/mydata/mysql_db;/mydata/mysql_db/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
Cd/mydata/mysql_db/mysql-test; perl mysql-test-run.pl

Please report any problems with the/mydata/mysql_db & ...... the remaining full text>
 
Help: mysql multiple port configurations/Multi-instance installation

Configure my. cnf for linux

[Mysqld_multi]
Mysqld =/usr/local/mysql/bin/mysqld_safe

[Client]
Socket =/tmp/mysql. sock
Default-character-set = utf8

[Mysqld1]
Datadir =/usr/local/mysql/data
Basedir =/usr/local/mysql
Character-set-server = utf8
Default-storage-engine = innodb
Port = 3306
Table_open_cache = 64
Innodb_data_home_dir =/usr/local/mysql/data
Innodb_data_file_path = ibdata1: 10 M: autoextend
Innodb_log_group_home_dir =/usr/local/mysql/data
Innodb_buffer_pool_size = 50 M
Innodb_additional_mem_pool_size = 2 M
Innodb_log_file_size = 5 M
Innodb_log_buffer_size = 8 M
Innodb_flush_log_at_trx_commit = 1
Innodb_lock_wait_timeout = 50

[Mysqld2]
Datadir =/usr/local/mysql/data1
Basedir =/usr/local/mysql
Character-set-server = utf8
Default-storage-engine = innodb
Port = 3307
Table_open_cache = 64
Innodb_data_home_dir =/usr/local/mysql/data1
Innodb_data_file_path = ibdata1: 10 M: autoextend
Innodb_log_group_home_dir =/usr/local/mysql/data1
Innodb_buffer_pool_size = 50 M
Innodb_additional_mem_pool_size = 2 M
Innodb_log_file_size = 5 M
Innodb_log_buffer_size = 8 M
Innodb_flush_log_at_trx_commit = 1
Innodb_lock_wait_timeout = 50

---- The configuration in [mysqld] is consistent with the mysqld configuration for a single instance. Each [mysqld] in multiple instances must have a different port number, sock, datadir

Then start:/usr/local/mysql/bin/mysqld_muults -- defaults-extra-file =/etc/my. cnf start 1

---- The number 1 here corresponds to [mysqld1]. If it is disabled, it means to change start to the full text...>