Problems with migrating from Mysql 5.5 to mariadb 10.1.14 _mysql

The migration plan is as follows:

1. Back up MySQL 5.5 database and make a backup of the specified library.

2. Restore to MARIADB, and then establish replication.

3. Then you can be willing to switch when and when.

The problems encountered:

Because it is usually innodb, I want to use the Xtrabackup tool to back up. After the backup, it is found that the restore relies on InnoDB table space switching.

1. Table space Switching, you must open innodb_file_per_table. In other words, all tables should have separate files. Some of the tables, though InnoDB, are ibdata below.

Fortunately can apply for Maintenance window, simple and rough reconstruction.

2. From MySQL 5.5 to mariadb, in the import tablespace error occurred, said table structure than matching, and then according to the field found are some date,datetime,timestamp types.

Encounter this problem, still looking for what solution, find a circle also did not find, to the error of the table used mysqldump, and then import, in fact, mysqldump speed is not slow.

3. Since Xtrabackup backup and mysqldump have been around for some time, there will certainly be some data already in existence, so it is certain to report primary key errors when creating a master-slave copy.

Open slave_skip_errors = 1062 directly, the primary key conflict is raised. It seems to be a bit much to fall, so mariadb this version already supports multi-threaded replication.

About the Parameters MARIADB official website can look, open up:

Slave_parallel_mode=optimistic slave_parallel_threads=6 set global innodb_flush_log_at_trx_commit=0;
Can't catch up with this also opened Mariadb [(none)]> show processlist; +----+-------------+-----------+------+---------+------+----------------------------------+------------------+- ---------+
| Id | User | Host | db | Command | Time | State | Info |
Progress | +----+-------------+-----------+------+---------+------+----------------------------------+------------------+- ---------+
| 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 to introduce you to the migration from MySQL 5.5 to mariadb 10.1.14 of the problems encountered, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!