Home » Msyql » Mysql Command Line.

Mysql Command Line.

Mysql Command Line.

# [mysql dir]/bin/mysql -h hostname -u root -p

Create a database on the sql server.

mysql> create database [databasename];

List all databases on the sql server.

mysql> show databases;

Switch to a database.

mysql> use [db name];

To see all the tables in the db.

mysql> show tables;

To see database’s field formats.

mysql> describe [table name];

To delete a db.

mysql> drop database [database name];

To delete a table.

mysql> drop table [table name];

Show all data in a table.

mysql> SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

Delete a row(s) from a table.

mysql> DELETE from [table name] where [field name] = ‘whatever’;

Update database permissions/privilages.

mysql> flush privileges;

Add a new column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

To Create user.

mysql>CREATE USER ‘username’@’%’ IDENTIFIED BY ‘password’;

To Grant privilege.

mysql>GRANT ALL ON `database`.* TO ‘username’@’localhost’ IDENTIFIED BY ‘password’;
mysql>GRANT ALL ON `database`.* TO ‘username’@’serveripaddress’ IDENTIFIED BY ‘password’;

To enable remote access

mysql>GRANT ALL ON `database`.* TO ‘username’@’%’ IDENTIFIED BY ‘password’;

Load a CSV file into a table.

mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);

Dump all databases for backup. Backup file is sql commands to recreate all db’s.

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

Dump one database for backup.

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

Dump a table from a database.

#mysqldump -c -u username -ppassword databasename tablename >

/tmp/databasename.tablename.sql
Restore database (or database table) from backup.

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

About

I am founder and webmaster of www.linuxpcfix.com and working as a Sr. Linux Administrator (Expertise on Linux/Unix & Cloud Server) and have been in the industry from last 7 years.

Leave a Reply

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

*
*

Time limit is exhausted. Please reload the CAPTCHA.

Categorized Tag Cloud