Total Pageviews

Tuesday 23 August 2016

Awesome MySQL


A curated list of awesome MySQL software, libraries and resources. Awesome
This list accepts and encourages pull requests. See CONTRIBUTING

CONTENTS

Analysis

Performance, structure & data analysis tools
  • Anemometer - Box SQL slow query monitor.
  • innodb-ruby - A parser for InnoDB file formats, in Ruby.
  • innotop - a ‘top’ clone for MySQL with many features and flexibility.
  • Prometheus/mysqld_exporter - Time series database for real-time monitoring and alerting.
  • pstop - a top-like program for MySQL, collecting, aggregating and displaying information from performance_schema.
  • mysql-statsd - A Python daemon to collect information from MySQL and send it via StatsD to Graphite.
  • MySQLTuner-perl - A script that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability.

Backup

Backup/restore/recovery tools
  • MyDumper - Logical, parallel backup/dumper tool for MySQL
  • MySQLDumper - open-source web based backup tool - useful for shared webhosting
  • Percona Xtrabackup - an open-source hot backup utility for MySQL - based servers that doesn’t lock your database during the backup.

Benchmarking

Tools to stress your servers
  • iibench-mysql - Java based version of the Index Insertion Benchmark for MySQL/Percona/MariaDB.
  • Sysbench - a modular, cross-platform and multi-threaded benchmark tool.

ChatOps

Scripts integrated into chat rooms

Configuration

MySQL sample configuration and advisors

Connectors

MySQL connectors for various programming languages
  • Connector/Python - a standardized database driver for Python platforms and development.
  • go-sql-driver - a lightweight and fast MySQL-Driver for Go’s (golang) database/sql package.
  • libAttachSQL - libAttachSQL is a lightweight, non-blocking C API for MySQL servers.
  • MariaDB Java Client - LGPL-licensed MariaDB Client Library for Java Applications.
  • MySQL-Python - MySQL database connector for Python programming.
  • node-mysql - A pure Nodejs Javascript client implementing the MySQL protocol.
  • PHP mysqlnd - MySQL native driver for MySQL, deprecating older libmysql based driver.

Deployment

MySQL deployment tools
  • MySQL Docker - Official Docker images.
  • MySQL Sandbox - a tool that installs one or more MySQL servers within seconds, easily, securely, and with full control.

Development

Tools to support MySQL-related development
  • Flywaydb - Database migrations; Evolve your database schema easily and reliably across all your instances
  • Liquibase - Source control for your database
  • Propagator - Centralized schema & data deployment on a multi-everything topology
  • Shift - An application that helps you run schema migrations on MySQL databases

GUI

GUI frontends & applications
  • Adminer - Database management in a single PHP file.
  • HeidiSQL - MySQL GUI frontend for Windows.
  • MySQL Workbench - provides DBAs and developers an integrated tools environment for database design & modeling; SQL devleopment; database administration.
  • phpMyAdmin - a free software tool written in PHP, intended to handle the administration of MySQL over the Web.
  • SequelPro - a Mac database management application for working with MySQL databases.
  • mycli - A Terminal Client for MySQL with AutoCompletion and Syntax Highlighting.
  • SQLyog Community edition - SQLyog Community edition. For Windows, works fine under wine in Mac and Linux

HA

High availability solutions
  • Galera Cluster - a true Multimaster Cluster based on synchronous replication.
  • MariaDB Replication Manager - a high availability solution to manage MariaDB 10.x GTID replication.
  • MHA - Master High Availability Manager and tools for MySQL
  • MySQL Fabric - an extensible framework for managing farms of MySQL Servers.
  • Percona Replication Manager - Asynchronous MySQL replication manager agent for Pacemaker. Supports file and GTID based replication, geo-distributed clusters using booth.

Proxy

Proxies to MySQL
  • MaxScale - open-source, database-centric proxy.
  • Mixer - a MySQL proxy powered by Go which aims to supply a simple solution for MySQL sharding.
  • MySQL Proxy - A simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication.
  • ProxySQL - High performance proxy for MySQL.
  • MySQL Router - MySQL Router is lightweight middleware that provides transparent routing between your application and any backend MySQL Servers.

Replication

Replication related software
  • orchestrator - MySQL replication topology management and visualization tool.
  • Tungsten Replicator - A high performance, open source, data replication engine for MySQL.

Schema

Add-on schemas
  • common_schema - DBA’s framework for MySQL, providing a function library, views library and QueryScript interpreter.
  • sys - A collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage.

Server

MySQL server flavors

Sharding

Sharding solutions/frameworks
  • vitess - vitess provides servers and tools which facilitate scaling of MySQL databases for large scale web services.
  • jetpants - An automation suite for managing large range sharding clusters, by Tumblr.

Toolkits

Toolkits, general purpose scripts
  • go-mysql - A pure go library to handle MySQL network protocol and replication.
  • MySQL Utilities - a collection of command-line utilities, written in Python, that are used for maintaining and administering MySQL servers, either individually, or within Replication hierarchies.
  • Percona Toolkit - a collection of advanced command-line tools to perform a variety of MySQL server and system tasks that are too difficult or complex to perform manually.
  • gh-ost - GitHub’s online schema migration for MySQL.
  • openark kit - a set of utilities that solve everyday maintenance tasks, which may be complicated or time consuming to do by hand, written in Python.
  • UnDROP - a tool to recover data from dropped or corrupted InnoDB tables.

Resources

At this stage “resources” will not include websites, blogs, slides, presentation videos, etc. in fear of list size

Conferences

Public, recurring conferences on and around MySQL
  • FOSDEM - a free event for software developers to meet, share ideas and collaborate. Annually, in Brussels. Offers “MySQL & friends” room.
  • MySQL Central - Oracle’s annual MySQL conference, as part of Oracle Open World.
  • Percona Live - MySQL & Openstack focused conference.
  • SCALE - A community organized Linux and Open Source conference held annually in Southern California. The local MySQL community runs a track under the name MySQL Community Day.

e-books

e-books as well as relevant materials on and around MySQL
  • SQL-exercise - contains several SQL exercises, including the schema description figure, SQL code to build schema, questions and solutions in SQL. Based on wikibook SQL Exercises.

Media

Public, ongoing video & audio casts. This excludes conference presentations in fear of list size
  • DBHangOps - a bi-weekly google hangout meeting of various members of the MySQL community that simply want to talk shop about MySQL in their day-to-day .
  • OurSQL Podcast - The MySQL database community podcast.

Newsletters

Newsletters require an email address, by definition. List below are newsletters that require nothing but an email address
from http://shlomi-noach.github.io/awesome-mysql/
----------------------

分析工具

性能，结构和数据分析工具
  • Anemometer - 一个 SQL 慢查询监控器。
  • innodb-ruby - 一个对 InooDB 格式文件的解析器，用于 Ruby 语言。
  • innotop - 一个具备多种特性和可扩展性的 MySQL 版 'top' 工具。
  • pstop - 一个针对 MySQL 的类 top 程序，用于收集，汇总以及展示来自 performance_schema 的信息。
  • mysql-statsd - 一个收集 MySQL 信息的 Python 守护进程，并通过 StatsD 发送到 Graphite。

备份

备份/存储/恢复 工具
  • MyDumper - 逻辑的，并行的 MySQL 备份/转储工具。
  • MySQLDumper - 基于 web 的开源备份工具-对于共享虚拟主机非常有用。
  • mysqldump-secure - 将加密，压缩，日志，黑名单和 Nagios 监控一体化的 mysqldump 安全脚本。
  • Percona Xtrabackup - 针对 MySQL 的一个开源热备份实用程序——在服务器的备份期间不会锁定你的数据库。

性能测试

给你的服务器进行压测的工具
  • iibench-mysql -基于 Java 的 MySQL/Percona/MariaDB 索引进行插入性能测试工具。
  • Sysbench - 一个模块化，跨平台以及多线程的性能测试工具。

聊天应用

