带来一篇临盆情况MySQL 5.5.x单机多实例设置理论

小知识：CentOS在服务器提供商、中小型公司中装机量几乎是最大的Linux发行版。
背景需求：
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-yinstallgccgcc-c++autoconfbisonlibjpeglibjpeg-devellibpnglibpng-develfreetypefreetype-devellibxml2libxml2-develzlibzlib-develglibcglibc-develglib2glib2-develbzip2bzip2-develncursesncurses-develcurlcurl-devele2fsprogse2fsprogs-develkrb5krb5-devellibidnlibidn-developensslopenssl-developenldapopenldap-develnss_ldapopenldap-clientsopenldap-servers
2)装置前的体系设置
#mkdir/opt/mysql//mysql装置目次
#mkdir/opt/mysql/data//mysql数据寄存目次
#groupaddmysql//创立用户
#useradd-gmysqlmysql//创立用户组
#chownmysql:mysql-R/opt/mysql/data//付与数据寄存目次权限
3)装置cmake
MySQL从5.5版本入手下手，经由过程./configure举行编译设置体例已被作废，取而代之的是cmake工具。因而，必要先在体系中源码编译装置cmake。
#mkdir/home/tools
#cd/home/tools
#wgethttp://www.ckuyun.com/files/v2.8/cmake-2.8.4.tar.gz
#tarxfcmake-2.8.4.tar.gz
#cdcmake-2.8.4
#./configure
#make;makeinstall
4)入手下手编译装置MySQL
#wgethttp://downloads.mysql.com/archives/mysql-5.5/mysql-5.5.19.tar.gz
#tarxfmysql-5.5.19.tar.gz
#cdmysql-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;makeinstall
2、创立单机撑持多实例
1)进进MySQL主目次
#cd/opt/mysql/
2)删除默许的data目次
#rm-rfdata
3)创立必要的目次
#mkdiretctmprunlogbinlogsdatadata2
#chown-Rmysql:mysqltmprunlogbinlogsdatadata2
4)创立my.cnf设置文件
#vietc/my.cnf
##Thisservermayrun2+separateinstances
##Soweusemysqld_multitomanagetheirservices
[mysqld_multi]
mysqld=/opt/mysql/bin/mysqld_safe
mysqladmin=/opt/mysql/bin/mysqladmin
log=/opt/mysql/log/mysqld_multi.log
user=root##Usedforstoppingtheserverviamysqladmin
#password=
##Thisisthegeneralpurposedatabase
##Thelocationsaredefault
#Theyareleftin[mysqld]incasetheserverisstartednormallyinsteadofbymysqld_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

##Thesesupportmaster-masterreplication
#auto-increment-increment=4
#auto-increment-offset=1##Sinceitismaster1
#log-bin=/opt/mysql/binlogs/bin-log-mysqld1
#log-bin-index=/opt/mysql/binlogs/bin-log-mysqld1.index
#binlog-do-db=##Leavethisblankifyouwanttocontrolitonslave
#max_binlog_size=1024M

##Thisisexlusivelyformysqld2
##Itison3307withdatadirectory/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

##DisableDNSlookups
#skip-name-resolve

##Thesesupportmaster-slavereplication
log-bin=/opt/mysql/binlogs/bin-log-mysqld2
log-bin-index=/opt/mysql/binlogs/bin-log-mysqld2.index
#binlog-do-db=##Leavethisblankifyouwanttocontrolitonslave
max_binlog_size=1024M

##Relaylogsettings
#relay-log=/opt/mysql/log/relay-log-mysqld2
#relay-log-index=/opt/mysql/log/relay-log-mysqld2.index
#relay-log-space-limit=4G

##Slowquerylogsettings
#log-slow-queries=/opt/mysql/log/slow-log-mysqld2
#long_query_time=2
#log-queries-not-using-indexes

