mysql-5.6+mysql-proxy构建主从复制与读写分离

环境：

系统：CentOS-6.5

MySQL-master

IP:192.168.1.10

MySQL-slave

IP:192.168.1.11

Proxry

IP:192.167.1.12

——安装MySQL

1）创建mysql用户

1

[root@mater ~]# mysql -M -s /sbin/nologin mysql

2）安装软件所需依赖包

1

[root@master ~]# yum install gcc gcc-c++ autoconf automake ncurses-devel libtool-ltdl-devel* libaio libaio-devel -y

3）解压安装MySQL [这里下载的是免安装版本]

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

[root@master linux]# tar fzvx mysql-5.6.16-linux-x86_64.tar.gz -C /usr/local/

[root@master linux]# mv mysql-5.6.16-linux-x86_64 mysql

[root@master local]# cd mysql/

[root@master mysql]# chown -R root:mysql .

[root@master mysql]# chown -R mysql:mysql data/

[root@master mysql]# ./scripts/mysql_install_db --user=mysql \

> --basedir=/usr/local/mysql/ \

> --datadir=/usr/local/mysql/data/

WARNING: The host 'master' could not be looked upwith /usr/local/mysql//bin/resolveip.

This probably means that your libc libraries are not100 % compatible

with this binary MySQL version. The MySQL daemon, mysqld, should work

normally withthe exception that host name resolving will not work.

This means that you shoulduse IP addresses instead of hostnames

when specifying MySQL privileges !

Installing MySQL system tables...2014-04-2619:57:060 [Warning] TIMESTAMP with implicit DEFAULT valueis deprecated. Pleaseuse --explicit_defaults_for_timestamp server option (see documentationfor more details).

2014-04-2619:57:061258 [Note] InnoDB: Using atomics to ref count buffer pool pages

2014-04-2619:57:061258 [Note] InnoDB: The InnoDB memory heapis disabled

2014-04-2619:57:061258 [Note] InnoDB: Mutexes and rw_locksuse GCC atomic builtins

2014-04-2619:57:061258 [Note] InnoDB: Compressed tablesuse zlib 1.2.3

2014-04-2619:57:061258 [Note] InnoDB: Using Linuxnative AIO

2014-04-2619:57:061258 [Note] InnoDB: Using CPU crc32 instructions

2014-04-2619:57:061258 [Note] InnoDB: Initializing buffer pool, size =128.0M

2014-04-2619:57:071258 [Note] InnoDB: Completed initialization of buffer pool

2014-04-2619:57:071258 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: anew database to be created!

2014-04-2619:57:071258 [Note] InnoDB: Setting file ./ibdata1 size to12 MB

2014-04-2619:57:071258 [Note] InnoDB: Database physically writes the file full: wait...

2014-04-2619:57:071258 [Note] InnoDB: Setting log file ./ib_logfile101 size to48 MB

2014-04-2619:57:111258 [Note] InnoDB: Setting log file ./ib_logfile1 size to48 MB

2014-04-2619:57:151258 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

2014-04-2619:57:151258 [Warning] InnoDB: New log files created, LSN=45781

2014-04-2619:57:151258 [Note] InnoDB: Doublewrite buffer not found: creatingnew

2014-04-2619:57:151258 [Note] InnoDB: Doublewrite buffer created

2014-04-2619:57:151258 [Note] InnoDB: 128 rollback segment(s) are active.

2014-04-2619:57:151258 [Warning] InnoDB: Creating foreign key constraint system tables.

2014-04-2619:57:161258 [Note] InnoDB: Foreign key constraint system tables created

2014-04-2619:57:161258 [Note] InnoDB: Creating tablespace and datafile system tables.

2014-04-2619:57:161258 [Note] InnoDB: Tablespace and datafile system tables created.

2014-04-2619:57:161258 [Note] InnoDB: Waitingfor purge to start

