MySQL Multi-instance installation

MysqlMulti-instanceinstallation 1. Configure 3306 Instances 1.1, check if MySQL is installed on the server 

[[Email protected]_64.rpm-bundle]# Rpm-aq | grep MySQL

Mysql-server-5.1.66-2.el6_3.x86_64

Mysql-libs-5.1.66-2.el6_3.x86_64

Mysql-devel-5.1.66-2.el6_3.x86_64

Qt-mysql-4.6.2-25.el6.x86_64

Mysql-5.1.66-2.el6_3.x86_64

1.2, uninstall the old version first

rpm-e MySQL--nodeps--allmatches ( ignore dependencies, delete all previous steps to find out MySQL)

[[Email protected] ~] #rpm-e mysql-server-5.1.66-2.el6_3.x86_64--nodeps--allmatches

[[Email protected] ~] #rpm-e mysql-libs-5.1.66-2.el6_3.x86_64--nodeps--allmatches

[[Email protected] ~] #rpm-e mysql-devel-5.1.66-2.el6_3.x86_64--nodeps--allmatches

[[Email protected] ~] #rpm-e qt-mysql-4.6.2-25.el6.x86_64--nodeps--allmatches

[[Email protected] ~] #rpm-e MySQL--nodeps--allmatches

[[Email protected] ~] #rpm-aq | grep MySQL

1.3, install software

# rpm-ivfmysql-server-5.6.25-1.el6.x86_64.rpm #MySQL service-side program

# rpm-ivfmysql-client-5.6.25-1.el6.x86_64.rpm #MySQL client program

# rpm-ivfmysql-devel-5.6.25-1.el6.x86_64.rpm #MySQL 's library and header files

# rpm-ivfmysql-shared-5.6.25-1.el6.x86_64.rpm #MySQL Shared library

# rpm-ivfmysql-shared-compat-5.6.25-1.el6.x86_64.rpm #RHEL Compatibility Pack

1.4, Change Password

#/etc/init.d/mysql stop

# Mysqld_safe--skip-grant-tables &

# mysql-uroot-e "Use mysql;update userset password=password (' 123456 ') where user= ' root ' and host= ' localhost ';";

# mysql-uroot-e "Use mysql;update user sethost= '% ' where user= ' root ' and host= ' localhost '; flush privileges;";

1.5, modify the configuration file

[[email protected]] #service MySQL stop

[[Email protected]]# vi/usr/my.cnf

[Client]

Default-character-set=utf8

Port =3306

Socket =/var/lib/mysql/mysql.sock

[MySQL]

No-auto-rehash

[Mysqld]

Replicate-ignore-db=mysql

Binlog-ignore-db=mysql

Binlog-ignore-db=performance_schema

Binlog-ignore-db=information_schema

Character-set-server=utf8

User =mysql

Port =3306

Socket =/var/lib/mysql/mysql.sock

Basedir =/USR

DataDir =/var/lib/mysql

Server-id= 1

Default-storage-engine=innodb

Binlog_format=mixed

Open_files_limit = 10240

Back_log= 600

max_connections= 1000

Max_connect_errors= 6000

table_open_cache=614

external-locking= FALSE

max_allowed_packet= 32M

Sort_buffer_size= 2M

Join_buffer_size= 2M

Thread_cache_size= 64

Thread_concurrency= 8

Query_cache_size= 64M

query_cache_limit= 4M

query_cache_min_res_unit= 2k

thread_stack= 192K

transaction_isolation= Repeatable-read

Tmp_table_size= 256M

Max_heap_table_size= 256M

Long_query_time= 1

Slow_query_log=1

Slow_query_log_file=/var/lib/mysql/mysql_slow_query.log

#log_long_format

Log-error=/var/lib/mysql/master.err

Pid-file=/var/lib/mysql/mysql.pid

Log-bin =/var/lib/mysql/master-bin

Log_bin_index=/var/lib/mysql/master-bin.index

Relay-log=/var/lib/mysql/relay-bin

Relay-log-info-file=/var/lib/mysql/relay-log.info

Binlog_cache_size= 4M

Max_binlog_cache_size= 50M

Max_binlog_size= 512M

expire_logs_days= 7

Key_buffer_size= 32M

Read_buffer_size= 1M

Read_rnd_buffer_size= 16M

Bulk_insert_buffer_size= 64M

Myisam_sort_buffer_size= 128M

Myisam_max_sort_file_size= 10G

