Xampp With Mysql

XAMPP is an easy to install Apache distribution containing MariaDB, PHP, and Perl. Just download and start the installer. XAMPP server uses it’s own MySQL database, so just deactivate your current MySQL database & use the one XAMPP provides. Sometimes that doesn’t work on windows or the same port as being used by MySQL is also used by Skype, and that also can cause a problem. You can try stopping the already running MySQL by sudo service mysql-server stop.

Here are the steps I used to to upgrad MySQL to MariaDB in XAMPP on Windows in about 5 minutes. After completing this process, MariaDB will look and work just like MySQL. You may even notice a performance increase in your website. No need to panic fellow developer, increased performance is perfectly normal and just one of the great benefits of MariaDB over MySQL.

IMPORTANT: Before you begin, always be sure to make a good backup. Do not do move instead of copy files in the instructions below as the original files may be required in order to back out and restore the original MySQL environment if it doesn't work out for you.

  1. Open a command prompt.
  2. Go to your xampp folder.
  3. Enter the following command: mysql/bin/mysql --version
  4. Take note of the version of MySQL you have installed. This is the version of MariaDB that you will need. You can now exit the command prompt as the rest of the instructions can be done through Windows Explorer.
  5. Download the ZIP version of MariaDB making sure that the version matches as closely as possible to the version of MySQL you currently have installed.
  6. Shutdown your XAMPP server if it is running.
  7. Rename the xampp/mysql folder to mysql.old.
  8. Unzip the contents of the MariaDB ZIP file into your XAMPP folder.
  9. Rename the MariaDB folder, called something like mariadb-5.5.37-win32, to mysql.
  10. Rename xampp/mysql/data to data.old.
  11. Copy the xampp/mysql.old/data folder (not just the contents) to xampp/mysql/data.
  12. Copy the xampp/mysql.old/backup folder to xampp/mysql/.
  13. Copy the xampp/mysql.old/scripts folder to xampp/mysql/.
  14. Copy mysql_uninstallservice.bat and mysql_installservice.bat from xampp/mysql.old/ into xampp/mysql/.
  15. Copy xampp/mysql.old/bin/my.ini into xampp/mysql/bin.
  16. Edit xampp/mysql/bin/my.ini using a text editor like Notepad. Find skip-federated and add a # in front (to the left) of it to comment out the line if it exists. Save and exit the editor. If you skip this step, MariaDB may not start and you may find an error message similar to the following in the MySQL error log:[ERROR] xamppmysqlbinmysqld.exe: unknown option '--skip-federated'
    [ERROR] Aborting
  17. Start-up XAMPP. If you can't get MariaDB to start, you may need to use the --skip-grant-tables option. I didn't need to do this but am including this tip in case you need it.
  18. Run xampp/mysql/bin/mysql_upgrade.exe. Once you do this, the database may no longer be internally compatible with the MySQL version of your server. If you skip this step, you may notice the following error message in the MySQL error log:[ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2′,'MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323′,'MYSQL40′,'ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2′,'MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323′,'MYSQL40′,'ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_A
    [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
  19. Shutdown and restart MariaDB (MySQL).
  20. Test your website to make sure everything still works. Once you start making changes to it, you will no longer be able to rollback to the MySQL server.
XamppWith

MariaDB should now be ready to use. Because it is a drop in replacement for MySQL, you will be able to start and stop it from within the XAMPP Control Panel just like you always have in the past.

If anything goes wrong, and you followed these instructions correctly, you can easily restore the original MySQL by simply shutdown XAMPP and renaming xampp/MySQL to xampp/mariadb and xampp/mysql.old to xampp/mysql to put everything back as it was before.

Xampp With Mysql Already Installed

If you prefer to troubleshoot your issue, you may find more information on the problem by viewing the MySQL error log, either by clicking the Logs button for MySQL in the XAMPP Control Panel, or by opening the following file in a text editor: xamppmysqldatamysql_error.log

Related Posts:

On our previous post, we have discussed about how to install LAMP Environment on your Windows PC easily using XAMPP. If you are a MySQL coder, or wish to learn it, then probably you have came to know about the MySQL terminal [or simply a command prompt in Windows]. Basically it is the best MySQL tool for any newbie user. You have type all your SQL Queries here manually and this way you can actually learn faster.

But opening the MySQL prompt can be a little tricky in Windows PC. As just typing “mysql” in command prompt does not work… So here is a complete guide to easily open MySQL terminal from Windows Command Prompt.

#1: Install XAMPP and locate installation Directory:

First you need to install XAMPP on your Windows PC. You can install MySQL alone if you want, but I shall recommend XAMPP for a better and easier usage. Simply follow the previous post to install it. Also Note down the installation directory.

The default installation directory is “C:XAMPP”. It may vary for your customized installation. So assuming this, try to locate this folder:

C:xamppmysqlbin” or “~Installation Folder~xamppmysqlbin

where ~installation Folder~ is obviously the folder where you have selected to install XAMPP.

You should be able to find a file named, mysql.exe . This is the main MySQL Terminal. If you simply open it by double clicking then although a MySQL Terminal will be launched, but you won’t actually be able to modify your database, as in this way MySQL terminal does not get connected to the DB server host!

Just follow the instruction below to open the terminal properly…

Xampp

#2: Open MySQL terminal from Windows Command Prompt(cmd):

  • Go to Start Menu > All Programs > Accessories > Command Prompt;
  • This will pop up the Windows CMD.
  • Now type in the following command and hit enter:
  • Note that if you have any other installation directory, then you need to replace C:XAMPP accordingly. Rest the Folder structure …mysqlbin remains the same.
  • Now type the following command on the cmd prompt:
    mysql.exe –h localhost –u root -p
  • MySQL will start and will prompt you for a password. Leave it blank and hit enter. This should bring you the MySQL terminal.

Xampp With Mysql 5.6

Now write and run the SQL Query languages and do whatever you wish to. Below is a process to add shortcut to the MySQL terminal.

#3: Adding shortcut using environment variables:

We will now add a environment variable to windows so that we can open the mysql.exe terminal window anywhere from command prompt without changing the working directory. Just follow the steps below:

  • On Windows 7/ Vista: Go to Control Panel > System > Advanced System Settings > Advance Tab > Environment Variable;
  • On Windows XP: Go to Control Panel > System > Advanced Tab > Environment Variable;
  • Now under “User Variables for ~UserName~” Click on “New” button.
  • Under “Variable Name” type: mysql
  • Under “Variable Value” type: C:xamppmysqlbinmysql.exe
  • Obviously you need to change the location of …/xampp if you have any other installation directory.
  • Now Save everything and close the window.

Xampp Mysql 5.7

With

You are done with creating the shortcuts… To launch MySQL all you need to is:

  • Open up Command Prompt;
  • Type:

    Jiffy. and hit enter (As shown on the first image). Give blank password as before and you are set to go…

Now you have got your MySQL terminal window on your Win7/XP/Vista. Use it the way you want.

Xampp Mysql Tutorial

I hope the article was informative and useful for you. Please do give your feedback on this. Also if you know any better method of opening MySQL terminal in Windows, then do share it with us!