MySQL service maintenance notes _ MySQL

MySQL Service maintains the backup of the note-taking service
Try to use MySQL DUMP instead of backing up data files directly. The following is a script that backs up data on weekday: the backup interval and cycle can be determined based on the backup requirements.

/Home/mysql/bin/mysqldump-S/data/app_1/mysql. sock-umysql db_name | gzip-f>/path/to/backup/db_name. 'data cannot exceed w'.dump.gz

Therefore, it is generally written in CRONTAB as follows:
15 4 */home/mysql/bin/mysqldump-S/data/app_1/mysql. sock-umysql db_name | gzip-f>/path/to/backup/db_name. 'data +/mongow'.dump.gz

Note:

1. in crontab, '%' must be converted to '/%'
2. according To log statistics, the minimum application load is usually between 4 and 6 in the morning.

Back up data locally and then upload it to a remote backup server, or directly create a database backup account to back up data directly on the remote server, for remote backup, you only need to replace-S/path/to/msyql in the preceding script. change sock to-h IP. ADDRESS.

Data recovery and system upgrade

Routine maintenance and data migration: when the data disk is not damaged, the hard disk is generally the hardware with the lowest service life in the system. However, system (including the operating system and MySQL applications) upgrades and hardware upgrades will encounter data migration problems.

As long as the data remains unchanged, first install the server, and then directly install the data disk (hard disk 2), just add the startup script to rc again. in the local file, the system is restored.

Disaster Recovery: when the database data is damaged, determine the time point of the damage and restore the data from the backup.

Key points of application design

If the CPU usage of MySQL applications exceeds 10%, you should consider optimization.

If this service can be replaced by other non-database applications (for example, many database-based counters can be replaced by WEB log statistics), it is best to disable it:

Is a non-user database unavailable? Although the database can indeed simplify the structure design of many applications, it is also an application with a large consumption of system resources. In some cases, DBM is a better choice for text than databases. for example, if many applications do not have high real-time statistics requirements, they can be recorded in file logs first, periodically import data to the database for subsequent statistical analysis. If you still need to record a simple 2-dimensional key-value structure, you can use a HEAP type table similar to DBM. Because HEAP tables are all accessed in the memory, the efficiency is very high, but data may be lost when the server suddenly loses power, so it is very suitable for storing online user information, logs and other temporary data. Even if you need to use a database, if the application does not have too complex data integrity requirements, you can simply not use commercial databases that support foreign keys, such as MySQL. A large database such as Oracle is required only when complete business logic and transaction integrity are required. For high-load applications, you can use lightweight methods such as log files, DBM, and MySQL as front-end data collection formats, then, Oracle MSSQL DB2 Sybase is used as a database warehouse for complex database mining and analysis.

Some friends and I said that the database performance has increased by 20 times after the standard MyISAM table is replaced by the InnoDB table.

Major bottleneck of database services: for an application, if the database table structure can be designed according to the database principle paradigm, and the latest MySQL version is used, and run in a relatively optimized way, the main bottleneck is generally the number of connections of a single service. even if a database supports 500 concurrent connections, it is best not to use the application to this point, because the number of concurrent connections is too large, the overhead of the database service itself for scheduling threads will also be very large. So if the application permits: let one machine run a few more MySQL services to share. Plan the service balance to multiple MySQL service ports, for example, app_1 ==> 3301 app_2 ==> 3302... app_9 ==> 3309. It is normal for a machine with 1 GB memory to run 10 MySQL instances. It is much more efficient to enable 10 MySQLD to handle 1000 concurrent connections than to enable 2 MySQLD to handle 1000 connections. Of course, this will also bring some application programming complexity;

Using a separate database server (do not let the database and front-end WEB service grab the memory), MySQL may have more memory to effectively cache the result set; in the preceding startup script, The-O key_buffer = 32M parameter is used to increase the default 8 M index cache to 32 M (of course)

The application tries its best to use the PCONNECT and polling mechanisms to save the connection overhead of the MySQL service, but it also causes too many MySQL concurrent connections (each HTTPD corresponds to a MySQL thread );

Horizontal split of tables: place 10% of the most frequently accessed data in a small table, and 90% of historical data in an archive table (fast and slow table ), data is saved through regular "migration" and regular deletion of invalid data in the middle. after all, most applications (such as forums) have little chance of accessing data two months ago, and the value is not very high. In this way, the application always selects data at a relatively small result level, which is more conducive to data caching. do not expect MySQL to have a high efficiency when the number of records in a single table is above 0.1 million. In addition, sometimes the data does not need to be so accurate. for example, if a quick table finds 60 results in an article published by a person, the ratio of the quick table to the slow table is, we can simply estimate that this person has published a total of 1200 articles. The same is true for Google's search results: for many of the 100,000 results, many of the following figures are estimated using certain algorithms.

Database field design: vertical split of a table (transition): put all the fixed-length fields (char, int, etc.) in a table, all the variable-length fields (varchar, text, blob and so on) in another table, two tables are associated with the primary key, so that the fixed-length field table can be greatly optimized (in this way, the HEAP table type can be used, data is fully accessed in the memory). here we also illustrate another principle. for us, we should try our best to use a fixed-length field to improve the access efficiency through space loss. MySQL also displays InnoDB tables that support foreign keys and transactions, standard MyISAM format tables, and HEAP memory tables based on the HASH structure. MySQL supports multiple table types, different optimization methods are provided for different applications;
Carefully check the index design of the application: you can add -- log-slow-queries [= file] to the service startup parameters to track and analyze application bottlenecks, the simplest way to track service bottlenecks is to use MySQL status to view MySQL service running statistics and show processlist to view the running SQL statements of the current service, if an SQL statement often appears in the PROCESS LIST. there may be many queries at this time. the fields that affect the query are not indexed. the number of returned results is too large. the database is SORTING. therefore, make a script, for example, run the show processlist command every two seconds, and output the result to the file, check what queries are using CPU.
Full-Text Retrieval: if no full-text index is available for the corresponding fields, full-text retrieval will consume a lot of CPU, because full-text retrieval cannot use the index of a general database, therefore, we need to traverse the corresponding field records. For details about full-text indexing, refer to Java-based full-text indexing engine lucene.

Record cache of the foreground application: for example, if you need to update the last logon time of a user who frequently uses database authentication, it is best to put the user into a cache after the record is updated (set to expire after 2 hours), so that if the user uses the login again within 2 hours, it will directly authenticate from the cache, this avoids too frequent database operations.

The query-first table should add indexes to the fields in the where and order by statements as much as possible. the less the database update and insert-first application indexes, the better.

In short: it is difficult to optimize more than 1 million records in a single table in any database. The key is to convert the application into the upper limit of data that the database is good. That is, to simplify complex requirements into mature solutions.

One optimization practice

The following example shows how to optimize a Forum application:

Webalizer is used to replace the original database statistics.

First, run the TOP command to check about 80% CPU usage and 10 MB memory usage of the MySQL service. This indicates that the index cache of the database is used up. modify the startup parameter, added-O key_buffer = 32 M. after the database is stable for a period of time, check whether the memory usage reaches the upper limit. At last, the cache is increased to 64 MB, so that the database cache can be fully used. For a database application, it is more practical to give memory to the database than to the WEB service, because the increase in MySQL Query speed can speed up the web application, thus saving the memory resources occupied by concurrent WEB services.

Use show processlist to count frequently-seen SQL statements:

Run show processlist once per minute and record the log:
* *** (/Home/mysql/bin/mysql-uuser-ppassword

In show_processlist. SQL:
Show processlist;

For example, the where clause can be filtered out from the log:
Grep where mysql_processlist.log

If a deadlock is found, you must review the database design. In general, if the query speed is very slow, add the index to the fields not indexed in the SQL where statement, if the sorting is slow, add the field with no index in the order by statement. For queries with % like %, disable and use full-text index acceleration later.

Based on show processlist, we can see that some databases are frequently used. we recommend that you split the database into other service ports.

MSSQL to MySQL data migration: ACCESS + MySQL ODBC Driver

In the previous data migration practices, I found that the simplest data migration process was not through professional database migration tools, it is not MSSQL's own DTS for data migration (there will be a lot of table error warnings during the migration process), but by obtaining external data from the MSSQL database and importing it to the database through ACCESS, right-click the ACCESS table ==>=> export, create ODBC, and export data through the MySQL DSN. In this way, most of the data will be migrated smoothly. if the exported table has an index problem, the index addition prompt will be displayed (DTS will not work ), then the rest of the work is to design the SQL script corresponding to the field in MySQL.