{"id":230,"date":"2025-06-09T17:36:19","date_gmt":"2025-06-09T15:36:19","guid":{"rendered":"https:\/\/kurt.www.pinboard.jp\/blog\/?p=230"},"modified":"2025-06-12T21:37:51","modified_gmt":"2025-06-12T19:37:51","slug":"mysql-mariadb-database-move","status":"publish","type":"post","link":"http:\/\/kurt.www.pinboard.jp\/blog\/kurt\/230\/","title":{"rendered":"MySQL\/MariaDB Database Move"},"content":{"rendered":"\n

I have a strictly internal database server running MariaDB v10.5.20 on Fedora 38. This is an old installation and has not been updated in a long time. Both, the DB and Fedora have moved a few versions up.<\/p>\n\n\n\n

So finally I’m moving to MariaDB v10.11.11 on Fedora 42. Since I have daily dumps of all databases created with<\/p>\n\n\n

\/usr\/bin\/mysqldump -A -Y \\\n    --add-drop-database --add-drop-table --add-drop-trigger \\\n    --comments --flush-logs --flush-privileges --lock-all-tables \\\n    --routines --events --dump-date \n        >\/var\/lib\/mysqldump\/$(date +%a)<\/pre>\n\n\n
that should be a piece of cake, or so I thought. During the restore of the databases on the new server, it complains that the table innodb_index_stats<\/code> does not exist. Bah! I’m by no means a database specialist, whenever I have to do something with databases, I’m utterly lost without google. So a quick search brings me to a page which details how to do a full backup and restore with MariaDB<\/a>. Let’s try this backup procedure on the old server:<\/p>\n\n\n
mariadb-backup --backup \\\n   --target-dir=<backup_directory><\/span><\/em> \\\n   --user=<mariadb-backup><\/span><\/em> --password=<mypassword><\/span><\/em><\/pre>\n\n\n
After copying the whole <destination directory> to the new server, a few steps are required there. First of all a preparation step.<\/p>\n\n\n
mariadb-backup --prepare \\\n   --target-dir=<copied_backup_directory><\/span><\/em><\/pre>\n\n\n
An error again (since I didn’t read the fine print). mariadb-backup<\/code> complains that the backup has been made by an older version of mariadb-backup<\/code> and this newer version can not process the files.<\/p>\n\n\n\n
Turns out, I need to copy and use the mariadb-backup<\/code> from the old server for this step. Crossing my fingers that this works and will not complain about things like missing libraries etc. I try this step with the old version of mariadb-backup<\/code> and, yeah, this works. The remaining steps can be performed with mariadb-backup<\/code> from the new server.<\/p>\n\n\n\n
    \n
  • stop MariaDB<\/li>\n\n\n\n
  • remove everything in the data directory of MariaDB (the place where the database files are kept; in my case \/var\/lib\/mysql\/<\/code>)<\/li>\n\n\n\n
  • the actual restore needs to be run 
    mariadb-backup --copy-back --target-dir=<copied_backup_directory><\/span><\/em><\/pre><\/li>\n\n\n\n
  • unless you ran the last command as the mysql user, permissions on everything in the data directory need to be fixed\n
    chown -R mysql:mysql \/var\/lib\/mysql\/<\/pre>\n<\/li>\n\n\n\n
  • now everything is restored and MariaDB can be started on the new server<\/li>\n<\/ul>\n\n\n\n
    Out of this experience I have added another weekly mysql cronjob which creates this type of backup and saves the required version of mariadb-backup<\/code> together with the backup<\/strong>. My additional weekly cronjob looks like this:<\/p>\n\n\n
    \/usr\/bin\/mariadb-backup --backup \\
        --target-dir \/var\/lib\/mysqldump\/full_backup_$(date +%Y-%m-%d)\/ \\
        --user=<dump_user><\/span><\/em> --password=<dump_user_password><\/span><\/em> \\
            2>>\/var\/log\/mysqlfull_backup.log >&2
    if [ \"${?}\" -ne \"0\" ]; then
        echo \"error while running mariadb-backup\"
    else
        cp -p \/usr\/bin\/mariadb-backup \\
              \/var\/lib\/mysqldump\/full_backup_$(date +%Y-%m-%d)
        find \/var\/lib\/mysqldump\/ -type d \\
                                 -name full_backup_\\* \\
                                 -mtime +1 | xargs -r rm -rf
    fi<\/pre>\n\n\n
    However, not all is good yet, unfortunately. My regular daily backups using mysqldump<\/code> fail with<\/p>\n\n\n
    <\/div>\n\n\n
    Couldn't execute 'show events': Cannot proceed, because event scheduler is disabled (1577)<\/pre>\n\n\n\n
    Even though I found instructions on how to enable the scheduler, that did not help in any way. Turns out, after the update to the new mariadb version and the load of the data, I still have to tell mariadb to update the data to the new version with<\/p>\n\n\n
    mysql_upgrade --verbose<\/pre>\n
    Finally everything working fine now?<\/p>","protected":false},"excerpt":{"rendered":"
    I have a strictly internal database server running MariaDB v10.5.20 on Fedora 38. This is an old installation and has not been updated in a long time. Both, the DB and Fedora have moved a few versions up. So finally I’m moving to MariaDB v10.11.11 on Fedora 42. Since I have daily dumps of all […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[29],"tags":[37,39],"_links":{"self":[{"href":"http:\/\/kurt.www.pinboard.jp\/blog\/wp-json\/wp\/v2\/posts\/230"}],"collection":[{"href":"http:\/\/kurt.www.pinboard.jp\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/kurt.www.pinboard.jp\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/kurt.www.pinboard.jp\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/kurt.www.pinboard.jp\/blog\/wp-json\/wp\/v2\/comments?post=230"}],"version-history":[{"count":15,"href":"http:\/\/kurt.www.pinboard.jp\/blog\/wp-json\/wp\/v2\/posts\/230\/revisions"}],"predecessor-version":[{"id":248,"href":"http:\/\/kurt.www.pinboard.jp\/blog\/wp-json\/wp\/v2\/posts\/230\/revisions\/248"}],"wp:attachment":[{"href":"http:\/\/kurt.www.pinboard.jp\/blog\/wp-json\/wp\/v2\/media?parent=230"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/kurt.www.pinboard.jp\/blog\/wp-json\/wp\/v2\/categories?post=230"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/kurt.www.pinboard.jp\/blog\/wp-json\/wp\/v2\/tags?post=230"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}