Migrate all databases to remote server with mysqldump in one step – 1:1 migration

Here is quick one, last in this year :). So I had to move a lot of databases to another server, but problem was, that on source server there wasn’t enough disk available. Also it was migration from very old mysql version to mariadb so mysqldump is your friend. Mysqldump all databases was out of the question because of low disk space. Dumping each database on its own would take too long and too many effort. But you can create dump of database and import it on new server in the same step.

First, you’ll need list of all databases on your source server and create them on new server. If your mysqldump creates “create database”, then you don’t need to create them manually on new server. If you want, you can skip mysql and any other databases that you don’t wish to transfer with grep. Put list of databases in some file – databases.txt for example. But first, make shure that command bellow show all databases. It is also necessary that you can remote access to mysql from source server to new server.

Test list all databases (exclude unwanted ones):

[root@oldserver ~]# mysql -e 'show databases' | grep -v "|" | grep -v "Database\|information_schema\|mysql\|performance_schema"
database1
geekytuts
database2
database3
database4
database5

Then put list of databases in text file databasest.txt:

[root@oldserver ~]# mysql -e 'show databases' | grep -v "|" | grep -v "Database\|information_schema\|mysql\|performance_schema" > databases.txt

Then you can import database to remote server like this:

mysqldump -u root -ppassword --single-transaction --skip-lock-tables database1 | mysql -h 1.1.1.1 -u root -ppassword database1

If you want to import all/multi databases, then use database.txt that we created in first step with for loop:

for i in `cat databases.txt`; do mysqldump -u root -ppassword --single-transaction --skip-lock-tables $i | mysql -h 1.1.1.1 -u root -ppassword $i; done

Bonus: If you need to create all databases listed in databases.txt on new server manualy, then you can also create all of them in one step. Use databases.txt on new server. If your mysqldump creates “create database” also, then you can skip this step.

[root@newserver ~]# for i in `cat databases.txt`; do mysql -u root -ppassword -e create database $i; done

Hope this helps someone.

Happy new year!

Linux: restore all system permissions of your server

If you ever found your self in situation when you accidentally overwrite all permissions of your system, and everything stops working, then solution bellow may do the trick. This CentOS server was overwritten by wrong permissions through the whole system. Quick solution is to set up right permissions back. If you have backup of server that’s great. Otherwise you’ll have to set up new server with similar installation or do this on some other server with similar installation.

On “new” server, copy permissions of the whole system and save it to a file. You can also exclude dirs that you don’t need also.

find / -not -path "/proc*" -not -path "/dev*" -not -path "/sys*" -not -path "/var/www*" -exec stat -c "chmod %a %n;" {} \; > permissions.txt

On your “broken” server, rewrite all permissions:

cat permissions.txt | bash

Restore MSSQL database with new name

Restoring old MSSQL database to a new database with different name can be a little bit tricky on Windows. This was done on Windows Server 2008 with SQL Server 2008 Management Studio installed.

Here is how:

  1. Login to your SQL Server Management Studio.
  2. Create new database with name that you wish. In this case, we will create database name “new_database”. Just right click on “Databases” and then “New database”.
    mssqlrestpre1

  3. Select newly created database and right click on it, then select “Tasks -> Restore -> Database …” In “Destination for restore”, select database that we created in second step – new_database. Select it from drop down menu “To database”. In “Source for restore”, select  “From device” and then navigate to your database backup file from which you want to restore database.
    mssqlrestpre2 

    Also click on “Options” on left side of the window (Select a page). Then check “Overwrite the existing database”. Make sure that you pointed to right database files (Restore the database files as -> Restore As field). When done, click OK. Restore process will start.
    mssqlrestpre3 

  4. Your database should now be restored from your old database backup file to your newly created database new_database.
    mssqlrestpre4

© 2020 geegkytuts.net
Hosted by Hosterdam


About author