集成进聊天室的脚本

配置

MySQL 配置实例及指导

连接器

多种编程语言的 MySQL 连接器
  • Connector/Python - 一个对于 Python 平台和开发的标准化数据库驱动程序。
  • go-sql-driver - 一个 Go 语言的轻量级、极速的 MySQL 驱动程序。
  • libAttachSQL - libAttachSQL 是 MySQL 服务器的一个轻量级，非阻塞的 C 语言 API。
  • MariaDB Java Client - 针对 Java 应用且经过 LGPL 许可的 MariaDB 客户端库。
  • MySQL-Python - 一个 Python 语言的 MySQL 数据库连接器。
  • PHP mysqlnd - 针对 MySQL 的 MySQL 本地驱动，弃用过时的 libmysql 基础驱动。

开发

支持 MySQL 相关开发的工具
  • Flywaydb - 数据库迁移;任意情况下轻松可靠地演变你的数据库版本。
  • Liquibase - 对你的数据库进行源代码控制。
  • Propagator - 集中模式和数据部署在一个多维拓扑上。

GUI

前端和应用的 GUI
  • Adminer - 一个 PHP 编写的数据库管理工具。
  • HeidiSQL - Windows 下的 MySQL 图形化管理工具。
  • MySQL Workbench - 提供给数据库管理员和开发人员进行数据库设计和建模的集成工具环境;SQL 开发;数据库管理。
  • phpMyAdmin - 一个 PHP 写成的开源软件，意图对 web 上的 MySQL 进行管理。
  • SequelPro - 一个 mac 下运行 MySQL 的数据库管理应用程序。
  • mycli - 一个带自动补全和语法高亮的终端版 MySQL 客户端

HA

高可用解决方案
  • Galera Cluster - 一个基于同步复制的多主机集群方案。
  • MHA - 针对 MySQL 的优秀高可用管理器及工具
  • MySQL Fabric - 一个用于管理 MySQL 服务器场（Server Farms）的可扩展框架。
  • Percona Replication Manager - 针对 MySQL 的异步复制管理代理。支持以文件和 GTID 为基础的复制，使用 booth 实现的地理分布式集群。

代理

MySQL 代理
  • MaxScale - 开源，以数据库为中心的代理。
  • Mixer - Go 实现的一个 MySQL 代理，目的为 MySQL 分片提供一个简单的解决方案。
  • MySQL Proxy - 一个处于你的客户端和 MySQL 服务端之间的简单程序，它可以检测、分析或者改变它们的通信。
  • ProxySQL - 高性能的 MySQL 代理。

复制

复制相关的软件

模式

附加模式
  • common_schema - MySQL DBA 的框架， 提供一个具有函数库、视图库和查询脚本的解释器。
  • sys - 一个视图、函数和过程的集合，以帮助 MySQL 管理人员更加深入理解 MySQL 数据库的使用。

服务器

MySQL server flavors

分片

分片解决方案/框架
  • vitess - 对于大规模的 web 服务，vitess 提供服务和工具以便于 MySQL 数据库的缩放。
  • jetpants - 一个自动化套件，用于管理大规模分片集群，由 Tumblr 开发。

工具包

工具包，通用脚本
  • go-mysql - 一个纯 go 的库，用于处理 MySQL 的网络协议和复制。
  • MySQL Utilities - 一个命令行实用程序的集合，Python 语言编写，用于维护和管理单一或多层的 MySQL。
  • Percona Toolkit - 一个先进的命令行工具集，用于执行对于 MySQL 服务器和系统过于困难或复杂的任务。
  • openark kit - 一组实用的工具，解决日常的维护工作，包括一些复杂的或需徒手操作的，用 Python 语言编写。
  • UnDROP - 一个用来恢复删除或损坏的 InnoDB 表中数据的工具。

资源

在这个阶段，“资源”不包括网站，博客，幻灯片，演示视频等。这些资源列表的大小令人恐惧。

会议

