MySQL developers need 12 tips and tricks to know

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

MySQL database management system is the most popular around the world, is the preferred programming community. It has a series of interesting characteristics, very good in many aspects. Because of its immense popularity, using skills in the Internet can find many MySQL. Here are 12 of the best tips and tricks, all MySQL database developers should know about.

  Avoid edit dump file

  Dump files created by Mysqldump which is harmless, but it is easy to be trying to edit. However, people should know that in case of any attempt to modify these documents prove to be dangerous. Intuitively for these changes to the file will cause the database is damaged, resulting in degradation of the system. In order to make your system from any trouble, you must avoid editing MySQL dump file.

  MyISAM block size

  Most developers forget this fact, file systems often require a MyISAM block to ensure efficient operation of the. Many developers do not know the block size setting. The.MYI file is stored in the myisam_block_size settings, the settings can be used to modify the block size large. The default value for the MyISAM block size is 1K, this is not the most appropriate setting system. Therefore, developers should consider a corresponding to the specified value.

  Open the Delay_Key_Write

  In order to avoid the collapse of the system database is damaged delay_key_write is off by default. Some people may ask, if this is the case, why put it in the first place open? From prevent database each write MyISAM key file to refresh the document aspect it is necessary. Through the open it, you can save a lot of time. Refer to the MySQL manual to understand your version of how to open it.

  Joins (table)

  The type of the column index creation and use the same: join (join) operations can be optimized in Mysql. If the application has many join operations, can be optimized by creating the same column type join. Create index is another way to accelerate the application of. Query modification helps you find the expected query results.

  Optimization of WHERE clauses

  Even if you only search a line of MySQL will query the entire table, therefore, suggest that you only need a result when limit is set to 1. By doing so, can avoid system through searching the entire table, which can be as fast as possible matches and you need to record.

  Use the Explain keyword in Select query.

  You want to get some help with any particular query related. The Explain keyword is very helpful in this respect. It provides the details for the query did what you. For example, in a complex join query of type Explain keyword you will get a lot of useful information.

  Using the query cache optimization query

  MySQL query caching is enabled by default. This is mainly because the rapid implementation of caching help query, cache can be run multiple times in the same query. You joined the current date, CURRDATE PHP code before the keyword to query cache it so as to enable this feature.

  The use of the stack trace isolation Bug

  A variety of Bug stack_trace can be used to isolate. A null pointer is enough to destroy a specific code, any developer knows that it has the ability to. Understand the use of the stack trace in detail, so as to avoid bug in your code.

  Set SQL_MODE

  Enumeration types always make people feel very confused. The field may have multiple possible values, these values include you specified in the code and null, there will be a lot of problems, you will always get a warning that the code is not correct. A simple solution is to set up the SQL_MODE.

//Start mysqld with
$–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

  Root password change

  Root password change was necessary for some specific settings, modify command.:

//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 for
$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

  MySQL Dump backup database

  Developers know the importance of database backup, when the system is the major faults can play the role of.

  The easiest way to backup database

$mysqldump –user [user name] –password=[password] [database name] > [dump file]
//You can also use the abbreviation "-u", "-p" to replace "user" and"password"
//Multiple databases into a file as long as add names to export database:
mysqldump –user [user name] –password=[password]
[first database name] [second database name] > [dump file]
//Many databases provide sequential backup function, to backup database only need to add the --all-databases parameter. If you didn't like command line, Download automysqlbackup from Sourceforge.

  Adjust the CONFIG configuration

  The PERL script MySQL Tuner is another powerful to optimize database performance tool, it can help you to the MySQL configuration for multiple adjustments and modifications. To understand it further you can access the project website.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Abbott at November 29, 2013 - 6:13 AM