mysql 初识基础

1、查看当前binlog文件列表

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       828 |
| mysql-bin.000002 |       522 |
| mysql-bin.000003 |       234 |
+------------------+-----------+
3 rows in set (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       828 |
| mysql-bin.000002 |       522 |
| mysql-bin.000003 |       234 |
+------------------+-----------+
3 rows in set (0.00 sec)

2、共享表空间和独立表空间

mysql5.6.6之前的版本，innodb默认会将所有数据库innodb引擎的表存储在一个共享的表空间ibdata1中，这样管理起来比较困难，增删数据库的时候ibdata1也不会自动收缩，单个数据库备份也成问题，为优化上述问题，mysql5.6.6之后独立表空间innodb_file_per_table默认开启。

mysql> show global variables like 'innodb_file_per_table';
 

mysql> show global variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

即使指定了独立表空间，用户自定义数据库中的某些元数据信息，回滚（undo）信息，插入缓冲等还是存放在共享表空间ibdata1，又称为系统表空间。 默认包含一个ibdata1系统数据文件，位于mysql数据目录（datadir）下。

系统表空间数据文件的位置、大小和数目有参数innodb_data_file_path和innodb_data_home_dir启动项控制。

mysql> show global variables like 'innodb_data%';
+-----------------------+-------------------------+
| Variable_name         | Value                   |
+-----------------------+-------------------------+
| innodb_data_file_path | ibdata1:200M:autoextend |
| innodb_data_home_dir  |                         |
+-----------------------+-------------------------+
2 rows in set (0.00 sec)

使用独立表空间的好处在于drop或者truncate时空间可以被回收至操作系统，单表方便在不同实例间移动。

3、undo log

undo log 存放在系统表空间ibdata1文件中，为了避免ibdata1被撑爆，建议单独存放。

innodb_undo_directory指定单独存放目录，innodb_undo_tablespaces单独存放undo个数，建议大于3个。

 set global innodb_undo_log_truncate=1;开启后可在线收缩undo表空间

mysql> set global innodb_undo_log_truncate=1;
Query OK, 0 rows affected (0.00 sec)

mysql>  show global variables like '%undo%';
+--------------------------+---------------------+
| Variable_name            | Value               |
+--------------------------+---------------------+
| innodb_max_undo_log_size | 1073741824          |
| innodb_undo_directory    | /mysql/product/undo |
| innodb_undo_log_truncate | ON                  |
| innodb_undo_logs         | 128                 |
| innodb_undo_tablespaces  | 2                   |
+--------------------------+---------------------+
5 rows in set (0.00 sec)

mysql> show global variables like '%undo%';
+--------------------------+---------------------+
| Variable_name            | Value               |
+--------------------------+---------------------+
| innodb_max_undo_log_size | 1073741824          |
| innodb_undo_directory    | /mysql/product/undo |
| innodb_undo_log_truncate | OFF                 |
| innodb_undo_logs         | 128                 |
| innodb_undo_tablespaces  | 2                   |
+--------------------------+---------------------+
5 rows in set (0.00 sec)

mysql> exit
Bye
[root@master1 data]# cat /etc/my.cnf|grep -i undo
innodb_undo_directory=/mysql/product/undo
innodb_undo_tablespaces=2

4、临时表空间

mysql5.7之前，innodb存储引擎的临时表空间存放在ibdata里，对其他表产生很大的影响。mysql5.7中，对临时表空间2个方面调整优化：1、单独的临时表空间参数为innodb_temp_data_file_path。2、临时表检索信息保存在系统信息表中：information_schema.innodb_temp_table_info. mysql5.7想查看临时表的系统信息没有什么太好的方法，

虽然临时表有自己的表空间，但是还不能自定义临时表空间路径，只能继承innodb_data_home_dir。

mysql> show global variables like 'innodb_temp%';
+----------------------------+------------------------+
| Variable_name              | Value                  |
+----------------------------+------------------------+
| innodb_temp_data_file_path | ibtmp1:200M:autoextend |
+----------------------------+------------------------+
1 row in set (0.00 sec)

[root@master1 data]# cat /etc/my.cnf|grep -i temp
innodb_temp_data_file_path=ibtmp1:200M:autoextend

5、logerror

mysql> show global variables like 'log_error';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| log_error     | /mysql/product/log/myerror.log |
+---------------+--------------------------------+
1 row in set (0.00 sec)
[root@master1 data]# cat /etc/my.cnf|grep -i error
max_connect_errors=50000
log_error=/mysql/product/log/myerror.log

 6、slowlog

指定慢查询路径：slow_query_log_file，超过long_query_time值的sql（默认10s）

mysql> show global variables like 'slow%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_launch_time    | 2                                 |
| slow_query_log      | OFF                               |
| slow_query_log_file | /mysql/product/log/mysql-slow.log |
+---------------------+-----------------------------------+
3 rows in set (0.00 sec)

mysql> show global variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@master1 data]# cat /etc/my.cnf|grep -i slow
slow_query_log=0
slow_query_log_file=/mysql/product/log/mysql-slow.log
[root@master1 data]#

7、generallog

mysql> show global variables like 'general%';
+------------------+--------------------------------------+
| Variable_name    | Value                                |
+------------------+--------------------------------------+
| general_log      | OFF                                  |
| general_log_file | /mysql/product/log/mysql-general.log |
+------------------+--------------------------------------+
2 rows in set (0.00 sec)

mysql>

8、db.opt文件

记录该库默认字符集编码和字符集排序规则：

[root@master1 db02]# cat db.opt
default-character-set=utf8mb4
default-collation=utf8mb4_general_ci