How to: having MySQL 8 portable

Posted by in Databases, MySQL

In the previous entry we commented on how to have a portable MariaDB server, using the XAMPP tool, while in this entry, I propose that instead of MariaDB, let’s prepare a MySQL 8 as the server to work.

Downloading MySQL

The first thing for having a MySQL portable version is that we can’t use the MySQL installer (in this blog I already told you how to install MySQL 5.6 with the installer). This time, we downloaded the zip version.

Descargando MySQL 8

Once downloaded the file, unzip the file in the directory you like, and as a advice, avoid white space or wierd characters. In my case, I have it on f:mysql8

Before being able to start MySQL server, it is necessary to have a configuration file my.ini, a vital file for MySQL, and that we will have to create (or use one that we already have from another instance of MySQL).

A second very important aspect is that the directory data is not included, which is where the databases are normally located, so it will be necessary to carry out some intermediate steps.

But first, let’s create a configuration’s file (in this link, you have what I use when working with MySQL 5.6):

my.ini

The my.inifiel is the configuration file for MySQL, but if you look in the /bin, you won’t find anyone. You need to create on. Here I have one for you with the basic parameters, based on the directories I have used:

# my.ini para MySQL 8.0.x de @manejandodatos . http://manejandodatos.es

[client]
port=3306
socket=/tmp/mysql.sock

[mysql]
default-character-set=utf8

[mysqld]
port=3306
socket=/tmp/mysql.sock
key_buffer_size=16M
max_allowed_package=100M
basedir=”f:/mysql8″
datadir=”f:/mysql8/data”

Here, you can see the config file:

my.ini para configurar MySQL

Creating a _start.bat file for starting MySQL

The easiest way to start a MySQL server is by creating a _start.bat file that avoid us to repeat the instruction every time you want to start the server, and it is very simple:

f:mysql8binmysqld.exe –console

I also have include th efile under the mysql8 directory.

Now, open a console, and execute the _start.bat file and …

Error when starting

… I have a “nice” error, because the server is still not ready: the configuration of the server is not finished.

To avoid this error, you only need to modify the first initialization of the server including –initialize option (in MariaDB, this option is worthless)

mysqld --initialize --console

Of course, you can read in the documentación about this initial step adn all you need to know..

WARNING!This process of starting the data directory has a different approach on MariaDB, where there is no –initialize option.

Inicializaation of the data directory

To do that, let’s execute the next command:

binmysqld.exe –initialize –console

And in a few seconds, the initialize process is executed, accesing the my.ini config file.

Inicializing the data dir

As you can read in the last line of the report, the initializing process has created the data dir, but also, it has created a temporary password for the root user. And,do not forget the password!!!

Nowm you can start the server using the _start.bat file, and as you can check, it is up and running:

MySQL8 operativo

Let’s try accesing the MySQL server by using the temporary password. To do that, let’s open a new console, go to your path (in my case f:mysql8bin), and write this command:

mysql -u root -p

and you are asked for the temporary password:

Consola de MySQL

But, with the temporary password, MySQL doesn’t allow us to use the server. You need to change it:

Clave temporal impide trabajar con MySQL

So, following the instructions in the documentation, the way to change the user’s password from root to root-password (here you write yours !) is with the command:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root-password';

Now, you can execute the query to check what version of the server we are using:

MySQL operativo

Of course, if you need your server in a different unit, you just need to modify the my.ini file to the right path and also modify _start.bat.

Let’s use HeidiSQL now to verify that everything is ready to work:

MySQL 8 accediendo desde HeidiSQL

And that’s all for this entrance. I hope this content will be useful for you and …

Happy coding!