centos下安装多个mysql数据库

MySQL的多实例有两种方式可以实现，两种方式各有利弊。 
第一种是使用多个配置文件启动不同的进程来实现多实例，这种方式的优势逻辑简单，配置简单，缺点是管理起来不太方便。 
第二种是通过官方自带的mysqld_multi使用单独的配置文件来实现多实例，这种方式定制每个实例的配置不太方面，优点是管理起来很方便，集中管理。 
下面就分别来实战这两种多实例的安装和管理

先来学习第一种使用多个配置文件启动多个不同进程的情况：

环境介绍：

mysql 版本：5.1.50 
操作系统：SUSE 11 
mysql实例数：3个 
实例占用端口分别为：3306、3307、3308

创建mysql用户

/usr/sbin/groupadd mysql/usr/sbin/useradd -g mysql mysql
1
2
1
2

编译安装mysql

tar xzvf mysql-5.1.50.tar.gzcd mysql-5.1.50./configure '--prefix=/usr/local/mysql' '--with-charset=utf8' '--with-extra-charsets=complex' '--with-pthread' '--enable-thread-safe-client' '--with-ssl' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-plugins=partition,innobase,blackhole,myisam,innodb_plugin,heap,archive' '--enable-shared' '--enable-assembler'makemake install
1
2
3
4
5
1
2
3
4
5

初始化数据库

/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3306 --user=mysql/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3307 --user=mysql/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3308 --user=mysql
1
2
3
1
2
3

创建配置文件

vim /data/dbdata_3306/my.cnf 
3306的配置文件如下

[client]port = 3306socket = /data/dbdata_3306/mysql.sock[mysqld]datadir=/data/dbdata_3306/skip-name-resolvelower_case_table_names=1innodb_file_per_table=1port = 3306socket = /data/dbdata_3306/mysql.sockback_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 1key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120[mysqldump]quickmax_allowed_packet = 256M[mysql]no-auto-rehashprompt=\\u@\\d \\R:\\m>[myisamchk]key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]open-files-limit = 8192
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

vim /data/dbdata_3307/my.cnf 
3307的配置文件如下：

[client]port = 3307socket = /data/dbdata_3307/mysql.sock[mysqld]datadir=/data/dbdata_3307/skip-name-resolvelower_case_table_names=1innodb_file_per_table=1port = 3307socket = /data/dbdata_3307/mysql.sockback_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 1key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120[mysqldump]quickmax_allowed_packet = 256M[mysql]no-auto-rehashprompt=\\u@\\d \\R:\\m>[myisamchk]key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]open-files-limit = 8192
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

vim /data/dbdata_3308/my.cnf 
3308的配置文件如下：

[client]port = 3308socket = /data/dbdata_3308/mysql.sock[mysqld]datadir=/data/dbdata_3308/skip-name-resolvelower_case_table_names=1innodb_file_per_table=1port = 3308socket = /data/dbdata_3308/mysql.sockback_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 1key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120[mysqldump]quickmax_allowed_packet = 256M[mysql]no-auto-rehashprompt=\\u@\\d \\R:\\m>[myisamchk]key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]open-files-limit = 8192
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

创建自动启动文件

vim /data/dbdata_3306/mysqld 
3306的启动文件如下：

#!/bin/bashmysql_port=3306mysql_username="admin"mysql_password="password"function_start_mysql(){printf "Starting MySQL...\n"/bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/dbdata_${mysql_port}/my.cnf 2>&1 > /dev/null &}function_stop_mysql(){printf "Stoping MySQL...\n"/usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data/dbdata_${mysql_port}/mysql.sock shutdown}function_restart_mysql(){printf "Restarting MySQL...\n"function_stop_mysqlfunction_start_mysql}function_kill_mysql(){kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')}case $1 instart)function_start_mysql;;stop)function_stop_mysql;;kill)function_kill_mysql;;restart)function_stop_mysqlfunction_start_mysql;;*)echo "Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill}";;esac
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

