0

there are many "howto" of how to change mariadb to mysql in xampp (HERE, HERE, HERE, HERE, etc, etc.), but they are all for windows OS

Anyone know how to do it, for Ubuntu 20.04, by bash command line? (preferably to mysql 5.7)

wget https://www.apachefriends.org/xampp-files/7.4.12/xampp-linux-x64-7.4.12-0-installer.run
sudo chmod +x xampp-linux-x64-7.4.12-0-installer.run
sudo ./xampp-linux-x64-7.4.12-0-installer.run --mode unattended --launchapps 0
sudo /opt/lampp/manager-linux-x64.run

wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
tar -xzf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
sudo mv -f mysql-5.7.35-linux-glibc2.12-x86_64/* /opt/lampp/mysql

# and after that?

enter image description here

workaround to downgrade:

There is no version of Xampp with MySQL 5.7. According to Apache Friends, since XAMPP 5.5.30 and 5.6.14, XAMPP ships MariaDB instead of MySQL (According to RELEASE NOTES, last Xampp with MySQL was v5.6.12-0 -with MySQL v5.6.25-; then jumped to Xampp v5.6.14 -with MariaDB v10.0.17-)

RELEASE NOTES

[2015-10-19] XAMPP for Linux 64bit 5.6.14
This version of XAMPP includes:
    - Updated Apache to 2.4.17
    - Updated PHP to 5.6.14
    - Added MariaDB 10.0.17
    - Updated phpMyAdmin 4.5.0.2

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

[2015-08-27] XAMPP for Linux 64bit 5.6.12-0
This version of XAMPP includes:
    - Updated Apache to 2.4.12
    - Updated PHP to 5.6.11
    - Updated MySQL to 5.6.25
    - Updated phpMyAdmin 4.4.12
    - Update OpenSSL to 1.0.1p
    - Phanebook issue with SQLite
    - Fix charset admin pages
    - Added OS X HOW-TO guides
    - Enabled SSL support for PostgreSQL PHP extension
    - Added PHP XMLRPC extension
    - Added PHP MSSQL extension
    - Included new dashboard

So i found a package called bitnami lamp, and in previous versions it has one with mysql 5.7 and can downgrade

enter image description here

but still, I would like to know the answer to my question, if such a thing can be done in linux

3
  • You may want to ask on a XAMPP-specific site as the project does a lot of non-standard things with installations, configurations, and the like. If you’re looking for a modern means of running an older version of MySQL, you may find that Docker containers are a flexible way to go 👍🏻
    – matigo
    Commented Oct 9, 2021 at 20:16
  • I can't downgrade because there is no Xampp version with mysql 5.7. In the forum nobody says anything about my question. Windows only. That's why I'm here.
    – acgbox
    Commented Oct 10, 2021 at 1:27
  • Fun. Well, in that case, I would advise learning how Docker containers work, and spinning one up with 5.7. As I said earlier, XAMPP does non-standard things with its version of Apache/MySQL/PHP/PostgreSQL, so swapping out versions is likely to have a lot of gotchas and other painful things that people shouldn't have to struggle with. Docker may be aptly suited for your needs ... though there is a slight learning curve involved 👍🏻
    – matigo
    Commented Oct 10, 2021 at 2:01

1 Answer 1

1

Long story short, I have successfully migrated from MariaDB 10.4.11 to MySQL 5.7.38. The reason for that was to check InnoDB engine of specific version, namely 5.7.38.

Actually, it's fairly easy with bitnami lampstack. Moreover, with bitnami you can easily configure to run both MariaDB and MySQL [1] (simultaneously on different ports or one at a time on the same default port) and to control them via manager (or via systemd services).

My first attempt was to find the closest version of MySQL in bitnami lampstack package changelog and to download this package, yet it's missing on the web (Version above 7.1.33 (MySQL 5.7.28) and below 7.2.24 (switch to MySQL 8)).

My next best bet was to grab any available bitnami package (7.2.26) and just to swap installed MySQL package. (Sorry, no wget here as link is revealed by timer. Please, update if you'll find their archives)

Run installer first to get full list of options (select which modules to install):

./bitnami-lampstack-7.2.26-0-linux-x64-installer.run --help

and to install with overridden settings (i prefer to install it as user, so no sudo here):

./bitnami-lampstack-7.2.26-0-linux-x64-installer.run --prefix /opt/bitnami/lampstack-7.2.26.0 --disable-components varnish,zendframework,symfony,codeigniter,cakephp,smarty,laravel --base_password testpass --apache_server_port 9955 --apache_server_ssl_port 9943  --mysql_port 3309 --mode unattended

At this point you'll get a working LAMP stack with running services on selected ports. Sadly, but bitnami has no flag to disable apps from starting, so we need to do that manually:

$ cd /opt/bitnami/lampstack-7.2.26.0 && ./ctlscript.sh status  && ./ctlscript.sh stop
apache already running
mysql already running
Syntax OK
/opt/bitnami/lampstack-7.2.26.0/apache2/scripts/ctl.sh : httpd stopped
/opt/bitnami/lampstack-7.2.26.0/mysql/scripts/ctl.sh : mysql stopped

Install choosen MySQL version

As you have noticed, then lampstack modules are controlled via ctl scripts in their 'scripts' folder. So the most important part is to have those scripts in custom installment of MySQL.

$ mv mysql mysql_original
$ mkdir mysql && mkdir mysql/{tmp,run,data}
$ cp -r mysql_original/scripts mysql/
$ cp mysql_original/my.cnf mysql ##it should be adapted to your previous configuration, but keep defined directory variables
$ wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
$ tar -xzf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz  -C mysql --strip-components 1 ##untar archive contents into mysql skipping root directory
$ ln -s /opt/bitnami/lampstack-7.2.26.0/mysql/bin/mysqld /opt/bitnami/lampstack-7.2.26.0/mysql/bin/mysqld.bin ##or fix mysql ctl.sh to use mysqld instead of mysqld.bin

Now you have two options: to copy 'data' from old MariaDB installation or to init new DB and then import dump. The first is unreliable, so i prefer the second one.

Option 1 Migrate MariaDB 'data'

NB! Backup 'data' folder and/or create DB dump (by doing that skip option 1 and jump to option 2).

$ cp -R old_data/ /opt/bitnami/lampstack-7.2.26.0/mysql/
$ ./opt/bitnami/lampstack-7.2.26.0/ctlscript.sh start mysql

For sure you'll get this error if you'll follow this option and are downgrading.

 InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.2.44. Please follow the instructions at http://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html

NB! Potentially harmful action A workaround is to delete ib_logfile* files and then start service again.

Then you'll end up with a bunch of errors, yet db will be up and running.

[ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery

and

[ERROR] InnoDB: Page [page id: space=0, page number=734] log sequence number 3261161573 is in the future! Current system log sequence number 3221898782.
[ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.

After that you need to upgrade DB by calling mysql_upgrade. Let's create ctl_upgrade.sh script for that:

$ vi mysql/scripts/ctl_upgrade.sh

#!/bin/sh
MSQLPATH=/opt/bitnami/lampstack-7.2.26.0/mysql
MYSQL_PIDFILE=$MSQLPATH/run/mysqld.pid

MYSQL_START="$MSQLPATH/bin/mysql_upgrade --defaults-file=$MSQLPATH/my.cnf --socket=$MSQLPATH/tmp/mysql.sock --verbose"

$MYSQL_START >$MSQLPATH/data/mysqld-update.log 2>&1

$ ./mysql/scripts/ctl_upgrade.sh
$ ./mysql/scripts/ctl.sh stop && ./mysql/scripts/ctl.sh start

Option 2 Dump and import DB

I won't go in deep here, as the process is fairly straightforward. To dump:

$ vi /opt/bitnami/mysqldump.sh

#!/bin/sh
## Dump DB with no DB name in two rows:
## 1. dump structure with routines and no data
## 2. dump only data in ansi mode
## I avoid dumping with auto creating database to avoid collisions with existing databases on import.
MYSQL_BIN=mysqldump ##note, here i use old mysql binary available globally
PASSWORD=
HOST=$1
USER=root
DATABASE=$2

IGNORED_TABLES_STRING='' #if you want to skip some tables from dumping

now="$(date +'%d_%m_%Y_%H_%M')"
filename="${DATABASE}_db_backup_$now.sql"
backupfolder="./mysqldump"
logfile="$backupfolder/"backup_log_"$(date +'%Y_%m')".txt

echo "Dump structure started at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
    $MYSQL_BIN --host=${HOST} --user=${USER} --password=${PASSWORD} --no-create-db ${DATABASE} --single-transaction --no-data --routines --log-error $logfile > $backupfolder/${DB_FILE} 
echo "Dump structure finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"

echo "Dumping contents"
##note dumped db will be in ansi mode, for better [compatibility][5] 
$MYSQL_BIN --compatible=ansi --host=${HOST} --user=${USER} --password=${PASSWORD} --no-create-db ${DATABASE} --no-create-info --skip-triggers ${IGNORED_TABLES_STRING} --log-error $logfile >> $backupfolder/${DB_FILE} 

$ cd /opt/bitnami && mkdir mysqldump && sh mysqldump.sh localhost db_to_dump
## stop old lampp services
$ ./opt/lampp/ctlscript.sh stop

Import into new DB:

$ vi /opt/bitnami/mysqlrestore.sh

#!/bin/sh
## Note here we use lampstack MySQL binary (not added to PATH)
MYSQL_BIN="/opt/bitnami/lampstack-7.2.26.0/mysql/bin/mysql --socket=/opt/bitnami/lampstack-7.2.26.0/mysql/tmp/mysql.sock"

PASSWORD=
filename="${1}"
USER=root
DATABASE=$2

now="$(date +'%d_%m_%Y_%H_%M')"

echo "Creating DB $DATABASE"
$MYSQL_BIN --user=$USER --password=$PASSWORD -e "CREATE DATABASE $DATABASE;"

MYSQL_BIN -u $USER --password=$PASSWORD $DATABASE < $filename

$ cd /opt/bitnami/lampstack-7.2.26.0/mysql/scripts && cp ctl.sh ctl_init.sh && vi ctl_init.sh
change MYSQL_START to 
MYSQL_START="/opt/bitnami/lampstack-7.2.26.0/mysql/bin/mysqld --socket=/opt/bitnami/lampstack-7.2.26.0/mysql/tmp/mysql.sock  --datadir=/opt/bitnami/lampstack-7.2.26.0/mysql/data --log-error=/opt/bitnami/lampstack-7.2.26.0/mysql/tmp/mysqld_init.log  --pid-file=$MYSQL_PIDFILE --lower-case-table-names=1 --user=mysql --initialize"
 NB! if you need root without password use --initialize-insecure

$ ./ctl_init.sh start
$ ./ctl.sh start
$ cd /opt/bitnami && ./mysqlrestore.sh mysqldump/dumped_file.sql db_new_name

Check logs in 'data' directory. Migration completed.

Optional: Install MariaDB into lampstack manager

I've selected MariaDB with the same InnoDB version.

 $ cd /opt && mkdir mariadb && mkdir mariadb/10.2.44-systemd 
 $ wget https://archive.mariadb.org/mariadb-10.2.44/bintar-linux-systemd-x86_64/mariadb-10.2.44-linux-systemd-x86_64.tar.gz
 $ tar -xzf mariadb-10.2.44-linux-systemd-x86_64.tar.gz -C 10.2.44-systemd --strip-components 1 
 $ cd 10.2.44-systemd && mkdir {tmp,run,data}
 $ cp /opt/bitnami/lampstack-7.2.26.0/mysql/scripts scripts
 $ cp /opt/bitnami/lampstack-7.2.26.0/mysql/my.cnf my.cnf
 $ ln -s /opt/mariadb/10.2.44-systemd /opt/bitnami/lampstack-7.2.26.0/mariadb

 At this point bitnami is aware that mariab module is installed, yet we need to update paths in copied ctl scripts to point to mariadb.

 $ cd scripts
 $ grep -rlZ "/opt/bitnami/lampstack-7.2.26.0/mysql" . ../my.cnf | xargs -0 sed -i 's/\/opt\/bitnami\/lampstack-7.2.26.0\/mysql/\/opt\/mariadb\/10.2.44-systemd/g'
 
 However, PhpMyAdmin will fail to connect, because in conf it points to mysql socket. Workaround is to create mariadb socket into mysql dir.

 $ vi ctl.sh
  In start_mysql() function in success clause (ERR -eq 0) add 
  ln -s/opt/mariadb/10.2.44-systemd/tmp/mysql.sock /opt/bitnami/lampstack-7.2.26.0/mysql/tmp/mysql.sock

  And in stop_mysql() in (RUNNING -eq 0) clause put
  rm /opt/bitnami/lampstack-7.2.26.0/mysql/tmp/mysql.sock

 Now you can run both MariaDB and MySQL and easily control them via manager.

Optional: Systemd services for lampstack

$ sudo vi /etc/systemd/system/xampp.target
[Unit]
Description=Lampp target 
Requires=multi-user.target
After=multi-user.target
AllowIsolate=yes

$ sudo vi /etc/systemd/system/[email protected]
[Unit]
Description=Start Xampp module: %I
PartOf=xampp.target

[Service]
Type=simple
User=your_username
ExecStart=/opt/bin/xampp_ctl.sh start %i
ExecStop=/opt/bin/xampp_ctl.sh stop %i 
Restart=on-failure
RemainAfterExit=yes

[Install]
WantedBy=xampp.target

$ daemon-reload
$ systemctl enable xampp@apache  ## for service name after @ use ctlscript tags (check by calling ctlscript.sh help)
$ systemctl enable xampp@mariadb ## to start mariadb by default
$ systemctl enable xampp.target
$ systemctl disable xampp.service ## old xampp service
$ ln -s /opt/bitnami/lampstack-7.2.26.0/ctlscript.sh /opt/bin/xampp_ctl.sh
$ systemctl start xampp.target

[1]: I believe it's possible with xampp linux package, yet on the first sight i found its configuration messy as all modules are somewhat joined.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .