MySQL multi-instance installation configuration

MySQL multi-instance installation configuration

I. Basic CONCEPTS

MySQL multi-instance is to open a number of different service ports on a single machine (such as: 3306,3307,3308 ...), running multiple MySQL service processes, these service processes through different sockets to listen to different ports to provide services.

MySQL can share a set of security programs, using different MY.CNF configuration files, launching programs, data files.

is logically independent, but an overload of one instance can have an impact on others.

MySQL multi-instance function and problem :

1. Efficient Use of server resources

2. Conserve server resources

3. Resource preemption Issues

This instance consumes a lot of memory when a service is implemented and has a high or slow query.

Two. Common configuration scenarios for MySQL

1. Multiple configuration files, multiple launcher programs

2. Single configuration file deployment scenario

Three. Start the installation

1. Installing dependent Packages

Yum-y Install Ncurses-devel Libaio-devel

2. Kill the previous MySQL process

Pkill mysqld

Netstat-ntlp

Ps-ef | grep MySQL

Rm-f/etc/init.d/mysqld

3. Create a multi-instance directory structure

Mkdir-p/data/{3306,3307}/data

Tree/data

4. Create a multi-instance configuration file

Startup of multi-instance startup files MySQL Service essence:

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

Shutdown of multi-instance startup files MySQL Service essence

mysqladmin-uroot-p***-s/data/3306/mysql.sock shutdown

My.cnf

[client]port            = 3306socket           = /data/3306/mysql.sock[mysql]no-auto-rehash[mysqld ]user    = mysqlport    = 3306socket  = /data /3306/mysql.sockbasedir = /application/mysqldatadir = /data/3306/dataopen_files_limit     = 1024back_log = 600max_connections = 800max_connect_errors  = 3000table_cache = 614external-locking = falsemax_allowed_packet =8msort_ Buffer_size = 1mjoin_buffer_size = 1mthread_cache_size = 100thread_concurrency  = 2query_cache_size = 2mquery_cache_limit = 1mquery_cache_min_res_unit =  2k#default_table_type = InnoDBthread_stack = 192K#transaction_isolation =  Read-committedtmp_table_Size = 2mmax_heap_table_size = 2mlong_query_time = 1#log_long_format#log-error  = /data/3306/error.log#log-slow-queries = /data/3306/slow.logpid-file = /data/ 3306/mysql.pidlog-bin = /data/3306/mysql-binrelay-log = /data/3306/ Relay-binrelay-log-info-file = /data/3306/relay-log.infobinlog_cache_size = 1mmax_binlog_ cache_size = 1mmax_binlog_size = 2mexpire_logs_days = 7key_buffer_size =  16mread_buffer_size = 1mread_rnd_buffer_size = 1mbulk_insert_buffer_size =  1m#myisam_sort_buffer_size = 1m#myisam_max_sort_file_size = 10g#myisam_max_extra_ sort_file_size = 10g#myisam_repair_threads = 1#myisam_recoverlower_case_table_names =  1skip-name-resolveslave-skip-errors = 1032,1062replicate-ignore-db=mysqlserver-id =  1innodb_additional_mem_pool_size = 4miNnodb_buffer_pool_size = 32minnodb_data_file_path = ibdata1:128m:autoextendinnodb_file_io_ Threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb _log_buffer_size = 2minnodb_log_file_size = 4minnodb_log_files_in_group =  3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table =  0[mysqldump]quickmax_allowed_packet = 2m[mysqld_safe]log-error=/data/3306/mysql_ Qb3306.errpid-file=/data/3306/mysqld.pid

Configure the time need to pay attention to the port, as well as the last log need to know where, convenient troubleshooting; Then there is a "Server-id = 1" The best ID of each instance should not be the same.

MySQL (start the script to shut down the service)

