显示标签为“mysql”的博文。显示所有博文
显示标签为“mysql”的博文。显示所有博文

2014年5月8日星期四

普通用户 mysqldump 导出数据库出现错误的解决办法

今天用 mysql 的一个普通用户 admin 用 mysqldump 工具进行备份的时候，出现以下错误

 mysqldump: Got error: 1044: Access denied for user 'admin'@'localhost' to database 'mydatabase' when using LOCK TABLES
后来查阅网上资料，终于得到解决办法。只需添加 --single-transaction 选项。

 $ mysqldump --single-transaction -u admin -p mydatabase > mydatabase.sql
发帖者 时间： 没有评论:
标签： ,

2012年12月7日星期五

MySQL 5.5 主从复制概述

    主从复制功能通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间 SELECT 查询可以发送到从服务器，以降低主服务器的查询处理负荷。修改数据的语句仍然发送到主服务器，以使主、从服务器保持同步。如果非更新查询为主（如 SELECT 查询），该负载均衡策略很有效。

    MySQL 主从复制优点如下：

  • 增长健壮性。主服务器出现问题时，切换到从服务器作为备份。
  • 优化响应时间。不要同时在主从服务器上进行更新，这样可能引起冲突。
  • 在从服务器备份过程中，主服务器继续处理更新。


主从复制工作原理


    主从复制通过 3 个过程实现，其中一个过程发生在主服务器上，另外两个过程发生在从服务器上。具体情况如下：


  1. 主服务器将用户对数据库更新的操作以二进制格式保存到 Binary Log 日志文件中，然后由 Binlog Dump 线程将 Binary Log 日志文件传输给从服务器。
  2. 从服务器通过一个 I/O 线程将主服务器的  Binary Log 日志文件中的更新操作复制到一个叫 Relay Log 的中继日志文件中。
  3. 从服务器通过另一个 SQL 线程将 Relay Log 中继日志文件中的操作依次在本地执行，从而实现主从之间数据的同步。

  主从复制详细过程如图所示

主从复制过程

（1）BinLog Dump 线程

    BinLog Dump 线程运行在主服务器上，主要工作是把 Binary Log 二进制日志文件的数据发送给从服务器。

    使用SHOW PROCESSLIST 语句查看该线程是否正在运行。

（2）I/O 线程

    从服务器执行 START SLAVE 语句后，创建一个 I/O 线程。此线程运行在从服务器上，与主服务器建立连接，然后向主服务器发出更新请求。之后，I/O 线程将主服务器发送的更新操作复制到本地 Relay Log 日志文件中。

    使用 SHOW SLAVE STATUS 语句查看 I/O 线程状态。

 （3）SQL 线程

    SQL 线程运行在从服务器上，主要工作是读取 Relay Log 日志文件中的更新操作，并将这些操作依次执行，从而使主从服务器数据得到同步。

   

主从复制详述

    MySQL 服务器之间的复制是基于二进制日志机制的。主机的数据库实例会把更新和变化的事件写入二进制日志。根据数据库的设置，二进制日志被存储为不同的日志格式。从服务器根据配置从主服务器那里读取二进制日志，然后在本地数据库执行相应的二进制日志事件。

    在复制过程中，主机是被动的。一旦启用了二进制日志，所有的语句都会被记录进里面。每一台从服务器都会接受一份二进制日志副本，从服务器决定二进制日志哪些语句需要被执行；你不应该在主服务器配置只记录某一类的事件。如果你没有指定的话，在主服务上二进制事件在从服务器都会被执行。如果有需要的话，你可以在从服务器上指定那些应用于特殊的数据库或者表事件被执行。

    每一台从服务器都保存有一条二进制日志坐标的记录：文件名和要从主服务器读取和处理的位置。这意味着多台从服务器能同时连接主机和读取同一个二进制日志不同的部分。因为这个过程是由从服务器处理，单台从服务器连接或者断开主服务器的连接都不会影响主服务器的操作。由于每台从服务器都会记住执行到二进制日志的哪个位置，所以即使断开与主服务器的连接，然后再次连接，都会从上次断开的位置读取二进制日志。

    主服务器和从服务器必须配置成唯一的ID。另外，每台从服务器必须配置主服务的主机名、日志名称和读取日志相应的位置。这些详细的配置可以在从服务器上的会话中用 CHANGE MASTER TO  语句来控制。这些配置信息都保存在从服务器的文件 master.info 中。
