Mysql, sqlsever, oracle, redis, mongo, postgres and other database backup and recovery instructions organized_MySQL

The following only summarizes the simplest usage of each database backup and recovery. For more detailed parameters and configurations, please refer to the relevant information

1. mysql:

Parameter description:

Backup: mysqldump –single-transaction -l -u $user -p'$password' -h $targethost -P $port –default-character-set=$charset $dbname $table > $filename

Restore: mysql $dbname -u $user -p'$password' -h $targethost -P $port –default-character-set=$charset <$filename

2. sqlserver:

Note: sqlserver2003 and below only support osql, sqlserver2005 and above support both osql and sqlcmd. The following methods are common to both sqlcmd and osql.

Parameter description:

Full backup: osql -U $user -P “$password” -d master -S $instance -Q “ALTER DATABASE $dbname SET OFFLINE WITH ROLLBACK IMMEDIATE; RESTORE DATABASE $dbname FROM DISK='$filename& #39; WITH REPLACE; ALTER DATABASE $dbname SET ONLINE”

Incremental backup: osql -U $user -P “$password” -d master -S $instance -Q “ALTER DATABASE $dbname SET OFFLINE WITH ROLLBACK IMMEDIATE; RESTORE DATABASE $dbname FROM DISK='$ filename' WITH REPLACE,NORECOVERY; RESTORE DATABASE $dbname FROM DISK='$diff_filename' WITH RECOVERY; ALTER DATABASE $dbname SET ONLINE”

Full recovery: osql -U $user -P “$password” -d master -S $instance -Q “ALTER DATABASE $dbname SET OFFLINE WITH ROLLBACK IMMEDIATE; RESTORE DATABASE $dbname FROM DISK='$filename& #39; WITH REPLACE; ALTER DATABASE $dbname SET ONLINE”

Incremental recovery: osql -U $user -P “$password” -d master -S $instance -Q “ALTER DATABASE $dbname SET OFFLINE WITH ROLLBACK IMMEDIATE; RESTORE DATABASE $dbname FROM DISK='$ filename' WITH REPLACE,NORECOVERY; RESTORE DATABASE $dbname FROM DIST='$diff_filename' WITH RECOVERY; ALTER DATABASE $dbname SET ONLINE”

3. Oracle:

Oracle mainly uses rman for backup and recovery. If you are interested, you can learn more about the specific use of rman

4. redis:

Note: The redis data backup file is stored in the full path file name composed of dir+dbfilename in the configuration file;

Backup: redis-cli -h targethost -p $port -a $password save

Restore: Just place the backed up files in the original directory and restart redis.

5. mongo:

Parameter description:

Backup: mongodump –host $targethost:$port -d $dbname -o $backup_dir -u $user -p $password

Recovery: mongorestore –host $targethost:$port -d $dbname -u $user -p$password -c $table $recovery_dir

6. postgres:

Backup: pg_dump -c -b -h $targethost -p $port -U “$user” $dbname > $filename

Recovery: psql -h $targethost -p $port -U “$user” -d $dbname -f $recovery_file