分类目录归档:数据库技术

MySQL\Oracle\Informix\MS SQL\BDB\Sqlite\NoSQL etc

ubuntu修改mysql 5.7数据存储目录datadir

环境:Ubuntu 16.04、mysql5.7

在16.04版本的MySQL数据库,默认是5.7版本的;
想要修改MySQL数据库存储的目录,需要了解mysql配置文件,以及apparmor的配置文件。
这里提一下apparnor 是控制访问权限的,而mysql依赖它,所以不单单是改完mysql配置文件的内容,同样的需要修改apparmor的相应的配置文件。
1.创建MySQL另外存储的目录
mkdir /database/mysql
chmod 700 /database/mysql
chowd mysql:mysq /database/mysql
2.将以前的数据库复制到新的存储目录 (这样避免了再次初始化,并且数据还在)
cp -av /var/lib/mysql/* /database/mysql
3.删除日志 (不删除会报错)
rm -rf /database/mysql/ib_logfile0
rm -rf /database/mysql/ib_logfile1

继续阅读

pdo_informix

1. Installing Informix Client SDK for Linux x86_64

1.1 Download Informix Client SDK 3.70 for Linux x86_64 from IBM website, https://www-01.ibm.com/marketing/iwm/tnd/search.jsp?rs=ifxdl

1.2 Extract the file, `cd /opt/informix; tar -xvf clientsdk.3.70.FC8DE.LINUX.tar`

1.3 Start installation, `./installclientsdk`, install all

2. Installing PDO Informix

2.1 Download PDO Informix 1.3.1, `wget https://pecl.php.net/get/PDO_INFORMIX-1.3.1.tgz`

2.2 Extract the file, `tar -xvf PDO_INFORMIX-1.3.1.tgz`

2.3 `cd PDO_INFORMIX-1.3.1` and compiling

2.3.1 `phpize`

2.3.2 `./configure –with-pdo-informix=/opt/informix`, if getting error `configure: error: Cannot find php_pdo_driver.h`, do `ln -s /usr/include/php5 /usr/include/php` and try again.

2.3.3 `make`

2.3.4 `make install`

3. Include pdo_informix.so in php.ini

Other reference: http://stackoverflow.com/questions/19909075/php-and-informix-on-debian-how-to-install-configure-the-pdo

Sample Code:

<?php

$db = new PDO("informix:host=hostname_or_ipaddr; service=port;database=dbname; server=instancename; protocol=onsoctcp;EnableScrollableCursors=1;", "username", "password");

print "Connection Established!\n\n";

$stmt = $db->query("select * from tablename");
$res = $stmt->fetch( PDO::FETCH_BOTH );
$rows = $res[0];
echo "Table contents: $rows.\n";

?>

MYSQL提权总结

最近在测试一个项目,遇到了MYSQL数据库,想尽办法提权,最终都没有成功,很是郁闷,可能是自己很久没有研究过提权导致的吧,总结一下MYSQL提权的各种姿势吧,权当复习了。关于mysql提权的方法也就那么几种,希望也能帮到各位小伙伴们。

一、利用mof提权

前段时间国外Kingcope大牛发布了mysql远程提权0day(MySQL Windows Remote System Level Exploit (Stuxnet technique) 0day),剑心牛对MOF利用进行了分析,如下:
Windows 管理规范 (WMI) 提供了以下三种方法编译到 WMI 存储库的托管对象格式 (MOF) 文件:
方法 1: 运行 MOF 文件指定为命令行参数将 Mofcomp.exe 文件。
方法 2: 使用 IMofCompiler 接口和 $ CompileFile 方法。
方法 3: 拖放到 %SystemRoot%\System32\Wbem\MOF 文件夹的 MOF 文件。
Microsoft 建议您到存储库编译 MOF 文件使用前两种方法。也就是运行 Mofcomp.exe 文件,或使用 IMofCompiler::CompileFile 方法。
第三种方法仅为向后兼容性与早期版本的 WMI 提供,并因为此功能可能不会提供在将来的版本后,不应使用。 继续阅读

Perform point-in-time table-level restore in Informix Dynamic Server

This article describes how to perform point-in-time table-level restores that extract tables or portions of tables from archives and logical logs. Table-level restore is a new feature for IBM® Informix® Dynamic Server Version 10.0. This feature is useful where portions of a database, a table, a portion of a table, or a set of tables need to be recovered and also useful in situations where tables need to be moved across server versions or platforms.

Introduction

Informix Dynamic Server V10.0 (IDS) provides you with the ability to easily extract data in tables of databases from a 0-level backup to a specified point in time. The extracted data can be loaded in internal, external tables or ASCII files. This feature lets you:

  • Extract a table or a set of tables
  • Filter the retrieved data
  • Retrieve just a subset of columns
  • Repartition the data
  • Place data in the same version of the database, or in a different database version with a different machine architecture.

To use this feature, you use the archecker utility to extract and load data to specific tables.

Overview of the archecker utility

The archecker utility requires the following:

  • The archecker configuration file
  • The schema command file
  • The archecker command, in order to execute

继续阅读

Mysql常见问题

1.1 MySQL常见问题

1.Slave I/O: error connecting to master ‘backup@192.168.1.x:3306’-retry-time: 60 retries: 86400,Error_code:1045
解决方法:
从服务器上删除掉所有的二进制日志文件,包括一个数据目录下的master.info文件和hostname-relay-bin开头的文件。
master.info::记录了Mysql主服务器上的日志文件和记录位置、连接的密码。

2. Errorreading packet from server: File ‘/home/mysql/mysqlLog/log.000001’ not found(Errcode: 2) ( server_errno=29)
解决方法:
由于主服务器运行了一段时间,产生了二进制文件,而slave是从log.000001开始读取的,删除主机二进制文件,包括log.index文件。

3.Slave SQL: Error ‘Table ‘xxxx’ doesn’t exist’ on query.Default database: ‘t591’.Query: ‘INSERT INTO `xxxx`(type,post_id,browsenum)
SELECT type,post_id,browsenum FROM xxxx WHEREhitdate=’20090209”, Error_code: 1146

解决方法:
由于slave没有此table表,添加这个表,使用slave start 就可以继续同步。 继续阅读

Galera Cluster

Galera Cluster Documentation

Galera Cluster is a synchronous multi-master database cluster, based on synchronous replication and Oracle’s MySQL/InnoDB. When Galera Cluster is in use, you can direct reads and writes to any node, and you can lose any individual node without interruption in operations and without the need to handle complex failover procedures.

At a high level, Galera Cluster consists of a database server—that is, MySQL or MariaDB—that then uses the Galera Replication Plugin to manage replication. To be more specific, the MySQL replication plugin API has been extended to provide all the information and hooks required for true multi-master, synchronous replication. This extended API is called the Write-Set Replication API, or wsrep API.Galera

Through the wsrep API, Galera Cluster provides certification-based replication. A transaction for replication, the write-set, not only contains the database rows to replicate, but also includes information on all the locks that were held by the database during the transaction. Each node then certifies the replicated write-set against other write-sets in the applier queue. The write-set is then applied, if there are no conflicting locks. At this point, the transaction is considered committed, after which each node continues to apply it to the tablespace.

This approach is also called virtually synchronous replication, given that while it is logically synchronous, the actual writing and committing to the tablespace happens independently, and thus asynchronously on each node. 继续阅读

Sharding-JDBC

简介(https://github.com/dangdangdotcom/sharding-jdbc)

Sharding-JDBC直接封装JDBC API,可以理解为增强版的JDBC驱动,旧代码迁移成本几乎为零:

  • 可适用于任何基于javaORM框架,如:JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC
  • 可基于任何第三方的数据库连接池,如:DBCP, C3P0, BoneCP, Druid等。
  • 理论上可支持任意实现JDBC规范的数据库。虽然目前仅支持MySQL,但已有支持OracleSQLServerDB2等数据库的计划。

Sharding-JDBC定位为轻量级java框架,使用客户端直连数据库,以jar包形式提供服务,未使用中间层,无需额外部署,无其他依赖,DBA也无需改变原有的运维方式。SQL解析使用Druid解析器,是目前性能最高的SQL解析器。 继续阅读

MySQL Cluster fault tolerance – impact of deployment decisions

MySQL Cluster fault tolerance – impact of deployment decisions

Typical management configuration

Fig 1. Typical management configuration

MySQL Cluster is designed to be a High Availability, Fault Tolerant database where no single failure results in any loss of service.

This is however dependent on how the user chooses to architect the configuration – in terms of which nodes are placed on which physical hosts, and which physical resources each physical host is dependent on (for example if the two blades containing the data nodes making up a particular node group are cooled by the same fan then the failure of that fan could result in the loss of the whole database).

Of course, there’s always the possibility of an entire data center being lost due to earthquake, sabotage etc. and so for a fully available system, you should consider using asynchronous replication to a geographically remote Cluster.

Fig 1. illustrates a typical small configuration with one or more data nodes from different node groups being stored on two different physical hosts and a management node on an independent machines (probably co-located with other applications as its resource requirements are minimal. If any single node (process) or physical host is lost then service can continue.

继续阅读

不同场景下 MySQL 的迁移方案

一 目录


二 为什么要迁移

继续阅读