Problems with migrating from MySQL 5.5 to Mariadb 10.1.14, mariadb10.1.14

Problems with migrating from MySQL 5.5 to Mariadb 10.1.14, mariadb10.1.14

The migration plan is as follows:

1. Back up the database of MySQL 5.5 and back up the specified database.

2. Restore to Mariadb and create a copy.

3. Then you can switch whenever you want.

Problems:

Because it is generally innodb, I want to use the xtrabackup tool for backup. After the backup, it is found that the tablespace that relies on innodb needs to be restored.

1. to switch the tablespace, innodb_file_per_table must be enabled. That is to say, all tables must have independent files. Some tables, although innodb, exist under ibdata.

Fortunately, we can apply for a maintenance window and simply and roughly reconstruct it.

2. An error occurred during import of tablespace from MySQL 5.5 to Mariadb, indicating that the table structure ratio is matched, and some date, datetime, and timestamp types are found based on the field.

If you encounter this problem, you are still looking for a solution. If you have not found the solution, you can use mysqldump for the error table and then import it. In fact, mysqldump is not slow.

3. Because xtrabackup backup and mysqldump have been running for some time, some data will certainly exist. Therefore, a primary key error will be reported when you create a master-slave copy.

Slave_skip_errors = 1062 is enabled directly, and a primary key conflict is mentioned. It seems that there are a lot of drops, so mariadb already supports multi-thread replication.

For details about the parameter mariadb official website, go to the following link:

Slave_parallel_mode = optimistic slave_parallel_threads = 6 set global innodb_flush_log_at_trx_commit = 0; -- MariaDB [(none)]> show processlist; + ---- + ------------- + ----------- + ------ + --------- + ------ + upper + ------------------ + ---------- + | Id | User | Host | db | Command | Time | State | Info | Progress | + ---- + ------------- + ----------- + ------ + --------- + ------ + upper + ------------------ + ---------- + | 5 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 | 23 | system user | NULL | Connect | 1197 | Waiting for master to send event | NULL | 0.000 | 24 | system user | NULL | Connect | 1197 | Waiting for work from SQL thread | NULL | 0.000 | 25 | system user | NULL | Connect | 1197 | Waiting for work from SQL thread | NULL | 0.000 | 26 | system user | NULL | Connect | 1197 | Waiting for work from SQL thread | NULL | 0.000 | 27 | system user | NULL | Connect | 1197 | Waiting for work from SQL thread | NULL | 0.000 | 28 | system user | NULL | Connect | 1197 | Waiting for work from SQL thread | NULL | 0.000 | 29 | system user | NULL | Connect | 1197 | Waiting for work from SQL thread | NULL | 0.000 | 30 | system user | NULL | Connect | 0 | apply log event | NULL | 0.000 | + ---- + ------------- + ----------- + ------ + --------- + ------ + ---------------------------------- + ------------------ + ---------- + 9 rows in set (0.00 sec)

The above is a small series of questions about migrating from MySQL 5.5 to Mariadb 10.1.14. I hope to help you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!