| 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 --version | Show distribution version |
| 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 |
| 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 restart | Restart 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.sql | Backup 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.sql | Restore 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_name | Backup local database and restore to remote server with one liner |