分类目录归档:MySQL

从Aliyun RDS还原备份数据备忘

从后台下载备份, 可以下载到本地,也可以在远程机器上操作。

安装工具:
https://www.percona.com/doc/percona-xtrabackup/2.2/installation/yum_repo.html

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum list | grep percona
yum install percona-xtrabackup-22

rpm -qa | grep percona
percona-release-0.1-3.noarch
percona-xtrabackup-22-2.2.13-1.el7.x86_64

/usr/bin/innobackupex
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-22-2.2.13
/usr/share/doc/percona-xtrabackup-22-2.2.13/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz

需要使用的是/usr/bin/innobackupex工具。

下载解压工具:http://oss.aliyuncs.com/aliyunecs/rds_backup_extract.sh?spm=5176.7741817.2.6.Aw8bl9&file=rds_backup_extract.sh

chmod +x rds_backup_extract.sh

解压实例备份数据(看数据大小,可能时间较长, 另外确保磁盘空间足够):

rds_backup_extract.sh -f hins3426165_data_xxx.tar.gz -C /root/mysql/data

还原数据:

innobackupex —defaults-file=/root/mysql/data/backup-my.cnf --apply-log /root/mysql/data

启动实例:

mysqld_safe --defaults-file=/root/mysql/data/backup-my.cnf --user=mysql --datadir=/root/mysql/data 

启动实例可能会遇到各种问题,主要是配置文件导致的,注释backup-my.cnf中对应的配置即可:
1 [ERROR] /alidata/server/mysql/bin/mysqld: unknown variable ‘innodb_log_checksum_algorithm=innodb’
把innodb_log_checksum_algorithm=innodb注释掉

2 [ERROR] InnoDB: ./ibdata1 can’t be opened in read-write mode
rm -fr ibdata1 ib*

3 [ERROR] –gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires –log-bin and –log-slave-updates
添加gtid_mode = on,enforce_gtid_consistency = 1,log_slave_updates = 1

4 [ERROR] Fatal error: Can’t open and lock privilege tables: Can’t find file: ‘./mysql/user.frm’
新建用户:
delete from mysql.db where user<>‘root’ and char_length(user)>0;
delete from mysql.tables_priv where user<>‘root’ and char_length(user)>0;
flush privileges;

5 其它问题
一般都是配置相关,对应注释掉相对应的指令即可。

MySQL 事务与锁查看

information_schema.innodb_trx

trx_id					事务ID
trx_state				事务状态
trx_started				事务执行开始时间
trx_requested_lock_id			事务等待锁ID号(等待其它事务释放锁)
trx_wait_started			事务等待锁开始时间
trx_weight						
trx_mysql_thread_id			事务线程ID
trx_query				具体的SQL
trx_operation_state			事务当前操作状态
trx_tables_in_use			事务中有多少个表被使用
trx_tables_locked				
trx_lock_structs					
trx_lock_memory_bytes			事务锁住的内存大小(B)
trx_rows_locked				事务锁住的行数
trx_rows_modified			事务更改的行数
trx_concurrency_tickets			事务并发数
trx_isolation_level			事务隔离级别
trx_unique_checks			是否唯一性检查
trx_foreign_key_checks			是否外键检查
trx_last_foreign_key_error		最后的外键错误
trx_adaptive_hash_latched		
trx_adaptive_hash_timeout		

注:trx_started记录了事务开始的时间,如果过去了很长时间,可能是异常事务。trx_wait_started记录了等待时间,如果等待了很长时间,可能是异常事务。对应等待锁的事务,trx_query记录了具体的SQL语句。trx_mysql_thread_id可以定位到具体的线程(回话ID)

information_schema.innodb_locks

lock_id							锁ID
lock_trx_id						拥有锁的事务ID
lock_mode						锁模式
lock_type						锁类型
lock_table						被锁的表
lock_index						被锁的索引(类型)
lock_space						被锁的表空间号
lock_page						被锁的页号
lock_rec						被锁的记录号
lock_data						被锁的数据(对应索引编号,一般是ID号)

事务可以持有多个锁。锁类型有S和X。根据索引类型不同,lock_rec和lock_data可以定位行号。当开启一个事务,对相关行上锁,这个时候的锁不会出现在innodb_locks表中,只有相关的锁被其它事务等待时,产生了锁等待,才会把锁与等待的锁插入此表(换个说法就是此表是用来存放有依赖关系的锁的)

information_schema.innodb_lock_waits

requesting_trx_id					请求锁的事务ID
requested_lock_id					请求锁的锁ID
blocking_trx_id						当前拥有锁的事务ID
blocking_lock_id					当前拥有锁的锁ID
requesting_thd_id					请求锁的线程ID
blocking_thd_id						当前拥有锁的线程ID

记录依赖关系。请求锁等待持有锁。

1 当要查看有哪些线程时,直接运行show full processlist即可,这个命令动态列出当前的线程状态
2 当要查看有哪些事务时(比如检查有哪些事务长时间未结束),可以直接查看innodb_trx表,这个表中的trx_started记录了开始事务的时间。
3 当要查看是否有锁等待时,可以查看innodb_locks,只要有记录,就说明产生了锁等待,具体是哪个依赖哪个,需要查看innodb_lock_waits的关系。

一般来说,产生了锁等待,如果超时,事务会自动释放,但是如果事务开启了,单长时间没有结束,就应该去innodb_trx查看确认(从线程基本无法查看到已经开启了事务)。

获得导致行锁等待和行锁等待超时的会话:

select l.* from ( select 'Blocker' role, p.id, p.user, left(p.host, locate(':', p.host) - 1) host, tx.trx_id, tx.trx_state, tx.trx_started, timestampdiff(second, tx.trx_started, now()) duration, lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index, tx.trx_query, lw.requesting_thd_id Blockee_id, lw.requesting_trx_id Blockee_trx from information_schema.innodb_trx tx, information_schema.innodb_lock_waits lw, information_schema.innodb_locks lo, information_schema.processlist p where lw.blocking_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.blocking_lock_id union select 'Blockee' role, p.id, p.user, left(p.host, locate(':', p.host) - 1) host, tx.trx_id, tx.trx_state, tx.trx_started, timestampdiff(second, tx.trx_started, now()) duration, lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index, tx.trx_query, null, null from information_schema.innodb_trx tx, information_schema.innodb_lock_waits lw, information_schema.innodb_locks lo, information_schema.processlist p where lw.requesting_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.requested_lock_id) l order by role desc, trx_state desc;

对于复杂的多个会话相互行锁等待情况,建议先终止 Role 为 Blocker 且 trx_state 为 RUNNING 的会话;终止后再次检查,如果仍旧有行锁等待,再终止新结果中的 Role 为 Blocker 且 trx_state 为 RUNNING 的会话。

对于标识为 Blocker 的会话(持有锁阻塞其他会话的 DML 操作,导致行锁等待和行锁等待超时),确认业务可以接受其对应的事务回滚的情况下,可以将其终止。比如,可以通过 Kill 命令来今后会话终止。