掌握MySql：一篇文章带你入门

首先说一下，数据库事务的四个特性是一种数据库操作逻辑，而解决不用事务的并发问题的时候就需要根据不同的隔离级别，而不同的隔离级别底层用的就是不通的就是使用了不同的数据库机制，比如行锁、表锁、页锁、悲观锁、乐观锁等。

mysql事务的用途：mysql的事务主要用于处理操作量大，复杂度高的数据，比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本信息，还需要删除用户的相关信息等，这些数据库操作语句就构成了一个事务！

在mysql中只有使用innodb数据库引擎的数据库或者表才支持事务。

事务的处理可以用来维持数据库的完整性，保证成批的sql语句要么全部执行，要么全部不执行。

事务用来管理insert、update、delete语句。

一般来说，事务是必须满足4个条件（ACID）：原子性、一致性、隔离性、持久性。

原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间的某一个环节，事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个事务同时对表进行读写和修改操作，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，事务隔离分为不同级别，包括读未提交、读提交、可重复读、串行化。

持久性：事务处理结束后，对数据的修改就是永久的，即使数据故障也不会丢失。

在mysql命令行默认设置下，事务都是默认自动提交的，即执行sql语句后就会马上执行commit操作。因此要显式地开启一个事务必须使用命令begin或者start transaction（启动事务） ，或者执行命令set autocommit=0（设置自动提交为0），用来禁止使用当前会话的自动提交。

mysql事务处理重要有两种方法：

①用begin、rollback、commit来实现

begin 开启一个事务

rollback 事务回滚

commit 事务确认

②直接用set来改变mysql的自动提交模式

set autocommit = 0 禁止自动提交

set autocommit = 1 开启自动提交

PHP中使用事务实例

<?php$dbhost = 'localhost:3306'; // mysql服务器主机地址$dbuser = 'root'; // mysql用户名$dbpass = '123456'; // mysql用户名密码$conn = mysqli_connect($dbhost, $dbuser, $dbpass);if(! $conn ) {die('连接失败: ' . mysqli_error($conn)); }// 设置编码，防止中文乱码 mysqli_query($conn, "set names utf8");mysqli_select_db( $conn, 'RUNOOB' );mysqli_query($conn, "SET AUTOCOMMIT=0"); // 设置为不自动提交，因为MYSQL默认立即执行 mysqli_begin_transaction($conn); // 开始事务定义if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(8)")) { mysqli_query($conn, "ROLLBACK"); // 判断当执行失败时回滚 }if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(9)")) { mysqli_query($conn, "ROLLBACK"); // 判断执行失败时回滚 }mysqli_commit($conn); //执行事务mysqli_close($conn);?>

因事务并发所造成的问题：

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

事务隔离级别为读已提交（不可重复读）时，写数据只会锁住相应的行。（查询仍旧可以查询-脏读，但是写操作的时候，必须等待锁的释放。）
mysql 日志类型以及文件：

mysql的日志文件分为四种，分别是：错误日志 记录启动、停止、运行过程中mysqld出现的问题通用日志 记录建立客服端连接和执行的语句二进制日志 记录更改数据的所有语句，还用于复制。慢查询日志 记录执行日志时间超过long_query_time秒的所有查询以上为mysql的四种日志文件，下面说一下，mysql的数据文件：1.db.opt 数据库结构定义和设置2.*.frm 数据表的结构定义3.*.MYD myisam引擎表数据4.*.MYI myisam引擎索引数据5.ibdata* innodb表空间数据文件 6.ib_logfile* innodb重做日志文件7.*.idb innodb数据和索引8.*.trg 触发器mysql数据库的binlog和relay log日志有着举足轻重的作用，并且relay log仅仅存在于mysql 的slave库，它的作用就是记录slave库中的io进程接收的从主库传过来的binlog,然后等待slave库的sql进程去读取和应用，保证主从同步，但是binlog主库和从库（slave）都可以存在，记录对数据发生或潜在发生更改的SQL语句，并以二进制的形式保存在磁盘，所以可以通过binlog来实时备份和恢复数据库。
mysql命令show global variables like "%datadir%"; MySQL 数据文件的存储位置show variables like 'slow_query%' 查看慢日志文件的位置与开启状态show variables like 'gene%'; 查询通用日志的位置与开启状态show variables like 'log_error'; 查询错误日志所在的位置show variables like 'log_bin'; 查询二进制日志开启状态
mysql 复制原理以及解析

mysql从3.23开始提供复制功能，复制指将主库的ddl和dml操作通过binlog文件传送到从库上执行，从而保持主库和从库数据同步。mysql支持一台主库同时向多台从库复制，从库同时也可以作为其他从库的主库，从而实现级联复制功能。mysql复制功能相当于oracle数据库的逻辑dg功能。

mysql复制原理大致如下：

1）mysql主库事务提交时会把数据变更作为event记录在binlog文件中，mysql主库的sync_binlog参数控制binlog日志刷新到磁盘。

2）从库收集主库binlog中的event到从库的中继日志relay log中，之后从库根据中继日志重做数据变更操作，通过逻辑复制来达到与主库同步的目的。

基本步骤如下：

l 主服务器将更新写入binlog文件，并维护文件的一个索引以跟踪日志的循环。

l 从库复制主库的binlog event到本地中继日志（relay log）。

l 从库sql线程重放中继日志。

将从服务器设置为复制主服务器的数据后，它将连接主服务器并等待更新过程。如果主服务器失败，或者从服务器和主服务器之间失去联系，那么从服务器将保持定期尝试重连，直到它能够继续监听为止。由--master-connect-retry选项控制着重试间隔，默认60s。

mysql通过3个线程完成复制功能：其中binlog dump线程跑在主库上，io线程和sql线程跑在从库上。当从库启动复制（start slave）时，从服务器首先创建io线程连接主库，主库随后创建binlog dump线程读取event发送给io线程，io线程获取到event后更新到从库中继日志relay log中，之后从库sql线程根据relay log内容在从库从做操作。从而完成mysql复制功能。由此可知，这样读取和执行语句将被分成两个独立的任务。mysql复制结构如图：

​​主库通过show processlist命令可以看到binlog dump进程，如下：

mysql> show processlist \G;

*************************** 1. row ***************************

Id: 125

User: root

Host: localhost

db: test

Command: Query

Time: 0

State: init

Info: show processlist

*************************** 2. row ***************************

Id: 127 --线程127为主库复制线程

User: rep1

Host: 192.168.80.136:44889

db: NULL

Command: Binlog Dump

Time: 16991

State: Master has sent all binlog to slave; waiting for binlog to be updated --已经发送了所有binlog，等待更新

Info: NULL

2 rows in set (0.00 sec)

从库通过show processlist命令可以看到io线程和sql线程，如下：

mysql> show processlist \G;

*************************** 1. row ***************************

Id: 4

User: root

Host: localhost

db: test

Command: Query

Time: 0

State: init

Info: show processlist

*************************** 2. row ***************************

Id: 7 --从库中的io线程

User: system user

Host:

db: NULL

Command: Connect

Time: 17079

State: Waiting for master to send event --等待主库发送日志状态

Info: NULL

*************************** 3. row ***************************

Id: 8 --从库sql线程

User: system user

Host:

db: NULL

Command: Connect

Time: 17107 --可以显示从服务器比主服务器滞后多长时间

State: Slave has read all relay log; waiting for the slave I/O thread to update it --已经应用了所有relay log

Info: NULL

3 rows in set (0.00 sec)

复制的作用：

复制有很多用途，比如备份、读写分离、软件升级、故障转移等。注意在mysql版本升级过程需要考虑到复制的兼容性问题。

mysql复制涉及到两种文件：binlog和relay log文件。其中根据binlog的不同设置，mysql复制分为3种模式。

l 基于sql语句级别的复制。

l 基于行的复制

l 混合模式复制

1）基于SQL语句级别的复制

基于语句级的复制，复制将执行主库上所执行的语句，也就是说，从库执行的更新语句和主库执行的一样。基于语句的复制，优点有：

l 相对于基于行的复制，更简单，易实现。

l 数据库产生的二进制日志更少，传输占用带宽更少。

l 二进制日志可读性更好，mysqlbinlog可以方便读取binlog日志内容。

l 有利于排查问题，从库上执行和主库一样。

基于语句的复制，缺点有：

l 主库的某些操作不能正确复制到从库。

l 从库需要锁定更多记录。

l 负载、代价大的sql在从库上需要再次执行。

l 对于非核心功能支持有限。

2）基于行的复制

Mysql5.1开始支持基于行的复制，它的适用范围更广，也可靠地多。基于行的复制其格式比较难以理解。基于行的复制可以处理各种高级功能，比如存储过程、触发器等，如果你想更兼容高级功能，建议采用基于行的复制模式。

