Build the MySQL MMM architecture from scratch
- 2020-10-23 20:19:06
- OfStack
The cloud platform is a good thing, MySQL-ES2en typically requires five machines, one as mmm admin, two as master, and two as slave. It's not easy to find five machines, not to mention having to install the same operating system. With cloud, we have a complete experimental environment in a few simple steps: 4 database servers and 1 management server (Memory: 8G, CPU: 2G, Disk: 128G, 64bit RHEL6). Here, I would like to express my heartfelt thanks to my colleagues who have worked so hard to build the cloud platform :-) Now let's get back to work and start a new journey of MySQL mmm.
The MySQL Cluster environment to be configured consists of 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 |
Once configured, access MySQL Cluster using the following virtual IP
| 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 was installed directly from the yum command.
2. Modify configuration file /etc/ ES47en.cnf
To place the additions in the [mysqld] section of the configuration file, you will see the following:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Below is the new addition
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
Note:
1) The values of ES58en-ES59en on each server are different, which are 1, 2, 3 and 4 in order here.
2) Since the log file is configured under /var/log/mysql, and the default directory of mysql is /var/lib/mysql, the mysql folder should be created first, and then the chown-ES71en mysql.mysql mysql command should be used to change the owner of mysql to user mysql. Second, ensure that the permissions of the mysql folder are 755 (i.e. -ES78en-ES79en-ES80en).
If there is no modify permissions and ownership, restart services will appear in the error log can't find mysql - bin. log or mysql - bin. log. index error (/ usr/libexec/mysqld: File '/ var log mysql/mysql - bin. log. index' not found (Errcode: 13)).
3. Restart mysql service
After completing the modifications to ES108en.cnf, the mysql service is restarted via service mysqld restart. After proper startup, you can check that the configuration is correct by:
1) Log in mysql and execute show master status to see if there is the following output:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | mysql |
+------------------+----------+--------------+------------------+
2) Go to /var/log/mysql directory to see if files similar to ES126en-ES127en.000001 and ES128en-ES129en.log.index have been generated.
2. Create the users needed for the synchronization database
Using ES136en-ES137en 1 requires a total of three users: replication, mmm_agent, and mmm_monitor (the user on the administration server that monitors the status of cluster, so you can restrict login to the administration server only). Create the three users and assign the corresponding permissions using the following three commands:
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';
3. Synchronize master and slave databases
1. Export the current database contents from the primary database server
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | mysql |
+------------------+----------+--------------+------------------+
Be careful to keep the above information as it will be used later. Also, do not end the current mysql console, reopen a window and export the database.
# mysqldump -uroot -proot --all-databases > db01_20111005.sql
Release the lock
mysql> UNLOCK TABLES;
2. Import the exported sql file to several other database servers. First, copy the past through scp:
# scp db01_20111005.sql root@192.168.85.167:/root/
Import SQL files on other services:
# mysql -uroot -proot < db01_20111005.sql
3. Start the PROCESS from database SLAVE.
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. Use db02 as master and db01 as slave, repeat 1-3.
4. Install MMM
The mysql-ES186en monitor and agent programs are run on the administrative server and the database server, respectively. Install the following separately:
1. Install the monitor
On the administration server (192.168.84.174), execute the following command:
# yum -y install mysql-mmm-monitor*
All files dependent on monitor will also be installed with one exception, perl-ES196en-ES197en, so you will also need to execute the following command:
# yum -y install perl-Time-HiRes*
2. Install the agent
Execute the following command on the database server:
# yum -y install mysql-mmm-agent*
5. Configuration MMM
1. Edit mmm_common. conf
After the installation is complete, all the configuration files are placed under /etc/ ES217en-ES218en /. A common file es219EN_common.conf should be included on both the administrative server and the database server, which reads 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 192.168.85.167
mode master
peer db2
</host>
<host db2>
ip 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 it to monitor, db2, db3, and db4 with the scp command, respectively.
2. Edit ES234en_ES235en.conf On the database server, there is another mmm_ES238en.conf that needs to be modified, which is as follows:
include mmm_common.conf
# The 'this' variable refers to this server. Proper operation requires
# that 'this' server (db1 by default), as well as all other servers, have the
# proper IP addresses set in mmm_common.conf.
this db1
The last line, db1, needs to be changed to db2, db3, and db4 on different database servers, otherwise the agent will fail to start.
3. Edit ES250en_ES251en.confg On the management server, modify the es253EN_mon.conf file, and the modified content 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
# cd /etc/init.d/
# chkconfig mysql-mmm-agent on
# service mysql-mmm-agent start
2. Start the monitor on the administration server
# cd /etc/init.d/
# chkconfig mysql-mmm-monitor on
# service mysql-mmm-monitor start
After startup, wait a few seconds to check the status through the mmm_control program:
# 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. There are two problems
1. The monitor server cannot start
On the administration server, once all the slices are completed, check the status via mmm_control and get the following error message: ERROR: Can 't to monitor daemon! Open the debug status of the monitor by editing the /etc/ ES291en-ES292en/mmm_ES294en.conf file to change debug 0 to debug 1. Restart the monitor (service ES300en-ES301en-ES302en restart) and you will see the detailed error message and the Perl Time HiRes library cannot be found. This can be done by executing ES307en-ES308en install ES310en-ES311en-ES312en *.
2. Firewall problems caused Warning: agent on host db1 is not reachable
After the console program starts correctly, execute mmm_control show again, only to see the following output:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | mysql |
+------------------+----------+--------------+------------------+
Open debug again and find the following error message:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | mysql |
+------------------+----------+--------------+------------------+
By checking the network connection under telnet 192.168.85.167 9989, the error message of No route to host was obtained. Log on to db1 and close Firewall via Firewall configuration in the setup program (not a good idea). Similarly, close the firewall on db2, db3, and db4, and restart the monitor again. Cut 1 back to normal!
Reference article:
MySQL MMM official document http installation: / / mysql - mmm org/mmm2: guide
MMM Manual http://mysql-mmm.org/mysql-mmm.html