Build the MySQL MMM architecture from scratch and build the mysqlmmm Architecture

Build the MySQL MMM architecture from scratch and build the mysqlmmm Architecture

The cloud platform is a good thing. A typical configuration of MySQL-mmm requires five machines, one as the mmm admin, two masters, and two slave servers. It is really not easy to find five machines at once, not to mention installing the same operating system. With cloud, we have a complete lab environment in just a few steps: Four database servers and one management server (Memory: 8G, CPU: 2G, Disk: 128G, 64bit RHEL6 ). Here, I would like to express my sincere gratitude to my colleagues who have worked hard to build the cloud platform:-) Let's get back to the point and start a new MySQL mmm journey.

The following MySQL Cluster environment includes four database servers and one management server:

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 address to access the MySQL Cluster

IP	Role
192.168.85.200	Writer
192.168.85.201	Reader
192.168.85.202	Reader
192.168.85.203	Reader

1. Configure MySQL Relication

1. Install MySQL

Mysql5.1.52 is directly installed using the yum command.

2. modify the configuration file/etc/my. cnf

Put the added content in the [mysqld] section of the configuration file, as follows:
Copy codeThe Code is as follows:
[Mysqld]
Datadir =/var/lib/mysql
Socket =/var/lib/mysql. sock
User = mysql

# The following content is newly added
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 = 100 M
Log_slave_updates = 1

Note:

1) The server-id values on each server are different. Here the values are 1, 2, 3, and 4 in sequence.
2) because the log file is configured to/var/log/mysql, and the default directory of mysql is/var/lib/mysql, you must first create a mysql folder, then use chown-R mysql. the mysql Command changes the mysql owner to mysql. Ensure that the mysql folder has 755 permissions (-rwxr-xr-x ).
If you do not have the modification permission and owner, an error with the mysql-bin.log or mysql-bin.log.index not found in the error log will occur when you restart the Service (/usr/libexec/mysqld: file '/var/log/mysql/mysql-bin.log.index' not found (Errcode: 13 )).

3. Restart the mysql service.

After modifying my. cnf, restart the mysql service through service mysqld restart. After the instance is correctly started, you can check whether the configuration is correct as follows:

1) log on to mysql and run show master status to check whether the following output exists:
Copy codeThe Code is as follows:
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000001 | 106 | mysql |
+ ------------------ + ---------- + -------------- + ------------------ +

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

2. Users required to create a database for synchronization

When using mysql-mmm, a total of three users are required: replication, mmm_agent, and mmm_monitor (users who use the Management Server to monitor the cluster status can only log on from the Management Server ). Use the following three commands to create the three users and assign the corresponding permissions:

Copy codeThe Code is as follows:
Grant replication client on *. * TO 'mmm _ monitor' @ '2017. 192.% 'identified by 'monitor ';
Grant super, replication client, process on *. * TO 'mmm _ agent' @ '2017. 192.% 'identified by 'agent ';
Grant replication slave on *. * TO 'replicase' @ '192. 192.% 'identified by 'replicase ';

Iii. Synchronize master-slave Databases
1. Export the content of the current database from the master database server
Copy codeThe Code is 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. In addition, do not end the current mysql console. open a new window to export the database.
Copy codeThe Code is as follows:
# Mysqldump-uroot-proot -- all-databases> db01_20111005. SQL

Release lock
Copy codeThe Code is as follows:
Mysql> unlock tables;

2. Import the exported SQL file to several other database servers. First, use scp to copy the past:
Copy codeThe Code is as follows:
# Scp db01_20111005. SQL root@192.168.85.167:/root/

Import and modify the SQL file on several other services:
Copy codeThe Code is as follows:
# Mysql-uroot-proot <db01_20111005. SQL

3. Start the SLAVE process from the database.
Copy codeThe Code is as follows:
Mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Mysql> change master to master_host = '2017. 168.85.167 ', master_port = 3306, master_user = 'replicase', master_password = 'replicase', 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. Use db02 as the master, and db01 as the slave. Repeat 1-3.

4. Install MMM

