MySQL Multi-instance installation

Installing MySQL-5.5.49

Getting the installation package: http://mirrors.sohu.com/mysql/

# install MySQL requires a dependency package
yum install ncurses-devel libaio-devel-y
rpm-qa ncurses-devel libaio-devel
# Install compile MySQL required software
yum install cmake-y
rpm-qa cmake
# Create user
useradd mysql-u 777-s/sbin/nologin-m
ID M Ysql

Upload MySQL package, unzip

Mkdir-p/home/oldboy/tools
cd/home/oldboy/tools
rz-y #mysql -5.5.49.tar.gz
ll-sh
Tar XF mysql-5.5.49.tar.gz
CD mysql-5.5.49

Compiling the installation

CMake. -dcmake_install_prefix=/application/mysql-5.5.49-dmysql_datadir=/application/mysql-5.5.49/data-dmysql_unix_ Addr=/application/mysql-5.5.49/tmp/mysql.sock-ddefault_charset=utf8-ddefault_collation=utf8_general_ci-dextra_ Charsets=gbk,gb2312,utf8,ascii-denabled_local_infile=on-dwith_innobase_storage_engine=1-dwith_federated_ Storage_engine=1-dwith_blackhole_storage_engine=1-dwithout_example_storage_engine=1-dwithout_partition_storage _engine=1-dwith_fast_mutexes=1-dwith_zlib=bundled-denabled_local_infile=1-dwith_readline=1-dwith_embedded_ Server=1-dwith_debug=0 make
&& make install && echo $?
Cd..

Create a soft link

Ln-s/application/mysql-5.5.49//application/mysql

Configuring multiple instances

Create a database file directory, configuration file

Mkdir-p/data/{3306,3307}/data
TOUCH/DATA/{3306,3307}/{MYSQL,MY.CNF}

tree/data/
/data/
├──3306#<== instance 3306
│├──data#<==3306 database file directory
│├──my.cnf#<==3306 configuration file 
   │└──mysql#<==3306 startup script
└──3307#<== instance 3307
├──data#<==3307 database file directory
├──my.cnf#<==330 7 configuration file
└──mysql#<==3307 startup script

Instance 3306 configuration file

vim /data/3306/my.cnf [Client] port             = 3306 Socket          = /data/3306/mysql.sock [
MySQL] no-auto-rehash [mysqld] User    = mysql port    = 3306 Socket  = /data/3306/mysql.sock Basedir = /application/mysql datadir = /data/ 3306/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_ errors = 3000 table_cache = 614 External-locking = false max_allowed_packet = 8M sort_buffer_size = 1m join_buffer_size = 1m thread_cache_size = 100 thread_ Concurrency = 2 query_cache_size = 2m query_cache_limit = 1m Query_cache_min_res_ unit = 2k #default_table_type  = innodb thread_stack = 192k #transaction_isolation  = read-committed tmp_table_size = 2m max_heap_table_size = 2m long_query_time  = 1 #log_long_format #log-error = /data/3306/error.log #log-slow-queries = /data/3306/ Slow.log pid-file = /data/3306/mysql.pid Log-bin = /data/3306/mysql-bin relay-log =  /data/3306/relay-bin Relay-log-info-file = /data/3306/relay-log.info binlog_cache_size =  1m max_binlog_cache_size = 1m max_binlog_size = 2m expire_logs_days = 7 Key_ buffer_size = 16m read_buffer_size = 1m read_rnd_buffer_size = 1m Bulk_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_recover lower_case_
Table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 Replicate-ignore-db=mysql Server-id = 1 innodb_additional_mem_pool_size = 4m innodb_buffer_pool_size = 32m innodb_ Data_file_path = ibdata1:128m:autoextend innodb_file_io_threads = 4 Innodb_thread_
Concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2m innodb_log_file_size = 4m innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct =  90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] Quick max_allowed _packet = 2m [Mysqld_safe] Log-error=/data/3306/mysql_oldboy3306.err pid-file=/data/3306/mysqld.pid

Instance 3307 configuration file

vim /data/3307/my.cnf [Client] port             = 3307 Socket          = /data/3307/mysql.sock [
MySQL] no-auto-rehash [mysqld] User    = mysql port    = 3307 Socket  = /data/3307/mysql.sock Basedir = /application/mysql datadir = /data/ 3307/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_ errors = 3000 table_cache = 614 External-locking = false max_allowed_packet = 8M sort_buffer_size = 1m join_buffer_size = 1m thread_cache_size = 100 thread_ Concurrency = 2 query_cache_size = 2m query_cache_limit = 1m Query_cache_min_res_ unit = 2k #default_table_type  = innodb thread_stack = 192k #transaction_isolation  = read-committed tmp_table_size = 2m max_heap_table_size = 2m #long_query_time  = 1 #log_long_format #log-error = /data/3307/error.log #log-slow-queries = /data/ 3307/slow.log pid-file = /data/3307/mysql.pid #log-bin = /data/3307/mysql-bin relay-log  = /data/3307/relay-bin Relay-log-info-file = /data/3307/relay-log.info binlog_cache_size =  1m max_binlog_cache_size = 1m max_binlog_size = 2m expire_logs_days = 7 Key_ buffer_size = 16m read_buffer_size = 1m read_rnd_buffer_size = 1m Bulk_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_recover lower_case_ Table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 Replicate-ignore-db=mysql ServEr-id = 3 innodb_additional_mem_pool_size = 4m innodb_buffer_pool_size = 32m InnoDB _data_file_path = ibdata1:128m:autoextend innodb_file_io_threads = 4 Innodb_thread_
Concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2m innodb_log_file_size = 4m innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct =  90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] Quick max_allowed _packet = 2m [Mysqld_safe] Log-error=/data/3307/mysql_oldboy3307.err pid-file=/data/3307/mysqld.pid

