MySQL5.5.35单机多实例配置详解

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

1

2

3

[root@node1 src]# yum install -y ntp

[root@node1 src]# ntpdate 202.120.2.101

[root@node1 src]# hwclock –w

1

[root@node1 ~]# yum install -y autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool* openssl*

1

[root@node1 ~]# yum install -y cmake

1

2

[root@node1 ~]# mkdir /data/mysql

[root@node1 ~]# mkdir /data/mysql/data

1

2

3

[root@node1 ~]# useradd mysql

[root@node1 ~]# id mysql 

uid=500(mysql) gid=500(mysql)组=500(mysql)

1

2

[root@node1 ~]# chown -R mysql.mysql /data/mysql/ 

[root@node1 ~]# chown -R mysql.mysql /data/mysql/data

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

1

2

[root@node1 ~]# cd /data/mysql/ 

[root@node1 mysql]# rm -rf data

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

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

1

2

[root@node1 etc]# chown -R root.root /data/mysql/etc

[root@node1 etc]# chmod 600 /data/mysql/etc/my.cnf

1

[root@node1 ~]# cd /data/mysql/

1

[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data --user=mysql

1

[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data2 --user=mysql

1

[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data3 --user=mysql

1

[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data4 --user=mysql

1

[root@node1 mysql]# mkdir /data/mysql/init.d

1

[root@node1 mysql]# cp support-files/mysqld_multi.server init.d/

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

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

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    &
网页名称：MySQL5.5.35单机多实例配置详解
当前地址：http://cdkjz.cn/article/jejjsc.html