Run the mysql-mmm monitor and agent programs on the Management Server and database server respectively. Install the following:
1. Install the Monitoring Program
On the Management Server (192.168.84.174), run the following command:
Copy codeThe Code is as follows:
# Yum-y install mysql-mmm-monitor *

All files dependent on the monitor will also be installed, but with the exception of perl-Time-HiRes, you also need to execute the following command:
Copy codeThe Code is as follows:
# Yum-y install perl-Time-HiRes *

2. Install the agent

Run the following command on the database server:
Copy codeThe Code is as follows:
# Yum-y install mysql-mmm-agent *

5. Configure MMM

1. Edit mmm_common.conf

After the installation is complete, all the configuration files are stored under/etc/mysql-mmm. The Management Server and database server must contain a common file mmm_common.conf. The content is as follows:
Copy codeThe Code is as follows:
Active_master_role writer

<Host default>
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
</Host>

<Host db1>
Ip address 192.168.85.167
Mode master
Peer db2
</Host>

<Host db2>
Ip address 192.168.85.169
Mode master
Peer db1
</Host>

<Host db3>
Ip 192.168.85.171
Mode slave
</Host>

<Host db4>
Ip 192.168.85.168
Mode slave
</Host>

<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>

After editing the file on db1, copy the file to monitor, db2, db3, and db4.

2. Edit mmm_agent.conf. On the database server, another mmm_agent.conf file needs to be modified. Its content is:
Copy codeThe Code is as follows:
Include mmm_common.conf

# The 'this' variable refers to this server. Proper operation requires
# That 'it' server (db1 by default), as well as all other servers, have
# Proper IP addresses set in mmm_common.conf.
This db1

The last row of db1 should be changed to db2, db3, and db4 on different database servers; otherwise, the agent will fail to start.

3. Edit mmm_mon.confg. On the Management Server, modify the mmm_mon.conf file. The modified content is as follows:
Copy codeThe Code is 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 60

# 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 monitor
</Host>

Debug 0

6. Start MMM

1. Start the agent on the database server
Copy codeThe Code is as follows:
# Cd/etc/init. d/
# Chkconfig mysql-mmm-agent on
# Service mysql-mmm-agent start

2. Start the monitoring program on the Management Server
Copy codeThe Code is as follows:
# Cd/etc/init. d/
# Chkconfig mysql-mmm-monitor on
# Service mysql-mmm-monitor start

After starting, wait a few seconds and check the status through the mmm_control program:
Copy codeThe Code is 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)

7. Two Problems

1. The monitoring program server cannot be started.

After everything is done on the Management Server, you Can view the status through mmm_control and get the following ERROR message: ERROR: Can't connect to monitor daemon! Change debug 0 to debug 1 by editing the/etc/mysql-mmm/mmm_mon.conf file to enable the debug status of the monitoring program. Restart the Monitoring Program (service mysql-mmm-monitor restart). The detailed error message is displayed and the Perl Time HiRes library cannot be found. Run yum-y install perl-Time-HiRes.

2. Firewall problems cause Warning: agent on host db1 is not reachable.
After the console program is correctly started, run mmm_control show again, but the following output is displayed:

Copy codeThe Code is as follows:
# Warning: agent on host db1 is not reachable
# Warning: agent on host db2 is not reachable
# Warning: agent on host db3 is not reachable
# Warning: agent on host db4 is 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:

Open debug again and find the following error message:
Copy codeThe Code is as follows:
13:38:45 DEBUG Sending command 'get _ AGENT_STATUS () 'to db4 (192.168.85.167: 9989)
13:38:45 ERROR The status of the agent on host 'db4' cocould not be determined (answer was: 0 ).

Telnet 192.168.85.167 9989 to check the network connection and obtain the error message "No route to host. Log on to db1 and close Firewall through Firewall configuration in the setup program (this is not a good idea ). Similarly, shut down the firewall on db2, db3, and db4, restart the monitoring program again, and everything goes back to normal!

References:

MySQL MMM installation documentation http://mysql-mmm.org/mmm2:guide
MMM Manual http://mysql-mmm.org/mysql-mmm.html