网易MySQL数据库工程师微专业学习笔记（十）

一、mysql日常运维

DBA的日常工作主要包括导数据、数据修改、表结构修改、加权限和研发关于数据库的问题响应（如研发经常会给DBA反应某个数据表响应慢）。这些工作并不复杂，但是往往不注意的话还是可能会出现一些问题，而数据库出现的问题往往会对线上项目造成极大的影响。因此这里总结几点上述工作内容中的注意事项。

1. 导数据

导数据一般就是使用mysqldump来导入导出，但是在导数据时需要注意三点：第一，导出csv格式的数据时账号需要有file权限才能导出，并且只能将数据导出到数据库服务器上无法远程导出；第二，使用mysqldump导出数据时要加上参数--single-transaction，这样就不会导致因为数据导出而产生锁表；第三，最好在镜像库中进行数据导出，这样不会对线上数据库产生什么不好的影响。

除了mysqldump意外，还可以使用select into outfile命令来导出数据，例如如下代码。

select * from t1 into outfile 'D:/tmp/t1.csv';

使用select into outfile同样需要拥有file权限的账号才能执行。相对于mysqlbump其在某些方面更加的灵活，例如可以支持连表数据导出、自定义导出文件的名称等。

2. 数据修改

数据修改需要注意这几点：第一，数据修改前要做好备份；第二，先开启事务，再修改数据，修改完成后检查一下修改的是否正确，如果有问题可以通过rollback来回滚数据；第三，如果要修改大量数据应该分批完成，避免长时间锁表，以及因异常导致数据修改失败后的长时间回滚；第四，不要在业务的高峰期做数据修改。

3. 表结构修改

修改表结构必须是在业务的低峰时期做，此外mysql5.5以及之前的版本中，修改表结构都会锁表，导致修改期间数据无法写入(读取是可以的)，mysql5.6之后的版本都支持online ddl的功能（但也不是所有的ddl都不会锁表的）。因此，可以使用percona公司的pt-online-schema-change工具来修改表结构，这个工具的逻辑是创建一张和需要修改表结构的表结构相同的表，然后在新表上修改表结构。接下来pt-online-schema-change工具会在原表上加三个触发器，让原表上的增删改操作同样会发生在新表上，最后执行一下select insert语句将原表上的数据导入新表，导入完成后用新表代替原表。

4. 加权限

加权限需要注意这几点：第一，只给符合需要的最低权限；第二，修改权限时注意不要修改用户密码（就是不要在修改权限的语句后面再加identified by了）；第三，避免给应用账号super权限。

5. 数据库慢

如果有研发人员提出数据库慢，那么首先要问清楚是哪里慢，是查询慢还是写入慢。然后用show processlist查看一下mysql的连接状态，如果有大量的连接出于异常状态，那么可能是数据库服务出了问题。

二、mysql参数调优

实际的线上mysql在上线前需要进行参数优化，这么做的原因主要有三点：第一，不同的服务器间性能是不同的，有的服务器性能好，有的比较差；第二，不同的业务场景对数据的需求是不同的，普通的互联网业务往往是读多写少，但是有时候也会有写多读少的情况；第三，mysql的默认配置只是一个参考值，往往不能发挥出服务器的最佳性能。mysql的参数设置有两个方面，第一是服务器硬件参数的配置，第二是mysql服务中的参数配置。

先简单介绍一下服务器硬件相关的配置（话说课上老师讲的配置我一个都没有听过，更别说用过）。

1.CPU和网卡是否开启节电模式

现在的CPU和网卡都有节电模式，其目的是让服务器在访问量小的时候通过降低性能的方式来节约电能，但是对于数据库服务器这个功能最好关闭，方式当瞬时高峰来临时服务器性能一下子起不来。

2.服务器numa设置

numa是硬件解决cpu无限扩容的一种方法，通过将内存分片并直接提供给cpu读写，解决了之前硬件中数据访问总是需要占用总线的问题。但是如果cpu访问的数据不再分配的内层片中就会导致读取速度下降。对应到mysql就是如果mysql要读取的数据页不在对应的cpu的内层片中，那么访问的性能就会一下子下降（说实话这个我也不是很懂，老师就是这么一讲，我实际工作中还没有接触到过这么高级的东西）。

