By using a MySQL database such as phpMyAdmin on your web server, you can manage your database. Though phpMyAdmin poses security threats and thus some prefer SSH which you can use on a Windows machine through the PuTTY software (terminal) which you have to download. On Mac or Linux, there is a built-in terminal see below.
The example above shows a user logging into MySQL with the root password. Always keep the password safe. In case you forget it (hence why you are reading this), you can retrieve it as explained below.
- Using SSH, log into your server as root. For a safer method, run the following commands as a root user: su/sudo.
- Run the command
cd /etc/my.cnfto access the MySQL config file
- Using command or any text editor, view the my.cnf file by running
Resetting MySQL root password
- Login to your server using root user privileges. Use SSH.
- Stop the database service by running
service mysql stop
- Run the command –skip-grant-tables to bypass password authorization after restarting your database, though this is considered highly insecure; thus next commands have to be within a brief period. Use the symbol ‘&’ at the end to ensure that the command runs parallel with commands coming after:
mysqld_safe --skip-grant-tables &
- Login to your database
mysql -u root
- At the prompt, change the password by running
UPDATE mysql.user SET Password=password('NEWSTRONGPASSWORD') WHERE User='root';replace with password of your choice
- Next, run the command at the MariaDB> prompt
- As the database server shuts down, you will be asked to type the root password at which point you will key in the new password
mysqladmin -u root -p shutdown
- Start MySQL normally
service mysql start
- Update the MySQL root password in the file /etc/my.cnf to be able to add new databases or users. This is only needed on certain versions. Assuming your file does not have the password in clear text, like figure 2 above, you can ignore this step.
Other Basic MySQL Commands
See available databases, so you can see a list.
Creating a database, so you can build your tables.
CREATE DATABASE DatabaseNameHere;
Delete a database, when you no longer need it.
DROP DATABASE DatabaseNameHere;
Use a particular database, to run your SQL statements against
Find out the database you are currently using, so you don’t run a query on the wrong one!
Create a MySQL account, so you can assign a user to access a database
CREATE USER 'UsernameHere'@'localhost' IDENTIFIED BY 'PasswordHere';
Grant the user privileges, allowing the user to perform a role.
GRANT ALL PRIVILEGES ON DatabaseNameHere.* TO 'UsernameHere'@'localhost';
Find more information at www.mariadb.org