Mysql cluster 1: master-slave replication, load balancing through mysql-proxy

There are many mysql cluster architecture methods, and different architectures are implemented according to different requirements. Simply put, mysql replication is the replication function of Mysql. The modes include master-slaves, master-slaves, master-slaves, and so on can have multiple layers, so now I want to talk about the master-slaves mode (the principles of other modes are basically the same ), then, use the mysql-proxy officially provided by Mysql to implement read/write splitting to achieve load balancing.

Environment:

HOST: master: 192.168.1.109, slave1: 192.168.1.110, mysqlProxy: 192.168.1.112. (Create virtual machine simulation on workstation10 platform)

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:

[Root @ localhost ~] # Cd/usr/local // enter the installation directory

[Root @ localhost local] # tar-zxvf mysql-5.5.37-linux2.6-i686.tar.gz // unzip the mysql Binary Package

[Root @ localhost local] # ln-s mysql-5.5.37-linux2.6-i686 mysql // unzip and add the link

[Root @ localhost local] # groupadd mysql // Add a mysql user group

[Root @ localhost local] # useradd-g mysql // Add a mysql user account

[Root @ localhost local] # cd mysql // enter the mysql directory after installation

[Root @ localhost mysql] # chown-R root: mysql. // modify the file group and owner. Note that the command is followed by a vertex indicating the current directory

[Root @ localhost mysql] # chown-R mysql: mysql data // change the owner of the data directory to mysql

[Root @ localhost mysql] # cp support-files/my-large.cnf/etc/my. cnf // copy the mysql configuration file under the etc directory and rename it my. cnf

[Root @ localhost mysql] # cp support-files/mysql. server/etc/init. d/mysqld // copy the mysql STARTUP script to the/etc/init. d/directory.

[Root @ localhost mysql] # service mysqld start // start mysql

So far, mysql installation is complete, followed by the master-slave configuration of mysql. (Install mysql on the other two hosts)

 

2. Configure master:

[Root @ localhost mysql] # vi/etc/my. cnf // edit the configuration file

Server-id = 1 // The default value is 1. If not, change it to 1.

Thread_concurrency = 2 // number of concurrent threads (CPU * 2)

Log-bin = mysql-bin // enable the binary log function

Save and exit, and restart mysql.

Log on to mysql:

[Root @ localhost mysql] # mysql-uroot-p // the default password is null. Press enter to enter

Mysql> grant replication slave on *. * to repl @ '192. 192.% 'identified by '123 ';

Mysql> flush privileges;

Mysql> show grants for 'repl' @ '192. 192.% '; // view the authorization. If there is a record, OK

Mysql> show master status \ G; // you can view the status of the master server, including the binary log file name and position)

So far, the mysql configuration is complete.

 

3. Configure slave

[Root @ localhost mysql] # vi/etc/my. cnf // edit the configuration file

Server-id = 11 // set it to be different from the master,

Thread_concurrency = 2 // number of concurrent threads (CPU * 2)

Relay-log = mysql-log // enable relay log

Relay-log-index = mysql-log.index // set relay-log-index file name

Save and exit, and restart mysql.

Log on to mysql:

[Root @ localhost mysql] # mysql-uroot-p // the default password is null. Press enter to enter

Mysql> change master

-> Master_host = '192. 168.1.109 ', // master server ip Address

-> Mastet_user = 'repl', // User Name

-> Mastet_password = '000000', // Password

-> Master_log_file = 'mysql-bin.000001 ', // binary log name of the master server

-> Master_log_pos = 107;

Mysql> slave start; // start slave

Mysql> show slave status \ G; // view the 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, the configuration is OK, which may occur during the process:

Slave I/O: error connecting to master 'repl @ 192.168.1.109: 3306 '-retry-time: 60 retries: 86400, Error_code: 2003

The cause is that the firewall is blocked. The solution is to modify the firewall configuration. The simple and direct method is to turn off the firewall of the master server and run the/etc/init. d/iptables stop command.

Next, create a database db_test in the master to check whether db_test exists in the slave.

Now the slave configuration is complete, and other slave servers are configured in this way.

 

