mysql

Mysql log

错误日志

错误日志记录了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。

可以用–log-error[=file_name]保存错误日志文件的位置。

mysql默认在参数DATADIR指定的目录写入日志文件。

二进制日志

二进制日志记录了所有的DDL语句和DML语句，但是不包括数据查询语句。语句以“事件”的形式保存，它描述了数据的更改过程。此日志对于灾难时的数据恢复起着极其重要的作用。

日志的位置以及格式

当用–log-bin[=file_name]选项启动时，mysqld将包含所有更新数据的sql命令写入日志文件。

如果没有给出file_name将被写入datadir指定的目录。

查询日志

查询日志记录了客户端的所有语句，而二进制日志不包含只查询数据的语句。

慢查询日志

当用–log-slow-queries[=file_name]选项启动 mysqld（MySQL 服务器）时，慢查询日志开始被记录。和前面几种日志一样，如果没有给定 file_name 的值，日志将写入参数 DATADIR（数据目录）指定的路径下，默认文件名是 host_name-slow.log。

慢查询日志记录了包含所有执行时间超过参数long_query_time所设置值的sql语句的日志，获得表锁定的时间不算执行时间。

备份与恢复

备份与恢复在任何数据库里面都是非常重要的内容，好的备份策略将会使得数据库中的数据更加高效和安全，和很多数据库类似，mysql的备份也主要分为逻辑备份和物理备份。

备份和恢复一般情况下需要注意以下的事项：

要确定备份表的存储引擎是事务型还是非事务型的，两种不同的存储引擎在处理数据一致性的时候不太一样。
确定使用全备份还是增量备份。
可以考虑采用复制的方法来做异地备份，但是复制不能代替备份，它对于数据库的误操作无能为力。
要定期做备份，备份的周期要充分考虑系统可以承受的恢复时间，备份要在系统负载较小的时候进行。
确保mysql打开了log-bin选项，有了binlog，mysql才可以在必要的时候做完整恢复，或者基于时间点的恢复，或者基于位置的恢复。
要经常做备份恢复测试，确保备份是有效的，并且是可以恢复的。

逻辑备份和恢复

备份

mysql中的逻辑备份是将数据库中的数据备份为一个文本文件，备份的文件可以被查看和进行编辑，在mysql中，使用mysqldump来完成逻辑备份。

有以下的三种方法可以来调用mysqldump

# 1. 备份指定的数据库，或者此数据库中的某些表。
mysqldump [options] db_name [tables]
# 2. 备份指定的一个或者多个数据库
mysqldump [options] --database DB1 [...]
# 3. 备份所有的数据库
mysqldump [options] --all--database

需要强调的是，为了保证数据备份的一致性，MyISAM 存储引擎在备份的时候需要加上-l 参数，表示将所有表加上读锁，在备份期间，所有表将只能读而不能进行数据更新。但是对于事务存储引擎（InnoDB 和 BDB）来说，可以采用更好的选项–single-transaction，此选项将使得 InnoDB 存储引擎得到一个快照（Snapshot），使得备份的数据能够保证一致性。

完全恢复

#in the morning 9am, backup database, the param -l means lock the db,
#the F params means start log file
mysqldump -uroot -p -l -F test > test.dmp
#in the morning 9:30am,excute insert serveral data
insert into emp values(5,'z5')
insert into emp values(6,'z6')
#in the morning 10am, the database suddenly broken up, need to restore
mysqldump -uroot -p test > test.dmp
#and we need to restore the data after using backup
mysqlbinlog localhost-bin.000015 | mysql -uroot -p test

基于时间点恢复

以下是基于时间点恢复的操作步骤。

（1）如果上午 10 点发生了误操作，可以用以下语句用备份和 BINLOG 将数据恢复到故障前：

1	shell>mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 \| mysql -uroot –pmypwd

（2）跳过故障时的时间点，继续执行后面的 BINLOG，完成恢复。

1	shell>mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456\| mysql -uroot -pmypwd \

基于位置恢复

和基于时间点的恢复类似，但是更精确，因为同一个时间点可能有很多条SQL 语句同时执行。恢复的操作步骤如下。

（1）在 shell 下执行如下命令：

1	shell>mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00"/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

该命令将在/tmp 目录创建小的文本文件，编辑此文件，找到出错语句前后的位置号，例如前后位置号分别是 368312 和 368315。

（2）恢复了以前的备份文件后，应从命令行输入下面内容：

shell>mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \

| mysql -u root -pmypwd

shell>mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \

| mysql -u root -pmypwd \

上面的第一行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为 mysqlbinlog 的输出包括每个 SQL 语句记录之前的 SET TIMESTAMP 语句，因此恢复的数据和相关 MySQL 日志将反应事务执行的原时间。

物理备份和恢复

冷备份

冷备份实际上就是停掉数据库服务，cp数据库文件的方法。

热备份

不同引擎方法不同，innodb使用付费软件ibbackup进行热备份。

Mysql 复制

mysql复制是指将主数据库的DDL和DML操作通过二进制日志传到复制服务器（从服务器）上，然后在从服务器上进行重新执行，从而使得从服务器和主服务器的数据保持同步。

