Most common commands for MySQL and other forks


GENERAL NOTES
CLIENT MAGIC
mysql -uusername -ppassword -hhostname -Pport_num -Ddb_name Connect to database with common options (which are optional)
  • --user=username, -u username  Username for the connection
  • --password=password, -ppassword  Password for the connection. Notice that there is no space between -p option and password. If password is not given it's asked
  • --port=port_num, -P port_num  The TCP/IP port number to use for the connection
  • --host=host_name, -h host_name  Connect to the database server on the given host (or IP)
  • --execute='query', -e 'query'  Execute query and exit (back to shell)
  • (--database=db_name, -D db_name  The database to use, this option is not really needed)
mysql -uusername -p -e 'show databases;'Connect, execute command (show databases;) and return to shell example
mysql --versionShow distribution version

COMMON CLIENT COMMANDS
status;Get status information from the server
show databases;List databases
use db_name;Select/use specific database
show tables;Show tables in selected database
desc table_name;Show table structure (field name, type, parameters)
exit; or \qExit the MySQL client (Ctrl + D also works in *nix)

COMMON QUERIES
select * from table_name where column1='some1' order by column1 desc; Basic select example with where clause and the reverse (descending) order (the default sort order is ascending, with smallest values first)
select * from table_name where column1 in ('some1','%some2_','%some3%'); Select query with in clause and wildcards (% zero or more characters, _ a single character)
select column1, concact(column2, ' and ' , column3) as summary from table_name; Concat example to combine values from two columns and show in renamed column summary
select column1 from table_name1 where column2 in (select column1 from table_name2); Query within a query and using in clause
select a.column1, b.column1 from table_name1 a, table_name2 b where a.column2=b.column2 limit 0,5; Combining two (or more) tables and limit to constraint the number of rows (syntax limit offset, count)
insert into table_name (column1, column2) values (value1, value2); Insert values to the table
update table_name set column1=value1, column2=value2 where column1='value1'; Update a specific row in the table
delete from table_name [where column1='value1']; Delete command - without where clause all data will be deleted from given table
update user set password=PASSWORD("NEWPASSWORD") where User='foobar'; Update / change user foobar password. Remember to do flush privileges; to reload privileges!
drop database db_name; Delete the database named db_name
create database db_name; Create a database named db_name
GRANT ALL ON db_name.* TO username@localhost IDENTIFIED BY 'password'; Create username with password and grant all rights to the database named db_name

COMMON MAINTENANCE COMMANDS
mysqladmin -u root -p password 'newpassword' After installation you need to change the root password for first time
mysqladmin -u root -p'oldpassword' password 'newpassword' Change / update root password
sudo service mysql restartRestart mysql server (service name: mysqld in RHEL and likes, mysql in Debian and likes). Other options [start|stop|restart|reload|force-reload|status]
mysqldump -uroot -ppassword db_name > dumpfile.sqlBackup database to a dumpfile
mysqldump -uroot -ppassword --databases db_name1 db_name2 > dumpfile.sql Backup multiple databases to a dumpfile
mysqldump -uroot -ppassword --all-databases > dumpfile.sql Backup all databases to a dumpfile
mysqldump -uroot -ppassword db_name table_name > dumpfile.sql Backup single table to a dumpfile
mysql -uroot -ppassword db_name < dumpfile.sqlRestore database from dumpfile. Please notice that the empty database needs to exists prior to restore (create database db_name;)
mysqldump -uroot -ppassword db_name | mysql -uroot -ppassword db_name --host=remote-server -C db_nameBackup local database and restore to remote server with one liner