mysql 5.5.35 单机多实例配置详解_MySQL 5.5.35 单机多实例配置详解

一、前言二、概述三、环境准备四、安装MySQL5.5.35五、新建支持多实例的配置文件(我这里配置的是四个实例)六、初始化多实例数据库七、提供管理脚本mysqld_mult

2.背景/需求、注意事项

(1).背景与需求

(2).注意事项

三、环境准备

1.安装yum源[root@node1 src]# wget

[root@node1 src]# rpm -ivh epel-release-6-8.noarch.rpm

2.同步时间[root@node1 src]# yum install -y ntp

[root@node1 src]# ntpdate 202.120.2.101

[root@node1 src]# hwclock –w

3.安装mysql5.5依赖包[root@node1 ~]# yum install -y autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool* openssl*

4.安装cmake[root@node1 ~]# yum install -y cmake

四、安装MySQL 5.5.35

1.创建安装目录与数据存放目录[root@node1 ~]# mkdir /data/mysql

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

2.创建mysql用户与组[root@node1 ~]# useradd mysql

[root@node1 ~]# id mysql

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

3.授权安装目录与数据目录[root@node1 ~]# chown -R mysql.mysql /data/mysql/

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

4.安装mysql[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.删除默认的数据目录[root@node1 ~]# cd /data/mysql/

[root@node1 mysql]# rm -rf data

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.提供配置文件[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

本文原创发布php中文网，转载请注明出处，感谢您的尊重！