All Categories
Database Backup
FAQ

FAQ

FAQ on MySQL Backup

How to exclude tables from a MySQL database backup?

In order to exclude certain tables from a MySQL database backup, simply go to the Advanced section on your Backup page and fill in the "Custom Dump Flags" field as described below.

--ignore-table=database_name.table_one --ignore-table=database_name.table_two
 

What are the default flags you use while backing up a MySQL database?

We rely on mysqldump with these flags:

--single-transaction --quick

This will not lock InnoDB tables and will use the least RAM possible to complete the dump in a consistent state.

However, for MyISAM tables (usually not the case since InnoDB is the default) tables will be locked.

 

How to dump stored procedures / functions on MySQL

Stored procedures/functions are not dumped by default (unless you choose the back up all databases option).

To dump stored procedures/functions on a single MySQL database, you need a special dump flag --routines to your backup.

  1. Go to the backup page
  1. Click on Advanced
  1. Under the Custom Dump Flags section (only visible if your backup type supports it)
  1. Enter --routines
  1. Click Save
 

Errors

Illegal mix of collations error in MySQL

If you run into the following MySQL issue (note that the actual collations could be different than the ones here)

Illegal mix of collations (utf8mb4_general_ci,COERCIBLE), (utf8mb4_unicode_ci,COERCIBLE), (utf8mb4_unicode_ci,COERCIBLE) for operation 'replace' (1270)

Then it is very likely that it is a result of mixing collations in your MySQL database, thus, comparing strings of different collations or selecting combined data fails.

In the example above, the two different collations are utf8mb4_unicode_ci and utf8mb4_unicode_ci - but it could also be utf8_unicode_ci and utf8_general_ci, etc...

To fix this, we need to ensure that both collation match. One way we can do this is by changing the collation of one column to match the other.

Find the columns with inappropriate collation:

SHOW CREATE TABLE <table_name>;

Afterwards, you need to change the collation of the table to match that of the other table.

You can change the collation on each column:

ALTER TABLE <table_name> CHANGE <column_name> <data_type> CHARACTER SET <charset_name> COLLATE <collation_name>;

If you want to make a collation change table-wide:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET <charset_name> COLLATE <collation_name>;

If you want to make a collation change database-wide:

ALTER DATABASE <database_name> CHARACTER SET <charset_name> COLLATE <collation_name>;
ERROR: 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
MySQL 8, caching_sha2_password, connection error

If you keep getting the following error from MySQL when trying to connect. Here are a few things to check:

  • Check that you use the hostname localhost and not 127.0.0.1 (in both cases, the error message will return localhost so this could be tricky)
  • Check that your MySQL user is not using the auth_socket authentication method. If this is the case, it means that your MySQL does not in fact use a password to authenticate

To check the authentication method of each user, run the following in MySQL:

SELECT user,authentication_string,plugin,host FROM mysql.user;
Did this answer your question?
😞
😐
🤩

Last updated on August 4, 2021

How to create a Passwordless MySQL Backup Hot Backups in MySQL Using Percona XtraBackup