围绕 MySQL 进行公开，经常性的大会。
  • FOSDEM - 一个软件开发人员见面、交流思想与协作的活动。每年在 Brussels 举行。提供 “MySQL & friends” 开发工作室。
  • MySQL Central - Oracle 年度 MySQL 大会，是 Oracle Open World 的一部分。
  • Percona Live - MySQL 和 OpenStack 的重要会议。
  • SCALE - 一个每年在南加州举办，由社区组织的 Linux 和 开源软件大会。由当地 MySQL社区组织并以MySQL社区日的名义举办。

电子书

MySQL 电子书以及相关材料。
  • SQL-exercise - 包含几个 SQL 练习，包括模式描述，用 SQL 语句去建立模式，SQL 的问题及解决方案。以 wikibook SQL 练习为基础。

媒体

公开，持续的视频和音频转播。这不包括会议演讲那令人恐惧的资源列表大小。
  • DBHangOps - 两周一次由各种各样的 MySQL 社区人员参加的 google 聚集大会，大会的日常就是谈论一切关于 MySQL 的东西。
  • OurSQL Podcast - MySQL 数据库社区播客。

新闻周刊

__顾名思义，新闻周刊需要一个 email 地址。下面列出周刊只需要一个 email 地址。
  • Weekly MySQL News - 非官方的 MySQL新闻摘要，包含关于MySQL的各类信息。

微信公众号

  • 数据库开发者：专注分享数据库相关内容，包括：各种主流 DB 的最佳实践、数据库基础知识、性能优化、数据安全等。 
  • Linux爱好者：专注分享 Linux/Unix 相关内容，包括：工具资源、使用技巧、课程、书籍等。 
    from https://github.com/jobbole/awesome-mysql-cn
    -------------------
    from http://hao.jobbole.com/?catid=7

    数据存储

    更多 »

    • Titan

      一个分布式图形数据库

    • InfluxDB

      开源分布式数据库

    • CartoDB

      云上的地理数据库

    • MapDB

      一个java嵌入式数据库

    • opentsdb

      一个分布式可扩展的时间序列数据库

    • TaffyDB

      开源JavaScript数据库

    开发框架

    更多 »

    管理工具

    更多 »

    • sequelpro

      一款Mac平台的MySQL管理工具

    • MyCli

      支持自动补全和语法高亮的 MySQL 客户端

    • Hunk

      Hadoop 新型数据分析工具

    • Vitess

      打造高性能MySQL前端

    • phinx

      php数据库迁移

    • pgModeler

      PostgreSQL 数据库专用的建模工具
       

-------------------------------

MySQL 简单操作

一、安装后

安装 mysql 后设置管理员 root 密码

mysqladmin -u root password 123456

修改 root 密码

mysqladmin -uroot -p password 'newpassword'

重设 root 密码

/etc/rc.d/mysqld stop
mysqld_safe --skip-grant-tables &
mysql -uroot mysql

UPDATE user SET password=PASSWORD("newpassword") WHERE User='root';
FLUSH PRIVILEGES;

二、登录数据库

连接本地 mysql

mysql -uroot -p

通过 IP 和端口连接远程mysql服务器。-h: 指定数据库服务器IP，-P: 指定要连接的端口号。

mysql -u root -p 123456 -h ip -P 3306

通过 TCP 连接管理不同端口的多个 MySQL（注意：MySQL4.1 以上版本才有此项功能）

mysql -u root --p 123456 --protocol=tcp --host=localhost --port=3307

通过 socket 套接字管理不同端口的多个 MySQL

mysql -u root -p --socket=/tmp/mysql3307.sock

退出 mysql

mysql> exit;

三、 数据库操作

显示有多少数据库

show databases;

创建与删除molyx数据库

create database molyx;
drop database molyx;

选定molyx数据库

use molyx;

当前选择的数据库

select database();

查看状态

show status;

查看进程

show processlist;

显示当前用户

select user();

显示所有用户

select user,host,password from mysql.user;

显示用户molyxuser权限

show grants for molyxuser@localhost;

查看名为molyx的数据库

mysqlshow -uroot -p molyx

四、 数据表操作

