December 18, 2007

Export/import all your database

I have many backlogs at the faculty, the one was new server with user databases and other services. So I spent this Sunday moving them from one server to another. PostgreSQL and MySQL were easy, but I had some problems with installing Oracle 10g on Gentoo x86-64. I made a short notes for me on how to export/import data from them.

With the indentity of user postgres dump all:
$ pg_dumpall -D >pgsql.dmp
On new machine init database under postgres:
$ initdb -E UTF-8 --locale=cs_CZ.UTF-8 /var/lib/pgsql/data
Configure and run database:
$ vim /var/lib/pgsql/data/postgresql.conf
$ /etc/init.d/postgresql start
Recover database under user postgres:
$ psql -f pgsql.dmp

Dump all data:
$ mysqldump -u root -p -A >mysql.dmp
On new server init the data structures, configure and start server and import old data:
$ vi /etc/my.cnf
$ mysql_install_db
$ /etc/init.d/mysqld start
$ mysql -u root <mysql.dmp
(or look at the mysqlimport command)

To export/import Oracle there are exp/imp utilities in $ORACLE_HOME/bin directory.
$ exp file=oracle.dmp full=y
$ imp file=oracle.dmp full=y

No comments: