哈尔滨智能建站模板,做的网站放在阿里云,南京专业网站制作,jsp网站开发四酷全书一、MySQL多实例简介 MySQL多实例#xff0c;简单地说#xff0c;就是在一台服务器上同时开启多个不同的服务端口#xff08;如#xff1a;3306、3307#xff09;#xff0c;同时运行多个MySQL服务进程#xff0c;这些服务进程通过不同的socket监听来自不同的端口来提供… 一、MySQL多实例简介 MySQL多实例简单地说就是在一台服务器上同时开启多个不同的服务端口如3306、3307同时运行多个MySQL服务进程这些服务进程通过不同的socket监听来自不同的端口来提供服务 多实例不仅节省物理主机成本还有效提升了单台物理主机的CPU、磁盘I/O使用效率而且还可以在多实例之间做部署数据库HA方案。 随着实例数量的增加就面临统一管理问题这样我们就需要用MySQL自带的管理程序 mysqld_multi 来进行管理... 二、MySQL启动流程mysqld_multi #多实例管理程序 mysqld #MySQL最主要的启动方式里面有很多参数现在使用多实例就需要用新的mysql_safe 来启动mysql mysql_safe #实则还是调用mysqld并且会读取mysqld中的my.cnf配置参数来启动mysqlmysql_safe本身也有很多参数但是这些参数会优先于my.cnf my.cnf #mysql的配置文件 my.sock #mysql创建的sock文件开启、停止、登陆和管理mysql都是通过这个接口文件 三、接下来基于mysql5.5.52版本安装方法请看MySQL5.5.52编译安装利用mysqld_multi配置一个多实例 1、停止单实例mysql数据库 1 2 [rootdb01 ~]# /etc/init.d/mysqld stop Shutting down MySQL. SUCCESS! 2、禁止开机自启动 1 2 3 [rootdb01 ~]# chkconfig mysqld off [rootdb01 ~]# chkconfig --list mysqld mysqld 0:关闭 1:关闭 2:关闭 3:关闭 4:关闭 5:关闭6:关闭 3、创建多实例根目录/data/目录 1 [rootdb01 ~]# mkdir -p /data/{3306,3307}/data 4、拷贝mysqld_multi程序文件 1 [rootdb01 ~]# cp /application/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi.server 1修改mysqld_multi.server路径配置 1 2 [rootdb01 ~]# sed -i s#basedir/usr/local/mysql#basedir/application/mysql#g /etc/init.d/mysqld_multi.server [rootdb01 ~]# sed -i s#bindir/usr/local/mysql/bin#bindir/application/mysql/bin#g /etc/init.d/mysqld_multi.server 2添加mysqld_multi用到的/etc/mysqld_multi.cnf配置文件 #这个模板文件可以用命令mysqld_multi --example导出来 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 [rootdb01 ~]# vim /etc/mysqld_multi.cnf [mysqld_multi] mysqld /application/mysql/bin/mysqld_safe mysqladmin /application/mysql/bin/mysqladmin #user multi_admin #password my_password [mysqld1] socket /data/3306/mysql.sock port 3306 pid-file /data/3306/mysql.pid datadir /data/3306/data #language /application/mysql/share/mysql/english user mysql [mysqld2] socket /data/3307/mysql.sock port 3307 pid-file /data/3307/mysql.pid datadir /data/3307/data #language /application/mysql/share/mysql/english user mysql 5、配置MySQL多实例的文件权限 通过下面的命令授权mysql用户和用户组管理整个多实例的根目录/data 1 [rootdb01 ~]# chown -R mysql.mysql /data 6、初始化MySQL多实例的数据库文件 1初始化MySQL数据库 cd /application/mysql/scripts/ 注意和MySQL5.1的路径不同MySQL5.1不在MySQL bin路径下了 3306实例 /application/mysql/scripts/mysql_install_db \ --basedir/application/mysql \ --datadir/data/3306/data \ --usermysql 3307实例 /application/mysql/scripts/mysql_install_db \ --basedir/application/mysql \ --datadir/data/3307/data \ --usermysql 提示--basedir/application/mysql为MySQL的安装路径--datadir为不同的实例数据目录 操作过程 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 [rootdb01 ~]# cd /application/mysql/scripts/ 3306实例 [rootdb01 scripts]# /application/mysql/scripts/mysql_install_db \ --basedir/application/mysql \ --datadir/data/3306/data \ --usermysql WARNING: The host db01 could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL daemon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Installing MySQL system tables... 161117 14:14:14 [Note] /application/mysql/bin/mysqld (mysqld 5.5.52) starting as process 46676 ... OK Filling help tables... 161117 14:14:15 [Note] /application/mysql/bin/mysqld (mysqld 5.5.52) starting as process 46683 ... OK 如果有两个ok就表示初始化成功 3307实例 [rootdb01 scripts]# /application/mysql/scripts/mysql_install_db \ --basedir/application/mysql \ --datadir/data/3307/data \ --usermysql Installing MySQL system tables... 161117 14:18:20 [Note] /application/mysql/bin/mysqld (mysqld 5.5.52) starting as process 46733 ... OK Filling help tables... 161117 14:18:21 [Note] /application/mysql/bin/mysqld (mysqld 5.5.52) starting as process 46740 ... OK 如果有两个ok就表示初始化成功 7、启动多实例 1查看数据库状态 1 2 3 4 5 mysqld_multi --defaults-extra-file/etc/mysqld_multi.cnf report [rootdb01 ~]# mysqld_multi --defaults-extra-file/etc/mysqld_multi.cnf report Reporting MySQL servers MySQL server from group: mysqld1 is not running MySQL server from group: mysqld2 is not running 2启动数据库 1 2 3 4 5 [rootdb01 ~]# mysqld_multi --defaults-extra-file/etc/mysqld_multi.cnf start 1,2 [rootdb01 ~]# mysqld_multi --defaults-extra-file/etc/mysqld_multi.cnf report Reporting MySQL servers MySQL server from group: mysqld1 is running MySQL server from group: mysqld2 is running 3查看端口 1 2 3 [rootdb01 ~]# ss -nlutp|grep 330 tcp LISTEN 0 50 *:3306 *:* users:((mysqld,47045,10)) tcp LISTEN 0 50 *:3307 *:* users:((mysqld,47041,10)) 8、停止数据库 1 2 3 4 5 [rootdb01 ~]# mysqld_multi --defaults-extra-file/etc/mysqld_multi.cnf stop 1,2 [rootdb01 ~]# mysqld_multi --defaults-extra-file/etc/mysqld_multi.cnf report Reporting MySQL servers MySQL server from group: mysqld1 is not running MySQL server from group: mysqld2 is not running 9、登陆数据库 1启动数据库 1 [rootdb01 ~]# mysqld_multi --defaults-extra-file/etc/mysqld_multi.cnf start 1,2 2登录数据库 方法一指定端口和主机IP适合远程连接 1 mysql -uroot -h127.0.0.1 -P3306 方法二指定socket登陆适合在本机连接 1 mysql -S /data/3307/mysql.sock 操作演示 方法一指定端口和主机IP适合远程连接 1 2 3 4 5 6 7 8 9 10 11 12 [rootdb01 ~]# mysql -uroot -h127.0.0.1 -P3306 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.52 Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type help; or \h for help. Type \c to clear the current input statement. mysql 方法二指定socket登陆适合在本机连接 1 2 3 4 5 6 7 8 9 10 11 12 [rootdb01 ~]# mysql -S /data/3307/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.52 Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type help; or \h for help. Type \c to clear the current input statement. mysql 到这里MySQL多实例就配置完成啦O(∩_∩)O~~ 本文转自 炫维 51CTO博客原文链接:http://blog.51cto.com/xuanwei/1881521