显示molyx库中的数据表

show tables from molyx;

当前数据包含的表信息

show tables;

查看表结构

describe user;

or

desc tablename;

or

show columns from tablename;

建表

create table tablename(<column1><type1>,<column2><type2>,<column3><type3>);

example:

create table MyClass(
id int(4) NOT NULL primary key auto_increment,
name char(20) NOT NULL,
sex int(4) NOT NULL default '0',
degree double(16,2)) ENGINE = MYISAM;

查询表中数据

查询所有行

select * from tablename;

查询并排序

select * from tablename order by id desc;

查询前几行数据

select * from tablename order by id limit 0,2;

指定条件查询

select * from tablename where name = 'type';

查询唯一不重复记录

select distinct name from tablename;

插入数据

insert into tablename (<column1>,<column2>,<column3>) values (type1,type2,type3);

or

insert into tablename values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);

删除表中数据

delete from tablename where id=N

修改表中数据

update tablename set column='value' where id=1;

or

update tablename set column=replace(column,'oldvalue','newvalue');

example:

update article set content=concat('你好',content);
update user set pass='123456' where name='name';

更改用户名

update set user='newname' where user='oldname';

更改表名

rename table tablenameold to tablenamenew;

在表中增加字段

alter table tablename add column type default '0';

example:

alter table user add column date char(8) after pass;

在user表中修改字段名及字段数据类型

alter table tablename change column <column1> <type1> char(10);

example:

alter table user change column pass password char(10);

删除表中字段

alter table tablename grop column <column1>;

example:

alter table user drop column date;

删除用户molyxuser

use mysql;
DELETE FROM user WHERE User='molyxuser';

or

drop user 'molyxuser'@'%';

删除表

drop table tablename;

导出一个表

mysqldump -u user -p password tablename > filename

五、权限操作

先创建个molyx库，再创建个可以操作molyx库的用户molyxuser，密码为123456

create database molyx;
grant select,insert,update,delete,create,alter,create temporary tables,create view,show view,drop on molyx.* to 'molyxuser'@'localhost' identified by '123456';

or

grant all on molyx.* to 'molyxuser’@localhost identified by '123456';

收回molyxuser针对molyx库的所有权限

revoke all on molyx.* from 'molyxuser'@localhost identified by '123456';

创建一个针对molyx库具有所有权限，可从任意IP登录的帐号molyxuser

grant all on molyx.* to 'molyxuser'@'%' identified by '123456';

六、备份与还原

备份molyx到文件molyx.sql

mysqldump -uusername -ppassword molyx > /your/path/molyx.sql

备份全部数据

mysqldump -uroot -ppassword --all-databases > /your/path/backup.sql

备份molyx并压缩

mysqldump -uusername -ppassword molyx | gzip > /your/path/molyx.sql.gz

将molyx.sql导入到数据库

mysql -uusername -ppassword molyx < /your/path/molyx.sql

将压缩文件molyx.sql.gz中数据恢复到molyx库

gzip < molyx.sql.gz | mysql -uusername -ppassword molyx

将文本数据导入数据库:

use test;
load data local infile 'filename' into table tablename;

检查所有的.myi文件

