9C1.Mysql Dump/Resotre

Apply to mysql 8.0/5.7, native/cloud database. Support mysqldump backup and mysql restore.

9C1.1.Things when Backup

Use the wings-mysql-dump.sh to backup and only need wings.dba privileges. If you use mysqlpump, note that it is pump and not dump, you need additional privileges.

Do scp, gzip according to the .tip file in the dump file.

9C1.2.Things when Restore

When using mysqldump's backup, you need to adjust the DEFINER trigger sed -E 's/DEFINER=[^*]+//g'

When using mysqlpump's backup, you need to pay attention to the original database name in sql to avoid getting the wrong database. sed -E 's/`OLD_DB`/`NEW_DB`/g'

9C1.3.Data Masking

In general, live data must be desensitized, including but not limited to,

• Password category, such as in dev, simple replacement to avoid leaking or wrong login.
• Privacy category, such as name, ID, address, compliance replacement.
• Communication category, such as email, API address

UPDATE win_user_authn  SET password ='{noop}DevGr8Ag4in' WHERE id >= 1000;

9C1.4.Known Issues

The root user of the cloud database, usually without the SHUTDOWN and SUPER privileges, or even more.

01.ERROR 1227: the SUPER privilege

In Wings, when restoring a trigger with DEFINER= in the statement, it will report insufficient privileges. You must set the privileges of trigger to meet one or all of the following conditions.

• When mysql import, execute the .tip in dump, or manually run sed replacement
• Server-side log_bin_trust_function_creators = 1, asking for SUPER privileges

02.ERROR 1071: max key length is 767

Server-side setting innodb_large_prefix = 1, but some cloud db may disable this option. You can submit parameter changes via console without rebooting.

The following are the charset, char,byte knowledge.

• UTF-8MB4 - 767/4=191
• UTF-8MB4 - 767/3=255
• Expanded from 767 to 3072 bytes if enabled

03.RELOAD privilege(s) for this

Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)

GRANT RELOAD ON *.* TO 'maintain'@'%';
FLUSH PRIVILEGES;

04.server that has GTIDs

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions

[mysqldump]
set-gtid-purged=OFF

05.Unknown table 'column_statistics'

Unknown table 'column_statistics' in information_schema (1109)

[mysqldump]
column-statistics=0

06.Row size too large (> 8126)

Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

[mysqld]
innodb_strict_mode=0

07.Public Key Retrieval is not allowed

Public Key Retrieval is not allowed

add useSSL=false and allowPublicKeyRetrieval=true to

• QueryString of jdbc url, or,
• Driver properties of DBeaver