MySQL INNODB 损坏恢复指南

首要步骤是关闭MYSQL实例和备份数据目录

/etc/init.d/mysqld stop

mkdir /root/myql_backup
tar -jcvf /root/myql_backup/mysql_data.tar.bz2 /var/lib/mysql

 

尝试启动 mysql 实例

/etc/init.d/mysqld start

 

如果实例不崩溃那么 用mysqldump做逻辑备份

 

/etc/init.d/mysql start
mysqldump –single-transaction -AER > /root/dump_wtrans.sql
mysqldump -AER > /root/dump.sql

 

 

如果 MYSQL 实例崩溃则 尝试设置 innodb_force_recovery 参数

 

例如

 

mode=1; sed -i “/^\[mysqld\]/{N;s/$/\ninnodb_force_recovery=$mode/}” /etc/my.cnf

 

注意这些的mode=1 设置了 innodb_force_recovery 参数的级别

 

innodb_force_recovery 的参数级别含义为:

 

Mode 1 当发现一个损坏的数据页时不让MYSQL实例自动崩溃
Mode 2 不启用后台操作
Mode 3 不尝试 回滚事务
Mode 4 不计算状态也不应用buffer change
Mode 5 在启动过程中不去关心undo log
Mode 6 在启动过程中不关心重做日志ib_logfiles, 不去做前滚

 

以上 mode 2 会包含 mode 1, mode 3会包含 mode 2和 mode 1 ,依此类推

 

同时从 MYSQL 5.6.15 开始 mode 4-6 会让mysql 以只读模式运行

 

若已经设置了innodb_force_recovery =6 还是有问题 ,那么需要进一步诊断

 

检查日志

 

tail -200 /var/lib/mysql/`hostname`.err
tail -f /var/log/mysqld.log

如发现

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 515891.

 

 

以上报错说明mysql innodb 引擎发现有数据页损坏 进一步诊断:

 

#!/bin/bash
for i in $(ls /var/lib/mysql/*/*.ibd)
do
innochecksum $i
done

or

DATADIR=/var/lib/mysql; find $DATADIR -type f -name *.ibd -o -name ibdata* | xargs -I{} innochecksum {}

 

如发现

 

mysql: 120901 9:43:55 InnoDB: Error: page 70944 log sequence number 8 1483471899
mysql: InnoDB: is in the future! Current system log sequence number 5 612394935.
mysql: InnoDB: Your database may be corrupt or you may have copied the InnoDB
mysql: InnoDB: tablespace but not the InnoDB log files. See
mysql: InnoDB: [url=http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21.2 Starting InnoDB on a Corrupted Database[/url]

 

以上日志为LSN 不同步问题, 其一般可以通过 设置 innodb_force_recovery解决的

 

如发现

 

[ERROR] Table ./database/table has no primary key in InnoDB data dictionary, but has one in MySQL!

InnoDB: Error: table ‘database/table’
InnoDB: in InnoDB data dictionary has tablespace id 423,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?

[ERROR] Cannot find or open table database/table from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn’t support.

可能丢失了表的.frm 或 .ibd文件 ,找回这些文件

 

当实例可以启动 那么如何检测表是否存在讹误?

 

可以使用 check table 命令

 

 

mysql> CHECK TABLE roundcube.users;
+—————–+——-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+—————–+——-+———-+———-+
| roundcube.users | check | status | OK |
+—————–+——-+———-+———-+
1 row in set (0.13 sec)

mysql> CHECK TABLE roundcube.dictionary;
+———————-+——-+———-+—————————————————————-+
| Table | Op | Msg_type | Msg_text |
+———————-+——-+———-+—————————————————————-+
| roundcube.dictionary | check | Warning | InnoDB: Tablespace is missing for table ’roundcube/dictionary’ |
| roundcube.dictionary | check | Error | Table ’roundcube.dictionary’ doesn’t exist |
| roundcube.dictionary | check | status | Operation failed |
+———————-+——-+———-+—————————————————————-+
3 rows in set (0.00 sec)

 

 

如果以上检测发现讹误,则可能导致mysql崩溃 ,那么可能需要使用 innodb_force_recovery=1 来启动实例 否则可能启动时实例崩溃

可以使用 mysqlcheck 来检测一个数据库

 

[root@ocp ~]# mysqlcheck -A -uroot -p
Enter password:
mysql.columns_priv OK
mysql.component OK
mysql.db OK
mysql.default_roles OK
mysql.engine_cost OK
mysql.func OK
mysql.general_log OK
mysql.global_grants OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.password_history OK
mysql.plugin OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.role_edges OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
recovery.testeasy OK
sys.sys_config OK

 

如何从frm文件中恢复create table建表语句

 

可以从 https://downloads.mysql.com/archives/utilities/ 下载 MySQL Utilities

 

 

tar xvzf mysql-utilities*

cd mysql-utilities-1.4.3
chmod +x setup.py
./setup.py build
./setup.py install

 

 

mysqlfrm 会自己建一个mysql实例 需要单独的端口

mysqlfrm –basedir=/usr –user=mysql –port=3308 /var/lib/mysql/testdb/staff.frm

# Spawning server with –user=mysql.
# Starting the spawned server on port 3308 … done.
# Reading .frm files
#
# Reading the staff.frm file.
#
# CREATE statement for staff.frm:
#

CREATE TABLE `staff` (
`staff_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`address_id` smallint(5) unsigned NOT NULL,
`picture` blob,
`email` varchar(50) DEFAULT NULL,
`store_id` tinyint(3) unsigned NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT ‘1’,
`username` varchar(16) NOT NULL,
`password` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`staff_id`),
KEY `idx_fk_store_id` (`store_id`),
KEY `idx_fk_address_id` (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

#…done.

 

 

有时需要 SET FOREIGN_KEY_CHECKS=0; 才能建表成功

在实例 没崩溃情况下 如何重建有问题的表

 

 

mysql -u root -p

USE dbname;
CREATE TABLE tablename_recovered LIKE tablename;
INSERT INTO tablename_recovered SELECT * FROM tablename;

 

这里 先见表 后插入

 

插入如果 遇到问题页面 ,那么可以用limit

 

insert ignore into tablename_recovered select * from tablename limit 10;
insert ignore into tablename_recovered select * from tablename limit 50;
insert ignore into tablename_recovered select * from tablename limit 100;
insert ignore into tablename_recovered select * from tablename limit 200;

 

 

测试 下插入到哪个位置 才报错

 

如果成功复制表 那么把原表删除 新表重命名

 

 

DROP dbname.tablename;
RENAME TABLE dbname.tablename_recovered TO dbname.tablename;

 

 

还原 所有数据库和 重置 ibdata/ib_log files

首先要有 有效的备份, 做这些操作前 确认这个备份可用!!

mysqldump -AER > /root/recovery_dump.sql

干掉所有 有问题的db

 

 

mysql> SET FOREIGN_KEY_CHECKS=0;
mysql> DROP DATABASE db1;
mysql> DROP DATABASE db2;

重启mysql 实例

mysql -e “SET GLOBAL innodb_fast_shutdown = 0”
/etc/init.d/mysql stop

sed -i ‘/innodb_force_recovery/d’ /etc/my.cnf

nohup /etc/init.d/mysql start & tail -f /var/lib/mysql/`hostname`.err

 

 

恢复 数据

 

mysql < /root/recovery_dump.sql

 

对于 Log Sequence Number Mismatched/In Future 可以考虑重建整个数据库 也可以重建重做日志

 

 

mysql -e “SET GLOBAL innodb_fast_shutdown = 0”
/etc/init.d/mysql stop
cd /var/lib/mysql
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
/etc/init.d/mysql start

或者 临时交换存储引起 为myisam 然后换回来

 

mysql -e “SELECT concat(‘ALTER TABLE ‘, TABLE_NAME,’ ENGINE=MYISAM;’) FROM Information_schema.TABLES WHERE TABLE_SCHEMA = ‘db_name’ AND ENGINE = ‘InnoDB’ AND TABLE_TYPE = ‘BASE TABLE'”

/etc/init.d/mysql stop
mkdir -p /root/innodb.bak
mv ib* /root/innodb.bak/
/etc/init.d/mysql start
mysql -e “SELECT concat(‘ALTER TABLE ‘, TABLE_NAME,’ ENGINE=InnoDB;’) FROM Information_schema.TABLES WHERE TABLE_SCHEMA = ‘db_name’ AND ENGINE = ‘MyISAM'”

 

如何合适的删除.ibd文件 可以使用如下命令 SET FOREIGN_KEY_CHECKS=0;

 

mysql -e “ALTER TABLE roundcube.staff DISCARD TABLESPACE”

 

如果误删除了 .ibd文件 如何 基于备份恢复前的清理工作

 

[ERROR] MySQL is trying to open a table handle but the .ibd file for table dbname/tblname does not exist.

ALTER TABLE dbname.tblname DISCARD TABLESPACE;

InnoDB: Warning: cannot delete tablespace 251 in DISCARD TABLESPACE.
InnoDB: But let us remove the insert buffer entries for this tablespace.

DROP TABLE dbname.tblname;

InnoDB: We removed now the InnoDB internal data dictionary entry InnoDB: of table `dbname/tblname`.

 

 

如上完成后 再恢复重建整个表 .frm 文件在 但数据字典里没这个表了 先备份.frm 文件

 

然后执行 create table重建表 丢失.frm文件

 

但字典里有这个表

InnoDB: Error: table dbname/tblname already exists in
InnoDB internal InnoDB: data dictionary.

Have you deleted the .frm file

InnoDB: and not used DROP TABLE? …
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed.

 

 

可以在别的db下重建这个表获得.frm 文件 然后拷贝过去

# mysql

mysql> CREATE DATABASE test2;
mysql> CREATE TABLE … CHARSET=utf8;
mysql> quit
# cp /var/lib/mysql/test2/test.frm /var/lib/mysql/test/
# mysql
mysql> SET FOREIGN_KEY_CHECKS=0;
mysql> DROP TABLE test.test;

MySQL Centos/Redhat 6多版本迅速安装脚本

MYSQL 5.5.62

 

#如果之前有MYSQL 那么先停服务
/etc/init.d/mysqld stop


#如果之前已经装过MYSQL 那么先备份数据目录
tar -jcvf mysql.tar.bz2 /var/lib/mysql 

#卸载之前已经安装过的MYSQL软件RPM 


rpm -qa|grep mysq| xargs rpm -e --nodeps


#下载MYSQL 5.5.62

wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-common-5.5.62-2.el6.x86_64.rpm
wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-libs-compat-5.5.62-2.el6.x86_64.rpm
wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-libs-5.5.62-2.el6.x86_64.rpm
wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-client-5.5.62-2.el6.x86_64.rpm
wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-server-5.5.62-2.el6.x86_64.rpm


#安装

rpm -ivh mysql-community-libs-compat-5.5.62-2.el6.x86_64.rpm mysql-community-client-5.5.62-2.el6.x86_64.rpm mysql-community-common-5.5.62-2.el6.x86_64.rpm mysql-community-libs-5.5.62-2.el6.x86_64.rpm mysql-community-server-5.5.62-2.el6.x86_64.rpm



修改/etc/my.cnf 中的 datadir 到 你要的目录

mkdir /@@datadir 
chown mysql:mysql /@@datadir 

mysql_install_db


chown -R mysql:mysql /@@datadir 

mysqld_safe  &

mysql

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mysql');
FLUSH PRIVILEGES;
exit 



/etc/init.d/mysqld stop

/etc/init.d/mysqld start

mysql -uroot -pmysql




 

MYSQL 5.6.46

 

 

#如果之前有MYSQL 那么先停服务
/etc/init.d/mysqld stop


#如果之前已经装过MYSQL 那么先备份数据目录
tar -jcvf mysql.tar.bz2 /var/lib/mysql 

#卸载之前已经安装过的MYSQL软件RPM 


rpm -qa|grep mysq| xargs rpm -e --nodeps


#下载MYSQL 5.6.46
wget --no-check-certificate   https://zcdn.askmaclean.com/mysql-community-common-5.6.46-2.el6.x86_64.rpm
wget --no-check-certificate   https://zcdn.askmaclean.com/mysql-community-libs-compat-5.6.46-2.el6.x86_64.rpm
wget --no-check-certificate   https://zcdn.askmaclean.com/mysql-community-libs-5.6.46-2.el6.x86_64.rpm
wget --no-check-certificate   https://zcdn.askmaclean.com/mysql-community-client-5.6.46-2.el6.x86_64.rpm
wget --no-check-certificate   https://zcdn.askmaclean.com/mysql-community-server-5.6.46-2.el6.x86_64.rpm



#安装


rpm -ivh mysql-community-libs-compat-5.6.46-2.el6.x86_64.rpm mysql-community-libs-5.6.46-2.el6.x86_64.rpm mysql-community-client-5.6.46-2.el6.x86_64.rpm mysql-community-common-5.6.46-2.el6.x86_64.rpm  mysql-community-server-5.6.46-2.el6.x86_64.rpm


修改/etc/my.cnf 中的 datadir 到 你要的目录

mkdir /@@datadir 
chown mysql:mysql /@@datadir 

mysql_install_db


chown -R mysql:mysql /@@datadir 

mysqld_safe  &


mysql

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mysql');
FLUSH PRIVILEGES;
exit 



/etc/init.d/mysqld stop

/etc/init.d/mysqld start

mysql -uroot -pmysql




 

5.7.28

 

>#如果之前有MYSQL 那么先停服务
/etc/init.d/mysqld stop


#如果之前已经装过MYSQL 那么先备份数据目录
tar -jcvf mysql.tar.bz2 /var/lib/mysql 

#卸载之前已经安装过的MYSQL软件RPM 


rpm -qa|grep mysq| xargs rpm -e --nodeps


#下载MYSQL 5.7.28 
wget --no-check-certificate     https://zcdn.askmaclean.com/mysql-community-common-5.7.28-1.el6.x86_64.rpm
wget --no-check-certificate     https://zcdn.askmaclean.com/mysql-community-libs-compat-5.7.28-1.el6.x86_64.rpm
wget --no-check-certificate     https://zcdn.askmaclean.com/mysql-community-libs-5.7.28-1.el6.x86_64.rpm
wget --no-check-certificate     https://zcdn.askmaclean.com/mysql-community-client-5.7.28-1.el6.x86_64.rpm
wget --no-check-certificate     https://zcdn.askmaclean.com/mysql-community-server-5.7.28-1.el6.x86_64.rpm


rpm -ivh mysql-community-common-5.7.28-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.28-1.el6.x86_64.rpm mysql-community-libs-5.7.28-1.el6.x86_64.rpm  mysql-community-client-5.7.28-1.el6.x86_64.rpm mysql-community-server-5.7.28-1.el6.x86_64.rpm



修改/etc/my.cnf 中的 datadir 到 你要的目录

mkdir /@@datadir 
chown mysql:mysql /@@datadir 

mysqld --initialize


chown -R mysql:mysql /@@datadir 

mysqld_safe --skip-grant-tables &

mysql

UPDATE mysql.user SET authentication_string = PASSWORD('mysql') , password_expired = 'N' WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;
exit 



/etc/init.d/mysqld stop

/etc/init.d/mysqld start

mysql -uroot -pmysql



8.0.17

 

>>#如果之前有MYSQL 那么先停服务
/etc/init.d/mysqld stop


#如果之前已经装过MYSQL 那么先备份数据目录
tar -jcvf mysql.tar.bz2 /var/lib/mysql 

#卸载之前已经安装过的MYSQL软件RPM 


rpm -qa|grep mysq| xargs rpm -e --nodeps


#下载MYSQL 8.0.17

wget --no-check-certificate    https://zcdn.askmaclean.com/mysql-community-common-8.0.17-1.el6.x86_64.rpm
wget --no-check-certificate    https://zcdn.askmaclean.com/mysql-community-libs-8.0.17-1.el6.x86_64.rpm
wget --no-check-certificate    https://zcdn.askmaclean.com/mysql-community-client-8.0.17-1.el6.x86_64.rpm
wget --no-check-certificate    https://zcdn.askmaclean.com/mysql-community-server-8.0.17-1.el6.x86_64.rpm



rpm -ivh mysql-community-client-8.0.17-1.el6.x86_64.rpm mysql-community-common-8.0.17-1.el6.x86_64.rpm  mysql-community-libs-8.0.17-1.el6.x86_64.rpm mysql-community-server-8.0.17-1.el6.x86_64.rpm 
 
修改/etc/my.cnf 中的 datadir 到 你要的目录

mkdir /@@datadir 
chown mysql:mysql /@@datadir 

mysqld --initialize


chown -R mysql:mysql /@@datadir 

mysqld_safe --skip-grant-tables &


 mysql 
 
UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;



/etc/init.d/mysqld stop 

/etc/init.d/mysqld start


mysql

ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql';
FLUSH PRIVILEGES;
exit;


mysql -uroot -pmysql



MySQL FLUSH TABLE WITH READ LOCK详解

内容来源:https://www.cnblogs.com/cchust/p/4603599.html

 

FLUSH TABLES WITH READ LOCK简称(FTWRL),该命令主要用于备份工具获取一致性备份(数据与binlog位点匹配)。由于FTWRL总共需要持有两把全局的MDL锁,并且还需要关闭所有表对象,因此这个命令的杀伤性很大,执行命令时容易导致库hang住。如果是主库,则业务无法正常访问;如果是备库,则会导致SQL线程卡住,主备延迟。本文将详细介绍FTWRL到底做了什么操作,每个操作的对库的影响,以及操作背后的原因。

 

FTWRL做了什么操作?

 

FTWRL主要包括3个步骤:

 

1.上全局读锁(lock_global_read_lock)
2.清理表缓存(close_cached_tables)
3.上全局COMMIT锁(make_global_read_lock_block_commit)

 

FTWRL每个操作的影响

 

上全局读锁会导致所有更新操作都会被堵塞;关闭表过程中,如果有大查询导致关闭表等待,那么所有访问这个表的查询和更新都需要等待;上全局COMMIT锁时,会堵塞活跃事务提交。由于FTWRL主要被备份工具使用,后面会详细解释每个步骤的作用,以及存在的必要性。FTWRL中的第1和第3步都是通过MDL锁实现,关于MDL的实现,我之前总结了MDL锁的文章,这里主要介绍清理表缓存的流程。

 

清理表缓存

 

每个表在内存中都有一个table_cache,不同表的cache对象通过hash链表维护。
访问cache对象通过LOCK_open互斥量保护,每个会话打开的表时,引用计数share->ref_count++,
关闭表时,都会去对引用计数share->ref_count–。
若发现是share对象的最后一个引用(share->ref_count==0),并且share有old_version,
则将table_def_cache从hash链表中摘除,调用free_table_share进行处理。关键函数close table流程如下:

 

1.关闭所有未使用的表对象
2.更新全局字典的版本号
3.对于在使用的表对象,逐一检查,若表还在使用中,调用MDL_wait::timed_wait进行等待
4.将等待对象关联到table_cache对象中
5.继续遍历使用的表对象
6.直到所有表都不再使用,则关闭成功。

 

清理表缓存函数调用

 

mysql_execute_command->reload_acl_and_cache->close_cached_tables
->TABLE_SHARE::wait_for_old_version->MDL_wait::timed_wait->
inline_mysql_cond_timedwait

 

会话操作表流程

 

1.打开表操作,若发现还有old_version,则进行等待
2.share->ref_count++
3.操作完毕,检查share->ref_count–是否为0
4.若为0,并且检查发现有新版本号,则认为cache对象需要重载
5.将cache对象摘除,调用MDL_wait::set_status唤醒所有等待的线程。

 

关闭表对象函数调用

 

dispatch_command->mysql_parse->mysql_execute_command->
close_thread_tables->close_open_tables->close_thread_table->
intern_close_table->closefrm->release_table_share->my_hash_delete->
table_def_free_entry->free_table_share

 

关闭表导致业务库堵住的典型场景

 

假设有3个会话,会话A执行大查询,访问t表;然后一个备份会话B正处于关闭表阶段,需要关闭表t;随后会话C也请求访问t表。三个会话按照这个顺序执行,我们会发现备份会话B和会话C访问t表的线程都处于“waiting for table flush”状态。这就是关闭表引起的,这个问题很严重,因为此时普通的select查询也被堵住了。下面简单解释下原因:

 

1.会话A打开表t,执行中……
2.备份会话B需要清理表t的cache,更新版本号(refresh_version++)
3.会话B发现表t存在旧版本(version != refresh_version),表示还有会话正在访问表t,
等待,加入share对象的等待队列
4.后续会话C同样发现存在旧版本(version != refresh_version),
等待,加入share对象的等待队列
……
5. 大查询执行完毕,调用free_table_share,唤醒所有等待线程。

free_table_share //逐一唤醒所有等待的线程。
{
while ((ticket= it++))
ticket->get_ctx()->m_wait.set_status(MDL_wait::GRANTED);
}

第4步与第5步之间,所有的访问该表的会话都处于“waiting for table flush”状态,唯有大查询结束后,等待状态才能解除。

主备切换场景

在生产环境中,为了容灾一般mysql服务都由主备库组成,当主库出现问题时,可以切换到备库运行,保证服务的高可用。在这个过程中有一点很重要,避免双写。因为导致切换的场景有很多,可能是因为主库压力过大hang住了,也有可能是主库触发mysql bug重启了等。当我们将备库写开启时,如果老主库活着,一定要先将其设置为read_only状态。“set global read_only=1”这个命令实际上也和FTWRL类似,也需要上两把MDL,只是不需要清理表缓存而已。如果老主库上还有大的更新事务,将导致set global read_only hang住,设置失败。因此切换程序在设计时,要考虑这一点。

关键函数:fix_read_only

1.lock_global_read_lock(),避免新的更新事务,阻止更新操作
2.make_global_read_lock_block_commit,避免活跃的事务提交

FTWRL与备份

Mysql的备份方式,主要包括两类,逻辑备份和物理备份,逻辑备份的典型代表是mysqldump,物理备份的典型代表是extrabackup。根据备份是否需要停止服务,可以将备份分为冷备和热备。冷备要求服务器关闭,这个在生产环境中基本不现实,而且也与FTWRL无关,这里主要讨论热备。Mysql的架构支持插件式存储引擎,通常我们以是否支持事务划分,典型的代表就是myisam和innodb,这两个存储引擎分别是早期和现在mysql表的默认存储引擎。我们的讨论也主要围绕这两种引擎展开。对于innodb存储引擎而言,在使用mysqldump获取一致性备份时,我们经常会使用两个参数,–single-transaction和–master-data,前者保证innodb表的数据一致性,后者保证获取与数据备份匹配的一致性位点,主要用于搭建复制。现在使用mysql主备集群基本是标配,所以也是必需的。对于myisam,就需要通过–lock-all-tables参数和–master-data来达到同样的目的。我们在来回顾下FTWRL的3个步骤:

1. 上全局读锁
2. 清理表缓存
3. 上全局COMMIT锁

第一步的作用是堵塞更新,备份时,我们期望获取此时数据库的一致状态,不希望有更多的更新操作进来。对于innodb引擎而言,其自身的MVCC机制,可以保证读到老版本数据,因此第一步对它使多余的。第二步,清理表缓存,这个操作对于myisam有意义,关闭myisam表时,会强制要求表的缓存落盘,这对于物理备份myisam表是有意义的,因为物理备份是直接拷贝物理文件。对于innodb表,则无需这样,因为innodb有自己的redolog,只要记录当时LSN,然后备份LSN以后的redolog即可。第三步,主要是保证能获取一致性的binlog位点,这点对于myisam和innodb作用是一样的。

所以总的来说,FTWRL对于innodb引擎而言,最重要的是获取一致性位点,前面两个步骤是可有可无的,因此如果业务表全部是innodb表,这把大锁从原理上来讲是可以拆的,而且percona公司也确实做了这样的事情,具体大家可以参考blog链接。此外,官方版本的5.5和5.6对于mysqldump做了一个优化,主要改动是,5.5备份一个表,锁一个表,备份下一个表时,再上锁一个表,已经备份完的表锁不释放,这样持续进行,直到备份完成才统一释放锁。5.6则是备份完一个表,就释放一个锁,实现主要是通过innodb的保存点机制。相关的bug可以参考链接:http://bugs.mysql.com/bug.php?id=71017。

参考文献

https://www.percona.com/blog/2014/03/11/introducing-backup-locks-percona-server-2/

https://www.percona.com/blog/2012/03/23/how-flush-tables-with-read-lock-works-with-innodb-tables/

http://bugs.mysql.com/bug.php?id=71017

http://www.cnblogs.com/bamboos/p/3458233.html

‘Client does not support authentication protocol requested by server; consider upgrading MySQL client’, PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR

PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR
PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR

{ Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
    at Handshake.Sequence._packetToError (/root/pingnovel/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Handshake.ErrorPacket (/root/pingnovel/node_modules/mysql/lib/protocol/sequences/Handshake.js:123:18)
    at Protocol._parsePacket (/root/pingnovel/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/root/pingnovel/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/root/pingnovel/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/root/pingnovel/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket. (/root/pingnovel/node_modules/mysql/lib/Connection.js:91:28)
    at Socket. (/root/pingnovel/node_modules/mysql/lib/Connection.js:525:10)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    --------------------
    at Protocol._enqueue (/root/pingnovel/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Protocol.handshake (/root/pingnovel/node_modules/mysql/lib/protocol/Protocol.js:51:23)
    at Connection.connect (/root/pingnovel/node_modules/mysql/lib/Connection.js:119:18)
    at Object. (/root/pingnovel/src/db1.js:11:12)
    at Module._compile (module.js:652:30)
    at Object.Module._extensions..js (module.js:663:10)
    at Module.load (module.js:565:32)
    at tryModuleLoad (module.js:505:12)
    at Function.Module._load (module.js:497:3)
    at Function.Module.runMain (module.js:693:10)
  code: 'ER_NOT_SUPPORTED_AUTH_MODE',
  errno: 1251,
  sqlMessage: 'Client does not support authentication protocol requested by server; consider upgrading MySQL client',
  sqlState: '08004',
  fatal: true }


这种授权协议问题,可以通过使用MYSQL NATIVE PASSWORD来解决,例如:


ALTER USER ABC IDENTIFIED WITH mysql_native_password BY 'password'

【MySQL学生手册】mysqldump数据恢复

本文地址:https://www.askmaclean.com/archives/mysql-data-recovery.html

 

11.8 数据恢复

进行数据恢复操作的前提是你需要一份备份,作为恢复所需的一部分。它可以是你数据库某个时间点所做的snapshot快照(当进行备份时)。不过,对于一个活动的服务端,数据会在最近的备份之后就会发生改变。因此恢复操作所需的另一部分材料则是服务端之后所做的数据变更记录 —— 也就是,binary log。因此一个恢复操作涉及到使用备份来恢复数据库并且之后重新执行在binary log中所包含的备份之后的数据修改操作。

 

通常恢复操作有以下步骤:

  1. 首先对数据存放目录进行拷贝,这是为了防止在恢复时出错而以防万一。
  2. 使用备份文件恢复数据库。如果你已经制作有一个二进制备份,那么这个步骤涉及到关闭服务端并使用这些备份来替换丢失或损坏的文件。
  3. 重新执行在备份后记录在binary log中的数据修改操作。

 

11.8.1 导入mysqldump输出

可以使用mysqldump工具来导出数据并生成SQL格式的dump文件,之后通过mysql客户端工具来执行此文件进行导入操作。例如,你可以使用如下语句来生成一份world数据库下Country表的dump文件:

 

shell> mysqldump world Country > dump.sql

 

之后的数据库导入,则使用mysql:

 

shell> mysql world < dump.sql

 

当mysqldump的输出文件中没有指定数据库时,你在使用mysql客户端工具命令时就有必要指定到某个需要操作的库。如果在mysqldump执行中带有 –database 或 –all-databases项时,所生成的dump文件则已包含有相应的 USE db_name语句。

 

mysqldump在使用时不仅仅可用于恢复表和数据库,同时和mysql一起使用也可起到类似”拷贝”的用途。mysql可以直接从管道支进行读取,因此两个命令可以组合成一条命令来将库中的表拷贝到另一个库中。例如,将world数据库中Country表拷贝到test库中,使用以下命令:

shell> mysqldump world Country | mysql test

管道技术也可以将数据库或表通过网络拷贝到另一个远端库中。如,远端主机为other.host.com:

shell> mysqldump world Country | mysql -h other.host.com world

如果dump文件中包含有很长的INSERT语句,那么这可能会超出默认到通信缓冲(communication buffer: 1M)。你可以对mysqldump和mysql通过使用 --max-allowed-packet项来增大缓冲大小。这个命令项可以设置一个单位为byte的值或者值后加上K,M或G来指明大小。例如,--max-allowed-packet=32M定义了大小为32MB。服务端也需要在运行时使用 --max-allowed-packet值来增大其自身的通信缓冲,使其足够大。

当你使用mysqldump时使用了 --tab项,那么它会生成一个以tab制表符分隔的数据文件。对于这种dump文件,重新导入时则需要注意使用相应的方法。假设你的dump文件对应导出表world.City使用/tmp目录在作为输出目录:

shell> mysqldump --tab=/tmp world City

输出将分两部分,一部分为一个包含有City表CREATE TABLE语句的City.sql文件,另一部分为一个City.txt文件包含了相应的表数据。当使用这些文件进行表导入时,首先切换当前路径到导出的目录,然后使用mysql来处理.sql文件,之后只用mysqlimport来导入.txt中的表数据。

shell> cd /tmp
shell> mysql world < City.sql
shell> mysqlimport world City.txt

如果你在使用 --tab项时还结合其它格式控制项如 --fields-terminated-by--fields-enclosed-by一起使用,那么在使用mysqlimport进行导入时,就需要使用对应相同的格式控制项来使得命令程序知道如何翻译数据文件以进行导入。

[Read more…]

【MySQL学生手册】更多备份相关

本文地址:https://www.askmaclean.com/archives/mysql-more-bk-related.html

 

 

11.5 备份日志和状态文件

 

除了备份数据库之外,你还应该备份以下文件:

 

  • 你的二进制日志文件。如果你不得不进行数据库恢复的话,binary log备份存储了你在备份之后的更新。
  • 由服务端所使用的配置项文件(cnf或my.ini文件),这些文件包含了在数据库发生奔溃后恢复所需的的配置信息。
  • Replication从库端(Slave)所建立的一个info文件,这个文件包含有所需连接的主库信息。以及relay-log.info文件,这个文件记录了当前处理relay日志的进度情况。
  • Replication从库在处理LOAD DATA INFILE语句时会建立数据临时文件。这些文件被放置在由slave_load_tmpdir系统变量所设置的目录位置下,此变量可以在服务端启动时通过 –slave-load-tmpdir项进行设置。当slave_load_tmpdir未被设置,那么文件就会被存放在操作系统变量tmpdir都指定的位置。其处理的文件会以SQL_LOAD- 打头。

 

为了备份以上这些文件,你可以使用一般的文件系统操作。静态文件如配置文件(option file)不需要特别的注意即可进行备份。动态文件如服务端正在运行且改变的日志文件,则需要停止服务端,然后进行备份。

[Read more…]

【MySQL学生手册】建立文本备份

本文地址:https://www.askmaclean.com/archives/mysql-generate-text-backup.html

 

11.4 建立文本备份

 

11.4.1 通过SQL建立文件备份

 

SELECT命令可以和INTO OUTFILE语法一起使用来将返回结果直接写入文件中。在使用中,需要将INTO OUTFILE语法放在FROM语法之前。例如,将Country表中数据写入Country.txt文件中,执行以下语句:

 

mysql> select * from into outfile 'Country.txt' from Country;

 

 

其中文件名指定了你希望写入的位置,这里也可以写路径,如果没有写明路径,则是指当前会话登陆时所在位置下。

 

SELECT … INTO OUTFILE 使用时有以下特点:

 

  • 此语句可被用于本地或远程服务端。由于是服务端本身来写文件,因此生成的结果文件总是被建立在服务端。
  • 需要输出文件不能已经存在。
  • 语句可用于任何存储引擎。
  • 语句要求有FILE权限。
  • 输出格式可以通过使用语句项,定义其指定列和行分隔符,引用符和逃逸符来进行控制。

 

使用INTO OUTFILE可以在以下几个方面改变SELECT语句的操作:

 

  • 文件被写于服务端,而非通过网路将文件发送至客户端(文件名不能已经存在)。
  • 服务端会在其主机上写一个新文件(执行语句需要登录服务端并使用具有FILE权限的账号)
  • 被建立的文件具有文件系统访问权限,并为MySQL服务端所有,不过对所有用户开放可读。
  • 文件的包含的数据按查询语句返回结果中每条记录一行(默认,列值之间以tab制表符进行分隔,每行则在出现新记录时终止)

 

 

你可以像CSV格式一样建立以逗号分隔值,使用双引号括起值,并对行以回车换行符(Carriage Return:CR)结尾的格式文件。以这种格式来输出结果信息的话,可以使用以下SELECT … INTO OUTFILE语句:

 

 

SELECT * INTO OUTFILE '/tmp/data-out.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r'
FROM t;

[Read more…]

【MySQL学生手册】建立binary备份

本文地址:https://www.askmaclean.com/archives/mysql-generate-binary-bk.html

 

11.3.1 建立MyISAMBinary备份

为了对MyISAM表建立一份binary备份,可以拷贝对应表的.frm,.MYD和.MYI文件。当你这么做时,必须确保这些表在被拷贝时没有在被其它程序(包括服务端)所使用。如果你在拷贝表前就关停了服务端,那就没什么问题了。如果拷贝的时候服务端还在运行,那就需要使用适当的锁来避免服务端对这些表的访问。例如,拷贝world数据库中的Country表前,进行锁表并将有待永久保存的变更刷出内存操作:

mysql> use world;
mysql> lock tables Country read;
mysql> flush tables Country;

然后(在表被锁住的情况下)使用操作系统文件拷贝命令将表文件拷贝出来。

 

以MyISAM数据库表world.Country拷贝举例(这里假设Country表为MyISAM引擎表):

# cp /usr/local/mysql/data/world/Country.frm /var/backup/Countryfrm
# cp /usr/local/mysql/data/world/Country.MYI /var/backup/CountryMYI
# cp /usr/local/mysql/data/world/Country.MYD /var/backup/CountryMYD
# cp /usr/local/mysql/data/world/Country.TRN /var/backup/CountryTRN
# cp /usr/local/mysql/data/world/Country.TRG /var/backup/CountryTRG
# cp /usr/local/mysql/data/mysql/proc /var/backup/CountryROUTINES

在拷贝操作完成后,释放表锁:

mysql> unlock tables;

 

之前的方法可以用于Unix/Linux系统上的数据库文件备份。在Windows上,被服务端锁住的表文件则由于文件锁原因而不能被拷贝备份。在这样情况下,你就必须在拷贝表文件前关闭服务端。

 

另一种MyISAM二进制备份方式是使用mysqlhotcopy脚本工具,它可以帮你锁表并拷贝文件。

[Read more…]

【MySQL学生手册】binary备份 vs 文本备份

本文地址:https://www.askmaclean.com/archives/mysql-binary-vs-text-backup.html

 

 

11.2 binary备份 vs 文本备份

 

当备份数据库时,你有两种备份格式可选:

  • 二进制(binary)备份是一种对数据库中存储的内容文件的拷贝。这种拷贝实际上使得备份文件格式和MySQL在磁盘上存储的数据库文件格式保持了完全一致。因此此类数据库恢复则涉及将这些文件拷贝回它原有的位置。建立binary备份的技术包括使用文件拷贝命令(如cp或tar),mysqlhotcopy以及InnoDB Hot Backup**。
    ** 需要注意的是mysqlhotcopy从MySQL 5.7及其之后就被去除了,相关功能被融合到了其企业版MySQL Enterprise Backup工具mysqlbackup中。而InnoDB Hot Backup原先是商用软件的一部分,在MySQL Enterprise Backup 3.9之后其相应工具也被融合入mysqlbackup中。
  • 文本备份则是将数据库内容导出(dump)至文件文件中。恢复则涉及到通过处理这些文件的内容将数据返回到数据库中。生成文本备份的技术包括了使用SELECT … INTO OUTFILE 的SQL语句,mysqldump工具等。

 

这两种备份格式有其不同的优缺点。通常选择使用何种备份的考虑因素是对速度和便携性之间的权衡。

 

由于二进制备份仅是对文件进行拷贝操作,它不需要了解文件中的内部结构,因此在速度上这种备份速度会更快。然而,如果需要将这种备份传输到另一个使用不同架构的机器上,那么文件就需要更多考虑二进制的便携性。意思就是这些文件需要平台无关化才行,这样你才能直接拷贝它们,从一个MySQL服务端传输到另一个处于不同服务器上的数据库中,而且这第二个服务端需要能够没有任何问题地访问这些文件的内容。使用二进制备份方法,你还需要确保在进行备份的时候,服务端不会对在被拷贝的文件进行修改。

[Read more…]

【MySQL学生手册】备份和恢复

本文地址:https://www.askmaclean.com/archives/mysql-backup_recovery_knowledge.html

 

 

第11章 备份和恢复

 

章节概述

本章介绍了MySQL数据备份和恢复。你将学习并了解:

  • 备份的类型
  • 进行二进制备份和文本备份
  • 备份中日志和状态文件的角色作用
  • 使用一个复制从库(replication slave)来进行备份
  • 进行数据恢复
  • 倒入数据文件

 

11.1 备份概述

MySQL数据库备份一般用于应对可能的系统崩溃或硬件故障而导致的数据损失或讹误问题。备份同样对于用户误操作,如误删数据库或表等恢复有帮助。也有时候备份被用于将数据库拷贝或移动到另一个服务器中,如当你需要进行MySQL安装迁移,或建立一个复制从库时。

 

备份可以是对数据文件的直接拷贝,或是通过设计程序来完成同样的备份目的。这些程序包括mysqldump,mysqlhotcopy和InnoDB Hot Backup。

 

对于数据库运维,备份总是必要的,不过备份仅仅是在数据受损后所需进行数据恢复的组件之一。其它的你还需要二进制日志(binary log)文件,它包含了数据修改的记录。进行数据库恢复时,你使用备份将数据库恢复到备份时的状态,然后重新执行binary log中包含的语句来应用备份后的数据修改。

 

这里列出了在进行备份时需要记住的一些原则:

  • 制作一般备份。
  • 启用binary log,这样你在进行备份后,对数据修改的记录将会被保存在日志文件中。
  • 在备份后,使用flush命令,使得服务端从一个新的binary log文件开始,这个日志文件对应了备份的时间(也就是说,将此备份后的日志看作是一个“检查点”,从这个时间后开始的日志记录是备份之后新的数据修改)。
  • 将数据文件目录和你的备份放置在不同的物理设备中,这样一旦某个设备出现故障,不会造成同时被影响的后果。
  • 将你的备份存在在通常合理的文件系统位置中,这样一旦需要就可以从这些位置上找到备份进行恢复。

[Read more…]

沪ICP备14014813号

沪公网安备 31010802001379号