11.mysql服务日志文件及多种引擎实践

=========================
1.mysql服务之错误日志-查询日志-二进制日志
（1）错误日志：记录mysql服务进程mysqld在启动/关闭或者运行过程中遇到的错误信息。
[root@oldboyedu-01 ~]# vim /data/3306/my.cnf
[mysqld_safe]
log-error=/data/3306/mysql_oldboy3306.err

（2）二进制日志：记录数据被修改的相关信息。
#log-bin = /data/3306/mysql-bin
log-bin = /data/3306/mysql3306-bin

（3）查询日志：分为两类：
a.普通查询日志：记录客户端连接信息和执行的SQL语句信息。
b.慢查询日志：记录执行时间超出指定值的SQL语句。
说明：不打开该日志功能，没用！

普通查询日志参数：
mysql> show variables like 'general_log%';
+------------------+----------------------------------+
| Variable_name | Value |
+------------------+----------------------------------+
| general_log | ON |
| general_log_file | /data/3306/data/oldboyedu-01.log |
+------------------+----------------------------------+
mysql> set global general_log = ON;
mysql> show variables like '%_log%';
+-----------------------------------------+---------------------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------------------+
| back_log | 600 |
| binlog_cache_size | 1048576 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_stmt_cache_size | 32768 |
| expire_logs_days | 7 |
| general_log | ON |
| general_log_file | /data/3306/data/oldboyedu-01.log
[root@oldboyedu-01 ~]# ll /data/3306/data/oldboyedu-01.log
-rw-rw---- 1 mysql mysql 247 Jan 14 21:01 /data/3306/data/oldboyedu-01.log
[root@oldboyedu-01 ~]# cat /data/3306/data/oldboyedu-01.log
/application/mysql-5.5.32/bin/mysqld, Version: 5.5.32-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /data/3306/mysql.sock
Time Id Command Argument
180114 21:01:51 12 Query show variables like '%_log%'
mysql> set global general_log = OFF;

慢查询日志参数：
[root@oldboyedu-01 ~]# vim /data/3306/my.cnf
long_query_time = 1 //超过1s就写入slow.log，定时任务结合mysqladmin flush可以切割该日志文件。
#log-slow-queries = /data/3306/slow.log

mysql> show variables like '%index%';
log_queries_not_using_indexes

=========================
2.mysql服务二进制日志的三种模式
（1）statement level模式
每一条会修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。
（2）row level模式（行级模式）
日志会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改。
（3）mixed模式

工作中根据业务需求确定，若存储过程、触发器什么都没有或先测试可以用没有则可以用statement level模式；若对复制要求高则可以选择row level模式；也可以选择mixed模式。
=========================
3.mysql服务二进制日志的三种模式配置方法
mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

运行时在线生效：
mysql> SET SESSION binlog_format = 'STATEMENT';//ROW MIXED
全局生效：
mysql> SET GLOBAL binlog_format = 'STATEMENT'; //ROW MIXED

修改配置文件，设置参数：
[root@oldboyedu-01 ~]# vim /data/3306/my.cnf
#binlog_format="STATEMENT"

row level模式举例：
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> quit
重新登录：
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
刷新：
[root@oldboyedu-01 ~]# mysqladmin -uroot -poldboy123 -S /data/3306/mysql.sock flush-logs
[root@oldboyedu-01 ~]# ll /data/3306/
total 36
drwxr-xr-x 7 mysql mysql 4096 Jan 14 21:01 data
-rw-r--r-- 1 mysql mysql 1935 Jan 14 17:42 my.cnf
-rwxr-xr-x 1 mysql mysql 1310 Jan 13 03:24 mysql
-rw-rw---- 1 mysql mysql 776 Jan 14 23:41 mysql3306-bin.000001
-rw-rw---- 1 mysql mysql 107 Jan 14 23:41 mysql3306-bin.000002 //出现了！
-rw-rw---- 1 mysql mysql 64 Jan 14 23:41 mysql3306-bin.index

mysql> select * from oldboy.test;
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
| 6 | 老男孩 |
| 7 | 老男孩 |
| 8 | 老女孩 |
| 9 | baodao |
| 10 | yanjing |
+----+-----------+
mysql> update test set name='testnow';
mysql> select * from oldboy.test;
+----+---------+
| id | name |
+----+---------+
| 1 | testnow |
| 2 | testnow |
| 3 | testnow |
| 4 | testnow |
| 5 | testnow |
| 6 | testnow |
| 7 | testnow |
| 8 | testnow |
| 9 | testnow |
| 10 | testnow |
+----+---------+
10 rows in set (0.00 sec)
数据解析：
[root@oldboyedu-01 3306]# mysqlbinlog mysql3306-bin.000002
[root@oldboyedu-01 3306]# mysqlbinlog --help
[root@oldboyedu-01 3306]# mysqlbinlog --base64-output=decode-rows -v mysql3306-bin.000002 //