#!/bin/sh#initport=3306mysql_user= "root" mysql_pwd= "qb123" cmdpath= "/application/mysql/bin" mysql_sock= "/data/${ Port}/mysql.sock "#startup  functionfunction_start_mysql () {    IF [ ! -E   "$mysql _sock"  ];then      printf  "starting mysql...\n"       /bin/sh ${cmdpath}/mysqld_safe --defaults-file=/data/${port}/ my.cnf 2>&1 > /dev/null &    else       printf  "mysql is running...\n"       exit     FI} #stop  functionfunction_stop_mysql () {    IF [ ! -E   "$mysql _sock"  ];then       printf  "mysql is  stopped...\n "       exit    else        printf " stoping mysql...\n "       ${cmdpath}/mysqladmin -u ${mysql_ USER} -P${MYSQL_PWD} -S /DATA/${PORT}/MYSQL.SOCK SHUTDOWN   FI} #restart  functionfunction_restart_mysql () {    printf  "restarting mysql...\n"      function_stop_mysql    sleep 2    function_ Start_mysql}case $1 instart)     function_start_mysql;; Stop)     function_stop_mysql;; Restart)     function_restart_mysql;; *)     printf  "usage: /data/${port}/mysql {start|stop|restart}\n" Esac

5. Authorization

Chown-r mysql.mysql/data/

find/data/-type f-name "MySQL" | Xargs ls-l

find/data/-type f-name "MySQL" | Xargs chmod +x

If you do not have to do the file can not find the problem, I hope to encounter the thought here.

6. Configure the global use path for MySQL commands

(PATH)

7. Initialize (each port is done, here do 3306)

CD mysql/scripts/

./mysql_install_db--basedir=/application/mysql--datadir=/data/3306/data/--user=mysql

(see two OK for initialization success)

(This is done to create a basic data file)

8. Start the service

/data/3306/mysql start

/data/3307/mysql start

Netstat-ntlup | grep 330

If the port is not up, here's how to troubleshoot it:

Wait a few 10 seconds, maybe it's slower.

Look at the log, Tail/data/3306/mysql_qb3306.err.

Look at the screen that executes the command returns output

View System log,/var/log/messages

Note: Because the script is not very perfect, the restart command is best

/data/3306/mysql stop

/data/3306/mysql start

9. Multi-Instance Login

Mysql-s/data/3306/mysql.sock

Mysql-s/data/3307/mysql.sock

Four. mysql Security configuration

1. Add a password for root

Mysqladmin-u root-s/data/3306/mysql.sock Password ' * * *

Note: In the/data/3306/under the MySQL also need to change to this password,

is mysqladmin not MySQL, need MySQL service up otherwise no Mysql.sock file

Because of the above reasons, because the password is in a file, you need to set the authorization, set only the root user operation;

find/data/-type f-name "MySQL"-exec chmod 700 {} \;

find/data/-type f-name "MySQL"-exec chown root.root {} \;

find/data/-type f-name "MySQL"-exec ls-l {} \;

And then

Pkill mysqld

/data/3306/mysql stop

/data/3307/mysql stop

/data/3307/mysql start

/data/3306/mysql start

Netstat-ntlup|grep 330

2. Query and clean up redundant users

(There are many operations when installing MySQL)

3. Login

mysql-uroot-p***-s/data/3306/mysql.sock

4. Change Password:

Login First

Mysql>alter user ' root ' @ ' localhost ' identified by ' new password ';

Five. Add a MySQL instance

Mkdir-p/data/3308/data

cp/data/3306/my.cnf/data/3308/

cp/data/3306/mysql/data/3308/

cd/data/3308

Ls

Chown-r mysql.mysql/data/3308

Vim MY.CNF

: g/3306/s//3308/g-----Change Port

and change the server_id.

Vim MySQL

Change port

cd/application/mysql/scripts/

./mysql_install_db--user=mysql--basedir=/application/mysql--datadir=/data/3308/data/

/data/3308/mysql start

Mysqladmin-u root-s/data/3308/mysql.sock Password ' * * *

mysql-uroot-p***-s/data/3308/mysql.sock

(The password inside the/data/3308/mysql file is also changed to the same)

find/data/-type f-name "MySQL"-exec chmod 700 {} \;

find/data/-type f-name "MySQL"-exec chown root.root {} \;

find/data/-type f-name "MySQL"-exec ls-l {} \;

This article from "11773640" blog, declined reprint!

MySQL multi-instance installation configuration