基于行的复制优点有：

l 所有改变均被复制，对比与基于语句的复制，这是一种更安全的方式。

l 更少的锁定。

l 对于mysql高级特性兼容良好，比如存储过程、触发器。

l 二进制日志更有利于恢复，因为binlog里记录了更为详细的数据库变更。

l 更容易发现数据的不一致。

基于行复制的缺点有：

l 产生更多的binlog日志。

l Binlog日志内容不易阅读，不方便使用mysqlbinlog工具解读。

l 要求主从表结构一致，这样限制了它的灵活性，因为生产环境有时需要临时修改从库表结构，提升从库为主库。

3）混合复制模式

默认采用基于语句的复制，一旦检测到触发了某些条件，则使用基于行的方式进行复制，这种设计完美的解决了语句级和行级复制的缺点，这也是建议采用的复制方式。

复制的3种常见架构：一主多从、级联复制、双主架构

1）mysql一主多从架构：解决主库读请求压力解决方案。

​

​

​2）mysql级联架构：解决一主多从架构中主库io和网络压力，缺点是额外增加了主库到从库的应用延迟，其中master2可以设置为blackhold（黑洞）模式来缓解复制延迟。

​

​​3）双主复制/dual master架构：

​

​

​4）双主级联复制架构：

​

​​复制相关参数设置：

1）slave_exec_mode

复制冲突解决和错误检测可以采用如下两种模式：

l Strict：默认

l Idempotent：忽略duplicate-key、no-key-found错误，一般在主主复制、环形复制等其他特殊情况下才使用，不推荐使用。

2）max_allowed_packet

默认设置太小，声场环境中建设设置为16m或更大，设置太小可能导致从库不能解释接收主库发送的包，主从库建议设置成一样的值，如果数据库存在大的blob字段，还需要考虑将这个值设置为更大。

3）复制过滤选项

请不要随意使用复制过滤选项，除非你真的有理由这么做，即使使用，也建议在从库进行设置。复制过滤选项主库上主要由参数binlog-do-db和binlog-ignore-db两个参数决定，一般不建议使用，不好的地方是他们可能导致你不能进行基于时间点的恢复或者丢失数据。

Binlog-do-db=db_name：

告诉主服务器，如果当前的数据库是db_name，应该讲更新记录到binlog，其他所有没有明确指定的数据库将被忽略。如果使用了该选线，你应该确保仅对当前数据库进行更新。

Binlog_ignore_db=db_name：

告诉主服务器，若果当前数据库是db_name，那么应该不记录更新到binlog。

在从库上对应有两个参数replicate-do-db和replicate-ignore-db，他们的作用类似于主库设置的binlog-do-db和binlog-ignore-db的作用，不同点仅在于设置地方不一样。

*-do-db和*-ignore-db参数其实都仅仅是针对当前数据库，也就是说，如果我们use到其他数据库上，然后执行了一条更新其他库的SQL，这些参数将不起作用。

以下是在从库上设置的一些参数：

--replicate-ignore-db=db_name

这个选线告诉从服务器不要复制默认数据库为db_name的语句。要想忽略多个数据库，可以多次指定该参数，且每个数据库只能使用一次。如果存在跨数据库更新并且不想复制这些更新，那么就不要使用了。

--replicate-ignore-table=db_name.tb1_name

它将告诉从服务器复制线程不要复制更新指定表的任何语句。要想忽略多个表，需要多次指定该选项，且每个表只能指定一次。该选项可以跨数据库更新。

--replicate-wild-ignore-table=foo%.bar%

它告诉从服务器线程不要复制匹配通配符模式的语句，要想忽略多个表，需要多次指定该选项，且每个表只能指定一次。该选项可以跨数据库更新。如果一定要指定过滤选项，建议以上参数中仅适用此参数设置。

如果要在session级别禁用复制特性，那么我们可以在session级别设置变量set sql_log_bin=0，使当前的某些操作不被复制到从库。

4）slave_commpressed_protocol

请慎重对待跨集群复制，跨集群复制时可以采用
slave_commpressed_protocol=1压缩传输数据，需要在主库进行压缩，在从库进行解压缩，因为压缩需要额外的cpu，因此需要评审cpu资源。

5）Read-only

可以考虑在从库上设置read-obly，以只读方式启动从库，要注意的是super权限用户依旧可以在从库更新。

6）slave-net-timeout

由于生产环境网络异常，即使show slave status输出正常，但此时可能也已经停止复制，slave-net-timeout默认设置1小时，对于生产来说太长，很难及时发现网络问题，一般设置1分钟。

7）--slave-skip-errors

通常情况下，当出现错误时复制会停止，这个选项可以给你一次手动解决数据不一致问题的机会。当语句返回slave-skip-errors锁列问题时，该选线会告诉sql线程继续复制。

例如：--slave-skip-errors=1062,1053

--salve-skip-errors=all

8）skip-slave-start

Skip-slave-start可以在命令行下或配置文件中使用，目的是在mysql启动的时候不要启动slave，这在某些故障情况下很有用。使用--skip-slave-start选项来启动从库，可以防止当从库启动时，sql线程开始工作。

复制相关文件：

1）中继日志：默认情况下中继日志使用
host_name-relay-bin.nnnnnn形式命名，其中host_name表示主机名，nnnnnn表示编号。用连续的序列号来创建连续的中继日志文件。从服务器跟踪索引文件中目前正在使用的中继日志文件。中继日志索引文件命名为host_name-relay-bin.index。默认情况下，可以在从服务器的数据目录中创建这些文件。可以使用--relay-log和--relay-log-index服务器选项覆盖默认文件名，强烈建议设置这两个参数，这主要有助于以后的迁移及故障处理。

中继日志文件与binlog格式相同，所以可以使用mysqlbinlog工具读取中继日志文件内容。Sql线程执行中继日志中的event并且不再需要中继日志后，会立即自动删除它。没有直接删除中继日志的机制，因为sql线程可以自动完成它。

2）状态文件：从服务器数据目录中存在两个与复制有关的文件，分别为master.info和relay-log.info，它们包含了show slave status语句输出显示的信息。状态文件保存在磁盘上，因此从服务器关闭时不会丢失状态文件。下次启动从服务器，读取这些文件以确定从服务器已经从主库上读取了多少binlog日志，以及处理了自己的中继日志的成度。

由io线程更新master.info文件，master.info文件中和show slave status显示的列的对应关系如下表所示：

略。

由sql线程更新relay-log.info文件，relay-log.info文件中和show slave status显示的列的对应关系如下表所示：

略。

3）复制下的备份：

当备份从服务器的数据时，你还需要备份状态文件和中继日志文件。他们可以用来恢复从服务器后继续复制。如果丢失中继日志但是还存在relay-log.info文件，那么你可以通过检查该文件来确认sql线程已经执行的主服务器中的二进制日志文件程度。然后我们可以使用master_log_file和master_log_pos选项执行change_master命令来告诉从服务器需要重新从该点读取二进制日志。当然，binlog必须在主服务器上存在才可以。

4）mysql5.1中中继日志和状态文件并不是crash-safe的，也就是说，他们默认是不会实时刷新到磁盘的，那么发生故障的情况下，文件的信息可能就是错误的，将会导致复制异常。Mysql5.5，那么可以设置以下选项来确保这些文件刷新磁盘的频率：

Sync_master_info=1

Sync_relay_log=1

Sync_relay_log_info=1

注意这些参数会带来一定开销，对于写频繁的应用，建议不要设置这些参数。

Mysql5.5还有一个参数：relay_log_space_limit，这个参数设置了所有中继日志可以使用的空间阈值。意思是如果中间日志占用了空间超过了这个设置，那么io线程就会关闭，等待sql线程应用并删除中继日志释放空间。

复制环境常用的命令：

1）show master status命令查看主库状态：该命令用来提供主服务器binlog文件的状态信息，执行它需要super或replacation client权限。

mysql> show master status \G;

*************************** 1. row ***************************

File: dbking-bin.000004 --当前正在读取的binlog文件

Position: 1729 --读取binlog文件的位置

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

2）通过show slave status查看从库状态：该命令用于提供从库线程的关键参数信息。

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.80.133 --当前主服务器主机

Master_User: rep1 --被用于连接主服务器的当前用户

Master_Port: 3306 --连接主服务器端口号

Connect_Retry: 60 --“--master-connect-retry”参数的当前值

Master_Log_File: dbking-bin.000004 --io线程正在读取的主服务器binlog名称

Read_Master_Log_Pos: 1729 --io线程正在读取的主服务器binlog位置编号

Relay_Log_File:
chavinking-relay-bin.000010 --sql线程当前正在读取和执行的中继日志名称。

Relay_Log_Pos: 714 --当前relay log文件，sql线程已经读取和执行的位置

