Windows MySQL log basic view and import and export usage tutorial
- 2020-12-05 17:25:09
- OfStack
MYSQL has different types of log files (each of which stores different types of logs) from which you can find out what is being done in MYSQL, and these log files are indispensable for MYSQL administration.
1. Error log (The error log) : It records the error information during the database startup, operation and stop;
2.ISAM operation log (The isam log) : Records all changes to ISAM table, and this log is only used for debugging ISAM mode;
3.SQL execution log (The query log) : records client connection and SQL statement executed;
4. Update log (The update log) : the statement of changing data is recorded, which is no longer recommended and replaced by base 2 log;
5.2 Base log (The binary log) : records all modification statements to database data;
6. Timeout log (The slow log) : Record all statements whose execution time exceeds the maximum SQL execution time (long_query_time) or whose indexes are not used;
If you are using mysql for replication and backup, the slave server also provides a log file called relay log.
By default, all log files will be logged in MYSQL's data directory. You can force mysql to close and reopen a file for logging. Of course, the system will automatically add suffixed (e.g.00001,.00002) by executing statement mysql in mysql environment > flush logs; Or #mysqladmin ES53en-ES54en or #mysqladmin refresh via the mysqladmin hypervisor
These logs can be started in mysqld_safe mode when the database is started, followed by option parameters, or in the configuration file. The second method is recommended. The configuration method is very simple, I only configured three kinds of logs:
[mysqld]
log=/var/log/mysqld_common.log
log-error=/var/log/mysqld_err.log
log-bin=/var/log/mysqld_bin.bin
To view
The view of the log is very simple, most of it is text, directly use vim, less, more and other tools to see it, it is worth noting that the 2 base file view:
1). First, determine whether the function of base 2 file recording is enabled
mysql>show variables like 'log_bin';
mysql>show master status;
3). The program mysqlbinlog is needed to view the base 2 data to see what options it supports and use them according to your own needs.
mysql>mysqlbinlog /var/log/mysql/mysql-bin.000040;
Query a time range can be executed the following statement, if many records can be directed to a file to see for yourself :-) :
mysql>mysqlbinlog --start-datetime='2008-01-01 00:00:00' --stop-datetime='2008-08-08 00:00:00' /var/log/mysql/mysql-bin.000040 > ./tmp.log
export
There are many kinds of database exports for MySQL, so I will introduce the export and import of mysqldump which comes with MySQL.
Note: When exporting, export according to mysql table code. If the encoding on the mysql server side does not correspond to Table 1 when importing, an import error occurs.
1. MySQL exports the entire database table structure and data commands:
mysqldump -u The user name -p password dbName>f:\ The path + export SQL The name of the
Note: generate.sql files, but multiple databases, multiple databases separated by commas.
2. MySQL export database single table structure and data command:
mysqldump -u The user name -p password The database name The name of the table >f:\ The path + export SQL The name of the
Note: Multiple tables can be separated by commas.
3. MySQL export the entire database table structure command:
mysqldump -u The user name -p password -d The database name >f:\ The path + export SQL The name of the
Note: Entire database table structure, generate.sql file.
4. MySQL export database single table structure command:
mysqldump -u The user name -p password -d The database name The name of the table >f:\ The path + export SQL The name of the
Note: Single table structure, generate.sql file, but many tables. Multiple tables are distinguished by Spaces
The import
Import of MySQL:
1) Enter cmd
2)
mysql -h localhost -u The user name -p password
3)
mysql>show variables like 'log_bin';