Debug School

rakesh kumar
rakesh kumar

Posted on • Edited on

mysql and sql command cheatsheet

Normal Login (Without Skip Grant Tables)

mysql -u root -p
Enter fullscreen mode Exit fullscreen mode 
Note: for password see project env file
Enter fullscreen mode Exit fullscreen mode

Safe Mode Login (With --skip-grant-tables)

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables &
mysql -u root
Enter fullscreen mode Exit fullscreen mode

set password After Logging In With Skip Grant Tables

ALTER USER 'root'@'localhost' IDENTIFIED BY 'yourStrongPassword';
FLUSH PRIVILEGES
sudo service mysql restart

ALTER USER 'wizbrandos'@'localhost' IDENTIFIED BY 'myphpmyadminpassword';
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

How to Check and Remove Permanent --skip-grant-ables=

sudo nano /opt/lampp/etc/my.cnf
skip-grant-tables
Enter fullscreen mode Exit fullscreen mode

✅ If found, comment it out:
How to Create a New Admin User

mysql -u root -p
Run:
CREATE USER 'adminuser'@'localhost' IDENTIFIED BY 'yourSecurePassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'adminuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

How to Create a New App User

mysql -u root -p
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'AppPassword!';
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

how to move root to Adminuser or Appuser

# For admin user
mysql -u adminuser -p
# For normal user
mysql -u appuser -p
Enter fullscreen mode Exit fullscreen mode

Command to see root’s privileges ,adminuser and appuser privilige

SHOW GRANTS FOR 'root'@'localhost';
SHOW GRANTS FOR 'adminuser'@'localhost';
SHOW GRANTS FOR 'appuser'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
Enter fullscreen mode Exit fullscreen mode

GRANT ALL PRIVILEGES to all host

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'%' IDENTIFIED BY "Password";
Enter fullscreen mode Exit fullscreen mode

GRANT ALL PRIVILEGES to specific host

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'host' IDENTIFIED BY "password";
Enter fullscreen mode Exit fullscreen mode

how we can disable remote root login

mysql -u root -p
SELECT User, Host FROM mysql.user WHERE User='root';
DELETE FROM mysql.user WHERE User='root' AND Host!='localhost';
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

Explicitly update root to localhost only:

UPDATE mysql.user SET Host='localhost' WHERE User='root';
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

How to reload the grant tables in memory
How to make effective changes you made to user accounts or privileges

FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

mysql access denied for new user

grant all privileges on hellodb to hellouser identified by '123';
Enter fullscreen mode Exit fullscreen mode

ERROR 1045 (28000): Access denied for user 'hellouser'@'localhost' (using password: YES)

grant all privileges on hellodb.* to 'hellouser'@'localhost' identified by '123';
Enter fullscreen mode Exit fullscreen mode

To take a backup of MySQL databases or databases

# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
mysqldump -u adminuser -pMyAdminPassword mydatabase > mydatabase_backup.sql
Enter fullscreen mode Exit fullscreen mode

Backup Multiple MySQL Databases

mysqldump -u [username] -p --databases db1 db2 db3 > multi_db_backup.sql
mysqldump -u adminuser -p --databases salesdb hrdb inventorydb > selected_databases_backup.sql
Enter fullscreen mode Exit fullscreen mode

Backup All MySQL Databases

mysqldump -u [username] -p --all-databases > all_databases_backup.sql

mysqldump -u root -p --all-databases > all_databases_backup.sql
Enter fullscreen mode Exit fullscreen mode

Backup MySQL Database Structure Only (No Data)

mysqldump -u [username] -p -d [database_name] > db_structure.sql
mysqldump -u adminuser -p -d salesdb > salesdb_structure.sql
Enter fullscreen mode Exit fullscreen mode

Backup MySQL Database Data Only (No Structure)

mysqldump -u [username] -p --no-create-info [database_name] > db_data_only.sql

mysqldump -u adminuser -p --no-create-info salesdb > salesdb_data_only.sql
Enter fullscreen mode Exit fullscreen mode

Backup a Single Table of a Database

mysqldump -u [username] -p [database_name] [table_name] > table_backup.sql
mysqldump -u adminuser -p salesdb customers > customers_table_backup.sql
Enter fullscreen mode Exit fullscreen mode

Backup Multiple Tables of a Database

mysqldump -u [username] -p [database_name] table1 table2 > tables_backup.sql

mysqldump -u adminuser -p salesdb customers orders > salesdb_customers_orders_backup.sql
Enter fullscreen mode Exit fullscreen mode

Backup a Remote MySQL Database

mysqldump -h [remote_host] -u [username] -p [database_name] > remote_db_backup.sql
mysqldump -h 192.168.1.100 -u adminuser -p salesdb > remote_salesdb_backup.sql
Enter fullscreen mode Exit fullscreen mode

Import All MySQL Databases at One Time

mysql -u [username] -p < all_databases_backup.sql
mysql -u root -p < all_databases_backup.sql
Enter fullscreen mode Exit fullscreen mode

Export Each Database to a Separate File (Script Example)

for db in $(mysql -u root -pYourPassword -e 'SHOW DATABASES;' | grep -Ev '(Database|information_schema|performance_schema|mysql|sys)')
do
  mysqldump -u root -pYourPassword $db > ${db}_backup.sql
done
Enter fullscreen mode Exit fullscreen mode

Display available databases

SHOW DATABASES;
Enter fullscreen mode Exit fullscreen mode

Display all tables in a database


USE your_database_name;
SHOW TABLES;
Enter fullscreen mode Exit fullscreen mode

Display tables along with number of columns in a database

SELECT TABLE_NAME, COUNT(*) AS column_count
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
GROUP BY TABLE_NAME;
Enter fullscreen mode Exit fullscreen mode

Display total number of columns and rows of all tables in a database

SELECT COUNT(*) AS total_columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name';
Enter fullscreen mode Exit fullscreen mode

Rows in each table:

SELECT TABLE_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';
Enter fullscreen mode Exit fullscreen mode

Total rows in all tables:

SELECT SUM(TABLE_ROWS) AS total_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';
Enter fullscreen mode Exit fullscreen mode
  1. Display all columns of a table 
SHOW COLUMNS FROM your_table_name;
-- or
DESCRIBE your_table_name;
Shows column names, types, and details.
Enter fullscreen mode Exit fullscreen mode
  1. Display details about a specific column from a table 
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_NAME = 'your_table_name'
  AND COLUMN_NAME = 'your_column_name';
Enter fullscreen mode Exit fullscreen mode

How to Install mysqlshow

sudo apt install mariadb-client
Enter fullscreen mode Exit fullscreen mode 
mysqlshow -u root -p
mysqlshow -u root -p your_database_name
Enter fullscreen mode Exit fullscreen mode

Display all tables in a database using mysqlshow

# mysqlshow  -u root -ptmppassword sugarcrm
Enter fullscreen mode Exit fullscreen mode

set MYSQL environment variable in Ubuntu

mysql -u root -p
In root vi .bashsrc
export PATH=$PATH:/opt/lampp/bin
Enter fullscreen mode Exit fullscreen mode

How to check the mysql file location

> which mysql
> locate mysql
Enter fullscreen mode Exit fullscreen mode

ERROR:MySQL server has gone away
edit and increase size of [mysqld]
max_allowed_packet=16M

guide-to-logging-in-to-the-mysql-shell
managing-mariadb-root-password-and-connection-behavior-in-xampp-ubuntu

Top comments (0)