CentOS6.8 x86_64bit mysql-5.5.57 Multi-instance installation

MySQL Multi-instance installation
Note: Pre-download Save mysql-5.5.57 package with CMake compile MySQL tool
Ll/home/rich/tools
Mysql-5.5.57.tar.gz cmake-2.8.8.tar.gz

1. Install dependent environment and CMake compilation software
Yum install-y ncurses-devel Libaio-devel
Mkdir/home/rich/tools-p
Rz-y cmake-2.8.8.tar.gz mysql-5.5.57.tar.gz
Tar XF cmake-2.8.8.tar.gz
CD cmake-2.8.8
./configure
Gmake
Gmake Install

2. Add users who manage MySQL and go to the package directory to extract the MySQL package
Useradd mysql-s/sbin/nologin-m
Cd/home/rich/tools
Tar zxf mysql-5.5.57.tar.gz
CD mysql-5.5.57

3. Compiling MySQL with CMake
CMake. -dcmake_install_prefix=/application/mysql-5.5.57 \
-dmysql_datadir=/application/mysql-5.5.57/data \
-dmysql_unix_addr=/application/mysql-5.5.57/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

4. Create a soft link to the directory after installation is complete
Ln-s/application/mysql-5.5.57//application/mysql/

5. Create a multi-instance directory 3306 and 3307, named with the port number
Mkdir-p/data/{3306,3307}/data
cd/data/3306

6. Go to 3306 This instance of the directory under the edit instance of the boot profile my.cnf,3307 instance of the configuration file will only change 3306 to 3307, note that the server ID cannot be the same
VI 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
Thread_stack = 192K
Tmp_table_size = 2M
Max_heap_table_size = 2M
Long_query_time = 1

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

Lower_case_table_names = 1
Skip-name-resolve
Slave-skip-errors = 1032,1062
Replicate-ignore-db=mysql

Server-id = 1//id cannot be the same as the actual instance

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_rich3306.err
Pid-file=/data/3306/mysqld.pid

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

7. Write a multi-instance startup script MySQL, instance 3307 of the startup script as long as the modification 3306 is 3307
VI MySQL
##################################################
#!/bin/sh
#init
port=3306
Mysql_user= "Root"
Mysql_pwd= "123456"
Cmdpath= "/application/mysql/bin"
mysql_sock= "/data/${port}/mysql.sock"

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
}

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
}

Function_restart_mysql ()
{
printf "Restarting mysql...\n"
Function_stop_mysql
Sleep 2
Function_start_mysql
}

Case $ in
Start
Function_start_mysql
;;
Stop
Function_stop_mysql
;;
Restart
Function_restart_mysql
;;
*)
printf "Usage:/data/${port}/mysql {start|stop|restart}\n"
Esac

8. Modify multi-instance directory the owner of the data is MySQL, the permission to modify the startup script is 700
Chown-r Mysql.mysql/data
Find/data-type f-name "MySQL" |xargs chmod 700

9. Create a system environment variable when MySQL starts
Echo ' Export path=/application/mysql/bin: $PATH ' >>/etc/profile
Source/etc/profile

10. Start initializing the database when the installation is complete
cd/application/mysql/scripts/

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

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

11. Start the database
[[email protected] scripts]#/data/3306/mysql start
Starting MySQL ...

12. How to report errors that do not exist in the error log, you can manually create it yourself
[Email protected] scripts]# 171128 16:11:58 mysqld_safe error:log-error set to '/data/3306/mysql_rich3306.err ', however File don ' t exists. Create writable for user ' MySQL '.
Solution: cd/data/3306/
Touch Mysql_rich3306.err
cd/data/3307
Touch Mysql_rich3307.err
###################################################################

12. In the secondary startup database
/data/3307/mysql start

13. Check if the service was successful after startup
Ss-lntup|egrep "3306|3307"

14. Command line start multi-instance test
Mysqladmin-uroot Password 123456-s/data/3306/mysql.sock
Mysqladmin-uroot Password 123456-s/data/3307/mysql.sock

CentOS6.8 x86_64bit mysql-5.5.57 Multi-instance installation