重庆分公司,新征程启航

为企业提供网站建设、域名注册、服务器等服务

MySQL5.6多实例部署-创新互联

无论是迫于预算,亦或者是领导要求,多实例的安装也是DBA必须掌握的技术,她的启停和登录方式和单实例安装数据库略有不同,本文记录下如何完成MySQL5.6多实例部署。

创新互联-专业网站定制、快速模板网站建设、高性价比康乐网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式康乐网站制作公司更省心,省钱,快速模板网站建设找我们,业务覆盖康乐地区。费用合理售后完善,10余年实体公司更值得信赖。

首先我们看一下my.cnf和单实例的区分:

[root@HE1 scripts]#
cat /etc/my.cnf
[client]
#port = 3306
#socket = /tmp/mysql.sock
#default-character-set = utf8

[mysql]
#default-character-set = utf8

[mysqld3306]
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql_3306
socket = /tmp/mysql_3306.sock
slow_query_log_file = /data/mysql_3306/slow.log
log-error = /data/mysql_3306/error.log
log-bin = /data/mysql_3306/mysql-bin
sync_binlog = 1
binlog_format = row
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 100m

[mysqld3308]
port = 3308
basedir = /usr/local/mysql
datadir = /data/mysql_3308
socket = /tmp/mysql_3308.sock
slow_query_log = 1
slow_query_log_file = /data/mysql_3308/slow.log
log-error = /data/mysql_3308/error.log
long_query_time = 1
log-bin = /data/mysql_3308/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
default-storage-engine = InnoDB
binlog_format = row
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 100m

[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin

[mysqldump]
quick
max_allowed_packet = 32M

可以看出,多实例的my.cnf实际上就是如上所示,本文为了演示实验环境,innodb_buffer_pool_size仅仅开了100m,真实的生产库中多实例部署该参数要开大些,两个实例该参数的值达到内存的50%-80%都可以。

下面开始初始化我们的数据库
首先创建我们的数据目录
[root@HE1 ~]#mkdir -p /data/mysql_3306
[root@HE1 ~]#mkdir -p /data/mysql_3308
[root@HE1 ~]#echo "export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib" >>/etc/profile

进入到mysql的scripts文件夹下对数据库进行初始化,这里我们对3306端口数据库进行初始化
[root@HE1 scripts]#./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3306 --defaults-file=/etc/my.cnf --user=mysql

这里我们对3308端口数据库进行初始化
[root@HE1 scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3308 --defaults-file=/etc/my.cnf --user=mysql

初始化完成后,我们便可以启停数据库了,和单实例不同,多实例采用mysqld_multi来启停数据库
[root@HE1 bin]# ./mysqld_multi --defaults-file=/etc/my.cnf --user=root --password=MANAGER start 3306,3308

可以利用mysqld_multi的report命令来检测多实例的运行状况
1234 [root@HE1 bin]#./mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3308 is running

登录方式和单实例大体相同,不过由于多实例的存在,我们需要指定不同的端口号
[root@HE1 bin]# mysql -uroot -p -P3306 -h 192.168.1.48
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6 Server version: 5.6.16-log MySQL Community Server (GPL)

Copyright (c) 2000,
2014, 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> show databases;
+--------------------+
| Database      |
+--------------------+
| information_schema
|
| 3306db       |
| mysql       |
| performance_schema
|
| test        |
+--------------------+
5 rows in set (0.00 sec)

当然,利用socket文件登录也是可以的
[root@HE1 bin]#mysql -uroot -p -S /data/mysql_3306/mysql_3306.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 Server version: 5.6.16-log MySQL Community Server (GPL)

Copyright (c) 2000,
2014, 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> show databases;
+--------------------+
| Database      |
+--------------------+
| information_schema
|
| 3306db       |
| mysql       |
| performance_schema
|
| test        |
+--------------------+
5 rows in set (0.00 sec)

这里是登录3308端口数据库
[root@HE1 bin]#mysql -uroot -p -P3308 -h 192.168.1.48
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 Server version: 5.6.16-log MySQL Community Server (GPL)

Copyright (c) 2000,
2014, 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.

Type 'help;' or '\h'
for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database      |
+--------------------+
| information_schema
|
| 3308db       |
| mysql       |
| performance_schema
|
| test        |
+--------------------+
5 rows in set (0.00sec)

mysql> quit
Bye

利用3308端口的socket文件登录数据库
[root@HE1 bin]#mysql -uroot -p -S /data/mysql_3308/mysql_3308.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 Server version: 5.6.16-log MySQL Community Server (GPL)

Copyright (c) 2000,
2014, 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> show databases;
+--------------------+
| Database      |
+--------------------+
| information_schema
|
| 3308db       |
| mysql       |
| performance_schema
|
| test        |
+--------------------+
5 rows in set (0.00sec)

至此,MySQL5.6多实例部署完成。

另外有需要云服务器可以了解下创新互联cdcxhl.cn,海内外云服务器15元起步,三天无理由+7*72小时售后在线,公司持有idc许可证,提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案,具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势,专为企业上云打造定制,能够满足用户丰富、多元化的应用场景需求。


本文标题:MySQL5.6多实例部署-创新互联
文章位置:http://cqcxhl.cn/article/cociso.html

其他资讯

在线咨询
服务热线
服务热线:028-86922220
TOP