Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: Mysql problem after upgrade from 18.04 to 20.04.1

  1. #11
    Join Date
    Jan 2008
    Location
    Netherlands
    Beans
    296
    Distro
    Ubuntu 11.04 Natty Narwhal

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    hmm. I did some experiments trying to migrate from Mysql 8.0 to mariadb 10.3, 10.4 or 10.5.
    Without success.
    I've found this post on cPanel Forum.
    I do not use cPanel, but this article suggestes that migrating is possible.

    with this script:
    Code:
    #!/bin/bash
    mysql -ANe "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','information_schema','performance_schema', 'sys')" > databases.txt
    
    DBLIST=""
    for DB in `cat databases.txt` ; do DBLIST="${DBLIST} ${DB}" ; done
    
    MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
    mysqldump ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > all-dbs.sql
    I am able to create a database backup.
    I created a dump of the user table of the mysql database as an user.sql file.
    Is there a way to restore my different databases with these two files?

  2. #12
    Join Date
    Sep 2011
    Location
    Behind you!
    Beans
    1,408
    Distro
    Ubuntu 18.04 Bionic Beaver

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    The 1st line gets a list of all databases that are NOT system databases. Essentially, it issues the "show databases" command but strips out the system databases so you just have a list of user databases.
    The "for" loop cleans up the output and the mysqldump creates a single .sql file of all your user databases.

    You can test this out using a virtual machine on your PC (e.g. VirtualBox) to install MariaDB and import the "all-dbs.sql" file and then the "user.sql" file.

    What seems to be the issue with the drop-in update has to do with your settings...which seem to have also caused issue with MySQL.

    I would uninstall everything mysql, then rename/move the config files and the database files to a backup location so they are NOT found when you install MariaDB.

    After installing MariaDB and verify there are no residual issues from the prior install.

    Start and stop the database service:
    Code:
    sudo systemctl stop mysql
    sudo systemctl start mysql
    Check the logs to ensure nothing is wrong:
    Code:
    tail -n50 /var/log/mysql/mariadb.err
    Run some basic queries such as:
    Code:
    mysql -e "SHOW DATABASES;"
    mysql -e "SELECT user,host FROM mysql.user;"
    Once you are satisfied that MariaDB is working fine, then import the databases and users. Here is how I import large .sql files and avoid creating an unnecessary log:

    Code:
    mysql
    SET sql_log_bin = 0;
    source /tmp/all-dbs.sql
    source /tmp/user.sql
    exit
    You have me curious enough though that I will probably install MySQL 8, create a sample database and user and see if uninstalling MySQL and then MariaDB 10.5.5 will work as a drop-in replacement. If not, I don't want to get caught recommending something that is no longer true.

    LHammonds

  3. #13
    Join Date
    Jan 2008
    Location
    Netherlands
    Beans
    296
    Distro
    Ubuntu 11.04 Natty Narwhal

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    Thank you LHammonds,
    This time I test with an new virtual server but same Ubuntu 20.04.
    Mariadb 10.3
    As soon as I import the both databases and restart the mysql server I get an:
    Code:
    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
    (I had to switch to the mysql database to import the user table. Is it not?)
    This is same kind of error I get about /var/run/mysqld/mysqld.sock as on the VPS.
    Suggestions?

    Edit: When I update Mariadb to 10.5 I get the mysql database up and running!
    I will try this later on the VPS!
    Last edited by ahbart; 3 Weeks Ago at 09:13 PM.

  4. #14
    Join Date
    Sep 2011
    Location
    Behind you!
    Beans
    1,408
    Distro
    Ubuntu 18.04 Bionic Beaver

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    MariaDB 10.5 is the current stable version for Ubuntu 20.04 and what I'd recommend going using. I happen to be installing MySQL 8.0 right now to test the drop-in ability.

    NOTE: The 1st 14 lines in the user.sql should not be needed for importing into the MariaDB database (default install of MySQL 8.0). Those are system accounts which won't be needed on an existing/working server so be sure to remove those before import. Line 15 "should" be the 1st user account you added but make sure.
    Last edited by LHammonds; 3 Weeks Ago at 10:40 PM.

  5. #15
    Join Date
    Sep 2011
    Location
    Behind you!
    Beans
    1,408
    Distro
    Ubuntu 18.04 Bionic Beaver

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    TEST #1 - Install MySQL 8, create a database and user. Export to .sql. Then remove MySQL 8, install MariaDB 10.5, import the .sql to restore the database and user.
    TEST #1 - Success....see steps performed below:

    Code:
    administrator@ubuntu:~$ sudo su
    root@ubuntu:/# apt install mysql-server mysql-client mysql-common
    root@ubuntu:/# mysql_secure_installation
    root@ubuntu:/# mysql
    SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    SELECT user,host FROM mysql.user;
    +------------------+-----------+
    | user             | host      |
    +------------------+-----------+
    | debian-sys-maint | localhost |
    | mysql.infoschema | localhost |
    | mysql.session    | localhost |
    | mysql.sys        | localhost |
    | root             | localhost |
    +------------------+-----------+
    CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_bin;
    CREATE USER 'u_test'@'localhost' IDENTIFIED BY 'abcd1234';
    GRANT SELECT,INSERT,UPDATE,DELETE ON test.* to 'u_test'@'localhost';
    SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | test               |
    +--------------------+
    SELECT user,host FROM mysql.user;
    +------------------+-----------+
    | user             | host      |
    +------------------+-----------+
    | debian-sys-maint | localhost |
    | mysql.infoschema | localhost |
    | mysql.session    | localhost |
    | mysql.sys        | localhost |
    | root             | localhost |
    | u_test           | localhost |
    +------------------+-----------+
    exit
     root@ubuntu:/# mysqldump --databases test > /tmp/all-dbs.sql
    root@ubuntu:/# mysql --skip-column-names --no-auto-rehash --silent --execute="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | sort | mysql --skip-column-names --no-auto-rehash | sed 's/$/;/g' > /tmp/user.sql
    vi /tmp/user.sql                     <--- NOTE: I cleaned up the output by removing the system accounts which are not needed in this case.
    root@ubuntu:/#systemctl stop mysql
    root@ubuntu:/# apt purge mysql-server mysql-client mysql-common
    root@ubuntu:/# apt autoremove
    root@ubuntu:/# rm -rf /etc/mysql
    root@ubuntu:/# mv /var/lib/mysql /tmp/mysql.old
    root@ubuntu:/# apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
    root@ubuntu:/# add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.5/ubuntu focal main'
    root@ubuntu:/# apt update
    root@ubuntu:/# apt upgrade
    root@ubuntu:/# apt install mariadb-server mariadb-client mariadb-common
    root@ubuntu:/# tail -n50 /var/log/mysql/mariadb.err
    tail: cannot open '/var/log/mysql/mariadb.err' for reading: No such file or directory
    root@ubuntu:/# mysql_secure_installation
    root@ubuntu:/# mysql
    SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    +--------------------+
    SELECT user,host FROM mysql.user;
    +-------------+-----------+
    | User        | Host      |
    +-------------+-----------+
    | mariadb.sys | localhost |
    | mysql       | localhost |
    | root        | localhost |
    +-------------+-----------+
    SET sql_log_bin = 0;
    source /tmp/all-dbs.sql
    source /tmp/user.sql
    SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    SELECT user,host FROM mysql.user;
    +-------------+-----------+
    | User        | Host      |
    +-------------+-----------+
    | mariadb.sys | localhost |
    | mysql       | localhost |
    | root        | localhost |
    | u_test      | localhost |
    +-------------+-----------+
    SHOW GRANTS FOR u_test@localhost;
    +---------------------------------------------------------------------------------------------------------------+
    | Grants for u_test@localhost                                                                                   |
    +---------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO `u_test`@`localhost` IDENTIFIED BY PASSWORD '*4AD47E08DAE2BD4F0977EED5D23DC901359DF617' |
    | GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO `u_test`@`localhost`                                      |
    +---------------------------------------------------------------------------------------------------------------+

  6. #16
    Join Date
    Sep 2011
    Location
    Behind you!
    Beans
    1,408
    Distro
    Ubuntu 18.04 Bionic Beaver

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    TEST #2 - Install MySQL 8, create a database and user. Remove MySQL 8, install MariaDB 10.5 on top of the prior left-over files.

    TEST #2 - Drop-in replacement Failure

    Code:
    administrator@ubuntu:~$ sudo su
    root@ubuntu:/# apt install mysql-server mysql-client mysql-common
    root@ubuntu:/# mysql_secure_installation
    root@ubuntu:/# mysql
    SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    SELECT user,host FROM mysql.user;
    +------------------+-----------+
    | user             | host      |
    +------------------+-----------+
    | debian-sys-maint | localhost |
    | mysql.infoschema | localhost |
    | mysql.session    | localhost |
    | mysql.sys        | localhost |
    | root             | localhost |
    +------------------+-----------+
    CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_bin;
    CREATE USER 'u_test'@'localhost' IDENTIFIED BY 'abcd1234';
    GRANT SELECT,INSERT,UPDATE,DELETE ON test.* to 'u_test'@'localhost';
    SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | test               |
    +--------------------+
    SELECT user,host FROM mysql.user;
    +------------------+-----------+
    | user             | host      |
    +------------------+-----------+
    | debian-sys-maint | localhost |
    | mysql.infoschema | localhost |
    | mysql.session    | localhost |
    | mysql.sys        | localhost |
    | root             | localhost |
    | u_test           | localhost |
    +------------------+-----------+
    exit
    root@ubuntu:/# systemctl stop mysql
    root@ubuntu:/# apt purge mysql-server mysql-client mysql-common
    root@ubuntu:/# apt autoremove
    root@ubuntu:/# apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
    root@ubuntu:/# add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.5/ubuntu focal main'
    root@ubuntu:/# apt update
    root@ubuntu:/# apt upgrade
    root@ubuntu:/# apt install mariadb-server mariadb-client mariadb-common
    
    WARNING about /var/lib/mysql being renamed to /var/lib/mysql-8.0 due to bin-log format.
    Because it moved the old database repository, it therefore did not pull in the old databases.

    You have to change the system from binlog format BEFORE replacing with MariaDB at a minimum...but if you have .sql backup files, you can just use those to restore the databases as noted above.

    So, I now know I cannot say MariaDB is a "drop-in" replacement because of this. I think how roles are defined/used are different as well and may not translate well either. MariaDB came up with roles first and MySQL implemented later but in a different way.

    It was a good exercise. I should have guessed they would not work like when they first diverged. Too many changes on both sides for them to be perfect matches anymore. And just like with upgrading a database of the same engine to a much higher version number, it is still best to export the database and users into .sql format and import them cleanly.

    LHammonds
    Last edited by LHammonds; 3 Weeks Ago at 11:22 PM.

  7. #17
    Join Date
    Jan 2008
    Location
    Netherlands
    Beans
    296
    Distro
    Ubuntu 11.04 Natty Narwhal

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    LHammonds, This is absolutely excellent and very interesting. I will do some testing today! Thank you.

  8. #18
    Join Date
    Jan 2008
    Location
    Netherlands
    Beans
    296
    Distro
    Ubuntu 11.04 Natty Narwhal

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    hmm. No. a lots off error then when I source import te user sql. Created as you did.
    Code:
    ERROR 1064 (42000) at line 1 in file: '/home/bart/backup/database/2020-10-02-user.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ROLE, DROP ROLE ON *.* TO `bart`@`%` WITH GRANT OPTION' at line 1
    ERROR 1064 (42000) at line 2 in file: '/home/bart/backup/database/2020-10-02-user.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CON...' at line 1
    Query OK, 0 rows affected (0.002 sec)
    What is this error and do you know how to fix this? I did not have this on the virtual machine.

    I managed to import the users. It looks like I had to use mysql; But I'm not sure about that.
    The users where imported.
    But all the user passwords where lost. I was able to login to phpmyadmin with root and fix these passwords again.
    Now everything is up and running.

    I notice that there are several system users: mariadb.sys mysql.infoschema mysql.session mysql.sys
    Is there a user between these that is mysql specific what I could drop/delete?
    Is it right that these users have no passwords?
    Last edited by ahbart; 3 Weeks Ago at 03:18 PM.

  9. #19
    Join Date
    Sep 2011
    Location
    Behind you!
    Beans
    1,408
    Distro
    Ubuntu 18.04 Bionic Beaver

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    Quote Originally Posted by ahbart View Post
    Code:
    ERROR 1064 (42000) at line 1 in file: '/home/bart/backup/database/2020-10-02-user.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ROLE, DROP ROLE ON *.* TO `bart`@`%` WITH GRANT OPTION' at line 1
    Notice the word "role" here? As I noted earlier, roles did not exist in mysql when mariadb split in a different direction. MariaDB came up with roles 1st and years later MySQL implemented the "idea" but in a different way...and as such, cannot be directly exported/imported.

    You can analyze how the roles work in MySQL and re-create a similar function in MariaDB though but you need to understand how they work in both engines and the differences between them. Let's compare MariaDB and MySQL Roles.pdf

    Quote Originally Posted by ahbart View Post
    Code:
    ERROR 1064 (42000) at line 2 in file: '/home/bart/backup/database/2020-10-02-user.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CON...' at line 1
    AUDIT_ADMIN?
    BACKUP_ADMIN?
    BINLOG_ADMIN?

    Are those your user accounts or accounts related to the MySQL engine? Maybe those are roles being assigned to a single account. I did not document what users exist in a base install of MySQL while it was up but those do NOT need to be imported into a working MariaDB installation which already has its own and working system accounts. In fact, you don't want to cross those over since it very-likely will break MariaDB if it did.

    I prefix all my users with "u_" and all my roles with "r_" and as such, I never get them mixed up with system accounts which I never mess with.

    Quote Originally Posted by ahbart View Post
    What is this error and do you know how to fix this? I did not have this on the virtual machine.
    That error is talking about syntax problems. Likely related to the role. But without seeing it, I cannot say. Again, a syntax error means there is a feature/command in the SQL that was used during the dump that does not exist during the import (an engine difference). I am also assuming you did not make any edits/changes to the .sql file. If you did, you might have goofed something in there such as a missing semicolon.

    Quote Originally Posted by ahbart View Post
    I managed to import the users. It looks like I had to use mysql; But I'm not sure about that.
    Make sure that you not only imported the users but also their grants/privileges. The ID is no good if they cannot access the data they are supposed to access.

    Code:
    SHOW GRANTS FOR 'user1'@'localhost';
    If they were part of a role on MySQL, look into the permissions granted to those role(s). iirc Role in MySQL were cumulative automatically but in MariaDB only 1 active role is allowed at a time but you can configure inheritance.

    Quote Originally Posted by ahbart View Post
    But all the user passwords where lost.
    This should not have happened...but, I did not verify that field in my test. Should be fairly easy to see if the password hash is included in the user.sql file. At the very least, you can run this command to verify the passwords are identical (even if hashed) on both systems:

    Code:
    SELECT user,host,password FROM mysql.user ORDER BY user ASC;
    LHammonds
    Last edited by LHammonds; 3 Weeks Ago at 04:11 PM.

  10. #20
    Join Date
    Jan 2008
    Location
    Netherlands
    Beans
    296
    Distro
    Ubuntu 11.04 Natty Narwhal

    Re: Mysql problem after upgrade from 18.04 to 20.04.1

    Thank you LHammonds, I do not see this AUDIT_ADMIN, BACKUP_ADMIN, BINLOG_ADMIN in the user table now. So it might is a role. I'm using the database from Nextcloud, Wordpress, (Joomla), PhPMyAdmin, Roundcube and spamassassin. It looks like everything is working again, but I assume that thit is more my luck then wisdom.
    The migration from MySQL 8.0 to Mariadb 10.5 is in my opinion not as smooth as it was from earlier versions of MySQL. But of what I've read on internet, also that is as expected.
    Thanks again!

Page 2 of 3 FirstFirst 123 LastLast

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •