[MySQL/MariaDB] Reset MySQL Root Password

Reset Root Password in MySQL(MariaDB)

Let's check how to Reset Root Password for MySQL or MariaDB.

The script can be slightly different depending on OS and MySQL Editions.

Stop current running MySQL(MariaDB)

OS: Red Hat, Centos etc. DB: MySQL

sudo /etc/init.d/mysqld stop

OS: Ubuntu, Debian. DB: MariaDB

sudo /etc/init.d/mysql stop

-- OR 

sudo service mysql stop

-- OR

sudo systemctl stop mysql

Start MySQL(MariaDB) skipping permission check

sudo mysqld_safe --skip-grant-tables &

Note! From MySQL 8.0, you need to use "--init-file" option instead. From MariaDB 10.4, this way will not work.

Set new Password for Root User

Connect to MySQL without a password.

mysql -uroot

Update Root Password.

UPDATE mysql.user
SET
  authentication_string=PASSWORD("<new strong password>")
WHERE User='root';

-- OR

UPDATE mysql.user
SET
  PASSWORD=PASSWORD("<new strong password>")
WHERE User='root';

-- OR 
ALTER USER 'root'@'localhost' IDENTIFIED BY VIA mysql_native_password USING PASSWORD('<new strong password');

Apply changes.

flush privileges;

Exit from MySQL

quit;

Stop & Start MySQL(MariaDB)

OS: Red Hat, Centos etc. DB: MySQL

sudo /etc/init.d/mysqld stop

sudo /etc/init.d/mysqld start

OS: Ubuntu, Debian. DB: MariaDB

sudo /etc/init.d/mysql stop

-- OR 
sudo service mysql stop

-- OR
sudo systemctl stop mysql

And

sudo /etc/init.d/mysql start

-- OR 
sudo service mysql start

-- OR
sudo systemctl start mysql

Connect to MySQL(MariaDB) with the new password

mysql -u root -p

References

• MySQL mysqld_safe
• MariaDB mysqld_safe
• Authentication from MariaDB 10.4

[SQL Server] SQL Server remote admin connections -- DAC (Dedicated Admin Connection)

SQL Server remote admin connections -- DAC (Dedicated Admin Connection)

If the users can't connect to the server, or there's problem with SQL Server, then you might need to connect to SQL Server explicitly for diagnostics purpose or something.

Mostly this situation can happen when there are too many connections or there are too high CPU usage queries are running on the server so that no one can connect to the server at that moment.

DAC(dedicated administrator connection) is designed for these kinds of situations.

Enable DAC

First of all you need to check the current configuration.

sp_configure 'remote admin connections'
GO

sp_configure 'remote admin connections', 1;  
GO  
RECONFIGURE;  
GO
sp_configure 'remote admin connections'
GO

Verify ports for DAC 

Make sure the default DAC port (1434) is opened.

netstat -an | findstr ":1434"

Connect to the server via DAC

You can connect via SSMS, but it's better to connect via SQLCMD which is a command line.

Because when you connect to the server via SSMS, it opens at least 2~3 new sessions and which means it's not possible to connect with DAC by default.

sqlcmd -S {Servername} -U {username} -P {password} -d {databasename} -A

Note! Make sure SQL Server Browser service is Running.

** Error -- when SQL Browser is not running.

Check the current DAC session

Check Query

SELECT dess.session_id AS DACSessionID, @@SPID AS CurrentSessionID, dess.original_login_name AS DACLogin
FROM sys.endpoints AS ep JOIN sys.dm_exec_sessions AS dess ON ep.endpoint_id = dess.endpoint_id
WHERE ep.name='Dedicated Admin Connection';
GO

References

• remote admin connections Server Configuration Option
• Diagnostic Connection for Database Administrators
• sqlcmd Utility

[Windows, SQL Server] How to verify SQL Server port is opened from the client computer?

How to verify the SQL Server port is opened from the client computer?

Situation

You want to make sure that the SQL Server service port is opened and can connect from the client computer.

If you want to verify the listening port on SQL Server, take a look here.

Telnet Client

The telnet command is a very traditional old way to check the server port is opened.

Moreover, it's also possible to use the same command parameters as Linux.

But, you have to turn on Windows feature as below. 

And may require your computer reboot.

telnet <desination host> <destination port>

You may wait until it returns the result when it fails or you have to close the window even if it succeeded.

Luckily, there are 2 more ways you can do that quite easily.

PsPing

PsPing is one of the utility applications from PsTools.

You can download PsTools from here.

It enables you to check the qualities of "ICMP, TCP and UDP" connections.

Basic command

psping <Destination host>:<Destination port>

It has lots of options/parameters you can use. For example, you want to use PsPing with

• 2 seconds interval,
• 10 times try,
• Use IPv4,
• The destination host is "testserver",
• The destination port is "1433" 

then, the command would be like this.

 

Note! You need to perform this operation on the same directory whereas the executable files located. You can add the directory to your Path(System Properties > Advanced > Environment Variables > Path > Add directory of the file location)  Alternatively, you can copy the executable files to the current system directory e.g. C:\Windows\System32.

Portping

PortPing is the simplest tool to check the port (from my perspective)

You can download Portping from here.

It enables you to check the qualities of "ICMP, TCP and UDP" connections.

Basic command

portping [-c <tries(count)>] <Destination host> <Destination port>

It has only one option -c: stop after count connections (default 5).

Thus, it's very simple to use.

For e.g.

Note! The name can be different depending on the version such as "portping-v1.0-windows_amd64.exe", therefore it will be easier to rename this file to "portping.exe" in order to use convenience. You need to perform this operation on the same directory whereas the executable files located. You can add the directory to your Path(System Properties > Advanced > Environment Variables > Path > Add directory of the file location)  Alternatively, you can copy the executable files to the current system directory e.g. C:\Windows\System32.

What else?

PsPing and Portping can be also useful to check the connectivity to the server for the specific services in a situation of ICMP(ping) protocol blocked in the target network by the administrator.

References

• Windows 10: Enabling Telnet Client
• PsPing
• PortPing download(Sourceforge)
• Other tools: tcping, nmap

[Windows, SQL Server] How to verify the listening port of SQL Server on the local machine?

How to verify the listening port of SQL Server on the local machine?

Situation

You have already configured TCP/IP listening port for SQL Server from SQL Server Configuration Manager.

But if you want to make sure that the port is opened and the server listens to the port, then what should you do?

Before you start, if you want to verify the SQL Server port is opened from the client computer, then take a look here.

Traditional command prompt

In a traditional command prompt, you can use "netstat" command.

netstat -a -b | findstr <port number>

e.g. SQL Server Service port is "1433".

PowerShell Command

You can use "Get-Process" cmdlet  + "Get-NetTCPConnection" cmdlet.

Get-Process -Id (Get-NetTCPConnection -LocalPort <port number>).OwningProcess

e.g. SQL Server Service port is "1433".

PowerShell looks way much better.

References

Windows command prompt

• netstat
• findstr

Windows PowerShell Cmtlet

• Get-Process
• Get-NetTCPConnection