Blog

MySQL in a Nutshell

Tags: , , , , , ,
You are currently viewing MySQL in a Nutshell
Credits

MySQL is “The world’s most popular open source database”. It is easy to setup MySQL either in Linux or Windows. Installation procedure creates the first user root and applies basic configuration to current database server.

There are many database managers with user friendly graphical or web based interface, useful to perform basic or advanced MySQL administration tasks. However, these applications are not always available (for example in production server environment). MySQL (as any other database server) offers mysql client and the utility mysqldump, which are command line tools, useful to manage our MySQL server.

Here are the most important tasks for everyday use, every administrator or web developer needs to know.

Connect

Localhost

        mysql -u username -p

Remote host

        mysql -u username -p -h hostname

REMARK: in order to increase security, do not provide your password (e.g. mysql -u username -p[password]). Wait the system to prompt for the password.

Create database

We suppose that InnoDb is default in server installation and utf8 is default database collation.

        mysql -u username -p
        create database dbname collate utf8_general_ci;

“_ci” means case insensitive.

Delete database

Use the following commands:

        mysql -u username -p
        drop database dbname;

Create user

Use the following commands:

        mysql -u username -p
        create user 'username'@'hostname' identified by 'password';
        grant all privileges on dbname.* to 'username'@'hostname';
        flush privileges;

hostname = localhost or remote host or IP

Set or change user password

Use the following command:

        mysql -u username -p
        set password=password('password');

Export database to SQL

Export entire database

        mysqldump -u username -p dbname > /path/to/db.sql

Export only schema

        mysqldump -u username -p --no-data dbname > /path/to/db_structure.sql

Export only data

        mysqldump -u username -p --no-create-info dbname > /path/to/db_data.sql

Export a table

        mysqldump -u username -p dbname tablename > /path/to/db.sql

Import database from SQL

From Shell

        mysql -u username -p < /path/to/db.sql

From client environment

        mysql -u username -p
        use dbname;
        set names utf8;
        source /path/to/db.sql

Execute query from sql file

Use the following command:

        mysql -u username -p < /path/to/query.sql

Database objects

List all MySQL databases

        mysql -u username -p
        show databases;

List tables in MySQL database

        mysql -u username -p
        use dbname;
        show tables;

View table structure

        mysql -u username -p
        use dbname;
        describe tablename;

Exit client

Exit client and return to Shell:

        exit;