MySQL multi-instance application configuration and deployment guide

MySQL multi-instance application configuration and deployment guide

I. Functions of multiple Mysql instances

1. effectively use server resources
When the resources of a single server are surplus, the remaining resources can be fully utilized to provide more services.
2. Saving server resources
When the company's resources are tight, but the database needs to provide services independently, and master-slave synchronization and other technologies are required, it is best to use multiple instances.
3. Mutual resource Preemption
When a service instance has a high concurrency or slow query, the entire instance consumes more CPU, memory, and disk I/O resources, as a result, the quality of service provided by other instances on the server is greatly reduced, which is equivalent to a big house with multiple bedrooms. Everyone shares a bathroom, gets up for breakfast, and goes to the toilet by one person, which is used for a long time, others have to wait for the same reason.

II. Application scenarios of Mysql multi-instance

1. resource-intensive companies
The business volume is not big, you don't want to spend money, and there are many demands, so it is particularly suitable for multiple instances.
2. concurrent access to websites that are not particularly large
When the company's business volume is not suitable, the server resources are not fully utilized, there is a waste of time, you can use multiple instances
3. Baidu search engine uses multiple instances, and sina also uses multiple instances.
Objective: To save IDC space and make full use of resources

Sina configuration SATA 15000 to 4 disks for RAID 5 48 gb memory

Iii. MySQL multi-instance configuration Scheme
Solution 1: We recommend using this

The advantages of this method are simple logic, simple configuration, and low coupling.
The disadvantage is that it is not easy to manage.

Solution 2: A single deployment solution is not recommended, and the coupling is too high
[Mysqld_muti]
That is, a configuration file starts multiple instances.

4. Start configuring MySQL multi-instance

Environment Introduction:
Mysql version: 5.6.27
Operating System: CentOS 6.5
Mysql instances: 2
The ports used by the instance are 3306 and 3307 respectively.

Configure solution 1 first:

1. Download: Go to http://dev.mysql.com/downloads/mysql/

2. Extract
Tar xvf mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz

3. Move to/usr/local/mysql
 
Music mysql-5.6.27-linux-glibc2.5-x86_64 mysql

4. Install the dependent lib package:
Centos: yum install libaio-y

5. Configure users and directories
Shell> groupadd mysql
Shell> useradd-r-g mysql
Shell> cd/usr/local/mysql
Shell> chown-R mysql.
Shell> chgrp-R mysql.

6. Use the/data/directory as the total directory of MySQL multi-instance
Mkdir-p/data/{3306, 3307}/data

7. Start configuring the configuration file for multiple instances
Vim/data/3306/my. cnf

[Mysqld]
# General
Basedir =/usr/local/mysql
Data =/data/3306/data
Socket =/data/3306/mysql. sock
User = mysql
Port = 3306
Server_id = 1
Log-error =/data/3306/err. log
Pid =/data/3306/mysql. pid
# Binlog
Log-bin =/data/3306/mysql-bin
Sync-binlog = 1
# InnoDB
Innodb_flush_log_at_trx_commit
Innodb_support_xa = 1
# Other
Character_set_server = utf8
Default_storage_engine = InnoDB
Ft_min_word_len = 1
Open_files_limit = 65535
Auto-increment = 10
Auto-increment-offset = 1
Log_slave_updates = 1 allows the slave database to record the events it replays to its own binary log.
Read_only = 0: prevents any threads with no privileged permissions from modifying data.
Skip_slave_start
[Client]
Socket =/data/3306/mysql. sock
Port = 3306

Vim/data/3307/my. cnf

[Mysqld]
# General
Basedir =/usr/local/mysql
Data =/data/3307/data
Socket =/data/3307/mysql. sock
User = mysql
Port = 3307
Server_id = 1
Log-error =/data/3307/err. log
Pid =/data/3307/mysql. pid
# Binlog
Log-bin =/data/3307/mysql-bin
Sync-binlog = 1
# InnoDB
Innodb_flush_log_at_trx_commit
Innodb_support_xa = 1
# Other
Character_set_server = utf8
Default_storage_engine = InnoDB
Ft_min_word_len = 1
Open_files_limit = 65535
Auto-increment = 10
Auto-increment-offset = 1
Log_slave_updates = 1 allows the slave database to record the events it replays to its own binary log.
Read_only = 0: prevents any threads with no privileged permissions from modifying data.
Skip_slave_start
[Client]
Socket =/data/3307/mysql. sock
Port = 3307

8. Create a Startup File for multiple instances.
Put it under/data/3306 |/data/3307
Script omitted (mysqld)

The essence of MySQL Service Startup for multiple instance startup files:

Mysqld_safe -- defaults-file =/data/3306/my. cnf 2> & 1>/dev/null &
Mysqld_safe -- defaults-file =/data/3307/my. cnf 2> & 1>/dev/null &

