一、安裝部署mysql

1、安裝兩臺伺服器

ip及hostname如下：

192.168.9.241 mytest1 --主節點

192.168.9.242 mytest2 --從節點

192.168.9.240 viptest --浮動ip

現在linux安裝時，好多安裝了mysql，下面以從節點安裝為例進行說明

[root@mytest2 mysqldb]# rpm -qa | grep mysql

對顯示出來的安裝包，採用如下方式進行解除安裝

[root@mytest2 u02]# rpm -qa | grep mysql

mysql-libs-5.1.66-2.el6_3.x86_64

mysql-5.1.66-2.el6_3.x86_64

mysql-server-5.1.66-2.el6_3.x86_64

qt-mysql-4.6.2-25.el6.x86_64

You have new mail in /var/spool/mail/root

[root@mytest2 u02]# rpm -ev --nodeps mysql-libs-5.1.66-2.el6_3.x86_64

[root@mytest2 u02]# rpm -ev --nodeps mysql-5.1.66-2.el6_3.x86_64

[root@mytest2 u02]# rpm -ev --nodeps mysql-server-5.1.66-2.el6_3.x86_64

[root@mytest2 u02]# rpm -ev --nodeps qt-mysql-4.6.2-25.el6.x86_64

2、準備mysql安裝包

下載網址：dev.mysql.com，然後選擇cluster，然後在網頁中出現的選擇平臺中，選擇linux generic！在這裡選擇所需要tar包，我這裡用的是mysql-cluster-gpl-7.0.37-linux-x86_64-glibc23.tar

下載完成後，使用ftp傳送到伺服器上面

3、安裝部署mysql

--建立相應目錄

[root@mytest2 mysql]# mkdir -p /usr/local/mysql --用於存放剛才解壓的檔案，如mysql的bin目錄等

[root@mytest2 mysql]# mkdir -p /u01/mysql/data/mysqldb --用於儲存資料檔案(innodb)

--對該包進行解壓

[root@mytest2 tmp]# tar -zxf mysql-cluster-gpl-7.0.37-linux-x86_64-glibc23.tar.gz

解壓後，生成了一個 mysql-cluster-gpl-7.0.37-linux-x86_64-glibc23的目錄

進入該目錄，將目錄裡的檔案移動到/usr/local/mysql

[root@mytest2 mysql-cluster-gpl-7.0.37-linux-x86_64-glibc23]# mv * /usr/local/mysql/

--編輯環境變數

[root@mytest2 tmp]# vi /etc/profile

新增如下：

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

export PATH

[root@mytest2 tmp]# source /etc/profile --使修改生效

[root@mytest2 mysql]# scripts/mysql_install_db --user=mysql --datadir=/u01/mysql/data/mysqldb

Installing MySQL system tables...

OK

Filling help tables...

OK

To start mysqld at boot time you have to copy

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

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

To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'

./bin/mysqladmin -u root -h mytest2 password 'new-password'

Alternatively you can run:

./bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default. This is

strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

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

Please report any problems with the ./bin/mysqlbug script!

注意，在這裡建立資料庫時，進入scripts執行時，需要新增—dirbase，否則會報目錄不存在的。

--複製mysql.server

[root@mytest1 support-files]# pwd

/usr/local/mysql/support-files

[root@mytest1 support-files]# ls -lrt

total 32

-rw-r--r--. 1 mysql mysql 773 Oct 9 21:46 magic

-rwxr-xr-x. 1 mysql mysql 10880 Oct 9 22:42 mysql.server

-rwxr-xr-x. 1 mysql mysql 894 Oct 9 22:42 mysql-log-rotate

-rwxr-xr-x. 1 mysql mysql 1061 Oct 9 22:42 mysqld_multi.server

-rw-r--r--. 1 mysql mysql 1126 Oct 9 22:42 my-default.cnf

-rwxr-xr-x. 1 mysql mysql 1153 Oct 9 22:42 binary-configure

[root@mytest01 support-files]# cp mysql.server /etc/rc.d/init.d/mysqld

--編輯my.cnf引數檔案

[root@mytest2 support-files]# cp my-medium.cnf /etc/my.cnf

編輯my.cnf 加入

datadir=/u01/mysql/data/mysqldb

--啟動資料庫，並設定密碼

[root@mytest2 data]# service mysqld start

Starting MySQL.[ OK ]

[root@mytest2 data]# mysqladmin -u root password 'petrel' --設定密碼

[root@mytest2 data]# mysql -uroot -ppetrel

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.1.67-ndb-7.0.37-cluster-gpl-log MySQL Cluster Server (GPL)