Instance 3306 startup script

Vim /data/3306/mysql #!/bin/sh ################################################ #this  scripts is  created by oldboy at 2007-06-09 #oldboy  qq:31333741 #site:/http www.etiantian.org #blog: http://oldboy.blog.51cto.com #oldboy  trainning qq group: 208160987  226199307  44246017 ################################################ #init port=3306 mysql_user= " Root "mysql_pwd=" Oldboy "cmdpath="/application/mysql/bin "mysql_sock="/data/${port}/mysql.sock "#startup  
function Function_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  function Function_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  function Function_restart_mysql () {    printf  "restarting mysql...\n"      function_stop_mysql     sleep 2     function_start_mysql} case
 $1 in start)     function_start_mysql;;
Stop)     function_stop_mysql;;
restart)     function_restart_mysql;; *)     printf  "usage: /data/${port}/mysql {start|stop|restart}\n" Esac

Instance 3307 startup script

Vim /data/3307/mysql #!/bin/sh ################################################ #this  scripts is  created by oldboy at 2007-06-09 #oldboy  qq:31333741 #site:/http www.etiantian.org #blog: http://oldboy.blog.51cto.com #oldboy  trainning qq group: 208160987  226199307  44246017 ################################################ #init port=3307 mysql_user= " Root "mysql_pwd=" Oldboy "cmdpath="/application/mysql/bin "mysql_sock="/data/${port}/mysql.sock "#startup  
function Function_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  function Function_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  function Function_restart_mysql () {    printf  "restarting mysql...\n"      function_stop_mysql     sleep 2     function_start_mysql} case
 $1 in start)     function_start_mysql;;
Stop)     function_stop_mysql;;
restart)     function_restart_mysql;; *)     printf  "usage: /data/${port}/mysql {start|stop|restart}\n" Esac

To modify permissions for a startup script

Find/data-type f-name "MySQL" |xargs chmod 700

Authorizing MySQL users to manage the/data directory

Chown-r Mysql.mysql/data

The primary database

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

start multi-instance, join the boot self-booting

/data/3306/mysql start
/data/3307/mysql start
netstat-tunlp |grep 330

# the-e parameter of the echo command handles special characters; \ n wraps and the cursor moves to the beginning of the line
echo-e "\n# #mysql multi instance\n/data/3306/mysql start\n/data/3307/mysql start ">>/etc/rc.local

Tail-3/etc/rc.local
# #mysql Multi instance
/data/3306/mysql start
/data/3307/mysql start

Setting environment variables

Method One: Put the relevant MySQL commands into the environment variables are already in the directory (recommended!! ）

cp/application/mysql/bin/*/usr/local/bin/

Method Two

Export path=/application/mysql-5.5.49/bin/: $PATH
echo ' export path=/application/mysql-5.5.49/bin/: $PATH ' > >/etc/profile
tail-1/etc/profile
source/etc/profile
Echo $PATH

Logging in and exiting multiple instances

Multi-Instance login (no password)

Mysql-s/data/3306/mysql.sock
mysql-s/data/3307/mysql.sock

Log out (4 types)

Mysql> exit
mysql> quit
mysql> Ctrl + C
mysql> Ctrl + D

Set Password

Mysqladmin-uroot password ' oldboy123 '-s/data/3306/mysql.sock
mysqladmin-uroot password ' oldboy123 '-s/data/3307 /mysql.sock

Change Password to: oldboy456

mysqladmin-uroot-poldboy123 password ' oldboy456 '-s/data/3306/mysql.sock

Multi-instance login (with password)

Mysql-uroot-poldboy123-s/data/3306/mysql.sock
mysql-uroot-poldboy123-s/data/3307/mysql.sock

Add one more instance 3308

Mkdir/data/3308/data-p
Cp/data/3306/{my.cnf,mysql}/data/3308/
sed-i ' s#3306#3308#g '/data/3308/{my.cnf, MySQL}
sed-i ' S#server-id = 1#server-id = 8#g '/data/3308/my.cnf
chown-r mysql.mysql/data/3308 chmod
700/d Ata/3308/mysql
cd/application/mysql-5.5.49/scripts/
./mysql_install_db--user=mysql--basedir=/ Application/mysql--datadir=/data/3308/data/
/data/3308/mysql start
echo "/data/3308/mysql start" >>/ Etc/rc.local
netstat-tunpl |grep
mysql-s/data/3308/mysql.sock mysqladmin-uroot password
' Oldboy123 '-s/data/3308/mysql.sock
mysql-uroot-poldboy123-s/data/3308/mysql.sock

MySQL Multi-instance optimization

Clean up useless MySQL library (3306)

Mysql-uroot-poldboy123-s/data/3306/mysql.sock
drop database test;
show databases;

Clean up useless MySQL users

Select User,host from Mysql.user;
Drop user "root" @ ":: 1";
Drop user "@" DB02 ";
drop User "root" @ "db02";
Drop user "@" localhost ";

Failure: Drop command cannot delete user

may be the result of uppercase and Linux hostnames, note: Delete the user as far as possible with the drop command, do not use the Delete command

Here's how to fix it:

Delete from Mysql.user where user= ' and host= ' S1 ';
Delete from mysql.user where user= ' root ' and host= ' S1 ';

Refresh Permissions

Refresh permissions to make changes take effect immediately

Flush privileges;

This article is from the "Chen was 007" blog, please be sure to keep this source http://chenfage.blog.51cto.com/8804946/1931931

MySQL Multi-instance installation