MySQL Cluster one: master-slave replication, load Balancing via Mysql-proxy

MySQL cluster architecture in many ways, according to different needs to do the same architecture, the simple point is the MySQL replication, that is, MySQL replication function, the mode is: Master-slaves,master-slaves-slaves, Master-master-slaves and so can have multiple layers, so now I want to say is the master-slaves mode (the other mode principle is basically the same), and then through the official MySQL provided by the Mysql-proxy to achieve read and write separation, Achieve the effect of load balancing.

Environment:

Host: master:192.168.1.109,slave1:192.168.1.110,mysqlproxy:192.168.1.112. (Workstation10 platform Create virtual machine emulation)

Operating system: Red Hat Enterprise Linux Server release 5.8

Mysql:mysql-5.5.37-linux2.6-i686.tar.gz

Mysql-proxy:mysql-proxy-0.8.4-linux-glibc2.3-x86-32bit.tar.gz

1. Install MySQL:

[[email protected] ~]# cd/usr/local//Enter into the installation directory

[[email protected] local]# TAR-ZXVF mysql-5.5.37-linux2.6-i686.tar.gz//decompression MySQL binary package

[[email protected] local]# ln-s mysql-5.5.37-linux2.6-i686 MySQL//unzip after adding link

[[email protected] local]# Groupadd mysql//add MySQL user group

[[email protected] local]# useradd-g mysql mysql//add MySQL user account

[[Email protected] local]# CD MySQL//Enter the MySQL directory after installation

[Email protected] mysql]# chown-r root:mysql. Modify the genus and owner of the file, notice that there is a point behind the command that indicates the current directory

[[email protected] mysql]# chown-r mysql:mysql data///Change the owner of the data directory to MySQL

[[email protected] mysql]# CP support-files/my-large.cnf/etc/my.cnf//Copy mysql config file in etc directory and renamed to MY.CNF

[[email protected] mysql]# CP support-files/mysql.server/etc/init.d/mysqld//copy MySQL startup script in/etc/init.d/directory

[[Email protected] mysql]# service mysqld start//start MySQL

Now that the MySQL installation is finished, the next step is MySQL's master and slave configuration. (The other two hosts also install MySQL)

2. Configure Master:

[[email protected] mysql]# vi/etc/my.cnf//edit config file

Server-id=1//default is 1, no words are changed to 1

Thread_concurrency = 2//thread concurrency (cpu*2)

Log-bin=mysql-bin//Turn on the binary logging feature

Save exit and restart MySQL.

Log in to MySQL:

[[email protected] mysql]# mysql-uroot-p//default password empty, directly hit enter

mysql> grant replication Slave on * * to [e-mail protected] ' 192.168.1.% ' identified by ' 123456 ';

Mysql>flush privileges;

Mysql>show grants for ' REPL ' @ ' 192.168.1.% '; View authorization, record description OK

Mysql>show Master Status\g; View master server status, with binary log file name and record location (position)

This completes the MySQL configuration.

3, configuration slave

[[email protected] MySQL] #vi/etc/my.cnf//edit config file

server-id=11//setting is not the same as master.

Thread_concurrency = 2//thread concurrency (cpu*2)

Relay-log=mysql-log//Turn on the relay log

Relay-log-index=mysql-log.index//Set Relay-log-index file name

Save exit and restart MySQL.

Log in to MySQL:

[[email protected] mysql]# mysql-uroot-p//default password empty, directly hit enter

Mysql>change Master to

->master_host= ' 192.168.1.109 ',//master server IP

->mastet_user= ' repl ',//user name

->mastet_password= ' 123456 ',//password

->master_log_file= ' mysql-bin.000001 ',//master server binary log name

->master_log_pos=107;

Mysql>slave start; Start slave

mysql> SHOW SLAVE status\g; View slave status

1. Row ***************************

slave_io_state:

master_host:192.168.1.109

Master_user:repl

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000001

Read_master_log_pos:4

relay_log_file:mysql-relay-bin.000001

Relay_log_pos:4

relay_master_log_file:mysql-bin.000001

Slave_io_running:yes

Slave_sql_running:yes

..... omitted ...

Seconds_behind_master:null

If Slave_io_running and slave_sql_running are displayed as Yes, configure OK and the process may appear:

Slave I/o: Error connecting to master ' [email protected]:3306 '-retry-time:60 retries:86400, error_code:2003

The cause of the problem is the firewall interception, the solution is to modify the firewall configuration, simple and straightforward way is to shut down the master server firewall, execute/etc/init.d/iptables stop command.

Next test, create a database db_test in master and see if db_test exists in slave.

At this point the slave configuration is complete and the other is configured in this manner from the server.

4, Configuration Mysql-proxy, Agent service is best to deploy another machine, especially on-line operation, so here the test is also used a separate virtual machine, not deployed on the master or slave machine.

[[email protected] local]# TAR-ZXVF mysql-proxy-0.8.4-linux-glibc2.3-x86-32bit.tar.gz//Unzip

[[email protected] local]# ln-s mysql-proxy-0.8.4-linux-glibc2.3-x86-32bit mysql-proxy//Add a shortcut link

[[email protected] local]# Groupadd mysql-proxy//create user group

[[email protected] local]# useradd-g mysql-proxy mysql-proxy//create User

[[Email protected] local]# CD mysql-proxy//Enter Mysql-proxy directory

[Email protected] mysql-proxy]# chown-r root:mysql-proxy. Change the directory owner and owner group, with a point (.) behind the command. Represents the current directory

[[email protected] mysql-proxy]# vi/etc/profile.d/mysql-proxy.sh//Add MySQL Bin directory to the PATH variable,

exportPATH=$PATH:/usr/local/mysql-proxy/bin

[[email protected] mysql-proxy]# source/etc/profile//Let the configuration file take effect immediately

[[email protected] mysql-proxy]# Mysql-proxy--help-all//View Mysql-proxy command, the following message appears:




Let's start Mysql-proxy and do a simple test, but let's briefly introduce the Mysql-proxy command.

Mysql-proxy command

• --help-all ———— to get all the Help information

• -- Proxy-address=host:port ———— the address and port that the agent service listens on

• --admin-address=host:port ———— The address and port that the management module listens on /span>

• --proxy-backend-addresses=host:port ———— The address and port of the backend MySQL server (master server)

• p> --proxy-read-only-backend-addresses=host:port ———— back-end read-only MySQL server address and port (from server)

• --proxy-lua-script=file ———— The Lua script to complete the MySQL proxy function

• --daemon ———— start in daemon mode mysql-proxy

• --defaults-file=/path/to/conf_file_name ———— default configuration file path

• Span>--log-file=/path/to/log_file_name ———— log file name

• --log-level=level ———— Log level

• --log-use-syslog ———— syslog-based logging

• --user=user_name ———— running MySQL User of the-proxy process

[Email protected] mysql-proxy]# mysql-proxy--daemon--log-level=debug--log-file=/var/log/mysql-proxy.log--plugins　　= "Proxy"--proxy-backend-addresses= "192.168.1.109:3306"--proxy-read-only-backend-addresses= "192.168.1.110:3306" Start Mysql-proxy[[email protected] mysql-proxy]# Tail/var/log/mysql-proxy.log//View boot log

2014-05-10 00:09:22: (critical) plugin proxy 0.8.4 started
2014-05-10 00:09:22: (Debug) Max Open file-descriptors = 1024
2014-05-10 00:09:22: (message) proxy listening on port:4040
2014-05-10 00:09:22: (message) added Read/write backend:192.168.1.109:3306
2014-05-10 00:09:22: (message) added Read-only backend:192.168.1.110:3306

[[email protected] mysql-proxy] #netstat-ntulp | grep:4040//viewing the monitored ports

TCP 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 10056/mysql-proxy

Now we need to create a remote login MySQL account in the master server.

Master

mysql> GRANT ALL ON *.* TO [email protected] ‘192.168.1.%‘ IDENTIFIED BY  ‘123456‘ ;  Query OK, 0 rows affected (0.07 sec) mysql> FLUSH PRIVILEGES;  Query OK, 0 rows affected (0.04 sec)To test from the server: Slave: [[email protected] ~] # mysql -uroot -p123456 -h192.168.1.112 --port=4040 This will connect to the Mysql-proxy server, if an error occurs: 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.1.112 ' (113), please modify the firewall configuration or shutdown. Let's implement the read and write separation, mysql-proxy itself will not achieve read and write separation, mainly rely on Lua script implementation. [[email protected] mysql-proxy]# Killall mysql-proxy//kill Mysql-proxy Process [[email protected]  mysql-proxy]# Netstat-ntlup | grep 4040//See if the process is stopped [[Email protected] mysql-proxy]# cd share/doc/mysql-proxy/ [[email  Protected] mysql-proxy]# ls | grep Rw-splitting.lua//view read/write detach script, Lua language implementation rw-splitting.lua[[email protected] mysql-proxy]#  Mysql-proxy--daemon--log-level=debug--log-file=/var/log/mysql-proxy.log--plugins= "proxy"-- Proxy-backend-addresses= "192.168.1.109:3306"--proxy-read-only-backend-addresses= "192.168.1.110:3306"-- proxy-lua-script= "/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" [[email protected]  Mysql-proxy] #在slave server to do the test, OK, completed, Mysql-proxy realize the MySQL cluster read and write separation is complete, this mode is the most basic MySQL cluster mode, can also achieve load balancing, Follow up on MySQL's deeper clusters and share progress together. ^_^ &Nbsp;