Upgrade Database

Exporting an old MySQL 5.7 database to a new MySQL 8.x or MariaDB server and converting the data to utf8mb4 involves a few steps.

Here's a comprehensive guide:

Warning

Before proceeding, backup your files (uploads) and database.

1. Export the MySQL 5.7 Database

You'll use the mysqldump utility for this. It's crucial to include the --default-character-set option to ensure the data is exported with its current encoding.

mysqldump -u <old_username> -p --databases <database_name> --default-character-set=latin1 > old_database.sql

Replace:

• <old_username>: Your MySQL 5.7 username.

• -p: You'll be prompted for the password.

• <database_name>: The name of the database you want to export.

• --default-character-set=latin1: Crucially, replace latin1 with the actual default character set of your MySQL 5.7 database.
  You can check this by connecting to your MySQL 5.7 server and running:

  SHOW VARIABLES LIKE 'character_set_database';

• old_database.sql: The name of the file where the SQL dump will be saved.

---

Considerations for Export

• Large Databases: For very large databases,
  consider using options like --single-transaction
  (for InnoDB tables to ensure a consistent snapshot) or splitting the dump into smaller files.

• Triggers and Events: If your database has triggers or events, you might want to include --triggers and --events options
  in your mysqldump command. They are included by default in recent versions, but it's good to be aware.

• Stored Procedures and Functions: These are usually included by default. If you want to exclude them, use --skip-routines.

• Binary Data: If your tables contain binary data, ensure your terminal and connection settings can handle it.

---

2. Prepare the SQL Dump for utf8mb4

You'll need to modify the exported SQL file to change the character set and collation definitions.
You can use a text editor or command-line tools for this.

• Replace DEFAULT CHARSET=latin1 (or your old charset) with DEFAULT CHARSET=utf8mb4:
  Search and replace all occurrences of the old default character set with utf8mb4.
  Be careful to only replace it where it defines the table's default.

• Replace DEFAULT COLLATE=latin1_swedish_ci (or your old collation) with DEFAULT COLLATE=utf8mb4_unicode_ci (or a suitable utf8mb4 collation):
  Similarly, search and replace the old default collation with a utf8mb4 collation. utf8mb4_unicode_ci is a good general-purpose choice for Unicode.

• Modify Existing Table and Column Definitions: Look for CREATE TABLE statements and ALTER TABLE statements that explicitly define character sets
  and collations for individual columns. Change these to CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci. For example:

  -- Before
  CREATE TABLE my_table (
      name VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci
  );
  
  -- After
  CREATE TABLE my_table (
      name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
  );
  
  -- Before
  ALTER TABLE another_table MODIFY COLUMN description TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
  
  -- After
  ALTER TABLE another_table MODIFY COLUMN description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

• Add ALTER DATABASE Statement (Optional but Recommended): Add the following lines at the beginning of your SQL file to set the default database character set and collation for the new MySQL 8 server:

  ALTER DATABASE `<database_name>` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  USE `<database_name>`;

Caution

Replace <database_name> with the actual name of your database.

---

3. Import the Modified SQL Dump into MySQL 8

Now, connect to your new MySQL 8 server and import the modified SQL file.

mysql -u <new_username> -p <database_name> < modified_database.sql

Replace:

• <new_username>: Your MySQL 8 username (make sure this user has the necessary privileges to create the database and import data).

• -p: You'll be prompted for the password.

• <database_name>: The name of the database you want to create and import into on the MySQL 8 server.
  Make sure this database exists on the new server. You can create it using:

  CREATE DATABASE `<database_name>` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

• modified_database.sql: The path to the modified SQL dump file.

---

4. Verify the Data and Character Set on MySQL 8

After the import is complete, connect to your MySQL 8 database and verify that the tables and data are correctly encoded in utf8mb4.

• Check Database Character Set and Collation:

  SELECT @@character_set_database, @@collation_database;

  This should return utf8mb4 and the collation you chose (e.g., utf8mb4_unicode_ci).

• Check Table Character Set and Collation:

  SHOW TABLE STATUS LIKE 'your_table_name'\G

  Look for the Collation field. It should be a utf8mb4 collation.

• Check Column Character Set and Collation:

  SHOW FULL COLUMNS FROM your_table_name;

Important

Look at the Collation column for each text-based column. They should also be utf8mb4.

• Inspect the Data: Query some of your text data, especially data that might have contained non-ASCII characters
  or characters that would have been problematic in your old encoding. Ensure they are displayed correctly.

---

Important Considerations for MySQL 8

• Default Character Set: MySQL 8 defaults to utf8mb4, which simplifies things for new databases.
  However, you still need to ensure your imported data is correctly converted.

• Collations: MySQL 8 has a wider range of utf8mb4 collations.
  Choose one that best suits your application's linguistic requirements.
  utf8mb4_unicode_ci is generally a safe and good default for multilingual applications.
  utf8mb4_general_ci is faster for comparisons but has some linguistic inaccuracies.

• sql_mode: MySQL 8 has stricter default sql_mode settings.
  This might cause issues with your old SQL dump if it contains syntax that is no longer allowed by default.
  You might need to adjust the sql_mode on your MySQL 8 server temporarily or modify the SQL dump to comply
  with the stricter rules. Check the MySQL 8 error logs if you encounter import issues.

---

Troubleshooting

• Encoding Issues: If you see garbled characters after importing,
  it likely means there was a mismatch in the character sets assumed during the export or import process.
  Double-check the --default-character-set option in mysqldump and ensure your text editor saved the modified SQL file in UTF-8.

• Import Errors: If you encounter errors during the import, carefully examine the error messages.
  They might indicate syntax issues due to the MySQL version change or problems with the SQL dump itself.

By following these steps carefully, you should be able to successfully export your old MySQL 5.7 database to a new MySQL 8 server
and convert the data to utf8mb4. Remember to test your application thoroughly after the migration to ensure everything works as
expected with the new character set.