Oracle is a 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>

主節點的安裝部署也採用同樣的方法即可

二、部署主主

1、關閉或者配置防火牆

[root@mytest1 ~]# service iptables status

iptables: Firewall is not running.

如果開啟的，使用如下操作

[root@mytest1 ~]# service iptables stop

[root@mytest1 ~]# chkconfig iptables off

2、修改MySQL配置檔案

Mytest1的配置：

[root@mytest1 ~]# cat /etc/my.cnf | grep -v "#"

[mysqld]

log-bin = mysql-bin

server-id = 10

datadir = /u01/mysql/data/mysqldb

binlog-do-db = mytest

auto-increment-increment = 2

auto-increment-increment = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

其它的引數，我們就不在這裡列出來，主要是把主要的引數加進去

[root@mytest2 ~]# cat /etc/my.cnf | grep -v "#"

[mysqld]

log-bin = mysql-bin

server-id = 10

datadir = /u01/mysql/data/mysqldb

binlog-do-db = mytest

auto-increment-increment = 2

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

說明：

在主主同步配置時，需要將兩臺伺服器的auto_increment_increment增長量都配置為2，而要把auto_increment_offset分別配置為1和2.這樣才可以避免兩臺伺服器同時做更新時自增長欄位的值之間發生衝突。

如果不新增binlog-do-db指定要複製的資料庫、binlog-ignore-db，指定要跳過的資料庫，表示全部資料庫都進行復制，這裡我們只是複製mytest這個資料庫。

3、建立授權使用者

在192.168.9.241上建立授權使用者

MySQL> grant replication slave on *.* to 'conuser'@'192.168.9.242' identified by 'conuser';

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

在192.168.9.242上建立授權使用者

MySQL> grant replication slave on *.* to 'conuser'@'192.168.9.241' identified by 'conuser';

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

說明，建立的連線使用者，交換資料用，可以測試一下！

4、建立主主模式

Mytest1上面進行如下操作

MySQL> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G;

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

File: mysql-bin.000002

Position: 439

Binlog_Do_DB: mytest

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

記錄master_log_file、master_log_pos值

記住file為mysql-bin.000002，且position為439

在mytest2上面進行同樣操作：

MySQL> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G;

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

File: mysql-bin.000001

Position: 2528

Binlog_Do_DB: mytest

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

同樣記錄下file及position

5、設定主伺服器：

Mytest1執行如下操作：

mysql> change master to master_host='192.168.9.242',master_user='conuser',master_password='conuser',master_log_file='mysql-bin.000001',master_log_pos=2528;

Query OK, 0 rows affected (0.00 sec)

mytest2執行如下操作：

mysql> change master to master_host='192.168.9.241',master_user='conuser',master_password='conuser',master_log_file='mysql-bin.000002',master_log_pos=439;

Query OK, 0 rows affected (0.00 sec)

執行完成後分別在兩臺機器上執行

mysql>unlock tables;

Query OK, 0 rows affected (0.00 sec)

MySQL> start slave;

Query OK, 0 rows affected (0.00 sec)

MySQL> show slave status\G;

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.9.243

Master_User: conuser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 439

Relay_Log_File: mytest2-relay-bin.000004

Relay_Log_Pos: 602

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: 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: 439

Relay_Log_Space: 940

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: 20

Master_UUID: a0cd8918-5c44-11e5-a730-005056acc647

Master_Info_File: /u01/mysql/data/mysqldb/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for 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.00 sec)

ERROR:

No query specified

如果有報錯，會在後面的error體現如來。在這裡，我們可以看到：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

這兩項為yes，並且沒有報錯證明是成功的。

6、MySQL主主同步測試

這一步就省略了，我的測試是成功的

三、部署keepalived

安裝缺失的包

[root@mytest2 ~]# yum install -y popt popt-devel openssl openssl-devel gcc

Loaded plugins: fastestmirror, refresh-packagekit

Loading mirror speeds from cached hostfile

Setting up Install Process

Package popt-1.13-7.el6.x86_64 already installed and latest version

Package popt-devel-1.13-7.el6.x86_64 already installed and latest version

Package openssl-1.0.0-27.el6.x86_64 already installed and latest version

Package openssl-devel-1.0.0-27.el6.x86_64 already installed and latest version

Package gcc-4.4.7-3.el6.x86_64 already installed and latest version

Nothing to do

如果沒有安裝，就會自動安裝上去。

解壓重編譯keepalived

[root@mytest2 u01]# ls -lrt

total 467456

-rw-r--r--. 1 root root 330361 Sep 16 10:57 keepalived-1.2.18.tar.gz

