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’;
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
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 since more than 14 years.
