FAQ - MySQL iDataAgent
- What are the storage engines supported by MySQL agent?
- Can we protect data on non-global zones if the File System iDataAgent is installed only on the global zone?
- How do we protect MySQL data on a non-global zone?
- How do we back up MySQL databases?
- Can I run multiple transaction log backups in parallel?
- How do I attain consistent backup of databases that have MYISAM or mixed MYISAM InnoDB tables?
- On which operating systems do we support cluster configuration for MySQL iDataAgent?
- Do we support NDB cluster configuration for MySQL iDataAgent?
- What is a staging location and how is it useful?
- Is GTID-based replication running on MySQL Server 5.6.x and later versions supported for SnapProtect backup and restore operations?
- What are the syntaxes to insert statement in case of row-based logging, statement-based logging, and mixed logging?
Following is the list of storage engines that are supported by MySQL agent:
- Infobright is supported only for block-level backups and SnapProtect backups.
- We recommend you to run only data backups on Infobright storage engines.
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.
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.
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.
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.
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 used by default with Mysqldump command by the software. --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.
- Now --single-transaction and --lock-all-tables are mutually exclusive, they cannot be used together. Hence we skip --single-transaction using “--skip-single-transaction” and then perform --lock-all-tables.
- Commands passed in command line will take precedence over the default options.
- You can use the --skip-single-transaction --lock-all-tables options in the sAdvancedBackupOptions setting under Options that can be supplied to the mysqldump utility to get a consistent backup.
We support MySQL cluster configuration both on Linux and Windows.
No, we do not support NDB cluster configuration for MySQL iDataAgent.
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.
Is GTID-based replication running on MySQL Server 5.6.x and later versions supported for SnapProtect backup and restore operations?
No, GTID-based replication running on MySQL Server 5.6.x and later versions is not supported for SnapProtect backup and restore operations.
However, as a workaround, you can perform SnapProtect backup and restore operations on MySQL Server running GTID-based replication by creating the additional setting sAdvancedBackupOptions and setting its value as --set-gtid-purged=OFF. This disables the GTID values from becoming a part of dump.
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')
insert into prova.tb1 values ('remote')
For statement-based logging and mixed logging, use the below syntax to insert statement in a log file:
insert into table_name values ('value’)
insert into tb1 values ('remote’)