MySQL 5.5.22 单机多实例配置实践
在一台服务器上通过源码编译安装一个版本为5.5以上的MySQL数据库；将所有配置文件与数据等均存放在/opt/mysql，便于今后实现快速迁移、整体备份和快速复制；
在同一个MySQL中运行两个实例，一个绑定在端口3306，另一个绑定在端口3307；绑定在3306端口的实例，不开启binlog，数据存放在/opt/mysql/data；绑定在3307端口的实例，开启binlog，数据存放在/opt/mysql/data2；两个实例均采用InnoDB作为默认的存储引擎，字符编码采用UTF-8；两个实例均采用相同的性能优化配置参数；
在编译安装时，将数据库的配置文件my.cnf以及data目录等均指向到/opt/mysql目录；通过mysqld_multi的方式来管理两个不同的实例，采用相同的配置文件共享性能优化配置参数；在同一个配置文件中，利用[mysqld1]与[mysqld2]标签实现不同实例的差异化配置；
MySQL从5.5版本开始，通过./configure进行编译配置方式已经被取消，取而代之的是cmake工具。
因此，我们首先要在系统中源码编译安装cmake工具。
# wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz
# tar zxvf cmake-2.8.4.tar.gz
# cd cmake-2.8.4
# ./configure
# make
# make install
通过 rpm -qa | grep name 的方式验证以下软件包是否已全部安装。
gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool*
如果缺少相关的软件包，可通过yum -y install 的方式在线安装，或直接从系统安装光盘中找到并通过rpm -ivh 的方式安装。
建立mysql安装目录及数据存放目录
# mkdir /opt/mysql
# mkdir /opt/mysql/data
创建用户和用户组
# groupadd mysql
# useradd -g mysql mysql
赋予数据存放目录权限
# chown mysql:mysql -R /opt/mysql/data
通过http://www.mysql.com/downloads/mysql官方网址或国内的sohu镜像下载软件包，如目前最新的MySQL 5.5.22。
# wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.22.tar.gz
# tar zxvf mysql-5.5.22.tar.gz
# cd mysql-5.5.22
# cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql /
-DSYSCONFDIR=/opt/mysql/etc /
-DMYSQL_DATADIR=/opt/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
# make
# make install
在make与make install的时候可以看到进度百分比，感觉这一点要比configure方式要好。
进入MySQL主目录
# cd /opt/mysql/
删除默认的data目录
# rm -rf data
创建需要的目录
# mkdir etc tmp init.d run log binlogs data data2
# chown -R mysql:mysql tmp run init.d log binlogs data data2
创建my.cnf配置文件
# vim etc/my.cnf
## This server may run 2+ separate instances
## So we use mysqld_multi to manage their services
[mysqld_multi]
mysqld = /opt/mysql/bin/mysqld_safe
mysqladmin = /opt/mysql/bin/mysqladmin
log = /opt/mysql/log/mysqld_multi.log
user = root ## Used for stopping the server via mysqladmin
#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 = /opt/mysql/run/mysqld.sock
port = 3306
pid-file = /opt/mysql/run/mysqld.pid
datadir = /opt/mysql/data
lc-messages-dir = /opt/mysql/share/english
## These support master - master replication
#auto-increment-increment = 4
#auto-increment-offset = 1 ## Since it is master 1
#log-bin = /opt/mysql/binlogs/bin-log-mysqld1
#log-bin-index = /opt/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 /opt/mysqld/data2
[mysqld2]
socket = /opt/mysql/run/mysqld.sock2
port = 3307
pid-file = /opt/mysql/run/mysqld.pid2
datadir = /opt/mysql/data2
lc-messages-dir = /opt/mysql/share/english
## Disable DNS lookups
#skip-name-resolve
## These support master - slave replication
log-bin = /opt/mysql/binlogs/bin-log-mysqld2
log-bin-index = /opt/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 = /opt/mysql/log/relay-log-mysqld2
#relay-log-index = /opt/mysql/log/relay-log-mysqld2.index
#relay-log-space-limit = 4G
## Slow query log settings
#log-slow-queries = /opt/mysql/log/slow-log-mysqld2
#long_query_time = 2
#log-queries-not-using-indexes
## The rest of the my.cnf is shared
## Here follows entries for some specific programs
## The MySQL server
[mysqld]
basedir = /opt/mysql
tmpdir = /opt/mysql/tmp
socket = /opt/mysql/run/mysqld.sock
port = 3306
pid-file = /opt/mysql/run/mysqld.pid
datadir = /opt/mysql/data
lc-messages-dir = /opt/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 = 200
## 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
## Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /opt/mysql/data
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /opt/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 = /opt/mysql/log/mysqld.log
pid-file = /opt/mysql/run/mysqld.pid
open-files-limit = 8192
[client]
default-character-set = utf8
修改my.cnf读写权限，避免普通用户获取到MySQL密码
# chown -R root:root /opt/mysql/etc
# chmod 600 /opt/mysql/etc/my.cnf
切换到mysql用户
# su - mysql
进入MySQL主目录
# cd /opt/mysql/
初始化实例[mysqld1]
# scripts/mysql_install_db --basedir=/opt/mysql --user=mysql --datadir=/opt/mysql/data/
初始化实例[mysqld2]
# scripts/mysql_install_db --basedir=/opt/mysql --user=mysql --datadir=/opt/mysql/data2/
返回到root
# exit
创建mysqld_multi.server脚本
# cp support-files/mysqld_multi.server /opt/mysql/init.d/
# vim /opt/mysql/init.d/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=/opt/mysql
bindir=/opt/mysql/bin
conf=/opt/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
# cd /opt/
# tar czvf mysql_5.5.22_full.tar.gz mysql/
备份完成后，直接将mysql_5.5.20_full.tar.gz拿到其他服务器上，解压后便可以直接启用。
同时 启动/关闭 实例 [mysqld1] 与 [mysqld2]：
# /opt/mysql/init.d/mysqld_multi.server start 1,2
然后，可以看到两个MySQL实例都已经成功的启动了。
# netstat -lntp | grep mysqld
同时 关闭 实例 [mysqld1] 与 [mysqld2]：
# /opt/mysql/init.d/mysqld_multi.server stop 1,2
仅 启动/关闭 实例[mysqld1]：
# /opt/mysql/init.d/mysqld_multi.server start 1
# /opt/mysql/init.d/mysqld_multi.server stop 1
在启动了实例[mysqld1]与[mysqld2]后，通过以下方式登陆不同的实例：
登陆[mysqld1]：
# /opt/mysql/bin/mysql -uroot -h127.0.0.1 -P3306 -p
登陆[mysqld2]：
# /opt/mysql/bin/mysql -uroot -h127.0.0.1 -P3307 -p
# /opt/mysql/bin/mysqladmin -u root -h127.0.0.1 -P3306 password 'new-password'
# /opt/mysql/bin/mysqladmin -u root -h127.0.0.1 -P3307 password 'new-password'
# vim /opt/mysql/etc/my.cnf
user = root ## Used for stopping the server via mysqladmin
password = new-password
分别登陆实例[mysqld1]与[mysqld2]，执行以下命令：
mysql>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]...等更多的实例；
3.MySQL自带了几个不同的配置文件，放置在/opt/mysql/support-files目录下，分别是my-huge.cnf,my-innodb-heavy-4G.cnf,my-large.cnf,my-medium.cnf,my-small.cnf，通过名称我们可以很直观的了解到他们是针对不同的服务器配置的，本文中仅有的一点关于InnoDB的配置，是取自于my-small.cnf的，因为我是在虚拟机上进行的设置；在生产环境中，我们可以通过参考my-huge.cnf或my-innodb-heavy-4G.cnf中的部分参数配置，来对服务器进行优化；
4.在单机运行多实例的情况下，切忌使用 mysql -hlocalhost 或 直接忽略-h参数 登陆服务器，这应该算是MySQL的一个bug，就是如果使用localhost或忽略-h参数，而不是指定127.0.0.1的话，即使选择的端口是3307，还是会登陆到3306中去，因此应尽量避免这种混乱的产生，统一用127.0.0.1绑定端口 或 采用socket 来登陆；
本文出自 “xmshuiyong’blog” 博客，请务必保留此出处http://xmshuiyong.blog.51cto.com/1980172/1113498
How to solve invalid synrax
What to do if windows photo viewer is out of memory
Common encryption methods for data encryption storage
Introduction to foreign free vps software
The difference between scratch and python
What does network access denied mean wifi?
Port 8080 is occupied
SQL 5120 error solution
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
