Build Mysql mmm architecture from scratch _mysql

Cloud Platform is a good thing, the typical configuration of mysql-mmm is to need five machines, one as MMM admin, two master, two slave. It's hard to find five machines at once, not to mention installing the same operating system. And with the cloud, a simple few steps to have a complete experimental environment: Four database server and a Management Server (Memory:8g,cpu:2g,disk:128g,64bit RHEL6). In this, to pay hard to build a cloud platform for colleagues to express heartfelt thanks to:-) below to start a new MySQL mmm tour.

The MySQL cluster environment that is configured below contains four database servers and one Management Server, as follows:

function	IP	Server Name	Server ID
Monitor	192.168.84.174	-	-
Master	192.168.85.167	Db1	1
Master	192.168.85.169	Db2	2
Slave	192.168.85.171	Db3	3
Slave	192.168.85.168	Db4	4

After the configuration is complete, use the following virtual IP to access the MySQL Cluster

Role

IP
192.168.85.200	Writer
192.168.85.201	Reader
192.168.85.202	Reader
192.168.85.203	Reader

First, configure the MySQL relication

1. Install MySQL

MYSQL5.1.52 is installed directly through the Yum command.

2. Modify configuration file/etc/my.cnf

To place the added content in the [mysqld] section of the configuration file, as follows:

Copy Code code as follows:

[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
User=mysql

#下面为新添加的内容
Default-storage-engine = InnoDB

replicate-ignore-db = MySQL
binlog-ignore-db = MySQL

Server-id = 1
Log-bin =/var/log/mysql/mysql-bin.log
Log_bin_index =/var/log/mysql/mysql-bin.log.index
Relay_log =/var/log/mysql/mysql-bin.relay
Relay_log_index =/var/log/mysql/mysql-bin.relay.index
Expire_logs_days = 10
Max_binlog_size = 100M
Log_slave_updates = 1

Attention:

1 The value of Server-id on each server is different, in order 1, 2, 3, 4.
2 because here the log file configuration to the/var/log/mysql, and MySQL default directory is in/var/lib/mysql, so first to create a new MySQL folder, and then use Chown-r mysql.mysql The MySQL command modifies the MySQL owner to user MySQL. Next to ensure that the MySQL folder permissions 755 (that is,-rwxr-xr-x).
If you do not modify the permissions and the owner, restarting the service will cause a Mysql-bin.log or mysql-bin.log.index error to be found in the error log (/usr/libexec/mysqld:file '/var/log/mysql/ Mysql-bin.log.index ' not Found (errcode:13)).

3. Restart the MySQL service

After completing the modifications to MY.CNF, restart the MySQL service via service mysqld restart. After the correct startup, you can check that the configuration is correct in the following ways:

1 login MySQL, perform show master status to see if there are the following output:

Copy Code code as follows:

+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000001 |              106 | | MySQL |
+------------------+----------+--------------+------------------+

2 to the/var/log/mysql directory to see if a file similar to mysql-bin.000001 and Mysql-bin.log.index is produced.

New users needed to synchronize the database

A total of three users are required to use MYSQL-MMM: replication, Mmm_agent, and Mmm_monitor (users on the Management server that monitor the status of the cluster, so you can qualify to log on only from the Management Server). Use the following three commands to create the new three users and assign the appropriate permissions:

Copy Code code as follows:

GRANT REPLICATION CLIENT on *.* to ' mmm_monitor ' @ ' 192.168.84.% ' identified by ' monitor ';
GRANT SUPER, REPLICATION CLIENT, PROCESS on *.* to ' mmm_agent ' @ ' 192.168.85.% ' identified by ' agent ';
GRANT REPLICATION SLAVE on *.* to ' REPLICATION ' @ ' 192.168.85.% ' identified by ' REPLICATION ';

Third, synchronization master database
1. Export the current database contents from the primary database server

Copy Code code as follows:

Mysql> FLUSH TABLES with READ LOCK;
Mysql> show MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000001 |              106 | | MySQL |
+------------------+----------+--------------+------------------+

Note that the above information is retained and will be used later. Also, do not end the current MySQL console, reopen a window, and export the database.

Copy Code code as follows:

# mysqldump-uroot-proot--all-databases > Db01_20111005.sql

Release lock

Copy Code code as follows:

Mysql> UNLOCK TABLES;

2. Import the exported SQL files to several other database servers. First copy the past through the SCP:

Copy Code code as follows:

# SCP Db01_20111005.sql root@192.168.85.167:/root/

Import a modified SQL file on several other services:

Copy Code code as follows:

# Mysql-uroot-proot < Db01_20111005.sql

3. Start the slave process from the database.

Copy Code code as follows:

mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)

mysql> change MASTER to master_host= ' 192.168.85.167 ', master_port=3306, master_user= ' replication ', Master_password = ' Replication ', master_log_file= ' mysql-bin.000001 ', master_log_pos=106;
Query OK, 0 rows affected (0.07 sec)

mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show Slave Status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.85.180
Master_user:replication
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000001
read_master_log_pos:106
relay_log_file:mysql-bin.000003
relay_log_pos:251
relay_master_log_file:mysql-bin.000001
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
Replicate_ignore_db:mysql
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:106
relay_log_space:400
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 row in Set (0.00 sec)

4. Db02 as master,db01 as slave, repeat 1-3.

Iv. installation of MMM

