Networking, Programming and Graphics - Tutorials
ONLINEHOWTO.net Tutorials Category
Home >> Server Management >> Database Servers >> MySQL >>

MySQL commands, and MySQL usage

Type: Terms
Networking, Programming and Graphics - Tutorials
Level: Expert Networking, Programming and Graphics - Tutorials Networking, Programming and Graphics - Tutorials Networking, Programming and Graphics - Tutorials 
Networking, Programming and Graphics - Tutorials
Date: 2010-Jan-30
Networking, Programming and Graphics - Tutorials
Visited: 635 times
Networking, Programming and Graphics - Tutorials
Rating: Networking, Programming and Graphics - Tutorials
Networking, Programming and Graphics - Tutorials
Published: Tony Potter

To be honest this won’t be a tutorial, but a list with useful MySQL commands common used to create, mange and maintain MySQL databases, as well as many PHP and Perl API functions you can use to interface with MySQL.
Certainty they are command line commands, but some of them can be used in PHPMyAdmin also.

Let’s start with the “first” MySQL command used for connecting to a database server:
# [mysql dir]/bin/mysql -h hostname -u root -p
Create a database command on SQL server.
mysql> create database [databasename];
List all databases on a SQL server.
mysql> show databases;
Switch (start using) a database.
mysql> use [db name];
The next will show all the tables in a database.
mysql> show tables;
Let’s print database's field formats.
mysql> describe [table name];
Here is how to delete a database.
mysql> drop database [database name];
This is how to delete a MySQL database table.
mysql> drop table [table name];
Show all data in a table.
mysql> SELECT * FROM [table name];
Shows the columns and column information that belongs to a particular table.
mysql> show columns from [table name];
Show certain selected rows with the value "anything".
mysql> SELECT * FROM [table name] WHERE [field name] = "anything";
Show all records containing the name "SAM" AND social security number '768129873123'.
mysql> SELECT * FROM [table name] WHERE name = "SAM" AND social_security_number = '768129873123';
Show all records not containing the name " SAM " AND the social security number '768129873123' order by the social_security_number field.
mysql> SELECT * FROM [table name] WHERE name != "SAM" AND social_security_number = '768129873123' order by social_security_number;
Show all records starting with the letters 'SAM' AND the social security number '768129873123'.
mysql> SELECT * FROM [table name] WHERE name like "SAM%" AND social_security_number = '768129873123';
Show all records starting with the letters 'SAM' AND the social security number '768129873123' limit to records 1 through 5.
mysql> SELECT * FROM [table name] WHERE name like "SAM%" AND social_security_number = '768129873123' limit 1,5;
Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.
mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";
Show unique records.
mysql> SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted in an ascending (asc) or descending (desc).
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Return number of rows.
mysql> SELECT COUNT(*) FROM [table name];
Sum column.
mysql> SELECT SUM(*) FROM [table name];
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;
Chain of commands Creating a new user. Login as root. Switch to the MySQL database. 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 MySQL users password from unix shell.
# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'
Change a users password from MySQL prompt. Login as root. Set the password. Update privileges.
# mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;
Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.
# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start
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 "SAM" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL database. Give privs. Update privileges.
# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to SAM@localhost identified by 'passwd';
mysql> flush privileges;
Give user privileges for a database. Log-in as root. Switch to the MySQL database. Grant privileges. Update privileges.
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;

or

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;
To update info already in a table.
mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';
Delete a row(s) from MySQL table.
mysql> DELETE from [table name] where [field name] = 'anything';
Update database permissions/privilages.
mysql> flush privileges;
Delete a column.
mysql> alter table [table name] drop column [column name];
Add a new column to database.
mysql> alter table [table name] add column [new column name] varchar (20);
Change column name.
mysql> alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes.
mysql> alter table [table name] add unique ([column name]);
Make a column bigger.
mysql> alter table [table name] modify [column name] VARCHAR(3);
Delete unique from table.
mysql> alter table [table name] drop index [colmn name];
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 databases.
# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
Dump one database for backup.
# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
Dump a table from a database.
# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
Restore database (or database table) from backup.
# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
Create Table " first way.
mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
Create Table " second way .
mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');
MySQL Flow Control Functions
CASE

IF

IFNULL

NULLIF
MySQL Command-Line Utilities
comp_err

isamchk

make_binary_distribution

msql2mysql

my_print_defaults

myisamchk

myisamlog

myisampack

mysqlaccess

mysqladmin

mysqlbinlog

mysqlbug

mysqlcheck

mysqldump

mysqldumpslow

mysqlhotcopy

mysqlimport

mysqlshow

perror
This list of MySQL commands is growing and the next command, rather requested by one of our readers will be about how to migrate MySQL database from one server to another with one MySQL command
mysqldump --add-drop-table -h OLDDBHOST -u UNAME -p'PASSWORD' DBNAME | mysql -h NEWDBHOST -u UNAME -p'PASSWORD' DBNAME
Rate this tutorial:                    
Post Comment

    • Dave K
      ( davidkaplan [ at ] sbcglobal . net ),

      posted on 2010-Mar-16 | 04:47:45 AM
      In this string:

      grant usage on *.* to SAM@localhost identified by 'passwd';

      I was wandering if I want to set privileges to for all interfaces instead for localhost only how is the right syntax?
    • Tonny Potter

      posted on 2010-Mar-16 | 06:29:06 AM
      The command will look like this one:

      grant usage on *.* to SAM@'%' identified by 'passwd';

      Take in mind that this is not really clever looked from security side.

      Better use this MySQL command:

      grant usage on *.* to SAM@'192.168.0.0/24' identified by 'passwd';

      Which will add access to certain IP range.
    • Sam Raahan

      posted on 2010-Mar-24 | 10:41:47 AM
      Hi. Very useful MySQL commands. I bookmarked it. For sure I will use in the future. But, I have a question. How to migrate a MySQL database without a dump file directly to other MySQL server?
    • Tonny

      posted on 2010-Mar-24 | 10:55:22 AM
      Sam, if you want to move the database from one server to another without dump you can copy the files straight from one MySQL server to another, make sure you have absolutely the same. For example on some Linux systems the MySQL files are under /var/lib/mysql and if you just copy them to another server in the same location it will start working.

      If you are asking for a command which moves/migrates MySQL database directly to another server you may find useful this command:

      mysqldump --add-drop-table -h OLDDBHOST -u UNAME -p'PASSWORD' DBNAME | mysql -h NEWDBHOST -u UNAME -p'PASSWORD' DBNAME


      I will add that to the list though since it is useful.
    • Guest

      posted on 2010-Mar-30 | 06:02:33 AM
      Tonny I saw you answered to Raahan that you can copy the MySQL binary files to another server, and they will work.

      I tried that, but it is not working. I cannot understand from your post if the MySQL versions must be equal?
    • Tonny

      posted on 2010-Mar-30 | 07:10:00 AM
      The both MySQL server versions must be exactly the same, even compiled with the same parameters. Otherwise there could be always an issue.

    • PhillR
      ( philiprichards [ at ] hostway . com ),

      posted on 2010-Apr-15 | 05:54:53 AM
      Nice list of MySQL commands! But I am wondering how to import txt or csv file to MySQL from the command line?
    • Tonny

      posted on 2010-Apr-15 | 09:12:19 AM
      Well, you've missed it, but I there is field here called :"Load a CSV file into a table."

      Also you can check this separate tutorial: http://www.onlinehowto.net/Tutorials/MySQL/How-to-Import-CSV-file-into-MySql-database/1353
Need a specific tutorial? Do not hesitate and submit a request!
Your e-mail: