你的位置:首页 > 操作系统

[操作系统]mysql高可用架构之mysql


实验系统:CentOS 6.6_x86_64

实验前提:防火墙和selinux都关闭

实验说明:本实验共有5台主机,IP分配如拓扑

实验软件:mariadb-10.0.20  mysql-mmm-2.2.1  mysql-mmm-monitor-2.2.1  mysql-mmm-agent-2.2.1

下载地址:http://pan.baidu.com/s/1bnnYiMr

实验拓扑:

    

一、准备工作

  1.根据下表,将各主机名进行修改:

    

  2.修改hosts文件,添加如下内容:

vim /etc/hosts------------------------------------------->192.168.19.21  mon192.168.19.66  db1192.168.19.74  db2192.168.19.76  db3 192.168.19.79  db4

  3.规划虚拟ip,列表如下:

    

二、安装mariadb并配置

  1.在db1-4上安装:

tar xf mariadb-10.0.20-linux-x86_64.tar.gz -C /usr/local/cd /usr/local/ln -sv mariadb-10.0.20-linux-x86_64 mysqluseradd -r mysqlmkdir -pv /mydata/datachown -R mysql.mysql /mydata/data/cd mysql/chown -R root.mysql .scripts/mysql_install_db --user=mysql --datadir=/mydata/data/cp support-files/my-large.cnf /etc/my.cnfcp support-files/mysql.server /etc/init.d/mysqldchkconfig --add mysqldchkconfig mysqld on

  2.编辑配置文件:

    db1:

vim /etc/my.cnf--------------------------------------------->[mysqld]server-id = 1datadir = /mydata/datalog-bin = /mydata/data/mysql1-binbinlog_format = ROWrelay_log = /mydata/data/relay-logauto-increment-increment = 2auto-increment-offset = 1sync_binlog = 1sync_master_info = 1sync_relay_log = 1sync_relay_log_info = 1max_binlog_size = 100Mlog_slave_updates  = 1
<---------------------------------------------
service mysqld start

    db2:

vim /etc/my.cnf--------------------------------------------->[mysqld]server-id = 2datadir = /mydata/datalog-bin = /mydata/data/mysql2-binbinlog_format = ROWrelay_log = /mydata/data/relay-logauto-increment-increment = 2auto-increment-offset = 2sync_binlog = 1sync_master_info = 1sync_relay_log = 1sync_relay_log_info = 1max_binlog_size = 100Mlog_slave_updates  = 1<---------------------------------------------service mysqld start

    db3:

vim /etc/my.cnf--------------------------------------------->[mysqld]server-id = 3datadir = /mydata/datalog-bin = /mydata/data/mysql3-binbinlog_format = ROWrelay_log = /mydata/data/relay-logsync_binlog = 1sync_master_info = 1sync_relay_log = 1sync_relay_log_info = 1max_binlog_size = 100Mlog_slave_updates  = 1<---------------------------------------------service mysqld start

  db4:

vim /etc/my.cnf--------------------------------------------->[mysqld]server-id = 4datadir = /mydata/datalog-bin = /mydata/data/mysql4-binbinlog_format = ROWrelay_log = /mydata/data/relay-logsync_binlog = 1sync_master_info = 1sync_relay_log = 1sync_relay_log_info = 1max_binlog_size = 100Mlog_slave_updates  = 1<---------------------------------------------service mysqld start

  3.在db1上创建用户:

     这里需要创建三个用户,如下表:

    

/usr/local/mysql/bin/mysql------------------------------------------------->GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.19.%' IDENTIFIED BY '123456';GRANT SUPER,REPLICATION CLIENT,PROCESS ON *.* TO 'mmm_agent'@'192.168.19.%' IDENTIFIED BY '123456';GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.19.%' IDENTIFIED BY '123456'

  4.查看二进制日志位置:

FLUSH TABLES WITH READ LOCK;     //施加锁SHOW MASTER STATUS;+-------------------+----------+--------------+------------------+| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| mysql1-bin.000004 |   936 |       |         |+-------------------+----------+--------------+------------------+

  5.不要关闭这个mysql进程连接,避免锁失效,我们另起一个ssh连接db1服务器,进行数据库备份:

/usr/local/mysql/bin/mysqldump --all-databases > /tmp/database-backup.sql

  6.回到刚才mysql进程,进行解锁:

UNLOCK TABLES;

  7.将database-backup.sql文件复制到其他db节点:

scp /tmp/database-backup.sql db2:/tmp/scp /tmp/database-backup.sql db3:/tmp/scp /tmp/database-backup.sql db4:/tmp/

  8.db2-4主机导入sql文件,并刷新权限:

/usr/local/mysql/bin/mysql < /tmp/database-backup.sql
/usr/local/mysql/bin/mysql
------------------------------------------------->
FLUSH PRIVILEGES;

三、设置复制

  1.在db2-4上操作,将db1设置为db2-4的主:

CHANGE MASTER TO MASTER_HOST='192.168.19.66',MASTER_USER='replication',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql1-bin.000004',MASTER_LOG_POS=936;START SLAVE;

  2.查看状态:

SHOW SLAVE STATUS\G

    

    

    

  3.查看db2的master日志位置:

SHOW MASTER STATUS;+-------------------+----------+--------------+------------------+| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| mysql2-bin.000001 |   313 |       |         |+-------------------+----------+--------------+------------------+

  4.在db1上操作,将db2设置为db1的主:

CHANGE MASTER TO MASTER_HOST='192.168.19.74',MASTER_USER='replication',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql2-bin.000001',MASTER_LOG_POS=313;START SLAVE;SHOW SLAVE STATUS\G

    

四、安装MMM

  1.创建用户:

useradd -s /sbin/nologin mmmd

  2.在mon上安装:

yum -y install mysql-mmm-monitor

  3.在db1-4上安装:

yum -y install mysql-mmm-agent

  4.编写配置文件,五台主机必须一致:

vim /etc/mysql-mmm/mmm_common.conf-------------------------------------------------------->active_master_role   writer<host default>  cluster_interface    eth0  pid_path        /var/run/mysql-mmm/mmm_agentd.pid  bin_path        /usr/libexec/mysql-mmm/  replication_user    replication      //用于复制的用户  replication_password  123456        //复制用户的密码  agent_user       mmm_agent       //用于改变模式的用户  agent_password     123456        //改变模式用户的密码</host><host db1>  ip   192.168.19.66  mode  master  peer  db2                  //需要监视的“同伴”</host><host db2>  ip   192.168.19.74  mode  master  peer  db1</host><host db3>  ip   192.168.19.76  mode  slave</host><host db4>  ip   192.168.19.79  mode  slave</host><role writer>  hosts  db1, db2               //可写节点  ips   192.168.19.150            //写操作使用的VIP  mode  exclusive              //排他模式,此处资源同一时间只能分配给一个主机</role><role reader>  hosts  db1, db2, db3, db4         //可读节点  ips   192.168.19.151, 192.168.19.152, 192.168.19.153, 192.168.19.154  //读操作使用的VIP  mode  balanced              //平衡模式</role>

  5.在db1-4上修改mmm_agent.conf,只需要修改db1这里,是哪台就改成哪台,这里只给出db1的:

vim /etc/mysql-mmm/mmm_agent.conf------------------------------------------------->include mmm_common.conf# The 'this' variable refers to this server. Proper operation requires # that 'this' server (db1 by default), as well as all other servers, have the # proper IP addresses set in mmm_common.conf.this db1       //只改这里

  6.配置mon上的mmm_mon.conf:

vim /etc/mysql-mmm/mmm_mon.conf---------------------------------------------------->include mmm_common.conf<monitor>  ip         127.0.0.1  pid_path      /var/run/mysql-mmm/mmm_mond.pid  bin_path      /usr/libexec/mysql-mmm  status_path     /var/lib/mysql-mmm/mmm_mond.status  ping_ips      192.168.19.50, 192.168.19.66, 192.168.19.74, 192.168.19.76, 192.168.19.79
//用于测试网络可用性的IP地址,其中有一个地址能ping通,就代表网络正常,不要写入本机的ip地址 auto_set_online 60 //是否设置自动上线,如果该值大于0,抖动的主机在抖动的时间范围过后,则设置自动上线 # The kill_host_bin does not exist by default, though the monitor will # throw a warning about it missing. See the section 5.10 "Kill Host # Functionality" in the PDF documentation. # # kill_host_bin /usr/libexec/mysql-mmm/monitor/kill_host #</monitor><host default> monitor_user mmm_monitor //用于监控的用户 monitor_password 123456 //监控用户的密码</host>debug 0

五、启动测试:

  1.在db1-4上启动agents:

chkconfig mysql-mmm-agent onservice mysql-mmm-agent start

  2.在mon上启动monitor:

vim /etc/default/mysql-mmm-monitor--------------------------------------------------->ENABLED=1
<---------------------------------------------------
service mysql-mmm-monitor start

  3.检查集群状态:

mmm_control show

    

    如果服务器状态不是ONLINE,可以用如下命令将服务器上线,例如:

mmm_control set_online db1

  4.从刚才图片可以看到,写请求的VIP在db1上,所有从节点也都把db1当做主节点。下面将db1停掉:

service mysqld stop     //停掉db1的mysql服务

    可以看到写请求的VIP已经转移到db2上了,且从节点的主都指向了db2:

    

    

  5.最后开启db3、db4的只读:

vim /etc/my.cnf------------------------------->[mysqld]read_only = 1
<-------------------------------
service mysqld restart

   到此为止,所有的配置都完成了。一个writer的VIP和四个reader的VIP都已经正常分配,大家可以创建一个远程登录账号进行测试,这里就不再给出具体的过程了。由于时间紧迫,且经验有限,配置过程中可能会出现纰漏,如有发现请及时联系我,欢迎大家的指正,谢谢! 联系QQ:82800542