2014-04-2619:57:161258 [Note] InnoDB: 5.6.16started; log sequence number 0

2014-04-2619:57:161258 [Note] RSA privatekey file not found: /usr/local/mysql/data//private_key.pem. Some authentication plugins will not work.

2014-04-2619:57:161258 [Note] RSA publickey file not found: /usr/local/mysql/data//public_key.pem. Some authentication plugins will not work.

2014-04-2619:57:171258 [Note] Binlog end

2014-04-2619:57:171258 [Note] InnoDB: FTS optimize thread exiting.

2014-04-2619:57:171258 [Note] InnoDB: Starting shutdown...

2014-04-2619:57:191258 [Note] InnoDB: Shutdown completed; log sequence number1625977

OK

Filling help tables...2014-04-2619:57:190 [Warning] TIMESTAMP with implicit DEFAULT valueis deprecated. Pleaseuse --explicit_defaults_for_timestamp server option (see documentationfor more details).

2014-04-2619:57:191281 [Note] InnoDB: Using atomics to ref count buffer pool pages

2014-04-2619:57:191281 [Note] InnoDB: The InnoDB memory heapis disabled

2014-04-2619:57:191281 [Note] InnoDB: Mutexes and rw_locksuse GCC atomic builtins

2014-04-2619:57:191281 [Note] InnoDB: Compressed tablesuse zlib 1.2.3

2014-04-2619:57:191281 [Note] InnoDB: Using Linuxnative AIO

2014-04-2619:57:191281 [Note] InnoDB: Using CPU crc32 instructions

2014-04-2619:57:191281 [Note] InnoDB: Initializing buffer pool, size =128.0M

2014-04-2619:57:191281 [Note] InnoDB: Completed initialization of buffer pool

2014-04-2619:57:191281 [Note] InnoDB: Highest supported file formatis Barracuda.

2014-04-2619:57:191281 [Note] InnoDB: 128 rollback segment(s) are active.

2014-04-2619:57:191281 [Note] InnoDB: Waitingfor purge to start

2014-04-2619:57:191281 [Note] InnoDB: 5.6.16started; log sequence number 1625977

2014-04-2619:57:191281 [Note] RSA privatekey file not found: /usr/local/mysql/data//private_key.pem. Some authentication plugins will not work.

2014-04-2619:57:191281 [Note] RSA publickey file not found: /usr/local/mysql/data//public_key.pem. Some authentication plugins will not work.

2014-04-2619:57:191281 [Note] Binlog end

2014-04-2619:57:191281 [Note] InnoDB: FTS optimize thread exiting.

2014-04-2619:57:191281 [Note] InnoDB: Starting shutdown...

2014-04-2619:57:211281 [Note] InnoDB: Shutdown completed; log sequence number1625987

OK

To start mysqld at boot time you have to copy

support-files/mysql.server to the right placefor your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To doso, start the server, then issue the following commands:

  /usr/local/mysql//bin/mysqladmin -u root password 'new-password'

  /usr/local/mysql//bin/mysqladmin -u root -h master password 'new-password'

Alternatively you can run:

  /usr/local/mysql//bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created bydefault.  This is

strongly recommendedfor production servers.

See the manual for more instructions.

You can start the MySQL daemonwith:

  cd . ; /usr/local/mysql//bin/mysqld_safe &

You can test the MySQL daemonwith mysql-test-run.pl

  cd mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

The latest information about MySQLis available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

New defaultconfig file was created as/usr/local/mysql//my.cnf and

will be used by default by the server when you start it.

You may edit this file to change server settings

WARNING: Default config file /etc/my.cnf exists on the system

This file will be read bydefault by the MySQL server

If you donot want to use this, either remove it, or usethe

--defaults-file argument to mysqld_safe when starting the server.

4）复制配置文件并设置启动脚本

1

2

3

4

5

[root@node1 mysql]# cp my.cnf /etc/my.cnf

