Mysql 一主多实例与主从同步详解

原创

ssc76109360 2015-10-14 15:47:06 博主文章分类：Database ©著作权

文章标签 服务器数据库 master mysql多实例 文章分类 服务器

©著作权归作者所有：来自51CTO博客作者ssc76109360的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、简介

一主多实例,通俗讲就是，一个主库，多个实例库，以我们公司为例，我现在公司目前上线一款手游，每个区分别有一个主库，但是如果每个区单独开一个从库，会比较浪费资源，所以多个区的主库数据同步到了一台从库服务器中，当然此台从库服务器要开多个mysql，这就是我下面要讲的内容。

二、演示环境

Linux:CentOS6.5 x64

MySql: mysql5.5.rpm (此处我是用的是rpm包，下面演示rpm与编译安装方式一样的)

iptables stop

selinux disabled

master_ip: 10.10.168.2

slave_ip:10.10.168.3

三、实例演示（master:主库服务器；slave:从库服务器）

（1）master端

1、master 安装mysql数据库

yum -y install cmake gcc-c++ ncurses-devel bison perl readline-devel time zlib-devel libaio-devel perl-DBI perl-Time-HiRes libtool
rpm -ivh mysql-5.5.24-1.el6.x86_64.rpm --nodeps --force
source /etc/profile

2、设置master数据库访问权限

grant replication slave on *.* to 'rsync'@'10.10.%' identified by '1q2w3e4r';
grant all privileges on *.* to 'root'@'10.10.%' identified by '密码';
flush privileges;

3、修改master数据库配置文件

vim /etc/my.cnf
server-id       = 1    //master端ID号
log-bin=/data/logbin/mysql-bin    //日志路径及文件名
#binlog-do-db = cacti    //同步cacti，此处关闭的话，就是除不允许的，其它的库均同步
binlog-ignore-db = mysql    //不同步mysql库，以下同上
binlog-ignore-db = test
binlog-ignore-db = information_schema

(2)slave端
1、slave从库多实例设置

yum -y install cmake gcc-c++ ncurses-devel bison perl readline-devel time zlib-devel libaio-devel perl-DBI perl-Time-HiRes libtool
rpm -ivh mysql-5.5.24-1.el6.x86_64.rpm --nodeps --force
source /etc/profile

2、把使用工具添加到 /usr/bin/ 目录下

ln -s /usr/local/mysql/bin/mysqld_multi /usr/bin/mysqld_multi
ln -s /usr/local/mysql/scripts/mysql_install_db /usr/bin/mysql_install_db

3、初始化使用的mysql实例目录

/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql3307
/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql3308
/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql3309
/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql3310

4、修改初始化目录的属主属组权限

chown -R mysql:mysql /data/mysql3307
chown -R mysql:mysql /data/mysql3308
chown -R mysql:mysql /data/mysql3309
chown -R mysql:mysql /data/mysql3310

5、创建 mysql{3307,3308,3308,3310}目录下的sock与pid文件

touch /data/mysql3307/mysql3307.sock
touch /data/mysql3308/mysql3308.sock
touch /data/mysql3309/mysql3309.sock
touch /data/mysql3310/mysql3307.sock
touch /data/mysql3307/mysql3307.pid
touch /data/mysql3308/mysql3308.pid
touch /data/mysql3309/mysql3309.pid
touch /data/mysql3310/mysql3310.pid

6、用mysqld_multi工具生成配置文件，命令为：mysqld_multi --example，这里我没用默认生成的配置文件，而是在生成的配置文件基础之上进行了修改。vim /etv/mysqld_multi.cnf

