Here is the list of commands for MySQL that will be evry handy while using MySQL. Over the period of time, you will remember these commands but to get started, you can use this as a cheat sheet for MySQL commands.
Connecting to MySQL
- Check status of MySQL
$ systemctl status mysql
- Start and Stop MySQL Server using
$ sudo systemctl start mysql
$ sudo systemctl stop mysql
- Start MySQL without admin privileges
sudo mysqld_safe --skip-grant-tables --skip-networking &
- After installation defact userid and password can be viewed by using following command
sudo cat /etc/mysql/debian.cnf
[client]
host = localhost
user = debian-sys-maint
password = XXX
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = XXX
socket = /var/run/mysqld/mysqld.sock- Login to MySQL Database from command line. Once authentication is completed successfully, mysql> prompt will appear.
$mysql -u USERNAME -p PASSWORD
OR
$ mysql --host=localhost --user=USERNAME --password PASSWORD
If you want to connect to any remote MySQL server, replace ‘localhost’ with remote db address name and add port parameter.
$ mysql --host=remote.example.com --port=13306 --user=USERNAME --password PASSWORD
Commands to be used in mysql> prompt:
Use following command to get list of databases
mysql> show databases;
Use following command to create database
mysql> create database <newDatabaseName>;
Create new user
mysql>CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
grant access to newly created user
mysql> grant create, delete, insert, select, update, alter,references ON <databaseName>.* TO 'usernmae'@'localhost';
Use following command to delete or drop database
mysql> drop database DatabaseName;
Use following command to get list of users
mysql> SELECT User FROM mysql.user;
To check the access level of the user
mysql>SHOW GRANTS FOR 'root'@'localhost';
To use specific database name use following command
mysql>use DBNAME
To export database
$mysqldump -u [username] -p [databaseName] > [databaseName].sql
To import database
$mysql -u [username] -p newdatabase < [databaseName].sql