标签存档: mysql

MySQL常用的三种备份方法

mysql按照备份恢复方式分为逻辑备份和物理备份。逻辑备份是备份sql语句,在恢复的时候执行备份的sql语句实现数据库数据的重现,物理备份就是备份数据文件了,比较形象点就是cp下数据文件,但真正备份的时候自然不是的cp这么简单。

这2种备份各有优劣,一般来说,物理备份恢复速度比较快,占用空间比较大,逻辑备份速度比较慢,占用空间比较小

下面介绍以下3种常用的备案方法

一、mysqldump工具备份

mysqldump由于是mysql自带的备份工具,所以也是最常用的mysql数据库的备份工具。支持基于InnoDB的热备份。但由于是逻辑备份,所以速度不是很快,适合备份数据量比较小的场景。
mysqldump完全备份+二进制日志 —>实现时间点恢复

温备:

在使用MyISAM引擎中,只能使用温备份,这时候要防止数据的写入,所以先加上读锁

这时候可以进入数据库手动加读锁。这样比较麻烦,在mysqldump工具中直接有一个加锁的选项

mysqldump –databases mydatabase –lock-all-tables –flush-logs> /tmp/backup-`date +%F-%H-%M`.sql

如果是针对某张表备份,只要在数据库名称后面加上表名称就行了

这里注意,要实现时间点的恢复,加上–flush-logs选项,在使用备份文件恢复后,然后再基于二进制日志进行时间点的恢复

时间点的恢复方法

mysqlbinlog mysql-bin.000000x > /tmp/PointTime.sql

然后用mysql命令导入这个sql脚本就行了

热备:如果使用的是InnoDB引擎,就不必进行对数据库加锁的操作,加一个选项既可以进行热备份:–single-transaction
mysqldump –databases mydb –single-transaction –flush-logs –master-data=2 > /tmp/backup-`date +%F-%H-%M`.sql

注意点
恢复的时刻关闭二进制日志
mysql>set sql_log_bin=0;
因为这是基于逻辑备份方式,在恢复日志时会执行sql语句插入数据,而恢复时候插入数据的日志没有意义。

二、基于LVM快照备份

在物理备份中 ,有基于文件系统的物理备份(LVM的快照),也可以直接用tar之类的命令打包。但这些只能进行冷备份
不同的存储引擎能备份的级别也不一样,MyISAM能备份到表级别,而InnoDB不开启每表一文件的话就只能备份整个数据库。

下面就介绍下使用LVM的快照功能进行备份
为了安全 首先在数据库上施加读锁
mysql>FLUSH TABLES WITH READ LOCK;

刷新一下二进制日志,便于做时间点恢复

mysql>FLUSH LOGS;

然后创建快照卷

lvcreate –L 1G –s –n data-snap –p –r /dev/myvg/mydata

最后进入数据库释放读锁

UNLOCK TABLES;

挂载快照卷进行备份

mount –r /dev/myvg/data-snap /mnt/snap

然后对/mnt/snap下的文件进行打包备份
还原的时候,关闭mysqld,然后备份二进制日志后将原来备份的文件还原进去,然后通过二进制日志还原到出错的时间点(通过二进制还原时间点的时候不要忘了暂时关闭二进制日志)

三、使用percona提供的xtrabackup(推荐)

支持InnoDB的物理热备份,支持完全备份,增量备份,而且速度非常快,而且支持InnoDB引擎的数据在不同数据库迁移
为了让xtrabackup支持更多的功能扩展,配置InnoDB每表一个文件的功能
在my.cnf的mysqld中加入此项: innodb_file_per_table=1
此项不启用将不支持备份单独的表
但如果之前没有启用这个选项,要实现单表一文件的话,可以用mysqldump导出数据,然后启用该选项,恢复回去后就是单表一文件了

首先下载xtrabackup,下载地址:http://www.percona.com/software/percona-xtrabackup,可以直接下载rpm包安装即可。

xtrabackup有完全备份,增量备份和部分备份(前面开启innodb每表一文件,就是为了此功能)

1.完全备份整个数据库

innobackupex –user=root –password=123456 /tmp/backup

此时会在/tmp/backup目录下生成以时间为名的文件夹,里面是备份文件

在这里,备份的数据还不能直接用来还原,因为备份数据中会含有尚未提交的事务或者未同步到数据文件中的事物。这里需要用prepare回滚事物使数据文件处于一致性。

innobackupex –apply-log /tmp/backup/dir

处理完成后才能用来还原数据,用此命令还原

innobackupex –copy-back /tmp/backup/dir

要实现时间点还原,还是需要使用二进制日志

2.增量备份

增量备份支持Innodb,对于MyISAM只能完全备份
innobackupex –incremental /tmp/backup/incremental –incremental-basedir=/tmp/backup/dir

在进行一次增量备份–incremental-basedir要指向上一次增量备份的目录

如果要进行还原,先进行prepare处理

这里处理的方式,将备份合并

innobackupex –apply-log –redo-only /tmp/backup/dir

innobackupex –apply-log –redo-only /tmp/backup/dir –incremental-dir=/tmp/backup/incremental

最后使用完全备份的那个备份还原

至于差异备份,只要每次将basedir指向完全备份文件夹就行了

最后再废话一句:要实现时间点还原,是需要使用二进制日志的,所以备份好二进制日志至关重要。除非在恢复时间点和上一次备份时间点这段时间的数据对你来说无所谓。

Mysql命令行导入导出.sql文件

如果通过phpmyadmin等web客户端操作mysql的话,经常会有2M的容量限制。不过如果你可以操作命令行的话,可以轻松解决这个问题:

一.MYSQL的命令行模式的设置:

桌面->我的电脑->属性->环境变量->新建->

PATH=“;pathmysqlin;”其中path为MYSQL的安装路径。

二.简单的介绍一下命令行进入MYSQL的方法:

1.C:>mysql -h hostname -u username -p

按ENTER键,等待然后输入密码。这里hostname为服务器的名称,如localhost,username为MYSQL的用户名,如root。

进入命令行后可以直接操作MYSQL了。

2.简单介绍一下MYSQL命令:

   mysql->CREATE DATABASE dbname;//创建数据库

   mysql->CREATE TABLE tablename;//创建表

   mysql->SHOW DATABASES;//显示数据库信息,有那些可用的数据库。

  mysql->USE dbname;//选择数据库

   mysql->SHOW TABLES;//显示表信息,有那些可用的表

   mysql->DESCRIBE tablename;//显示创建的表的信息

三.从数据库导出数据库文件:

1.将数据库mydb导出到e:mysqlmydb.sql文件中:

打开开始->运行->输入cmd    进入命令行模式

c:>mysqldump -h localhost -u root -p mydb >e:mysqlmydb.sql

然后输入密码,等待一会导出就成功了,可以到目标文件中检查是否成功。

2.将数据库mydb中的mytable导出到e:mysqlmytable.sql文件中:

c:>mysqldump -h localhost -u root -p mydb mytable>e:mysqlmytable.sql

3.将数据库mydb的结构导出到e:mysqlmydb_stru.sql文件中:

c:>mysqldump -h localhost -u root -p mydb –add-drop-table >e:mysqlmydb_stru.sql

四.从外部文件导入数据到数据库中:

从e:mysqlmydb2.sql中将文件中的SQL语句导入数据库中:

1.从命令行进入mysql,然后用命令CREATE DATABASE mydb2;创建数据库mydb2。

2.退出mysql 可以输入命令exit;或者quit;

3.在CMD中输入下列命令:

c:>mysql -h localhost -u root -p mydb2 < e:mysqlmydb2.sql

然后输入密码,就OK了。

五.下面谈一下关于导入文件大小限制问题的解决:

默认情况下:mysql 对导入文件大小有限制的,最大为2M,所以当文件很大时候,直接无法导入,下面就这个问题的解决列举如下:

1.在php.ini中修改相关参数:

影响mysql导入文件大小的参数有三个:

    memory_limit=128M,upload_max_filesize=2M,post_max_size=8M

修改upload_max_filesize=200 M  这里修改满足你需要的大小,

可以同时修改其他两项memory_limit=250M  post_max_size=200M

这样就可以导入200M以下的.sql文件了。

修改遗失了的mysql的root密码

