Mysql 性能调优 二 1
Mysql 性能调优 二 1
关系型数据库中，最耗资源的sql操作：
关联表与排序
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
系统参数：
tmp_table_size
状态参数：
create_tmp_disk_tables
created_tmp_tables
max_tmp_table_size
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
系统参数：
sort_buffer_size
innodb_sort_buffer_size
状态参数：
Sort_merge_passes
Sort_range
Sort_rows
Sort_scan
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
系统参数：
log_bin
类似于oracle归档日志，但是log_bin打开后生成的文件是二进制的文件
使用binlog方法：
1. 启用binlog
[root@test1 ~]# vi /etc/my.cnf
[mysqld]
log_bin
2. 查看binlog日志放置目录
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| test1-bin.000001 | 120 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> select @@datadir;
+-----------------+
| @@datadir |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)
3. 查看binlog，并生成文本文件
mysql> create database ss;
Query OK, 1 row affected (0.04 sec)
mysql> use ss;
Database changed
mysql> create table tmp as select * from mysql.user;
Query OK, 4 rows affected (0.48 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| test1-bin.000001 | 328 |
+------------------+-----------+
1 row in set (0.00 sec)
[root@test1 ~]# mysqlbinlog /var/lib/mysql/test1-bin.000001 > binlog.txt
[root@test1 ~]#
[root@test1 ~]# cat binlog.txt
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#141217 12:28:03 server id 1 end_log_pos 120 CRC32 0x897a5f31 Start: binlog v 4, server v 5.6.22-log created 141217 12:28:03 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
UwaRVA8BAAAAdAAAAHgAAAABAAQANS42LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABTBpFUEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAATFf
eok=
'/*!*/;
# at 120
#141217 12:31:02 server id 1 end_log_pos 208 CRC32 0xf75dce06 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1418790662/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database ss
/*!*/;
# at 208
#141217 12:31:32 server id 1 end_log_pos 328 CRC32 0x55564536 Query thread_id=1 exec_time=1 error_code=0
use `ss`/*!*/;
SET TIMESTAMP=1418790692/*!*/;
create table tmp as select * from mysql.user
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@test1 ~]#
状态参数：
binlog_cache_size
max_binlog_size
===================================================================================================
利用mysql的binlog恢复数据
MySQL Binary Log也就是常说的bin-log, ,是mysql执行改动产生的二进制日志文件,其主要作用有两个:
* 数据回复
* 主从数据库。用于slave端执行增删改，保持与master同步。
1.开启binary log功能
需要修改mysql的配置文件，添加一句log_bin = mysql_bin即可
[mysqld]
......
log_bin = mysql_bin
......
log_bin是生成的bin-log的文件名，后缀则是6位数字的编码，从000001开始，按照上面的配置，生成的文件则为：
mysql_bin.000001
mysql_bin.000002
......
配置保存以后重启mysql的服务器，用show variables like '%bin%'查看bin-log是否开启，如图：
mysql> show variables like '%bin%';
+-----------------------------------------+--------------------------------+
| Variable_name | Value |
+-----------------------------------------+--------------------------------+
| bind_address | * |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | IGNORE_ERROR |
| binlog_format | STATEMENT |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlogging_impossible_mode | IGNORE_ERROR |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/test1-bin |
| log_bin_index | /var/lib/mysql/test1-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| simplified_binlog_gtid_recovery | OFF |
| sql_log_bin | ON |
| sync_binlog | 0 |
+-----------------------------------------+--------------------------------+
25 rows in set (0.00 sec)
mysql>
2.查看产生的binary log
bin-log因为是二进制文件，不能通过记事本等编辑器直接打开查看，mysql提供两种方式查看方式，在介绍之前，我们先对数据库进行一下增删改的操作，否则log里边数据有点空。
create table bin( id int(10) primary key auto_increment,name varchar(255));(测试前我已经建表)
insert into bin(name) values ('orange');
1.在客户端中使用 show binlog events in 'mysql_bin.000001' 语句进行查看,为了排序美观，可以在结尾加\G使结果横变纵，此时结尾无需加；语句结束符。
eg:
mysql> show binlog events in 'mysql_bin.000001'\G
...............省略...............
*************************** 3. row ***************************
Log_name: mysql_bin.000001
Pos: 174
Event_type: Intvar
Server_id: 1
End_log_pos: 202
Info: INSERT_ID=2
*************************** 4. row ***************************
Log_name: mysql_bin.000001
Pos: 202
Event_type: Query
Server_id: 1
End_log_pos: 304
Info: use `test`; insert into bin(name) values ('orange')
*************************** 5. row ***************************
...............省略...............
Log_name:此条log存在那个文件中，从上面可以看出这2条log皆存在与mysql_bin.000001文件中。
Pos:log在bin-log中的开始位置
Event_type:log的类型信息
Server_id:可以查看配置中的server_id,表示log是那个服务器产生
End_log_pos：log在bin-log中的结束位置
Info:log的一些备注信息，可以直观的看出进行了什么操作
2.用mysql自带的工具mysqlbinlog,这是我们就需要知道bin-log存在硬盘的什么位置，
mysqlbinlog mysql_bin.000001
3.利用bin_log恢复数据
1. 最常用的就是回复指定数据端的数据了，可以直接恢复到数据库中：
mysqlbinlog --start-date="2012-10-15 16:30:00" --stop-date="2012-10-15 17:00:00" mysql_bin.000001 |mysql -uroot -p123456
2. 亦可导出为sql文件，再导入至数据库中：
mysqlbinlog --start-date="2012-10-15 16:30:00" --stop-date="2012-10-15 17:00:00" mysql_bin.000001 >d:\1.sql
source d:\1.sql
3. 指定开始\结束位置，从上面的查看产生的binary log我们可以知道某个log的开始到结束的位置，我们可以在恢复的过程中指定回复从A位置到B位置的log.需要用下面两个参数来指定：
--start-positon="50" //指定从50位置开始
--stop-postion="100"//指定到100位置结束
最后介绍几个bin_log的操作：（主备机）
1.产看最后一个bin日志文件是那个,现在位置
mysql> show mastre status;
2.启用新的日志文件,一般备份完数据库后执行
mysql> flush logs;
3.清空现有的所用bin-log
mysql> reset master;
关系型数据库中，最耗资源的sql操作：
关联表与排序
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
系统参数：
tmp_table_size
状态参数：
create_tmp_disk_tables
created_tmp_tables
max_tmp_table_size
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
系统参数：
sort_buffer_size
innodb_sort_buffer_size
状态参数：
Sort_merge_passes
Sort_range
Sort_rows
Sort_scan
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
系统参数：
log_bin
类似于oracle归档日志，但是log_bin打开后生成的文件是二进制的文件
使用binlog方法：
1. 启用binlog
[root@test1 ~]# vi /etc/my.cnf
[mysqld]
log_bin
2. 查看binlog日志放置目录
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| test1-bin.000001 | 120 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> select @@datadir;
+-----------------+
| @@datadir |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)
3. 查看binlog，并生成文本文件
mysql> create database ss;
Query OK, 1 row affected (0.04 sec)
mysql> use ss;
Database changed
mysql> create table tmp as select * from mysql.user;
Query OK, 4 rows affected (0.48 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| test1-bin.000001 | 328 |
+------------------+-----------+
1 row in set (0.00 sec)
[root@test1 ~]# mysqlbinlog /var/lib/mysql/test1-bin.000001 > binlog.txt
[root@test1 ~]#
[root@test1 ~]# cat binlog.txt
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#141217 12:28:03 server id 1 end_log_pos 120 CRC32 0x897a5f31 Start: binlog v 4, server v 5.6.22-log created 141217 12:28:03 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
UwaRVA8BAAAAdAAAAHgAAAABAAQANS42LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABTBpFUEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAATFf
eok=
'/*!*/;
# at 120
#141217 12:31:02 server id 1 end_log_pos 208 CRC32 0xf75dce06 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1418790662/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database ss
/*!*/;
# at 208
#141217 12:31:32 server id 1 end_log_pos 328 CRC32 0x55564536 Query thread_id=1 exec_time=1 error_code=0
use `ss`/*!*/;
SET TIMESTAMP=1418790692/*!*/;
create table tmp as select * from mysql.user
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@test1 ~]#
状态参数：
binlog_cache_size
max_binlog_size
===================================================================================================
利用mysql的binlog恢复数据
MySQL Binary Log也就是常说的bin-log, ,是mysql执行改动产生的二进制日志文件,其主要作用有两个:
* 数据回复
* 主从数据库。用于slave端执行增删改，保持与master同步。
1.开启binary log功能
需要修改mysql的配置文件，添加一句log_bin = mysql_bin即可
[mysqld]
......
log_bin = mysql_bin
......
log_bin是生成的bin-log的文件名，后缀则是6位数字的编码，从000001开始，按照上面的配置，生成的文件则为：
mysql_bin.000001
mysql_bin.000002
......
配置保存以后重启mysql的服务器，用show variables like '%bin%'查看bin-log是否开启，如图：
mysql> show variables like '%bin%';
+-----------------------------------------+--------------------------------+
| Variable_name | Value |
+-----------------------------------------+--------------------------------+
| bind_address | * |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | IGNORE_ERROR |
| binlog_format | STATEMENT |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlogging_impossible_mode | IGNORE_ERROR |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/test1-bin |
| log_bin_index | /var/lib/mysql/test1-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| simplified_binlog_gtid_recovery | OFF |
| sql_log_bin | ON |
| sync_binlog | 0 |
+-----------------------------------------+--------------------------------+
25 rows in set (0.00 sec)
mysql>
2.查看产生的binary log
bin-log因为是二进制文件，不能通过记事本等编辑器直接打开查看，mysql提供两种方式查看方式，在介绍之前，我们先对数据库进行一下增删改的操作，否则log里边数据有点空。
create table bin( id int(10) primary key auto_increment,name varchar(255));(测试前我已经建表)
insert into bin(name) values ('orange');
1.在客户端中使用 show binlog events in 'mysql_bin.000001' 语句进行查看,为了排序美观，可以在结尾加\G使结果横变纵，此时结尾无需加；语句结束符。
eg:
mysql> show binlog events in 'mysql_bin.000001'\G
...............省略...............
*************************** 3. row ***************************
Log_name: mysql_bin.000001
Pos: 174
Event_type: Intvar
Server_id: 1
End_log_pos: 202
Info: INSERT_ID=2
*************************** 4. row ***************************
Log_name: mysql_bin.000001
Pos: 202
Event_type: Query
Server_id: 1
End_log_pos: 304
Info: use `test`; insert into bin(name) values ('orange')
*************************** 5. row ***************************
...............省略...............
Log_name:此条log存在那个文件中，从上面可以看出这2条log皆存在与mysql_bin.000001文件中。
Pos:log在bin-log中的开始位置
Event_type:log的类型信息
Server_id:可以查看配置中的server_id,表示log是那个服务器产生
End_log_pos：log在bin-log中的结束位置
Info:log的一些备注信息，可以直观的看出进行了什么操作
2.用mysql自带的工具mysqlbinlog,这是我们就需要知道bin-log存在硬盘的什么位置，
mysqlbinlog mysql_bin.000001
3.利用bin_log恢复数据
1. 最常用的就是回复指定数据端的数据了，可以直接恢复到数据库中：
mysqlbinlog --start-date="2012-10-15 16:30:00" --stop-date="2012-10-15 17:00:00" mysql_bin.000001 |mysql -uroot -p123456
2. 亦可导出为sql文件，再导入至数据库中：
mysqlbinlog --start-date="2012-10-15 16:30:00" --stop-date="2012-10-15 17:00:00" mysql_bin.000001 >d:\1.sql
source d:\1.sql
3. 指定开始\结束位置，从上面的查看产生的binary log我们可以知道某个log的开始到结束的位置，我们可以在恢复的过程中指定回复从A位置到B位置的log.需要用下面两个参数来指定：
--start-positon="50" //指定从50位置开始
--stop-postion="100"//指定到100位置结束
最后介绍几个bin_log的操作：（主备机）
1.产看最后一个bin日志文件是那个,现在位置
mysql> show mastre status;
2.启用新的日志文件,一般备份完数据库后执行
mysql> flush logs;
3.清空现有的所用bin-log
mysql> reset master;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29500582/viewspace-1379437/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Mysql 性能调优 一 3
下一篇：
Mysql 性能调优 二 2
请登录后发表评论
登录
全部评论
