FAQ

What are the storage engines supported by MySQL agent?

Following is the list of storage engines that are supported by MySQL agent:

• MyISAM

• InnoDB

• TokuDB

• Infobright

• XtraDB

Can we protect data on non-global zones if the File System iDataAgent is installed only on the global zone?

Yes. We can protect file system data on non-global zones if the File System iDataAgent is installed only on the global zone. However, in order to enable consistent backups of application specific data on the non-global zones, you will need to install the corresponding application specific iDataAgent on the non-global zone.

How do we protect MySQL data on a non-global zone?

In order to enable consistent backups of MySQL data on a non-global zone, you need to install the MySQL iDataAgent on the non-global zone.

How do we back up MySQL databases?

We use mysqldump utility to perform backups on MySQL databases. Various storage engines such as InnoDB and MyISAM in the MySQL server store the tables in different ways.

By default, the mysqldump utility uses the –opt flag for databases to be dumped. The –opt flag uses the –lock tables command that locks tables before dump.

We use –single-transaction as the default option for Mysqldump in our solution as it does not need to -lock-tables. It is useful in getting the consistent backups of tables having transactional storage engines like InnoDB. Mysqldump does not change dynamically, but it discards –lock-tables options (part of –opt by default) when we supply –single-transaction option.

We can also attain the consistent backups of MyISAM or MyISAM and InnoDB mixed tables.

By default, the Mysqldump utility also passes the --routines parameter to automatically include the stored procedures and functions in the backup data. Following is an example of passing the routines parameter:

/server_1/bin/mysqldump --routines --max_allowed_packet=1073741824 -u root --socket=/server_1/data/mysql.sock --databases test

You can exclude the stored procedures from your backups. For more information, see Excluding Stored Procedures from Backup.

How do we restore MySQL databases?

For traditional dump data restore, we set up a pipeline between the MediaAgent and the client. To create a database dump, a stream of data is sent through the pipeline when MySQL command is run on the destination client.

For log restore, we recover the logs through a staging location. For each log file, the SQL statements are extracted with mysqlbinlog command in a file (created in the same staging location), and then it is applied to the MySQL server.

Can I run multiple transaction log backups in parallel?

When multiple transaction log backups run in parallel on two or more subclients, each log file is backed up only once. Only one log backup job can run at a time. If multiple log backup jobs are initiated at the same time, they are run sequentially in a queue.

How do I attain consistent backup of databases that have MyISAM or mixed MyISAM InnoDB tables?

We have a way to accept user options to get consistent backup of databases that have MyISAM or mixed MyISAM InnoDB tables.

• --single-transaction is required to attain consistent backups of InnoDB tables without locking.

• For consistent backups of MyISAM engines or MyISAM and InnoDB mix tables, we should lock tables before taking dump. Perform --lock-tables to get consistent backups of MyISAM engines or MyISAM and InnoDB mix tables.

• Now --single-transaction and --lock-tables are mutually exclusive, they cannot be used together. Hence we just perform --lock-tables.

• Commands passed in command line will take precedence over the default options.

On which operating systems do we support cluster configuration for MySQL iDataAgent?

We support MySQL cluster configuration both on Linux and Windows.

Do we support NDB cluster configuration for MySQL iDataAgent?

No, we do not support NDB cluster configuration for MySQL iDataAgent.

What is a staging location and how is it useful?

Staging location is a temporary destination folder where transaction logs are staged for recovery. It is recommended to have an empty temporary folder for the staging location. After all the logs are staged to the temporary folder, the software applies them one-by-one to the MySQL Server. Ensure that you do not mix up the staging location folder with the MySQL datadir or log data directory or bin directory, etc.

Can I perform backup and restore operations for MySQL server that is enabled with GTID?

Yes, you can perform backup and restore operations on GTID-enabled MySQL servers version 5.6 and later.

How to restore the MySQL databases that were backed up during the traditional backup operation with GTID enabled?

To restore the backed up MySQL databases with GTID enabled, you can run a normal restore operation on the databases.

What are the syntaxes to insert statement in case of row-based logging, statement-based logging, and mixed logging?

For row-based logging, use the below syntax to insert statement in a log file:

 
insert into database_name.table_name values ('value')

For example,

insert into prova.tb1 values ('remote')

For statement-based logging and mixed logging, use the below syntax to insert statement in a log file:

 
use database_name
 insert into table_name values ('value’)

For example,

use prova
 insert into tb1 values ('remote’)

During a MySQL backup operation, while taking dumps of databases, why are the flush table commands executed?

During a backup operation, when the MySQL dump command is executed with parameters master-data and single-transaction, flush tables are implicitly executed. These parameters are used to obtain consistency and are unavoidable.

How to determine which is the active node of MySQL cluster?

You can find the active node in the Client Computer Properties dialog box of a MySQL cluster.

While running a backup operation, in what cases do the backup jobs complete with one or more errors?

Backup jobs from MySQL iDataAgent will be displayed as "Completed w/ one or more errors" in the Job History in the following cases:

• If the subclient contains multiple databases, and some of the database backups encounter errors and other database backups run successfully.

• If the subclient contains multiple databases, and some databases in the subclient are corrupted or removed from the database server.

Do we support backup and restore operations for MySQL servers running in a docker container?

Yes, we support traditional backup and restore operations for MySQL servers running in a docker container. The prerequisite is that while creating an instance, ensure that the MySQL bin path, socket file, log data directory (if binary logging is enabled), and config file in the container should be exposed.

We use mysql, mysqladmin, mysqldump and mysqlbinlog binaries for backup and restore operations. Ensure that all these binaries reside in the MySQL bin path that is used to create an instance. If these binaries do not reside in the MySQL bin path, download an archived MySQL package (having the same version as the MySQL server), extract the bin directory, and then update the MySQL instance with this bin path.

Do we support transportable tablespace restore for MySQL?

Yes, we do support transportable tablespace restore for MySQL. Before starting a block-level table level restore operation, set the value of additional setting sRestoreType as 3 to perform a transportable tablespace restore operation. We support transportable tablespace only for InnoDB tables that are created with the innodb_file_per_table option enabled.

Is Galera cluster supported for MariaDB and MySQL? How do we configure backups for Galera cluster?

Yes, we support Galera cluster for MariaDB and MySQL on Linux. In a Galera cluster, every node is like a master node. You can install MySQL agent on any node. You can then create an instance, and perform MySQL backup operation and restore operation.

Can backing up any one Galera cluster node ensure that all the latest changes in the cluster nodes are included?

Yes, you can back up any one Galera cluster node to include any changes in the nodes, because in Galera cluster, all the nodes are the master nodes.

While running a backup operation in a Galera cluster, why does the MySQL error log file shows that the MySQL node is getting desynchronized and resynchronized?

While running a backup operation, locks are acquired on databases that cause the MySQL node to desynchronize and resynchronize every time the lock is released.

For a block level backup operation, databases are locked only once for a fraction of a second. But, for a traditional backup operation, each database is locked separately while it is getting backed up.

Can I change the case of a MySQL user name in an instance?

No, you cannot change the case of a MySQL user name in an instance. MySQL user name is not case sensitive. If you try to change the case of a MySQL user name, you will not get any error message but still the case will not be changed.

As a workaround, change the user name of the MySQL instance to another user name, ignore the warning that states failed to connect to the MySQL server, and then update the user name back to the correct case.

For example, if you want to update the user root to Root, change the user root to root1, ignore the warning that appears, and then again change the user root1 to Root.

Do we support the restoration of encrypted binary logs?

No, when binary log files have been encrypted, mysqlbinlog cannot read them directly, so they cannot be restored.