I cannot figure out my MySQL root password; how can I find this out? Is there any file where this password is stored?
I am following this link but I do not have directadmin directory in local.
Ben
51.4k36 gold badges127 silver badges148 bronze badges
asked Jun 5, 2012 at 9:54
8
thanks to @thusharaK I could reset the root password without knowing the old password.
On ubuntu I did the following:
sudo service mysql stop
sudo mysqld_safe --skip-grant-tables --skip-syslog --skip-networking
Then run mysql in a new terminal:
mysql -u root
And run the following queries to change the password:
UPDATE mysql.user SET authentication_string=PASSWORD('password') WHERE User='root';
FLUSH PRIVILEGES;
In MySQL 5.7, the password field in mysql.user table field was removed, now the field name is ‘authentication_string’.
Quit the mysql safe mode and start mysql service by:
mysqladmin shutdown
sudo service mysql start
answered Oct 7, 2014 at 10:28
eeezyyeeezyy
2,0791 gold badge15 silver badges18 bronze badges
7
You can’t view the hashed password; the only thing you can do is reset it!
Stop MySQL:
sudo service mysql stop
or
$ sudo /usr/local/mysql/support-files/mysql.server stop
Start it in safe mode:
$ sudo mysqld_safe --skip-grant-tables
(above line is the whole command)
This will be an ongoing command until the process is finished so open another shell/terminal window, log in without a password:
$ mysql -u root
mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';
MySQL 5.7 and over:
mysql> use mysql;
mysql> update user set authentication_string=password('password') where user='root';
Exit the MySQL CLI:
mysql> exit
Restart MySQL in normal mode, first stopping the safe mode instance:
$ mysqladmin -u root -p shutdown # (when prompted, enter the new password just set)
$ sudo service mysql start
or
$ sudo /usr/local/mysql/support-files/mysql.server start
Your new password is ‘password’.
Reg Edit
6,6311 gold badge35 silver badges46 bronze badges
answered Sep 17, 2014 at 3:57
tk_tk_
16.2k8 gold badges80 silver badges90 bronze badges
5
One thing that tripped me up on a new install of MySQL and wondering why I couldn’t get the default password to work and why even the reset methods where not working.
Well turns out that on Ubuntu 18 the most recent version of MySQL server does not use password auth at all for the root user by default. So this means it doesn’t matter what you set it to, it won’t let you use it. It’s expecting you to login from a privileged socket.
mysql -u root -p
This will not work, even if you are using the correct password.
Instead, you need to use:
sudo mysql
that will work with out any password.
then once you in you need type in
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Password you want to use';
Then log out and now it will accept your password.
Jakov
8612 gold badges17 silver badges34 bronze badges
answered Apr 20, 2019 at 3:24
Kit RamosKit Ramos
1,4331 gold badge15 silver badges32 bronze badges
1
MySQL 5.7 and above saves root in MySQL log file.
Please try this:
sudo grep 'temporary password' /var/log/mysqld.log
answered Mar 13, 2016 at 14:59
VladVlad
3,4551 gold badge30 silver badges24 bronze badges
2
You cannot find it. It is stored in a database, which you need the root password to access, and even if you did get access somehow, it is hashed with a one-way hash. You can reset it: How to Reset the Root Password
answered Jun 5, 2012 at 9:55
lanzzlanzz
41.7k10 gold badges86 silver badges96 bronze badges
1
Follow these steps to reset password in Windows system
-
Stop Mysql service from task manager
-
Create a text file and paste the below statement
MySQL 5.7.5 and earlier:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('yournewpassword');
MySQL 5.7.6 and later:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'yournewpassword';
-
Save as
mysql-init.txtand place it in'C' drive. -
Open command prompt and paste the following
C:> mysqld --init-file=C:\mysql-init.txt
answered Jul 1, 2016 at 15:13
2
This worked for me:
On terminal type the following
$ sudo mysql -u root -p
Enter password://just press enter
mysql>
answered Feb 19, 2017 at 15:35
LightLight
1431 silver badge6 bronze badges
2
Unless the package manager requests you to type the root password during installation, the default root password is the empty string. To connect to freshly installed server, type:
shell> mysql -u root --password=
mysql>
To change the password, get back the unix shell and type:
shell> mysqladmin -u root --password= password root
The new password is ‘root’. Now connect to the server:
shell> mysql -u root --password=
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Oops, the password has changed. Use the new one, root:
shell> mysql -u root --password=root
...
blah, blah, blah : mysql welcome banner
...
mysql>
Bingo! New do something interesting
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
Maurycy
answered Jan 17, 2015 at 9:34
mszmurlomszmurlo
1,2281 gold badge13 silver badges27 bronze badges
1
As addition to the other answers, in a cpanel installation, the mysql root password is stored in a file named /root/.my.cnf. (and the cpanel service resets it back on change, so the other answers here won’t help)
answered Sep 24, 2015 at 7:48
Ohad CohenOhad Cohen
5,6163 gold badges38 silver badges36 bronze badges
0
you can view mysql root password , well i have tried it on mysql 5.5 so do not know about other new version well work or not
nano ~/.my.cnf
answered Oct 28, 2016 at 17:26
user889030user889030
4,2533 gold badges46 silver badges51 bronze badges
0
The default password which worked for me after immediate installation of mysql server is : mysql
LinusGeffarth
26.7k29 gold badges118 silver badges171 bronze badges
answered Sep 29, 2014 at 8:43
1
The procedure changes depending the version of MySql. Follow the procedure exactly as described for your version:
-
HINTS – Read before the instructions page for your version of MySql*
-
In step 5: Instead of run CMD, create a shortcut on your desktop calling CDM.exe. Then right-click on the shortcut and select “Execute as Administrator”.
-
In step 6: Skip the first proposed version of the command and execute the second one, the one with the –defaults-file parameter
-
Once you execute the command, if everything is ok, the CMD window remains open and the command of step 6 continues executing. Simply close the window (click ‘x’), and then force close MySQl from the Task Manager.
-
Delete the file with the SQL commands, and start again MySQL. The password must be changed now.
5.0
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
5.1
http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html
… just change the version in the link (5.5, 5.6, 5.7)
ashatte
5,4228 gold badges37 silver badges50 bronze badges
answered Oct 31, 2013 at 0:28
In your “hostname”.err file inside the data folder MySQL works on, try to look for a string that starts with:
“A temporary password is generated for roor@localhost “
you can use
less /mysql/data/dir/hostname.err
then slash command followed by the string you wish to look for
/"A temporary password"
Then press n, to go to the Next result.
answered Jan 11, 2016 at 5:06
I solved this a different way, this may be easier for some.
I did it this way because I tried starting in safe mode but cannot connect with the error:
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
What I did was to connect normally as root:
$ sudo mysql -u root
Then I created a new super user:
mysql> grant all privileges on *.* to 'myuser'@'%' identified by 'mypassword' with grant option;
mysql> quit
Then log in as myuser
$ mysql -u myuser -p -h localhost
Trying to change the password gave me no errors but did nothing for me so I dropped and re-created the root user
mysql> drop user 'root'@'localhost;
mysql> mysql> grant all privileges on *.* to 'root'@'localhost' identified by 'mypassword' with grant option;
The root user is now working with the new password
answered Sep 6, 2019 at 18:04
Using Debian / Ubuntu mysql packages, you can login with user debian-sys-maint, which has all the expected privileges, the password is stored in the file /etc/mysql/debian.cnf
answered Jan 21, 2022 at 9:51
Ohad CohenOhad Cohen
5,6163 gold badges38 silver badges36 bronze badges
0
System:
- CentOS Linux 7
- mysql Ver 14.14 Distrib 5.7.25
Procedure:
-
Open two shell sessions, logging in to one as the Linux root user
and the other as a nonroot user with access to themysqlcommand. -
In your root session, stop the normal mysqld listener and start a
listener which bypasses password authentication (note: this is a
significant security risk as anyone with access to themysql
command may access your databases without a password. You may want
to close active shell sessions and/or disable shell access before
doing this):# systemctl stop mysqld
# /usr/sbin/mysqld --skip-grant-tables -u mysql & -
In your nonroot session, log in to mysql and set the mysql root password:
$ mysql
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> quit; -
In your root session, kill the passwordless instance of mysqld and restore the normal mysqld listener to service:
# kill %1
# systemctl start mysqld -
In your nonroot session, test the new root password you configured above:
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
...
mysql>
answered Jun 24, 2019 at 17:57
CODE-REaDCODE-REaD
2,7393 gold badges32 silver badges60 bronze badges
I was stuck with this problem for a couple of minutes and the following was the only solution that actually worked:
https://phoenixnap.com/kb/access-denied-for-user-root-localhost
sudo mysqlALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'insert_password';mysql -u root -p
answered Jan 28, 2021 at 11:56
Ali HavasiAli Havasi
5515 silver badges8 bronze badges
In case you already set a password in the past the mysql -uroot -p solution will not work,
In my case I used some of the answers above to solve this (Ubuntu 16). The result was:
sudo service mysql stop
sudo mysqld_safe --skip-grant-tables &
if you see this text in the screen:
mysqld_safe Directory ‘/var/run/mysqld’ for UNIX socket file don’t exists.
then do:
sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld
sudo mysqld_safe --skip-grant-tables & # Look at the & at the end!
Enter other terminal to set your password like this:
sudo mysql -u root
mysql> use mysql;
mysql> SET PASSWORD FOR 'root'@'localhost'=PASSWORD('__NEW__PASSWORD__');
mysql> flush privileges;
mysql> quit;
then restart the service and login
# end mysqld_safe in the other terminal
sudo service mysql start
sudo mysql -h 127.0.0.1 -uroot -p
answered May 4, 2021 at 18:04
user9869932user9869932
6,3533 gold badges53 silver badges48 bronze badges
For MySQL 5.5 on Windows 10
You can’t find the password as it is hashed in the table, so resetting it is the only option.
The solution of importing the new password script by .txt file, as offered by Lokesh kumar Chippada, didn’t work for me. I found that the command prompt just froze after initiating the import.
I added skip-grant-tables to the my.ini file as per the top the answer on this SO post by tonycoupland.
I was then able to login to mysql from the command line
$> mysql
and then in mysql
mysql> FLUSH PRIVILEGES;
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
See ‘B.3.3.2.3 Resetting the Root Password: Generic Instructions’ on mysql dev page. I have now removed skip-grant-tables from the my.ini file, and I can login as a root user using the new password I created.
answered May 6, 2022 at 2:16
Mr. JMr. J
1,3982 gold badges17 silver badges40 bronze badges
Go to phpMyAdmin > config.inc.php > $cfg[‘Servers’][$i][‘password’] = ”;
answered Oct 19, 2017 at 4:39
1
От автора: вы куда полезли через ограду, гражданин! На заборе написали пароль, чтоб не забыть. Так может вы с этой стороны написали? Да нет – это не мой написан. Ну, удачных поисков, а всем остальным я расскажу, как узнать пароль MySQL, не перелезая через чужие заборы.
Нет ничего проще!
Если у вас есть элементарные знания и навыки обращения с СУБД MySQL, и (главное) учетная запись администратора, то узнать пароли всех пользователей можно в два счета. Для этого можно использовать как программные оболочки, так и команды SQL.
Что представляет собой сервер СУБД? Это обычная база данных, содержащая в себе всю нужную для работы MySQL информацию. Здесь хранятся все настройки сервера, баз, сведения о плагинах, дате и времени, пользовательские учетные записи, их привилегиях и паролях. В контексте данной статьи нас интересует значения последних.
Чтобы узнать пароль MySQL, нужно зайти на сервер под своей учеткой администратора. Затем открыть системную базу данных с именем «mysql» и сделать выборку значений из таблицы user. Для наглядности все интересующие сведения (значения паролей) мы получим с помощью php MyAdmin.
Откроем системную БД, и посмотрим на содержимое нужной нам таблицы «сблизи»: в одном из ее столбцов прописаны все пароли. Как видите, ничего сложного и для этого нам понадобилось всего несколько минут. Но что это такое? Кто «стибрил» из таблицы понятные значения паролей и заменил их какой-то «абракадаброй»!
Спокойствие, и только спокойствие! Никто ничего «не стибрил», просто в таблице указываются уже хешированные пароли. А что у вас глаза такие удивленные? Сейчас все разложим «по полочкам».
Как происходит шифрование в MySQL
Дело в том, что данная СУБД использует собственный алгоритм шифрования паролей. Точнее, не шифрования, а хеширования. Из-за этого пока никто не придумал способа, как расшифровать пароли в MySQL.
Существуют различные алгоритмы хеширования, но если при этом будет использоваться криптографическая толь, то шансов получить значение пароля сводится почти к 0. Криптографическая соль – это дополнительная строка, которая присоединяется к первоначальному значению. В результате на выходе (после хеширования) получается почти «невзламываемый» пароль.
Для установки пароля СУБД использует функцию PASSWORD(). Она не возвращает значения, которое было послано ей на обработку. Поэтому использовать данную функцию для получения «читаемого» пароля не получится. Единственное, что можно сделать – это получить хешированную строку по первоначальному значению. Синтаксис запроса:
|
SELECT PASSWORD(‘значение_пароля’); |
Это же значение можно найти в системной таблице user (служебная база данных mysql), куда заносятся все учетные записи пользователей СУБД и хешированные значения паролей.
Путем перебора (если знать хотя бы примерную структуру значения) можно попытаться вспомнить забытый пароль. Но восстановить таким образом полностью неизвестное значение практически невозможно. Помните, что все описанные выше операции производятся под учетной записью администратора (root).
Использование обратимого шифрования
Узнать пароль MySQL, заданный системой по умолчанию для учетных записей сервера не удастся. Но это можно реализовать на уровне баз данных или даже таблиц. Многие движки и CMS, работающие на основе MySQL, имеют собственную (встроенную) систему аутентификации.
Например, если открыть таблицу БД WordPress, где движок сохраняет все данные о пользователях, то в столбце user_pass вы увидите такую же «абракадабру», как и в системной базе MySQL. Это говорит о том, что данная CMS также использует один из алгоритмов необратимого шифрования паролей (md5).
Но можно реализовать схему аутентификации на основе обратимых методов шифрования. Этим и займемся. Не будем сегодня огорчать себя «черным» цветом, и все запросы SQL выполним не через командную строку, а в phpMyAdmin. Для экспериментов я воспользуюсь тестовой БД «db1», в которой хранится одна таблица со «зверюшками» (animal). Добавим новую таблицу с именами хозяев зверей и паролем для входа в их «клетки» 🙂
Запустите программу, слева в списке выберите нужную БД. После этого перейдите во вкладку SQL и запустите на выполнение следующий код:
|
CREATE TABLE user_animal (id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(15) NOT NULL, pasword BLOB NOT NULL, PRIMARY KEY (id)); |
Теперь перед тем, как узнать пароль MySQL, давайте заполним созданную таблицу данными. Для этого мы снова используем запросы SQL, как поступают настоящие разработчики. Код запроса:
|
INSERT INTO user_animal (`name`,`pasword`) VALUES (‘holms’,‘dog’); |
Меняя значения в скобках после оператора VALUES, добавьте в таблицу еще несколько строк. Теперь сделаем из нее выборку данных:
|
SELECT * FROM user_animal; |
Вообще-то непорядок получается! Значение паролей всех пользователей видны «как на ладони». Сейчас мы их слегка «хешанем» с помощью встроенной функции AES_ENCRYPT. Она принимает 2 параметра: столбец для хеширования и значение «соли»:
|
UPDATE user_animal SET pasword=AES_ENCRYPT(pasword,‘animal’); |
Теперь давайте еще раз сделаем выборку из таблицы и посмотрим на ее результаты:
Как видите, одни «блобы» получились вместо паролей. Это говорит о том, что значения надежно хешированы, и без «соли» взломать их практически невозможно. Но мы с вами знаем, в чем «соль». Сейчас я вам покажу, как узнать пароли к базе данных MySQL, точнее вернуть их в более «читаемом» виде. Для этого используется другая функция — AES_DECRYPT(). Код запроса с ее «участием»:
|
SELECT AES_DECRYPT( pasword, ‘animal’ ) FROM user_animal; |
Результаты этой выборки:
Как видите, чтобы узнать пароль MySQL, нужно правильно понимать, в чем «соль» проблемы. А если лазить по чужим заборам, то можно запросто получить заряд соли (из ружья) в то место, на которое не стоит лишний раз искать приключений. Лучше это время потратить на изучение MySQL.
When installing MySQL, you may noticed that it does not ask for a password. This become troublesome when you want to connect your application to MySQL database. In this article, I will show you on how to find MySQL default password.
Well, straight forward, by default the username is root and there is no password setup. You may need to reset the root password.
Also, in case you have accidently put a password during installation process and can’t recall the password, you need to reset the password.
There is no way to view the password as it’s already written as hashed.
How To Reset MySQL Default Password
Windows OS
1. Ensure that your logged on account has administrator rights.
2. On the windows search box, search for services.msc and click to open.
3. Scroll down to all services with its status. Find MySQL services, right-click on it and click stop services.
4. Create a text file which contains the SQL statement in a single line as below:
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘MyNewPass’;
Change MyNewPass to your new desired password.
5. Save it to a text file. For example, save it as C:new-password.txt.
6. On the windows search box, search for cmd and click Run as administrator.
7. Start the MySQL with init_file system variable set to text file name created as below:
C:> cd “C:Program FilesMySQLMySQL Server 5.7bin”
C:> mysqld –init-file=C:\mysql-init.txt
You may replace your MySQL installation location after cd command.
Linux
1. Open terminal.
2. Stop MySQL server.
sudo service mysql stop
Or
sudo /usr/local/mysql/support-files/mysql.server stop
3. Start MySQL in safe mode.
sudo mysqld_safe –skip-grant-tables
4. Open another terminal and login as root by run below command.
mysql -u root
3. Once MySQL is logged in via terminal, run below queries.
UPDATE mysql.user SET authentication_string=PASSWORD(‘password’) WHERE User=’root’;
FLUSH PRIVILEGES;
which be looks like:
mysql>UPDATE mysql.user SET authentication_string=PASSWORD(‘password’) WHERE User=’root’;
FLUSH PRIVILEGES;
(Note: In MySQL 5.7, the password field in mysql.user table field was removed, now the field name is ‘authentication_string’.)
If you are using MySQL 5.7 and above you need to run command as below:
mysql>
use mysql;
mysql>update user set authentication_string=password(‘password’) where user=’root’;
FLUSH PRIVILEGES;
4. Now, you can exit MySQL safe mode.
mysqladmin shutdown
If you received error ‘access denied’ you can run below command with the new password:
mysqladmin -u root -p shutdown
5. Start MySQL service again by run below command.
sudo service mysql start
What If Still Failed To Reset MySQL Default Password?
If by using ALTER USER still not able to reset password, you may try to modify the user table directly by repeating the steps above and run below query:
UPDATE mysql.user
SET authentication_string = PASSWORD(‘MyNewPass’), password_expired = ‘N’
WHERE User = ‘root’ AND Host = ‘localhost’;
FLUSH PRIVILEGES;
Thanks for reading this article. I hope you find it helpful.
Узнать его нельзя, он зашифрован. Но можно сбросить.
// Останавливаем MySQL
sudo /etc/init.d/mysql stop
// Запускаем в режиме игнорирования прав и запретом доступа из сети
sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
// Входим как root
mysql -u root
// Получаем права на изменение паролей
FLUSH PRIVILEGES;
// Меняем пароль на новый. NEW_PASSWORD заменить на свой пароль
SET PASSWORD FOR root@'localhost' = PASSWORD('NEW_PASSWORD');
// Обновляем таблицы привелегий
FLUSH PRIVILEGES;
// Выходим из клиента и перезапускаем сервер в обычном режиме
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql startПароль хранится в базе. Сбрось его и все.
#!/bin/bash
mysql_password="новый_пароль"
/etc/init.d/mysql stop
echo "UPDATE mysql.user SET Password=PASSWORD('${mysql_password}') WHERE User='root';" > /tmp/mysql-set-pass
echo "FLUSH PRIVILEGES;" >> /tmp/mysql-set-pass
su -c "mysqld_safe --init-file=/tmp/mysql-set-pass &" mysql --shell=/bin/bash
sleep 5
echo "[mysql]
password = ${mysql_password}
[mysqldump]
password = ${mysql_password} " > /root/.my.cnf
/etc/init.d/mysql restart
mysql --password="" -e "SELECT 1;" 2>/dev/null && exit 1
mysql -e "SELECT 1;" 1>/dev/null || exit 1
rm /tmp/mysql-set-passСбрось скриптом на новый пароль.
Ever forgotten or just wanted to change your MySQL password? You’ve come to the right place – in this guide, we’ll walk you through how to set, change and recover your MySQL root password as well as other useful MySQL commands.
RunCloud uses MariaDB, an enhanced drop-in replacement for MySQL, as a database. MariaDB is a community-driven package by the original developers of MySQL, with the promise of future compatibility, hence, terminal commands and software tools for MySQL database can be used for MariaDB as well.
On RunCloud, there are a few ways to manage your MySQL server and database. You can manage either by accessing the server using a terminal and run the commands from there or you can safely install a MySQL database manager like phpMyAdmin as a web app using the RunCloud script installer.
Some administrators do not like to use a public-facing web app like phpMyAdmin because of the potential security vulnerabilities that it poses. But it remains popular as it is fairly simple to use, even for non-technical people, and it provides an easily accessible tool that can save you time when you just need to run some simple tasks.
However, if you want to avoid any of these risks then SSH is the way to go. If you have no prior experience managing MySQL, don’t worry, with RunCloud, it is as easy as ABC.
First, you need a terminal to work on. On a Windows machine, you can download the PuTTY software. If you are on a Mac or Linux, there is already a built-in Terminal app installed.
The MySQL root password that is displayed on your terminal screen at the end of the RunCloud agent installation on your server (example below is installation on DigitalOcean) is meant to be kept for safekeeping elsewhere, but if you forgot to copy it down, you can still retrieve it.
Pic1: Keep the MySQL password somewhere safe!
- Log in as root into your server through SSH (eg: puTTY/terminal/bash). Alternatively, run the commands that follow as
suorsudoas root user. This method is safer. - Navigate to /etc/mysql
/cd /etc/mysql
- View the file my.cnf either using the command cat or use any text editing software (vi/vim/nano).
cat my.cnf
Pic 2: Viewing the MySQL root password
How to Reset/Change MySQL Password
- Login to your server using SSH.These commands must be run with root user privileges. Although you can login to your server as root user, it is highly recommended not to. If you can, log in to your server as a system user with root privileges and run these commands as su or sudo commands using those administrative privileges. You can also close the incoming traffic for MySQL port (default port is 3306 unless you changed it to something else) temporarily at the firewall if you want to be extra careful.
- Stop the MySQL service.
service mysql stop
- Restart MySQL service in single-user mode and bypassing password authorization, by running the command below. Since the command
--skip-grant-tablesenables anyone to connect without a password, it is considered highly insecure, hence, all commands that follow must be done within a brief period. The ampersand (symbol &) at the end is required as it ensures that the command is being run parallel with the commands that follow afterwards.mysqld_safe --skip-grant-tables &
- Login into MySQL to connect.
mysql
- At the MariaDB> prompt, change the root password by running the commands below. Replace the NEWSTRONGPASSWORD with your chosen new strong password.
UPDATE mysql.user SET Password=password('NEWSTRONGPASSWORD') WHERE User='root'; - At the MariaDB> prompt, run the following commands.
FLUSH PRIVILEGES; exit;
- Stop the MySQL server using the following command. Before the MySQL server shuts down, you will be prompted to key in the root password. Key in your new root password.
mysqladmin -u root -p shutdown
- Start the MySQL server in normal mode.
service mysql start
- Verify that you are able to connect to MySQL by running the command mysql again. If you disable incoming traffic for the mysql port, please remember to enable it again.
- Lastly, you need to update the MySQL root password in the file /etc/mysql/my.cnf (as shown in Pic2) or you might not be able to add new database or user. You can use vi, vim or nano to edit the file.
nano /root/.my.cnf
Pic 3: Resetting the MySQL root password
Other Basic MySQL Commands
Even though you can run some basic MySQL commands in the RunCloud control panel, it would not hurt to know how to do the same tasks in command line. Below are some of the basic commands that should get you going.
When you run the commands below, replace the database name of your choice at the DatabaseNameHere.
To show the available databases.
SHOW DATABASES;
Creating a database.
CREATE DATABASE DatabaseNameHere;
Delete a database. You need a ‘drop’ privilege (or root user) to drop a database and the database must not be used (open for reading or writing by any user) at the particular time. Be careful when running this command as it will not prompt for confirmation and it will drop all the tables inside the database.
DROP DATABASE DatabaseNameHere;
To use or operate on a particular database. The MySQL prompt will change to reflect the database name that you have specified.
USE DatabaseNameHere;
To find out what database you are currently operating in, run the following command.
SELECT database();
To create a MySQL account. Change the UsernameHere and PasswordHere according to you preferences.
CREATE USER 'UsernameHere'@'localhost' IDENTIFIED BY 'PasswordHere';
To grant the user privileges. (This user will only be able to connect only from the local host)
GRANT ALL PRIVILEGES ON *.* TO 'UsernameHere'@'localhost' -> WITH GRANT OPTION;
These are just some of the basic commands that you can run using the command line. The same commands above can also be done using the RunCloud control panel. If you are new to administering a MySQL server using command line, getting used to these commands is a good start!
Categories: Tutorials, Server Management, Tips & Tricks
