y<\/strong> to finish the setup. You have successfully changed the root user password.<\/li>Checking Current MySQL Version<\/h3>
Once you’ve MySQL installed on CentOS 9 Sream, you can test if everything was set up correctly by checking its version. Type in the following command:<\/p>
mysql -u root -p\n<\/pre>Enter the root password that you’ve created, and the response will be as below:<\/p>
Welcome to the MySQL monitor. Commands end with ; or g.\nYour MySQL connection id is 22\nServer version: 8.0.20\n\nCopyright (c) 2000, 2020, Oracle and\/or its affiliates. All rights reserved.\n\nOracle is a registered trademark of Oracle Corporation and\/or its affiliates. Other names may be trademarks of their respective owners.\n\nType 'help;' or 'h' for help. Type 'c' to clear the current input statement.\n<\/pre>Resetting the MySQL Root Password<\/h3>
If you want to reset your password, the process is pretty straightforward. Follow the steps below to change your MySQL root password:<\/p>
\n- Stop the MySQL server with this command:\n
sudo systemctl stop mysqld<\/pre>\n<\/li>\n\n\n\n
- Restart MySQL in safe mode<\/strong>, or without a password by typing in:\n
sudo mysqld_safe --skip-grant-tables<\/pre>\n<\/li>\n\n\n\n
- Press ENTER<\/strong> if the MySQL server has started but seems to be hanging.<\/li>\n\n\n\n
- Use the following command to reconnect to MySQL as root:\n
mysql -uroot<\/pre>\n<\/li>\n\n\n\n
- To change the root password, use the following commands:\n
USE MYSQL;\nUPDATE USER SET PASSWORD=PASSWORD(“newpassword”) WHERE USER=’root’;\nFLUSH PRIVILEGES;\nEXIT<\/pre>\n<\/li>\n\n\n\n
- Replace the newpassword<\/strong> with a strong password of your own.<\/li>\n\n\n\n
- Finally, restart MySQL using:\n
sudo systemctl start mysqld<\/pre>\n<\/li>\n\n\n\n
- You can now login to MySQL with your new password.<\/li>\n<\/ol>
Creating a New MySQL User, Database<\/h3>
As the root user in MySQL, you have full access to all of the databases.<\/p>
However, if you work with a team, there are cases where you might need to apply some restrictions. You’d either have to build a new database or create users with custom permissions.<\/p>
Here’s how to quickly solve this issue:<\/p>
\n- Use the following command to create a new database:\n
CREATE DATABASE newdb<\/pre>\n<\/li>\n\n\n\n
- Change newdb<\/strong> with the name of the database.<\/li>\n\n\n\n
- Create a new user by typing the following line:\n
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'<\/pre>\n<\/li>\n\n\n\n
- Change the variables for both username<\/strong> and password<\/strong> to create your new user.<\/li>\n\n\n\n
- If you want to delete a certain user instead, use the command:\n
DROP USER ‘username’@‘localhost’<\/pre>\n<\/li>\n<\/ol>Managing MySQL User Permissions<\/h3>
Grant the new user access to a database with by typing in:<\/p>
GRANT ALL PRIVILEGES ON newdb.* TO 'username'@'localhost'\n<\/pre>You can also grant privileges individually, including:<\/p>
\n- SELECT<\/strong> – users can read through the database using the select command<\/li>\n\n\n\n
- CREATE<\/strong> – they can generate new tables<\/li>\n\n\n\n
- DROP<\/strong> – allows users to remove tables<\/li>\n\n\n\n
- DELETE<\/strong> – users can take out rows from tables<\/li>\n\n\n\n
- INSERT<\/strong> – lets users add in rows into tables<\/li>\n\n\n\n
- UPDATE<\/strong> – enable them to update the rows<\/li>\n\n\n\n
- GRANT OPTION<\/strong> – they can grant or remove the privileges of other users<\/li>\n<\/ul>
For example, to grant the CREATE<\/strong> privilege, you’d need to type:<\/p>GRANT CREATE ON newdb.* TO 'username'@'localhost'\n<\/pre>On the other hand, if you want to remove someone’s access, use the following:<\/p>
REVOKE permission_type ON newdb.* TO 'username'@'localhost'\n<\/pre>You can also check what current privileges a user has:<\/p>
SHOW GRANTS username\n<\/pre>Finally, once you’ve finished managing your users, it’s a good idea to reset all the privileges by using this command for your changes to take effect:<\/p>
FLUSH PRIVILEGES\n<\/pre>Other Useful MySQL Commands<\/h2>
MySQL also has a list of other helpful commands. Simply enter h<\/strong> or help<\/strong> to view the list shown below:<\/p>List of all MySQL commands:\nNote that all text commands must be first on line and end with ';'\n? (?) Synonym for `help'.\nclear (c) Clear command.\nconnect (r) Reconnect to the server. Optional arguments are db and host.\ndelimiter (d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.\nedit (e) Edit command with $EDITOR.\nego (G) Send command to mysql server, display result vertically.\nexit (q) Exit mysql. Same as quit.\ngo (g) Send command to mysql server.\nhelp (h) Display this help.\nnopager (n) Disable pager, print to stdout.\nnotee (t) Don't write into outfile.\npager (P) Set PAGER [to_pager]. Print the query results via PAGER.\nprint (p) Print current command.\nprompt (R) Change your mysql prompt.\nquit (q) Quit mysql.\nrehash (#) Rebuild completion hash.\nsource (.) Execute an SQL script file. Takes a file name as an argument.\nstatus (s) Get status information from the server.\nsystem (!) Execute a system shell command.\ntee (T) Set outfile [to_outfile]. Append everything into given outfile.\nuse (u) Use another database. Takes database name as argument.\ncharset (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.\nwarnings (W) Show warnings after every statement.\nnowarning (w) Don't show warnings after every statement.\n\nFor server side help, type 'help contents'\nmysql>\n<\/pre>Conclusion<\/h2>
You have learned how to install MySQL on a CentOS 9 Stream server. By installing MySQL, you are able to store your databases and manage them efficiently on your server.<\/p>
Be sure to check out our other VPS hosting tutorials<\/a> and leave a comment down below if you have any questions.<\/p>\n","protected":false},"excerpt":{"rendered":"MySQL is one of the most widely used database management systems for websites and server applications. It’s an essential tool that every webmaster should learn. That’s why in this tutorial, we will show you how to install MySQL on CentOS 9 Stream through an SSH connection. How to Install MySQL on a CentOS 9 Stream Server? […]<\/p>\n
Read More…<\/a><\/p>\n","protected":false},"author":27,"featured_media":5160,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rank_math_title":"","rank_math_description":"","rank_math_focus_keyword":"","footnotes":""},"categories":[22642,22640],"tags":[],"class_list":["post-725","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-pre-installed-applications","category-vps"],"hreflangs":[{"locale":"en-US","link":"https:\/\/www.hostinger.com\/tutorials\/how-to-install-mysql-on-centos","default":0},{"locale":"es-ES","link":"https:\/\/www.hostinger.com\/es\/tutoriales\/instalar-mysql-centos","default":0},{"locale":"id-ID","link":"https:\/\/www.hostinger.com\/id\/tutorial\/cara-install-mysql","default":0},{"locale":"en-UK","link":"https:\/\/www.hostinger.com\/uk\/tutorials\/how-to-install-mysql-on-centos","default":0},{"locale":"en-MY","link":"https:\/\/www.hostinger.com\/my\/tutorials\/how-to-install-mysql-on-centos","default":0},{"locale":"en-PH","link":"https:\/\/www.hostinger.com\/ph\/tutorials\/how-to-install-mysql-on-centos","default":0},{"locale":"es-MX","link":"https:\/\/www.hostinger.com\/mx\/tutoriales\/instalar-mysql-centos","default":0},{"locale":"es-CO","link":"https:\/\/www.hostinger.com\/co\/tutoriales\/instalar-mysql-centos","default":0},{"locale":"es-AR","link":"https:\/\/www.hostinger.com\/ar\/tutoriales\/instalar-mysql-centos","default":0},{"locale":"en-IN","link":"https:\/\/www.hostinger.com\/in\/tutorials\/how-to-install-mysql-on-centos","default":0},{"locale":"en-CA","link":"https:\/\/www.hostinger.com\/ca\/tutorials\/how-to-install-mysql-on-centos","default":0},{"locale":"en-AU","link":"https:\/\/www.hostinger.com\/au\/tutorials\/how-to-install-mysql-on-centos","default":0},{"locale":"en-NG","link":"https:\/\/www.hostinger.com\/ng\/tutorials\/how-to-install-mysql-on-centos","default":0}],"_links":{"self":[{"href":"https:\/\/www.hostinger.com\/in\/tutorials\/wp-json\/wp\/v2\/posts\/725","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.hostinger.com\/in\/tutorials\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.hostinger.com\/in\/tutorials\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.hostinger.com\/in\/tutorials\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.hostinger.com\/in\/tutorials\/wp-json\/wp\/v2\/comments?post=725"}],"version-history":[{"count":32,"href":"https:\/\/www.hostinger.com\/in\/tutorials\/wp-json\/wp\/v2\/posts\/725\/revisions"}],"predecessor-version":[{"id":124636,"href":"https:\/\/www.hostinger.com\/in\/tutorials\/wp-json\/wp\/v2\/posts\/725\/revisions\/124636"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.hostinger.com\/in\/tutorials\/wp-json\/wp\/v2\/media\/5160"}],"wp:attachment":[{"href":"https:\/\/www.hostinger.com\/in\/tutorials\/wp-json\/wp\/v2\/media?parent=725"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.hostinger.com\/in\/tutorials\/wp-json\/wp\/v2\/categories?post=725"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.hostinger.com\/in\/tutorials\/wp-json\/wp\/v2\/tags?post=725"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/www.hostinger.com\/tutorials\/wp-content\/uploads\/sites\/2\/2022\/11\/Linux-cheat-sheet-1024x283.png\")
<\/a><\/figure><\/div>![\"Downloading](\"\/tutorials\/wp-content\/uploads\/sites\/2\/2016\/12\/download-mysql-1.png\")
<\/a><\/li>\n\n\n\n![\"Install](\"\/tutorials\/wp-content\/uploads\/sites\/2\/2016\/12\/install-mysql.png\")
<\/a><\/li>\n\n\n\n![\"Checking](\"\/tutorials\/wp-content\/uploads\/sites\/2\/2016\/12\/status-mysql.png\")
<\/a><\/li>\n<\/ol>![\"Kodee](\"https:\/\/www.hostinger.com\/tutorials\/wp-content\/uploads\/sites\/2\/2016\/12\/hpanel-vps-kodee-mysql-1024x328.png\")