Tag Archives: mysql


How To Import and Export Databases in MySQL

MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language.

A relational database organizes data into one or more data tables in which data types may be related to each other; these relations help structure the data. SQL is a language programmers use to create, modify and extract data from the relational database, as well as control user access to the database. In addition to relational databases and SQL, an RDBMS like MySQL works with an operating system to implement a relational database in a computer’s storage system, manages users, allows for network access and facilitates testing database integrity and creation of backups.

What is mysql?

mysql is a simple SQL shell (with GNU readline capabilities). It supports interactive and
non-interactive use. When used interactively, query results are presented in an ASCII-table format.
When used non-interactively (for example, as a filter), the result is presented in tab-separated
format. The output format can be changed using command options.

What is mysqldump?

The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump
a database or a collection of databases for backup or transfer to another SQL server (not necessarily
a MariaDB server). The dump typically contains SQL statements to create the table, populate it, or
both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML

Export a MySQL Database

Use mysqldump to export your database:

mysqldump -u username -p database_name > database_name-dump.sql

You can compress the data on the run using pipe and gzip.

mysqldump -u username -p database_name | gzip > database_name-dump.sql.gz

*Using GZIP will save a lot of space on disk for huge databases.

Import a MySQL Database

Use mysql to import your database:

Create the database first.

mysql > CREATE DATABASE database_name;

Import the database now.

mysql -u username -p database_name < database_name-dump.sql

If the file is compressed with gzip. use zcat to extract on the run.

zcat database_name-dump.sql.gz | mysql -u username -p database_name

Handy scripts for admins who do backups daily

bkpmysqlgz() {
    set -x
    sudo mkdir -p $DEST
    if command -v pv > /dev/null 2>&1; then
        sudo mysqldump $@ | pv | gzip > $DEST/"${@: -1}"-$DATE.sql.gz
        sudo mysqldump $@ | gzip > $DEST/"${@: -1}"-$DATE.sql.gz
    ls -lh $DEST/"${@: -1}"-$DATE.sql.gz
    set +x

using script.

$ bkpmysqlgz /opt/backups -u root -p secret dbname

How to fix missing foreign keys and/or indexes – AWS DMS

AWS Database Migration Service (DMS) helps you migrate databases to AWS quickly and securely. The source database remains fully operational during the migration, minimizing downtime to applications that rely on the database. The AWS Database Migration Service can migrate your data to and from most widely used commercial and open-source databases.

The Database Migration Service is a data mover. It creates only the structures required to migrate your data, (this is for performance reasons mainly.) Additionally, it doesn’t migrate secondary indexes, default values, procedures, triggers, auto increment columns etc. These objects/modifications need to be made after migrating the data, (and typically prior to switching the app.)

But it can be fixed by importing schema manually.


missing foreign keys and/or indexes


To fix foreign keys & indexes missing issue, follow this

  1. Import Database schema manually to RDS.
  2. Set “`Target table preparation mode“` to “`Truncate“`

Using JSON:


Using DMS GUI:


Now run the task.

You will see all foreign keys and indexes in target (RDS).

Asynchronous DB Operations in Twisted

Twisted is an asynchronous networking framework. Other Database API Implementations have blocking interfaces.

For this reason, twisted.enterprise.adbapi was created. It is a non-blocking interface,which allows you to access a number of different RDBMSes.

General Method to access DB API.

1 ) Create a Connection with db.

 db = dbmodule.connect('dbname','user','password')

2) create a cursor.

 cursor = db.cursor()

3) do a query.

resultset = cursor.query('SELECT * FROM table WHERE condition=expression')

Cursor blocks to response in asynchronous framework. Those delays are unacceptable when using an asynchronous framework such as Twisted.
To Overcome blocking interface, twisted provides asynchronous wrapper for db module such as twisted.enterprise.adbapi

Database Connection using adbapi API.

To use adbapi, we import dependencies as below

 from twisted.enterprise import adbapi

1) Connect Database using adbapi.ConnectionPool

db = adbapi.ConnectionPool("MySQLdb",db="agentdata",user="root",passwd="<yourpassword>")

Here, We do not need to import dbmodule directly.
dbmodule.connect are passed as extra arguments to adbapi.ConnectionPool’s Constructor.

2) Run Database Query

query = ("SELECT * FROM agentdetails WHERE name = '%s'" % (name))
return dbpool.runQuery(query).addCallback(self.receiveResult).addErrback(self.errorquery)

Here, I used ‘%s’ paramstyle for mysql. if you use another database module, you need to use compatible paramstyle. for more, use DB-API specification.

Twisted doesn’t attempt to offer any sort of magic parameter munging – runQuery(query,params,…) maps directly onto cursor.execute(query,params,…).

This query returns Deferred, which allows arbitrary callbacks to be called upon completion (or failure).

Demo : Select, Insert and Update query in Database.

from twisted.enterprise import adbapi
import datetime,logging
from twisted.internet import reactor

Test DB : This File do database connection and basic operation.

log = logging.getLogger("Test DB")

dbpool = adbapi.ConnectionPool("MySQLdb",db="agentdata",user="root",passwd="<yourpassword>")

class AgentDB():

    def getTime(self):
        log.info("Get Current Time from System.")
        time = str(datetime.datetime.now()).split('.')[0]
        return time

    def doSelect(self,name):
        log.info("Select operation in Database.")
        query = ("SELECT * FROM agentdetails WHERE name = '%s'" % (name))
        return dbpool.runQuery(query).addCallback(self.receiveResult).addErrback(self.errorquery)

    def receiveResult(self,result):
        print "Receive Result"
        print result
        # general purpose method to receive result from defer.
        return result

    def errorquery(self,result):
        print "error received", result
        return result

    def doInsert(self,name,status,state):
        log.info("Insert operation in Database.")
        query = """INSERT INTO agentdetails (name,state,status,logtime) values('%s','%s','%s','%s')""" % (
        return dbpool.runQuery(query).addCallback(self.receiveResult)

    def doUpdate(self,name,status,state):
        log.info("Update operation in Database.")
        query = ("UPDATE agentdetails SET status = '%s', state = '%s',logtime = '%s' WHERE name = '%s'" % (
        status,state, self.getTime(),name))
        return dbpool.runQuery(query).addCallback(self.receiveResult)

    def checkDB(self):

a= AgentDB()

Here, I have used MySQLdb api, agentdata as a database name, root as a user, 123456 as a password.
Also, I have created select, insert and update query for select, insert and update operation respectively.
runQuery method returns deferred. For this, add callback and error back to handle success and failure respectively.

How to delete records by disabling foreign key checks or constraints in mysql

If you have a foreign key constraints on your table, you can’t delete records from parent table, but you can delete from child table. The main reason we use the foreign key constraints is to maintain data integrity.

Some times you many need to disable this kind of enforcing against some operations like delete. To do so, you have to set mysql server system variable foreign_key_checks

 set foreign_key_checks=OFF

Now, you can delete or update with out any restriction because of foreign key constraint. But you will mess up the data integrity.

You can reset it back to enforce for integrity

 set foreign_key_checks=ON

There are global and local system variables. If you set the global variables it applies to all clients which are connected. If you change the local variable it applies to that current client session only. Here is how you can query local and global variable.

mysql> show global variables like "foreign_key_checks";
| Variable_name      | Value |
| foreign_key_checks | ON    |
1 row in set (0.00 sec)

mysql> show local variables like "foreign_key_checks";
| Variable_name      | Value |
| foreign_key_checks | OFF   |
1 row in set (0.00 sec)

To sent global variable you just need to add global , where to set local you need to local. By default local or session variable will be changed

mysql> set local foreign_key_checks=OFF

To set global variable

mysql> set global foreign_key_checks=OFF

NOTE: Make sure you to revert the variable back to original state to maintain data integrity in your database.


MySQL Tips

MySQL tips for beginners and intermediate

This article will take you through few MySQL tips those are pretty helpful.

We will start with few useful statements,

Do you know, if you are working offline with mysql  and you need some help on syntax. You can get it right away on terminal  with simple statement  help followed by command or statement.


mysql> help  <COMMAND or STATEMENT>

The above statement in the mysql console will give you the whole syntax and it’s usage.

Here is  an example:

mysql> help create database
    [create_specification] ...

    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: http://dev.mysql.com/doc/refman/5.6/en/create-database.html

For suppose you want know available list of date and time functions. Here is how you will get,

mysql> help  date and time functions;
You asked for help about help category: "Date and Time Functions"
For more information, type 'help <item>', where <item> is one of the following



The  status statement in the mysql console will give you the quick overview about connection, mysql server and client details

mysql> status
mysql  Ver 14.14 Distrib 5.6.27, for debian-linux-gnu (x86_64) using  EditLine wrapper

Connection id:		38
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.6.27-0ubuntu0.14.04.1 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			6 hours 4 min 6 sec

Threads: 1  Questions: 112  Slow queries: 0  Opens: 87  Flush tables: 1  Open tables: 80  Queries per second avg: 0.005


Are you frustrated with querying lot of records to analyze in mysql conventional command line client console? No worries,  can set the pager in mysql client console. You can set it to either less or more. Here is the way to do it,

mysql>\P less
PAGER set to 'less'
mysql>\P more
PAGER set to 'more'

Let’s say if you set pager to less, you will see your query output like wise when you read file using less command. You try the following statements.

mysql>\P less
mysql>show status;
Reset pager:

you can reset your pager using \P with arguments;



The following statement show processlist will give you overview about all connected clients and their work, even queries they are being executed,

mysql> show processlist;
| Id | User | Host      | db   | Command | Time | State | Info             |
| 38 | root | localhost | NULL | Query   |    0 | init  | show processlist |
1 row in set (0.00 sec)


An other statement which very helpful in optimizing your queries and schema is explain . You can’t imagine how helpful this statement is, it will show how mysql is using indexes how many rows or records it’s searching for a result. Based on the report you can optimize the queries and even you can add indexes to table if required to speed up results.

Here is simple example,

mysql> EXPLAIN SELECT t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') ORDER BY t.name ASC;
| id | select_type | table | type   | possible_keys             | key              | key_len | ref                       | rows | Extra                 |
|  1 | SIMPLE      | t     | ALL    | PRIMARY                   | NULL             | NULL    | NULL                      |    1 | Using filesort        |
|  1 | SIMPLE      | tt    | eq_ref | term_id_taxonomy,taxonomy | term_id_taxonomy | 138     | wordpress.t.term_id,const |    1 | Using index condition |
2 rows in set (0.00 sec)

You will see a record for each table from which you are selecting or used in query. Find more about explain here


Other than few handy statements explained above, now we will go through few tips to avoid common pit falls in MySQL.

  • Always try to avoid creating the user with wildcard host . Doing so will mitigate the possible risks of getting hacked.
  • Only give required permissions to the user, so other databases or things will not get screwed up either intentionally or accidentally because of user or script.
  • When you are deleting something from production or from important place, use transactions. Here is how you will do it,
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> -- do you work like deleting records and other stuff
mysql> delete from users where id in (1, 3, 4);

This is useful to avoid common problems caused by either typos or accidental mistakes. Before you type commit, you can always review your changes and rollback if changes are unintended.

ysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> -- do you work like deleting records and other stuff
mysql> delete from users;  -- lets say you forgot where  in delete query
mysql>rollback;   -- You can rollback your accidental mistake

User Account Management In MySQL

You need to have a user account to execute queries on data. When you install mysql you will prompted to enter root user details or some times you might have installed mysql with root user and no password. It is recommended to have a password for root user after completion of installation and it is best practice to use different set of mysql users to serve different purposes instead of root user to mitigate security vulnerabilities.

To execute any of the following instructions to create and edit user you have to connect with mysql server using mysql client.

mysql -u root -p

Assuming your mysql server is on localhost else specify server with option -h

Create User

To create user we use the statement CREAT USER.  Find the definitive syntax here

mysql>  CREATE USER ‘Bob’@’localhost’ IDENTIFIED BY ‘password‘;

In mysql every user is associated with host. Here it is localhost specified in the statement above. It meas user Bob can only login from host localhost(same machine)

After your create user, he will be assigned the default permissions. To let him access specific database or table you have to grant him the permissions. To do so, we use the statement GRANT

To grant all permissions on all database,

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘Bob’@’localhost’;

To grant all permissions on specific database,

mysql> GRANT ALL PRIVILEGES ON db_name.* TO ‘Bob’@’localhost’;

Once you are done with creating user. Reload privileges using

mysql> flush privileges;

This command will reload users and their privileges.

Change User Password

User can be altered using the statement ALTER USER. To change the password of an user, use the following statement

mysql> ALTER USER ‘Bob’@’localhost’ IDENTIFIED BY ‘password’;

You can change your own password by following statment,

mysql> ALTER USER USER() IDENTIFIED BY ‘password’;

You can also change the password of an user using following two statements as well,


Change password using SET PASSWORD

mysql> SET PASSWORD FOR ‘Bob’@’localhost’ =’password’;

Note: Before MySQL 5.7.6 you have to give hash to the statement SET PASSWORD using password function.

Change password using GRANT

mysql> GRANT ALL PRIVILEGES ON  *.* TO ‘Bob’@’localhost’ IDENTIFIED BY ‘new_password’;

List All Users

Following query will list all available users

mysql> select User, Host from mysql.user;

Change host associated with user account

You can change host of any user by updating the Host column of table mysql.user for that user. With out having permissions to update this table you can’t change the host of any user

mysql> update mysql.user set Host=’host’ where Host=’old_host’;

Delete User

To delete an user, we use the statement DROP USER.

mysql> DROP USER ‘Bob’@’localhost’; 


How to reset wordpress user password using mysql

WordPress uses md5 hash for password. We can reset wordpress user password using mysql single query.

To reset password using mysql,

Login to mysql and select the wordpress database

Following single query is helpful to reset wordpress user password

update wp_users set user_pass=md5(‘new_password’) where user_login=’username’ ;

Replace, new_password and username with your password and username.

mysql best practices

10 MySQL best practices

When we design database schema it’s recommended to follow the beast practices to use memory in optimal way and to gain performance. Following are 10 MySQL best practices

Always try to avoid redundancy

We can say database schema designed is the best one if it is having no redundancy. If you want to avoid redundancy in your schema, normalize it after you design.

Normalize tables

Database normalization is the process of  organizing columns and tables in relational database to avoid redundancy. Find more about normalization here

Use (unique) indexes on foreign key columns

We use foreign keys for data integrity and to represent relation. Some times these are result of process called normalization. When tables are mutually related obviously we can’t query  the data without using joins

Avoid using varchar for fixed width column instead use char

Choose the right one CHAR vs VARCHAR. CHAR(15) will just allocate the space for 15 characters but VARCHAR(15) will allocate the space only required by number of characters you store.

Always use explain  to investigate your queries and learn about how mysql is using indexes

EXPLAIN  statement is very handy in mysql. I’m sure it will spin your head. This statement will give you analyzed report. Where you can use it to improve your queries and schema. It works on both select and update. If you try it on update queries it will that query as select and will give you the report.

Use right data type

Choosing right data type for you column will help you to get rid of many bottle necks. MySQL query optimizer will choose the indexes based on data type you used in query and column datatype. There are many MySQL datatype.

Use ENUM if required  

ENUM is one datatype that mysql supports. By using this you can save lot of memory if you have predefined and predictable values in your database column.

Don’t use too many indexes, it will slow down the inserts and updates. Only use the indexes on selected column

As you know indexes will help you query data much faster than expected. It’s very tempting to you indexes on unintended columns. Choosing index on every column or unnecessary columns will get you slow inserts and updates. You need to think of indexes as seperate table. Where MySQL needs to create a index for every insert in seperate table/file. It’s extra overhead.

Tune  mysql default parameters

MySQL comes with default parameters. These parameters are not suitable if you want use mysql on dedicated machine or production. You have to tune these parameters. Formally we call them as system variables.

 Always create an account with associated hosts instead of wildcard %

MySQL manages the user with associated hosts. i.e, the user  root@localhost can’t login to mysql from everywhere except localhost. but root@% can login from every where. Using only associated hosts will mitigate many attacks those are in your blind spot.