Invoke mysql_secure installation without arguments to improve mysql installation security. This utility enables you to improve the security of your MySQL installation in the following ways:
– set a password for root accounts
– remove root accounts that are accessible from outside the local host
– remove anonymous-user accounts
– remove the test database, which by default can be accessed by anonymous users
Initiate a connection of MySQL
mysql -u root -p
-u means that we are going to connect with a username root
-p means that we will enter username’s password
To list all databases on a MySQL server
show databases;
Switch to a specific database using the use statement
use databasename;
To list tables in a MySQL database
show tables;
To get information about a specified table
describe tablename;
The COUNT(*) function represents the count of all rows present in the table
SELECT COUNT(*) FROM table_name;
Leave the MySQL prompt
exit
Create a new database
create database databasename;
Delete an existing SQL database
drop database databasename;
Export a single MySQL database in the form of a .sql file.
The -p option prompts for the password of the MySQL user
mysqldump -u username -p databasename > databasefilename.sql
Import an existing dump file into MySQL
mysql -u username -p databasename < databasefilename.sql
Show MySQL users and hosts they are allowed to connect from:
select user, host from mysql.user;
Show all MySQL users:
select user from mysql.user;
Create a user. Be sure to change user to your preferred username and password to a strong password of your choosing.
create user ‘user’@’localhost’ identified by ‘password’;
Grant your MySQL user all privileges. Anyone with access to this MySQL user will have complete control over every database on the server.
grant all privileges on *.* to ‘user’@’host’ with grant option;
Reload the grant tables’ privileges, ensuring that any changes made to user permissions are immediately applied
flush privileges;
List user and grants
show grants;
You’ll be prompted for a password. If you get an Access denied error, your username or password are incorrect. Otherwise, all the databases will show that the specified user has access to.
mysqlshow -u database_username -p
Check the MySQL status on a CentOS 9 server
systemctl status mysqld
Restarting MySQL on a CentOS 9 server
systemctl restart mysqld
Check MySQL version
mysql -V
Clear the MySQL screen console window
system clear;
Test connection. If the connection is successful, you see the message: Connected to <host_IP>. If the connection fails, you see the message: Unable to connect to remote host. The default port of MySQL is 3306
telnet host_IP port_number
Find the MySQL configuration file. The primary configuration file for the MySQL database server is called my.cnf
find ‘/’ -name “my.cnf”
To learn MySQL more quickly and effectively, use the Sakila sample database available on mysql.com. The Sakila sample database is one of the most widely used sample databases. Other sample databases on mysql.com include the world sample database and employees sample database.
How to read .mysql_history containing \040
sed "s/\\\040/ /g" < .mysql_history