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? Continue Reading…

There are many discussions about this topic and an endless debate between which database engine to choose InnoDB or MyISAM.

Both options are practical and efficient  the oldest and the default used is usually MyISAM  and in most cases we can change our engine when we want or need. Now I come with this question:

What engine is more efficient?


  • The structure of the tables in MyISAM are simpler and is most recommended for users.
  • MyISAM allows table locking.
  • With MyISAM you get high speed on SELECT because they don’t have integrity checks.


  • Support for transaction type ACID (Atomicity, Consistency, Isolation and Durability) that guarantees the integrity of the tables.
  • Record locking.
  • Recovery of unexpected problems through the transaction log.

Continue Reading…

This job can be done in many ways like dumping all databases and replacing myisam with innodb in all created tables. However this solution can take very long time.

The simplest way is to use maatkit package. I will show you how to convert your MySQL databases from MyISAM to InnoDB using maatkit:

Step1. Install maatkit package:

apt-get install maatkit

Step2. Now we need to find a list with all your databases. You can do that using the following command:

mysql -u root --password=your_password --skip-column-names -e "show databases;" > databases.list

Step3. Now convert all your MySQL databases from MyISAM to InnoDB

for databases in `cat databases.list`; do /usr/bin/mk-find $databases --engine MyISAM --exec "ALTER TABLE %D.%N ENGINE=INNODB" --print; done

This command will extract all database names from databases.list file and send that name to mk-find  which is executing “ALTER TABLE” command on all of its tables to convert their storage engine from MyISAM to InnoDB.


Page 1 of 11