本文由西安白癜风专科医院 www.xapfb120/ 收集，转载请注明出处
MySQL 的 MMM 安装指南
Installation Guide
A basic installation contains at least 2 database servers and 1 monitoring server. In this guide, I used 4 servers with Ubuntu 11.10 。
function
ip
hostname server id 1 2 3
monitoring host 192.168.0.10 mon master 1 master 2 slave 1 192.168.0.11 db1 192.168.0.12 db2 192.168.0.13 db3
I used the following virtual IPs. They will be distributed across the hosts by MMM.
ip
role
description
192.168.0.100 writer Your application should connect to this IP for write queries. 192.168.0.101 reader 192.168.0.102 reader Your application should connect to one of these four IPs for read queries 192.168.0.103 reader
The architecture as below：
本文由西安白癜风专科医院 www.xapfb120/ 收集，转载请注明出处
本文由西安白癜风专科医院 www.xapfb120/ 收集，转载请注明出处
Basic configuration of master 1
First we install MySQL on all hosts:
aptitude install mysql-server
Then we edit the configuration file /etc/mysql/myf and add the following lines be sure to use differe
nt server ids for all hosts:
server_id log_bin log_bin_index relay_log relay_log_index expire_logs_days max_binlog_size log_slave_updates = 1 = /var/log/mysql/mysql-bin.log = /var/log/mysql/mysql-bin.log.index = /var/log/mysql/mysql-relay-bin = /var/log/mysql/mysql-relay-bin.index = 10 = 100M = 1
Then remove the following entry:
bind-address = 127.0.0.1
Set to number of masters:
auto_increment_increment = 2
mysql下载starting the serverSet to a unique, incremented number, less than auto_increment_increment, on each server
auto_increment_offset = 1
Do not bind of any specific IP, use 0.0.0.0 instead:
bind-address = 0.0.0.0
Afterwards we need to restart MySQL for our changes to take effect:
本文由西安白癜风专科医院 www.xapfb120/ 收集，转载请注明出处
本文由西安白癜风专科医院 www.xapfb120/ 收集，转载请注明出处
/etc/init.d/mysql restart
Create users
Now we can create the required users. We'll need 3 different users:
function monitor user agent user relication user
description used by the mmm monitor to check the health of the MySQL servers
privileges REPLICATION CLIENT
used by the mmm agent to change read-only mode, SUPER, REPLICATION CLIENT, replication master, etc. PROCESS used for replication REPLICATION SLAVE
ON *.* TO 'mmm_monitor'@'192.168.0.%' IDENTIFIED BY
GRANT REPLICATION CLIENT 'monitor_password';
GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.0.%' 'agent_password'; GRANT REPLICATION SLAVE 'replication_password';
IDENTIFIED BY
ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED BY
Note: We could be more restrictive here regarding the hosts from which the users are allowed to connect: mmm_monitor is used from 192.168.0.10. mmm_agent and replication are used from 192.168.0.11 - 192.168.0.14. Note: Don't use a replication_password longer than 32 characters
Synchronisation of data between both databases
I'll assume that db1 contains the correct data. If you have an empty database, you still have to syncronize the accounts we have just created. First make sure that no one is altering the data while we create a backup.
(db1) mysql> FLUSH TABLES WITH READ LOCK;
Then get the current position in the binary-log. We will need this values when we setup the replication on db2, db3 and db4.
(db1) mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+
本文由西安白癜风专科医院 www.xapfb120/ 收集，转载请注明出处
本文由西安白癜风专科医院 www.xapfb120/ 收集，转载请注明出处
| File
| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+ | mysql-bin.000002 | 374 | | |
+------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
DON'T CLOSE this mysql-shell. If you close it, the database lock will be removed. Open a second console and type:
db1$ mysqldump -u root -p --all-databases > /tmp/database-backup.sql
Now we can remove the database-lock. Go to the first shell:
(db1) mysql> UNLOCK TABLES;
Copy the database backup to db2, db3 and db4.
db1$ scp /tmp/database-backup.sql <user>@192.168.0.12:/tmp db1$ scp /tmp/database-backup.sql <user>@192.168.0.13:/tmp db1$ scp /tmp/database-backup.sql <user>@192.168.0.14:/tmp
Then import this into db2, db3 and db4:
db2$ mysql -u root -p < /tmp/database-backup.sql db3$ mysql -u root -p < /tmp/database-backup.sql db4$ mysql -u root -p < /tmp/database-backup.sql
Then flush the privileges on db2, db3 and db4. We have altered the user-table and mysql has to reread this table.
(db2) mysql> FLUSH PRIVILEGES; (db3) mysql> FLUSH PRIVILEGES; (db4) mysql> FLUSH PRIVILEGES;
本文由西安白癜风专科医院 www.xapfb120/ 收集，转载请注明出处
本文由西安白癜风专科医院 www.xapfb120/ 收集，转载请注明出处
On debian and ubuntu, copy the passwords in /etc/mysql/debianf from db1 to db2, db3 and db4. This password is used for starting and stopping mysql. Both databases now contain the same data. We now can setup replication to keep it that way. Note: Import just only add records from dump file. You should drop all databases before import dump file.
Setup replication
Configure replication on db2, db3 and db4 with the following commands:
(db2) mysql> CHANGE MASTER TO master_host='192.168.0.11', master_port=3306, master_user='replication', master_password='replication_password', master_log_file='<file>', master_log_pos=<position>; (db3) mysql> CHANGE MASTER TO master_host='192.168.0.11', master_port=3306, master_user='replication', master_password='replication_password', master_log_file='<file>', master_log_pos=<position>; (db4) mysql> CHANGE MASTER TO master_host='192.168.0.11', master_port=3306, master_user='replication', master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;
Please insert the values return by “show master status” on db1 at the <file> and <position> tags. Start the slave-process on all 3 hosts:
(db2) mysql> START SLAVE; (db3) mysql> START SLAVE; (db4) mysql> START SLAVE;
Now check if the replication is running correctly on all hosts:
(db2) mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** 本文由西安白癜风专科医院 www.xapfb120/ 收集，转载请注明出处

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。