cp：是否覆盖"/etc/my.cnf"？ y

[root@node1 mysql]# cp support-files/mysql.server /etc/init.d/mysqld[root@master mysql]# chmod +x /etc/init.d/mysqld

[root@master mysql]# chkconfig --add mysqld

[root@master mysql]# chkconfig mysqld on

5）开启二进制日志

1

2

3

4

5

6

7

8

[root@master mysql]# mkdir /usr/local/mysql/logs

[root@master mysql]# chown -R mysql:mysql /usr/local/mysql/logs/

[root@master mysql]# vi /etc/my.cnf

[mysqld]

#开启二进制日志

log-bin = /usr/local/mysql/logs/sql-bin

#唯一编号 [一般取IP的后两位]

server_id = 10

6）设置MySQL环境变量

1

2

3

[root@master mysql]# vi /etc/profile.d/mysql.sh

PATH=$PATH:/usr/local/mysql/bin:

[root@master mysql]# source /etc/profile

7）启动MySQL

1

2

3

4

5

[root@master mysql]# service mysqld start

Starting MySQL............ SUCCESS!

[root@master mysql]# netstat -antup |grep3306

tcp        0     0 :::3306                    :::*                        LISTEN      1768/mysqld

#salve步骤一样，这里省略...

——主从复制

1）在master上的MySQL创建相关用户

1

2

3

4

5

6

7

8

9

10

11

[root@node1 mysql]# mysql -u root

Welcome to the MySQL monitor.  Commands endwith ; or \g.

Your MySQL connection idis 1

Server version: 5.6.16-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle isa registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;'or '\h' for help. Type '\c'to clear the current input statement.

mysql> grant replication slave on *.* to'repl'@'192.168.1.11'identified by '12345';

Query OK, 0 rows affected (1.49sec)

2）查询二进制日志

1

2

3

4

5

6

7

mysql> show master status;

+----------------+----------+--------------+------------------+-------------------+

| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+----------------+----------+--------------+------------------+-------------------+

| sql-bin.000001|      120 |              |                  |                   |

+----------------+----------+--------------+------------------+-------------------+

1 row in set(0.12 sec)

3）配置slave服务器

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

[root@slave ~]# mysql -u root

Welcome to the MySQL monitor. Commands endwith ; or \g.

Your MySQL connection idis 3

Server version: 5.5.25-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle isa registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;'or '\h' for help. Type '\c'to clear the current input statement.

mysql> change master to[root@slave1 ~]# mysql -u root

Welcome to the MySQL monitor. Commands endwith ; or \g.

Your MySQL connection idis 3

Server version: 5.5.25-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle isa registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;'or '\h' for help. Type '\c'to clear the current input statement.

mysql> change master to

   -> master_host='192.168.1.254',

   -> master_user='repl',

   -> master_password='12345',

   -> master_log_file='sql-bin.000001',

   -> master_log_pos=120;

Query OK, 0 rows affected (0.25sec)

4）启动slave检测是否同步成功

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

mysql> start slave;

Query OK, 0rows affected (0.00sec)

mysql> show slave status\G;

***************************1. row ***************************

               Slave_IO_State: Waitingfor master to send event

                  Master_Host:192.168.1.10

                  Master_User: repl

                  Master_Port:3306

                Connect_Retry:60

              Master_Log_File: sql-bin.000001

          Read_Master_Log_Pos:120

               Relay_Log_File: node2-relay-bin.000002

                Relay_Log_Pos:281

        Relay_Master_Log_File: sql-bin.000001

             Slave_IO_Running: Yes        #为Yes说明成功

            Slave_SQL_Running: Yes        #为Yes说明成功

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno:0

                   Last_Error:

                 Skip_Counter:0

          Exec_Master_Log_Pos:120

              Relay_Log_Space:454

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos:0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master:0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno:0

                Last_IO_Error:

               Last_SQL_Errno:0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id:10

                  Master_UUID: 8c55a12b-cd22-11e3-bc81-0050563f4084

             Master_Info_File: /usr/local/mysql/data/master.info

                    SQL_Delay:0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waitingfor the slave I/O thread to update it

           Master_Retry_Count:86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position:0

