How to migrate database from MySQL to MariaDB

Posted on In Views: Word count in article: 2.6k Reading time ≈ 2 mins.

Today, due to I am close to graduation, I need to migrate services which run on the server in my dormitory to cloud. In this progress, I learnt how to migrate database from MySQL to MariaDB. Now let me teach you how to do this.

How to transport databse

Basic assumption:

1
2
3
4

Old MySQL database called MyDB owned by MyDB_user is on server A

New MariaDB database is on server B

  1. Log into server A.

    1
    2
    3
    4
    mysqldump -u MyDB_user -p MyDB > ~/MyDB.sql # export your database
    cp ~/MyDB.sql ~/MyDB.sql.bak # backup in case of incident
    sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_520_ci/g' ~/MyDB.sql # replace all occurrences of utf8mb4_0900_ai_ci with utf8mb4_unicode_520_ci in my SQL dump file as MariaDB only support the later one.
    scp ~/MyDB.sql root@serverB:~/ # transport database file to server B, your can also use other methods to do this

  2. Log into server B.

    1
    2
    3
    4
    apt update
    apt upgrade
    apt install mariadb-server # install mariadb
    mysql
    1
    2
    3
    4
    5
    CREATE DATABASE MyDB;
    CREATE USER 'MyDB_user'@'%' IDENTIFIED BY 'your_password';
    GRANT ALL PRIVILEGES ON MyDB.* TO 'MyDB_user'@'%';
    FLUSH PRIVILEGES;
    exit;
    1
    mysql MyDB < ~/WMChatGPT.sql # import database from file

How to enable MariaDB public access.

Log into server B.

1
vim /etc/mysql/mariadb.conf.d/50-server.cnf
1
2
3
4
5
6
#bind both ipv4 and ipv6
bind-address            = *
#bind ipv4 only
#bind-address            = 0.0.0.0
#bind ipv6 only
#bind-address            = ::
1
2
systemctl restart mariadb
netstat -ant | grep 3306 # check if edits take effects

Reference

  1. https://estuary.dev/how-to-migrate-a-mysql-database/#:~:text=the%20MySQL%20command.-,For%20all%20databases%3A,-mysql%20%2Du%20%5Buser
  2. https://dev.mysql.com/doc/refman/8.0/en/mysqldump-copying-to-other-server.html#:~:text=9.4.5.2-,Copy%20a%20Database%20from%20one%20Server%20to%20Another,-On%20Server%201
  3. https://dba.stackexchange.com/questions/248904/mysql-to-mariadb-unknown-collation-utf8mb4-0900-ai-ci#:~:text=I%27ve%20solved%20my%20issue%20by%20replacing%20all%20occurrences%20of%20utf8mb4_0900_ai_ci%20with%20utf8mb4_unicode_520_ci%20in%20my%20SQL%20dump%20file.
  4. https://stackoverflow.com/questions/48123194/cant-connect-to-mariadb-on-localhost-using-dbeaver/48158693#48158693:~:text=rebooted%20the%20machine.-,I%20changed%20the%20line,-%3A
  5. https://serverfault.com/questions/808977/mariadb-refuses-remote-connections#:~:text=First%20thing%20is%20to%20allow%20non%2Dlocal%20host%20connections.%20Edit%20/etc/mysql/mariadb.conf.d/50%2Dserver.cnf%20an%20check%20the%20bind%2Daddress%3A
  6. https://webdock.io/en/docs/how-guides/database-guides/how-enable-remote-access-your-mariadbmysql-database#:~:text=host%20IPv4%20interfaces.-,bind%2Daddress%20%3D%200.0.0.0,-Save%20and%20close
  7. https://dev.mysql.com/doc/refman/8.0/en/ipv6-remote-connections.html#:~:text=%5Bmysqld%5D-,bind_address