MySql console – Most used commands

1. Login

mysql -u USERNAME -p

At the Enter Password prompt, type your password. When you type the correct password, the mysql> prompt appears.

2. List all databases

show databases;

3. Access a specific database

use DBNAME

4. List all tables

show tables;

5. The path to root config file

/etc/mysql/conf.d/my.cnf

6. Create user

To create a database user, type the following command. Replace username with the user you want to create, and replace password with the user’s password:

grant all privileges on *.* to 'user_name'@'localhost' identified by 'password';

For remote connection use % for domain name

 grant all privileges on *.* to 'user_name'@'%' identified by 'password';

7. Create and restore backup from mysqldump

mysqldump -u user_name -p database_name > /tmp/dump.sql
mysql -u user_name -p database_name < /tmp/dump.sql

8. Allow remote connection

Edit the config file /etc/mysql/conf.d/my.cnf by commenting the bind_address

#bind-address           = 127.0.0.1

Then restart mysql server

/etc/init.d/mysql restart

Then open the port 3306 on the machine

sudo iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

And test if from the remote computer

nc -z -v ip_address 3306

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.