msyql5.6雙mysql安裝以及簡單最佳化

標籤：設定檔   伺服器   system   mysql   local   最佳化   

注意事項：
只要做到以下4點，2個mysql就可以同時安裝到一台機器ps：可以舉一反三，一台機器只要你的負載夠的話，想安裝幾個都可以。下面以2個為例：

我的測試伺服器配置為2個6核cpu 48g記憶體 4t硬碟，安裝主從測試之後10w的並發基本是上限。
2個mysql安裝到一台伺服器
1.socket檔案不同
2.data檔案不同
3.設定檔不同
4.mysql連接埠不同

安裝步驟：
第一個mysql5.6正常安裝：
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版本 show engine innodb status\G;
###注意:innodb 一定要在初始化之前加入設定檔
###5.6版本需要去掉#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 are not 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 .
初始化mysql：
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --defaults-file=/etc/my.cnf
啟動mysql：
/usr/local/mysql/bin/mysqld_safe  --defaults-file=/etc/my.cnf --user=mysql&
登入：
/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 修改密碼

授權遠端連線：
grant all privileges on   *.* to [email protected]"%" identified by ‘xxx‘ with grant option;
flush privileges ;

第二個mysql：

#需要知道不同的設定檔，data檔案和scoket檔案名稱不同
tar -zxvf mysql-5.6.25.tar.gz
cd mysql-5.6.25
注意：
安裝位置不能相同，這裡指定為/usr/local/3307與前面的mysql區分開

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版本 show engine innodb status\G;
###注意:innodb 一定要在初始化之前加入設定檔
###5.6版本需要去掉#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 are not 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 .
初始化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
重新加入mysql最佳化檔案內容，初始化之後設定檔被重設

制定設定檔啟動mysql：
/usr/local/mysql/bin/mysqld_safe  --defaults-file=/usr/local/3307/my.cnf --user=mysql&
登入：
/usr/local/3307/bin/mysql --socket=/tmp/mysql1.sock （無密碼）
/usr/local/3307/bin/mysqladmin --socket=/tmp/mysql1.sock -u root password xxxx（設定密碼為xxxx）
/usr/local/3307/bin/mysql --socket=/tmp/mysql1.sock  -u root -p （有密碼登入）
/usr/local/3307/bin/mysqladmin --socket=/tmp/mysql1.sock -u root shutdown -p（關閉）
/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 修改密碼
授權遠端連線：
grant all privileges on   *.* to [email protected]"%" identified by ‘xxxxx‘ with grant option;
flush privileges ;

附件下載：

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

msyql5.6雙mysql安裝以及簡單最佳化