25th MySQL Replication (master-Slave) configuration and added Nagios monitoring

Environment Introduction:

master:23.247.76.253

[[Email Protected]_client1 tool]#

Mysql-v

MySQL Ver 14.14 distrib 5.6.32, for linux-glibc2.5 (x86_64) using Editline Wrapper

[[Email Protected]_client1 tool]#

Cat/etc/redhat-release

CentOS Release 6.7 (Final)

slave:23.247.78.254

[[Email Protected]_client2 ~]#

Mysql-v

MySQL Ver 14.14 distrib 5.6.32, for linux-glibc2.5 (x86_64) using Editline Wrapper

[[Email Protected]_client2 ~]#

Cat/etc/redhat-release

CentOS Release 6.9 (Final)

1. Set the Master

To modify a configuration file:

Vim/usr/local/mysql_2/my.cnf

In the [mysqld] section, see if you have the following, and if not, add:

Server-id=1

Log-bin=mysql-bin

In addition to these two lines are required, there are also two parameters that you can use for sexual selection:

Binlog-do-db=test3,databasename2

Binlog-ignore-db=databasename1,databasename2

Binlog-do-db= needs to replicate the database name, multiple database names, separated by commas. Binlog-ignore-db= does not need to replicate database library names, multiple database names, separated by commas. These two parameters are actually used in one. Set the TEST3 for the database that I want to synchronize.

Restart after modification:

/etc/init.d/mysqld restart

Shutting down MySQL. success!

Starting MySQL. success!

mysql> grant replication Slave on * * to ' repl ' @ ' 23.247.78.254 ' identified by ' 123456 ';

The REPL here is for the slave end of the user to access the master-side MySQL data, the password is 123456, where the 23.247.78.254 is the slave IP.

Mysql> flush tables with read lock; Locks the database and does not allow changes to any data at this time

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show master status; View status, this data is to be recorded, one will be used in the slave end of the

+------------------+----------+--------------+------------------+-------------------+

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+------------------+----------+--------------+------------------+-------------------+

|      mysql-bin.000002 | 330 |                  Test3 |                   | |

+------------------+----------+--------------+------------------+-------------------+

1 row in Set (0.00 sec)

Set Slave:

First modify the slave configuration file my.cnf:

Vim/etc/my.cnf

Find "Server-id = 1" This line, delete or change to "Server-id = 2" In short, you can not let this ID and master, otherwise it will be an error. On the other hand, you can also choose to add the following two lines, corresponding to the two lines that the Lord added:

Replicate-do-db=databasename1,databasename2

Replicate-ignore-db=databasename1,databasename2

After the change, restart slave:

Service mysqld Restart

Then configure the master and slave on the Slave:

mysql> stop Slave;

Query OK, 0 rows affected, 1 Warning (0.00 sec)

mysql> Change Master to master_host= ' 23.247.76.253 ', master_port=3306,

Master_user= ' Repl ', master_password= ' 123456 ',

-master_log_file= ' mysql-bin.000002 ', master_log_pos=330;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;

Where Master_log_file and Master_log_pos are the data found on the above using show Master status. After you perform this step, you need to perform a step on master:

mysql> unlock tables;

Query OK, 0 rows Affected (0.00 sec)

Then check the status of the slave:

Mysql> show Slave status\g;

Error encountered:

Slave_io_running:no

Slave_sql_running:yes

last_io_errno:1593

Last_io_error:fatal error:the slave I/O thread stops because master and slave have equal MySQL server IDs; These IDs must be different for replication to work (or the--replicate-same-server-id option must is used on slave but th is does do sense; Please check the manual before using it).

Solve:

Mysql> Show variables like ' server_id '; View the server ID number set global server_id=xx Modify ID number

+---------------+-------+

| variable_name | Value |

+---------------+-------+

| server_id | 1 |

+---------------+-------+

1 row in Set (0.00 sec)

mysql> set global server_id=2;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like ' server_id ';

+---------------+-------+

| variable_name | Value |

+---------------+-------+

| server_id | 2 |

+---------------+-------+

1 row in Set (0.00 sec)

Verify that the following two parameters are yes:

Slave_io_running:yes

Slave_sql_running:yes

Test master-Slave synchronization:

Master execution:

Mysql> Select COUNT (*) from T1

;

+----------+

| COUNT (*) |

+----------+

| 0 |

+----------+

1 row in Set (0.00 sec)

mysql> drop table T1; Delete T1 table

Query OK, 0 rows affected (0.01 sec)

Mysql> Select COUNT (*) from T1;

ERROR 1146 (42S02): Table ' test3.t1 ' doesn ' t exist

Slave execution:

Mysql> Select COUNT (*) from T1

;

+----------+

| COUNT (*) |

+----------+

| 0 |

+----------+

1 row in Set (0.00 sec)

Mysql> Select COUNT (*) from T1;

ERROR 1146 (42S02): Table ' test3.t1 ' doesn ' t exist

Delete the T1 table in Mster, and then slave check the T1 table to indicate that the T1 table does not exist. Proof that the master operation has been synchronized;

The master-slave configuration is very simple, but this mechanism is also very fragile, once we accidentally write the data on the slave, then the master and slave is destroyed. In addition, if you restart Master, be sure to stop the slave first, that is, you need to slave up the Stop Slave command, and then to restart the master MySQL service, otherwise it is likely to be interrupted. Of course, after the restart, you also need to open the slave to start slave.

Nagios monitors MySQL master-slave synchronization status

Slave query

[Email protected]_client2 ~]# mysql-uroot-p "123456"-e "show slave Status\g" |grep "Running:"

Warning:using a password on the command line interface can is insecure.

Slave_io_running:yes

Slave_sql_running:yes

" Slave_io_running:yes " and"slave_sql_running:yes", these two values are all" yes "indicates that the master-slave library is successfully synchronized

Command:

[[Email Protected]_client2 ~]#

Slave_status= ($ (mysql-uroot-p "123456"-e "show slave status\g" |grep Running |awk ' {print $} '))

Warning:using a password on the command line interface can is insecure.

[[Email Protected]_client2 ~]#

Echo ${slave_status[0]}

Yes

[[Email Protected]_client2 ~]#

Echo ${slave_status[1]}

Yes

To view the check script:

Cat/usr/local/nagios/libexec/check_mysql_slave

#!/bin/sh slave_status= ($ (mysql-uroot-p "123456"-e "show slave status\g" |grep Running |awk ' {print $} ')) if ["${slave_s Tatus[0]} "=" yes "-a" ${slave_status[1]} "=" yes "] then echo" OK Nagios_client2-slave is running "Exit 0 else echo "Critical Nagios_client2-slave is Error" Exit 2 fi

Riga in Nrpe.cfg file:

Vi/usr/local/nagios/etc/nrpe.cfg

Command[check_mysql_slave]=/usr/local/nagios/libexec/check_mysql_slave

Restart Nrpe:

Pkill Nrpe

/usr/local/nagios/bin/nrpe-c/usr/local/nagios/etc/nrpe.cfg-d

Server-side configuration:

[[Email Protected]_server objects]#

/usr/local/nagios/libexec/check_nrpe-h 23.247.78.254-c Check_mysql_slave

OK Nagios_client2-slave is running

Editing the MySQL service monitoring configuration file

Vi/usr/local/nagios/etc/services/mysql.cfg

Plus:

Define Service {

Use Generic-service

HOST_NAME Nagios_client2

Service_description Check_mysql_replication_status

Check_command Check_nrpe!check_mysql_slave

Max_check_attempts 2

Normal_check_interval 2

Retry_check_interval 2

Check_period 24x7

Notification_interval 10

Notification_period 24x7

Notification_options W,u,c,r

Contact_groups Admins

Process_perf_data 1

}

Add to MySQL service group: VI servicegroups.cfg

Members nagios_client1,port_3306,nagios_client2,port_3306,Nagios_client2,check_mysql_replication_statu S

/etc/init.d/nagios Checkconfig #检测配置文件

[[Email Protected]_server services]#

/etc/init.d/nagios Reload #重新加载配置文件

Final Result:

25th MySQL Replication (master-Slave) configuration and added Nagios monitoring