一、安装部署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连接也没有中断

ITPub博客

keepalived与mysql主主集群自动切换

一、安装部署mysql

二、部署主主

三、部署keepalived

最新文章

ITPub博客

keepalived与mysql主主集群自动切换

一、 安装部署mysql

二、 部署主主

三、 部署keepalived

最新文章

一、安装部署mysql

二、部署主主

三、部署keepalived