数据库知识MySQL的HA、读写分离、均衡负载

MySQL的HA、读写分离、均衡负载作者:隋成龙第1页共25页目录一.需求第2页共25页一、需求：Application服务器的写操作分别留给下面的mysql-master，读操作交给mysql-slave.模拟上面的架构，两个mysql-master(db1与db2)做ha，一个mysql-slave（db3）如有多个mysql-slave用下面的方法也能解决。Twomasters+one/manyslaves这个例子中需要4台主机，在下面例子中分别为192.168.0.11db1.localhostdb1192.168.0.12db2.localhostdb2192.168.0.13db3.localhostdb3192.168.0.10mon.localhostmon随便找一台主机作为application就可以！首先设置正确的host，要不就弄个dns设置正确hosts文件，把它拷贝到所有机器上[root@mon~]#cat/etc/hosts#Donotremovethefollowingline,orvariousprograms#thatrequirenetworkfunctionalitywillfail.127.0.0.1localhost.localdomainlocalhost::1localhost6.localdomain6localhost6192.168.0.11db1.localhostdb1192.168.0.12db2.localhostdb2第3页共25页192.168.0.13db3.localhostdb3192.168.0.10mon.localhostmon注意：下面的虚拟ip一会供mysql-master与mysql-slave提供服务使用的，先不用绑定到主机上，昀后会通过mmm绑定到各个数据库上！IProledescription192.168.0.100writerYourapplicationshouldconnecttothisIPforwritequeries.192.168.0.101reader192.168.0.102reader192.168.0.103readerYourapplicationshouldconnecttooneofthesefourIPsforreadqueries在所有机器上安装mysql-server在db1上进行配置192.168.0.11（配置角色为master）#yuminstallmysql-server–y#vim/etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql#Defaulttousingoldpasswordformatforcompatibilitywithmysql3.x#clients(thoseusingthemysqlclient10compatibilitypackage).old_passwords=1server_id=1log_bin=/var/lib/mysql/mysql-bin.loglog_bin_index=/var/lib/mysql/mysql-bin.log.indexrelay_log=/var/lib/mysql/mysql-relay-binrelay_log_index=/var/lib/mysql/mysql-relay-bin.indexexpire_logs_days=10max_binlog_size=100Mlog_slave_updates=1read_only=1[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid把这个文件拷贝到所有mysql服务器上，保证每台主机的server_id不一样！！建立需要的用户，需要3个用户monitoruser,agentuser,relicationuserfunctiondescriptionprivilegesmonitoruserusedbythemmmmonitortocheckthehealthoftheMySQLserversREPLICATIONCLIENT第4页共25页agentuserusedbythemmmagenttochangeread-onlymode,replicationmaster,etc.SUPER,REPLICATIONCLIENT,PROCESSrelicationuserusedforreplicationREPLICATIONSLAVEmysqlGRANTREPLICATIONCLIENTON*.*TO'mmm_monitor'@'192.168.0.%'IDENTIFIEDBY'monitor_password';mysqlGRANTSUPER,REPLICATIONCLIENT,PROCESSON*.*TO'mmm_agent'@'192.168.0.%'IDENTIFIEDBY'agent_password';mysqlGRANTREPLICATIONSLAVEON*.*TO'replication'@'192.168.0.%'IDENTIFIEDBY'replication_password';注意：这里的限制不严格，你可以限制的更严格，比如to‘mmm_monitor‘@’192.168.0.14’，这个不是必须的，内网也不用弄这么严格了！在3台mysql服务器上重新启动mysql服务器#servicemysqldrestart在两个master之间同步数据在db1上执行以下命令#mysqlmysqlflushtableswithreadlock;mysqlshowmasterstatus;+------------------+----------+--------------+------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|+------------------+----------+--------------+------------------+|mysql-bin.000001|98|||+------------------+----------+--------------+------------------+1rowinset(0.00sec)注释：可以在配置文件中进行配置binlog-do-db=db1binlog-do-db=db2binlog-ignore-db=testbinlog-ignore-db=mysqlreplicate-do-db=db1replicate-do-db=ddb2其中binlog-do-db和replicate-do-db标记出需要进行同步的数据库，对于多个数据库，重复选项多次即可，如上面的配置则表示同步db1和db2。binlog-ignore-db表示不进行同步第5页共25页的数据库，上面的配置表示不同步test,mysql库。查看正确的position不要关闭这个mysql的shell界面，一旦关闭，刚才加上读锁就会被remove。再打开一个窗口#mysqldump-uroot--all-databases/tmp/database-backup.sql-p这个时候可以removedatabase-lockmysqlunlocktables;把刚才备份的数据拷贝到db2,db3拷贝/tmp/database-backup.sql给db2,db3[root@db1~]#scp/tmp/database-backup.sqldb2:/tmp/root@db2'spassword:database-backup.sql100%372KB371.5KB/s00:00[root@db1~]#scp/tmp/database-backup.sqldb3:/tmp/root@db3'spassword:database-backup.sql100%372KB371.5KB/s00:01[root@db1~]#在db2,db3上导入数据#mysql-uroot-p/tmp/database-backup.sql为了让新导入数据中的user-table生效，在db1，db2，db3上执行以下命令mysqlflushprivileges;建立复制关系在db2，db3上建立复制关系(db2)mysqlCHANGEMASTERTOmaster_host='192.168.0.11',master_port=3306,master_user='replication',master_password='replication_password',master_log_file='file',master_log_pos=position;第6页共25页(db3)mysqlCHANGEMASTERTOmaster_host='192.168.0.11',master_port=3306,master_user='replication',master_password='replication_password',master_log_file='file',master_log_pos=position;master_log_file，master_log_pos后面输入的位置就是mysqlshowmasterstatus;查看到的位置!注释：这个过程中如果出现报错，可以通过在slave上重新初始化数据库mysql_install_db--user=mysql来搞定！mysqlstartslave;然后在db2，db3上执行下面的命令看输出是否正常！mysqlSHOWSLAVESTATUS\G记住在这个过程中一定要看到两个YES在db1上配置为db2的slave把db2配置为另外一个master，实现ha。现在db2上执行Mysqlshowmasterstatus\G记录下相关的信息在db1上执行(db1)mysqlCHANGEMASTERTOmaster_host='192.168.0.12',master_port=3306,master_user='replication',master_password='replication_password',master_log_file='file',master_log_pos=position;(db1)mysqlstartslave;(db1)mysqlshowslavestatus\G记住在这个过程中一定要看到两个YES安装MMM在mon主机安装mmm软件包安装epel的yum源吧，这样你会很省事！到这里来找吧！#yuminstallmysql-mmm-monitor–y第7页共25页在db1，db2，db3上安装代理软件#yuminstallmysql-mmm-agent-y配置mmmMon,db1,db2,db3主机上的/etc/mysql-mmm/mmm_common.conf的文件都要一样配置如下：active_master_rolewriterhostdefaultcluster_interfaceeth0pid_path/var/run/mysql-mmm/mmm_agentd.pidbin_path/usr/libexec/mysql-mmm/replication_userreplicationreplication_password123agent_usermmm_agentagent_password123/host#用户名，