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.
What are the default flags you use while backing up a MySQL database?
We rely on mysqldump with these flags:
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.
- Go to the backup page
- Click on Advanced
- Under the Custom Dump Flags section (only visible if your backup type supports it)
- Click Save
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 - but it could also be
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
127.0.0.1(in both cases, the error message will return
localhostso this could be tricky)
- Check that your MySQL user is not using the
auth_socketauthentication 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;
Last updated on August 4, 2021