3.RAID卡缓存

在实际服务器上往往不会想PC机上直接插硬盘使用，而是将几个硬盘和一个RAID卡一起绑定组成一个RAID阵列，这样有两个好处一个是提高数据的读写性能，一个是提高数据的高可用性。

首先RAID卡会绑定一块内存作为缓冲区，然后将写入数据时先将数据放在这个缓冲区中，然后再异步的将内存中的数据刷入磁盘，这样就提高了数据写入的性能。当然这样当服务器异常宕机的时候缓冲区的数据就会丢失，为了解决这个问题RAID卡中添加了一个BBU(Backup Battery Unit)，就是一个电池，这样即使服务器宕机，这个电池也能保证将缓冲区中的数据刷入磁盘。但是如果BBU损坏或者没电了，那么RAID卡就会自动切换数据写入模式，当数据写入缓冲区后还要将数据写入磁盘，然后才能返回写入成功，这样就会导致写入效率急剧下降。

RAID盘的高可用主要是通过磁盘冗余来实现的，RAID的结构一般有RAID0、RAID1、RAID5和RAID10。

RAID0结构如下图所示，其实就是两块磁盘相加，他的磁盘空间就是两块磁盘空间的总和，性能也约等于两块盘性能的相加。当然不一定是两块盘，也可以是更多的盘组成RAID0。但是RAID的高可用性非常差，只要一块盘损坏，那么这块盘中的数据就无法找回了。

RAID1结构如下图所示，就是两块盘存储同样的数据，这样如果一块盘坏了，也不会丢失数据。但是RAID1只能用两块盘来做，所以不是无限扩容的。

RAID5如下图所示，就是最少由三块盘组成，当数据写入时根据算法切分数据为三份（如果四块盘组成RAID就是四份），并写入到三个磁盘中，同时还会在这3块硬盘上写入校验信息。当读取数据时会分别从3块硬盘上读取数据内容，再通过检验信息进行校验。当其中有1块硬盘出现损坏的时候,就从另外2块硬盘上存储的数据可以计算出第3块硬盘的数据内容。但是因为有数据切分和计算校验信息的过程，所以写入会慢一点。

RAID10如下图所示，就是将RAID0和RAID1两种模式进行组合。先将磁盘两两组成RAID1保证数据高可用，然后将RAID1的磁盘阵列再组成RAID0，解决了磁盘的无限扩容的问题。

下面介绍一下mysql常用的参数优化，一般参数优化有两个方面，一个是读优化，一个时写优化。

1.innodb_buffer_pool_size

这个参数是innodb存储引擎的查询数据缓存池大小，当查询innodb表时，如果数据在这个缓存池中的话就直接从缓存池中读取数据，如果不在再从磁盘中读取，因此这个参数越大，数据在缓存池中的可能性也越大，则数据读取可能就比较快。

2.innodb_thread_concurrency

这个参数是用来控制并发量的。主要是当服务器并发性不好的时候，这个innodb会根据这个参数控制一个并发队列，当并发数超过这个参数限制时并发请求就需要到队列中排队，等前面的情况完成后才能再被执行。但是现在的服务器往往并发性能都非常好，因此这个参数一般设置为0，即不做并发限制（实际上mysql5.5以后的版本中这个参数默认就是0）。

3.innodb_flush_log_at_trx_commit&sync_binlog