Stop the MySQL service in the Multi-instance Startup File:
Mysqladmin-u root-p passwd-S/data/3306/mysql. sock shutdown
Mysqladmin-u root-p passwd-S/data/3306/mysql. sock shutdown smooth stop

9. Authorize MySQL users to manage the entire multi-instance directory and group

Chown-R mysql. mysql/data

10. Configure MySQL Global Environment Variables

Echo "export PATH = $ PATH:/usr/local/mysql/bin/">/etc/profile
Source/etc/profile

11. Start database Initialization

Cd/usr/local/mysql/scripts/
./Mysql_install_db -- user = mysql -- datadir =/data/3306/data/-- basedir =/usr/local/mysql/
./Mysql_install_db -- user = mysql -- datadir =/data/3307/data/-- basedir =/usr/local/mysql/

What initialize database?
A. The main purpose of initialization is to create basic database files, such as generating MySQL database tables.
B. After initialization, check the directory to see some table files.

12. Start | stop the MySQL Service
If there is a script, use the script to start
/Data/3306/mysql start
/Data/3307/mysql start

If there is no script, use start
Mysqld_safe -- defaults-file =/data/3306/my. cnf 2> & 1>/dev/null &

Mysqld_safe -- defaults-file =/data/3307/my. cnf 2> & 1>/dev/null &

If there is no script, use start to stop

Mysqladmin-u root-p passwd-S/data/3306/mysql. sock shutdown

Mysqladmin-u root-p passwd-S/data/3306/mysql. sock shutdown smooth stop |

13. Check
Login:
Mysql-S/data/3306/mysql. sock

-S/data/3306/mysql. sock is used locally. If you are remotely using a different port connection, you can perform the management operation. There is no difference between other and single-instance management!

######################################## ######

Next, let's take a look at the second method to implement multi-instance practices through mysqld_multi:

Here, mysql installation and database initialization are the same as the previous steps, so we will not repeat them here.

Configuration of mysqld_multi
Vim/etc/my. cnf

[Mysqld_multi]
Mysqld =/usr/local/mysql/bin/mysqld_safe
Mysqladmin =/usr/local/mysql/bin/mysqladmin
User = admin
Password = password
[Mysqld1]
# General
Basedir =/usr/local/mysql/
Datadir =/data/3306/data/
Socket =/data/3306/mysql. sock
User = mysql
Port = 3306
Server_id = 1
Log-error =/data/3306/
Pid =/data/3306/
# Binlog
Log-bin =/data/3306/mysql-bin
Sync-binlog = 1
# InnoDB
Innodb_flush_log_at_trx_commit
Innodb_support_xa = 1
# Other
Character_set_server = utf8
Default_storage_engine = InnoDB
Ft_min_word_len = 1
Open_files_limit = 65535
Auto-increment = 10
Auto-increment-offset = 1
Log_slave_updates = 1 allows the slave database to record the events it replays to its own binary log.
Read_only = 0: prevents any threads with no privileged permissions from modifying data.
Skip_slave_start
[Client]
Socket =/data/3306/mysql. sock
Port = 3306
[Mysqld2]
# General
Basedir =/usr/local/mysql/
Datadir =/data/3307/data/
Socket =/data/3307/mysql. sock
User = mysql
Port = 3307
Server_id = 2
Log-error =/data/3307/
Pid =/data/3307/
# Binlog
Log-bin =/data/3307/mysql-bin
Sync-binlog = 1
# InnoDB
Innodb_flush_log_at_trx_commit
Innodb_support_xa = 1
# Other
Character_set_server = utf8
Default_storage_engine = InnoDB
Ft_min_word_len = 1
Open_files_limit = 65535
Auto-increment = 10
Auto-increment-offset = 1
Log_slave_updates = 1 allows the slave database to record the events it replays to its own binary log.
Read_only = 0: prevents any threads with no privileged permissions from modifying data.
Skip_slave_start
[Client]
Socket =/data/3307/mysql. sock
Port = 3307
[Mysql]
No-auto-rehash
Prompt = \ u @ \ d \ R: \ m>
[Mysqld_safe]
Open-files-limit = 8192

Start mysqld_multi
/Usr/local/mysql/bin/mysqld_multi start 1
/Usr/local/mysql/bin/mysqld_multi start 2


Stop a MySQL instance
Mysqladmin-uroot-p-S/data/3306/mysql. sock shutdown
Mysqladmin-uroot-p-S/data/3307/mysql. sock shutdown

Change Original Password
Mysqladmin-uroot password '200'-S/data/123456/mysql. sock
Mysqladmin-uroot password '200'-S/data/123456/mysql. sock

Test Login
Mysql-uroot-p-S/data/3306/mysql. sock
Mysql-uroot-p-S/data/3307/mysql. sock

This article permanently updates the link address: