Log configuration for mysql optimization

Source: Internet
Author: User
Tags syslog


If you choose mysql database as a data persistence tool, you need a reasonable log configuration, which will help you to troubleshoot and back up and recover data!

First, we can use the following MySQL SQL to query the log configuration in progress.

Let's get familiar with the usage of show variables like! This command is used to query the statements configured during MySQL runtime. The LIKE command can be followed by wildcards!

The corresponding setting syntax is set global, which will be used later.

Mysql> show variables like '% log % ';
+ ----------------------------------------- + --------------------------------- +
| Variable_name | Value |
+ ----------------------------------------- + --------------------------------- +
| Back_log | 50 |
| Bin log_cache_size | 32768 |
| Binlog_direct_non_transactional_updates | OFF |
| Binlog_format | STATEMENT |
| Expire_logs_days | 0 |
| General_log | OFF |
| General_log_file |/var/run/mysqld. log |
| Innodb_flush_log_at_trx_commit | 1 |
| Innodb_locks_unsafe_for_binlog | OFF |
| Innodb_log_buffer_size | 1048576 |
| Innodb_log_file_size | 5242880 |
| Innodb_log_files_in_group | 2 |
| Innodb_log_group_home_dir |./|
| Innodb_mirrored_log_groups | 1 |
| Log | OFF |
| Log_bin | OFF |
| Log_bin_trust_function_creators | OFF |
| Log_bin_trust_routine_creators | OFF |
| Log_error |/var/log/mysqld. log |
* | Log_output | FILE | *
| Log_queries_not_using_indexes | OFF |
| Log_slave_updates | OFF |
| Log_slow_queries | OFF |
| Log_warnings | 1 |
| Max_binlog_cache_size | 18446744073709547520 |
| Max_binlog_size | 1073741824 |
| Max_relay_log_size | 0 |
| Relay_log |
| Relay_log_index |
| Relay_log_info_file | relay-log.info |
| Relay_log_purge | ON |
| Relay_log_space_limit | 0 |
| Slow_query_log | OFF |
| Slow_query_log_file |/var/run/mysqld/mysqld-slow.log |
| SQL _log_bin | ON |
| SQL _log_off | OFF |
| SQL _log_update | ON |
| Sync_binlog | 0 |
+ ----------------------------------------- + --------------------------------- +
38 rows in set (0.00 sec)

Pay attention to the log_output line. The configuration determines whether to output logs to a file or table.

We can query this configuration through show variables like 'log _ output.

I. Configure through files

The mysql configuration file is located in/etc/my. cnf

[Mysqld]
Datadir =/var/lib/mysql
Socket =/var/lib/mysql. sock
User = mysql
Symbolic-links = 0
# Error log
Log-error =/var/log/mysql/error_mysql.log
# Slow Query
Log-slow-queries =/var/log/mysql/slow_mysql.log
Long_query_time = 2
# Querying records that do not use indexes
# Log-queries-not-using-indexes

# Regular query logs, which are mainly used to debug queries bound to PDO parameters
General_log = 1
General_log_file =/var/log/mysql/general_mysql.log
#<MySql5.1.12
# Log =/var/log/mysql/general_mysql.log


[Mysqld_safe]
# Syslog refers to recording logs to system logs.
# Syslog
Pid-file =/var/run/mysqld. pid
Log_error =/var/log/mysql/error_mysqld.log

Restart the server, but the configuration takes effect!

II. Hot configuration

What can I do if I want to enable logging without restarting mysql?

Of course, we can enable it through MySQL SQL query.

The following operations assume that you have logged on to the MySQL interactive interface as the root user:

Regular logs

# View general log configurations
Show variables like 'General _ log % ';
Next we start him

# Set the log file path. Ensure that the path exists first.
Set global general_log_file = '/var/log/mysql/general_mysql.log ';
# Enable logging
Set global general_log = ON;

# Disable logs
Set global general_log = OFF;
View general log configurations

Error log

Smart Friends, since we already know the "set global" and "show variables like" commands, I believe that other log configurations will be difficult for you.

So here we are, don't we ?? AliCloud? /P>

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.