mysql basic commands

Basic commands

login to mysql (from linux shell)

mysql -u root -p
mysql -h hostname -u root -p

create database

mysql> create database database_name;

show all databases

mysql> show databases;

use a database.

mysql> use database_name;

To see all the tables in the db.

mysql> show tables;

delete a database

mysql> drop database database_name;

delete a table.

mysql> drop table table_name;

Show all data in a table.

mysql> SELECT * FROM table_name;

privilages

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;

Change a users password from unix shell.

mysqladmin -u username -h hostname -p password 'new-password'

Set a root password if there is on root password.

mysqladmin -u root password newpassword

Update a root password.

mysqladmin -u root -p oldpassword newpassword

Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs.

mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by 'passwd';
mysql> flush privileges;

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

backup/restore

Dump (backup) all databases.

mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup.

mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Restore database from dump file.

mysql -u username -ppassword databasename < /tmp/databasename.sql

Repair

Check and repair all tables in all database

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

One thought on “mysql basic commands

Comments are closed.