High-availability MySQL cluster setup

For Web systems, bottlenecks are mostly on the database and disk IO, rather than on the computing power of the server. For system scalability we generally have 2 solutions, scale-up (vertical expansion) and scale-out (scale-out). The former such as expansion of memory, increase single-machine performance, replacement SSD, and so on, although it seems that the indicators are not the root and relatively expensive, but it is very effective, most of the data size is not very large, when the memory is enough to cache all the data, the disk has no pressure, such as various distributed solutions, redundant disk array.

In my opinion, MySQL read-write separation is a combination of scale-up and scale-out that improves system throughput through multiple machine services, but does not increase the total amount of storage. For some of the key business is relatively applicable, after all, the data volume of critical business will not reach a single storage limit. Non-critical data can be put into a variety of cache systems on the line. MySQL officially provides mysql-proxy to do load balancing (the QIHOO360 team's Open source project, Atlas, looks more powerful, optimizes the code behind the back-section and LUA parts), and allows us to unify our MySQL resources without having to do load balancing ourselves at the business level.

There is also a benefit of using mysql-proxy, which can be used to achieve a unified docking of UDF and memcached cache systems on proxies. In addition, the mysql-proxy itself can be used as a hot spare heartbeat, because there is no storage data on the proxy, so it can also facilitate linear expansion. Based on MySQL master-slave replication and Mysql-proxy we can build a highly available DB cluster, which is shown below.

Ready to work, three servers
192.168.1.107, installing Mysql-proxy (dependent on libevent)
192.168.1.108, installing Mysql-server, as slave, can be a few more slave server, need to add configuration on Mysql-proxy
192.168.1.109, install Mysql-server, as Master
Open remote login for Mysql-server,

Mysql> Grant all privileges on * * to [e-mail protected] '% ' identified by ' rootpwd ' with GRANT OPTION; mysql> ; FLUSH privileges;

1, configure the main MySQL (192.168.1.109) configuration file as follows, and then restart

Server-id               = 1 #主数据库id最好为1log_bin                 = /var/log/mysql/mysql-bin.logexpire_logs_days        = 10 max_binlog_size          = 100m#binlog_do_db           = include_database_namebinlog_ignore_db        = mysqlbinlog_ignore_ DB        = Information_schema

Increased permissions from the database, multiple configuration from the server (Rsyncuser and rsyncowd are used as synchronization users, passwords, preferably without root)

mysql> grant replication Slave on * * to [e-mail protected] ' 192.168.1.108 ' identified by ' rsyncpwd '; mysql> flush privileges; Mysql> Show master status; +------------------+----------+--------------+--------------------------+| File             | Position | binlog_do_db | binlog_ignore_db         |+------------------+----------+--------------+--------------------------+| mysql-bin .000001 |      333 |              | MySQL,information_schema |+------------------+----------+--------------+--------------------------+1 Row in Set (0.00 sec) mysql> Show variables like ' server_id '

2, configure the configuration file from MySQL (192.168.1.108) as follows (from the database ID must be larger than the primary database ID), and then restart

Server-id = 2

Point to Primary database, turn on sync

mysql> Change Master to master_host= ' 192.168.1.109 ', master_user= ' rsyncuser ', master_password= ' rsyncpwd ', master_ port=3306, master_log_file= ' mysql-bin.000001 ', master_log_pos=333; mysql> Show variables like ' server_id ' mysql> start slavemysql> show slave status ;

If everything is fine, the master-slave copy is set up. (if start slave not succeed, see if server_id is in effect)

3, configure proxy (192.168.1.107) as follows (multiple--proxy-read-only-backend-addresses parameters need to be added from the server):

#Vi/etc/init.d/mysql-proxy#!/bin/bashExport lua_path="/usr/share/mysql-proxy/?. Lua"Mode= $if[-Z"$mode" ] ; Then mode="Start"Ficase$mode inch' start ') MySQL-proxy--admin-username=admin--admin-password=adminpwd--daemon--admin-address=:4401--proxy-address=:3307-- proxy-backend-addresses=192.168.1.109:3306--proxy-read-only-backend-addresses=192.168.1.108:3306-- admin-lua-script=/usr/share/mysql-proxy/rw-Splitting.lua;; ' Stop ') killall MySQL-proxy;; ' Restart ')if  $stop; Then $StartElseEcho"Retart failed!!!"Exit1fi;; Esac

Start Proxy Service

Service Mysql-proxy Start

Connect mysql-uroot-p-h192.168.1.107-p3307 with client (management side mysql-uadmin-p-h192.168.1.107-p4401)

Report:

MySQL for security, by default users are allowed to log on locally only, and in some cases remote connections are required so that they can be remotely required to do the following:
First, allow the root user to telnet anywhere, and have all the library operation permissions, the following:

Mysql-uroot-prootpwd mysql> GRANT all privileges on * * to ' root ' @ '%' identified by ' rootpwd ' with GRANT OPTION; MySQL

Second, allow the root user to telnet in a specific IP, and have all the library operation permissions, the following:

Mysql-uroot-prootpwdmysql> GRANT All privileges on * * to [email protected]"192.168.1.107  "rootpwd" with GRANT OPTION; MySQL

Third, allow the root user to telnet in a specific IP, and have all the library-specific operation rights, the following:

Mysql-uroot-prootpwdmysql> GRANT select,insert,update,delete on * * to [email protected]"  172.16.16.152""rootpwd"; MySQL

Iv. Remove user authorization, need to use revoke command

REVOKE privileges on database [. Table name] from user-name: MySQL-uroot-PROOTPWD Authorization operation: Grant Select,insert,update,delete on TEST-db to [email protected]"192.168.1.107"Identified by"Rootpwd"; #then delete the authorization action:REVOKE all on test-db from test-user;#This action only clears the user's permissions for test-db, but this "test-user" user still exists. #Finally, clear the user from the user table:DELETE from user WHERE user="Test-user"; #Overload authorization Table:FLUSH privileges;#To exit the MySQL database:

V. MySQL permissions detailed classification:

PROCESS : Displays or kills service threads belonging to other users. RELOAD: Overloads the Access Control table, refreshes the log, and so on. SHUTDOWN: Turn off the MySQL service. Database /Data table/ data column permissions: Alter: Modifies an existing data table (for example, add /Remove Columns) and index. Create: Create a new database or data table. Delete: Deletes the record for the table. Drop: Deletes a data table or database. Index: Establish or delete the indexes. INSERT: Adds a table record. SELECT: Displays/ searches the table's records. UPDATE: Modifies a record that already exists in the table. Special permissions: All: Allow to do anything (as root). USAGE: Allow login only