Log configuration for mysql optimization

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>