4. Configure mysql-proxy. It is best to deploy another machine for the proxy service, especially after it is launched. Therefore, a separate virtual machine is used for testing and is not deployed on the master or slave machine.

[Root @ localhost local] # tar-zxvf mysql-proxy-0.8.4-linux-glibc2.3-x86-32bit.tar.gz // unzip

[Root @ localhost local] # ln-s mysql-proxy-0.8.4-linux-glibc2.3-x86-32bit mysql-proxy // Add a shortcut

[Root @ localhost local] # groupadd mysql-proxy // create a user group

[Root @ localhost local] # useradd-g mysql-proxy // create a user

[Root @ localhost local] # cd mysql-proxy // enter the mysql-proxy directory

[Root @ localhost mysql-proxy] # chown-R root: mysql-proxy. // change the directory owner and group. A dot (.) After the command indicates the current directory

[Root @ localhost mysql-proxy] # vi/etc/profile. d/mysql-proxy.sh // Add the bin directory of mysql to the path variable,

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

[Root @ localhost mysql-proxy] # source/etc/profile // make the configuration file take effect immediately

[Root @ localhost mysql-proxy] # mysql-proxy -- help-all // view the mysql-proxy command, the following information is displayed:

 


Next we will start mysql-proxy for a simple test, but we should first briefly introduce the mysql-proxy command.

Mysql-proxy command

• -- Help-all ---- get all help information

• -- Proxy-address = host: port ---- address and port of the proxy service listener

• -- Admin-address = host: port ---- address and port listened by the Management Module

• -- Proxy-backend-addresses = host: port ---- address and port of the backend mysql Server (master server)

• -- Proxy-read-only-backend-addresses = host: port ---- address and port of the backend read-only mysql Server (slave server)

• -- Proxy-lua-script = file ---- Lua script for completing the mysql proxy Function

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

• -- Defaults-file =/path/to/conf_file_name ---- default configuration file path

• -- Log-file =/path/to/log_file_name ---- log file Name

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

• -- Log-use-syslog ---- syslog-based logging

• -- User = user_name ---- user who runs the mysql-proxy process

[Root @ localhost 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 [root @ localhost mysql-proxy] # tail/var/log/mysql-proxy.log // view startup log

2014-05-00: 09: 22: (critical) plugin proxy 0.8.4 started
2014-05-00: 09: 22: (debug) max open file-descriptors = 1024
2014-05-00: 09: 22: (message) proxy listening on port: 4040
2014-05-00: 09: 22: (message) added read/write backend: 192.168.1.109: 3306
2014-05-00: 09: 22: (message) added read-only backend: 192.168.1.110: 3306

[Root @ localhost mysql-proxy] # netstat-ntulp | grep: 4040 // view the listening port

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 root@ '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)Perform a test on the slave server: Slave: [root@bogon ~] # mysql -uroot -p123456 -h192.168.1.112 --port=4040 In this way, the Mysql-Proxy server is connected. If an ERROR occurs: ERROR 2003 (HY000): Can't connect to MySQL server on '100. 168.1.112 '(113). Modify or disable the firewall. The following describes how to implement read/write splitting. Mysql-Proxy itself does not implement read/write splitting, mainly based on the Lua script. [Root @ localhost mysql-proxy] # killall mysql-proxy // kill the mysql-proxy process [root @ localhost mysql-proxy] # netstat-ntlup | grep 4040 // check whether the process is stop [root @ localhost mysql-proxy] # cd share/doc/mysql-proxy/[root @ localhost mysql-proxy] # ls | grep rw-splitting.lua // view the read/write splitting script, lua language rw-splitting.lua [root @ localhost mysql-proxy] # mysql-proxy -- daemon -- log-level = debug -- log-file =/var/log/mysql-proxy.log -- plugins = "prox Y "-- 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 "[root @ localhost mysql-proxy] # In slave test on the server, OK. The mysql-proxy implements the read/write splitting of the mysql cluster. This mode is the most basic mode of the MySQL cluster and can also achieve load balancing, follow-up research on the deeper MySQL cluster and share the progress together. Pai_^