这个参数是用来控制事务刷新方式的参数，一共有三个值，分别时0，1，2。0代表每隔一秒把事务日志缓存区中的数据写到日志文件中，以及把日志文件中的数据刷新到磁盘，这种模式下如果服务器异常就可能丢失一秒钟的数据。1代表每个事务提交的时候将事务日志缓存区中的数据写入到日志文件中，并且将日志文件中的数据刷新到磁盘上，线上推荐使用这种模式，因为这个是最安全的，不会轻易的丢失数据。2代表每个事务提交的时候都将事务日志缓存区的数据写入到日志文件中，每隔1秒刷新一下日志文件，但是不一定刷新到磁盘，而是根据操作系统的的调度策略来写入磁盘。这个模式相对与0模式安全一点，0模式下只要mysql服务宕机了，就会丢失数据。而这个模式下，即使mysql服务宕机，只要服务器系统没有宕机数据就不会丢失，但是如果服务器系统也宕机了就会丢失数据。

sync_binlog是用来控制binlog持久化方式的参数，设置为1则每次执行commit或者auto commit时都会将binlog中的数据刷新到磁盘上；如果设置为0则binlog的数据将又操作系统的脏数据刷出机制来将数据刷入磁盘，如果系统崩溃，则会有部分binlog数据丢失。如果设置为大于1的整数，比如100，则mysql每执行100次数据操作就写入一次binlog数据到磁盘。

如果这两个参数都设置为1，则每个事物提交时都会经历如下图所示的三个阶段。

第一个阶段是注册事务，即告诉存储引擎这个事务已经可以提交了。第二个阶段是写binlog，第三个阶段是事务数据提交，这三个阶段每阶段都要执行一次fsync操作（即将数据从内存中同步到存储设备上的操作）。而磁盘每秒可执行fsync操作的次数时有限的，如sas盘一秒可以做150次左右的fsync操作，这意味着是能一秒只能提交50个左右的事务。这个问题在官方的mysql5.6后得到了解决，即使两个参数都设置为1也不会对写操作产生大的影响。

5.innodb_log_file_size

这个参数是控制存储引擎事务日志大小的参数，将这个参数调大一点，就这样就不太容易出现因为ib_logfile0中还有数据没有写入数据表中而导致的事务提交阻塞，这个在前面的笔记中已经说明，这里就不再说明了。

6.innodb_io_capacity

这个参数用于控制innodb每次刷新多少个脏页到磁盘上，这决定了innodb存储引擎的吞吐能力。一般当服务器使用的ssd磁盘时会将这个参数适当的调高。

7.innodb_insert_buffer

这个参数是控制插入缓存池的大小的。插入缓存池的作用是，当有写入操作时往往除了修改数据文件外还要修改数据的索引文件，这个缓存池会先将需要修改的索引缓存起来，然后过一段时间将需要修改的索引数据整理合并，以顺序写入的方式写入磁盘，这样就提高了数据的写入效率。如果没有这个缓存吃，那么每次修改数据都要修改索引，这样就往往要执行随机写入，这样就需要大量的磁盘寻道操作，性能会极差。此外需要注意插入缓存池只对二级索引且非唯一索引才有效。而且mysql5.5以后的版本对于update和insert操作同样有效。

最后要提醒一点，无论是提高读性能还是写性能参数优化都只是辅助的手段。提高读性能的根本方法应该是添加索引，优化sql语句。提高写性能的根本方法应该是尽可能使用自增主键来定位数据的更新删除，避免不必要的索引冗余。

三、mysql的二进制安装

mysql除了常用的通过安装包安装，还可以通过linux的二进制安装包安装。二进制安装包安装的优势在于：第一，通过二进制安装可以在同一台服务器上部署多个mysql服务，最大程度的利用服务器资源；第二，二进制安装的服务升级新版本更方便。二进制安装包可以在mysql的官方网站上获取到，此外二进制安装包只能在linux的系统上安装。具体安装步骤如下。

首先在linux系统中添加如下用户和用户组。

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

然后解压下载的二进制安装包，并将解压的文件夹重命名为mysql，然后将文件夹放到/usr/local这个目录下，这么做是为了省去一些参数配置。

接下来创建一个数据存储路径，就是存放mysql数据和redo log的路径，当然也可以用默认的mysql文件夹下的作为数据存储路径，我这里在根目录下创建了一个/mysqldata/node1的路径，命令如下。

mkdiv -p /mysqldata/node1

