Prepare other self-managed databases for Backup and DR Service

Before you can back up the following self-managed database instances and data, you must add the database server as a host to Backup and DR Service. For this to work, most databases require some settings and other preparation steps. The procedures on this page are for most self-managed databases:

For Review Then use
IBM Db2 Backup and DR for IBM Db2 Db2 database prerequisites
MariaDB Backup and DR for MariaDB MariaDB database prerequisites
MySQL Backup and DR for MySQL MySQL database prerequisites
PostgreSQL Backup and DR for PostgreSQL PostgreSQL database prerequisites
SAP ASE Backup and DR for SAP ASE SAP ASE database prerequisites
SAP IQ Backup and DR for SAP IQ SAP IQ database prerequisites
SAP MaxDB Backup and DR for SAP MaxDB SAP MaxDB database prerequisites
SAP HANA Backup and DR for SAP HANA Prepare SAP HANA databases for backup
Microsoft SQL Server Backup and DR for SQL Server Prepare SQL server databases for Backup and DR Service
Oracle Backup and DR for Oracle Prerequisites for backing up an Oracle database and its following pages

For all other Backup and DR supported databases, follow the steps below.

Db2 database prerequisites

  • Each database must be using Automatic storage mode, or else only instances are discovered.

  • Each database must be in archive log mode, and LOGARCHMETH1 must be set to local disk. To learn if the database is in circular mode or archive log mode, run the command db2 get db cfg for <database name>| grep LOGARCHMETH1

  • If the database is in circular mode, then change the logging to archive mode before continuing. To set the LOGARCHMETH1 parameter, use the following:
    db2 update db cfg for <dbname> LOGARCHMETH1 using 'DISK:<location>'

  • For HADR-standby database the reads on standby option must be enabled, that is the DB2_HADR_ROS parameter must be set to ON.

MariaDB database prerequisites

  • The MariaDB database must be residing under LVM and it must not be the boot volume. Run mysql -e "select @@datadir" to get the database data path.

  • The LVM volume from which the MariaDB volumes are provisioned should have at least 20% free space.

  • Install the Backup and DR agent on the MariaDB server

  • Create a backup user with the privileges Reload, Select, Lock tables, and Super or Replication client. A backup username and password must be configured with host configuration.

    To create the backup user, complete the following:

    create user actuser identified by 'actpasswd';
    GRANT SELECT on *.* TO actuser;
    GRANT RELOAD on *.* TO actuser;
    GRANT LOCK TABLES on *.* TO actuser;
    GRANT SUPER on *.* TO actuser;
    GRANT REPLICATION CLIENT on *.* to actuser;
    SHOW GRANTS FOR CURRENT_USER;
    
  • MariaDB binary logging log_bin must be on to take log backups. To configure the binary log option, shut down the MariaDB server and edit the config file my.cnf or my.ini. Within the [mysqld] section of the configuration file, add the log-bin option.

    [mysqld]
    log_bin = /log1/mysql3306/mysql3306-bin.0000
    
  • The mysql user must be set to /bin/bash in the /etc/passwd file

MySQL database prerequisites

  • The MySQL database must be residing under LVM and it must not be the boot volume. Run mysql -e "select @@datadir" to get the database data path.

  • For best results, the LVM volume from which the MySQL volumes are provisioned should have at least 20% free space.

  • Install the Backup and DR agent on the MySQL server host

  • Make sure that the MySQL instance is up and running

  • The owner of MySQL database—OS user—must be enabled to login, for example, database OS user should have a working shell like /bin/bash.

    Command to enable OS user: usermod -s /bin/bash mysql

  • MySQL instance should have a database backup user, which should be able to connect from localhost. Example: create user actuser'@'localhost identified by '';

  • Privilege required for database backup user in case of volume-level snapshot backup and recovery:

    GRANT SELECT on *.* TO actuser;
    GRANT RELOAD on *.* TO actuser;
    GRANT LOCK TABLES on *.* TO actuser;
    GRANT SUPER on *.* TO actuser;
    GRANT REPLICATION CLIENT on *.* to actuser;
    SHOW GRANTS FOR CURRENT_USER;
    
  • The following privileges are required for database backup user in case of Full+Incremental—file-based traditional—backup and recovery:

    GRANT ALL ON *.* TO 'actuser'@'localhost';
    SHOW GRANTS FOR CURRENT_USER;
    
  • One user defined database must be present other than system databases—information_schema, mysql, performance_schema, or sys.

  • MySQL binary logging—log_bin—must be on to take log backup. To configure the binary log option, shut down the MySQL server and edit the config file—my.cnf or my.ini. Within the [mysqld] section of the configuration file, add the log-bin option.

    [mysqld]
    log_bin = /log1/mysql3306/mysql3306-bin.0000
    

PostgreSQL database prerequisites

This section includes information about the PostgreSQL database prerequisites.

Protect PostgreSQL from the primary node

  1. Create a backup user:

    create user actuser;

  2. Provide privilege for backup:

    alter user actuser with superuser;
    alter user actuser with Replication;
    
  3. PostgreSQL archive mode—archive_mode—must be set to ON to take log backups. To configure the archive log mode option, update the postgresql.conf file in the data directory of the instance and reload the PostgreSQL.

    archive_mode = on
     // Command to reload PostgreSQL
     pg_ctl reload -D <Postgres_datadir_path>
    ``
    

