欢迎光临
屌丝博客

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

一、简介

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

wKiom1YeDmCjVaFNAAEOOUfaie0974.jpg

二、演示环境

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