3 - Changing the databases on a server for a new IP address

Applications often store data such as the IP address in databases. So that this does not cause problems during the migration of an IP address, the entries in the databases must also be changed.

Important: Be sure to create a backup of all relevant databases before running either method.

There are two ways to modify the entries in the databases. We recommend the first method for beginners and users with average knowledge. The second method is suitable for advanced users, because it is faster but can destroy databases by accident..:

1 - You have phpmyadmin in use

phpmyadmin offers one search function per database. Proceed as follows:

1.1 Log in to phpmyadmin

1.2 Click Links to the Top Database

1.3 Click Export ("Exportieren" in the Screenshot) and then OK to download a database backup.

1.4 Click "Search ("Suche" in the Screenshot)".

1.5 Enter the IP address of your server in the search field (in the example screenshot 1.1.1.1) and select all tables from the list.

Please note: If you do not select all tables, you may not be able to find all entries. To select all tables, click on the first entry (in the example ALL_PLUGINS), scroll down the list, hold down the SHIFT key and click on the last entry. This will select all entries (from top to bottom).

1.6 Click OK.

1.7 A list of all tables in the database will be displayed. If a hit is found, you can click on "Show ("Anzeigen" in the Screenshot)" a little further to the right.

1.8 After you have clicked on "Show", the results found in the respective table will be displayed. You can then edit the value by clicking on "Edit" ("Bearbeiten" in the screenshot). If no "Edit" option exists, the entry cannot be edited. Either editing is not necessary or you have to contact the programmer of the software that uses (and created) the database.

1.9 Scroll back up to the list of found entries. Repeat steps 1.7 and 1.8 for all tables where hits were found.

1.10. Click on the next database on the left and repeat steps 1.3 to 1.9 for all databases on your server except information_schema and performance_schema.

2 - You do not use phpmyadmin

If you do not have phpmyadmin installed, you can either install phpmyadmin and select option 1 or do the following:

2.1 Log in to your database server:

mysql -u root -p

2.2 Display all databases:

SHOW DATABASES;

2.3 Make a note of the list of databases and leave the SQL Console:

exit;

2.4 Create a working directory, a temporary directory and change to the working directory

mkdir /databasebackup && mkdir /databasetemp && cd /databasetemp

2.4 Go through the following commands for all databases except information_schema and performance_schema and replace DBNAME in all three places with the name of the respective database (you have to enter your mysql root password every time after executing the first command):

mysqldump -u root -p DBNAME > DBNAME.sql

cp DBNAME.sql /databasebackup

Important: If you do not replace DBNAME in one place or forget to replace the database name, this can cause serious database errors. (For example, you may import a wrong database into another database).

2.5 You should now have one file for each database in /databasebackup and /databasetemp. You can check this with the following command:

ls -lh /databasebackup

ls -lh /databasetemp

You can check to see if the file sizes are the same. (marked in the screenshot with two arrows of the same color)

2.6 You can now use the following command to change the IP address in all database backups in /databasetemp:

Warning: The following command makes changes to your files:

grep -rwl '/databasetemp' -e 'IPADRESS' | xargs sed -i 's/IPADRESS/NEWIPADRESS/g'

Please note the following:

• Do not forget to replace IPADRESS with your old IP address and NEWIPADRESS with your new IP address in the back part of the command. If you forget this, it can cause errors in your system. If you have noticed that you have made a mistake, we recommend that you do not enter any further commands and contact our customer support.
• It is possible that the command will take several minutes to process as all database backups will be run. If the command takes longer than 5 minutes to execute, we recommend contacting our customer support.
• For example, if your old IP address was 1.1.1.1 and your new IP address 2.2.2.2, the command would look like this: grep -rwl '/databasetemp' -e '1.1.1.1' | xargs sed -i 's/1.1.1.1/2.2.2.2/g'
  

2.7 Import the databases again by executing the following command for all databases:

mysqldump -u root -p DBNAME < DBNAME.sql

Note that the direction of the ">" has changed to "<".

If you have executed one of the two variants, please restart your server via the customer center or the "reboot" command. If you have made the adjustments of the configuration files and the databases and your services are still not available, we recommend to check the log files of the respective application. If you do not find any errors or do not know where the log files are, please contact our customer support.

Source:

https://stackoverflow.com/questions/4822638/find-and-replace-entire-mysql-database/24652842