Mysql | 十字螺丝钉的DBA日记

2017年3月29日十字螺丝钉工具插件 2 comments

SQLAdvisor：MySQL SQL优化工具

SQLAdvisor是由美团点评公司技术工程部DBA团队（北京）开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析，结合分析SQL中的where条件、聚合条件、多表Join关系给出索引优化建议。目前SQLAdvisor在美团点评内部广泛应用，公司内部对SQLAdvisor的开发全面转到github上，开源和内部使用保持一致。 SQLAdvisor项目地址 https:

2017年3月15日十字螺丝钉 MySQL, troubleshooting Leave a comment

xtrabackup恢复数据后，MySQL无法启动问题处理

应审计需要，需将MySQL数据恢复到14天前。公司MySQL采用xtrabackup方式备份。使用xtrabackup恢复MySQL后，无法启动MySQL，报错如下

Starting MySQL....The server quit without updating PID file[失败]a/mysql/data/SL010A-IVAMD1.pid).

1	Starting MySQL....The server quit without updating PID file[失败]a/mysql/data/SL010A-IVAMD1.pid).

查看MySQL错误日志

2017-03-15 09:08:01 23485 [Note] InnoDB: Memory barrier is not used
2017-03-15 09:08:01 23485 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-03-15 09:08:01 23485 [Note] InnoDB: Using Linux native AIO
2017-03-15 09:08:01 23485 [Note] InnoDB: Using CPU crc32 instructions
2017-03-15 09:08:01 23485 [Note] InnoDB: Initializing buffer pool, size = 6.0G
2017-03-15 09:08:01 23485 [Note] InnoDB: Completed initialization of buffer pool
2017-03-15 09:08:01 23485 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode
2017-03-15 09:08:01 23485 [ERROR] InnoDB: The system tablespace must be writable!
2017-03-15 09:08:01 23485 [ERROR] Plugin 'InnoDB' init function returned error.
2017-03-15 09:08:01 23485 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
^G/data/mysql/bin/mysqld: File '/mysql/log/slow.log' not found (Errcode: 2 - No such file or directory)
2017-03-15 09:08:01 23485 [ERROR] Could not open /mysql/log/slow.log for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown t
he MySQL server and restart it.
2017-03-15 09:08:01 23485 [ERROR] Unknown/unsupported storage engine: InnoDB
2017-03-15 09:08:01 23485 [ERROR] Aborting

2017-03-15 09:08:01 23485 [Note] InnoDB: Memory barrier is not used

2017-03-15 09:08:01 23485 [Note] InnoDB: Compressed tables use zlib 1.2.3

2017-03-15 09:08:01 23485 [Note] InnoDB: Using Linux native AIO

2017-03-15 09:08:01 23485 [Note] InnoDB: Using CPU crc32 instructions

2017-03-15 09:08:01 23485 [Note] InnoDB: Initializing buffer pool, size = 6.0G

2017-03-15 09:08:01 23485 [Note] InnoDB: Completed initialization of buffer pool

2017-03-15 09:08:01 23485 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode

2017-03-15 09:08:01 23485 [ERROR] InnoDB: The system tablespace must be writable!

2017-03-15 09:08:01 23485 [ERROR] Plugin 'InnoDB' init function returned error.

2017-03-15 09:08:01 23485 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

^G/data/mysql/bin/mysqld: File '/mysql/log/slow.log' not found (Errcode: 2 - No such file or directory)

2017-03-15 09:08:01 23485 [ERROR] Could not open /mysql/log/slow.log for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown t

he MySQL server and restart it.

2017-03-15 09:08:01 23485 [ERROR] Unknown/unsupported storage engine: InnoDB

2017-03-15 09:08:01 23485 [ERROR] Aborting

解决办法：由于执行xtrabackup使用root用户，恢复

2017年2月13日十字螺丝钉备份还原 Leave a comment

MySQL将A库的表及数据导入到B库

在工作中，经常有开发提这样的需求：将A库中的表和数据导入到B库中。这里个记录，方便以后查找。 1、创建新数据库及用户

CREATE DATABASE db_B ;

GRANT ALL PRIVILEGES ON db_B.* TO user_B@'10.%.%.%' identified by "xxxx";

flush privileges;

CREATE DATABASE db_B ;

GRANT ALL PRIVILEGES ON db_B.* TO user_B@'10.%.%.%' identified by "xxxx";

flush privileges;

2、测试用户登录

mysql -uuser_B -p'xxx' -h 10.0.97.121

1	mysql -uuser_B -p'xxx' -h 10.0.97.121

3、导出源库数据

mysqldump -uuser_A -p -S /export1/data/mysql/mysql.sock -B db_A --single-transaction --no-create-db>/tmp/db_A.dmp

1	mysqldump -uuser_A -p -S /export1/data/mysql/mysql.sock -B db_A --single-transaction --no-create-db>/tmp/db_A.dmp

参数说明： –single-tran

2017年2月7日十字螺丝钉 MySQL Leave a comment

MySQL RANGE分区表

一、开启分区表功能 MySQL想要使用分区表，必须安装partition插件。查看是否安装了partition插件。

mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
...........
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
...........
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+----------------------------+----------+--------------------+---------+---------+
42 rows in set (0.00 sec)


SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';
	
+--------------------+---------+----------+
| Name               | Version | Status   |
+--------------------+---------+----------+
| binlog             | 1.0     | ACTIVE   |
| MRG_MYISAM         | 1.0     | ACTIVE   |
| MEMORY             | 1.0     | ACTIVE   |
| CSV                | 1.0     | ACTIVE   |
| MyISAM             | 1.0     | ACTIVE   |
| PERFORMANCE_SCHEMA | 0.1     | ACTIVE   |
| InnoDB             | 5.6     | ACTIVE   |
| BLACKHOLE          | 1.0     | ACTIVE   |
| FEDERATED          | 1.0     | DISABLED |
| ARCHIVE            | 3.0     | ACTIVE   |
| partition          | 1.0     | ACTIVE   |
+--------------------+---------+----------+
11 rows in set (0.00 sec)

mysql> show plugins;

+----------------------------+----------+--------------------+---------+---------+

+----------------------------+----------+--------------------+---------+---------+

...........

...........

+----------------------------+----------+--------------------+---------+---------+

42 rows in set (0.00 sec)

SELECT

PLUGIN_NAME as Name,

PLUGIN_VERSION as Version,

PLUGIN_STATUS as Status

FROM INFORMATION_SCHEMA.PLUGINS

WHERE PLUGIN_TYPE='STORAGE ENGINE';

+--------------------+---------+----------+

| Name | Version | Status |

+--------------------+---------+----------+

| binlog | 1.0 | ACTIVE |

| MRG_MYISAM | 1.0 | ACTIVE |

| MEMORY | 1.0 | ACTIVE |

| CSV | 1.0 | ACTIVE |

| MyISAM | 1.0 | ACTIVE |

| PERFORMANCE_SCHEMA | 0.1 | ACTIVE |

| InnoDB | 5.6 | ACTIVE |

| BLACKHOLE | 1.0 | ACTIVE |

| FEDERATED | 1.0 | DISABLED |

| ARCHIVE | 3.0 | ACTIVE |

| partition | 1.0 | ACTIVE |

+--------------------+---------+----------+

11 rows in set (0.00 sec)

MySQL 5.6二进制版本默认提供分区功能；如果是编译安装，那么编译时请加上 -DWITH_PARTITION_STORAGE_ENGINE 选项。如果不想使用分区，启动MySQL服务请加上–ski

2017年1月23日十字螺丝钉备份还原 Leave a comment

Percona XtraBackup 安装及使用说明

一、安装 Xtrabackup 下载地址 https://www.percona.com/software/mysql-database/percona-xtrabackup 建议使用二进制包，简单方便解压安装

# tar -xvf percona-xtrabackup-2.2.9-5067-Linux-x86_64.tar.gz

1	# tar -xvf percona-xtrabackup-2.2.9-5067-Linux-x86_64.tar.gz

添加到/etc/profile

export PATH=/root/percona-xtrabackup-2.2.9-Linux-x86_64/bin

1	export PATH=/root/percona-xtrabackup-2.2.9-Linux-x86_64/bin

二、创建备份专用账号

2017年1月21日十字螺丝钉备份还原 Leave a comment

一	二	三	四	五	六	日
« 9月
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

十字螺丝钉的DBA日记

分类：MySQL

SQLAdvisor：MySQL SQL优化工具

xtrabackup恢复数据后，MySQL无法启动问题处理

MySQL将A库的表及数据导入到B库

MySQL RANGE分区表

Percona XtraBackup 安装及使用说明

Percona XtraBackup 的工作原理

删除binlog日志造成MySQL无法启动

MySQL慢查询日志 – Slow Query Log