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.
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..
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.
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.
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.
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.
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.
In MySQL, there is a hierarchical system of privileges. The order is as follows:
- Host Level
- Database Level
- Table specific
- Column specific
For each of these levels there is a separate grant table in the MySQL database.
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.