mysql支持一台主服务器同时向多台从服务器进行复制，从服务器同时也可以作为其他服务器的主服务器，实现链状的复制。

mysql复制的优点主要包括以下三个方面：

如果主服务器出现问题，可以快速切换到从服务器提供服务。
可以在从服务器上执行查询操作，降低主服务器的访问压力。
可以在从服务器上执行备份，以避免备份期间影响主服务器的服务。

由于mysql实现的是异步的复制，所以主从服务器之间存在一定的差距，在从服务器上进行的查询操作需要考虑到这些数据的差异，实时性要求高仍然需要从主数据库上进行查询。

# 确保主从服务器上安装了相同版本的数据库
# 在主服务器上，设置一个复制使用的账号，并授予replication slave的权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.101' IDENTIFIED BY '1234test';
# 修改主数据库服务的配置文件my.cnf,开启binlog，并且设置server-id，这两个参数的修改
#需要重新启动数据库服务才可以生效
log-bin = /home/mysql/log/mysql-bin.log
server-id = 1 # server id must be only one
# 在主数据库上设置一个读锁定有效这步是为了生成一个一致性的快照
flush tables with read lock;
# 然后得到主服务器上当前的二进制日志名和偏移量值
show master status
# 现在主数据库已经停止了更新操作，需要生成主数据库的备份，备份的方式有很多种，
# 可以直接在操作系统上cp全部文件到从数据库服务器上
tar -cvf data.tar data
# 主数据库的备份完毕后，主数据库可以恢复写操作，剩下的操作只需要在从服务器上执行
unlock tables;
# 修改从服务器的配置文件my.cnf，增加srever-id参数
server-id = 2
# 在从服务器上，使用--skip-slave-start 选项启动从数据库，方便我们进行进一步的管理
# 对从数据库服务器进行响应的设置，指定复制使用的用户，主数据库服务器的ip，端口以及执行复制的日志文件
# 在从服务器上，启动slave线程
start slave
# 此时在slave上面执行show prolists；
show prolists;

日常管理维护

复制环境配置完成后，数据库管理员需要进行日常监控和维护的工作

查看从服务器状态

为了防止复制过程中出现故障从而导致复制进程停滞，我们需要经常检查从服务器的复制状态，一般使用show slave status命令来检查

1	show slave status；

在显示的这些信息中，我们主要关心“Slave_IO_Running”和“Slave_SQL_Running”这两个进程状态是否是“yes”

，这两个进程的含义分别如下。

Slave_IO_Running：此进程负责从服务器（Slave）从主服务器（Master）上读取BINLOG日志，并写入从服务器上的中继日志中。
Slave_SQL_Running：此进程负责读取并且执行中继日志中的BINLOG 日志。

只要其中有一个进程的状态是 no，则表示复制进程停止，错误原因可以从“Last_Errno”字段的值中看到。

除了查看上面的信息，用户还可以通过这个命令了解从服务器的配置情况以及当前和主服务器的同步情况，包括指向那个主服务器，主服务器的端口，复制使用的用户，当前日志恢复到的位置等，这些信息都是记录在从服务器这一端的，主服务器上并没有相应的信息。

Mysql Cluster

cluster的意思就是存储一组节点的组合。

这里的节点是一个逻辑概念，一个计算机上可以存放一个或者多个节点。这些节点的功能各不相同，有的可以用来存储数据，有的用来存储表结构，有的可以对于其他节点进行管理。这些节点组合在一起，可以为应用提供具有高可用，高性能和高可缩放的cluster数据管理。

mysql使用NDB存储引擎对于数据节点进行存储。

理论上，mysql cluster可以通过数据的分布式存储和可扩展性的系统架构，可以满足更大规模的应用，而且可以通过冗余策略提高系统的可靠性和数据的有效性。

cluster arch

mysql的系统架构图

可以看出，mysqlcluster按照节点类型可以分为三部分

管理节点：实际操作中，是通过一个叫做config.ini的配置文件进行维护而起到管理的作用。该文件可以用来配置有多少需要维护的副本，需要在每个数据节点上为数据和索引分配多少内存，数据节点的位置，在每个数据节点上保存数据的磁盘位置，sql节点的位置等信息。
sql节点： sql节点可以理解为应用和数据节点之间的一个桥梁，应用不能直接访问数据节点，只能先访问sql节点，然后sql节点再去访问数据节点来返回数据，cluster中可以有多个sql节点，通过每个sql节点查询到的数据都是一致的，通常来说，sql节点越多，分配到每个sql节点的负载就越小。
数据节点：用来存放cluster中的数据，可以有多个数据节点，每个数据节点可以有多个镜像节点。任何一个数据节点发生故障，只要它的镜像节点正常，cluster就可以正常运行。

mysql cluster的访问过程：前台应用利用一定的负载均衡算法对数据库的访问分散到不同的sql节点上，然后sql节点对数据节点进行数据访问并从数据节点返回结果，最后sql节点将收到的结果返回给前台应用，而管理节点并不参与访问过程，它只用来对于sql节点和数据节点进行配置管理。