发帖者 时间： 没有评论:
标签： ,

2012年11月26日星期一

MySQL 5.5 复制格式

基于语句复制的优点

从 MySQL 3.23 起就已经支持基于语句复制了

不用把大量的数据写进日志文件。当删除或者更新大量的数据时，日志的储存空间增长速度不会很快

日志记录了那些数据更改的SQL语句，保证数据库的一致。

基于语句复制的缺点

  • 基于语句的复制中，以下语句是不安全的。使用基于语句的复制中，并非所有的修改数据（例如 INSERT DELETE， UPDATE和 REPLACE语句）语句都可以成功被复制。在使用基于语句的复制中，任何不确定的行为是难以复制的。诸如这类数据修改语言（Data Modification Language）,还有以下这些
  • 在基于行的复制中，对于 INSERT ... SELECT 的 SQL 语句，需要行级别的锁定。
  • 在需要进行表扫描（因为 WHERE 子句中没有用到索引）的UPDATE 语句中，在基于行的复制中必须锁定行
  • 对于使用InnoDB引擎的：一个使用 AUTO_INCREMENT 的 INSERT 语句，会阻塞其他不冲突的 INSERT 语句。
  • 对于复杂的语句，语句必须先被从服务器识别和执行，再进行更新或插入操作。基于行的复制，从服务器只会修改受影响的行，不执行所有语句。
  • 在从服务器上在识别出现错误的话，特别是在执行复杂的语句时，基于语句的复制会随着时间的推移,那些受影响的行可能会慢慢增加误差。
  • 存储函数调用语句时会执行相同的 NOW（）值。
  • 用户自定义的函数功能必须是唯一确定的，才能适用于从服务器
  • 在主服务器和从服务器之间,表的定义必须保持一致

基于行复制的优点

  • 所有的改变都能被复制，这在复制中是最安全的一种方式。

mysql 数据库不会被复制, mysql 数据库不会被看作一个节点特定的数据库。但是，基于语句的复制会复制这些信息，包括 GRANT、REVOKE 还有触发器，存储过程和视图，都会被从服务器复制。

对于这些语句 CREATE TABLE ... SELECT，用 CREATE 语句创建表，都是用基于语句的复制的格式。而数据的插入则是使用 行复制。

对于下面的语句，在主服务器上只要很少的行锁定，能支持高并发。

INSERT ... SELECT

带有 AUTO_INCREMENT 的 INSERT 语句


UPDATE or DELETE statements with WHERE clauses that do not use keys or do not change most of the examined rows.


Row-Based 日志和复制的用法
  • RBL,非事务表和停止从服务器。 如果用 row-based 记录时，当从服务器在更新一个非事务表时，从服务器忽然被停止了，从服务器的数据库的可能会处于非一致的状态。所以当用 row-based 记录时，推荐用支持事务的引擎，如 InnoDB。
  • 数据库级别的复制选项。这些 --replicate-do-db, --replicate-ignore-db, 和 --replicate-rewrite-db 选项在基于  row-based 和  statement-based 来记录时，区别是很大的。基于这一点，尽量避免使用数据库级别的选项而改用表级别的选项，诸如  --replicate-do-table 和 --replicate-ignore-table
  • 不支持过滤 server ID 系统变量。经常会遇到这样一个场景：在 UPDATE 或者 DELETE 语句中通过 WHERE 中用 @@server_id <> id_value 语句来过滤掉从服务器的改变。如 WHERE @@server_id <> 1 。这个在用 row-based 来记录时，不能正常的工作。如果你一定要用 server_id 系统变量来声明过滤，要有 --binlog_format=STATEMENT.你也可以在  CHANGE MASTER TO 语句中 用IGNORE_SERVER_IDS 选项来过掉server_id 系统变量的影响。
  • 二进制日志缺乏校验
  • 当系统变量 slave_exec_mode 的值是 IDEMPOTENT 时，未能将更改应用于row-based 记录。因为无法找到原来的行不触发一个错误或会导致复制失败。这意味着更新不能成功的应用于从服务器上。所以主服务器和从服务器不会再同步。

