1.安装mysql依赖包
|
[root@centos6 mysql-5.1.63]# yum install ncurses ncurses-devel
|
2.安装mysql
|
[root
@server lnmp]tarzxvf mysql-5.1.63.tar.gz -C/usr/src
[root
@server lnmp]cd/usr/src/mysql-5.1.63
[root@centos6 mysql-5.1.63]#./configure --prefix=/usr/local/mysql/ --enable-assembler --with-extra-charsets=all --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --with-pthread --with-big-tables --without-debug --with-ssl
[root@centos6 mysql-5.1.63]make&&makeinstall
[root@centos6 mysql-5.1.63]/usr/local/mysql/bin/mysql_install_db--user=mysql
[root@centos6 mysql-5.1.63]chown-R root.mysql/usr/local/mysql
[root@centos6 mysql-5.1.63]cpsupport-files/my-medium.cnf/etc/my.cnf
[root@centos6 mysql-5.1.63]cpsupport-files/mysql.server/etc/init.d/mysqld
[root@centos6 mysql-5.1.63]chmod+x/etc/init.d/mysqld
[root@centos6 mysql-5.1.63]chkconfig --add mysqld
[root@centos6 mysql-5.1.63]chkconfig --level 35 mysqld on 开机启动
[root@centos6 mysql-5.1.63]service mysqld start
|
3.修改配置文件DB1，DB2和DB3
|
[root@centos6 mysql]# vi /etc/my.cnf
....
log-bin=mysql-bin
server-id = 1
保存
|
|
DB2
[root@centos6 mysql]# vi /etc/my.cnf
....
log-bin=mysql-bin
server-id = 2 #ID是唯一的
保存
|
BD3只需要修改 server-id =3即可
4.配置同步
配置主DB1，从DB2和DB3
|
DB1
[root@centos6 mysql]# /usr/local/mysql/bin/mysql -u root
MySQL> grant replication slave on *.* to'replication'@'%'identified by'xxxx'; 创建授权同步用户
Query OK, 0 rows affected (0.00 sec)
MySQL> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| MySQL-bin.000003 | 374 | | |
+------------------+----------+--------------+------------------+
1 rowinset(0.00 sec)
|
|
DB2
[root@centos6 mysql]# /usr/local/mysql/bin/mysql -u root
MySQL> change master to master_host='192.168.20.158',master_user='replication',master_password='xxxxx',master_log_file='MySQL-bin.000003',master_log_pos=374;
Query OK, 0 rows affected (0.05 sec)
MySQL> start slave;
Query OK, 0 rows affected (0.00 sec)
MySQL> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waitingformaster to send event
Master_Host: 192.168.20.158
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MySQL-bin.000003
Read_Master_Log_Pos: 374
Relay_Log_File: MySQL-master2-relay-bin.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: MySQL-bin.000003
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: 374
Relay_Log_Space: 235
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
1 rowinset(0.00 sec)
|
|
DB3
[root@centos6 mysql]# /usr/local/mysql/bin/mysql -u root
MySQL> change master to master_host='192.168.20.158',master_user='replication',master_password='xxxxx',master_log_file='MySQL-bin.000003',master_log_pos=374;
Query OK, 0 rows affected (0.05 sec)
MySQL> start slave;
Query OK, 0 rows affected (0.00 sec)
MySQL> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waitingformaster to send event
Master_Host: 192.168.20.158
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MySQL-bin.000003
Read_Master_Log_Pos: 374
Relay_Log_File: MySQL-master2-relay-bin.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: MySQL-bin.000003
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: 374
Relay_Log_Space: 235
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
1 rowinset(0.00 sec)
|
配置DB2主，DB1从 （即互为MATER）
|
DB2
[root@centos6 mysql]# /usr/local/mysql/bin/mysql -u root
MySQL> grant replication slave on *.* to'replication'@'%'identified by'xxxx';
Query OK, 0 rows affected (0.00 sec)
MySQL> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| MySQL-bin.000003 | 374 | | |
+------------------+----------+--------------+------------------+
1 rowinset(0.00 sec)
|
|
DB1
[root@centos6 mysql]# /usr/local/mysql/bin/mysql -u root
MySQL> change master to master_host='192.168.20.156',master_user='replication',master_password='xxxxxx',master_log_file='MySQL-bin.000003',master_log_pos=374;
Query OK, 0 rows affected (0.05 sec)
MySQL> start slave;
Query OK, 0 rows affected (0.00 sec)
MySQL> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waitingformaster to send event
Master_Host: 192.168.20.156
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MySQL-bin.000003
Read_Master_Log_Pos: 374
Relay_Log_File: MySQL-master2-relay-bin.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: MySQL-bin.000003
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: 374
Relay_Log_Space: 235
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
1 rowinset(0.00 sec)
|
测试现在在DB1上创建一个数据库，DB2和DB3都会同步表示成功。
5.安装mysql-mmm
安装Mysql-mmm之前，需要先安装一些perl模块，安装方法用perl -MCPAN -e shell 直接在线安装，第一次使用perl -MCPAN -e shell 命令时会有一大堆选项的，我直接enter过去，比较关键的是选择地区、国家和源这三个，按的时候注意选择一下就没问题，DB1、DB2和DB3需要安装以下模块：
注意如果没有安装CPAN模块需要手动编译安装：
数据库授权一个Mysql-mmm专用用户（DB1、DB2和DB3都要授权）：
|
DB1
[root@centos6 mysql]# /usr/local/mysql/bin/mysql -u root
MySQL>grant super,replication client,process on *.* to'mmm_xxxxt'@'%'identified by'xxxxx';
|
安装CPAN模块：
|
yuminstallperl perl-devel
wget http://search.cpan.org/CPAN/authors/id/A/AN/ANDK/CPAN-1.9205.tar.gz
[root@centos6 MMM]# tar zxvf CPAN-1.9205.tar.gz
[root@centos6 MMM]# cd CPAN-1.9205
[root@centos6 CPAN-1.9205]# perl Makefile
[root@centos6 CPAN-1.9205]# make
[root@centos6 CPAN-1.9205]# make install
|
用CPAN模块在线安装其他模块：(DB1，DB2，DB3，MOM 都需要安装)
|
# perl -MCPAN -e shell
cpan>installAlgorithm::Diff
cpan>installDBI
cpan>installLog::Dispatch
cpan>installLog::Log4perl
cpan>installMail::Send
cpan>installNet::ARP
cpan>installProc::Daemon
cpan>installTime::HiRes
cpan>installDBD::mysql
cpan>installFile::stat
cpan>installFile:basename
cpan>installClass::Singleton
cpan>install Thread::Queue
cpan>installPath::Class
脚本查看perl 安装了那些模块
[root@centos6 MMM]# vi perl-m
#!/usr/bin/perl
use strict;
use ExtUtils::Installed;
my $inst= ExtUtils::Installed->new();
my
@modules = $inst->modules();
foreach(@modules)
{
my $ver = $inst->version($_) ||"???";
printf("%-12s -- %s\n", $_, $ver);
}
exit;
[root@centos6 MMM]# perl perl-m 运行脚本查看模块
Algorithm::Diff -- 1.1902
CPAN -- 1.9205
Class::Singleton -- 1.4
DBD::mysql -- 4.021
DBI -- 1.620
Log::Log4perl -- 1.36
Mail -- ???
Net::ARP -- 1.0
Perl -- 5.10.1
Proc::Daemon -- 0.14
Test::Simple -- 0.98
Thread::Queue -- 2.12
Time::HiRes -- 1.9725
TimeDate -- ???
|
安装mysql-mmm
|
[root@centos6 MMM]# tar zxvf mysql-mmm-2.2.1.tar.gz
[root@centos6 MMM]# cd mysql-mmm-2.2.1
[root@centos6 mysql-mmm-2.2.1]# make install
/usr/lib/perl5/vendor_perl/5.8.8/MMM MMM 使用的 perl 模块
/usr/lib/mysql-mmm MMM 的脚本揑件
/usr/sbin MMM 的命令保存路径
/var/log/mysql-mmm MMM 的日志保存路径
/etc MMM 配置文件保存的路径
/etc/mysql-mmm MMM 配置文件保存的路径，优先级最高
/etc/init.d/ agentd 和 monitor 的启劢关闭脚本
|
6.配置MMM
|
DB1：
vi/etc/mysql-mmm/mmm_agent.confinclude mmm_common.conf
this db1
蓝色的部分DB2和DB3则分别修改为db2和db3
|
|
vi/etc/mysql-mmm/mmm_common.conf
active_master_role writer
<host default>
cluster_interface eth3 #（对应本地网卡）
pid_path /var/run/mmm_agentd.pid
bin_path /usr/lib/mysql-mmm/
replication_user replication
replication_password replication
agent_user mmm_xxxx
agent_password mmm_xxxx
</host>
<host db1>
ip 192.168.20.158
mode master
peer db2
</host>
<host db2>
ip 192.168.20.156
mode master
peer db1
</host>
<host db3>
ip 192.168.20.157
mode slave
</host>
<role writer>
hosts db1, db2
ips 192.168.20.11
mode exclusive
</role>
<role reader>
hosts db1, db2, db3
ips 192.168.20.12, 192.168.20.13, 192.168.20.14
mode balanced
</role>
|
把改文件复制到DB2，DB3，MOM,.只需修改网卡名
启动DB1,DB2,DB3 mmm-agent
|
[root
@localhost mysql-mmm-2.2.1]# /etc/init.d/mysql-mmm-agent start
Daemon bin:'/usr/sbin/mmm_agentd'
Daemon pid:'/var/run/mmm_agentd.pid'
Starting MMM Agent daemon... Ok
[root
@localhost mysql-mmm-2.2.1]# echo "/etc/init.d/mysql-mmm-agent start" >> /etc/rc.d/rc.local 开机启动
|
最后配置MOM的mysql-mmm-monitor
|
[root
@localhost mysql-mmm-2.2.1]vi/etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf
<monitor>
ip 192.168.20.159
pid_path /var/run/mmm_mond.pid
bin_path /usr/lib/mysql-mmm/
status_path /var/lib/misc/mmm_mond.status
ping_ips 192.168.20.156, 192.168.20.157, 192.168.20.158
</monitor>
<host default>
monitor_user mmm_XXX
monitor_password mmm_XXXt
</host>
debug 0
|
启动mysql-mmm-monitor：
|
[root
@localhost mysql-mmm-2.2.1]# /etc/init.d/mysql-mmm-monitor start
Daemon bin:'/usr/sbin/mmm_mond'
Daemon pid:'/var/run/mmm_mond.pid'
Starting MMM Monitor daemon: Ok
[root
@localhost mysql-mmm-2.2.1]# mmm_control show
db1(192.168.93.137) master/AWAITING_RECOVERY. Roles:
db2(192.168.93.138) master/AWAITING_RECOVERY. Roles:
db3(192.168.93.139) slave/AWAITING_RECOVERY. Roles:
[root
@localhost mysql-mmm-2.2.1]#echo "/etc/init.d/mysql-mmm-monitor start" >> /etc/rc.d/rc.local 开机启动
将所有DB服务器设为online状态：
[root
@localhost mysql-mmm-2.2.1]# mmm_control set_online db1
OK: State of'db1'changed to ONLINE. Now you can wait sometimeand check its new roles!
[root
@localhost mysql-mmm-2.2.1]# mmm_control set_online db2
OK: State of'db2'changed to ONLINE. Now you can wait sometimeand check its new roles!
[root
@localhost mysql-mmm-2.2.1]# mmm_control set_online db3
OK: State of'db3'changed to ONLINE. Now you can wait sometimeand check its new roles!
[root
@localhost mysql-mmm-2.2.1]# mmm_control show
db1(192.168.20.158) master/ONLINE. Roles: reader(192.168.20.13), writer(192.168.20.11)
db2(192.168.20.156) master/ONLINE. Roles: reader(192.168.20.12)
db3(192.168.20.157) slave/ONLINE. Roles: reader(192.168.20.14)
设置成功，下面查看节点状态：
[root
@localhost mysql-mmm-2.2.1]# mmm_control checks all
db2 ping [last change: 2011/10/0703:10:39] OK
db2 mysql [last change: 2011/10/0703:10:39] OK
db2 rep_threads [last change: 2011/10/0703:10:39] OK
db2 rep_backlog [last change: 2011/10/0703:10:39] OK: Backlog is null
db3 ping [last change: 2011/10/0703:10:39] OK
db3 mysql [last change: 2011/10/0703:10:39] OK
db3 rep_threads [last change: 2011/10/0703:10:39] OK
db3 rep_backlog [last change: 2011/10/0703:10:39] OK: Backlog is null
db1 ping [last change: 2011/10/0703:10:39] OK
db1 mysql [last change: 2011/10/0703:10:39] OK
db1 rep_threads [last change: 2011/10/0703:10:39] OK
db1 rep_backlog [last change: 2011/10/0703:10:39] OK: Backlog is null
|
7。测试
在DB1，DB2，DB3上查看ip绑定：
|
[root@centos6 mysql-mmm-2.2.1]# ip add list
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8scope host lo
inet6 ::1/128scope host
valid_lft forever preferred_lft forever
2: eth3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether00:0c:21:68:85:11 brd ff:ff:ff:ff:ff:ff
inet 192.168.20.156/24brd 192.168.20.255 scope global eth3
inet 192.168.20.11/32scope global eth3 #192.168.20.11被绑定在这里那么你访问192.168.20.11就是访问该数据库，其他IP都是同样道理
inet 192.168.20.12/32scope global eth3
inet6 fe80::20c:21ff:fe68:8511/64scope link
valid_lft forever preferred_lft forever
|
用192.168.20.11登录写入数据。如用192.168.20.12,13,14能查看成功。
把当前write主机down掉。
用ip add list 查看备份maste主机 192.168.20.11 write IP绑定在该机上成功。
查看slave DB3主机，：
|
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waitingformaster to send event
Master_Host: 192.168.20.156 # DB2IP slave的master已经自动切换
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 508
Relay_Log_File: centos6-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000003
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: 508
Relay_Log_Space: 408
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:
1 rowinset(0.00 sec)
|
重新UP DB1数据库改被定为read主机 ，只有DB2 down了才会切换
8.数据恢复
1.当数据库宕机或是服务出现问题是重新启动服务器，如果mysql能正常提供服务器时，数据不用处理，会自动同步。
2.如果数据库彻底不能提供服务，需要重新安装mysql时。数据恢复也就是重新配置slave。在有数据动态时配置mysql主从同步步骤如下：
*.锁表命令： flush tables with read lock; //锁住同步数据库，清空写入操作。并且禁止写入操作，允许read操作。
解锁命令：unlock tables;
217
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
