glFusion Wiki

Site Tools


glfusion:siteadministration:dbadmin

Database Administration

The Database Administration allows you to Backup Database, Optimize Tables, and Convert MyISAM tables to InnoDB.

Backup Database

Backup the contents of your glFusion site. This allows you to easily recover your site's database in the event there is a database failure or if you wish to move your site to a new hosting provider.

Selecting Backup Database will initiate an interactive backup of all your database tables. The backup SQL file will be placed in the private/backups directory.

The backup utility will backup all tables in the database that start with the prefix configured in db-config.php.

Optimize Tables

Optimizing the database tables reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table.

Optimizing can improve performance of the database, which can improve performance of your site. But, optimizing generally does not provide a significant performance improvement.

You do not need to optimize more than once a month.

Optimization may take some time - please be patient.

Optimization is only available for MyISAM tables, it is not support for InnoDB storage engine tables

For more technical information on optimizing tables, please see the MySQL Manual - Optimize Table documentation.

Convert to InnoDB

Determining the best MySQL storage engine is not always a clear choice. MyISAM tables are very good for sites with frequent reads and fewer inserts or updates. InnoDB tends to be better for larger sites that have more frequent updates to the database.

Which is best for glFusion? The answer is based on your particular site. glFusion does both reads and writes to various tables for each and every page loaded. Generally, there are many more reads than there are writes. If you have a high volume site, it may benefit from using InnoDB tables. Otherwise, MyISAM is probably the best choice.

You have the ability to convert your tables over to InnoDB.

Clicking on Convert, InnoDB tables can provide better performance on large databases. Please read up on the benefits and drawbacks of InnoDB tables before you perform a conversion.

Note: Conversion may take some time - please be patient.

Convert to MyISAM

If you have converted your database to InnoDB and want to convert back to MyISAM, select this option.

Note: Conversion may take some time - please be patient.

Upgrade to UTF8MB4

If your site is currently configured to use the UTF-8 character set (which is the default for all new glFusion installations) and your host environment supports using UTF-8 4 byte multibyte storage, you can upgrade your database and tables to use UTF8MB4.

What is UTF8MB4 and Why Do I Care?

glFusion uses the MySQL database to store all the content - if your site is configured to use UTF-8 and your database is also configured to use a UTF-8 collation, such as utf8_general_ci, this means you can store 'multibyte' characters without issue. A multibyte character would include many characters used in languages other than english. Such as the ¥ Yen sign.

Recent versions of MySQL (v5.5.3+) and recent versions of MariaDB (5.5+), which is a drop-in replacement for MySQL, support 4 byte multibyte characters using a collation of utf8mb4_general_ci. If you database server supports this newer collation, it is recommended that you upgrade your database to take advantage of this feature.

A good example of a UTF8MB4 type character are emoji's used on modern phones and tablets, such as 😁. These characters require 4 bytes of storage to be saved in the database. If your site is not using UTF8MB4 - you cannot store these types of items in your database.

glFusion is smart enough to know if you can support UTF8MB4 and will automatically replace any 4 byte characters with a ? (question mark) before saving, otherwise an error would be generated.

glFusion also has the ability to easily convert your existing UTF-8 database to UT8MB4 if your environment supports it. Under Database Administration, you should see an option to Upgrade to UTF8MB4.

Upgrading to UTF8MB4

If your site supports it - glFusion can easily convert your database and tables to UTF8MB4. The following requirements must be met:

  • MySQL v5.5.3 or newer
  • PHP compiled with MySQLND v5.0.9 or newer OR
  • PHP compiled with libmysqlclient v5.5.3 or newer
  • Current database / tables using UTF-8 collation

glFusion will check each of these items and determine whether you can run the UTF8MB4 upgrade scripts. If the menu option appears, your site meets the minimum requirements.

The UTF8MB4 conversion will walk through all the glFusion tables in your database, converting each table and each column in the table to the UTF8MB4 collation.

You must be running glFusion v1.6.3 or newer and ensure all your plugins are up-to-date with the latest releases. Due to the additional storage requirements for UTF8MB4, several indexes with many plugins have had to be adjusted to account for this new requirements.

Please note, upgrading your database and tables to UTF8MB4 can take a long time - the process is fully Ajax driven, so there is no fear of timeouts, but it does take a significant amount of time to process each table since each column has to be converted.

If there is an error converting a table column or a table, the table and the database will not be converted to UTF8MB4. Once the conversion is complete, it will report any errors. Once you resolve the errors (generally by upgrading the plugin or code), run the conversion again to complete the full database migration to UTF8MB4.

Configure

You can customize what database tables are backed up, whether to just backup the table structure, and how many backups you wish to keep online.

Tables to Backup
Here you can customize which tables are excluded from the backup. By default, glFusion will backup all tables. Some tables can be very large and may not need to be backed up - for example, if you have the GUS plugin installed, you may want to exclude the GUS tables.
Only Backup Table Structure
Generally, you want to backup both the table structure and the data - but there could be a case where you want to move your database to another system, for example a test site, and only want the table structure without the data. Generally it is best to leave this option unchecked and backup both the structure and the data.
Number of Backup File to Keep
Each time you back up your database, glFusion will check this setting and delete any older backup files. For example, if you set this to 5, the 6th time you backup, the oldest backup will be deleted so you only have 5 backup files online.

Set this to 0 (zero) to prevent glFusion from deleting any older backup files.
Use Gzip if Available
If this is checked - glFusion will try to compress the backup file online.
2017/04/12 21:09
glfusion/siteadministration/dbadmin.txt · Last modified: 2017/04/12 21:11 (external edit)

Page Tools