Myisam_recover

Skip-name-resolve

Lower_case_table_names= 1

slave-skip-errors=1032,1062

Innodb_additional_mem_pool_size= 16M

Innodb_buffer_pool_size= 2048M

Innodb_file_io_threads= 4

Innodb_thread_concurrency= 8

Innodb_flush_log_at_trx_commit= 1

Innodb_log_buffer_size= 16M

Innodb_log_file_size= 128M

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= 32M

[Mysqld_safe]

Log-error=/var/lib/mysql/mysql_master.err

Pid-file=/var/lib/mysql/mysqld.pid

Service Mysqlstart

2, configuring 3307 instances2.1, set up 3307 directory to store data:

[[Email protected] ~] #mkdir/data/3307/mysql-p

[[Email protected] ~] #cd/

[Root[email protected]/] #chown-R mysql.mysql Data

[[email protected]/] #cd/data/3307/mysql

2.2, set up 3307 configuration files:

VI my.cnf

[Client]

Default-character-set=utf8

Port = 3307

Socket =/data/3307/mysql.sock

[MySQL]

No-auto-rehash

[Mysqld]

Character-set-server=utf8

user = MySQL

Port = 3307

Socket =/data/3307/mysql.sock

Basedir =/usr

DataDir =/data/3307/mysql

Binlog_format=mixed

Open_files_limit = 10240

Sync_binlog = 1

Back_log = 600

Max_connections = 3000

Max_connect_errors = 6000

#table_cache = 614

external-locking = FALSE

Max_allowed_packet = 32M

Sort_buffer_size = 2M

Join_buffer_size = 2M

Thread_cache_size = 300

Thread_concurrency = 8

Query_cache_size = 64M

Query_cache_limit = 4M

Query_cache_min_res_unit = 2k

#default_table_type = InnoDB

Thread_stack = 192K

Transaction_isolation = read-committed

Tmp_table_size = 256M

Max_heap_table_size = 256M

Long_query_time = 1

Slow_query_log=1

Slow_query_log_file =/data/3307/mysql/mysql_slow_query.log

#log_long_format

Log-error=/data/3307/mysql/error.log

#log-slow-queries=/data/3307/mysql/slow-log.log

Pid-file=/data/3307/mysql/mysql.pid

Log-bin =/data/3307/mysql/mysql-bin

Relay-log=/data/3307/mysql/relay-bin

Relay-log-info-file=/data/3307/mysql/relay-log.info

Binlog_cache_size = 4M

Max_binlog_cache_size = 50M

Max_binlog_size = 512M

Expire_logs_days = 7

Key_buffer_size = 32M

Read_buffer_size = 1M

Read_rnd_buffer_size = 16M

Bulk_insert_buffer_size = 64M

Myisam_sort_buffer_size = 128M

Myisam_max_sort_file_size = 10G

#myisam_max_extra_sort_file_size = 10G

Myisam_repair_threads = 1

Myisam_recover

Skip-name-resolve

Lower_case_table_names = 1

slave-skip-errors=1032,1062

#repicate-ignore-db=mysql

Server-id = 11

Innodb_additional_mem_pool_size = 16M

Innodb_buffer_pool_size = 2048M

#innodb_data_file_path =ibdata1:1024m:autoextend

Innodb_file_io_threads = 4

Innodb_thread_concurrency = 8

Innodb_flush_log_at_trx_commit = 1

Innodb_log_buffer_size = 16M

Innodb_log_file_size = 1024M

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 = 32M

[Mysqld_safe]

Log-error=/data/3307/mysql/mysql_master.err

Pid-file=/data/3307/mysql/mysqld.pid

Permissions to modify configuration parameters

[Email protected]]# chown mysql.mysql my.cnf

[Email protected]]#/usr/bin/mysql_install_db--defaults-file=/data/3307/mysql/my.cnf--user=mysql--BASEDIR=/USR- -datadir=/data/3307/mysql/

[Email protected]]#/usr/bin/mysqld_safe--defaults-file=/data/3307/mysql/my.cnf--user=mysql--BASEDIR=/USR-- datadir=/data/3307/mysql/&

2.3, view port number:

Netstat-nat|grep 330

2.4, change Password:

Mysqladmin-uroot password ' 123456 '-s/data/3307/mysql.sock

This article is from the "MySQL" blog, be sure to keep this source http://wenjing.blog.51cto.com/5908329/1695404

MySQL Multi-instance installation