发帖者 时间： 没有评论:
标签： ,

2012年10月12日星期五

MySQL 5.5 复制配置

这里以 Windows 7 平台下 wamp  2.1 为例说明怎样配置 MySQL 的主从复制。

第一步：编辑主服务器 MySQL 的 my.ini 文件。


# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id  = 1​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​

注意，上面的编辑应该是在 [wampmysqld] 节点下面进行编辑，如下图，然后按照下面的方式重启MySQL。
[wampmysqld] 节点
[wampmysqld] 节点

 

shell> mysqladmin -u root -p shutdown   #关闭MySQL
shell> mysqld --remove wampmysqld   #移除
wampmysqld 服务






shell> mysqld --install wampmysqld --binlog-do-db=test  #添加 wampmysqld 服务，并只对数据库 test 进行日志的操作。然后需手动在 控制面板\系统和安全\管理工具\服务 中启动 mysql

服务


第二步：编辑从服务器 MySQL 的 my.ini 文件。


 # required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
server-id       = 2​​​​​​​​​​​​​​​​​​​​

同样的，上面的编辑也是在 [wampmysqld] 节点下面进行编辑，如下图，然后按照下面的方式安装 MySQL 服务。

 
shell> mysqladmin -u root -p shutdown   #关闭MySQL
shell> mysqld --remove wampmysqld   #移除
wampmysqld 服务




shell> mysqld --install wampmysqld   #安装为服务

第三步：专门为从服务器创建一个用户（可选）

打开主服务器MySQL客户端，输入以下命令：

 

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

第四步：获取主服务器二进制日志的坐标

1.打开主服务器MySQL客户端，执行下面的SQL语句来防止MySQL的写操作

mysql> FLUSH TABLES WITH READ LOCK;

2.获取主服务器的二进制文件名和位置

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

    在主服务器，我们能够通过 --binlog-do-db and --binlog-ignore-db 选项来控制哪些数据库被记录入二进制日志。详情见：Binary Log Options and Variables

第五步：使用 mysqldump 创建数据快照

1.打开主服务器 MySQL 客户端

mysql> FLUSH TABLES WITH READ LOCK;

2.打开一个新的 DOS 窗口，用 mysqldump 创建转储，可以是你要复制的所有数据库，也可以是单个数据库

shell> mysqldump --all-databases --lock-all-tables >dbdump.db

   还有一种方法是用裸转储，用 --master-data 选项，在从服务器启动复制进程的时候会自动添加 CHANGE MASTER TO 语句。

shell> mysqldump --all-databases --master-data >dbdump.db

3.在第1步的 MySQL 客户端输入以下命令来解锁

mysql> UNLOCK TABLES;


第六步：使用复制原始文件创建数据快照

   如果你的数据库很大，复制数据库的原始文件会比用 mysqldump 的效率高。
   
   如果你主从服务器的系统变量 ft_stopword_file, ft_min_word_len 或者 ft_max_word_len 有差异，在复制那些有全文索引的表也会出现问题。

第七步：在已经有数据的从服务器上启动复制


a. 用 --skip-slave-start 选项启动从服务器的MySQL，确保复制还没开始

shell> mysqld --skip-slave-start    #然后需手动在 控制面板\系统和安全\管理工具\服务 中启动 mysql



b. 导入转储文件










shell> mysql < fulldb.dump









6.设定从服务器复制的相关配置








mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;














7.启动从服务器的线程








