Xampp Server Mysql

Xampp Server Mysql

17 Nov 2019

XAMPP is an open source server set up to test the projects on a local server before making it available to everyone over the web. This web app development server comes pre-installed with Apache web server, MySQL database, PHP, and Perl that help you build an offline application with desired features and functions. From the XAMPP control panel, you should see a green status for both Apache and MySQL. If you do not, then you can simply Stop then Start each of the web server services. Sometimes, the first time you start each server the do not get a green status because of the Windows firewall. This is completely normal. XAMPP Control panel (v3.2.2) is a plain, simple interface for XAMPP which helps you to Start/Stop, Administer and configure services like Apache, MYSQL, FileZilla, Mercury, Tomcat. You can also change ports, monitor services, start shell, view logs, etc. Go to Start and search for XAMPP and select the XAMPP Control panel to start it in Windows. In the local mysql/data folder of XAMPP you will need to delete all the original content except for the following directories (they can't change, so be sure to remove those directories from the data of your server): mysql (directory) mysqlupgradeinfo (file) performanceschema (directory) Then, add the content from your server (step 1) without. XAMPP is the title used for a compilation of free software.The name is an acronym, with each letter representing one of the five key components. The software packet contains the web server Apache, the relational database management system MySQL (or MariaDB), and the scripting languages Perl and PHP.

Xampp Server Php

The latest version of XAMPP contains MariaDB instead of MySQL.

But MariaDB is not 100% compatible with MySQL and can be replaced with the “original” MySQL server.

Requirements

  • Windows
  • The latest XAMPP for Windows v.7.1+ (64-Bit)
  • The latest Visual C++ Redistributable Packages,
  • Administrator privileges to restart Windows services

Backup

  • Backup the old databases into a SQL dump file (without the system databases)
  • Stop the MariaDB service
  • Rename the folder: c:xamppmysql to c:xamppmariadb

Installation

  • Download MySQL Community Server 8.x (64-Bit) from: https://dev.mysql.com/downloads/mysql/
  • Scroll down to Other Downloads: and click Download.
  • Click the link: No thanks, just start my download
  • Create a new and empty folder: c:xamppmysql
  • Extract ZIP archive to: c:xamppmysql
  • Create a new file: c:xamppmysqlbinmy.ini and copy this content:

Initializing the data directory

Important: Don’t skip this step!

Start the MySQL service (in your XAMPP Control Panel)

Data restore

Import the SQL dump file into the new database.

Done.

Known issues

  • Error: Authentication plugin 'caching_sha2_password' cannot be loaded
    • MySQL 8 uses caching_sha2_password as default authentication plugin.
    • Please follow the instructions (from above) and it should not happen.
    • Add the command line option: –default-auth=mysql_native_password
    • Read more: Connecting MySQL - 8.0 with MySQL Workbench
  • Error: mysqldump: Got error: 2026: SSL connection error: error:00000000:lib(0):func(0):reason(0) when trying to connect
    • Add the [client] ssl-mode=DISABLED option to your my.ini file
  • XAMPP control panel error: MySQL shutdown unexpectedly.
    • Make sure you have installed the 64-bit version of XAMPP and MySQL.
    • Make sure that no other MySQL server is already running on the same port (3306).
    • Try to run xampp-control.exe as administrator
    • You might forgot to initialize the data directory.
    • Some MySQL versions are not compatible with the XAMPP control panel.
    • Try to start mysqld.exe from the console: c:xamppmysqlbin>mysqld.exe
    • Try to delete the ibdata1 file
  • I can’t start or stop MySQL using the XAMPP control panel button.
    • Make sure you have installed the 64-bit version of XAMPP and MySQL.

Changing default MySQL port in Xampp

For situations like setting-up localhost in enterprise setting or for setting up localhost in development system for Web development changing default port of MySQL in Xampp is critical. We will change default MySQL port in Xampp by changing port number in Xampp ini files as well as Xampp settings.

Total number of file to change default 3306 to something else(let’s we change the port from 3306 to 3307) is three.

The files involved, to change the default MySQL port in Xampp are –

  1. my.ini file in MySQL config(in total 2 instances, and other 3 in comments)
  2. config.inc.php in Apache config (in total 1 instance)
  3. php.ini file in Apache Config( total 2 instance)

And the final one is in Xampp Config > Service and port Settings (1 instance)

Opening Config files with notepad++

Before changing port number by config files, set-up notepad++ to open up the config files. Although any text editor can be choosen for this purpose, but for this article, we will use notepad++ to open config files in Xampp.

To set notepad++ to default to open up config files in Xampp, install notepad++ by following any guide, which is straightforward. And write notepad++.exe in Configuration of Control Panel > Editor Xampp settings.

(1/4)Changing my.ini file configurations in MySQL config

For changing my.ini file, we need to stop the running XAMPP server 1. Apache server 2. MySQL server, or additional servers running though Xampp to avoid any Application or System failure.

Open my.ini file in MySQL config in Xampp control Panel

Click the config button of MySQL in Xampp Control Panel, and it will open-up option of my.ini and <choose>. Syncthing qnap install. click my.ini file, and as we have notepad++ as default editor, notepad++ will open the my.ini file.

Find 3306 in my.ini file in the notepad++ editor

Find for 3306 in the my.ini file opened in the notepad++ editor, there will be total 5 occurrences, but first two should be changed, and the later three occurrences will be in comment.

  1. (1/6)First occurrence will be in line number 20, it is to be changed to 3307
  2. (2/6)And the second occurrence will be in line number29, it also is to be changed to 3307

Now save the my.ini file and you may close the file.

(2/4)Changing config.inc.php file configurations in Apache config

Next we should change the third occurrence of port number 3306, which will be in config.inc.php file. Open the file, from the Config menu beside Apache > config button, and choose the config.ini.php option, to open this file with editor.

Find 3306 in config.inc.php file in the notepad++ editor

After we open up config.inc.php file in notepad++ editor, look for 3306, which will be in line number 27. The only occurrence(3/6) will be in line number 27, change port number from 3306 to 3307.

$cfg['Servers'][$i]['host'] = '127.0.0.1:3307';

Save the file, and file can be closed

(3/4) Changing php.ini file configurations in Apache config

The php.ini file have in total two occurrences of port number to change. Open php.ini file beside Apache config. Open the file from Apache > config button.

Xampp Mysql Server Version

After opening the file with notepad++, look for port number 3306.

  1. The first occurrence will be in line number 1174
  2. The second occurrence will be in line number 1976

After replacing with new port number, save and close the php.ini file.

(4/4)Changing Mysql port in Xampp control panel configurations

Our final occurrence of the port number to change is in the XAMPP control panel.

Xampp Mysql Tutorial

  1. open Xampp open control Panel
  2. then the config button on the top of all buttons
  3. then open up service and port settings button
  4. this will open Apache tab by default, but switch to MySQL tab
  5. change from default 3306 port to 3307, and save and close in the MySQL tab
  6. and finally save and close in service and port settings panel

Start MySQL in Xampp with new port number

As we have changed from default port number from 3306 to 3307 in first two occurrence in the my.ini file, third occurrence in the config.inc.php file and the final occurance in the Xampp Control Panel config. We have saved and closed the files and the Xampp control panel config.

Now start the server, we see that the default port number of MySQL in Xampp is changed from 3306 to our choosen 3307. The port number can be any number upon the system and application dependency, this port number was shown as an example.

In this article, how to change default MySQL port in Xampp was discussed.