Linux Tutorial | Matt Cooper - Open Source Support
Linux Tutorial | Matt Cooper - Open Source Support
Bash Scripting, MySQL Tutorial

MySQL Master-Slave Replication – Bash Script Monitor

Simple script to verify that MySQL replication is working:

*Script is setup on slave server

#!/bin/bash
# Originally from http://lists.mysql.org/replication/1672
# Checks MySQL Replication status. Sends user(s) a notification of status
status=0
MasterHost="10.10.10.10"
SlaveHost="127.0.0.1"
emails="user@email.com" #multiple emails space separated
DownSubject="Replication status - Down"
GoodSubject="Replication status - Good"
GoodMessage="Everything regarding MySQL replication on $SlaveHost is good.nHave a great day!nn"

#Grab the lines for each and use Gawk to get the last part of the string(Yes/No)
SQLresponse=`mysql -u root --password=password dbname -e "show slave status G" |grep -i "Slave_SQL_Running"|gawk '{print $2}'`
IOresponse=`mysql -u root --password=password dbname -e "show slave status G" |grep -i "Slave_IO_Running"|gawk '{print $2}'`

if [ "$SQLresponse" = "No" ]; then
error="Replication on the slave MySQL server($SlaveHost) has stopped working.nSlave_SQL_Running: Non"
status=1
fi

if [ "$IOresponse" = "No" ]; then
error="Replication on the slave MySQL server($SlaveHost) has stopped working.nSlave_IO_Running: Non"
status=1
fi

# If the replication is not working
if [ $status = 1 ]; then
for address in $emails; do
echo -e $error | mail -s "$DownSubject" $address
echo "Replication down, sent email to $address"
done
fi

# If the replication is working fine
if [ $status = 0 ]; then
for address in $emails; do
echo -e $GoodMessage | mail -s "$GoodSubject" $address
echo "Replication is up, still sent email to $address"
done
fi
March 14, 2014by Matt Cooper
FacebookTwitterPinterestGoogle +Stumbleupon
MySQL Tutorial

MySQL – Master Slave Replication Setup – Debian

This tutorial will setup Master-Slave replication presuming the following:

Master – 10.10.10.1
Slave – 10.10.10.2

Master Server

Edit the MySQL configuration file

 
vim /etc/mysql/my.cnf

The following needs updated – comment out bind-address, uncomment server-id, uncomment log_bin, uncomment binglog_do_db:

#bind-address = 127.0.0.1
server-id =1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = newdatabase

Save and quit.

*NOTE – newdatabase = the database which is going to be replicated.

Restart MySQL:

/etc/init.d/mysql restart

Next we access MySQL:

mysql -u root -p

The slave server needs privileges to access the master server. Set username and password as follows:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

Now are apply/flush privileges:

FLUSH PRIVILEGES;

The next stage is to find the log file position of the master database. This is done by:

USE newdatabase;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 150 | newdatabase | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Now we take a copy of the database on the Master server and transfer to the slave:

mysqldump -u root -p newdatabase > newdatabase.sql

Lastly we need to unlock the tables and quit:

unlock tables;
quit;

 

Slave Server

Setup the new slave database (same name as master for replication):

create database newdatabase;
quit;

Import the database which was previously exported from the master:

mysql -u root -p newdatabase < /path/to/newdatabase.sql

Now the MySQL configuration file needs updated similar to the master setup:

 
vim /etc/mysql/my.cnf

The following needs updated – comment out bind-address, uncomment server-id, uncomment log_bin, uncomment binglog_do_db, add relay-log:

#bind-address = 127.0.0.1
server-id =2
log_bin = /var/log/mysql/mysql-bin.log
relay-log= /var/log/mysql/mysql-relay-bin.log
binlog_do_db = newdatabase

Save and quit. Restart MySQL:

/etc/init.d/mysql restart

Next is to enable replication within the MySQL shell:

mysql -u root -p
CHANGE MASTER TO MASTER_HOST='10.10.10.2',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 150;

Now start slave:

Start Slave;

To check the slave is running run the following:

SHOW SLAVE STATUSG

This should output something similar to:


*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.10.1
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 525399
Relay_Log_File: mysqld-relay-bin.000005
Relay_Log_Pos: 464001
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 525399
Relay_Log_Space: 525805
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1

Below are the 2 most important parts of the slave status:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If both are yes then you’re replication is up and running.

To test the replication, simply add new records to the master database and verify the same the records appear on the slave.

Monitoring script – https://www.linuxtutorial.co.uk/master-slave-replication-bash-script-monitor/

March 13, 2014by Matt Cooper
FacebookTwitterPinterestGoogle +Stumbleupon
MySQL Troubleshooting

ERROR: Query failed. 1142: SELECT command denied to user ‘user’@’10.10.10.10’ for table ‘proc’

When running the mysqldbcompare command as mentioned in the previous post you may get the following error:

ERROR: Query failed. 1142: SELECT command denied to user 'user'@'10.10.10.10' for table 'proc'

Essentially the user ‘user’ doesn’t have the correct permissions to ‘SELECT’ from the database. To fix update permissions from within MySQL:

grant all privileges on database.* to 'root'@'10.10.10.10' identified by 'password';

flush privileges;
March 12, 2014by Matt Cooper
FacebookTwitterPinterestGoogle +Stumbleupon
MySQL Tutorial

MySQL Compare Databases on Remote Servers – mysqldbcompare

A simple script to compare 2 databases on different servers.

Pre-Req – install mysql workbench to pull in all dependencies and the mysqldbcompare scripts:

apt-get install mysql-workbench

Now run the following command to run the comparison of the 2 databases:

mysqldbcompare --server1=databaseuser:password@127.0.0.1 --server2=databaseuser:password@10.10.10.10 server1db:server2db --changes-for=server2 -a --difftype=sql >> results.log

The above compares the database ‘server1db’ against ‘server2db’ and outputs the results to results.log. Below is a sample of the log file:

# server1 on 127.0.0.1: ... connected.
# server2 on 10.10.10.10: ... connected.
# Checking databases server1db on server1 and server2db on server2
#

# WARNING: Cannot generate SQL statements for these objects.
# Check the difference output for other discrepencies.
--- server2db
+++ server1db
@@ -1 +1 @@
-CREATE DATABASE `server2db` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `server1db` /*!40100 DEFAULT CHARACTER SET latin1 */

# WARNING: Cannot generate SQL statements for these objects.
# Check the difference output for other discrepencies.
--- server2db
+++ server1db
@@ -1 +1 @@
-CREATE DATABASE `server2db` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `server1db` /*!40100 DEFAULT CHARACTER SET latin1 */

# Defn Row Data
# Type Object Name Diff Count Check
# -------------------------------------------------------------------------
# TABLE table1 pass pass pass
# TABLE table2 pass pass SKIP
#
No primary key found.
# TABLE table3 pass pass FAIL
#
# Transformation for --changes-for=server2:
#

# Data differences found among rows:
UPDATE server2db.table3 SET testid = '500' WHERE id = '5';

# Database consistency check failed.
#
# ...done

The above found that one of the rows in ‘table3’ on server1 differed from ‘table3’ on server2. It then outputs the updae statement needed to correct ‘table3’ on server1. i.e.

# Data differences found among rows:
UPDATE server2db.table3 SET testid = '500' WHERE id = '5';

TROUBLESHOOTING

ERROR: Query failed. 1142: SELECT command denied to user ‘user’@’10.10.10.10’ for table ‘proc’
Failed to connect to DB

March 12, 2014by Matt Cooper
FacebookTwitterPinterestGoogle +Stumbleupon
Command line Tutorial

Find recently modified files Linux

The below allows you to search for recently modified files in the directory of your choosing.

Find by time (minutes):

 
find /directory -type f -mmin -60 

Find by date:

 
find /directory -type f -mtime -2 
January 28, 2014by Matt Cooper
FacebookTwitterPinterestGoogle +Stumbleupon
Page 30 of 35« First...1020«2829303132»...Last »

About me

Hi, I'm Matt Cooper. I started this blog to pretty much act as a brain dump area for things I learn from day to day. You can contact me at: matt@linuxtutorial.co.uk

Recent Comments

  • Andrew on Export list of Amazon EC2 Instances to CSV
  • Matt Cooper on Proxmox – add a second hard drive to node for Backups
  • karis on Proxmox – add a second hard drive to node for Backups
  • Matt Cooper on Remote MySQL Database – Slow Connection
  • Matt Cooper on Bash script to send public IP address to Email

Categories

  • AdvancedTomato
  • Amazon AWS
  • Amazon Linux
  • Amazon S3
  • Apache
  • Apache Kafka
  • Backup Tutorial
  • Bash Scripting
  • Centos Tutorial
  • CloudFlare
  • Command line Tutorial
  • CPanel Troubleshooting
  • CPanel Tutorial
  • Debian Troubleshooting
  • Debian Tutorial
  • DKIM
  • Docker
  • EC2
  • ESXi
  • Faildows
  • Google Adwords
  • Google Analytics
  • Google Chromebook
  • Google Mail
  • graylog
  • IAM
  • imapsync
  • iRedmail Tutorial
  • ISPConfig3 Tutorial
  • Java
  • ldap
  • letsencrypt
  • MyDNS
  • MySQL Troubleshooting
  • MySQL Tutorial
  • Nest Install
  • Netbeans
  • Nginx
  • Nginx Troubleshooting
  • openssl
  • PCI Compliance
  • Percona
  • PHP
  • Plex Media Server Tutorial
  • Postfix
  • Proxmox Tutorial
  • Pure FTPd
  • Resourcespace Tutorial
  • Route 53
  • Rsync Tutorial
  • Security
  • Smoothwall Troubleshooting
  • Smoothwall Tutorial
  • SSH
  • tcpdump
  • Thunderbird
  • Tomcat Troubleshooting
  • Ubuntu Tutorial
  • Uncategorized
  • Unison
  • vmware
  • vzdump
  • WHM
  • Wireshark
  • Wordpress Troubleshooting
  • WordPress Tutorial

“See, you not only have to be a good coder to create a system like Linux, you have to be a sneaky bastard too." Linus Torvalds

© 2017 copyright www.linuxtutorial.co.uk // All rights reserved