Sooner or later it will come to a situation in which we have to transfer data between different database. Given the fact we have to do a normal database upgrade Oracle Export/Import will help us to do as long we will go with a full database export and all users, priviledges, permissions and quota will get transfered.

Now given the case that we based on schedules or migration plans just have to overtake single schemas world does look different. Oracle Import will expect theuser schema already exisiting and even try to create all database objects in relation to their old location within the former database.

Now I’m not going to explain you how to change tablespace or storage information within this posting … perhaps in another one … but will try to help you with pre-setting up the schema and additional security information before importing the data.

As for we work with some database system all data can for sure get queried and even stored within directly executable sql statement. Since years I’m working with an easy to use set of scripts collecting these information for me.

Feel free to download it from here and using it for your own pleasure.  When unpacked you can call the script 0_capture_security_information.sql within SQLPLUS and you will get generated another result set of scripts containing all users, roles and granted system and role priviledges stored with the database you are connected too.

Connecting now to the new target database and executing the scripts full or in parts will allow you to set up security there again.

As for todays hosting is mainly done on UNIX systems transfering websites can be done quick and reliable using UNIX commands. And therefore a changing your Hosting Provider can become fairly easy.

Instead of downloading all content from Server A to your local system using a however clever FTP Client and later uploading it again to Server B, this whole file transfer can be done within one step using a server to server copying command.

As a basic requirement to use this feature, you have to have secure shell access onto both servers. Being logged in to one server (it doesn’t matter which one) you can use SCP command to transfer all your files. The syntax of the command is fairly easy and it is part of the command to specify source and target system and therefore is doesn’t matter if you are pulling or pushing the files.

Easy command line examples for an SCP command would be:

Copying file to host:

scp SourceFile user@host:directory/TargetFile

Copying file from host:

scp user@host:directory/SourceFile TargetFile

Using the -R option as well will allow you to transfer whole directory trees within one single copy command.

While transfering all files and perhaps several domain can take up several days depending on the size and complexity of your hosting, it can occur that parts of the original files are already changed again until the final go life.

To prevent differences in between source and new target system a final update of both system shortly before switching over does make sense. Here now the command RSYNC will help us.

With RSYNC you can syncronize files and folders between systems.

Again an easy command line example would be:

rsync -r user@host:directory/SourceFolder TargetFolder

Also here source and target system needs to be specified and therefore both sending and receiving are possible.

For security reason you should change the root password of your MySQL installation immediately! Unfortunately and even it is very easy many users leave it untouched though.

There are several way to do and a lot of MySQL tools will assist nad help you with that. But to give you a helping hand with your security we will share the most two common ways to do with you here.

First there is the possibility to directly change it on your server prompt by entering a simple command forcing the MySQL engine to ask for a root password further on:

To change your the MySQL root password to PaSsWoRd, use:

mysqladmin -u root password PaSsWoRd

Another way to succeed could be changing it by SQL statement:

grant all on database.* to username@’%’  identified by “password”;
grant all on database.* to username@’localhost’  identified by “password”;

With this given example you can do both, either create a new user or change the password for an already existing user.

Please take care about to substitute the given variable within the SQL statement for database, username and password with appropriate values though. As also be aware that based on MySQL is differencing between local and remote traffic you have to do for both sources!

Already set a password and later on, you have to use the following command:
mysql -u root -p and at command login prompt password:  “type in your password” to then go on executing the statements above.