[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
#user       = root
#password   = 3633

[mysqld1]
socket     = /data/mysql3307/mysql3307.sock
port       = 3307
pid-file   = /data/mysql3307/mysql3307.pid
datadir    = /data/mysql3307
#language   = /usr/local/mysql/share/mysql/english
user       = mysql
server-id  = 2
log-error  = /var/log/mysqld1.log

[mysqld2]
socket     = /data/mysql3308/mysql3308.sock
port       = 3308
pid-file   = /data/mysql3308/mysql3308.pid
datadir    = /data/mysql3308
#language   = /usr/local/mysql/share/mysql/english
user       = mysql
server-id  = 3
log-error  = /var/log/mysqld2.log

[mysqld3]
socket     = /data/mysql3309/mysql3309.sock
port       = 3309
pid-file   = /data/mysql3309/mysql3309.pid
datadir    = /data/mysql3309
#language   = /usr/local/mysql/share/mysql/english
user       = mysql
server-id  = 4
log-error  = /var/log/mysqld3.log

[mysqld4]
socket     = /data/mysql3310/mysql3310.sock
port       = 3310
pid-file   = /data/mysql3310/mysql3310.pid
datadir    = /data/mysql3310
#language   = /usr/local/mysql/share/mysql/english
user       = mysql
server-id  = 5
log-error  = /var/log/mysqld4.log

7、（启动,停止，查看当前状态） mysql实例数据库

将/usr/local/mysql/bin加到$PATH环境变量里

export PATH=/usr/local/mysql/bin:$PATH

#查看数据库状态  
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  
MySQL server from group: mysqld3 is not running  
MySQL server from group: mysqld4 is not running

#启动 mysql实例
mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start  
#结果为  
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 server from group: mysqld4 is running

#停止 mysql实例
mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf stop 
#结果为  
Reporting MySQL servers  
MySQL server from group: mysqld1 is not running  
MySQL server from group: mysqld2 is not running  
MySQL server from group: mysqld3 is not running  
MySQL server from group: mysqld4 is not running

也可以单独启动某个实例数据库 //在start 或 stop 尾部 加上 1，2，3，4 或 1-2 ，1-3，2-4
mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start 1,2,3,4
mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf stop 1,2,3,4

备注：有时候配置完数据库实例之后，一次启动所有实例数据库无法启动，需要先启动一台，在启动所有即可。这个问题，请注意一下。

通过 netstat -lntp 命令查看当前数据库服务是否处于被监听状态

8、mysql实例数据库登录方式

#进入端口为3307的数据库  
mysql -uroot -p -h127.0.0.1 -P3307 
  
#通过sock文件登录  
mysql -uroot -p -S /data/mysql3307/mysql3307.sock  


注:做一主多实例必须查看下面信息是否与当前库配置信息相符

#查看server_id文件  
mysql> SHOW VARIABLES LIKE '%server_id%'; 
  
#查看socket文件  
mysql> SHOW VARIABLES LIKE 'socket';  
  
#查看pid文件  
mysql> SHOW VARIABLES LIKE '%pid%';

9、slave端从库配置（以3307库为例，其余同理）

（1）  配置多实例服务器从库
命令（分段）说明：
Stop slave; 停止同步功能
Change master to
Master_host=’10.10.168.2’,    //master端IP
Master_user=’rsync’,      //master端创建的同步帐号
Master_password=’1q2w3e4r’, //同步密码
Master_port=3306,  //master端 mysql 访问端口
Master_log_file=’mysql-bin.000047’,  //master端记录的file值
Master_log_pos=107; //master端记录的position值
Start slave； //启动同步功能
Show slave status\G //查看参数状态
整体语句如下：
changemaster to master_host=’10.10.168.2’ master_user=’rsync’master_password=’1q2w3e4r’ master_port=3306 master_log_file=’mysql-bin.000047’master_log_pos=107;

注：查看上面的Slave_IO_Running: Yes 和Slave_SQL_Running: Yes 2个都为yes则证明主从同步正常，如果有任一个显示NO,则证明同步有问题。可以查看数据库日志文件，里面基本上会显示出错误之处，根据错误一步一步排查，基本上都可以解决的。

10、mysql实例化脚本

（本文附带mysql实例化脚本提供大家方便创建mysql多实例库）

#!/bin/bash
#
IP="127.0.0.1"
PASSWORD="new_password"

echo_err(){
    echo -e "\E[1;31m""$@ \033[0m"
}

echo_ok(){
    echo -e "\E[1;32m""$@ \033[0m"
}

test -d /data/mysql/  || mkdir -p /data/mysql
cd /opt/
wget http://10.10.114.178:8008/mysql-5.5.24-1.el6.x86_64.rpm
yum -y install cmake gcc-c++ ncurses-devel bison perl readline-devel time zlib-devel libaio-devel perl-DBI perl-Time-HiRes libtool
rpm -ivh mysql-5.5.24-1.el6.x86_64.rpm --nodeps --force
source /etc/profile

/etc/init.d/mysqld stop

ln -s /usr/local/mysql/bin/mysqld_multi /usr/bin/mysqld_multi
ln -s /usr/local/mysql/scripts/mysql_install_db /usr/bin/mysql_install_db
cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi.server

for i in {3307..3310}
do
    /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql${i}
    while [[ `test -d /data/mysql${i} && echo $?` -ne 0 ]]
    do
        sleep 1
    done
    chown -R mysql:mysql /data/mysql${i}
    touch /data/mysql${i}/{mysql${i}.sock,mysql${i}.pid}
done
echo_ok "数据库初始化完成，开始部署多实例"

cat >> /etc/mysqld_multi.cnf <<EOF
[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
#user       = root
#password   = 3633

[mysqld1]
socket     = /data/mysql3307/mysql3307.sock
port       = 3307
pid-file   = /data/mysql3307/mysql3307.pid
datadir    = /data/mysql3307
#language   = /usr/local/mysql/share/mysql/english
user       = mysql
server-id  = 2
log-error  = /var/log/mysqld1.log

[mysqld2]
socket     = /data/mysql3308/mysql3308.sock
port       = 3308
pid-file   = /data/mysql3308/mysql3308.pid
datadir    = /data/mysql3308
#language   = /usr/local/mysql/share/mysql/english
user       = mysql
server-id  = 3
log-error  = /var/log/mysqld2.log

[mysqld3]
socket     = /data/mysql3309/mysql3309.sock
port       = 3309
pid-file   = /data/mysql3309/mysql3309.pid
datadir    = /data/mysql3309
#language   = /usr/local/mysql/share/mysql/english
user       = mysql
server-id  = 4
log-error  = /var/log/mysqld3.log

[mysqld4]
socket     = /data/mysql3310/mysql3310.sock
port       = 3310
pid-file   = /data/mysql3310/mysql3310.pid
datadir    = /data/mysql3310
#language   = /usr/local/mysql/share/mysql/english
user       = mysql
server-id  = 5
log-error  = /var/log/mysqld4.log
EOF

########此段操作，由于mysql启动缺陷，这样启动 ########
echo_ok "启动多实例"
mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report
mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start 1
sleep 3
mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report
netstat -lntp
mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start 1
sleep 3

while [ `netstat -nlpt|grep -q 3307;echo $?` -ne 0 ]
do
    echo -n "."
    sleep 1
done

mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report
mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start 1,2,3,4
sleep 3
mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report
netstat -lntp
mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start 1,2,3,4
sleep 3
#######################################################

for i in {3307..3310}
do
    while [ `netstat -nlpt|grep -q ${i};echo $?` -ne 0 ]
    do
        echo -n "."
        sleep 1
    done
    /usr/local/mysql/bin/mysql -uroot -h${IP} -P${i} -e "use mysql;update user set password=password('new_password') where user='root';flush privileges;"
done

source /etc/profile