[root@mytest2 u01]# tar -zxf mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar.gz

解壓後，生成了一個目錄為

keepalived-1.2.18

[root@mytest2 u01]# cd keepalived-1.2.18

[root@mytest2 keepalived-1.2.18]# ./configure

[root@mytest2 keepalived-1.2.18]# make && make install

編譯完成，並沒有報錯。

複製檔案，並進行相應的配置

[root@mytest2 u01]# cp /usr/local/etc/rc.d/init.d/keepalived /etc/init.d/

[root@mytest2 u01]# cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/

[root@mytest2 u01]# chmod +x /etc/init.d/keepalived

[root@mytest2 u01]# chkconfig --add keepalived

[root@mytest2 u01]# chkconfig keepalived on

[root@mytest2 u01]# mkdir /etc/keepalived

[root@mytest2 u01]# ln -s /usr/local/sbin/keepalived /usr/sbin/

1-3，同時也在第一臺機器上執行，其方式完全相同

4、編輯keepalived.conf

[root@mytest2 keepalived-1.2.18]# cd /etc/keepalived

[root@mytest2 keepalived]# cat keepalived.conf

global_defs {

notification_email {

admin@example.com

}

notification_email_from haohailuo@163.com

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id MySQL-HA

}

vrrp_instance VI_1 {

state BACKUP 兩臺機器均設定為backup

interface eth1 網路卡

virtual_router_id 51

priority 100 優先順序別，如果相把某臺機做為主，就設定較大

advert_int 1

nopreempt

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.9.240 浮動ip

}

virtual_server 192.168.9.240 {

delay_loop 2

lb_algo wrr

lb_kind DR

persistence_timeout 60

protocol TCP

real_server 192.168.9.242 3306 { 實際物理ip

weight 3

notify_down /u01/mysql/mysql.sh

TCP_CHECK {

connect_timeout 10

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

第一臺機器

[root@mytest1 keepalived]# more keepalived.conf

global_defs {

notification_email {

admin@example.com

}

notification_email_from haohailuo@163.com

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id MySQL-HA

}

vrrp_instance VI_1 {

state BACKUP

interface eth1

virtual_router_id 51

priority 90

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.9.240

}

virtual_server 192.168.9.240 3306 {

delay_loop 2

lb_algo wrr

lb_kind DR

persistence_timeout 60

protocol TCP

real_server 192.168.9.241 3306 {

weight 3

notify_down /u01/mysql/mysql.sh

TCP_CHECK {

connect_timeout 10

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

附mysql.sh

這個只有一條語句：

Pkill keepalived

啟動keepalived

分別在兩臺機器上啟動

[root@mytest2 keepalived]# service keepalived start

Starting keepalived: [ OK ]

通常情況下，先啟動mysql，再啟動keepalived

檢查浮動ip在哪臺機器上服務

[root@mytest2 ~]# ip a

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

inet 127.0.0.1/8 scope host lo

inet6 ::1/128 scope host

valid_lft forever preferred_lft forever

2: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UNKNOWN qlen 1000

link/ether 00:50:56:ac:96:b6 brd ff:ff:ff:ff:ff:ff

inet 192.168.9.242/24 brd 192.168.9.255 scope global eth1

inet 192.168.9.240/32 scope global eth1

inet6 fe80::250:56ff:feac:96b6/64 scope link

valid_lft forever preferred_lft forever

可以看到在第二臺機器上執行。

建立使用者，並進行測試

[root@mytest2 ~]# mysql -uusertest -pusertest -h192.168.9.240

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3939

Server version: 5.6.21-ndb-7.3.7-cluster-gpl-log MySQL Cluster Community Server (GPL)

Oracle is a 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 variables like 'server%';

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

| Variable_name | Value |

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

| server_id | 20 |

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

1 rows in set (0.00 sec)

在第二機器上面

關閉第二臺機器，再進行檢視

[root@mytest2 keepalived]# shutdown -r now

Broadcast message from root@mytest3

(/dev/pts/0) at 11:53 ...

The system is going down for reboot NOW!

進入mysql檢視如下：

mysql> show variables like 'server%';

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

| Variable_name | Value |

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

| server_id | 10 |

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

1 rows in set (0.00 sec)

整個過程約10秒，並且navicat連線也沒有中斷

keepalived與mysql主主叢集自動切換

一、安裝部署mysql

二、部署主主

三、部署keepalived

相關文章

keepalived與mysql主主叢集自動切換

一、 安裝部署mysql

二、 部署主主

三、 部署keepalived

相關文章

一、安裝部署mysql

二、部署主主

三、部署keepalived