Run MYSQL-MMM monitor and agent separately on both the Management Server and the database server. The following are installed separately:
1. Installation of monitoring procedures
On the Management Server (192.168.84.174), execute the following command:

Copy Code code as follows:

# yum-y Install mysql-mmm-monitor*

All files that are dependent on monitor will also be installed, but with one exception perl-time-hires, the following command is required:

Copy Code code as follows:

# yum-y Install perl-time-hires*

2. Install Agent Program

Execute the following command on the database server:

Copy Code code as follows:

# yum-y Install mysql-mmm-agent*

V. Configuration of MMM

1. Editor mmm_common.conf

When the installation is complete, all of the configuration files are placed under/etc/mysql-mmm/. Both the Management Server and the database server contain a common file mmm_common.conf, which reads as follows:

Copy Code code as follows:

Active_master_role writer

Cluster_interface eth0

Pid_path/var/run/mysql-mmm/mmm_agentd.pid
bin_path/usr/libexec/mysql-mmm/

Replication_user replication
Replication_password replication

Agent_user mmm_agent
Agent_password Agent

IP 192.168.85.167
Mode Master
Peer DB2

IP 192.168.85.169
Mode Master
Peer DB1

IP 192.168.85.171
mode slave

IP 192.168.85.168
mode slave

<role writer>
Hosts DB1, DB2
IPs 192.168.85.200
Mode Exclusive
</role>

<role reader>
Hosts DB2, DB3, DB4
IPs 192.168.85.201, 192.168.85.202, 192.168.85.203
Mode Balanced
</role>

The file can be edited on DB1 and copied to monitor, DB2, DB3, and DB4, respectively, through the SCP command.

2. Edit Mmm_agent.conf. On the database server, there is also a mmm_agent.conf that needs to be modified, which reads:

Copy Code code as follows:

Include mmm_common.conf

# the ' This ' variable refers to this server. Proper operation requires
# that ' this ' server (db1 by default), as OK as all other servers, have the
# proper IP addresses set in mmm_common.conf.
This DB1

The last line of DB1, on different database servers to be changed to DB2, DB3 and DB4, otherwise the agent will not start.

3. Edit MMM_MON.CONFG. On the Management Server, modify the mmm_mon.conf file, with the following modifications:

Copy Code code as follows:

Include mmm_common.conf

<monitor>
    ip                   192.168.84.174
    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            192.168.85.167, 192.168.85.169, 192.168.85.171, 192.168.85.168
    auto_set_online    

# The Kill_host_bin does not exist by default, though the monitor would
# Throw a warning about it missing. The section 5.10 "Kill Host"
# functionality ' in the PDF documentation.
#
# Kill_host_bin/usr/libexec/mysql-mmm/monitor/kill_host
#
</monitor>

Monitor_user Mmm_monitor
Monitor_password Monitor

Debug 0

Six, start MMM

1. Start the agent on the database server

Copy Code code as follows:

# cd/etc/init.d/
# Chkconfig Mysql-mmm-agent on
# service Mysql-mmm-agent Start

2. Start the monitor on the Management Server

Copy Code code as follows:

# cd/etc/init.d/
# Chkconfig Mysql-mmm-monitor on
# service Mysql-mmm-monitor Start

After starting, wait a few seconds, you can view the status through the Mmm_control program:

Copy Code code as follows:

# Mmm_control Show
DB1 (192.168.85.167) master/online. Roles:writer (192.168.85.200)
DB2 (192.168.85.169) master/online. Roles:reader (192.168.85.202)
DB3 (192.168.85.171) slave/online. Roles:reader (192.168.85.203)
DB4 (192.168.85.168) slave/online. Roles:reader (192.168.85.201)

Seven, two problems encountered

1. Monitor server failed to start

On the management Server, after everything is done, check the status through Mmm_control to get the following error message: Error:can ' t connect to monitor daemon! Turn debug 0 To debug 1 by editing the/etc/mysql-mmm/mmm_mon.conf file to open the debug state of the monitor. Restarting the monitor (service mysql-mmm-monitor restart), you will see a detailed error message and the Perl time hires library is not found. The execution of Yum-y install perl-time-hires* can be resolved.

2. A firewall problem caused warning:agent on host db1 be not reachable.
When the console program starts correctly, executes Mmm_control show again, but sees the following output:

Copy Code code as follows:

# warning:agent on host db1 are not reachable
# Warning:agent on host DB2 are not reachable
# warning:agent on host db3 are not reachable
# warning:agent on host DB4 are not reachable
DB1 (192.168.85.167) master/online. Roles:
DB2 (192.168.85.169) master/online. Roles:
DB3 (192.168.85.171) slave/online. Roles:
DB4 (192.168.85.168) slave/online. Roles:

Turn on debug again and find the following error message:

Copy Code code as follows:

2011/10/07 13:38:45 DEBUG sending command ' get_agent_status () ' to DB4 (192.168.85.167:9989)
2011/10/07 13:38:45 ERROR The status of the agent on host ' DB4 ' could is not determined (answer was:0).

Check the network connection under Telnet 192.168.85.167 9989 and get the error message for no route to host. Log in to the DB1 and close firewall through the firewall configuration in the Setup program (this is not a good idea). Again, turn off the firewall on the DB2, DB3 and DB4 and restart the monitor again, everything back to normal!

Reference articles:

MySQL MMM Official Installation documentation Http://mysql-mmm.org/mmm2:guide
MMM Manual http://mysql-mmm.org/mysql-mmm.html