1 row in set(0.05 sec)

ERROR:

No query specified

——测试

1）在master创建一个数据表

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

mysql> create database user;

Query OK, 1row affected (0.10 sec)

mysql> create table user.test1(idint);

Query OK, 0rows affected (0.80sec)

mysql> insert into user.test1 values(1);

Query OK, 1row affected (0.12 sec)

mysql> insert into user.test1 values(2);

Query OK, 1row affected (0.15 sec)

mysql> select * from user.test1;

+------+

| id   |

+------+

|    1|

|    2|

+------+

2 rows in set(0.02 sec)

2）检测slave是否同步。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

| user               |

+--------------------+

5 rows in set(0.34 sec)

mysql> select * from user.test1;

+------+

| id   |

+------+

|    1|

|    2|

+------+

2 rows in set(0.04 sec)

#说明同步完成。

——配置读写分离

1）创建相关用户和组

1

2

[root@Proxy ~]# groupadd -g3306 mysql-proxy

[root@Proxy ~]# useradd -u3306 -g mysql-proxy -s /sbin/nologin -M mysql-proxy

2）编译安装mysql-proxy

1

2

3

4

5

[root@Proxy ~]# cd /Linux/

[root@Proxy Linux]# tar zfvx mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz -C /usr/local/

[root@Proxy local]# mv mysql-proxy-0.8.3-linux-rhel5-x86-64bit/ mysql-proxy

[root@Proxy local]# cd mysql-proxy/

[root@Proxy mysql-proxy]# chown -R root:mysql-proxy .

3）设置mysql-proxy环境变量

1

2

3

[root@Proxy mysql-proxy]# vi /etc/profile.d/mysql-proxy.sh

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

[root@Proxy mysql-proxy]# source /etc/profile

4）启动mysql-proxy

1

2

3

4

[root@Proxy mysql-proxy]# mysql-proxy --daemon --log-level=debug \

--log-file=/var/log/mysql-proxy.log --plugins="proxy"\

--proxy-backend-addresses="192.168.1.10:3306"\

--proxy-read-only-backend-addresses="192.168.1.11:3306"

5）查看是否启动[默认端口4040]

1

2

[root@Proxy mysql-proxy]# netstat -antup|grep4040

tcp        0     0 0.0.0.0:4040               0.0.0.0:*                   LISTEN     1326/mysql-proxy

6）客户端测试连接[Proxy IP 地址]

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

[root@slave ~]# mysql -uuser -p -h192.168.1.12--port=4040

Enter password:

Welcome to the MySQL monitor. Commands endwith ; or \g.

Your MySQL connection idis 5

Server version: 5.5.25-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle isa registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;'or '\h' for help. Type '\c'to clear the current input statement.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

| user               |

+--------------------+

7）设置读写分离

1

2

3

4

5

6

7

8

9

10

11

12

13

[root@Proxy mysql-proxy]# killall mysql-proxy

[root@Proxy mysql-proxy]# netstat -antup |grep4040

[root@Proxy mysql-proxy]# cd /usr/local/mysql-proxy

[root@Proxy mysql-proxy]# cp lib/mysql-proxy/lua/admin.lua share/doc/mysql-proxy/

——启动mysql-proxy

[root@Proxy mysql-proxy]# mysql-proxy --daemon --log-level=debug \

--log-file=/var/log/mysql-proxy.log --plugins="proxy"\

--proxy-backend-addresses="192.168.1.10:3306"

--proxy-read-only-backend-addresses="192.168.1.11:3306"\

--proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"\

--plugins=admin --admin-username="admin"\

--admin-password="admin"\

--admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

8）启动mysql-proxy

1

2

3

4

5

6

7

8

[root@Proxy mysql-proxy]# mysql-proxy --daemon --log-level=debug \

--log-file=/var/log/mysql-proxy.log --plugins="proxy"\

--proxy-backend-addresses="192.168.1.252:3306"

--proxy-read-only-backend-addresses="192.168.1.254:3306"\

--proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"\

--plugins=admin --admin-username="admin"\

--admin-password="admin"\

--admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

9）测试读写分离状态

1

2

3

客户端执行

[root@node1 /]# mysql -uuser -p12345 -h192.168.1.10--port=4040 -e "select user from mysql.user;"

[root@node2 /]# mysql -uuser -p12345 -h192.168.1.11--port=4040 -e "create database user1;"

10）优化配置启动脚本，并修连接端口

1

2

3

4

5

6

7

8

9

10

11

[root@Proxy mysql-proxy]# vi /etc/sysconfig/mysql-proxy

ADMIN_USER="admin"

ADMIN_PASSWORD="admin"

ADMIN_ADDRESS=""

ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

PROXY_ADDRESS="0.0.0.0:3306"

PROXY_USER="mysql-proxy"

PROXY_OPTIONS="--daemon --log-level=info --log-file="/var/log/mysql-proxy.log"

--plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.1.12:3306

--proxy-read-only-backend-addresses=192.168.1.13:3306

--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

11）添加启动脚本

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

[root@Proxy mysql-proxy]# vi /etc/init.d/mysql-proxy

#!/bin/bash

#

# mysql-proxy This script starts and stops the mysql-proxy daemon

#

# chkconfig: - 78 30

# processname: mysql-proxy

# description: mysql-proxyis a proxy daemonfor mysql

# Source functionlibrary.

. /etc/rc.d/init.d/functions

prog="/usr/local/mysql-proxy/bin/mysql-proxy"

# Source networking configuration.

if [ -f /etc/sysconfig/network ]; then

. /etc/sysconfig/network

fi

# Check that networkingis up.

[ ${NETWORKING} ="no" ] && exit 0

# Set defaultmysql-proxy configuration.

ADMIN_USER="admin"

ADMIN_PASSWD="admin"

ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

PROXY_OPTIONS="--daemon"

PROXY_PID=/var/run/mysql-proxy.pid

PROXY_USER="mysql-proxy"

# Source mysql-proxy configuration.

if [ -f /etc/sysconfig/mysql-proxy ]; then

. /etc/sysconfig/mysql-proxy

fi

RETVAL=0

start() {

echo -n $"Starting $prog: "

daemon $prog $PROXY_OPTIONS --pid-file=$PROXY_PID

--proxy-address="$PROXY_ADDRESS"--user=$PROXY_USER

--admin-username="$ADMIN_USER"--admin-lua-script="$ADMIN_LUA_SCRIPT"

--admin-password="$ADMIN_PASSWORD"

RETVAL=$?

echo

if [ $RETVAL -eq 0 ]; then

touch /var/lock/subsys/mysql-proxy

fi

}

stop() {

echo -n $"Stopping $prog: "

killproc -p $PROXY_PID -d3 $prog

RETVAL=$?

echo

if [ $RETVAL -eq 0 ]; then

rm -f /var/lock/subsys/mysql-proxy

rm -f $PROXY_PID

fi

}

# See how we were called.

case "$1" in

start)

start

;;

stop)

stop

;;

restart)

stop

start

;;

condrestart|try-restart)

if status -p $PROXY_PIDFILE $prog >&/dev/null; then

stop

start

fi

;;

status)

status -p $PROXY_PID $prog

;;

*)

echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}"

RETVAL=1

;;

esac

exit $RETVAL

本文出自 “ˉ、穎濤┃﹎” 博客，请务必保留此出处http://hypocritical.blog.51cto.com/3388028/1404210