如果忘记了 MySQL 的 root 密码,可以用以下方法重新设置:

1. KILL掉系统里的MySQL进程;

2. 用以下命令启动MySQL,以不检查权限的方式启动;

mysqld_safe -skip-grant-tables &

3. 然后用空密码方式使用root用户登录 MySQL;

mysql -u root

4. 修改root用户的密码;

mysql> update mysql.user set password=PASSWORD(‘新密码‘) where User=‘root‘; 
mysql> flush privileges; 
mysql> quit

 

5. 重新启动MySQL,就可以使用新密码登录了。

 

 更改mysql的root密码的几种方法

第一種︰

shell> mysql -u root mysql mysql> UPDATE user SET Password=PASSWORD(’new_password’) WHERE user=’root’; mysql> FLUSH PRIVILEGES;

第二種︰使用 set password 語法

shell> mysql -u root mysql mysql> SET PASSWORD FOR root=PASSWORD(’new_password’);

第三種︰使用 mysqladmin命令

shell>mysqladmin -u root password new_password

 

MySQL中最常用的几个命令

在MySQL里面,如果你只是想新建一个User、一个Database。那么知道下面这几个命令就足够了:

登录MySQL: mysql -u root -p

添加数据库: create database xxxxx

添加并授权用户: grant all privileges on xxxxx.* to ‘newuser‘@‘localhost‘ identified by "password"

 

MySQL中修改密码及访问限制设置详解 [收藏]

MySQL是一个真正的多用户、多线程SQL数据库服务器。MySQL是以一个客户机/服务器结构的实现,它由一个服务器守护程序mysqld和很多不同的客户程序和库组成。由于其源码的开放性及稳定性,且与网站流行编徎语言PHP的完美结合,现在很多站点都利用其当作后端数据库,使其获得了广泛应用。处于安全方面的考虑,需要为每一用户赋于对不同数据库的访问限制,以满足不同用户的要求。下面就分别讨论,供大家参考。

一、MySQL修改密码方法总结

首先要说明一点的是:一般情况下,修改MySQL密码是需要有mysql里的root权限的,这样一般用户是无法更改密码的,除非请求管理员帮助修改。

方法一

使用phpMyAdmin (图形化管理MySql数据库的工具),这是最简单的,直接用SQL语句修改mysql数据库库的user表,不过别忘了使用PASSWORD函数,插入用户用Insert命令,修改用户用Update命令,删除用Delete命令。在本节后面有数据表user字段的详细介绍。

方法二

使用mysqladmin。输入:

mysqladmin -u root -p oldpassword newpasswd

执行这个命令后,需要输入root的原密码,这样root的密码将改为newpasswd。同样,把命令里的root改为你的用户名,你就可以改你自己的密码了。 当然如果你的mysqladmin连接不上mysql server,或者你没有办法执行mysqladmin,那么这种方法就是无效的,而且mysqladmin无法把密码清空。

下面的方法都在mysql提示符下使用,且必须有mysql的root权限:

方法三

 

mysql"gt; INSERT INTO mysql.user (Host,User,Password)
 valueS (‘%‘,‘system‘, PASSWORD(‘manager‘)); 
mysql"gt; FLUSH PRIVILEGES

确切地说这是在增加一个用户,用户名为system,密码为manager。注意要使用PASSWORD函数,然后还要使用FLUSH PRIVILEGES来执行确认。

方法四

和方法三一样,只是使用了REPLACE语句

 

mysql"gt; REPLACE INTO mysql.user (Host,User,Password) 
valueS(‘%‘,‘system‘,PASSWORD(‘manager‘)); 
mysql"gt; FLUSH PRIVILEGES

方法五

使用SET PASSWORD语句

 

mysql"gt; SET PASSWORD FOR system@"quot;%"quot; = PASSWORD(‘manager‘);

你也必须使用PASSWORD()函数,但是不需要使用FLUSH PRIVILEGES来执行确认。

方法六

使用GRANT … IDENTIFIED BY语句,来进行授权。

 

mysql"gt; GRANT USAGE ON *.* TO system@"quot;%"quot; IDENTIFIED BY ‘manager‘;

这里PASSWORD()函数是不必要的,也不需要使用FLUSH PRIVILEGES来执行确认。

注:PASSWORD()函数作用是为口令字加密,在程序中MySql自动解释。

二、MySql中访问限制的设置方法

我们采用两种方法来设置用户。进入到Mysql执行目录下(通常是c:mysqlin)。输入mysqld-shareware.exe,输入mysql –user=root mysql ,不然不能添加新用户。进入到mysql"gt;提示符下进行操作。假设我们要建立一个超级用户,用户名为system,用户口令为manager。

方法一

用Grant 命令授权,输入的代码如下:

 

mysql"gt;GRANT ALL PRIVILEGES ON *.* TO system@localhost 
IDENTIFIED BY ‘manager‘ WITH GRANT OPTION;

应显示:Query OK, 0 rows affected (0.38 sec)

方法二

对用户的每一项权限进行设置:

 

mysql"gt;INSERT INTO user valueS(‘localhost‘,‘system‘,
PASSWORD(‘manager‘), ‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘);

对于3.22.34版本的MySQL,这里共14个"quot;Y"quot;,其相应的权限如下(按字段顺序排列): 权限 表列名称 相应解释 使用范围

select Select_priv 只有在真正从一个表中检索时才需要select权限 表

insert Insert_priv 允许您把新行插入到一个存在的表中 表

update Update_priv 允许你用新值更新现存表中行的列 表

delete Delete_priv 允许你删除满足条件的行 表

create Create_priv 允许你创建新的数据库和表 数据库、表或索引

drop Drop_priv 抛弃(删除)现存的数据库和表 数据库或表

reload Reload_priv 允许您告诉服务器再读入授权表 服务器管理

shutdown Shutdown_priv 可能被滥用(通过终止服务器拒绝为其他用户服务) 服务器管理

process Process_priv 允许您察看当前执行的查询的普通文本,包括设定或改变口令查询 服务器管理

file File_priv 权限可以被滥用在服务器上读取任何可读的文件到数据库表 服务器上的文件存取

grant Grant_priv 允许你把你自己拥有的那些权限授给其他的用户 数据库或表

references References_priv 允许你打开和关闭记录文件 数据库或表

index Index_priv 允许你创建或抛弃(删除)索引 表

alter Alter_priv 允许您改变表格,可以用于通过重新命名表来**权限系统 表

如果创建用户时只有select、insert、update和delete权限,则允许用户只能在一个数据库现有的表上实施操作。

下面就可以创建我们要用到的数据库了,我们直接输入. 例如:我们要创建数据库名为XinXiKu,可用如下代码:

mysql"gt;create database XinXiKu;

应显示:Query OK, 1 row affected (0.00 sec)

巧在MySQL中直接储存图片

如果你想把二进制的数据,比如说图片文件和HTML文件,直接保存在你的MySQL数据库,那么这篇文章就是为你而写的!我将告诉你怎样通过HTML表单来储存这些文件,怎样访问和使用这些文件。
  本文概述: 
  在mysql中建立一个新的数据库 
  一个怎样储存文件的例子程序 
  一个怎样访问文件的例子程序 
  在mysql中建立一个新的database
   首先,你必须在你的mysql中建立一个新的数据库,我们将会把那些二进制文件储存在这个数据库里。在例子中我会使用下列结构,为了建立数据库,你必须做下列步骤: 
  进入MySql控制器 
  输入命令"create database binary_data;" 
  输入命令"use binary_data;" 
  输入命令"CREATE TABLE binary_data ( id INT  (4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
description CHAR(50), bin_data LONGBLOB, filename CHAR(50), filesize CHAR  (50), filetype CHAR(50));" (不能断行) 
  如果没有意外,数据库 和 表 应该建立好了。 
  一个怎样储存文件的例子程序 用这个例子你可以通过Html表单将文件传输到数据库中。.

store.php3

以下为引用的内容:

// store.php3 – by Florian Dittmer 
?>

// 如果提交了表单,代码将被执行:

if ($submit) {

     // 连接到数据库
     // (你可能需要调整主机名,用户名和密码)

    MYSQL_CONNECT( "localhost", "root", "password");
    mysql_select_db( "binary_data");

    $data = addslashes(fread(fopen($form_data,  "r"), filesize($form_data)));

    $result=MYSQL_QUERY( "INSERT INTO binary_data (description,bin_data,filename,filesize,filetype) ".
         "VALUES (’$form_description’,’$data’,’$form_data_name’,’$form_data_size’,’$form_data_type’)");

    $id= mysql_insert_id();
    print  "

This file has the following Database ID: $id";

    MYSQL_CLOSE();

} else {

     // 否则显示储存新数据的表单
?> 
}

?>

  如果你执行了这个程序,你将会看见一个简单的Html表单,单击“浏览”选择一个文件,然后单击提交。
  当文件上传至web服务器之后,程序将会告诉你刚刚上传的文件的ID,记住这个ID,待会要用的。
  一个怎样访问文件的例子程序
  你可以通过这个程序访问你刚才储存的文件

以下为引用的内容:

// getdata.php3 – by Florian Dittmer 
// 调用方法: getdata.php3?id=

if($id) {

     // 你可能需要调整主机名,用户名和密码:
    @MYSQL_CONNECT( "localhost", "root", "password");

    @mysql_select_db( "binary_data");

    $query =  "select bin_data,filetype from binary_data where id=$id";
    $result = @MYSQL_QUERY($query);

    $data = @MYSQL_RESULT($result,0, "bin_data");
    $type = @MYSQL_RESULT($result,0, "filetype");

    Header(  "Content-type: $type");
    echo $data;

};
?>

  程序必须知道要访问那个文件, 你必须将ID作为一个参数。
  例如: 一个文件在数据库中的ID为2. 你可以这样调用它:
                    getdata.php3?id=2如果你将图片储存在数据库里, 你可以向调用图片一样调用它。
  Example: 一个图片文件在数据库中的ID为3. 你可以这样调用它:
                    
  怎样储存大于1MB的文件:
  如果你想储存大于1MB的文件,你必须对你的程序、PHP设置、SQL设置进行许多修改,。
  下面几条也许可以帮助你储存小于24MB的文件:
  修改 store.php3 ,将 MAX_FILE_SIZE 的值改成 24000000。 
  修改你的PHP设置,在一般情况下,PHP只允许小于2MB的文件,你必须将max_filesize(在php.ini中)的值改成24000000

  去掉MYSQL的数据包大小限制,在一般情况下 MYSQL 小于1 MB的数据包. 
  你必须用以下参数重启你的MYSQL
  /usr/local/bin/safe_mysqld -O key_buffer=16M -O table_cache=128 -O sort_buffer=4M -O record_buffer=1M -O max_allowed_packet=24M 

如何在.Net中访问MySQL数据库

引言

如果你不是只在大集团公司工作过的话,你一定会有机会接触到MySQL,虽然它并不支持事务处理,存储过程,但是它提供的功能一定能满足你的大部分需求,另外,简洁的MySQL也有一些它独到的优势,在有些时候,它的速度甚至超过大型数据库。

那么如何在.NET中访问MySQL数据库呢?也许很多人马上会说:用OLEDB嘛,但是事实上采用.NET OleDb Data Provider并不能访问MySQL,如果你使用的话,系统会提示你:"Net Data OLE DB 提供程序 (System.Data.Odbc) 不支持 MSDASQL 提供程序(用于 Odbc 驱动程序的 Microsoft OLE DB 提供程序)。",是什么原因我并不知道,按照MySQLDriverCS的作者的说法就是它被"abandoned by the owner",呵呵,兴许还有些故事。

幸好,我们还有其它的选择,这里就要介绍两种访问MySQL数据库的办法。

使用ODBC.NET

ODBC.NET(全称ODBC .NET Data Provider)是一个免费的.NET Framework附加组件,需要到微软公司的网站上去下载,下载地址为:http://download.microsoft.com/download/dasdk/Install/1.0.4030.0/W98NT42KMeXP/EN-US/odbc_net.msi,它需要系统已经安装MDAC 2.7或者更高版本。另外,还需要安装MySQL的ODBC驱动程序,下载地址为:http://www.mysql.com/downloads/api-myodbc-2.50.html,还需要在"ODBC数据源管理器"中配置一下DSN,如下图所示:

看原大图

在对象的设计上,ODBC.NET也跟OLEDB,SQL等一样,分别为OdbcConnection, OdbcCommand, OdbcDataAdapter, OdbcDataReader,用法也完全一样,如果你希望用ODBC .NET来代替以前的OleDb .NET Data Provider,事实上完全可以通过查找替换的办法来修改你的程序。

以下是一段代码示例:

try
{
string constr = "DSN=MySQL;" + "UID=;" +"PWD="; ;
conn = new OdbcConnection(constr);
conn.Open();
string query = "insert into test.dbtable values10,‘disksidkfsdi‘, ‘asdfaf‘, ‘adsfasdf‘)";
string tmp = null;
OdbcCommand cmd = new OdbcCommand(query, conn);
for(int i = 0; i < 100000; i++)
{
cmd.ExecuteNonQuery();
}
cmd.Dispose();
conn.Close();
query = "select * from test.dbtable";
OdbcCommand cmd2 = newOdbcCommand(query, conn);
conn.Open();
OdbcDataReader reader = cmd2.ExecuteReader();
while(reader.Read())
{
tmp = reader[0].ToString();
tmp = reader[1].ToString();
tmp = reader[2].ToString();
tmp = reader[3].ToString();
}
conn.Close();
query = "delete from test.dbtable";
OdbcCommand cmd3 = newOdbcCommand(query, conn);
conn.Open();
cmd3.ExecuteNonQuery();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
只要是用C#写过数据库应用的人一定能知道,上面的代码执行了十万次插入数据和读取数据,最后将数据记录全部删除的操作。

使用MySQLDriverCS

可能大部分的人都不知道这个东西,MySQLDriverCS是MySQL数据库的一个免费开源的.NET驱动程序。和Sql .NET Data Provider是为Sql Server一样,它是专门为MySQL设计的,可以叫做MySQL .NET Data Provider。使用他不需要额外的去设置ODBC数据源,基本上只要能连接到MySQL就能通过MySQLDriverCS来访问。

MySQLDriverCS是SourceForge.NET上的一个项目,不过不知道什么原因,这个网站在国内访问不到。

下面是使用MySQLDriverCS的代码示例:

MySQLConnection conn = null;
try
{
string connstr = "Data Source=MySQL;Password=root;User ID=root;Location=localhost";
conn = new MySQLConnection(constr);
conn.Open();
string query = "insert into test.dbtable values(10, ‘disksidkfsdi‘, ‘asdfaf‘, ‘adsfasdf‘)";
string tmp = null;
MySQLCommand cmd = new MySQLCommand(query, conn);
for(int i = 0; i < 100000; i++)
{
cmd.ExecuteNonQuery();
}
cmd.Dispose();
conn.Close();
query = "select * from test.dbtable";
MySQLCommand cmd2 = new MySQLCommand(query, conn);
conn.Open();
MySQLDataReader reader = cmd2.ExecuteReaderEx();
while(reader.Read())
{
tmp = reader[0].ToString();
tmp = reader[1].ToString();
tmp = reader[2].ToString();
tmp = reader[3].ToString();
}
conn.Close();
query = "delete from test.dbtable";
MySQLCommand cmd3 = new MySQLCommand(query, conn);
conn.Open();
cmd3.ExecuteNonQuery();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
和上面的那段代码几乎一模一样,所不同的是Odbc变成了MySQL,另外,需要注意的一点是Command的ExecuteReader方法在MySQLDriverCS中变成了ExecuteReaderEx,还有些细微的差别请参考附带的文档详细的介绍。

性能测试

有些读者其实已经看出来我以上写的那段代码的用意,对了,其实目的就是用来进行性能测试的。以上两段代码的执行时间分别是:ODBC.NET为 24秒左右,MySQLDriverCS为17秒左右。结果并不出人意外,作为MySQL的专用数据驱动程序,MySQLDriverCS的速度大大快于 ODBC.NET是在情理之中的。

总结

本文介绍了两种MySQL数据库访问的方法,同时对它们的性能做了一个简单的测试,希望能为各位读者在采用MySQL数据库开发.NET应用的时候提供一个有价值的参考。