Relay_Master_Log_File: dbking-bin.000004 --sql线程执行包含多个近期event的主服务器binlog名称

Slave_IO_Running: Yes --io线程运行状态，正常状态为yes

Slave_SQL_Running: Yes --sql线程运行状态，正常状态为yes

Replicate_Do_DB: --使用--replicate-do-db选项指定的数据库清单

Replicate_Ignore_DB: --使用--replicate-ignore-db选项指定的数据库清单

Replicate_Do_Table: <------------------------------------------------

Replicate_Ignore_Table: 使用期间这些选项指定的表清单

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table: ------------------------------------------------->

Last_Errno: 0 <--多数最近被执行的查询返回错误数和错误消息，Last_Errno为0

Last_Error: 并且Last_Error为null，意味着没有错误，否则从库错误日志会被记录>

Skip_Counter: 0 --最近被使用的用于sql_slave_skip_counter的值

Exec_Master_Log_Pos: 1729 --来自主服务器binlog、由sql线程执行的、上一个时间的位置 （relay_master_log_file）.主服务器中binlog中的 （relay_master_log_file,exec_master_log_pos）对

应中继日志中的（relay_log_file、relay_log_pos）。

Relay_Log_Space: 1056 --所有原有中继日志结合起来总大小

Until_Condition: None <------------------------------

Until_Log_File: 在start slave语句的until子句中指定的值

Until_Log_Pos: 0 ------------------- --->

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0 --是从库落后多少的一个指示。一般是基于同一集群内网主从集群，此值应为 0。本字段用于测量从库sql线程和从库io线程之间时间差距，单位以秒计算

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 9b92b2a8-b7e0-11e6-81e4-000c29fa5a95

Master_Info_File: /usr/local/software/mysql-5.6.24-linux-glibc2.5-x86_64/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

3）change master命令

这个命令在从库中执行，可以配置所要连接的数据库信息，以及从哪里开始同步。常用语法如下：

change master to

master_host='xxx.xxx.xxx.xxx',

master_port=3306,

master_user='replic_user',

master_password='password',

master_log_file='log file name',

master_log_pos=position;

我们可以在正在运行的从库中动态修改连接主库的信息。例如修改密码：

mysql>stop slave;

mysql>change master to master_password='password';

mysql>start slave;

此更改没有必要指定没有变更的参数。

参数解释：

master_host和master_port：指定主库ip和端口。

master_log_file和master_log_pos：指定主库binlog名称和位置

master_user和master_password：指定复制用户的账号和密码，从库将使用这个账号去连接主库，所以主库要为这个账号授予replication slave的权限。

change master：会删除所有中继日志文件并启动一个新的日志，除非你指定了relay_log_file或relay_log_pos，这种情况下，中继日志将被保持。

change master to：会改变从库master.info和relay-log.info文件内容。

4）start slave和stop slave命令

stop salve命令可以停止io线程和sql线程。而我们常用的start slave语句有3种常用用法：

A）start slave不带任何参数

不包含任何参数的start slave命令会同时启动2个从库线程。io线程从主服务器读取查询，并把他们存储到中继日志文件中；sql线程读取中继日志文件，并应用复制。start slave要求super权限。如果start slave成功地启动了从库线程，则会返回，不会出现错误。但是，即使如此，也有可能出现从库线程启动了但是又停止了。start slave对此不发生任何警告，必须检查从库错误日志确定错误原因，或者使用show slave status命令检查io线程和sql线程运行状态。

B）start slave启动单个服务器线程

start slave io_thread;

start slave sql_thread;

C）start slave指定到某个位置自动停止

可以在start slave命令中添加until子句，指定从库应启动并运行，直到sql线程达到主服务器二进制日志中一个给定点为止。当sql线程执行到该指定点，它会自动停止。如果在该语句中指定了sql_thread选项，它会仅仅启动sql线程，否则它会同时启动2个线程。一般情况下，这种操作我们仅操控sql线程。语法如下：

start slave [sql_thread] until master_log_file='binlog_name',master_log_pos='binlog_post'

5）show slave host命令在主库上运行可以查询从库信息

存储过程

存储过程和函数是在数据库定义一些sql语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的sql语句。存储过程和函数可以避免开发人员重复编写相同的sql语句，而且，存储过程和函数是在mysql服务器中存储和执行的，可以减少客户端和服务器的数据传输。

有写的不对的欢迎指出。