Every MySQL developer should understand 10 tips _ MySQL

MySQL is the most popular database management system in the world and the first choice for programming communities all over the world. It has an interesting series of features and is very good at many aspects. Thanks to its popularity, you can find many MySQL usage tips online. Here are 12 of the best tips and tips for all MySQL database developers. Avoid editing the dump file Mysqldump. the dump file created by Mysqldump is harmless, but it is easy to be edited. However, we should know that MySQL is the most popular database management system in the world and the first choice for programming communities all over the world. It has an interesting series of features and is very good at many aspects. Thanks to its popularity, you can find many MySQL usage tips online. Here are 12 of the best tips and tips for all MySQL database developers.

Avoid editing dump files

The dump file created by Mysqldump is harmless, but it is easy to be edited. However, people should know that any attempt to modify these files under any circumstances proves dangerous. Intuitively, changes to these files will lead to database corruption and system degradation. To protect your system from any trouble, you must avoid editing MySQL dump files.

MyISAM block size

Most developers forget this fact. file systems often need a large MyISAM block to ensure efficient operation. Many developers do not know how to set the block size .. MYI files are stored in the setting of myisam_block_size. this setting item can be used to modify the large block size. The default value of MyISAM block size is 1 K, which is not an appropriate setting for most systems. Therefore, developers should consider specifying an appropriate value.

Open Delay_Key_Write

Delay_key_write is disabled by default to avoid database corruption during system crash. Some may ask, if so, why should we put it first? This is necessary to prevent the database from refreshing the key file every time it writes the MyISAM file. By enabling it, developers can save a lot of time. Refer to the MySQL official manual to learn how to open your version.

Joins (table join)

Create an index and use the same column type: the join (table join) operation can be optimized in Mysql. If there are many join operations in the application, you can create the same column type to optimize the join operation. Creating an index is another way to accelerate the application. The query modification helps you retrieve the expected query results.

Optimize WHERE clause

Even if you search for only one row of MySQL, the entire table is queried. Therefore, we recommend that you set limit to 1 when you only need one result. By doing so, you can avoid the system from searching the entire table, so that you can find the record that matches your needs as quickly as possible.

Use the Explain keyword in the Select query

You certainly want some help related to any specific query. The Explain keyword is very helpful in this respect. It provides detailed details when you are looking for a query of what has been done. For example, you can enter the Explain keyword before a complex join query to obtain a lot of useful information.

Use query cache to optimize queries

MySQL Query cache is enabled by default. This is mainly because the cache helps to quickly execute queries, and the cache can be used multiple times in the same query. You can add the current date, CURRDATE, and other PHP code before the keyword to query and cache it to enable this function.

Use stack tracking to isolate bugs

Various bugs can be isolated using stack_trace. A null pointer is enough to destroy a specific piece of code. any developer knows that it has such capabilities. Understand the details of stack tracing to avoid bugs in your code.

Set SQL _MODE

Enumeration type is always confusing. Because a field may have multiple possible values, including the values you specify and null, many problems may occur during encoding, you will always get a warning that the code is incorrect. A simple solution is to set SQL _MODE.

// Start mysqld
$-SQL-mode = "modes"
// Or
$ SQL-mode = "modes" (my. ini-Windows/my. cnf-Unix)
// Change at runtime, separate multiple modes with a comma
$ Set [GLOBAL | SESSION] SQL _mode = 'modes'
// TRADITIONAL is equivalent to the following modes:
STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_AUTO_CREATE_USER

Change Root password

Modifying the root password is essential for some specific settings. the command is as follows:

// Straightforward MySQL 101.
$ Mysqladmin-u root password [Type in selected password]
// Changing users ROOT password
$ Mysqladmin-u root-p [type old password] newpass [hit enter and type new password. Press enter]
// Use mysql SQL command
$ Mysql-u root-p
// Prompt "mysql>" pops up. Enter:
$ Use mysql;
// Enter user name you want to change password
$ Update user set password = PASSWORD (Type new Password Here) where User = 'username ';
// Don't forget the previous semicolon, now reload the settings for the users privileges
$ Flush privileges;
$ Quit

Use the MySQL Dump command to back up the database

Developers all know the importance of database backup and can help when a major system failure occurs.

The simplest way to back up a database

$mysqldump –user [user name] –password=[password] [database name] > [dump file]

// You can also use the shorthand "-u" and "-p" to replace "user" and "password" respectively"
// To import multiple databases to one file, you only need to add the name of the database to be exported:

mysqldump –user [user name] –password=[password] [first database name] [second database name] > [dump file]

// Many databases provide the sequential backup function. to back up all databases, you only need to add the -- all-databases parameter. If you do not like command line, download automysqlbackup from Sourceforge.

Adjust CONFIG configurations

The PERL script MySQL Tuner is another powerful tool for optimizing database performance. it helps you adjust and modify MySQL configurations in multiple places. You can visit the official website of the project to learn more about it.

Original English text: 10-useful-tricks-every-mysql-database-0000-must-consider