然后将mysql安装文件夹和node1数据文件夹都分配给刚才创建的mysql用户和用户组，具体方法是定位到文件夹中，然后执行如下命令。

chown -R mysql .
chgrp -R mysql .

接着用mysql文件夹中scripts文件夹下的mysql_install_db脚本来初始化mysql的数据信息到数据存储路径下，命令如下。

scripts/mysql_install_db --user=mysql --datadir=/mysqldata/node1

完成后，在mysqldata文件夹下创建一个my1.cnf的文件作为mysql的配置文件，文件中内容如下。

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
datadir = /mysqldata/node1
port = 5000
server_id = 5000
socket = /tmp/mysql1.sock

character-set-server = utf8
max_connections = 100
log_bin = /mysqldata/node1/mysql-bin
expire_logs_days = 7
max_binlog_size = 100M
binlog_format = ROW

innodb_flush_log_at_trx_commit = 2
sync_binlog = 0

log-error = /mysqldata/node1/mysqld.err

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

当然也可以根据具体需要对这个配置文件进行修改，不过要小心，如果配置文件中写错了会导致mysql无法启动，并且很难定位出错的地方。

最后先将mysql文件夹重新分配给root用户，命令如下（当然也要先定位到mysql文件夹下）。

chown -R root .

然后执行mysql文件夹下bin文件夹中的 mysqld_safe 脚本就可以启动mysql了，命令如下。

bin/mysqld_safe --defaults-file=/mysqldata/my1.cnf --user=mysql &

启动mysql后可以用socket连接进去，命令如下。

mysql -uroot -S /tmp/mysql1.sock

-S参数后面指定的是sock文件的绝对路径，这个是根据再cnf文件中定义的路径决定的，我这里就是定义在tmp文件夹下，文件名是mysql1.sock。

连接进行mysql后可以到mysql数据库下的user表中查看一下当前的账号，将不需要的账号删除，并修改一下密码，默认刚创建的mysql是没有密码的。另外可以将系统自动创建的test库删除，这是因为所有用户对于test库都是有super权限的，因此为了安全最好将test库删除。

如果需要在服务器上再安装多个mysql服务，只要重复上面的步骤即可，但是注意数据存储路径和cnf文件不能使用相同的。
四、主从复制

mysql支持多种类型的主从复制，包括一主一从、一主多从、多主一从（5.7开始才有）、主主复制和联级复制等类型的主从复制。

主从复制的作用主要有三个：第一，用于实时灾备，当线上主库宕机时可以使用从库顶上从而实现数据库的快速恢复；第二，可以实现读写分离降低主库计算压力，就是将部分的查询请求放在从库上完成；第三，可以在从库上进行备份操作，这样可以使备份不影响主库的性能。

mysql实现主从复制的方式是通过复制binlog日志到从库并执行，从而实现主从上执行相同的数据操作命令使数据一致。具体实现的流程入下图所示。

从图中可以看出当从库开启主从复制后从库上会开启两个线程，一个是I/O thread和SQL thread，I/O thread线程会向主库请求binlog日志文件，当主库收到请求后会开启一个log dump thread将binlog文件传输给从库。从库收到binlog数据后I/O thread会将数据写入relay log（中继日志）。而SQL thread会不停的检查relay log中的数据是否有更新，如果有那么SQL thread就会在从库上执行新增的数据操作命令，从而让主从数据一致。

下面具体介绍一下mysql中如何配置主从复制。首先要保证主库与从库间的数据表和数据库一直，因为主从复制是基于binlog的，而binlog只记录数据变更操作，而不记录数据定义操作。所以如果主库中有某个库或表，而从库中没有这样是无法主从复制的。当然主库与从库的表间可以有不同的索引，这个mysql是支持的。为了简单起见这里直接通过mysqldump将主库的所有的数据对象和具体数据导出，命令如下。

mysqldump -uroot -p --socket=/mysqldata/node1/mysql1.sock --single-transaction -A --master-data=1 > /all_db.sql