vim /data/dbdata_3307/mysqld 
3307的启动文件如下：

#!/bin/bashmysql_port=3307mysql_username="admin"mysql_password="password"function_start_mysql(){printf "Starting MySQL...\n"/bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/dbdata_${mysql_port}/my.cnf 2>&1 > /dev/null &}function_stop_mysql(){printf "Stoping MySQL...\n"/usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data/dbdata_${mysql_port}/mysql.sock shutdown}function_restart_mysql(){printf "Restarting MySQL...\n"function_stop_mysqlfunction_start_mysql}function_kill_mysql(){kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')}case $1 instart)function_start_mysql;;stop)function_stop_mysql;;kill)function_kill_mysql;;restart)function_stop_mysqlfunction_start_mysql;;*)echo "Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill}";;esac
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

vim /data/dbdata_3308/mysqld 
3308的启动文件如下：

#!/bin/bashmysql_port=3308mysql_username="admin"mysql_password="password"function_start_mysql(){printf "Starting MySQL...\n"/bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/dbdata_${mysql_port}/my.cnf 2>&1 > /dev/null &}function_stop_mysql(){printf "Stoping MySQL...\n"/usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data/dbdata_${mysql_port}/mysql.sock shutdown}function_restart_mysql(){printf "Restarting MySQL...\n"function_stop_mysqlfunction_start_mysql}function_kill_mysql(){kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')}case $1 instart)function_start_mysql;;stop)function_stop_mysql;;kill)function_kill_mysql;;restart)function_stop_mysqlfunction_start_mysql;;*)echo "Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill}";;esac
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

启动3306、3307、3308的mysql

/data/dbdata_3306/mysqld start/data/dbdata_3307/mysqld start/data/dbdata_3308/mysqld start
1
2
3
1
2
3

更改原来密码（处于安全考虑，还需要删除系统中没有密码的帐号，这里省略了）：

/usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3306/mysql.sock/usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3307/mysql.sock/usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3308/mysql.sock
1
2
3
1
2
3

登录测试并创建关闭mysql的帐号权限

/usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';flush privileges;/usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';flush privileges;/usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';flush privileges;
1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
7
8
9

创建了admin帐号以后脚本的stop功能和restart功能就正常了！

更改环境变量

vim /etc/profile 添加下面一行内容PATH=${PATH}:/usr/local/mysql/bin/source /etc/profile
1
2
3
1
2
3

添加到自动启动

vim /etc/init.d/boot.local/data/dbdata_3306/mysqld start/data/dbdata_3307/mysqld start/data/dbdata_3308/mysqld start
1
2
3
4
1
2
3
4

如果是rhel或者centos系统的话自启动文件/etc/rc.local

管理的话，在本地都是采用 -S /data/dbdata_3308/mysql.sock，如果在远程可以通过不同的端口连接上去坐管理操作。其他的和单实例的管理没什么区别！

再来看第二种通过官方自带的mysqld_multi来实现多实例实战：

这里的mysql安装以及数据库的初始化和前面的步骤一样，就不再赘述。 
值得注意的是高版本的mysql 
初始化的命令不一样(mysql_install_db)位置

[root@www scripts]# /usr/local/mysql/scripts/mysql_install_db –basedir=/usr/local/mysql –datadir=/usr/local/mysql3307/var –user=mysql 
Installing MySQL system tables… 
OK 
Filling help tables… 
OK

To start mysqld at boot time you have to copy 
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! 
To do so, start the server, then issue the following commands:

/usr/local/mysql/bin/mysqladmin -u root password ‘new-password’ 
/usr/local/mysql/bin/mysqladmin -u root -h www password ‘new-password’

Alternatively you can run: 
/usr/local/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test 
databases and anonymous user created by default. This is 
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with: 
cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl 
cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

mysqld_multi的配置

【再次注意这里你如果已经安装过mysql 3306的请停止掉，不要开机启动 下面这个配置是全部走的mysql_multi 直接改的my.cnf】 
结尾我会补充如果在已经安装过的mysql 3306上，额外加一个3307或者3308实例

vim /etc/my.cnf

[mysqld_multi]mysqld = /usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladminuser = adminpassword = password[mysqld1]socket = /data/dbdata_3306/mysql.sockport = 3306pid-file = /data/dbdata_3306/3306.piddatadir = /data/dbdata_3306user = mysqlskip-name-resolvelower_case_table_names=1innodb_file_per_table=1back_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 1key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120[mysqld2]socket = /data/dbdata_3307/mysql.sockport = 3307pid-file = /data/dbdata_3307/3307.piddatadir = /data/dbdata_3307user = mysqlskip-name-resolvelower_case_table_names=1innodb_file_per_table=1back_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 1key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120[mysqld3]socket = /data/dbdata_3308/mysql.sockport = 3308pid-file = /data/dbdata_3308/3308.piddatadir = /data/dbdata_3308user = mysqlskip-name-resolvelower_case_table_names=1innodb_file_per_table=1back_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 1key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120[mysqldump]quickmax_allowed_packet = 256M[mysql]no-auto-rehashprompt=\\u@\\d \\R:\\m>[myisamchk]key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]open-files-limit = 8192
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173

mysqld_multi启动

/usr/local/mysql/bin/mysqld_multi start 1/usr/local/mysql/bin/mysqld_multi start 2/usr/local/mysql/bin/mysqld_multi start 3
1
2
3
1
2
3

可能会报错 
[root@www ~]# /usr/local/mysql/bin/mysqld_multi start 1 
WARNING: my_print_defaults command not found. 
Please make sure you have this command available and 
in your path. The command is available from the latest 
MySQL distribution. 
ABORT: Can’t find command ‘my_print_defaults’. 
This command is available from the latest MySQL 
distribution. Please make sure you have the command 
in your PATH. 
把环境变量加入到PATH中去 
/usr/local/mysql/bin 
[root@www ~]# source /etc/profile

或者采用一条命令的形式：

/usr/local/mysql/bin/mysqld_multi start 1-3
1
1

更改原来密码（处于安全考虑，还需要删除系统中没有密码的帐号，这里省略了）：

/usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3306/mysql.sock/usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3307/mysql.sock/usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3308/mysql.sock
1
2
3
1
2
3

登录测试并创建admin密码(停止mysql的时候需要使用到)

/usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';flush privileges;/usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';flush privileges;/usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';flush privileges;
1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
7
8
9

更改环境变量

vim /etc/profilePATH=${PATH}:/usr/local/mysql/bin/source /etc/profile
1
2
3
1
2
3

添加到开机启动

vim /etc/init.d/boot.local/usr/local/mysql/bin/mysqld_multi start 1-3
1
2
1
2

如果是rhel或者centos系统的话自启动文件/etc/rc.local

管理的话，在本地都是采用 -S /data/dbdata_3308/mysql.sock，如果在远程可以通过不同的端口连接上去坐管理操作。其他的和单实例的管理没什么区别！ 
大家在管理多实例的话可以使用mysqlmanager实例管理器，管理器来会比较方面，限于篇幅，这里就不在多做介绍！

【转自飞雪无痕的博客http://blog.chinaunix.net/uid-20639775-id-3438560.html】

现在我来补充如果在已经安装过的mysql 3306上，额外加一个3307或者3308实例

