MySQL数据库文件有以下几种
- database(schema)
–mysql/performance_schema
–datafile(select into outfile/ import) - error log
- general log
- slow log
- ibdata(system tablespace)
- iblogs(redo)
- binlog(.index)
- relay-log(.index)
- master.info
- relay-log.info
- master_upgrade_info
- auto.cnf
- database(schema)
- –mysql/performance_schema
–datafile(select into outfile/ import)
db.opt
创建该数据库时的一下参数设置
mysql> CREATE DATABASE lucien DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.33 sec)
[root@mysqlrep2 lucien]# cat db.opt
default-character-set=utf8
default-collation=utf8_general_ci
.frm .MYI .MYD .ibd
表结构描述文件,MyISAM索引文件,MyISAM数据文件,InnoDB数据文件
可以通过select into outfile方式将表中数据导出到文件。
mysql> select * from lijingkuan;
+—-+——-+
| id | name |
| 1 | hello |
| 2 | what |
| 3 | are |
| 4 | you |
| 5 | doing |
| 6 | now |
+—-+——-+
6 rows in set (0.00 sec)mysql> select * from lijingkuan into outfile ‘/root/lijingkuan.dbf’;
ERROR 1 (HY000): Can’t create/write to file ‘/root/lijingkuan.dbf’
(Errcode: 13 - Permission denied)
–目录权限问题mysql> select * from lijingkuan into
outfile ‘/dbdata/data/lijingkuan.dbf’;
Query OK, 6 rows affected (0.00sec)
–basedir目录可以写mysql> select * from lijingkuan into outfile ‘lijingkuan1.dbf’; Query
OK, 6 rows affected (0.00 sec)
–默认目录位置是该表所在数据库文件夹下[root@mysqlrep2 lucien]# cat lijingkuan1.dbf
1 hello
2 what
3 are
4 you
5 doing
6 now
同样的,用load data infile导入数据的时候,也会遇到文件目录权限问题,最好把要导入的文件放到tmp目录下,因为mysql默认用tmp目录作为临时目录。
load data infile ‘file_name’ into table table_name
mysqlimport用法:略
- error log
相关参数有两个:log_error和log_warnings
- log_error[=filename]
(filename可选,默认为hostname.err)
- log_warnings[=level]
level取值为:
- 0
- 1 Aborted connection…/slave
- >1 access deined…
general log
general_log={0|1}
general_log_file=file_name
log_output={TABLE| FILE|NONE}
log_raw (>= 5.6.3)
–动态参数,可以在线修改,可以设置全局或当前session general log状态
SET GLOBAL general_log = ‘ON’;
SET sql_log_off = ‘ON’;
slow log
与slow log相关的参数:
• slow_query_log = {0|1}
• slow_query_log_file =file_name (host_name-slow.log)
• long_query_time
• min_examined_row_limit
• log_slow_admin_statements
• log_queries_not_using_indexes
• log_throttle_queries_not_using_indexes
• log_slow_slave_statements
• log_output={TABLE| FILE|NONE}
官方提供的处理slow log的工具:mysqldumpslow
非官方,但是很好用的工具:mysqlsla
binlog
• log-bin[=base_name] #basename.xxx
• relay_log=
• binlog_checksum={CRC32|NONE}
• binlog-do-db/binlog-ignore-db
• log-slave-updates
• max_binlog_size
• sync_binlog
• expire_logs_days
• binlog_format = {‘STATEMENT’|’ROWS’|’MIXED’}
• mixed:
• uuid()
• UDF
• temporary
• rows_found/row_count
• user()/current_user()
• LOAD_FILE()
mysql官方提供的binlog分析工具:mysqlbinlog
以上所有log文件的管理:
• mv mysql.log $backup/mysql.log.20151010
• mv mysql-slow.log $backup/mysql-slow.log.20151010
• mysqladmin flush-logs
• mysql> flush logs;
• mysql> reset master;
• mysql> purge binary logs;
• mysql> purge binary logs to ‘mysql-bin.000002’;
• mysql> purge binary logs before ‘2008-04-02 22:46:26’;