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

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 

If you are unable to access Smoothwall’s web interface but can shell OK carry out the following:

killall -9 httpd 

/usr/apache2/sbin/httpd -DSSL

Try and login again.

This is following on from setting up Smoothwall with BT Infinity – https://www.linuxtutorial.co.uk/smoothwall-express-bt-infinity-setup/

The issue I now had was giving our network a static IP address instead of Dynamic. This was to allow another host to allow a static IP through their Firewall.

The workaround for this was to assign an External (red) IP address to an Internal (Green) IP address.

BT will provide 5 extra static IPs for £5 per month.

To set this up:

1. Full Firewall – Multiple IPs
2. Set the required external red IP and the IP of the internal machine

Tpye the following into commandline:

/etc/rc.d/rc.netaddress.down; /etc/rc.d/rc.netaddress.up

Which logs out the following on screen:

Setting up firewall
Restarting DNS proxy
Bringing up interface aliases (if they exist)
Register: Already registered
got my.SmoothWall system info with id [xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx]
Setting up firewall
Starting traffic stats collector
Setting DMZ pinholes
Setting up advanced networking features
Setting up IP block
Setting external access rules
Setting up outgoing filter
Setting up timed access rules