[root@www etc]# vim /etc/my_multi.cnf[mysqld_multi]mysqld = /usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladminuser = rootpassword = root[mysqld1]port            = 3307socket          = /tmp/mysql3307/mysql.sockdatadir = /usr/local/mysql3307/varpid-file = /tmp/mysql3307/mysql3307.piduser = mysqlskip-external-lockingskip-name-resolvemax_connections = 1000key_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kinnodb_data_home_dir = /usr/local/mysql3307/varinnodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = /usr/local/mysql3307/varinnodb_buffer_pool_size = 16Minnodb_additional_mem_pool_size = 2Minnodb_log_file_size = 5Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1innodb_lock_wait_timeout = 50[mysqld2]port            = 3308socket          = /tmp/mysql3308/mysql.sockdatadir = /usr/local/mysql3308/varpid-file = /tmp/mysql3308/mysql3308.piduser = mysqlskip-external-lockingskip-name-resolvemax_connections = 1000key_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kinnodb_data_home_dir = /usr/local/mysql3308/varinnodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = /usr/local/mysql3308/varinnodb_buffer_pool_size = 16Minnodb_additional_mem_pool_size = 2Minnodb_log_file_size = 5Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1innodb_lock_wait_timeout = 50[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeoutexpire_logs_days = 10[root@www ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql3307/var --user=mysqlInstalling MySQL system tables...OKFilling help tables...OKTo start mysqld at boot time you have to copysupport-files/mysql.server to the right place for your systemPLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !To do so, start the server, then issue the following commands:/usr/local/mysql/bin/mysqladmin -u root password 'new-password'/usr/local/mysql/bin/mysqladmin -u root -h www password 'new-password'Alternatively you can run:/usr/local/mysql/bin/mysql_secure_installationwhich will also give you the option of removing the testdatabases and anonymous user created by default.  This isstrongly recommended for production servers.See the manual for more instructions.You can start the MySQL daemon with:cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe &You can test the MySQL daemon with mysql-test-run.plcd /usr/local/mysql/mysql-test ; perl mysql-test-run.plPlease report any problems at http://bugs.mysql.com/#同理3308也这样操作#把/usr/local/mysql/bin 加入环境变量[root@www ~]# vim /etc/profile#加上下面的这行export PATH=$PATH:/usr/local/mysql/bin/[root@www ~]# /usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/etc/my_multi.cnf start 1[root@www ~]# /usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/etc/my_multi.cnf start 2#这里的1 和2就是你在my_multi.cnf 写的[mysql1] [mysql2][root@www ~]# netstat -tnlpActive Internet connections (only servers)Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   tcp        0      0 0.0.0.0:21                  0.0.0.0:*                   LISTEN      1550/pure-ftpd (SER tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      1073/sshd           tcp        0      0 127.0.0.1:631               0.0.0.0:*                   LISTEN      1017/cupsd          tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      1533/sendmail       tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      18242/mysqld        tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      22666/mysqld        tcp        0      0 127.0.0.1:11211             0.0.0.0:*                   LISTEN      1566/memcached      tcp        0      0 127.0.0.1:6379              0.0.0.0:*                   LISTEN      1513/redis-server 1 tcp        0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN      23394/mysqld        tcp        0      0 0.0.0.0:80                  0.0.0.0:*                   LISTEN      6094/nginx          tcp        0      0 :::21                       :::*                        LISTEN      1550/pure-ftpd (SER tcp        0      0 :::22                       :::*                        LISTEN      1073/sshd           tcp        0      0 ::1:631                     :::*                        LISTEN      1017/cupsd          [root@www ~]# # 可以看到3307 和3308 端口已经起来了#下面修改他们的root密码[root@www ~]# /usr/local/mysql/bin/mysqladmin -uroot password 'root' -S /tmp/mysql3307/mysql.sock[root@www ~]# /usr/local/mysql/bin/mysqladmin -uroot password 'root' -S /tmp/mysql3308/mysql.sock#登录一下了[root@www ~]# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3307/mysql.sockEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.5.42-log Source distributionCopyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+4 rows in set (0.00 sec)mysql>#可以看到是正常的 同理3308也如此#下面就是加入到开机启动 最简单的办法[root@www ~]# vim /etc/rc.local#加上下面的这行/usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/etc/my_multi.cnf start 1-2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173