MySQL Commands With Examples for Beginners

Valic —  February 12, 2013 — Leave a comment

In this article I will show you some MySQL commands with examples.

1. MySQL Set Root Password

You can set the mysql root password from command line like this:

mysqladmin -u root password your_password

You can also change mysql root password if you forgot it using this tutorial: How to Recover MySQL Root Password

2. Change MySQL Users Passwords from the command line

mysqladmin -u mysql_username -h mysql_host -p password 'new_mysql_password'

3. How To Connect to Local MySQL Server

This is how you connect to your local MySQL server from the command line:

mysql -u root -p

4. Create a MySQL Database

The following command will create a new MySQL database after you are connected:

create database debiantutorials_db;

5. Backup MySQL Database with mysqldump

Backup a database to a .sql file:

mysqldump -u root -p debiantutorials_db > debiantutorials_db.sql

You can also compress the database with gzip on the fly while you are dumping:

mysqldump -u root -p debiantutorials_db | gzip -v > debiantutorials_db.sql.gz

6. Mysqldump and Skip Table

You want to dump a mysql database with one or more crashed tables and you get an error like this?

mysqldump: Error 1194: Table 'comments' is marked as crashed and should be repaired when dumping table `comments` at row: 120

MySql provides a “repair table `table_name`” command to fix it.  You only need to log in into the mysql database and executed the following commands.

mysql>  use my_database;
mysql> repair table `comments`

If first solution is not working you need to tell mysqldump to skip the tables with:

mysqldump -u mysql_username -p debiantutorials_db –ignore-table=comments > debiantutorials_db.sql

7. Dump a specific table from a mysql database

mysqldump -c -u mysql_username -p mysql_password debiantutorials_db table_name > debiantutorials_db.table_name.sql

8. Import a MySQL Database

This is how you import a simple mysql database  from command line:

mysql -u mysql_username -p mydatabase_name < mydatabase_backup.sql

9. Select a Database in MySQL

This is how you select a database in mysql:

use db_name;

10. How to Show Tabels in a MySQL Database

First select the databse you wish to use and then run the following command to show tables:

show tables;

11. How to Create a MySQL User

In the following example I will show you how to create a MySQL user called “debtuts” with the password “my_pasword”:

grant usage on *.* to [email protected] identified by 'my_password';

Now you need to grant the user permission to access his database. This command will allow permission for the user “debtuts” on the database “debian_tutorials”.

grant all privileges on debian-tutorials.* to [email protected]

Now if you want “debtuts” to have access to all databases use this command:

grant all privileges on *.* to [email protected];

12. How to List MySQL Databases

The following command will list all mysql databases from your server:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| data |
| lpic |
| mysql |
| performance_schema |
| phpmyadmin |
| tutorials_test |
| webstats |
+--------------------+
7 rows in set (0.00 sec)

13. How to Drop a MySQL Database

The following command will delete a database:

drop database database_name;

14. How to Drop a MySQL Table

This command will delete a mysql table. First you need to select the database:

use database_name;
drop table table_name;

15. How to Create a MySQL table

CREATE TABLE new_table (
id INT,
data VARCHAR(50)
);

16. How to Create an INNODB Table in MySQL

The following command will create an innodb table:

 CREATE TABLE new_table_innodb (
 id INT,
 data VARCHAR(50)
 ) TYPE=innodb;

17. How to Convert a MySQL Databese from MyISAM to INNODB

This command will convert your table from MyISAM to INNODB:

ALTER TABLE ENGINE=INNODB;

You can read this article for more info about convesting databases from MyISAM to INNODB: How to convert your MySQL databases from MyISAM to InnoDB

18. How to Repair a Broken Table in MySQL

If you have a a broken table run this command to repair it:

repair table broken_table;

19. How to Displays the contents of a MySQL Table

SELECT * FROM table_name;

20. How to Show Columns in a MySQL Table

show columns from table_name;

21. How to Add a new column in MySQL

The following command shows you how to add a new column in mysql:

alter table table_name add column new_column varchar (100);

22. How to Delete a Column in MySQL

The following command shows you an example of how to delete a column in mysql:

alter table table_name drop column column_to_drop;

23. How to Search and Show Rows Containing a Value

This command  will show all rows containing “debian-tutorials”:

SELECT * FROM table_name WHERE field_name = "debian-tutorials";

Valic

Posts Twitter Facebook

Editor in Chief at Debian-Tutorials, Linux enthusiast.

No Comments

Be the first to start the conversation.

Leave a Reply