Example: pg_ctl reload -D /pgdata

Protect PostgreSQL from a standby node

  1. Create a backup user:

    create user actuser;

  2. Provide privilege for backup:

    alter user actuser with superuser;
    alter user actuser with Replication;
    
  3. PostgreSQL archive mode—WAL—setup on standby node:

    Create the directory where we want to keep the WALs.
    Example assumes /pglog is the directory. Run the following steps as root user.

    mkdir /pglog
    chown -R postgres:postgres /pglog
    

    Configure the parameters for archiving.
    Example assumes $PGDATA is /pgdata/11/data. Run the below steps as postgres user.
    Add or update the parameters in the file /pgdata/11/data/postgresql.conf

     wal_level = replica
     archive_mode = always
     archive_command = 'test ! -f /pglog/%f && cp %p /pglog/%f'
    

    Restart the PostgreSQL:

     /usr/pgsql-11/bin/pg_ctl stop -D /pgdata/11/data
     /usr/pgsql-11/bin/pg_ctl start -D /pgdata/11/data
    

    Add or update entry in pg_hba.conf on Primary to accept database connection from standby.

    host postgres <db-user> <standby-ip/32> <connection method>
    

    Example: host postgres postgres 10.128.0.29/32 trust

    Test the archive generation (as postgres user):

    ls -l /pglog/
    

    Run this log switch command:

     /usr/pgsql-11/bin/psql -h 10.128.0.28 -p5432 -c "select pg_switch_wal();"
     sleep 5 # log shipping may take some time depending on network
     ls -l /pglog/
    

SAP ASE database prerequisites

Before you begin, on the SAP ASE server complete the following:

  • A backup user account must be created with backup privilege—either oper_role or sa_role. A backup username and password must be configured with host configuration. Create the user with or without a password to run the quiesce command on the source with sa_role/or with quiesce privileges. To create the user with a password, run the following:

    isql -Usa -P<password> -S<SAP ASE_Server_Name>
    1>sp_addlogin actuser, '<password>'
    2>go
    1>grant role sa_role to actuser
    2>go
    

    To configure the user login without a password requirement, set the password to null, then run the following command:

    1>sp_configure "minimum password length", 0
    2>go
    1> sp_modifylogin actuser, "min passwd length",
    "0"
    2> go
    3> exit
    isql -Uactuser -P'<password>' -S<server_name>
    > sp_password '<password>', NULL
    >go
    >sp_configure "minimum password length", 8
    >go
    
  • Install the Backup and DR agent on the SAP ASE server.

  • To enable transaction log backup, Truncate on chkpt false must be set at the database level for each database of the SAP ASE instance.

  • The following is true for volume level backups:

    • The SAP ASE instance must reside under LVM and it must not be the boot volume.

    • The LVM volume from which the SAP ASE volumes are provisioned should have at least 20% free space.

SAP IQ database prerequisites

Before you begin, review the following on the SAP IQ server:

  • SAP IQ PITR logging must be ON to take log backup. To configure PITR log option, you need these SAP IQ API:

    • ALTER DBSPACE IQ_SYSTEM_LOG RENAME /<pitr_log_location>

    • SET OPTION PUBLIC.IQ_POINT_IN_TIME_RECOVERY_LOGGING = ON`

    After enabling PITR logging, a database full backup is needed.

  • The utility database needs to start by specifying the username and password, and dbname should be utility_db. For example: start_iq -n utility_db -su act,passwd

  • The database backup username and password should be the same as the utility_db username and password and it must have DBA privileges. To create the backup user, for example:

    create user actuser identified by '<mypassword>';
    grant dba to actuser;
    
    • Install the Backup and DR agent on the SAP IQ server host.

SAP MaxDB database prerequisites

Before you begin, on the SAP MaxDB server:

  • If there are multiple MaxDB instances running on a server, then the DB username and password must be common for all MaxDB instance running on that server.

  • Autolog backup must be enabled. Use the following command to enable auto log backup:

    dbmcli -d <DBSID> -u <DBM_USERNAME>,<DBM_PASSWD> autolog_on
    <autolog_backup_template_name>
    
  • For enabling the autolog backup, you need an autolog backup template.

To create a backup template, use the following command.

    dbmcli -d <DBSID> -u <DBM_USERNAME>,<DBM_PASSWD> backup_template_create
    <BACKUP_TEMPLATE_NAME> to file  <FULL_PATH_WITH_FILE_NAME> content log
  • Log Overwrite area must be deactivated to enable autolog backup enable.

To deactivate log overwrite, use the following command:

    dbmcli -d <DBSID> -u <DBM_USERNAME>,<DBM_PASSWD> db_execute SET LOG AUTO
    OVERWRITE OFF.
  • Install the Backup and DR agent on the SAP MaxDB server host.

What's next

After the database is prepared for Backup and DR, you can add the host and discover the databases.