MySQL is a relational database management system (RDBMS) that is free and open-source. A relational database organizes data into one or more data tables where data types may be compared to one another, allowing the data to be structured.
SQL is a programming language that enables programmers to build, modify, and extract data from relational databases and manage user access.
MySQL has independent clients that enable users to communicate directly with a MySQL database using SQL. Still, it’s more common for MySQL to be used in conjunction with other programs to create applications that need relational databases. The LAMP web server development stack (and many others) includes MySQL.
LAMP stands for Linux, Apache, MySQL, Perl/PHP/Python. Many database-driven web apps, such as Drupal, Joomla, phpBB, and WordPress, use MySQL. Many standard websites, such as Facebook, Flickr, MediaWiki, Twitter, and YouTube, also use MySQL.
How did MySQL start?
MySQL started as a low-cost alternative to more efficient proprietary databases, but it has grown to accommodate larger-scale requirements. It is still most widely used as a component in a LAMP-based web application or as a standalone database server in small to medium scale single-server deployments. MySQL’s popularity stems from its relative ease of use, made possible by an ecosystem of open-source software like phpMyAdmin.
However, since there are limitations on how much a single server’s output may scale up (‘scaling up’), multi-server MySQL (‘scaling out’) deployments are expected on larger scales to provide better performance and reliability. A typical high-end setup might include a robust master database that handles data writes and is replicated to multiple slaves that handle all read operations. The master server continuously pushes binlog events to attached slaves, allowing a slave to be promoted to become the new master in the event of a failure, thus reducing downtime. Further performance can be improved by using Memcached to cache database query results in memory or dissolve a database into smaller pieces called shards that can be distributed through several distributed server clusters.
How to Set, Change, Reset, or Recover a MySQL Password?
If you use MySQL, you may need to set or change the root user password at some point. This can happen if you forget your password or if you want to improve the strength of your password.
As you would expect, the method to change the password is entirely controlled through the command line and can be used with either MySQL or MariaDB installations. It makes no difference which Linux distribution you are using.
Given the current state of attacks across the IT landscape, it is strongly advised for you to use strong database passwords. Use a password generator to generate a random password, which you can then store in a password manager (instead of using an easy-to-remember password).
-
Setting the password for the first time
Typically, you are prompted to create an initial password while installing MySQL or MariaDB. If this does not occur, you will be required to create a password for the first time. After opening a terminal window, type the command given below to achieve the same:
mysqladmin -u root password NEW PASSWORD
The password will be set to NEW PASSWORD.
After that you will be asked to enter the newly configured password when you log into MySQL with the command MySQL -u root -p.
The MySQL secure connection command is another choice for setting the root password for the first time and it also adds a layer of protection to your MySQL database. This command will not only change the root user’s password, but it will also eliminate anonymous users, disable remote root login, and delete the test database.
After you’ve answered the questions regarding your newly configured password, the password will be created (as well as your database being a bit more secure).
-
Changing the MySQL root user password
To change the MySQL password, first create a new file with the following content:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD';
The new password to be used is PASSWORD. The file should be saved as -/MySQL-PWD.
Next, use the following command to terminate the MySQL daemon:
sudo systemctl stop MySQL
With the new admin password, you should be able to log into the MySQL command prompt as follows:
MySQL -u root -p
Type the admin password when asked, and you’re good to go.
-
Recovering the MySQL Password
What if you forgot the password for the MySQL root user? This could have been quite a predicament if the developers hadn’t foreseen it. You simply need to take these steps to recover the password:
1. Stop the MySQL server process with the command:
sudo service MySQL stop
2. Start the MySQL server with the command:
sudo mysqld_safe --skip-grant-tables --skip-networking &
3. Connect to the MySQL server as the root user with the command
MySQL -u root
4. To reset the root password, issue the following MySQL commands at this point:
mysql> use mysql;
mysql> update user set authentication_string=password('NEWPASSWORD') where user='root';
mysql> flush privileges;
mysql> quit
Now, the password will be reset and the new password will be set as NEWPASSWORD.
- Restart the MySQL daemon with the command:
sudo service MySQL restart
You can log into MySQL with the new password.
How to Change the MySQL User Root Password in Linux?
Step 1: Log in as a MySQL user
Make sure you’re signed in as the same user who runs MySQL when you boot up your Linux system. Even though you can log in as root, make sure you start the MySQL server with the root password –user=mysqloption.
Otherwise, the device can create files with the root user’s permissions, which can trigger issues.
Step 2: Search for the .pid file for the MySQL service
The position is determined by your Linux distribution as well as a few other factors.
Several systems store them in /var/lib/mysql/, /var/run/mysqld/, or /usr/local/mysql/data/. Most of the time, the filename usually starts with mysqld (or your system’s hostname) and uses the .pid extension.
Step 3: Kill the MySQLD Process
To kill the process, open a command line, and run the following command:
kill `cat /mysql-data-directory/host_name.pid`
Instead of mysql-data-directory/hostname.pid, use the filename you find in the previous phase.
Also, make sure to include the entire file route. Instead of a single-quote symbol, use the back-tick key (usually located above the tab key).
Step 4: Create a password file
- Run the following command in your favorite text editor:
sudo vim
- After that, add the following line to execute the command:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
Remember to use the semicolon and single-quote marks. Change the NewPassword with your desired password in the above command.
This command will work on the computer you’re working on right now. Replace localhost with the right hostname if you’re connecting to a different device.
- Save the file to home/me/mysql-init.
Substitute your user account for /home/me, and save the file to a password-protected user account.
Step 5: Apply the New Password to the MySQL Server after it has been restarted.
Run the following command in the terminal window:
mysqld --init-file=/home/me/mysql-init &
This will start MySQL and change the password in the text file.
Step 6: Clean up
Finally, use the root account to log into your MySQL server and test the new password. Then, delete the file you have created in step 4.
How to Reset MySQL Root Password in Windows?
Step 1: Shut down the MySQL server
First, make sure you’re signed in as an administrator. Press and hold the Windows/Super key and then press the “R” key. Once the “Run” box appears, type the following:
services.msc
Click OK. To find the MySQL app, scroll down the list of services. Right-click the entry, then select Stop from the drop-down menu.
Step 2: Launch the text editor
To find the Notepad app, go to the Windows menu and look for it.
You may also use the following path: menu > Windows Accessories > Notepad.
Step 3: Using the Password Command, create a new text file.
Enter ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘NewPassword’; in the text editor.
Make sure the quotation marks and semicolons are there. Replace NewPassword with your preferred password.
To save the file to the root of your hard drive, go to File > Save As. Choose a filename, such as mysql-init.txt. As a result, the localhost command will update the password on your local machine. Substitute the hostname for localhost if you’re trying to change the password on a system over the network.
Step 4: Open a command prompt
- Press Ctrl+Shift+Esc.
- Then, click on the File menu > Run a new task.
- Type cmd.exe, and check the box to run as administrator.
- Click OK.
Step 5: With Your New Config File, Restart the MySQL Server
Using the command prompt, navigate to the MySQL directory:
cd "C: Program FilesMySQLMySQL Server 8.0bin"
As a result, the command line will indicate that you’re in this folder.
mysqld --init-file=C:mysql-init.txt
Step 6: Clean up
You can now use the new password to log into your MySQL server as root.
Make sure it works by checking it twice. If you have specific configuration options (for example, starting MySQL with the –defaults-file option), use them.
Remove the C:myswl-init.txt file after MySQL has started.
Conclusion
This article discusses what MySQL software is and how it started. Then it talks about how to reset the root password on MySQL in Linux and Windows. The method of changing and resetting the password has been discussed in detail.
People are also reading: