-
编辑 删除
mysqldump: Got error: 1356: View 'goolen.v_dbstatus' references invalid
一网友使用mysqldump导出数据的时候，由于试图引用的表被drop，导出过程出现问题，模拟如下：[root@goolen ~]# mysqldump -uroot -pxxx goolen > goolen.sql mysqldump: Got error: 1356: View 'goolen.v_dbstatus' references invalid table(s) or column(s) or functi...
-
编辑 删除
ERROR 1827 (HY000): The password hash doesn't have the expected format
在执行grant操作的时候，报错ERROR 1827mysql> grant all privileges on goolen.* to root@'%' identified by '123456';ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.mysql> select version();+---...
-
编辑 删除
表上无索引导致MySQL备库复制延迟
早上发现一套内网mysql库主备复制延迟很大mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Mas...
-
编辑 删除
测试percona-data-recovery-tool是否能恢复被truncate的数据
版本：mysql> select version();+-------------------------------------------+| version() |+-------------------------------------------+| 5.6.22-enterprise-commercial-advanced-log |+---...
-
编辑 删除
[ERROR] InnoDB: Failed to find tablespace for table XXX in the cache
在使用innodb_force_recovery参数强制恢复mysql库以后，错误日志里出现大量错误信息：2015-01-01 17:58:28 26714 [ERROR] InnoDB: Failed to find tablespace for table '"live_koo"."live_consumer_koo"' in the cache. Attempting to load the tablespace with space id 6.2015-01-01 17:58:28 26714 [ERROR] InnoDB: F...
-
编辑 删除
mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1'
上一篇中，因为使用参数强制启动了mysql服务，所以需要使用mysqldump对数据进行备份但是备份过程中报错如下：[root@test11g ~]# mysqldump -uroot -proot -h 192.168.100.141 --all-database > all.sqlmysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': You have an error in your SQL syntax; check ...
-
编辑 删除
xtrabackup结合percona recover工具恢复被drop的表
mysql版本mysql> select version();+-------------------------------------------+| version() |+-------------------------------------------+| 5.6.22-enterprise-commercial-advanced-log |+...
-
编辑 删除
使用xtrabackup恢复被单表数据
mysql版本mysql> select version();+-------------------------------------------+| version() |+-------------------------------------------+| 5.6.22-enterprise-commercial-advanced-log |+...
-
编辑 删除
使用percona recover工具恢复被delete的数据-共享innodb表空间
mysql版本mysql> select version();+-------------------------------------------+| version() |+-------------------------------------------+| 5.6.22-enterprise-commercial-advanced-log |+...
-
编辑 删除
使用percona recover工具恢复被delete的数据-独享innodb表空间
MySQL 5.6版本，独享innodb表空间模式测试表：mysql> select count(*) from live_users;+----------+| count(*) |+----------+| 34258 |+----------+1 row in set (0.02 sec)mysql> delete from live_users;Query OK, 34258 rows affected (0.25 sec)mysql> commit;Query OK, 0 rows affected (...
-
编辑 删除
Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
今早启动一台mysql测试库的时候，启动失败：[root@test11g scripts]# /etc/init.d/mysqld restartShutting down MySQL.. [ OK ]Starting MySQL..The server quit wi...
-
编辑 删除
Fatal error: The master and slave have equal MySQL server UUIDs
装了一套mysql5.6测试用，配置主从的时候，为了方便，直接拷贝的主库的mysql目录到备库的查看slave状态的时候发现报错如下：Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work查看文档发现mysq...
-
编辑 删除
[Warning] TIMESTAMP with implicit DEFAULT value is deprecated
MySQL 5.6启动的时候提示警告信息如下：2014-12-20 08:02:25 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).警告信息说timestamp的默认值设置被废弃了，需要配置explicit_defaults_for_ti...
-
编辑 删除
MySQL分区处理null值的方式
range分区表：mysql> create table goolen( -> id int , -> name varchar(35) -> ) engine=innodb -> partition by range(id) ( -> partition p0 values less than (10), -> partition p1 values less than (20), -> ...
-
编辑 删除
Hang Waiting for table metadata lock
刚刚开发来找我，说他用客户端工具对表添加字段的时，点击保存就一直卡着等到服务器上，show processlist 看了一下，很多类似的session| 1041480 | root | 192.168.100.220:56387 | helpservicesystem | Query | 54 | Waiting for table metadata lock &n...
-
编辑 删除
innodb_log_file_size参数配置导致xtrabackup备份失败
用xtrabackup做mysql库的增量备份时报错如下：xtrabackup: innodb_log_file_size = 5242880InnoDB: Error: log file ./ib_logfile0 is of different size 0 524288000 bytesInnoDB: than specified in the .cnf file 0 5242880 bytes!innobackupex: Error: The xtrabackup child process has died at /usr/local...
-
编辑 删除
大批量删除没有任何索引的大表数据导致备库复制延迟
昨天和今天都碰到了同样的问题，内网mysql备库上SQL线程一直停留在某个position上很长一段时间：mysql> show slave status\G。。。Exec_Master_Log_Pos的值一直不变，Seconds_Behind_Master的值在不停的网上升mysql> show slave status\G;*************************** 1. row *************************** &...
-
编辑 删除
xtrabackup-安装
安装 http://www.percona.com/downloads/XtraBackup/XtraBackup-2.1.6/binary/Linux/x86_64/[root@goolen ~]# ls percona-xtrabackup-2.1.6-702-Linux-x86_64.tar.gz percona-xtrabackup-2.1.6-702-Linux-x86_64.tar.gz[root@goolen ~]# tar zxfv percona-xtrabackup-2.1.6-702-Linux-x86_64.tar.gz ...
-
编辑 删除
percona-toolkit系列-pt-find
pt-find - Find MySQL tables and execute actions, like GNU find.用法：pt-find [OPTION...] [DATABASE...]例子：找出创建于一天之前，并且是myisam存储引擎的表[root@goolen ~]# pt-find --ctime +1 --engine MyISAM -uroot -proot`mysql`.`db``mysql`.`event``mysql`.`func``mysql`.`help_category``mysql...
-
编辑 删除
percona-toolkit系列-pt-archiver
pt-archiver工具其实就是用来清理，归档数据用的下面列举了三种常用的功能1,只清理数据mysql> use sbDatabase changedmysql> select * from sb2;+----+------+| id | name |+----+------+| 1 | a || 2 | b || 3 | c || 4 | d |...