命令中的-u、-p、--socket和--single-transaction参数在前面的mysql备份相关的笔记中已经解释过了，这里不赘述。-A参数表示备份所有的库和表，--master-data参数设为1表示这个备份将被用于主从复制，这样在备份文件的最后会记录当前备份对应的binlog的文件和位置，如下图所示。

然后在从库上通过如下命令将主库上导出的数据导入。

source /all_db.sql

接下来在主库上创建一个用于主从复制的帐号，命令如下。

grant replication slave on *.* to repl@'127.0.0.1' identified by '123456';

命令中replication slave是指定的权限，这个权限就是主从复制的权限，其他的参数和第一个笔记中记录的参数意义都相同，所以不赘述了。

最后在从库中配置主库信息就完成了主从复制的配置，命令如下。

CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_PORT=5000,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=489;

master_host参数指定了主库的ip，我这里因为主库和从库都在同一台机器上，因此ip是127.0.0.1。master_port指定的是主库的端口号。master_user就是主库上用于主从复制的帐号，就是上面创建的。master_password就是主从负责的帐号的密码。master_log_file和master_log_pos指定主库上的binlog文件和位置，这个就是刚才在备份文件中看到的位置。

这样就已经完成了主从复制的配置，最后通过如下命令启动主从复制。

start slave;

关闭主从复制就是stop slave;

启动主从复制后可以在从库上通过如下命令查看复制状态。

show slave status\G;

这里主要看两个参数，分别是Slave_IO_Running和Slave_SQL_Running，这两个参数值都为YES才说明主从复制成功，如下图所示。

主从复制虽然开启了，但是这样的主从复制存在两个问题。第一，主从复制因为需要通过网络，因此数据的同步必定是有一定的延迟的，如果主库宕机，将数据库切换到从库时就可能会缺失延时的数据；第二，mysql的主库上数据的提交是多线程的，但是从库上只有一个SQL Thread在提交数据，这样当主库有大量并发的写操作时从库上可能就会有大量的数据延时。mysql中通过半同步复制和并行复制来解决这两个问题。

半同步复制是mysql5.5后加入的，需要通过插件来实现。半同步复制的原理很简单，就是将之前事务提交时只要主库提交成功即为成功的模式改为还需要从库返回成功信息才是成功。但是这样会有一个问题，就是数据传输到从库是通过网络的，这样提交肯定会变慢。此外如果从库宕机，那么主库的提交操作也会卡住，知道超时或者从库恢复。也就是说数据的一致性和数据的提交速度只能两者去其一，具体就要根据业务需要判断了。半同步复制的配置方法如下。

首先连接进入主库，在主库中安装半同步复制插件，命令如下。

install PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 

安装完成后可以通过show plugins;命令来查看插件是否已经被安装上了。然后连接从库，在从库上也安装半同步复制插件，命令如下。

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

安装完成后需要在主库和从库上将半同步复制功能启动，默认是关闭的。启动的方法就是将半同步复制的系统参数值改为ON，命令如下。

主库：

SET GLOBAL rpl_semi_sync_master_enabled=1;

从库：

SET GLOBAL rpl_semi_sync_slave_enabled=1;

设置完成后在从库上通过如下命令重启一下主从复制。

stop slave;
start slave;

这样就完成了半同步复制的设置。如果从库宕机，那么主库会等待一段时间，具体时长由参数rpl_semi_sync_master_timeout控制，可以通过set和show命令来设置和查看（在主库上），单位是毫秒。如果超过这个时间就会报超时错误。如果要查看半同步复制的状态可以在主库上通过如下命令来查看。

show global status like "%semi%";

命令执行的结果和各个状态的意义如下图所示。

并行复制就是开启多个SQL Thread来并行的执行从库上的回放日志，从而提升从库同步数据的速度。但是这个功能在mysql5.5中是没有的，只有mysql5.6和后面的版本中才有。配置的方法很简单，只要修改从库上的slave_parallel_workers参数即可，可以通过set命令来修改。该参数默认为0表示关闭并行复制，设为5就表示开启5个SQL Thread线程进行并行复制。