1 数据库预防性维护工作的基本原则
遇到数据表损坏或数据丢失,处理原则:
用来完成以上任务的工具包括MySQL服务器本身的能力和MySQL发行版本自带的工具程序: myisamchk程序是一个可以直接操作数据表文件的独立程序。
2 在MySQL服务器允许时维护数据库 执行数据库维护操作的另一种办法,是使用一个不依赖于MySQL服务器的外部程序,但这个办法需要由你来协调各有关数据表上的访问冲突。如:检查和修复MyISAM数据表的工作还可以通过允许myisamchk程序的办法来进行,该程序将直接打开有关的数据表文件。在myisamchk程序在在访问数据表文件时,必须阻止MySQL服务器去修改那个数据表。
MySQL服务器提供了两种锁定机制:
2.1 以只读方式或读写方式锁定一个或多个数据表 使用lock table和unlock table语句来锁定数据表和接触锁定。它们还使用flush table语句通知MySQL服务器把挂起的改动写入硬盘。这个思路关键是必须在同一个mysql会话任务里执行所有的lock flush 和 unlock语句。
这里描述的利用MySQL服务器的内部锁定机制去锁定数据表的办法仅适用于那些把每个数据表分别表示为一组相关文件的存储引擎,不适用把多个数据表的信息保存在同一个给定文件里的存储一引擎。 mysql> lock table tbl_name read; mysql> flush table tbl_name; 释放已经打开的表(如果已经打开) 然后在另一窗口运行: myisamchk tbl_name 或者需要指定绝对路径 然后回来: mysql> unlock table;
2) 以读写方式锁定一个数据表 mysql> lock table tbl_name write; mysql>flush table tbl_name; 切换到另一个终端: myisamchk –recover tbl_name; 然后回来: flush table tbl_name; 再次刷新 unlock table; 2.2 以只读方式锁定所有的数据库 flush tables with read lock; set global read_only = ON; FLUSH语句将申请一个全局性的读操作锁,set语句将在其他客户释放他们锁定的所有数据表并完成所有正在执行的事务之后才开始执行,在此之前一直处于被堵塞状态。 释放锁: set global read_only = off; unlock tables;
3 预防性维护 对于MyISAM数据表,MySQL服务器提供了一种可选的数据表自动恢复能力,但必须启用这一能力。启用MyISAM数据表自动恢复能力的办法是使用—myisam-recover=level选项来启动MySQL服务器。level的值是一个以逗号分隔的、由以下几个值中的一个或多个构成的列表:backup(如果自动恢复工作需要修改某个数据表,先为它创建一个备份),force(强制恢复,可能丢失数据),quick(快速恢复)或default(不进行任何其它特殊处理的普通恢复,跟把该选项省略一样)。 如果在运行MySQL服务器时使用了—delay-key-write选项或把一些MyISAM数据表配置成使用键字缓写功能,就更应该启用MyISAM存储引擎的自动恢复能力了。所谓键字缓写是把索引数据的修改缓存在内存里,等到关闭数据表时才把它们写入磁盘文件。 3.2 定期进行预防性维护
4 制作数据库备份 mysqldump程序必须与MySQL服务器配合使用,所以可以可以在MySQL服务器正在运行时使用它。MyISAM和InnoDB数据表通常与具体的机器无关,这两类数据表的数据表文件都可以直接复制到运行在另一台有着不同硬件结果的机器上的MySQL服务器去。 4.1 用mysqldump程序制作文本备份 mysqldump sampdb member > member.sql mysql sampdb < member.sql mysqldump –all-databases > /home/root/dump.2013.03.05 mysqldump sampdb > /home/root/sample.bk 一定不要用mysqlimport程序去重新加载mysqldb程序的SQL格式的输出。mysqlimport程序只能读取数据行,不能用来读取SQL语句。 减少转存文件长度的一个办法是对它进行压缩。 mysqldum sampldb | gzip > /root/db.bk 还可以分别转存各个数据表 mysqldump sampdb member president > hist-l.sql mysqldump sampdb student score grade_event absence > gradebook.sql
mysqldump程序有许多选项:
4.2 制作二进制数据库备份 4.3 备份InnoDB数据表
5 把数据库复制到另一个服务器
6 数据表的检查和修复 check table tbl1, tbl2, tbl3 fast medium; 2)用repair table语句修复数据表(不能对应InnoDB)
6.2 用mysqlcheck程序检查和修复数据表 mysqlcheck sampledb; mysqlcheck sampledb president member; mysqlcheck –databases sampledb test; mysqlcheck –all-databases 与check table和repair table语句相比,语句需要明确给出想检查或修复的数据表的名字。 mysqldump程序选项 check table语句选项 --check-only-changed changed --extended extended --fast fast --medium-check medium --quick quick mysqlcheck程序选项 repair table语句选项 --repair 无 --repair –extended extended --repair –quick quick --repair –use-frm use_frm
6.3 用myisamchk程序检查和修复数据表 myisamchk tbl_name myisamchk –check tbl_name 如果不带任何选项,myisamchk程序的默认行为将是进行—check检查。如果myisamchk程序报告数据有错误,应该尽量修复它。 2)用myisamchk程序修复数据表 myisamchk –recover –quick tbl_name myisamchk –recover tbl_name myisamchk –safe-recover tbl_name 3)让myisamchk程序运行得更快 myisamchk –sort_buffer_size=16M –read_buffer_size=1M –wreite_buffer_size=1M other-options tbl_name
7 使用备份进行数据恢复 mysqlbinlog binlog.[0-9]* | mysql 如果要先编辑日志文件在重新执行它们,可以先把它们转换为文本格式并把转换结果保存为一个临时文件,再编辑那个临时文件,然后再把编辑结果导向mysql程序: mysqlbinlog binlog.[0-9]* > text_file vi text_file mysql < text_file 不要使用mysqlbinlog和mysql一个个地处理二进制日志文件。这是因为个日志文件之间可能存在着某种依赖关系。 如只需要把作用于某特定数据库的语句提取出来: mysqlbinlog –database=db_name binlog.[0-9]* | mysql
7.4 InnoDB存储引擎的自动恢复功能 [mysqld] innodb_forece_recovery=level InnoDB存储引擎默认使用的自动恢复策略比较保守,它会尽量使用一个比较小的level值。在需要强行启动的绝大多数场合,建议从4开始。在强行启动后,用mysqldump转存数据,然后删除InnoDB数据表,再用备份恢复它。
8 设置复制服务器
8.2 建立主从复制关系 [mysqld] server-id=master_server_id log-bin=binlog_name [mysqld] server-id=slave_server_id 在主服务器上,创建一个账户供从服务器连接主服务器并修改信息: create user ‘slave_user’@’slave_host’ identified by ‘slave_pass’; grant replication slave on *.* to ‘slave_user’@’slave_host’ 连接到主服务器并通过执行show master status语句确定当前的复制坐标 mysql> flush tables; show master status;
记住File和Position值。 把主服务器上的数据库复制到从服务器。 连接到从服务器并使用change master语句来配置它,这包括用来连接主服务器的参数和初始复制坐标告诉从服务器: change master to master_host=’’, master_user=’’, master_password=’’, master_log_file=’log_file_name’, master_log_pos=log_file_pos; 如果主服务器所监听的网络端口不是默认端口,可以在change语句里包含一个master_port选项以明确给出一个端口号。
让从服务器开始复制 从服务器把create master语句所给出的参数保存在其数据目录中的一个名为master.info的文件里以记录初始复制状态,并随着镜像工作的进展而刷新那个文件。 在从服务器的选项文件里加上以下代码,让它不要执行来自主服务器的对mysql数据库的任何修改: [mysqld] replicate-ignore-db=mysql 从服务器使用两个内部线程来管理服务活动。IO线程负责与主服务器进行通信,接收来自主服务器的信息,把接收到的数据修改命名写入从服务器的中继日志。SQL线程负责从中继日志读出数据修改命令并执行。可以通过在slave stop或slave start语句末尾加上io_thread 或sql_thread关键字的办法分别挂起或重新开始这两个线程中的任何一个。
8.3 二进制日志的格式 日志格式可以通过在启动时使用—binlog-format选项或是通过在运行时设置binlog_format系统变量的办法来加以选择。从MySQL 5.1.2开始,默认的日志格式是MIXED。其它可取值是STATEMENT或ROW。 8.4 使用复制机制制作备份 (责任编辑:好模板) |