mysql> SET GLOBAL binlog_format = STATEMENT;
mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
[root@oldboyedu-01 ~]# ll /data/3306/
total 36
drwxr-xr-x 7 mysql mysql 4096 Jan 14 21:01 data
-rw-r--r-- 1 mysql mysql 1935 Jan 14 17:42 my.cnf
-rwxr-xr-x 1 mysql mysql 1310 Jan 13 03:24 mysql
-rw-rw---- 1 mysql mysql 776 Jan 14 23:41 mysql3306-bin.000001
-rw-rw---- 1 mysql mysql 537 Jan 14 23:44 mysql3306-bin.000002 //没变

=========================
4.mysql服务存储引擎简介
数据库表里的数据存储在数据库里及磁盘上和上述的视频格式及存储磁盘文件系统格式特征类似，也有很多种存储方式。
对于用户和应用程序来说同样一张表的数据无论用什么引擎来存储用户看到的数据都是一样的。不同的引擎存取，引擎功能，占用的空间大小，读取性能等可能有区别。
mysql常用存储引擎：MyISAM和InnoDB

=========================
5.mysql服务存储引擎体系结构
----------------图521

=========================
6.MyISAM引擎
新版本mysql数据库中InnoDB引擎由于其对事务的参照完整性，以及更高并发性等优点开始逐步取代MyISAM引擎。

[root@oldboyedu-01 ~]# ll /data/3306/data/mysql/
-rw-rw---- 1 mysql mysql 10630 Jan 14 18:13 user.frm //文件保存表的定义
-rw-rw---- 1 mysql mysql 468 Jan 14 18:39 user.MYD //数据文件
-rw-rw---- 1 mysql mysql 2048 Jan 14 18:46 user.MYI //索引文件

=========================
7.mysql服务事务介绍及ACID特性说明
（1）数据库事务：
事务就是逻辑上的一组SQL语句操作，组成这组操作的各个SQL语句，执行时要么同时成功要么全失败。
这就是事务的原子性。
MySQL5.5支持事务的引擎：innodb/ndb

事务的四大特性：ACID
原子性：atomicity，事务是一个不可分割的单元，事务中所有的SQL等操作要么都发生，要么都不发生。
一致性：consistency，事务发生前和发生后，数据的完整性必须保证一致。
隔离性：isolation，当并发访问数据库时，一个正在执行的事务在执行完毕前，对于其他的会话是不可见的，多个并发事务之间的数据是相互隔离的；备份的参数。
[root@oldboyedu-01 ~]# mysqldump --help|grep single
--single-transaction
持久性：durability，一个事务一旦被提交，它对数据库中的数据改变就是永久性的。如果出了错误，事务也就不允许撤销，只能通过“补偿性事务”。

rollback 回滚事务
commit 提交事务
//电商支付时可能会应用。

=========================
8.mysql服务之MyISAM引擎特点
（1）不支持事务
（2）表级锁定（更新时锁整个表）：锁定机制是表级索引，这虽然可以让锁定的实现成本很小但是也同时大大降低其并发性能。
（3）读写相互阻塞：但读与读之间不会阻塞。
（4）只会缓存索引：MyISAM可以通过key_buffer_size缓存索引，以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据。
[root@oldboyedu-01 ~]# grep key_buffer_size /data/3306/my.cnf
key_buffer_size = 32M
（5）读取速度较快，占用资源相对少。功能弱呀！
（6）不支持外键约束，但支持全文索引。
（7）MyISAM引擎是mysql5.5之前缺省的存储引擎。

=========================
9.mysql服务之MyISAM引擎生产场景
（1）不需要事务支持的业务(例如转账就不行,充值付款)。
（2）一般为读数据比较多的应用,读写都频繁场景不适合,读多或者写多的都适合。
（3）读写并发访问相对较低的业务(纯读纯写高并发也可以)(锁定机制问题)。
（4）数据修改相对比较少的业务（阻塞问题）。
（5）以读为主的业务,例如:www,blog,图片信息数据库,用户数据库,商品库等业务。
（6）对数据一致性要求不是非常高的业务。
（7）硬件资源比较差的机器可以用MyISAM。
小结:单一对数据库的操作都可以使用 MYISAM，所谓单一就是尽量纯读,或纯写(insert, update, delete)等。

=========================
10.mysql服务之MyISAM引擎调优
（1）设置合适的索引(缓存机制)。
（2）调整读写优先级,根据实际需求确保重要操作更优先执行。 ..mysql> help select
（3）启用延迟插入改善大批量写入性能(降低写入频率,尽可能多条数据一次性写入)。
（4）尽量顺序操作让 Insert数据都写入到尾部,减少阻塞。
（5）分解大的时间长的操作,降低单个操作的阻塞时间。
（6）降低并发数(减少对 MYSQL访问)某些高并发场景通过应用进行排队队列机制。
（7）对于相对静态（更改不频繁）的数据库数据，充分利用Query Cache或memcached缓存服务可以极大地提高访问频率。
[root@oldboyedu-01 ~]# grep query /data/3306/my.cnf
query_cache_size = 2M //缓存空间大小
query_cache_limit = 1M //缓存的限制
query_cache_min_res_unit = 2k //最小的缓存对象
（8）MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问。
mysql> select count(*) from oldboy.test;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
（9）把主从同步的主库使用innodb，从库使用MyISAM引擎。//从库读么，速度快！但维护不方便！不推荐！

=========================
11.mysql服务之InnoDB引擎简介
InnoDB引擎和其他存储引擎相比，优点是支持兼容ACID的事务（类似于PostgreSQL）以及参数完整性（即对外键的支持）。mysql5.5.5以后数据库默认存储引擎为InnoDB引擎。

[root@oldboyedu-01 ~]# ll /data/3306/data/
total 143384
-rw-rw---- 1 mysql mysql 134217728 Jan 14 23:44 ibdata1 //共享表空间对应物理数据文件

=========================
12.mysql服务之InnoDB引擎特点
（1）支持事务:支持4个事务隔离级别,支持多版本读。
（2）行级锁定(※更新时一般是锁定当前行):通过索引实现,全表扫描仍然会是表锁,注意间隙锁的影响。
（3）读写阻塞与事务隔离级别相关。
（4）具有非常高效的缓存特性:能缓存索引,也能缓存数据。
（5）整个表和主键以 Cluster方式存储,组成一颗平衡树。
（6）所有 Secondary Index都会保存主键信息。
（7）※支持分区,表空间,类似 oracle数据库。
（8）支持外键约束,5.5以前不支持全文索引,以后支持了。
小结: Supports transactions, row-level locking, and foreign keys。
（9）和 MyISAM引擎比, Innodb对硬件资源要求比较高。

=========================
13.mysql服务之InnoDB引擎生产场景

（1）需要事务支持的业务（具有较好的事务特性）。
（2）行级锁定对高并发有很好的适应能力,但需要确保查询是通过索引完成。
（3）数据读写及更新都较为频紫的场景,如:BBS,SNS,微博,微信等。
（4）数据一致性要求较高的业务,例如:充值转账,银行卡转账。
（5）硬件设备内存较大,可以利用Innodb较好的缓存能力来提高内存利用率,尽可能减少磁盘IO。

[root@oldboyedu-01 ~]# grep innodb /data/3306/my.cnf
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0

[mysqld]
innodb_file_per_table = 0
innodb_data_home_dir=/data/3306/data

mysql> use oldboy
Database changed
mysql> create table t1(id int);
mysql> create table t2(id int);

[root@oldboyedu-01 ~]# ll /data/3306/data/oldboy
total 40
-rw-rw---- 1 mysql mysql 61 Jan 14 04:08 db.opt
-rw-rw---- 1 mysql mysql 8556 Jan 15 02:36 t1.frm
-rw-rw---- 1 mysql mysql 8556 Jan 15 02:36 t2.frm
-rw-rw---- 1 mysql mysql 8586 Jan 14 04:08 test.frm

（6）相比myisam引擎，innodb引擎更消耗资源，速度没有myisam引擎快。

=========================
14.mysql服务之InnoDB引擎调优及不同引擎功能对比

（1）主键尽可能小,避免给Secondary index带来过大的空间负担。
（2）避免全表扫描,因为会使用表锁。
（3）尽可能缓存所有的索引和数据,提高响应速度,减少磁盘IO消耗。
（4）在大批量小插入的时候,尽量自己控制事务而不要使用 autocommit自动提交。有开关可以控制提交方式。
（5）合理设置 innodb_flush_log_at_trx_commit参数值,不要过度追求安全性。
如果innodb_flush_log_at_trx_commit的值为0，log buffer每秒就会被刷写日志文件到磁盘,提交事务的时候不做任何操作。
（6）避免主键更新,因为这会带来大量的数据移动。

　　
=========================
15.mysql服务之引擎种类功能及对应参数配置说明

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| CSV | YES | CSV storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)
mysql> show engines\G

innodb引擎重要参数：
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M //缓存
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0

注释：
[root@oldboyedu-01 ~]# vim /application/mysql/support-files/my-innodb-heavy-4G.cnf

=========================
16.批量修改mysql服务引擎的多种方案

法一：mysql命令语句修改
mysql> alter table t1 engine=MyISAM;
mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
进入库中利用for循环依次取表进行修改。

法二：使用sed对备份内容进行引擎转换（适合较少数据量）
法三：利用参数mysql_convert_table_format
[root@oldboyedu-01 ~]# which mysql_convert_table_format
/application/mysql/bin/mysql_convert_table_format
[root@oldboyedu-01 ~]# `which mysql_convert_table_format` --help

[root@oldboyedu-01 ~]# mysql_convert_table_format --user=root --password=oldboy123 --socket=/data/3306/mysql.sock --engine=MyISAM oldboy t2
mysql> show create table t2;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

=========================
17.有关面试题

posted @ 2018-01-27 16:47 bkycrmn 阅读(128) 评论(0) 收藏举报

刷新页面返回顶部

11.mysql服务日志文件及多种引擎实践

公告