mysql


How to reset root password in XAMPP MySQL

Recently a client got locked out of a MySQL deployment managed by a XAMPP. They requested us to reset the password for the root account. To do so, we followed the following procedure:

Using a text editor, we opened the following file, which is the MySQL configuration file:

C:\xampp\mysql\bin\my.ini

We found the section for the MySQL daemon, which starts after the line that contains the following tag:

[mysqld]

Right below the tag, we added a new line and added the following directive:

skip-grant-tables

Example of a configuration file:

[mysqld]
skip-grant-tables
port= 3306
socket = "C:/xampp/mysql/mysql.sock"
basedir = "C:/xampp/mysql" 
tmpdir = "C:/xampp/tmp"
...

The skip-grant-tables directive causes the server not to read the grant tables in the MySQL system schema and, thus, start without using the privilege system. This option gives anyone unrestricted server access to all databases.

After saving the file, we restarted the MySQL server from the XAMPP interface. This action forced the new settings to be loaded and take effect.

Next, using the command line, we connected to the MySQL server using the CLI client for MySQL:

C:\xampp\mysql\bin\mysql.exe --user=root

In the above command, we issued the mysql.exe application with only one parameter, the --user=root, which instructs the client to ignore the default login username and use root instead.

Once we activated the MySQL command line, we switched the usage to the mysql database using the following command:

USE mysql;

Using the following command, you can set the password for the root:

UPDATE user SET password = 'newPassword' WHERE user = 'root';

Note: In case you want to leave the root password blank, then you can use the following:

UPDATE user SET password = '' WHERE user = 'root';

Please note that there might be more than one entry for root in the system, as it could contain different IPs etc. To see all available options, execute the following:

SELECT * FROM user \G;

After we finished messing with the MySQL internals, we executed the following command to exit the MySQL terminal:

exit;

Then, we edited the MySQL configuration file again:

C:\xampp\mysql\bin\my.ini

We found the section for the MySQL daemon again, which starts after the line that contains the following tag:

[mysqld]

We removed our skip-grant-tables directive and saved the file.

We restarted the MySQL server via the XAMPP interface after saving the file. The restart forced the updated settings to load and take effect due to this action.

Success!! The root user had the password we set for it and we could access the database as expected.

The same guide can be used for other installations of MySQL so long you know where the MySQL configuration file is.


Quick notes on optimizing a mysql/mariadb database in a docker container

First, to avoid opening the network of the container to another network, you need to open a shell to the docker container itself. To do so, use the following command:

docker exec -it website_db /bin/bash;

After executing the exec command, you will get a shell to the docker container. Use the following command to optimize all databases and their tables:

mysqlcheck -p -o --all-databases;

The -p parameter instructs the command to ask for a password (which probably and hopefully have set).

To exit the console and close the connection just type exit; after you are done with the above command.

Note: Where we used the word website_db you need to use the name of your container. If you are not sure of the name of the container, you can list all of the containers with their names using the following command:

docker container ls;

How to temporarily open MySQL / MariaDB port on CentOS 7 firewall

Recently, we came across a CentOS 7 that was executing MariaDB (MySQL) server. The Database Administrators needed a way to open to the port and allow connections to the SQL server from outside the machine.
As they did not have a specific IP from which they would connect, we provided the following solution.

To temporarily open port 3306

firewall-cmd --add-port=3306/tcp;

To close the port 3306 (method A)

firewall-cmd --remove-port=3306/tcp;

or reload firewalld which will cause it to loose all changes that are not permanent (method B)

firewall-cmd  --reload;

firewalld (Dynamic Firewall Manager) tool provides a dynamically managed firewall. The tool enables network/firewall zones to define the trust level of network connections and/or interfaces. It has support both for IPv4 and IPv6 firewall settings. Also, it supports Ethernet bridges and allow you to separate between runtime and permanent configuration options. Finally, it supports an interface for services or applications to add firewall rules directly.


Notes on how to connect from an external machine to a docker database in Google Compute Engine

A) Create a firewall rule in your VPC Network that allows you to connect to your database from outside the network: https://console.cloud.google.com/networking/firewalls/list

B) From your remote machine repair your database using mysqlcheck.

On Fedora you can install it by installing the MariaDB package as follows:

sudo dnf install mariadb -y;

The following command will automatically repair all databases and tables in that instance of MySQL, where of course the user has access:

mysqlcheck --host 45.37.15.225 --port 33061 --user admin --all-databases --auto-repair --password;

C) Edit the new firewall rule and either disable it or delete it for security.


Easy way to repair and optimize core WordPress MySql DataBase Tables

To manually repair and optimize the core WordPress tables of your MySql (or MariaDB) database:

A) edit your wp-config.php and add

define('WP_ALLOW_REPAIR', true);

right before the line

/* That's all, stop editing! Happy blogging. */

B) Visit the following link (and replace example.com with the domain or the IP of your site installation)

https://example.com/wp-admin/maint/repair.php

C) Press the Repair and Optimize Database button and wait for some time (depending on the size of the core tables of your installation)

D) Once the above step is done, remove or comment out (by adding a # at the beginning of the line) the line:

define('WP_ALLOW_REPAIR', true);

You need to do this step to to prevent this page (which could potentially take a lot of resources) from being used by unauthorized users.