mysql> START SLAVE;












发帖者



时间：





没有评论:
  


















标签：
,

















          

        

          

        

2012年9月26日星期三


          

        










PHP5中使用PDO连接数据库












1.什么是PDO？



   PDO(PHP Data Objects) 是 PHP 的一个扩展，定义了一系列轻量级的、通用性的、跨数据库的访问接口。



   在以前，如果你用的是MySQL数据库，要打开 php_mysql.dll 的一个扩展，然后用 PHP 提供的 MySQL 函数来访问数据库；如果你用的是 MSSQL，就打开 php_mssql.dll 的扩展，用 PHP 提供的 MSSQL 函数来访问数据库。现在，你只要打开 pdo 相应的数据库扩展（例如：在Windows 平台 PHP 5.3.5 的 php.ini 中 php_pdo_mysql.dll,php_pdo_mssql.dll），就能用 PDO 提供的各种方法来访问各种不同类型的数据库，如MySQL、Oracle、MSSQL。



   PDO 是 PHP 5.1 新加入的，在 PHP 5.0 中 PDO 也能作为 PECL 的一个扩展来用，但是它不适用于 PHP 5.0 的早期版本。



   它有点类似Java框架的Hibernate。







2.基本例子




employees表


employees表





<?php

/* Connect to an ODBC database using driver invocation */

$dsn = 'mysql:dbname=test;host=127.0.0.1';

$user = 'root';

$password = 'root';



try {

    $dbh = new PDO($dsn, $user, $password);

} catch (PDOException $e) {

    echo 'Connection failed: ' . $e->getMessage();

}

$sth = $dbh->query('SELECT  * FROM employees');//query方法用于查询

$result = $sth->fetch();//获取第一行数据


print_r($result);

$result = $sth->fetchAll();//获取所有数据

print_r($result);

?>​






<?php

/* Connect to an ODBC database using driver invocation */

$dsn = 'mysql:dbname=test;host=127.0.0.1';

$user = 'root';

$password = 'root';



try {

    $dbh = new PDO($dsn, $user, $password);

} catch (PDOException $e) {

    echo 'Connection failed: ' . $e->getMessage();

}

//插入数据，exec方法用于 INSERT,UPDATE,DELETE等操作

$count = $dbh->exec("INSERT INTO employees (`id`,`fname`,`lname`,`hired`,`separated`,`job_code`,`store_id`) 

VALUES ('5','sherlock','wang','2012-01-01','2013-03-01','10','20')");



print("affected  $count rows.\n");

?>​




<?php
/* Connect to an ODBC database using driver invocation */
$dsn = 'mysql:dbname=test;host=127.0.0.1';
$user = 'root';
$password = 'root';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
$sth = $dbh->prepare('SELECT  * FROM employees WHERE job_code=:job_code AND store_id=:store_id');//prepare方法用於 SELECT、INSERT、UPDATE 及 DELETE 等需要多次進行資料處理的 SQL 上
$sth->execute(array(':job_code' => 2, ':store_id' => '2'));
$result = $sth->fetchAll();//获取所有数据
print_r($result);
$sth->execute(array(':job_code' => 12, ':store_id' => '7'));
$result = $sth->fetchAll();//获取所有数据
print_r($result);
?>​




<?php
/* Connect to an ODBC database using driver invocation */
$dsn = 'mysql:dbname=test;host=127.0.0.1';
$user = 'root';
$password = 'root';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
$sth = $dbh->prepare('SELECT  * FROM employees WHERE job_code=? AND store_id=?');//用 ? 代替
$sth->execute(array(2,'2'));//按 ? 出现的次序设置值
$result = $sth->fetchAll(PDO::FETCH_ASSOC);
print_r($result);
$sth->execute(array(12,'7'));
$result = $sth->fetchAll(PDO::FETCH_NUM);
print_r($result);
?>​




详情：PDO API












发帖者



时间：





没有评论:
  


















标签：
,
,
,

















        

      









订阅：
博文 (Atom)