##Therestofthemy.cnfisshared
##Herefollowsentriesforsomespecificprograms
##TheMySQLserver
[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

##Increasethemaxconnections
max_connections=200

##Theexpirationtimeforlogs,includingbinlogs
expire_logs_days=14

##Setthecharacterasutf8
character-set-server=utf8
collation-server=utf8_unicode_ci

##Thisisusuallyonlyneededwhensettingupchainedreplication
#log-slave-updates

##Enablethistomakereplicationmoreresilientagainstservercrashesandrestarts
##butcancausehigherI/Oontheserver
#sync_binlog=1

##Theserverid,shouldbeuniqueinsamenetwork
server-id=1

##SetthistoforceMySQLtouseaparticularengine/table-typefornewtables
##Thissettingcanstillbeoverriddenbyspecifyingtheengineexplicitly
##intheCREATETABLEstatement
default-storage-engine=INNODB

##UncommentthefollowingifyouareusingInnoDBtables
#innodb_data_home_dir=/opt/mysql/data
#innodb_data_file_path=ibdata1:10M:autoextend
#innodb_log_group_home_dir=/opt/mysql/data
##Youcanset.._buffer_pool_sizeupto50-80%ofRAM
##butbewareofsettingmemoryusagetoohigh
innodb_buffer_pool_size=16M
innodb_additional_mem_pool_size=2M
##Set.._log_file_sizeto25%ofbufferpoolsize
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-Rroot:root/opt/mysql/etc
#chmod600/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
#cpsupport-files/mysqld_multi.server/opt/mysql/init.d/
#vi/opt/mysql/init.d/mysqld_multi.server
#!/bin/sh
#
#A***startupscriptformysqld_multibyTimSmithandJaniTolonen.
#Thisscriptassumesthatmy.cnffileexistseitherin/etc/my.cnfor
#/root/.my.cnfandhasgroups[mysqld_multi]and[mysqldN].Seethe
#mysqld_multidocumentationfordetailedinstructions.
#
#Thisscriptcanbeusedas/etc/init.d/mysql.server
#
#CommentstosupportchkconfigonRedHatLinux
#chkconfig:23456436
#description:AveryfastandreliableSQLdatabaseengine.
#
#Version1.0
#
basedir=/opt/mysql
bindir=/opt/mysql/bin
conf=/opt/mysql/etc/my.cnf
exportPATH=$PATH:$bindir
iftest-x$bindir/mysqld_multi
then
mysqld_multi="$bindir/mysqld_multi";
else
echo"Cantexecute$bindir/mysqld_multifromdir$basedir";
exit;
fi
case"$1"in
start)
"$mysqld_multi"--defaults-extra-file=$confstart$2
;;
stop)
"$mysqld_multi"--defaults-extra-file=$confstop$2
;;
report)
"$mysqld_multi"--defaults-extra-file=$confreport$2
;;
restart)
"$mysqld_multi"--defaults-extra-file=$confstop$2
"$mysqld_multi"--defaults-extra-file=$confstart$2
;;
*)
echo"Usage:$0{start|stop|report|restart}">&2
;;
esac
4、全体备份MySQL
#cd/opt/
#tarczvfmysql_5.5.19_full.tar.gzmysql5519/

备份完成后，间接将mysql_5.5.19_full.tar.gz拿到其他办事器上，解压后即可以间接启用。
5、办理MySQL实例
1)同时启动实例[mysqld1]与[mysqld2]：
#/opt/mysql/init.d/mysqld_multi.serverstart1,2
2)检察两个MySQL实例是不是都已乐成启动
#netstat-lntp|grepmysqld
tcp000.0.0.0:33060.0.0.0:*LISTEN28752/mysqld
tcp000.0.0.0:33070.0.0.0:*LISTEN28756/mysqld同时封闭实例[mysqld1]与[mysqld2]：
#/opt/mysql/init.d/mysqld_multi.serverstop1,2

仅启动/封闭实例[mysqld1]：
#/opt/mysql/init.d/mysqld_multi.serverstart1
#/opt/mysql/init.d/mysqld_multi.serverstop1
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-uroot-h127.0.0.1-P3306password123456
#/opt/mysql/bin/mysqladmin-uroot-h127.0.0.1-P3307password123456
2)修正my.cnf设置文件中MySQL的root账户暗码
#vim/opt/mysql/etc/my.cnf
user=root##Usedforstoppingtheserverviamysqladmin
password=1234563)删除匿名毗连的空暗码帐号
分离上岸实例[mysqld1]与[mysqld2]，实行以下下令：
mysql>usemysql;//选择体系数据库mysql
Databasechanged
mysql>selectHost,User,Passwordfromuser;//检察一切用户
+-----------+------+-------------------------------------------+
|Host|User|Password|
+-----------+------+-------------------------------------------+
|localhost|root|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9|
|mysql-01|root||
|127.0.0.1|root||
|::1|root||
|localhost|||
|mysql-01|||
+-----------+------+-------------------------------------------+
6rowsinset(0.00sec)mysql>deletefromuserwherepassword="";//删除匿名毗连的空暗码帐号
QueryOK,5rowsaffected(0.00sec)
mysql>flushprivileges;//革新权限
QueryOK,0rowsaffected(0.00sec)
mysql>selectHost,User,Passwordfromuser;//确认暗码为空的用户是不是已全体删除
+-----------+------+-------------------------------------------+
|Host|User|Password|
+-----------+------+-------------------------------------------+
|localhost|root|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9|
+-----------+------+-------------------------------------------+
1rowinset(0.00sec)
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来上岸。小知识：CentOS并不包含封闭源代码软件。

其次，Linux简单易学，因为我们初学者只是学的基础部分，Linux的结构体系非常清晰，再加上老师循序渐进的教学以及耐心的讲解，使我们理解起来很快，短期内就基本掌握了操作和运行模式。

以前觉得Linux就跟dos一样，全是用命令窗口，相对于窗口界面来说多麻烦呀。

为什么要学Linux呢？每个人都有不同的看法，下面我说说自己的感想吧。?

掌握在Linux系统中安装软件，在安装Linux工具盘后大致日常所需的软件都会有,一般网络提供下载的软件都会有安装说明。

我是学习嵌入式方向的，这学期就选修了这门专业任选课。

linux鸟哥的私房菜，第三版，基础篇，网上有pdf下的，看它的目录和每章的介绍就行了，这个绝对原创！

编程学习及开发，Linux是免费，开源的操作系统，并且可开发工具相当多，如果您支持自由软件，一定要同广大热爱自由软件人士一同为其不懈努力。

如果上面的措施没有解决问题，此时你就需要Linux社区的帮助了。 Linux的使用者一般都是专业人士，他们有着很好的电脑背景且愿意协助他人。