How will you make MySQL system secure against attackers?

We can use following best practices to make MySQL secure against attackers:

  • Password: Each MySQL account should have a password. Also the password should be strong enough so that it can not be cracked by an attacker.
  • Unix account: Only the Unix user account with read or write privileges should run the mysqld process.
  • Root: MySQL should not be run by Unix root user. Because any user with FILE privilege in MySQL will be able to create file as root user.
  • FILE privilege: We should not give FILE privilege to non-administrative users in MySQL.
  • PROCESS/SUPER Privilege: Also it is not advisable to give PROCESS or SUPER privilege to non-administrative users.
  • SYMLINK: We should not allow symlinks to tables in MySQL.
  • Connections: We should limit the number of connections allowed to an account in MySQL.

How can you protect your MySQL server against Denial of Service attacks?

In a Denial of Service attack, a malicious user may load the server with so many unwanted requests that the system becomes very slow and almost unusable to most of the other genuine users.

  • We can use following techniques to protect our MySQL server against any Denial of Service attacks:
  • We should modify dynamic URLs with %22 (“), %23 (#) etc.
  • We should modify data type in dynamic URLs from number to character format.
  • We should check the size of data before passing it to MySQL. If there is larger amount of data than the average size, it should raise red flag.
  • We should use different users for application connectivity to database than the administrative user.
  • We can also enable strict sql mode to enable system to be more restrictive of the values it accepts..

What is the limitation of mysqldump?

Since mysqldump is a logical backup it is not useful for taking backup of a database with large tables. mysqldump loads an entire table’s data in memory before writing it to dump. Since memory is a limited resource, it is not a scalable option for dumping large tables.

In such a scenario, it is preferable to take physical backups of data.

What are the different optimization techniques in MySQL?

Some of the optimization techniques in MySQL are as follows:

  • Range Optimization: In this case we can use a single index to retrieve the subset of data from a table. Now the server has to work on less amount of data.
  • Index Optimization: In this method several range scans can be merged into one result. So index scans from same table can be merged into one dataset.
  • Engine Condition Pushdown Optimization: When we have a direct comparison between a non-indexed column and constant, MySQL optimizer will push the condition to storage engine for evaluation.
  • Index Condition Pushdown Optimization: This is another optimization for selecting rows from a table by using an index.

Can we run Javascript or Python code in MySQL?

MySQL provides a tool called mysqlsh to run Javascript or Python code in a MySQL shell. We can use it to execute the code from Javascript and Python.

On calling mysqlsh, a new shell is created within regular shell. In the new shell we can write and run Javascript/Python code.

What are user-defined variables in MySQL?

In MySQL we can create user-defined variables. We can store a value in a user-defined variable in one statement and later use it in subsequent statements.

User-defined variables are specific to a session. A user-defined variable can not be shared among multiple clients.

The name of user-defined variable can have maximum 64 characters and it is case-insensitive.

How will you optimize a SELECT query in MySQL?

We can use following options to increase the speed of a SELECT query in MySQL:

  • Index: We can create index on the columns that are used in WHERE clause of SELECT statement.
  • Function Isolation: We can check whether a function is being called multiple times or one time. By isolating the specific function that takes longer time in a query, we can optimize it to improve the overall performance of a query.
  • Full Table Scan: Wherever possible we should minimize the full table scan in a query. We can use optimal WHERE clause to do this.
  • Table Statistics: MySQL has an in-built optimizer that can optimize a sql query. But it needs current statistics from the table. To keep the statistics up to date we can run ANALYZE statement on a table.
  • Memory: We can also adjust the size and properties of Memory area to optimize the execution of a query in MySQL.

How can we optimize INSERT query in MySQL?

We can use following techniques to optimize INSERT queries in MySQL:

  • Values: For inserting multiple rows in a table we can pass multiple VALUES as a list in an INSERT statement. It is faster than using single row INSERT per statement.
  • Default: If we have default value set on a column then we should not try to insert default value. We can just insert data with non-default value.
  • LOAD_DATA_INFILE: We can use LOAD_DATA_INFILE to insert data from a text file into MySQL.

What is the order of privileges in MySQL?

In MySQL, there is a hierarchical system of privileges. The order is as follows:

  • Global
  • Host Level
  • Database Level
  • Table specific
  • Column specific

For each of these levels there is a separate grant table in the MySQL database.

Is it ok to mention a table name in lower case while creation and in uppercase while retrieving data from MySQL DB?

MySQL is a case sensitive platform. We have to specify the object names in same case all the times. Generally on all the Unix based platforms MySQL enforces case-sensitive rule for object names. In Microsoft Windows based installation MySQL does not enforce case-sensitive rule.

So it is always safe to use same case for Object names in CREATE and SELECT queries.

We can also use system level parameter lower_case_table_names to set the correct option for case-sensitivity in MySQL.