mysqld_multi 多实例部署

序言:多实例?Why?

 

随着硬件层面的发展,linux系统多核已经是普通趋势,而mysql是单进程多线程,所以先天上对多进程的利用不是很高,虽然5.6版本已经在这方面改进很多,但是也没有达到100%,所以为了充分的利用系统资源,mysql有自己的补充,那就是可以部署多实例,一个实例一个端口。

 

 

1,准备好mysql环境

源码安装mysql参考blog:http://blog.csdn.net/mchdba/article/details/35994251,源码包下载地址:http://download.csdn.net/detail/mchdba/7545037

1.1,准备好安装环境

groupaddmysql

useradd-g mysql mysql

 

autoreconf–force –install

libtoolize–automake –force

automake–force –add-missing

 

yuminstall -y libtoolize

yuminstall cmake -y

yuminstall gcc gcc-c++ -y

yuminstall -y ncurses-devel.x86_64

yuminstall -y cmake.x86_64

yuminstall -y libaio.x86_64

yuminstall -y bison.x86_64

yuminstall -y gcc-c++.x86_64

yuminstall make -y

 

1.2,创建mysql编译根目录

mkdir-p /usr/local/mysql

 

1.3,解压缩源码包

tar -xvf mysql-5.6.12.tar.gz

cd mysql-5.6.12

 

1.4,编译安装

timecmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-DMYSQL_DATADIR=/home/data/mysql/data -DWITH_INNOBASE_STORAGE_ENGINE=1-DMYSQL_UNIX_ADDR==/usr/local/mysql/mysql.sock -DMYSQL_USER=mysql-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci

 

由于time make耗时比较长,所以事先已经安装好

timemake

timemake install

 

 

2,准备第一个实例3307

 

2.1建立好目录

mkdir /usr/local/mysql3307

chown -R mysql.mysql /usr/local/mysql3307/

 

mkdir -p /home/data/mysql3307/data

chown -R mysql.mysql /home/data/mysql3307

chown -R mysql.mysql/home/data/mysql3307/data

 

mkdir -p /home/data/mysql3307/binlog

chown -R mysql.mysql /home/data/mysql3307

 

mkdir -p /home/data/mysql3307/relay_log/

chown -R mysql.mysql /home/data/mysql3307/

 

 

2.2 配置文件

[mysqld_multi]

mysqld = /usr/local/mysql/bin/mysqld_safe

mysqladmin =/usr/local/mysql/bin/mysqladmin

log =/home/mysql/mydata/log/mysqld_multi.log

 

[mysqld1]

socket = /usr/local/mysql3307/mysql.sock

port = 3307

pid-file = /usr/local/mysql3307/mysqld.pid

datadir = /home/data/mysql3307/data

 

log_bin=/home/data/mysql3307/binlog

server-id = 1230

relay_log =/home/data/mysql3307/relay_log/mysql-relay-bin

log_slave_updates = 1

read_only = 0

 

innodb_buffer_pool_size = 128M

innodb_flush_log_at_trx_commit = 0

2.3 初始化数据库

/usr/local/mysql/scripts/mysql_install_db–user=mysql –basedir=/usr/local/mysql –datadir=/home/data/mysql3307/data

 

—————————————————————————————————————-
<版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!>
原博客地址:  http://blog.csdn.net/mchdba/article/details/45798139
原作者:黄杉 (mchdba)
—————————————————————————————————————-

3,准备第二个实例3308

3.1 建立好目录

mkdir /usr/local/mysql3308

chown -R mysql.mysql /usr/local/mysql3308/

 

mkdir -p /home/data/mysql3308/data

chown -R mysql.mysql /home/data/mysql3308

chown -R mysql.mysql/home/data/mysql3308/data

 

mkdir -p /home/data/mysql3308/binlog

chown -R mysql.mysql /home/data/mysql3308

 

mkdir -p /home/data/mysql3308/relay_log/

chown -R mysql.mysql /home/data/mysql3308/

 

 

3.2 配置文件

[mysqld2]

socket = /usr/local/mysql3308/mysql.sock

port = 3308

pid-file = /usr/local/mysql3308/mysqld.pid

datadir = /home/data/mysql3308/data

 

log_bin=/home/data/mysql3308/binlog

server-id = 2230

relay_log =/home/data/mysql3308/relay_log/mysql-relay-bin

log_slave_updates = 1

read_only = 0

 

 

innodb_buffer_pool_size = 128M

innodb_flush_log_at_trx_commit = 0

3.3 初始化数据库

 

/usr/local/mysql/scripts/mysql_install_db–user=mysql –basedir=/usr/local/mysql –datadir=/home/data/mysql3308/data

 

 

4,准备第三个实例3309

4.1 建立好目录

mkdir /usr/local/mysql3309

chown -R mysql.mysql /usr/local/mysql3309/

 

mkdir -p /home/data/mysql3309/data

chown -R mysql.mysql /home/data/mysql3309

chown -R mysql.mysql/home/data/mysql3309/data

 

mkdir -p /home/data/mysql3309/binlog

chown -R mysql.mysql /home/data/mysql3309

 

mkdir -p /home/data/mysql3309/relay_log/

chown -R mysql.mysql /home/data/mysql3309/

 

4.2 配置文件

[mysqld3]

socket = /usr/local/mysql3309/mysql.sock

port = 3309

pid-file = /usr/local/mysql3309/mysqld.pid

datadir = /home/data/mysql3309/data

 

log_bin=/home/data/mysql3309/binlog

server-id = 3230

relay_log = /home/data/mysql3309/relay_log/mysql-relay-bin

log_slave_updates = 1

read_only = 0

 

innodb_buffer_pool_size = 128M

innodb_flush_log_at_trx_commit = 0

4.3 初始化数据库

 

/usr/local/mysql/scripts/mysql_install_db–user=mysql –basedir=/usr/local/mysql –datadir=/home/data/mysql3309/data

初始化信息如下所示:





































































































 

 

 

 

5,启动多实例数据库

mkdir -p /home/mysql/data/log

 

[mysql@data02 data]$ mysqld_multi–defaults-extra-file=/etc/my.cnf start 1,2,3

[mysql@data02 data]$

后台日志信息:

Starting MySQL servers

 

150514 09:36:14 mysqld_safe Logging to’/home/data/mysql3309/data/data02.err’.

150514 09:36:15 mysqld_safe Logging to ‘/home/data/mysql3308/data/data02.err’.

150514 09:36:15 mysqld_safe Logging to’/home/data/mysql3307/data/data02.err’.

150514 09:36:15 mysqld_safe Starting mysqlddaemon with databases from /home/data/mysql3308/data

150514 09:36:15 mysqld_safe Starting mysqlddaemon with databases from /home/data/mysql3307/data

150514 09:36:15 mysqld_safe Starting mysqlddaemon with databases from /home/data/mysql3309/data

6,查看多实例

[mysql@data02 ~]$ mysqld_multi–defaults-extra-file=/etc/my.cnf report

Reporting MySQL servers

MySQL server from group: mysqld1 is running

MySQL server from group: mysqld2 is running

MySQL server from group: mysqld3 is running

[mysql@data02 ~]$

 

后台日志信息:

Reporting MySQL servers

MySQL server from group: mysqld1 is running

MySQL server from group: mysqld2 is running

MySQL server from group: mysqld3 is running

7,停止多实例

mysqld_multi–defaults-extra-file=/etc/my.cnf stop 1,2,3

 

后台日志信息:

Stopping MySQL servers

 

150514 09:35:43 mysqld_safe mysqld from pidfile /usr/local/mysql3309/mysqld.pid ended

150514 09:35:43 mysqld_safe mysqld from pidfile /usr/local/mysql3307/mysqld.pid ended

150514 09:35:43 mysqld_safe mysqld from pidfile /usr/local/mysql3308/mysqld.pid ended

 

8,各自登录mysql实例

需要指定启动的socket就可以登录到各自的mysql实例,如下所示:

[mysql@data02 ~]$ mysql –socket=/usr/local/mysql3308/mysql.sock

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.12-log Sourcedistribution

 

Copyright (c) 2000, 2013, Oracle and/or itsaffiliates. All rights reserved.

 

Oracle is a registered trademark of OracleCorporation and/or its

affiliates. Other names may be trademarksof their respective

owners.

 

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ toclear the current input statement.

 

mysql> select @@port;

+——–+

| @@port |

+——–+

|  3308 |

+——–+

1 row in set (0.00 sec)

 

mysql>

mysql> create database d3308;

Query OK, 1 row affected (0.04 sec)

 

mysql>

 

 

9,可以在多端口上搭建主从、主主服务

9.1,在3307实例建立账号

复制账号

GRANTREPLICATION SLAVE,RELOAD,SUPER ON *.*TO repl@’192.168.52.%’ IDENTIFIED BY’repl_1234′;

 

备份账号

GRANTRELOAD, SUPER, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE ON *.* TO’backup’@’192.168.%’ IDENTIFIED BY ‘123456’;

GRANTALL PRIVILEGES ON mysql.* TO ‘backup’@’192.168.%’;

GRANTEVENT ON *.* TO ‘backup’@’192.168.%’;

 

 

9.2,将数据从3307实例同步到3308端口

 

备份:

/usr/local/mysql/bin/mysqldump-ubackup –password=123456 –host=192.168.52.138–socket=/usr/local/mysql3307/mysql.sock –port=3307 -R -E –skip-opt–single-transaction –flush-logs –master-data=2 –add-drop-table–create-option –quick –extended-insert=false –set-charset –disable-keys-A  > /tmp/alldbfullbackup.sql

 

导入:

/usr/local/mysql/bin/mysql-uroot -p –socket=/usr/local/mysql3308/mysql.sock –port=3308</tmp/alldbfullbackup.sql

 

 

9.3,在3308实例上建立复制链接

         找到复制点

more/tmp/alldbfullbackup.sql

 

         开始建立复制链接

CHANGE MASTER TOMASTER_HOST=’192.168.52.138′,MASTER_PORT=3307,

MASTER_USER=’repl’,

MASTER_PASSWORD=’repl_1234′,

MASTER_LOG_FILE=’mysql-bin.000006′,

MASTER_LOG_POS=120;

 

         开启复制

startslave;

 

         查看复制状态

showslave status\G;

mysql> show slave status\G

*************************** 1. row***************************

Slave_IO_State: Waiting formaster to send event

Master_Host: 192.168.52.138

Master_User: repl

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: mysql-bin.000011

Read_Master_Log_Pos: 120

Relay_Log_File:mysql-relay-bin.000005

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000011

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

……

Seconds_Behind_Master: 0

 

 

10,管理单个实例

停止实例2:

[root@data03 ~]# mysqld_multi–defaults-extra-file=/etc/my.cnf stop 2

[root@data03 ~]#

 

后台日志信息:

Stopping MySQL servers

 

150517 06:53:43 mysqld_safe mysqld from pidfile /usr/local/mysql3308/mysqld.pid ended

mysqld_multi log file version 2.16; run: 日  5月 17 06:53:48 2015

 

启动实例2:

[root@data03 ~]# mysqld_multi–defaults-extra-file=/etc/my.cnf start 2

[root@data03 ~]#

 

后台日志信息:

Starting MySQL servers

 

150517 06:53:48 mysqld_safe Logging to’/home/data/mysql3308/data/data03.err’.

150517 06:53:48 mysqld_safe Starting mysqlddaemon with databases from /home/data/mysql3308/data

 

 

11,如何使用多实例

由于多实例中,各个实例的资源都是不share的,所以要合理分配好各个实例的内存、磁盘等资源,避免out of memery或则 full disk的情况出现。

本文来自投稿,不代表Linux运维部落立场,如若转载,请注明出处:http://www.178linux.com/88417

发表评论

登录后才能评论

联系我们

400-080-6560

在线咨询:点击这里给我发消息

邮件:1823388528@qq.com

工作时间:周一至周五,9:30-18:30,节假日同时也值班