背景需求：

1)在一台新采购的服务器上通过源码编译安装一个版本为5.5.x以上的MySQL数据库，并将所有配置文件与数据等均存放在/opt/mysql，以便于今后实现快速迁移、复制和整体备份； 2)在同一个MySQL中运行两个实例，一个绑定在端口3306，一个绑定在端口3307；绑定在3306端口的实例，不开启binlog，数据存放在/opt/mysql/data；绑定在3307端口的实例，开启binlog，数据存放在/opt/mysql/data2； 3)两个实例均采用InnoDB作为默认的存储引擎，字符编码采用UTF-8；两个实例均采用相同的性能优化配置参数，实现单机多实例的配置应用。

实施方案：

在编译安装时，将数据库的配置文件my.cnf以及data目录等均指向到/opt/mysql目录；通过mysqld_multi的方式来管理两个不同的实例，采用相同的配置文件共享性能优化配置参数；在同一个配置文件中，利用[mysqld1]与[mysqld2]标签实现不同实例的差异化配置。

配置过程：

1、源码编译安装MySQL 1)安装所需系统软件包 # yum -y install gcc gcc-c++ autoconf bison libjpeg libjpeg-devel libpng libpng-devel freetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glibc glibc-devel glib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel openssl openssl-devel openldap openldap-devel nss_ldap openldap-clients openldap-servers

2)安装前的系统设置

# mkdir /opt/mysql  //mysql安装目录 # mkdir /opt/mysql/data  //mysql数据存放目录 # groupadd mysql  //创建用户 # useradd -g mysql mysql //创建用户组 # chown mysql:mysql -R /opt/mysql/data //赋予数据存放目录权限

3)安装cmake

MySQL从5.5版本开始，通过./configure进行编译配置方式已经被取消，取而代之的是cmake工具。因此，需要先在系统中源码编译安装cmake。 # mkdir /home/tools # cd /home/tools # wget # tar xf cmake-2.8.4.tar.gz # cd cmake-2.8.4 # ./configure # make;make install

4)开始编译安装MySQL

# wget # tar xf mysql-5.5.19.tar.gz # cd mysql-5.5.19 # 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

2、创建单机支持多实例

1)进入MySQL主目录 # cd /opt/mysql/

2)删除默认的data目录

# rm -rf data

3)创建需要的目录

# mkdir etc tmp run log binlogs data data2 # chown -R mysql:mysql tmp run log binlogs data data2

4)创建my.cnf配置文件

# vi 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/mysql/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

5)修改my.cnf读写权限，避免普通用户获取到MySQL密码 # chown -R root:root /opt/mysql/etc # chmod 600 /opt/mysql/etc/my.cnf

3、初始化数据库

1)切换到mysql用户，进入MySQL主目录 # su - mysql  # cd /opt/mysql/ 

2)初始化实例[mysqld1]和[mysqld2]

# scripts/mysql_install_db --basedir=/opt/mysql --user=mysql --datadir=/opt/mysql/data/ # scripts/mysql_install_db --basedir=/opt/mysql --user=mysql --datadir=/opt/mysql/data2/

3)回到root，创建mysqld_multi.server脚本

# exit # mkdir -p /opt/mysql/init.d # cp support-files/mysqld_multi.server /opt/mysql/init.d/ # vi /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

4、整体备份MySQL # cd /opt/ # tar czvf mysql_5.5.19_full.tar.gz mysql5519/

备份完成后，直接将mysql_5.5.19_full.tar.gz拿到其他服务器上，解压后便可以直接启用。

5、管理MySQL实例

1)同时启动实例 [mysqld1] 与 [mysqld2]： # /opt/mysql/init.d/mysqld_multi.server start 1,2

2)查看两个MySQL实例是否都已经成功启动

# netstat -lntp | grep mysqld

tcp        0      0 0.0.0.0:3306        0.0.0.0:*         LISTEN      28752/mysqld        tcp        0      0 0.0.0.0:3307        0.0.0.0:*         LISTEN      28756/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

6、登陆MySQL实例

在启动了实例[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

7、其他初始化设置

1)为MySQL的root帐户设置初始密码 # /opt/mysql/bin/mysqladmin -u root -h127.0.0.1 -P3306 password '123456' # /opt/mysql/bin/mysqladmin -u root -h127.0.0.1 -P3307 password '123456'

2)修改my.cnf配置文件中MySQL的root账户密码

# vim /opt/mysql/etc/my.cnf

user = root ## Used for stopping the server via mysqladmin  password = 123456

3)删除匿名连接的空密码帐号 分别登陆实例[mysqld1]与[mysqld2]，执行以下命令： mysql> use mysql; //选择系统数据库mysql

Database changed

mysql> select Host,User,Password from user; //查看所有用户

+-----------+------+-------------------------------------------+

| Host      | User | Password                                  |

+-----------+------+-------------------------------------------+

| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| mysql-01  | root |                                           |

| 127.0.0.1 | root |                                           |

| ::1       | root |                                           |

| localhost |      |                                           |

| mysql-01  |      |                                           |

+-----------+------+-------------------------------------------+

6 rows in set (0.00 sec)

mysql> delete from user where password=""; //删除匿名连接的空密码帐号

Query OK, 5 rows affected (0.00 sec)

mysql> flush privileges;  //刷新权限

Query OK, 0 rows affected (0.00 sec)

mysql> select Host,User,Password from user; //确认密码为空的用户是否已全部删除

+-----------+------+-------------------------------------------+

| Host      | User | Password                                  |

+-----------+------+-------------------------------------------+

| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

+-----------+------+-------------------------------------------+

1 row in set (0.00 sec)

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，通过名称我们可以很直观的了解到他们是针对不同的服务器配置的； 4、在单机运行多实例的情况下，不要使用mysql -hlocalhost或直接忽略-h参数登陆服务器，因为如果使用localhost或忽略-h参数，而不是指定127.0.0.1的话，即使选择的端口是3307，还是会登陆到3306实例中去，所以尽量避免这种混乱的产生，统一用127.0.0.1绑定端口或采用socket来登陆。 BTW：如果大家认为我写的还可以，希望能给我的博客投个票，谢谢！O(∩_∩)O