CentOS6.5 上部署 MySQL5.7.17 二进制安装以及多实例配置
1、建用户、下载、解压
groupaddmysql useradd-r-gmysqlmysql wgethttp://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz tarxvfmysql-5.7.17-linux-glibc2.5-x86_64.tar.gz-C/usr/local/ ln-sv/usr/local/mysql-5.7.17-linux-glibc2.5-x86_64/usr/local/mysql
2、输出环境变量、帮助
/etc/profile exportPATH=$PATH:/usr/local/mysql/bin source/etc/profile vim/etc/man.config MANPATH/usr/local/mysql/man
3、创建数据目录、授权、初始化mysqlmysql5.7.7及以上做了很多改变，5.7.7以前安装方法和以前差不多，初始化也保留了mysql_install_db，5.7.7以后则去掉了该脚本，使用了-initialize或者--initialize-insecure参数作为初始化。
mysql5.7.14版本初始化时候已经抛弃了defaults-file参数文件，所以在初始化时候指定配置文件会出错，同时必须保证datadir为空。
mkdir/data/mysql chownmysql.mysql/data/mysql [root@leomysql]#mysqld--verbose--help|more查看更多初始化参数 [root@leomysql]#/usr/local/mysql/bin/mysqld--initialize-insecure--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql 2016-12-21T09:37:13.532770Z0[Warning]TIMESTAMPwithimplicitDEFAULTvalueisdeprecated.Pleaseuse--explicit_defaults_for_timestampserveroption(seedocumentationformoredetails). 2016-12-21T09:37:16.364569Z0[Warning]InnoDB:Newlogfilescreated,LSN=45790 2016-12-21T09:37:16.881727Z0[Warning]InnoDB:Creatingforeignkeyconstraintsystemtables. 2016-12-21T09:37:17.115686Z0[Warning]NoexistingUUIDhasbeenfound,soweassumethatthisisthefirsttimethatthisserverhasbeenstarted.GeneratinganewUUID:0fbca93f-c761-11e6-9409-000c299a8601. 2016-12-21T09:37:17.220886Z0[Warning]Gtidtableisnotreadytobeused.Table'mysql.gtid_executed'cannotbeopened. 2016-12-21T09:37:17.284087Z1[Warning]root@localhostiscreatedwithanemptypassword!Pleaseconsiderswitchingoffthe--initialize-insecureoption.
初始化成功了。这里说明下，初始化参数我使用了--initialize-insecure，这样不会设置初始化root密码，如果是--initialize的话，会随机生成一个密码：
4、设置加密连接
[root@leomysql]#/usr/local/mysql/bin/mysql_ssl_rsa_setup Generatinga2048bitRSAprivatekey ..........................................................+++ .......+++ writingnewprivatekeyto'ca-key.pem' ----- Generatinga2048bitRSAprivatekey ...........+++ ...........................................+++ writingnewprivatekeyto'server-key.pem' ----- Generatinga2048bitRSAprivatekey ............................................+++ .............................................................+++ writingnewprivatekeyto'client-key.pem' -----
5、复制配置文件和启动脚本
[root@leomysql]#cp/usr/local/mysql/support-files/my-default.cnf/etc/my.cnf [root@leomysql]#cp/usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld
6、配置如下
[root@leomysql]#cat/etc/my.cnf #Foradviceonhowtochangesettingspleasesee #http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html #***DONOTEDITTHISFILE.It'satemplatewhichwillbecopiedtothe #***defaultlocationduringinstall,andwillbereplacedifyou #***upgradetoanewerversionofMySQL. [mysqld] #Removeleading#andsettotheamountofRAMforthemostimportantdata #cacheinMySQL.Startat70%oftotalRAMfordedicatedserver,else10%. #innodb_buffer_pool_size=128M #Removeleading#toturnonaveryimportantdataintegrityoption:logging #changestothebinarylogbetweenbackups. #log_bin #Thesearecommonlyset,removethe#andsetasrequired. basedir=/usr/local/mysql datadir=/data/mysql port=3306 server_id=1 socket=/data/mysql/mysql.sock symbolic-links=0 character_set_server=utf8 #Removeleading#tosetoptionsmainlyusefulforreportingservers. #TheserverdefaultsarefasterfortransactionsandfastSELECTs. #Adjustsizesasneeded,experimenttofindtheoptimalvalues. #join_buffer_size=128M #sort_buffer_size=2M #read_rnd_buffer_size=2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [client] socket=/data/mysql/mysql.sock
7、启动、并查看日志
[root@leo~]#/etc/init.d/mysqldstart StartingMySQL.Loggingto'/data/mysql/leo.err'. .[确定] [root@leo~]#/etc/init.d/mysqldrestart ShuttingdownMySQL..[确定] StartingMySQL..[确定] [root@leo~]#tail-f/data/mysql/leo.err 2016-12-21T09:42:48.493804Z0[Note]-'::'resolvesto'::'; 2016-12-21T09:42:48.493834Z0[Note]ServersocketcreatedonIP:'::'. 2016-12-21T09:42:48.493990Z0[Note]InnoDB:Loadingbufferpool(s)from/data/mysql/ib_buffer_pool 2016-12-21T09:42:48.496446Z0[Note]InnoDB:Bufferpool(s)loadcompletedat16122117:42:48 2016-12-21T09:42:48.512719Z0[Note]EventScheduler:Loaded0events 2016-12-21T09:42:48.512907Z0[Note]Executing'SELECT*FROMINFORMATION_SCHEMA.TABLES;'togetalistoftablesusingthedeprecatedpartitionengine.Youmayusethestartupoption'--disable-partition-engine-check'toskipthischeck. 2016-12-21T09:42:48.512922Z0[Note]Beginningoflistofnon-nativelypartitionedtables 2016-12-21T09:42:48.529189Z0[Note]Endoflistofnon-nativelypartitionedtables 2016-12-21T09:42:48.529703Z0[Note]/usr/local/mysql/bin/mysqld:readyforconnections. Version:'5.7.17'socket:'/data/mysql/mysql.sock'port:3306MySQLCommunityServer(GPL)
8、登录测试
[root@leomysql]#mysql WelcometotheMySQLmonitor.Commandsendwith;or\g. YourMySQLconnectionidis5 Serverversion:5.7.17MySQLCommunityServer(GPL) Copyright(c)2000,2016,Oracleand/oritsaffiliates.Allrightsreserved. OracleisaregisteredtrademarkofOracleCorporationand/orits affiliates.Othernamesmaybetrademarksoftheirrespective owners. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement. mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |mysql| |performance_schema| |sys| +--------------------+ 4rowsinset(0.01sec) mysql> [root@leomysql]#ps-ef|grepmysql root493051017:54pts/000:00:00/bin/sh/usr/local/mysql/bin/mysqld_safe--datadir=/data/mysql--pid-file=/data/mysql/leo.pid mysql4950149305017:54pts/000:00:01/usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql--datadir=/data/mysql--plugin-dir=/usr/local/mysql/lib/plugin--user=mysql--log-error=/data/mysql/leo.err--pid-file=/data/mysql/leo.pid--socket=/data/mysql/mysql.sock--port=3306 root4959846306018:14pts/000:00:00grepmysql
多实例配置部分
1、复制多实例启动脚本
[root@leo~]#cp/usr/local/mysql/support-files/mysqld_multi.server/etc/init.d/mysqld_multi
2、初始化数据库目录并修改多实例配置文件
mkdir/data/mysql2 mkdir/data/mysql3 chownmysql.mysql/data/mysql2 chownmysql.mysql/data/mysql3 [root@leo~]#/usr/local/mysql/bin/mysqld--initialize-insecure--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql2 2016-12-21T16:36:00.886650Z0[Warning]TIMESTAMPwithimplicitDEFAULTvalueisdeprecated.Pleaseuse--explicit_defaults_for_timestampserveroption(seedocumentationformoredetails). 2016-12-21T16:36:06.481686Z0[Warning]InnoDB:Newlogfilescreated,LSN=45790 2016-12-21T16:36:07.145444Z0[Warning]InnoDB:Creatingforeignkeyconstraintsystemtables. 2016-12-21T16:36:07.443823Z0[Warning]NoexistingUUIDhasbeenfound,soweassumethatthisisthefirsttimethatthisserverhasbeenstarted.GeneratinganewUUID:92945fc8-c79b-11e6-88a5-000c299a8601. 2016-12-21T16:36:07.453113Z0[Warning]Gtidtableisnotreadytobeused.Table'mysql.gtid_executed'cannotbeopened. 2016-12-21T16:36:07.456819Z1[Warning]root@localhostiscreatedwithanemptypassword!Pleaseconsiderswitchingoffthe--initialize-insecureoption. [root@leo~]#/usr/local/mysql/bin/mysqld--initialize-insecure--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql3 2016-12-21T16:36:16.094948Z0[Warning]TIMESTAMPwithimplicitDEFAULTvalueisdeprecated.Pleaseuse--explicit_defaults_for_timestampserveroption(seedocumentationformoredetails). 2016-12-21T16:36:21.224144Z0[Warning]InnoDB:Newlogfilescreated,LSN=45790 2016-12-21T16:36:21.900500Z0[Warning]InnoDB:Creatingforeignkeyconstraintsystemtables. 2016-12-21T16:36:22.095535Z0[Warning]NoexistingUUIDhasbeenfound,soweassumethatthisisthefirsttimethatthisserverhasbeenstarted.GeneratinganewUUID:9b500f71-c79b-11e6-8af0-000c299a8601. 2016-12-21T16:36:22.105950Z0[Warning]Gtidtableisnotreadytobeused.Table'mysql.gtid_executed'cannotbeopened. 2016-12-21T16:36:22.112685Z1[Warning]root@localhostiscreatedwithanemptypassword!Pleaseconsiderswitchingoffthe--initialize-insecureoption. [root@leo~]#cat/etc/my.cnf #Foradviceonhowtochangesettingspleasesee #http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html #***DONOTEDITTHISFILE.It'satemplatewhichwillbecopiedtothe #***defaultlocationduringinstall,andwillbereplacedifyou #***upgradetoanewerversionofMySQL. [mysqld_multi] mysqld=/usr/local/mysql/bin/mysqld_safe mysqladmin=/usr/local/mysql/bin/mysqladmin log=/data/mysql_multi.log user=root password=redhat #Removeleading#andsettotheamountofRAMforthemostimportantdata #cacheinMySQL.Startat70%oftotalRAMfordedicatedserver,else10%. #innodb_buffer_pool_size=128M #Removeleading#toturnonaveryimportantdataintegrityoption:logging #changestothebinarylogbetweenbackups. #log_bin #Thesearecommonlyset,removethe#andsetasrequired. [mysqld1] basedir=/usr/local/mysql datadir=/data/mysql port=3306 server_id=1 socket=/data/mysql/mysql.sock symbolic-links=0 character_set_server=utf8 pid-file=/data/mysql/mysql.pid [mysqld2] datadir=/data/mysql2 port=3307 socket=/data/mysql2/mysql.sock symbolic-links=0 character_set_server=utf8 pid-file=/data/mysql2/mysql2.pid [mysqld3] datadir=/data/mysql3 port=3308 socket=/data/mysql3/mysql.sock symbolic-links=0 character_set_server=utf8 pid-file=/data/mysql3/mysql3.pid #Removeleading#tosetoptionsmainlyusefulforreportingservers. #TheserverdefaultsarefasterfortransactionsandfastSELECTs. #Adjustsizesasneeded,experimenttofindtheoptimalvalues. #join_buffer_size=128M #sort_buffer_size=2M #read_rnd_buffer_size=2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #[client] #socket=/data/mysql/mysql.sock
3、启动多实例测试并查看日志
[root@leo~]#/etc/init.d/mysqld_multistart [root@leo~]#/etc/init.d/mysqld_multireport ReportingMySQLservers MySQLserverfromgroup:mysqld1isrunning MySQLserverfromgroup:mysqld2isrunning MySQLserverfromgroup:mysqld3isrunning [root@leo~]#netstat-antlp|grep:330* tcp00:::3307:::*LISTEN55762/mysqld tcp00:::3308:::*LISTEN55765/mysqld tcp00:::3306:::*LISTEN55764/mysqld
4、连接测试
[root@leo~]#mysql-uroot-h127.0.0.1-P3308-p
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis6
Serverversion:5.7.17MySQLCommunityServer(GPL)
Copyright(c)2000,2016,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|performance_schema|
|sys|
+--------------------+
4rowsinset(0.00sec)
mysql>grantshutdownon*.*to'root'@'%'identifiedby'redhat';#授权
QueryOK,0rowsaffected,1warning(0.08sec)
mysql>flushprovileges;
updatemysql.usersetauthentication_string=password('redhat')whereuser='root'andHost='localhost';#设置mysql登录密码
[root@leo~]#mysqladmin-uroot-p-S/data/mysql3/mysql.sockshutdown#关闭实例
Enterpassword:
5、附带脚本多实例管理脚本
stop关闭MySQL实例，注意此处是需要一个具有shutdown权限的用户，且密码并被是加密的，也不可以交互式输入密码，Linux又具有history功能，所以为了数据库的安全，还是不要用mysqld_multistop的方式关闭数据库了吧
[root@leo~]#cat/etc/init.d/mysqld_multi
#!/bin/sh
#
#Asimplestartupscriptformysqld_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=/usr/local/mysql
bindir=/usr/local/mysql/bin
conf=/etc/my.cnf
iftest-x$bindir/mysqld_multi
then
mysqld_multi="$bindir/mysqld_multi";
else
echo"Can'texecute$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--user=root--password=redhat
;;
'report')
"$mysqld_multi"--defaults-extra-file=$confreport$2
;;
'restart')
"$mysqld_multi"--defaults-extra-file=$confstop$2--user=root--password=redhat
"$mysqld_multi"--defaults-extra-file=$confstart$2
;;
*)
echo"Usage:$0{start|stop|report|restart}">&2
;;
esac
6、多实例测试启停
[root@leo~]#/etc/init.d/mysqld_multistop [root@leo~]#/etc/init.d/mysqld_multireport ReportingMySQLservers MySQLserverfromgroup:mysqld1isnotrunning MySQLserverfromgroup:mysqld2isnotrunning MySQLserverfromgroup:mysqld3isnotrunning [root@leo~]#/etc/init.d/mysqld_multistart [root@leo~]#/etc/init.d/mysqld_multireport ReportingMySQLservers MySQLserverfromgroup:mysqld1isrunning MySQLserverfromgroup:mysqld2isrunning MySQLserverfromgroup:mysqld3isrunning [root@leo~]#netstat-antlp|grep:330*#查看监听端口 tcp00:::3307:::*LISTEN74667/mysqld tcp00:::3308:::*LISTEN74666/mysqld tcp00:::3306:::*LISTEN74665/mysqld [root@leo~]#mysql-uroot-predhat-P3307-h127.0.0.1#登录3307测试 mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure. WelcometotheMySQLmonitor.Commandsendwith;or\g. YourMySQLconnectionidis4 Serverversion:5.7.17MySQLCommunityServer(GPL) Copyright(c)2000,2016,Oracleand/oritsaffiliates.Allrightsreserved. OracleisaregisteredtrademarkofOracleCorporationand/orits affiliates.Othernamesmaybetrademarksoftheirrespective owners. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement. mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |mysql| |performance_schema| |sys| |t1| +--------------------+ 5rowsinset(0.13sec) mysql> [root@leo~]#tail-f/data/mysql3/leo.err#查看日志 2016-12-21T21:47:56.114139Z0[Note]Serverhostname(bind-address):'*';port:3308 2016-12-21T21:47:56.145404Z0[Note]IPv6isavailable. 2016-12-21T21:47:56.169487Z0[Note]-'::'resolvesto'::'; 2016-12-21T21:47:56.171033Z0[Note]ServersocketcreatedonIP:'::'. 2016-12-21T21:47:57.157171Z0[Note]EventScheduler:Loaded0events 2016-12-21T21:47:57.157710Z0[Note]Executing'SELECT*FROMINFORMATION_SCHEMA.TABLES;'togetalistoftablesusingthedeprecatedpartitionengine.Youmayusethestartupoption'--disable-partition-engine-check'toskipthischeck. 2016-12-21T21:47:57.157729Z0[Note]Beginningoflistofnon-nativelypartitionedtables 2016-12-21T21:47:58.138317Z0[Note]Endoflistofnon-nativelypartitionedtables 2016-12-21T21:47:58.138474Z0[Note]/usr/local/mysql/bin/mysqld:readyforconnections. Version:'5.7.17'socket:'/data/mysql3/mysql.sock'port:3308MySQLCommunityServer(GPL)
mysqld_multi启动会查找my.cnf文件中的[mysqldN]组，N为mysqld_multi后携带的整数值。mysqld_multi的固定选项可在配置文件my.cnf中进行配置，在[mysqld_multi]组下配置（如果没有该组，可自行建立）。mysqld_multi使用方式如下：mysqld_multi[options]{start|stop|reload|report}[GNR[,GNR]...]
以上所述是小编给大家介绍的CentOS6.5上部署MySQL5.7.17二进制安装以及多实例配置，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！