出处http://navyaijm.blog.51cto.com/4647068/1230674
一、说明
服务器 | 主机名 | IP | serverID | mysql版本 | 系统 |
master1
|
db1
|
172.28.26.101
|
101
|
mysql5.5.15 | Centos 6.4 |
master2 |
db2
|
172.28.26.102
|
102
|
mysql5.5.15
|
Centos 6.4
|
slave1 |
db3
|
172.28.26.188
|
188
|
mysql5.5.15
|
Centos 6.4
|
slave2 |
db4
|
172.28.26.189
|
189
|
mysql5.5.15
|
Centos 6.4
|
monitor
|
monitor
|
172.28.26.103
|
无 |
Centos 6.4
|
VIP | Role | description |
172.28.26.104 | write | 应用配置的写入VIP |
172.28.26.105 | read |
应用配置的读入VIP
|
172.28.26.106 | read |
应用配置的读入VIP
|
1
2
3
4
5
6
|
tar -zxvf mysql-5.5.15. tar .gz
cd mysql-5.5.15
cmake -DCMAKE_INSTALL_PREFIX:PATH= /data/mysql/navy1 -DMYSQL_DATADIR= /data/mysql/navy1/db -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR= /tmp/mysql .sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1
make
make install
useradd mysql -s /sbin/nologin ; cd /data/mysql/navy1 ; chown mysql:mysql db/ logs/ -R
|
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
|
[mysqld_safe]
log-error= /data/mysql/navy1/logs/mysqld .log
pid- file = /data/mysql/navy1/logs/mysqld .pid
[client]
port = 3306
socket = /data/mysql/navy1/logs/mysql .sock
[mysqld]
port = 3306
socket = /data/mysql/navy1/logs/mysql .sock
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 64M
basedir= /data/mysql/navy1
datadir= /data/mysql/navy1/db
thread_concurrency = 8
log-bin=mysql-bin
binlog_format = mixed
server- id = 101
max_connections=2048
character_set_server=utf8
wait_timeout=1800
interactive_timeout=1800
skip-show-database
skip-name-resolve
tmp_table_size = 512M
max_heap_table_size = 512M
binlog-ignore-db = mysql
replicate-ignore-db = mysql
binlog-ignore-db = information_schema
replicate-ignore-db = information_schema
binlog-ignore-db = performance_schema
replicate-ignore-db = performance_schema
binlog-ignore-db = test
replicate-ignore-db = test
innodb_data_home_dir = /data/mysql/navy1/db
#innodb_data_file_path = ibdata1:4000M;ibdata2:10M:autoextend
innodb_file_per_table=1
innodb_log_group_home_dir = /data/mysql/navy1/db
innodb_buffer_pool_size = 2000M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
#default-storage-engine = MyISAM
default-storage-engine = InnoDB
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
|
1
|
/data/mysql/navy1/scripts/mysql_install_db --user=mysql --basedir= /data/mysql/navy1 --datadir= /data/mysql/navy1/db/
|
1
|
cd /data/mysql/navy1 ; /data/mysql/navy1/bin/mysqld_safe --defaults-extra- file = /data/mysql/navy1/my .cnf --user=mysql &
|
1
|
useradd mysql -s /sbin/nologin ; cd /data/mysql/navy1 ; chown mysql:mysql db/ logs/ -R
|
1
|
cd /data/mysql/navy1 ; /data/mysql/navy1/bin/mysqld_safe --defaults-extra- file = /data/mysql/navy1/my .cnf --user=mysql &
|
1
2
3
|
grant replication slave on *.* to slave@ '172.28.26.102' identified by "123456" ;
grant replication slave on *.* to slave@ '172.28.26.188' identified by "123456" ;
grant replication slave on *.* to slave@ '172.28.26.189' identified by "123456" ;
|
1
2
3
|
grant replication slave on *.* to slave@ '172.28.26.101' identified by "123456" ;
grant replication slave on *.* to slave@ '172.28.26.188' identified by "123456" ;
grant replication slave on *.* to slave@ '172.28.26.189' identified by "123456" ;
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> show master status \G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id : 3974
Current database: *** NONE ***
*************************** 1. row ***************************
File: mysql-bin.000024
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema, test ,mysql,information_schema,performance_schema, test
1 row in set (0.00 sec)
|
1
2
|
change master to master_host= '172.28.26.101' , master_Port=3306, master_user= 'slave' , master_password= '123456' , master_log_file= 'mysql-bin.000024' , master_log_pos=107;
slave start;
|
1
2
3
4
5
6
7
|
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000025
Position: 107
Binlog_Do_DB: navy
Binlog_Ignore_DB: mysql,mysql,information_schema,performance_schema, test ,mysql,information_schema,performance_schema, test
1 row in set (0.00 sec)
|
1
2
|
change master to master_host= '172.28.26.101' , master_Port=3306, master_user= 'slave' , master_password= '123456' , master_log_file= 'mysql-bin.000025' , master_log_pos=107;
slave start;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.28.26.102
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000025
Read_Master_Log_Pos: 107
Relay_Log_File: mysqld-relay-bin.000015
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000025
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
|
1
|
yum -y install mysql-mmm-agent
|
1
|
yum -y install mysql-mmm*
|
1
2
|
GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent' @ '172.28.26.%' IDENTIFIED BY '123456' ;
GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor' @ '172.28.26.%' IDENTIFIED BY '123456' ;
|
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
|
active_master_role writer
<host default>
cluster_interface eth1
pid_path /var/run/mysql-mmm/mmm_agentd .pid
bin_path /usr/libexec/mysql-mmm/
replication_user slave
replication_password 123456
agent_user mmm_agent
agent_password 123456
< /host >
<host db1>
ip 172.28.26.101
mysql_port 3306
mode master
peer db2
< /host >
<host db2>
ip 172.28.26.102
mysql_port 3306
mode master
peer db1
< /host >
<host db3>
ip 172.28.26.188
mysql_port 3306
mode slave
peer db3
< /host >
<host db4>
ip 172.28.26.189
mysql_port 3306
mode slave
peer db4
< /host >
<role writer>
hosts db1, db2
ips 172.28.26.104
mode exclusive
< /role >
<role reader>
hosts db3, db4
ips 172.28.26.105,172.28.26.106
mode balanced
< /role >
|
peer的意思是等同,表示db1与db2是同等的。
ips指定VIP
mode exclusive 只有两种模式:exclusive是排他,在这种模式下任何时候只能一个host拥有该角色
balanced模式下可以多个host同时拥有此角色。一般writer是exclusive,reader是balanced
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
include mmm_common.conf
<monitor>
ip 127.0.0.1
pid_path /var/run/mysql-mmm/mmm_mond .pid
bin_path /usr/libexec/mysql-mmm
status_path /var/lib/mysql-mmm/mmm_mond .status
ping_ips 172.28.26.101,172.28.26.102
auto_set_online 10
# The kill_host_bin does not exist by default, though the monitor will
# throw a warning about it missing. See the section 5.10 "Kill Host
# Functionality" in the PDF documentation.
#
# kill_host_bin /usr/libexec/mysql-mmm/monitor/kill_host
#
< /monitor >
<host default>
monitor_user mmm_monitor
monitor_password 123456
< /host >
debug 0
|
1
2
|
/etc/init .d /mysql-mmm-agent start
echo "/etc/init.d/mysql-mmm-agent start" >> /etc/rc . local
|
1
2
3
4
5
|
[root@monitor ~] # mmm_control show
db1(172.28.26.101) master /ONLINE . Roles: writer(172.28.26.104)
db2(172.28.26.102) master /ONLINE . Roles:
db3(172.28.26.188) slave /ONLINE . Roles: reader(172.28.26.106)
db4(172.28.26.189) slave /ONLINE . Roles: reader(172.28.26.105)
|
1
2
3
4
5
|
[root@monitor ~] # mmm_control show
db1(172.28.26.101) master /HARD_OFFLINE . Roles:
db2(172.28.26.102) master /ONLINE . Roles: writer(172.28.26.104)
db3(172.28.26.188) slave /ONLINE . Roles: reader(172.28.26.106)
db4(172.28.26.189) slave /ONLINE . Roles: reader(172.28.26.105)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> show slave status \G
Connection id : 5844
Current database: *** NONE ***
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.28.26.102
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000025
Read_Master_Log_Pos: 107
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000025
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
|
转载于:https://blog.51cto.com/bfe99/1231527
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/110089.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...