myisamchk /your/path/mysql/*.MYI

修复所有的.myi

myisamchk -r /your/path/mysql/*.MYI

七、其他

#mysql 显示和使用的mysql数据库。前面已经简单的提过用法；比如登录等
#mysqladmin 用来创建和维护mysql数据库的命令
#isamchk 是用来修复、检查和优化.ism后缀的数据库文件
#mysqldump 是用于备份数据库
#myisamchk 用来修复.myi后缀的数据库文件
1. 字段类型
  • INT[(M)] 型： 正常大小整数类型
  • DOUBLE[(M,D)] [ZEROFILL] 型： 正常大小(双精密)浮点数字类型
  • DATE 日期类型：支持的范围是1000-01-01到9999-12-31。MySQL以YYYY-MM-DD格式来显示DATE值，但是允许你使用字符串或数字把值赋给DATE列
  • CHAR(M) 型：定长字符串类型，当存储时，总是是用空格填满右边到指定的长度
  • BLOB TEXT类型，最大长度为65535(2^16-1)个字符。
  • VARCHAR型：变长字符串类型

安装升级

emerge --config =dev-db/mysql-5.1.53
mysql_upgrade tool
2. mysqldump 命令的使用

备份和导出数据库

mysqldump -h database_ip -u Username -p --opt databasename > backup-file.sql

只导出数据库表结构

mysqldump -h database_ip -d -u Username -p databasename >database_structure.sql

只导出数据库中的某个表

mysqldump --opt --add-drop-table -u Username -p databasename tablename > dump.sql

如果不想手工输入密码 请使用–password 参数

mysqldump -h database_ip -u Username --password=123456 --opt databasename > backup-file.sql
mysqldump -h database_ip -d -u Username --password=123456 databasename >database_structure.sql
3. mysql 命令使用

将查询结果保存到文件

select title from book into outfile '/tmp/outfile.txt';

查找表中多余的重复记录，重复记录是根据某个字段（peopleId）来判断

select * from people where peopleId in (select peopleId from people group by
peopleId having count(peopleId) > 1);

查询表中不重复记录(排除重复记录)

select * from phome_ecms_wma where title in (select distinct title from phome_ecms_wma);

删除表中重复记录,重复记录是根据某个字段（title）来判断

select *,count(distinct title) INTO OUTFILE '/tmp/table.bak' from phome_ecms_wma group by title;
delete from phome_ecms_wma;
LOAD DATA INFILE '/tmp/table.bak' REPLACE INTO TABLE phome_ecms_wma character set utf8;

查询数据库当前编码

show variables like 'character_set%';

修改表字段类型

alter table table_name change last_action last_action datetime NOT NULL default '0000-00-00 00:00:00';

给表添加一个新字段

ALTER TABLE host ADD ks_mac VARCHAR(100);

从表中删除一个字段

ALTER TABLE table_name DROP field_name;

重命名表

alter table t1 rename t2;

给字段加索引

alter table tablename add index 索引名 (字段名1[，字段名2 …]);
alter table tablename add index emp_name (name);

加主关键字的索引

alter table tablename add primary key(id);

加唯一限制条件的索引

alter table tablename add unique emp_name2(cardnumber);

删除某个索引

alter table tablename drop index emp_name;

远程访问mysql 设置

GRANT ALL PRIVILEGES ON database_test.* to root@192.168.1.9 IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

往表中加入记录

insert into mytable values ('test','m');

用文本方式将数据装入数据库表中（例如 d:\mysql.txt）

load data local infile 'd:/mysql.txt' into table mytable;

导入.sql文件命令（例如 d:\mysql.sql）

use database;
source d:/mysql.sql;

若要给此用户赋予他在相应对象上的权限的管理能力，可在grant后面添加with grant option选项。而对于用插入User表添加的用户，Password字段应用password函数进行更新加密，以防不轨之人窃看密码。对于那些已经不用的用户应给予清除，权限过界的用户应及时回收权限，回收权限可以通过更新User表相应字段，也可以使用revoke操作。以下是常用权限的解释：

全局管理权限

  • FILE：在MySQL服务器上读写文件。
  • PROCESS：显示或杀死属于其它用户的服务线程。
  • RELOAD：重载访问控制表，刷新日志等。
  • SHUTDOWN：关闭MySQL服务。

数据库/数据表/数据列权限

  • ALTER：修改已存在的数据表（例如增加/删除列）和索引。
  • CREATE：建立新的数据库或数据表。
  • DELETE：删除表的记录。
  • DROP：删除数据表或数据库。
  • INDEX：建立或删除索引。
  • INSERT：增加表的记录。
  • SELECT：显示/搜索表的记录。
  • UPDATE：修改表中已存在的记录。

特别的权限

  • ALL：允许做任何事（和root一样）。
  • USAGE：只允许登录–其它什么也不允许做.

 

 

Posted by at
Labels: