Simple bash script to remove duplicate rows from a table:

#!/bin/bash
echo "Remove duplicate records from tbl_duplicates"
mysql -uusername -ppassword database -e "DELETE n1 FROM tbl_duplicates n1, tbl_duplicates n2 WHERE n1.col_id > n2.col_id AND n1.col_duplicate = n2.col_duplicate;"

Terms mentioned:

tbl_duplicates – table to remove duplicates from
col_id – column ID
col_duplicates – a column which contains duplicate data
> – this keeps the row with the lowest id

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

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/

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;

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