MySQL slow query log can be to used to determine queries that take a longer time to execute in order to optimize them. The slow query log consists of SQL statements that took more than `long_query_time` seconds to execute, required at least `min_examined_row_limit` rows to be examined and fulfilled other criteria specified by the slow query log settings.

Before enabling the MySQL slow query log, we must decide criteria for SQL statements that will be logged and also select the location where they will be logged.

Once I faced a situation wherein I had no option to delete the database and was limited to use only the MySQL command line. I wasted a lot of time find and trying different ways to delete all tables in the database. So, I thought it will be worth sharing things that ultimately worked for me.

$ mysqldump -hHOSTNAME -uUSERNAME -pPASSWORD --add-drop-table --no-data DB_NAME | grep -e '^DROP \| FOREIGN_KEY_CHECKS' | mysql -hHOSTNAME -uUSERNAME -pPASSWORD DB_NAME;

While importing a database that has tables with foreign keys defined we get to see error “#1452 – Cannot add or update a child row: a foreign key constraint fails“.

Even while trying to delete some tables from such a database, we come across situation where we face foreign key constraint error: “#1217 – Cannot delete or update a parent row: a foreign key constraint fails“

To avoid such errors first disable the foreign key checks, then do your job of importing the database or deleting the table and finally enable the foreign key checks.

We need to import database on local machine when setting up a live website locally or when we are transferring hosting from one service provider to another. Let’s see how we can import MySQL database dump from command line

Assuming that we have created the database say ‘mynewdb’ in which we have to import.

Run the following command

$ mysql -u username -ppassword mynewdb < dbbackupfile.sql

It is necessary to take database backups at regular intervals to prevent loss of data. In this article we will see how we can backup mysql database using command line.

‘mysqldump’ command can be used to create backup of MySQL database as follows:

$ mysqldump -u username -ppassword dbname > dbname.sql

The above command assumes that you are running the ‘mysqldump’ command on the same server on which MySQL is installed.

Indexes are needed to improve the performance of SELECT queries. They can be defined with table creation and also created after table creation with CREATE INDEX and ALTER TABLE statements. If during optimization you find that an index is not useful then you can DROP them using DROP INDEX or ALTER TABLE statements.

Indexes are needed to improve the performance of SELECT queries. Indexes in Mysql are just like the indexes available in books. Without indexes if we have to look for a topic in a book then we will have to go through each page content but luckily we have an index, we find the page number from the index and then open that page directly. Similarly in MySql if we have to find row(s) have a particular column value then without index on that column MySql will have to scan each row of the table to find the matching rows.

It is highly recommended that you create indices on columns of a table from which you often query the data.

Are you trying to import a MySql dump file and getting the following error: “#1452 – Cannot add or update a child row: a foreign key constraint fails”? Its because of the foreign key validation checks.

You can run the following statement to get detailed error information:

SHOW ENGINE INNODB STATUS;

For connecting to the MySQL server you need to use MySQL client program. It needs to be provided parameters such as the hostname, username, password etc. to connect to the MySQL Server; if no parameters are specified the default values are assumed. Any of the connection parameters can be specified to override the default values.

Connecting to a local server specifying username and password

# mysql --user=username --password=mypassword databasename;
# mysql -u username -pmypassword databasename;

It is important to have passwords for all MySQL user accounts for securing the database. If anyone knows the username of an account with no password then he can successfully connect to the database server.

To assign or change a password for an existing account

mysql> SET PASSWORD FOR 'newuser'@'localhost' = PASSWORD('mypass');

NOTE: Only users such as root that have update access to the mysql database can change the password for other users.