msyql5.6 dual MySQL installation and simple optimization

Precautions:
As long as the following 4 points, 2 MySQL can be installed simultaneously to a machine PS: Can extrapolate, a machine as long as you load enough, want to install a few can. Here are 2 examples:

My test server is configured to 2 6-core CPU 48g memory 4t hard disk, after installing the master-slave test 10w concurrency is basically the upper limit.
2 MySQL installed to a server
1.socket files are different
2.data files are different
3. Different configuration files
4.mysql ports are different

Installation steps:
The first mysql5.6 is installed normally:
Groupadd MySQL
Useradd-s/sbin/nologin-m-G MySQL MySQL
TAR-ZXVF mysql-5.6.25.tar.gz
CD mysql-5.6.25
Cmake-dcmake_install_prefix=/usr/local/mysql-dextra_charsets=all-ddefault_charset=utf8-ddefault_collation=utf8 _general_ci-dwith_readline=1-dwith_ssl=system-dwith_zlib=system-dwith_embedded_server=1-denabled_local_infile= 1
CP SUPPORT-FILES/MY-DEFAULT.CNF/ETC/MY.CNF
Vi/etc/my.cnf

#加入优化的mysql配置:
# # #查看mysql InnoDB 5.5 version show engine InnoDB status\g;
# # #注意: InnoDB Be sure to include the configuration file before initializing
# # #5 6 version needs to be removed #log_slow_queries=/usr/local/mysql/slow-log.log
# # #需要chmod 777/tmp

[Client]
#password = Your_password
Port = 3306
Socket =/tmp/mysql.sock

# here follows entries for some specific programs

# The MySQL server
[Mysqld]
Port = 3306
Socket =/tmp/mysql.sock
Skip-external-locking

Max_allowed_packet = 32M
Table_open_cache = 2048

Net_buffer_length = 1M

Character-set-server = UTF8

Skip-name-resolve
Ft_min_word_len = 4

# # # # performance # #

Open_files_limit = 10240

Max_connections = 800

Max_connect_errors = 6000

Thread_stack = 192K

Transaction_isolation = Repeatable-read

Tmp_table_size = 256M

Max_heap_table_size = 256M

Slow_query_log

Long_query_time = 1

#log_slow_queries =/usr/local/mysql/slow-log.log

Net_buffer_length = 1M

# # # log # # #

Log-error=/usr/local/mysql/mysqld.err

Back_log = 500

Max_binlog_cache_size = 8M

Max_binlog_size = 512M

Binlog_format=mixed

Expire_logs_days = 7

# # # buffer && Cache # # #

Read_buffer_size = 10M

Read_rnd_buffer_size = 32M

Sort_buffer_size = 2M

Join_buffer_size = 2M

Thread_cache_size = 300

Thread_concurrency = 8

Query_cache_size = 64M

Query_cache_limit = 4M

Binlog_cache_size = 4M

Key_buffer_size = 32M

Bulk_insert_buffer_size = 64M

# # # # MyISAM # #

Myisam_sort_buffer_size = 128M

Myisam_max_sort_file_size = 10G

Myisam_repair_threads = 1

Myisam_recover

Log-bin=mysql-bin

Server-id = 1

Innodb_data_home_dir =/usr/local/mysql/data
Innodb_data_file_path = Ibdata1:10m:autoextend
Innodb_log_group_home_dir =/usr/local/mysql/data

Innodb_buffer_pool_size = 1G
Innodb_additional_mem_pool_size = 16M

Innodb_log_file_size = 150M
Innodb_log_buffer_size = 16M
Innodb_flush_log_at_trx_commit =2
Innodb_lock_wait_timeout = 60

Innodb_flush_method = O_direct
Innodb_open_files = 800
Innodb_file_per_table=1
Innodb_file_io_threads=4

[Mysqldump]
Quick
Max_allowed_packet = 32M

[MySQL]
No-auto-rehash
# Remove The next comment character if you is not a familiar with SQL
#safe-updates
[Myisamchk]
Key_buffer_size = 32M
Sort_buffer_size = 32M
Read_buffer = 8M
Write_buffer = 8M

[Mysqlhotcopy]
Interactive-timeout
[Mysqld_safe]
Open-files-limit = 8196

#修改目录权限:
cd/usr/local/mysql/
Chown-r MySQL.
Chgrp-r MySQL.
Initialize MySQL:
/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql--datadir=/usr/local/mysql/data--user=mysql --defaults-file=/etc/my.cnf
Start MySQL:
/usr/local/mysql/bin/mysqld_safe--defaults-file=/etc/my.cnf--user=mysql&
Login:
/usr/local/mysql/bin/mysql-u root-p
/usr/local/mysql/bin/mysqladmin-u root-p shutdown
/usr/local/mysql/bin/mysqladmin--socket=/tmp/mysql1.sock-u root password xxx change password

Authorizing remote connections:
Grant all privileges on * * to [e-mail protected] "%" identified by ' xxx ' with GRANT option;
Flush privileges;

A second MySQL:

#需要知道不同的配置文件, the data file differs from the Scoket file name
TAR-ZXVF mysql-5.6.25.tar.gz
CD mysql-5.6.25
Attention:
The installation location cannot be the same, this is specified as/usr/local/3307 with the previous MySQL section

Cmake-dcmake_install_prefix=/usr/local/3307-dextra_charsets=all-ddefault_charset=utf8-ddefault_collation=utf8_ General_ci-dwith_readline=1-dwith_ssl=system-dwith_zlib=system-dwith_embedded_server=1-denabled_local_infile=1

Make && make install

CP SUPPORT-FILES/MY-DEFAULT.CNF/USR/LOCAL/3307/MY.CNF

Vi/usr/local/3307/my.cnf
#加入优化的mysql配置:

# # #查看mysql InnoDB 5.5 version show engine InnoDB status\g;
# # #注意: InnoDB Be sure to include the configuration file before initializing
# # #5 6 version needs to be removed #log_slow_queries=/usr/local/mysql/slow-log.log
# # #需要chmod 777/tmp

[Client]
#password = Your_password
Port = 3307 # #修改mysql的端口文件不要和以前那个重名
socket =/tmp/mysql1.sock # #修改mysql的socket文件不要和以前那个重名

# here follows entries for some specific programs

# The MySQL server
[Mysqld]
Port = 3307 # #修改mysql的端口文件不要和以前那个重名
socket =/tmp/mysql1.sock # #修改mysql的socket文件不要和以前那个重名
Skip-external-locking

Max_allowed_packet = 32M
Table_open_cache = 2048

Net_buffer_length = 1M

Character-set-server = UTF8

Skip-name-resolve
Ft_min_word_len = 4

# # # # performance # #

Open_files_limit = 10240

Max_connections = 800

Max_connect_errors = 6000

Thread_stack = 192K

Transaction_isolation = Repeatable-read

Tmp_table_size = 256M

Max_heap_table_size = 256M

Slow_query_log

Long_query_time = 1

#log_slow_queries =/usr/local/mysql/slow-log.log

Net_buffer_length = 1M

# # # log # # #

Log-error=/usr/local/mysql/mysqld.err

Back_log = 500

Max_binlog_cache_size = 8M

Max_binlog_size = 512M

Binlog_format=mixed

Expire_logs_days = 7

# # # buffer && Cache # # #

Read_buffer_size = 10M

Read_rnd_buffer_size = 32M

Sort_buffer_size = 2M

Join_buffer_size = 2M

Thread_cache_size = 300

Thread_concurrency = 8

Query_cache_size = 64M

Query_cache_limit = 4M

Binlog_cache_size = 4M

Key_buffer_size = 32M

Bulk_insert_buffer_size = 64M

# # # # MyISAM # #

Myisam_sort_buffer_size = 128M

Myisam_max_sort_file_size = 10G

Myisam_repair_threads = 1

Myisam_recover

Log-bin=mysql-bin

Server-id = 1

Innodb_data_home_dir =/usr/local/mysql/data
Innodb_data_file_path = Ibdata1:10m:autoextend
Innodb_log_group_home_dir =/usr/local/mysql/data

Innodb_buffer_pool_size = 1G
Innodb_additional_mem_pool_size = 16M

Innodb_log_file_size = 150M
Innodb_log_buffer_size = 16M
Innodb_flush_log_at_trx_commit =2
Innodb_lock_wait_timeout = 60

Innodb_flush_method = O_direct
Innodb_open_files = 800
Innodb_file_per_table=1
Innodb_file_io_threads=4

[Mysqldump]
Quick
Max_allowed_packet = 32M

[MySQL]
No-auto-rehash
# Remove The next comment character if you is not a familiar with SQL
#safe-updates
[Myisamchk]
Key_buffer_size = 32M
Sort_buffer_size = 32M
Read_buffer = 8M
Write_buffer = 8M

[Mysqlhotcopy]
Interactive-timeout
[Mysqld_safe]
Open-files-limit = 8196

Modify Permissions:
cd/usr/local/mysql/
Chown-r MySQL.
Chgrp-r MySQL.
Initialize MySQL:
/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/3307--datadir=/usr/local/3307/data--user=mysql-- Defaults-file=/usr/local/3307/my.cnf
Vi/usr/local/3307/my.cnf
Re-join MySQL optimized file contents, after initialization configuration file is reset

Develop the configuration file to start MySQL:
/usr/local/mysql/bin/mysqld_safe--defaults-file=/usr/local/3307/my.cnf--user=mysql&
Login:
/usr/local/3307/bin/mysql--socket=/tmp/mysql1.sock (no password)
/usr/local/3307/bin/mysqladmin--socket=/tmp/mysql1.sock-u root password xxxx (set password to XXXX)
/usr/local/3307/bin/mysql--socket=/tmp/mysql1.sock-u root-p (login with password)
/usr/local/3307/bin/mysqladmin--socket=/tmp/mysql1.sock-u root shutdown-p (off)
/usr/local/3307/bin/mysqld_safe--defaults-file=/usr/local/3307/my.cnf--user=mysql&
/usr/local/mysql/bin/mysqladmin--socket=/tmp/mysql1.sock-u root password xxx change password
Authorizing remote connections:
Grant all privileges on * * to [e-mail protected] "%" identified by ' xxxxx ' with GRANT option;
Flush privileges;

Attachment Download:

http://down.51cto.com/data/2149472

msyql5.6 dual MySQL installation and simple optimization