Change IP addresses subnet in mysql

Change IP addresses subnet in mysql

Change IP addresses subnet in mysql

 

I created this script because I had to change all the IPs of users in the database. For example: all IPs like 1.1.1.x became 2.2.2.x.

#!/bin/sh
### variables
dbuser="database username"
dbpass="database password"
dbname="database name"
tablename="table name"
columnname="column name"
newsubnet="x.y.z"
### code
mysql -u $dbuser -p$dbpass $dbname -Bse "SELECT $columnname FROM $tablename;" | while read ip; do
    ipgroup4=$(echo ${ip} | tr "." " " | awk '{print $4}')
    newip=$newsubnet.$ipgroup4
    mysql -u $dbuser -p$dbpass $dbname -e "UPDATE $tablename SET $columnname='$newip' WHERE $columnname='$ip';"
done

Do not forget to backup the database before using this script!

Change IP addresses subnet in mysql




Change IP addresses subnet in mysql
Tagged on:             

Leave a Reply

Skip to toolbar