mysql 5.5.35 单机多实例配置详解_MySQL 5.5.35 单机多实例配置详解
一、前言
二、概述
三、環境準備
四、安裝MySQL 5.5.35
五、新建支持多實例的配置文件(我這里配置的是四個實例)
六、初始化多實例數據庫
七、提供管理腳本 mysqld_multi.server
八、整體備份方便后續遷移
九、管理MySQL多實例
十、登錄MySQL多實例
十一、其它管理配置
十二、總結
一、前言
在前幾篇博客中我們測試了MySQL 5.5的性能與重要指標，較MySQL 5.1性能與各指標有明顯的提升，不清楚的博友可以參考一下這幾篇博客：
在這一篇博客中我們主要的大家講解一下MySQL多實例的應用。
二、概述
1.應用場景采用了數據偽分布式架構的原因，而項目啟動初期又不一定有那多的用戶量，為此先一組物理數據庫服務器，但部署多個實例，方便后續遷移；
為規避mysql對SMP架構不支持的缺陷，使用多實例綁定處理器的辦法，把不同的數據庫分配到不同的實例上提供數據服務；
一臺物理數據庫服務器支撐多個數據庫的數據服務，為提高mysql復制的從機的恢復效率，采用多實例部署；
已經為雙主復制的mysql數據庫服務器架構，想部分重要業務的數據多一份異地機房的熱備份，而mysql復制暫不支持多主的復制模式，且不給用戶提供服務，為有效控制成本，會考慮異地機房部署一臺性能超好的物理服務器，甚至外加磁盤柜的方式，為此也會部署多實例；
傳統游戲行業的MMO/MMORPG，以及Web Game，每一個服都對應一個數據庫，而可能要做很多數據查詢和數據訂正的工作，為減少維護而出錯的概率，也可能采用多實例部署的方式，按區的概念分配數據庫；
2.背景/需求、注意事項
(1).背景與需求將所有的安裝文件、配置文件、數據目錄全部放存/data/mysql目錄中，便于今后實現快速遷移、整體備份和快速復制；
在一臺服務器上運行四個MySQL實例，分別綁定在3306、3307、3308、3309端口上；
四個實例都開啟binlog日志，數據目錄分別存放在/data/mysql/data、/data/mysql/data2、/data/mysql/data3、/data/mysql/data4
四個實例均采用InnoDB作為默認的存儲引擎，字符編碼采用UTF-8；
四個實例均采用相同的性能優化配置參數；
(2).注意事項在編譯安裝時，將數據庫的配置文件my.cnf以及data目錄等均指向到/data/mysql目錄中；
通過mysqld_multi的方式來管理四個不同的實例，采用相同的配置文件共享性能優化配置參數；
在同一個配置文件中，利用[mysqld1]、[mysqld2]、[mysqld3]、[mysqld4]標簽實現不同實例的差異化配置；
三、環境準備
1.安裝yum源
1
2[root@node1 src]# wget http://mirrors.hustunique.com/epel/6/x86_64/epel-release-6-8.noarch.rpm
[root@node1 src]# rpm -ivh epel-release-6-8.noarch.rpm
2.同步時間
1
2
3[root@node1 src]# yum install -y ntp
[root@node1 src]# ntpdate 202.120.2.101
[root@node1 src]# hwclock –w
3.安裝mysql5.5依賴包
1[root@node1 ~]# yum install -y autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool* openssl*
4.安裝cmake
1[root@node1 ~]# yum install -y cmake
四、安裝MySQL 5.5.35
1.創建安裝目錄與數據存放目錄
1
2[root@node1 ~]# mkdir /data/mysql
[root@node1 ~]# mkdir /data/mysql/data
2.創建mysql用戶與組
1
2
3[root@node1 ~]# useradd mysql
[root@node1 ~]# id mysql
uid=500(mysql) gid=500(mysql) 組=500(mysql)
3.授權安裝目錄與數據目錄
1
2[root@node1 ~]# chown -R mysql.mysql /data/mysql/
[root@node1 ~]# chown -R mysql.mysql /data/mysql/data
4.安裝mysql
1
2
3
4
5[root@node1 ~]# cd src/
[root@node1 src]# tar xf mysql-5.5.35.tar.gz
[root@node1 src]# cd mysql-5.5.35
[root@node1 mysql-5.5.35]# cmake -DCMAKE_INSTALL_PREFIX=/data/mysql -DSYSCONFDIR=/data/mysql/etc -DMYSQL_DATADIR=/data/mysql/data -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DMYSQL_USER=mysql -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1
[root@node1 mysql-5.5.35]# make && make install
好了，到這里我們的mysql就安裝完成了，下面我們為mysql提供多實例配置文件。
五、新建支持多實例的配置文件(我這里配置的是四個實例)
1.刪除默認的數據目錄
1
2[root@node1 ~]# cd /data/mysql/
[root@node1 mysql]# rm -rf data
2.創建多實例配置需要的目錄
1
2[root@node1 mysql]# mkdir etc tmp run log binlogs data data2 data3 data4
[root@node1 mysql]# chown -R mysql.mysql tmp run log binlogs data data2 data3 data4
3.提供配置文件
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
174
175[root@node1 ~]# cd src/
[root@node1 src]# cd mysql-5.5.35
[root@node1 mysql-5.5.35]# cp support-files/my-small.cnf /data/mysql/etc/my.cnf
[root@node1 ~]# cd /data/mysql/etc/
[root@node1 etc]# vim my.cnf
# This server may run 4+ separate instances. So we use mysqld_multi to manage their services.
[client]
default-character-set = utf8
[mysqld_multi]
mysqld =/data/mysql/bin/mysqld_safe
mysqladmin =/data/mysql/bin/mysqladmin
log =/data/mysql/log/mysqld_multi.log
user = root
#password =
# This is the general purpose database.
# The locations are default.
# They are left in [mysqld] in case the server is started normally instead of by mysqld_multi.
[mysqld1]
socket =/data/mysql/run/mysqld.sock
port = 3306
pid-file =/data/mysql/run/mysqld.pid
datadir =/data/mysql/data
lc-messages-dir =/data/mysql/share/english
# These support master - master replication
#auto-increment-increment = 4
#auto-increment-offset = 1? # Since it is master 1
log-bin =/data/mysql/binlogs/bin-log-mysqld1
log-bin-index =/data/mysql/binlogs/bin-log-mysqld1.index
#binlog-do-db = # Leave this blank if you want to control it on slave
max_binlog_size = 1024M
# This is exlusively for mysqld2
# It is on 3307 with data directory /data/mysqld/data2
[mysqld2]
socket =/data/mysql/run/mysqld.sock2
port = 3307
pid-file =/data/mysql/run/mysqld.pid2
datadir =/data/mysql/data2
lc-messages-dir =/data/mysql/share/english
# Disable DNS lookups
#skip-name-resolve
# These support master - slave replication
log-bin =/data/mysql/binlogs/bin-log-mysqld2
log-bin-index =/data/mysql/binlogs/bin-log-mysqld2.index
#binlog-do-db =? # Leave this blank if you want to control it on slave
max_binlog_size = 1024M
# Relay log settings
#relay-log = /data/mysql/log/relay-log-mysqld2
#relay-log-index = /data/mysql/log/relay-log-mysqld2.index
#relay-log-space-limit = 4G
# Slow query log settings
#log-slow-queries = /data/mysql/log/slow-log-mysqld2
#long_query_time = 2
#log-queries-not-using-indexes
# This is exlusively for mysqld3
# It is on 3308 with data directory /data/mysqld/data3
[mysqld3]
socket =/data/mysql/run/mysqld.sock3
port = 3308
pid-file =/data/mysql/run/mysqld.pid3
datadir =/data/mysql/data3
lc-messages-dir =/data/mysql/share/english
#Disable DNS lookups
#skip-name-resolve
# These support master - slave replication
log-bin =/data/mysql/binlogs/bin-log-mysqld3
log-bin-index =/data/mysql/binlogs/bin-log-mysqld3.index
#binlog-do-db =? # Leave this blank if you want to control it on slave
max_binlog_size = 1024M
# This is exlusively for mysqld4
# It is on 3309 with data directory /data/mysqld/data4
[mysqld4]
socket =/data/mysql/run/mysqld.sock4
port = 3309
pid-file =/data/mysql/run/mysqld.pid4
datadir =/data/mysql/data4
lc-messages-dir =/data/mysql/share/english
# Disable DNS lookups
#skip-name-resolve
# These support master - slave replication
log-bin =/data/mysql/binlogs/bin-log-mysqld4
log-bin-index =/data/mysql/binlogs/bin-log-mysqld4.index
#binlog-do-db =? # Leave this blank if you want to control it on slave
max_binlog_size = 1024M
# The rest of the my.cnf is shared
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
basedir =/data/mysql
tmpdir =/data/mysql/tmp
socket =/data/mysql/run/mysqld.sock
port = 3306
pid-file =/data/mysql/run/mysqld.pid
datadir =/data/mysql/data
lc-messages-dir =/data/mysql/share/english
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
# Increase the max connections
max_connections = 2
# The expiration time for logs, including binlogs
expire_logs_days = 14
# Set the character as utf8
character-set-server = utf8
collation-server = utf8_unicode_ci
# This is usually only needed when setting up chained replication
#log-slave-updates
# Enable this to make replication more resilient against server crashes and restarts
# but can cause higher I/O on the server
#sync_binlog = 1
# The server id, should be unique in same network
server-id = 1
# Set this to force MySQL to use a particular engine/table-type for new tables
# This setting can still be overridden by specifying the engine explicitly
# in the CREATE TABLE statement
default-storage-engine = INNODB
# Enable Per Table Data for InnoDB to shrink ibdata1
innodb_file_per_table = 1
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /data/mysql/data
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /data/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 % of RAM
# but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
[mysqlhotcopy]
interactive-timeout
[mysql.server]
user = mysql
[mysqld_safe]
log-error =/data/mysql/log/mysqld.log
pid-file =/data/mysql/run/mysqld.pid
open-files-limit = 8192
注，MySQL自帶了幾個不同的配置文件，放置在/data/mysql/support-files目錄下，分別是my-huge.cnf,my-innodb-heavy-4G.cnf,my-large.cnf,my-medium.cnf,my-small.cnf，通過名稱我們可以很直觀的了解到他們是針對不同的服務器配置的，本文的配置文件是來自于my-small.cnf的，因為我是在虛擬機上進行的設置；在生產環境中，我們可以通過參考my-huge.cnf或my-innodb-heavy-4G.cnf中的部分參數配置，來對服務器進行優化；
4.修改my.cnf讀寫權限
1
2[root@node1 etc]# chown -R root.root /data/mysql/etc
[root@node1 etc]# chmod 600 /data/mysql/etc/my.cnf
好了，到這里我們的配置文件就設置完成了，下面我們來初始化一下數據庫。
六、初始化多實例數據庫
1.切換到mysql的安裝目錄
1[root@node1 ~]# cd /data/mysql/
2.初始化實例[mysqld1]
1[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data --user=mysql
3.初始化實例[mysqld2]
1[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data2 --user=mysql
4.初始化實例[mysqld3]
1[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data3 --user=mysql
5.初始化實例[mysqld4]
1[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data4 --user=mysql
好了，到這里我們初始化工作就完成了，下面我們來提供一下多實例的管理腳本。
七、提供管理腳本 mysqld_multi.server
1.創建管理腳本目錄
1[root@node1 mysql]# mkdir /data/mysql/init.d
2.提供管理腳本
1[root@node1 mysql]# cp support-files/mysqld_multi.server init.d/
3.簡單修改一下腳本
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[root@node1 mysql]# cd init.d/
[root@node1 init.d]# vim mysqld_multi.server
#!/bin/sh
#
# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.
# This script assumes that my.cnf file exists either in /etc/my.cnf or
# /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the
# mysqld_multi documentation for detailed instructions.
#
# This script can be used as /etc/init.d/mysql.server
#
# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 64 36
# description: A very fast and reliable SQL database engine.
#
# Version 1.0
#
basedir=/data/mysql
bindir=/data/mysql/bin
conf=/data/mysql/etc/my.cnf
export PATH=$PATH:$bindir
if test -x $bindir/mysqld_multi
then
mysqld_multi="$bindir/mysqld_multi";
else
echo "Can't execute $bindir/mysqld_multi from dir $basedir";
exit;
fi
case "$1" in
'start' )
"$mysqld_multi" --defaults-extra-file=$conf start $2
;;
'stop' )
"$mysqld_multi" --defaults-extra-file=$conf stop $2
;;
'report' )
"$mysqld_multi" --defaults-extra-file=$conf report $2
;;
'restart' )
"$mysqld_multi" --defaults-extra-file=$conf stop $2
"$mysqld_multi" --defaults-extra-file=$conf start $2
;;
*)
echo "Usage: $0 {start|stop|report|restart}" >&2
;;
esac
好了，到這里我們所有的配置就全部完成了，下面我們打包備份一下。
八、整體備份方便后續遷移
1
2
3
4
5
6
7[root@node1 ~]# cd /data/
[root@node1 data]# tar czvf mysql-5.5.350-full.tar.gz /data/mysql/
[root@node1 data]# ll -h
總用量 128M
drwx------.? 2 root? root?? 16K 8月? 17 18:42 lost+found
drwxr-xr-x? 22 mysql mysql 4.0K 1月?? 6 22:08 mysql
-rw-r--r--?? 1 root? root? 128M 1月?? 7 00:25 mysql-5.5.350-full.tar.gz
注，備份完成后，直接將mysql-5.5.350-full.tar.gz拿到其他服務器上，解壓后便可以直接啟用。嘿嘿，方便吧……
九、管理MySQL多實例
1.同時啟動四個mysql實例
(1).方法一：
1[root@node1 ~]# /data/mysql/init.d/mysqld_multi.server start 1,2,3,4
或方法二：
1[root@node1 ~]# /data/mysql/init.d/mysqld_multi.server start 3306,3307,3308,3309
(2).查看一下啟動的實例
1
2
3
4
5[root@node1 ~]# netstat -ntulp | grep mysqld
tcp??????? 0????? 0 0.0.0.0:3307??????????????? 0.0.0.0:*?????????????????? LISTEN????? 31416/mysqld
tcp??????? 0????? 0 0.0.0.0:3308??????????????? 0.0.0.0:*?????????????????? LISTEN????? 31414/mysqld
tcp??????? 0????? 0 0.0.0.0:3309??????????????? 0.0.0.0:*?????????????????? LISTEN????? 31420/mysqld
tcp??????? 0????? 0 0.0.0.0:3306??????????????? 0.0.0.0:*?????????????????? LISTEN????? 31413/mysqld
2.同時關閉四個mysql實例
(1).方法一：
1[root@node1 ~]# /data/mysql/init.d/mysqld_multi.server stop 1,2,3,4
或方法二：
1[root@node1 ~]# /data/mysql/init.d/mysqld_multi.server stop 3306,3307,3308,3309
3.單獨啟動或關閉mysql實例
(1).啟動一個實例
1
2
3[root@node1 ~]# /data/mysql/init.d/mysqld_multi.server start 1
[root@node1 ~]# netstat -ntulp | grep mysqld
tcp??????? 0????? 0 0.0.0.0:3306??????????????? 0.0.0.0:*?????????????????? LISTEN????? 32221/mysqld
(2).關閉一個實例
1[root@node1 ~]# /data/mysql/init.d/mysqld_multi.server stop 1
注，啟動或關閉兩個或者三個實例方法的上面相同這里就不再演示。
十、登錄MySQL多實例
注，我們同時啟動四個實例，下面我們來演示一下怎么分別登錄這四個實例。為了演示四個實例的區別，我們分別在四個實例中創建mydb1、mydb2、mydb3、mydb4。
1.登錄[mysqld1]
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[root@node1 ~]# /data/mysql/bin/mysql -uroot -h127.0.0.1 -P3306 -p
Enter password:
Welcome to the MySQL monitor.? Commands end with ; or \g.
Your MySQL connectionid is 1
Server version: 5.5.35-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type'help;' or'\h' for help. Type'\c' toclear the current input statement.
mysql> show databases;
+--------------------+
| Database?????????? |
+--------------------+
| information_schema |
| mysql????????????? |
| performance_schema |
|test?????????????? |
+--------------------+
4 rowsin set (0.00 sec)
mysql> create database mydb1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database?????????? |
+--------------------+
| information_schema |
| mydb1????????????? |
| mysql????????????? |
| performance_schema |
|test?????????????? |
+--------------------+
5 rowsin set (0.00 sec)
2.登錄[mysqld2]
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[root@node1 ~]# /data/mysql/bin/mysql -uroot -h127.0.0.1 -P3307 -p
Enter password:
Welcome to the MySQL monitor.? Commands end with ; or \g.
Your MySQL connectionid is 1
Server version: 5.5.35-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type'help;' or'\h' for help. Type'\c' toclear the current input statement.
mysql> show databases;
+--------------------+
| Database?????????? |
+--------------------+
| information_schema |
| mysql????????????? |
| performance_schema |
|test?????????????? |
+--------------------+
4 rowsin set (0.00 sec)
mysql> create database mydb2;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database?????????? |
+--------------------+
| information_schema |
| mydb2????????????? |
| mysql????????????? |
| performance_schema |
|test?????????????? |
+--------------------+
5 rowsin set (0.00 sec)
3.登錄[mysqld3]
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[root@node1 ~]# /data/mysql/bin/mysql -uroot -h127.0.0.1 -P3308 -p
Enter password:
Welcome to the MySQL monitor.? Commands end with ; or \g.
Your MySQL connectionid is 1
Server version: 5.5.35-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type'help;' or'\h' for help. Type'\c' toclear the current input statement.
mysql> show databases;
+--------------------+
| Database?????????? |
+--------------------+
| information_schema |
| mysql????????????? |
| performance_schema |
|test?????????????? |
+--------------------+
4 rowsin set (0.01 sec)
mysql> create database mydb3;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database?????????? |
+--------------------+
| information_schema |
| mydb3????????????? |
| mysql????????????? |
| performance_schema |
|test?????????????? |
+--------------------+
5 rowsin set (0.00 sec)
4.登錄[mysqld4]
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[root@node1 ~]# /data/mysql/bin/mysql -uroot -h127.0.0.1 -P3309 -p
Enter password:
Welcome to the MySQL monitor.? Commands end with ; or \g.
Your MySQL connectionid is 1
Server version: 5.5.35-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type'help;' or'\h' for help. Type'\c' toclear the current input statement.
mysql> show databases;
+--------------------+
| Database?????????? |
+--------------------+
| information_schema |
| mysql????????????? |
| performance_schema |
|test?????????????? |
+--------------------+
4 rowsin set (0.01 sec)
mysql> create database mydb4;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database?????????? |
+--------------------+
| information_schema |
| mydb4????????????? |
| mysql????????????? |
| performance_schema |
|test?????????????? |
+--------------------+
5 rowsin set (0.00 sec)
好了，我們的MySQL多實例登錄就演示到這里了，下面我們來設置一下mysql的root密碼。
十一、其它管理配置
1.為mysql的root用戶創建密碼
1
2
3
4[root@node1 ~]# /data/mysql/bin/mysqladmin -uroot -h127.0.0.1 -P3306 password '123456'
[root@node1 ~]# /data/mysql/bin/mysqladmin -uroot -h127.0.0.1 -P3307 password '123456'
[root@node1 ~]# /data/mysql/bin/mysqladmin -uroot -h127.0.0.1 -P3308 password '123456'
[root@node1 ~]# /data/mysql/bin/mysqladmin -uroot -h127.0.0.1 -P3309 password '123456'
2.刪除匿名連接的空密碼帳號
注，分別登錄實例[mysqld1]、[mysqld2]、[mysqld3]、[mysqld4]，執行以下命令：
1
2
3
4
5
6mysql>use mysql;#選擇系統數據庫mysql
mysql>select Host,User,Password from user;#查看所有用戶
mysql>delete from user where password="";#刪除無密碼賬戶
mysql>flush privileges;#刷新權限
mysql>select Host,User,Password from user;#確認密碼為空的用戶是否已全部刪除
mysql>exit;
十二、總結
1.采用源碼編譯安裝MySQL，可能在第一次會花費較多的時間，但卻是非常值得的，因為我們可以自己組織所有MySQL相關文件的位置；并且經過源碼編譯安裝后的MySQL，可以直接復制到其它服務器上運行，大大方便了我們以后的遷移、備份和新服務器的配置。
2.本文中僅僅用了四個實例[mysqld1]、[mysqld2]、[mysqld3]、[mysqld4]來舉例，實際上我們可以通過這樣的方式，實現[mysqld5]、[mysqld6]...等更多的實例，前提是你的服務器硬件配置得根得 上，但是一般我們這邊不會超過6個實例。
3.在單機運行多實例的情況下，切忌不要使用 mysql -hlocalhost 或 直接忽略-h參數登錄服務器，這應該算是MySQL的一個bug，就是如果使用localhost或忽略-h參數，而不是指定127.0.0.1的話，即使選擇的端口是3307，還是會登陸到3306中去，因此應盡量避免這種混亂的產生，統一用127.0.0.1綁定端口或采用socket來登錄，在mysql5.5中你不指定-h127.0.0.1選項，你是無法登錄的。
最后，希望大家有所收獲吧^_^……
總結
以上是生活随笔為你收集整理的mysql 5.5.35 单机多实例配置详解_MySQL 5.5.35 单机多实例配置详解的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： asterisk cdr mysql_在
- 下一篇： 联想主板怎么进去bios设置界面 联想电