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

MySQL multi-instance configuration (2), 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. you can configure multiple instances in either of the following ways. cnf provides configuration for all instances. One is to use a configuration file for each instance. This document describes the second method.
For the first multi-instance configuration method, see MySQL multi-instance configuration (1)
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/data3606
Suse11 :~ # Mkdir-p/u01/app/mysqldata/data3706
Suse11 :~ # Chown mysql: mysql-R/u01/app/mysqldata/data3606
Suse11 :~ # Chown mysql: mysql-R/u01/app/mysqldata/data3706

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

 

4. Configure the my. cnf file for each instance
# Vi/u01/app/mysqldata/data3606/my3606.cnf

[Mysqld]
Socket =/tmp/mysql3606.sock
Port = 3606
Pid-file =/u01/app/mysqldata/data3606/mysql3606.pid
Datadir =/u01/app/mysqldata/data3606
Basedir =/u01/app/mysql
User = mysql
Servers-id = 3606
[Client]
Port = 3606
Socket =/tmp/mysql3606.sock

[Mysql]
No-auto-rehash
Socket =/tmp/mysql3606.sock
Prompt = \ u @ \ h [\ d]> \\

# Vi/u01/app/mysqldata/data3706/my3706.cnf

[Mysqld]
Socket =/tmp/mysql3706.sock
Port = 3706
Pid-file =/u01/app/mysqldata/data3706/mysql3706.pid
Datadir =/u01/app/mysqldata/data3706
Basedir =/u01/app/mysql
User = mysql
Servers-id = 3706
[Client]
Port = 3706
Socket =/tmp/mysql3706.sock

[Mysql]
No-auto-rehash
Socket =/tmp/mysql3706.sock
Prompt = \ u @ \ h [\ d]> \\

Suse11 :~ # Chown-R mysql: mysql/u01/app/mysqldata/data3606/my3606.cnf
Suse11 :~ # Chown-R mysql: mysql/u01/app/mysqldata/data3706/my3706.cnf

 

5. Start and close multiple instances
Suse11 :~ # Mysqld_safe -- defaults-file =/u01/app/mysqldata/data3606/my3606.cnf &
Suse11 :~ # Mysqld_safe -- defaults-file =/u01/app/mysqldata/data3706/my3706.cnf &
Suse11 :~ # Netstat-nltp | grep mysql
Tcp 0 0: 3606: * LISTEN 64277/mysqld
Tcp 0 0: 3706: * LISTEN 64597/mysqld

Suse11 :~ # Mysqladmin-uroot password 'xxx'-S/tmp/mysql3606.sock
Suse11 :~ # Mysqladmin-uroot password 'xxx'-S/tmp/mysql3706.sock

# Connect to the instance using Socket
Suse11 :~ # Mysql-uroot-pxxx-S/tmp/mysql3606.sock
Root @ localhost [(none)]>

Suse11 :~ # Mysql-uroot-pxxx-S/tmp/mysql3706.sock
Root @ localhost [(none)]>

# Use TCP to connect to the instance
Suse11 :~ # Mysql-uroot-pxxx-P3606 -- protocol = tcp
Root @ localhost [(none)]> exit

Suse11 :~ # Mysql-uroot-pxxx-P3706 -- protocol = tcp
Root @ localhost [(none)]>

# Disabling a mysql instance
Suse11 :~ # Mysqladmin-uroot-pxxx-S/tmp/mysql3606.sock shutdown
Suse11 :~ # Mysqladmin-uroot-pxxx-S/tmp/mysql3706.sock shutdown

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...>

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>