Mysql dba system learning (4) mysql multi-instance multi Start and Stop

Mysql multi-instance configuration and installation

I. Under what circumstances will we consider deploying multiple instances on a physical server? There are roughly the following situations:

1. The reason why the pseudo-distributed data architecture is adopted, and the project does not necessarily have a large number of users at the initial stage of startup. Therefore, a group of physical database servers should be established, but multiple instances should be deployed to facilitate subsequent migration;

2. To avoid the defect that mysql does not support the SMP architecture, the NUMA processor must support the multi-instance processor binding method, but most of the processors currently support this function !), Allocate different databases to different instances to provide data services;

3. A physical database server supports data services of multiple databases. To improve the recovery efficiency of mysql replication slave machines, multiple instances are deployed;

The dual-master replication mysql database server architecture requires that some important business data be backed up in a hot backup in a remote data center. mysql replication does not support multi-master replication, if you do not provide services to users, to effectively control costs, you may consider deploying a physical server with superior performance in a remote data center, or even multiple instances in addition to the Cabinet;

4. MMO/MMORPG in the traditional Game industry, and Web Game, each server corresponds to a database, but may do a lot of data query and Data Correction Work, to reduce the probability of failures during maintenance, you may also use multi-instance deployment to allocate databases by partition;

II. Introduction to multiple mysql instances

Mysqld_multi is designed to manage multiple mysqld service processes.

3. Configure mysqld and initialization for multiple instances

1. Create a data file directory for the instance

[Root @ test4 ~] # Cd/mysql/
[Root @ test4 mysql] # ls
Data
[Root @ test4 mysql] # mkdir data2/
[Root @ test4 mysql] # mkdir data3/

2. initialize multi-instance mysql
[Root @ test4 mysql] # mysql_install_db -- datadir =/mysql/data2/-- user = mysqlInstalling

MySQL system tables...
OK

[Root @ test4 mysql] # mysql_install_db -- datadir =/mysql/data3/-- user = mysqlInstalling MySQL

System tables...
OKFilling 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:
/Usr/local/mysql/bin/mysqladmin-u root password 'new-password'
/Usr/local/mysql/bin/mysqladmin-u root-h test4.wolf.org password 'new-password'
Alternatively you can run:
/Usr/local/mysql/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/usr/local/mysql;/usr/local/mysql/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
Cd/usr/local/mysql-test; perl mysql-test-run.pl
Please report any problems with the/usr/local/mysql/bin/mysqlbug script!

3. Configure the mysqld multi-instance my. cnf File

(1) ConfigurationMy. cnfDocument details

[root@test4 ~]# cat  /etc/my.cnf[mysqld_multi]mysqld =/usr/local/mysql/bin/mysqld_safemysqladmin =/usr/local/mysql/bin/mysqladminuser =testpassword =test[client]#password       = your_passwordport            = 3306socket          = /tmp/mysql.sock[mysqld3306]port            = 3306socket          = /tmp/mysql3306.sockskip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mlog-bin=mysql-binbinlog_format=mixeddatadir = /mysql/dataserver-id       = 1[mysqld3307]port            = 3307socket          = /tmp/mysql3307.sockskip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mlog-bin=mysql-binbinlog_format=mixeddatadir = /mysql/data2server-id       = 1[mysqld3308]port            = 3308socket          = /tmp/mysql3308.sockskip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mlog-bin=mysql-binbinlog_format=mixeddatadir = /mysql/data3server-id       = 1[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout

2) Start Multiple instances

[Root @ test4 ~] #/Usr/local/mysql/bin/mysqld_multi -- defaults-file =/etc/my. cnf start 3308

[Root @ test4 ~] # Ps aux | grep 3308
Root 4887 0.0 0.2 106012 1348 pts/1 S/bin/sh/usr/local/mysql/bin/mysqld_safe -- port = 3308 -- socket =/tmp/mysql3308.sock -- skip- external-locking -- key_buffer_size = 16 M -- buffers = 1 M -- table_open_cache = 64 -- sort_buffer_size = 512 K -- net_buffer_length = 8 K -- read_buffer_size = 256 K -- buffers = 512 K -- buffers = 8 M -- log-bin = mysql-bin -- binlog_format = mixed -- datadir =/mysql/data3 -- server-id = 1
Mysql 5034 0.0 1.3 125304 6668 pts/1 Sl/usr/local/mysql/libexec/mysqld -- basedir =/usr/local/mysql -- datadir =/mysql/data3 -- user = mysql -- skip-external-locking -- key_buffer_size = 16 M -- Memory = 1 M -- table_open_cache = 64 -- sort_buffer_size = 512 K -- net_buffer_length = 8 K -- read_buffer_size = 256 K -- Memory = 512 K -- myisam_sort_buffer_size = 8 M -- log-bin = mysql-bin -- binlog_format = mixed -- server-id = 1 -- log-error =/mysql/data3/test4.wolf.org. err -- pid-file =/mysql/data3/test4.wolf.org. pid -- socket =/tmp/mysql3308.sock -- port = 3308

3) connect to multiple mysql instances

[Root @ test4 ~] # Mysql-S/tmp/mysql3308.sock
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 1
Server version: 5.1.70-log Source distribution

Copyright (c) 2000,201 3, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be trademarks of their respective
Owners.

Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Test |
+ -------------------- +
3 rows in set (0.01 sec)
(4) Start and Stop multi-instance musql

Because mysql is managed by multiple instances, you cannot stop the database as follows

[Root @ test4 ~] #/Usr/local/mysql/bin/mysqld_multi -- defaults-file =/etc/my. cnf stop 3308

The real way to disable multi-instance MySQL is by configuring the test user in/etc/my. cnf,

[Root @ test4 ~] # Mysql-S/tmp/mysql3308.sock
Mysql> grant shutdown on *. * TO 'test' @ 'localhost' identified by 'test' with grant option;

Query OK, 0 rows affected (0.00 sec)

[Root @ test4 ~] # Mysqld_multi -- defaults-file =/etc/my. cnf stop 3306
[Root @ test4 ~] # Mysqld_multi -- defaults-file =/etc/my. cnf stop 3307
[Root @ test4 ~] # Mysqld_multi -- defaults-file =/etc/my. cnf stop 3308
[Root @ test4 ~] # Ps aux | grep mysql
Root 6148 0.0 0.1 103152 824 pts/1 S + grep mysql

This article from "Good to live" blog, please be sure to keep this source http://wolfword.blog.51cto.com/4892126/1241304