Php文档 Php问答行业资讯 Php论坛 Php手册 Php博客

游戏榜单

软件榜单

关闭导航

热搜榜

热门下载

热门标签

关闭搜索

php爱好者> php文档>mysql的常用命令以及备份和恢复

mysql的常用命令以及备份和恢复

时间：2008-10-13 来源：hanyang016

mysql有几个图形管理软件很好用，比如mysql-front ，phpadmin等等，简单易用，这里就不做介绍了。另外eclipse有个数据库管理的插件叫，可以对各种常用的数据库进行图形管理，也很好用。

下面步入正题，介绍下sql常用操作：

创建库表和查询插入删除记录的操作很熟悉,就不作介绍了。这里主要说一下对用户和密码的操作，这些操作也很重要，尤其在刚开始使用数据库时很有用。

登录MYSQL
>mysql -u root -p
>密码

创建用户
mysql> mysql> insert into mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_sub
ject) values("localhost","fred",password("love"),'','','');

创建后的用户名为：fred 密码为：love

创建数据库(test)
mysql>create database test;
将test数据库的所有权限授权给用户fred
>grant all privileges on test.* to fred@localhost identified by 'love';
刷新系统权限表
mysql>flush privileges;

如果想指定部分权限给一用户，可以这样来写:
mysql>grant select,update on phplampDB.* to fred@localhost identified by 'love';
刷新系统权限表
mysql>flush privileges;

修改指定用户密码。
>mysql -u root -p
>密码
mysql>update mysql.user set password=password('新密码') where User="fred" and Host="localhost";
mysql>flush privileges;

删除用户。
>mysql -u root -p
>密码
mysql>DELETE FROM user WHERE User="fred" and Host="localhost";
mysql>flush privileges;
删除用户的数据库
mysql>drop database test;

其它一些有用的操作：

列出所有数据库

mysql>show database;

切换数据库

mysql>use '数据库名';

列出所有表

mysql>show tables;

显示数据表结构

mysql>describe 表名;

删除数据库和数据表

mysql>drop database 数据库名;
mysql>drop table 数据表名;

1.关于自增字段重新赋初值的问题？

ALTER TABLE tbl AUTO_INCREMENT = 1;

2.如何实现mysql中自增长字段的功能？

create table abc(id int(10) not null auto_incremnet primary key,

name varchar(10) not null,

address varchar(200) not null,

postcode char(6) not null

);

这样就创建了一个表，这个表的id子段是自动增长的。

你还可以在一建好的表中增加这样的字段，操作如下：

alter table tb_name add id int(10) not null auto_increment first;

或者

alter table tb_name add id int(10) not null auto_increment;

3、如何更改mysql中用户密码？

a、在mysql/bin/目录下面

./mysqladmin -u[用户名如：root] -p[旧密码，如果没有密码留空] password [新密码]

./mysqladmin -uroot -p123456 password 456789

其中用户名: root 原来密码: 123456 新密码: 456789

b、以root用户进入mysql

mysql> use mysql

mysql>update user set Password=password('newpassword') where User='root';

mysql>flush privileges;

注意大小写。

4、如何远程连接mysql

(1)进入mysql，创建一个新用户xuys：

格式：grant 权限 on 数据库名.表名用户@登录主机 identified by "用户密码";

grant select,update,insert,delete on *.* to [email protected]

identified by "xuys1234";

查看结果，执行：

use mysql;

select host,user,password from user;

可以看到在user表中已有刚才创建的xuys用户。host字段表示登录的主机，其值可以用IP，也可用主机名，将host字段的值改为%就表示在任何客户端机器上能以xuys用户登录到mysql服务器，建议在开发时设为%。

update user set host = '%' where user = 'xuys';

(2) mysqladmin -uroot -ppwd reload

mysqladmin -uroot -ppwd shutdown

(3)./mysqld_safe --user=root &

记住：对授权表的任何修改都需要重新reload，即执行第3步。

如果经过以上3个步骤还是无法从客户端连接，请执行以下操作，

在mysql数据库的db表中插入一条记录：

use mysql;

insert into db values

('192.168.88.234','%','xuys','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

update db set host = '%' where user = 'xuys';

重复执行上面的第2、3步。

MySQL数据库备份与恢复

首先建立一个计划并严格遵守，定期实施备份。

让服务器执行更新日志。当你在崩溃后需要恢复数据时，更新日志将帮助你。在你用备份文件恢复数据到备份时的状态后，你可以通过运行更新日志中的查询再次运用备份后面的修改，这将数据库中的表恢复到崩溃发生时的状态。
以文件系统备份的术语讲，数据库备份文件代表完全倾倒（full dump），而更新日志代表渐进倾倒（incremental dump）。
使用一种统一的和易理解的备份文件命名机制。象backup1、buckup2等不是特别有意义。当实施你的恢复时，你将浪费时间找出文件里是什么东西。你可能发觉用数据库名和日期构成备份文件名会很有用。
你可能想在生成备份后压缩它们。备份一般都很大！你也需要让你的备份文件有过期期限以避免它们填满你的磁盘，就象你让你的日志文件过期那样。
用文件系统备份你的备份文件。如果遇上了一个彻底崩溃，不仅清除了你的数据目录，也清除了包含你的数据库备份的磁盘驱动器，你将真正遇上了麻烦。也要备份你的更新日志。
将你的备份文件放在不同于用于你的数据库的文件系统上。这将降低由于生成备份而填满包含数据目录的文件系统的可能性。
用于创建备份的技术同样对拷贝数据库到另一台机器有用。最常见地，一个数据库被转移到了运行在另一台主机上的服务器，但是你也可以将数据转移到同一台主机上的另一个服务器。下面以数据库dzzw作为讲解备份恢复的实例：
一、.数据恢复
1.使用mysqldump备份数据库
当你使用mysqldump程序产生数据库备份文件时，缺省地，文件内容包含创建正在倾倒的表的CREATE语句和包含表中行数据的INSERT语句。换句话说，mysqldump产生的输出可在以后用作mysql的输入来重建数据库。
Mysqldump参数如下：
#mysqldump数据库名 [表名] -u 用户名-p '密码' > 操作系统下文件名

举例：

C:\>mysqldump dzzw -u root -p > d:\buckupdb\dzzw\dzzw2040728.sql
Enter password: ******
C;\>
这说明备份成功。

输出文件的开头看起来象这样：
-- MySQL dump 9.10
--
-- Host: localhost Database: dzzw
-- ------------------------------------------------------
-- Server version 4.0.18-nt

--
-- Table structure for table `a_catalog`
--

CREATE TABLE a_catalog (
catalog_id varchar(50) NOT NULL default '',
dossier_id varchar(50) NOT NULL default '',
parent_id varchar(50) default NULL,
name text NOT NULL,
brief text,
creater_id varchar(50) NOT NULL default '',
active char(1) NOT NULL default 'Y',
ts datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (catalog_id,dossier_id)
) TYPE=MyISAM;

--
-- Dumping data for table `a_catalog`
--

INSERT INTO a_catalog VALUES ('sdaf','sadf','sadf','asdf','asdf','000','Y','2004-05-25 09:02:34');
INSERT INTO a_catalog VALUES ('sdf','sadf','sadf','sdaf','asdf','000','Y','2004-05-25 09:11:40');
INSERT INTO a_catalog VALUES ('DQGXL001','xhq001','xhq001','党群关系类','好好好好！','001','Y','2004-07-15 13:59:02');
INSERT INTO a_catalog VALUES ('阿萨达萨','xhq001','xhq001','阿萨','阿阿萨阿萨达萨巴','001','N','2004-07-15 14:03:03');
INSERT INTO a_catalog VALUES ('阿洒洒洒','xhq001','DQGXL001','阿达','阿达阿萨','001','Y','2004-07-15 14:03:40');
……
......　
文件剩下的部分有更多的INSERT和CREATE TABLE语句组成。
输出单个的表：

C:\>mysqldump dzzw a_catalog -u root -p > d:\buckupdb\dzzw\a_catalog2040728.sql
Enter password: ******

C;\>
这说明备份成功。
输出文件的开头看起来象这样：
-- MySQL dump 9.10
--
-- Host: localhost Database: dzzw
-- ------------------------------------------------------
-- Server version 4.0.18-nt

--
-- Table structure for table `a_catalog`
--

--
-- Dumping data for table `a_catalog`
--

缺省地，mysqldump在写入前将一个表的整个内容读进内存。这通常确实不必要，并且实际上如果你有一个大表，几乎是失败的。你可用--quick选项告诉mysqldump只要它检索出一行就写出每一行。为了进一步优化倾倒过程，使用--opt而不是--quick。--opt选项打开其它选项，加速数据的倾倒和把它们读回。

C:\>mysqldump dzzw a_catalog --opt -u root -p > d:\buckupdb\dzzw\a_catalog204072
8.sql
Enter password: ******

C:\>
这说明备份成功。

输出文件的开头看起来象这样：
DROP TABLE IF EXISTS a_catalog;
CREATE TABLE a_catalog (
catalog_id varchar(50) NOT NULL default '',
dossier_id varchar(50) NOT NULL default '',
parent_id varchar(50) default NULL,
name text NOT NULL,
brief text,
creater_id varchar(50) NOT NULL default '',
active char(1) NOT NULL default 'Y',
ts datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (catalog_id,dossier_id)
) TYPE=MyISAM;

--
-- Dumping data for table `a_catalog`
--

/*!40000 ALTER TABLE a_catalog DISABLE KEYS */;
LOCK TABLES a_catalog WRITE;
INSERT INTO a_catalog VALUES ('sdaf','sadf','sadf','asdf','asdf','000','Y','2004-05-25 09:02:34'),('sdf','sadf','sadf','sdaf','asdf','000','Y','2004-05-25 09:11:40'),('DQGXL001','xhq001','xhq001','党群关系类','好好好好！','001','Y','2004-07-15 13:59:02'),('阿萨达萨','xhq001','xhq001','阿萨','阿阿萨阿萨达萨巴','001','N','2004-07-15 14:03:03'),('阿洒洒洒','xhq001','DQGXL001','阿达','阿达阿萨','001','Y','2004-07-15 14:03:40');
UNLOCK TABLES;
/*!40000 ALTER TABLE a_catalog ENABLE KEYS */;

2.使用直接拷贝数据库的备份方法
另一种不涉及mysqldump备份数据库和表的方式是直接拷贝数据库表文件。
关闭数据库，拷贝D:\mysql\data\dzzw 所有文件。当你完成了备份时，你可以重启服务器（如果关闭了它）或释放加在表上的锁定（如果你让服务器运行）。要用直接拷贝文件把一个数据库从一台机器拷贝到另一台机器上，只是将文件拷贝到另一台服务器主机的适当数据目录下即可。要确保文件是MyIASM格式或两台机器有相同的硬件结构，否则你的数据库在另一台主机上有奇怪的内容。你也应该保证在另一台机器上的服务器在你正在安装数据库表时不访问它们。

二、.数据恢复
数据库损坏的发生有很多原因，程度也不同。如果你走运，你可能仅损坏一两个表（如掉电），如果你倒霉，你可能必须替换整个数据目录（如磁盘损坏）。在某些情况下也需要恢复，比如用户错误地删除了数据库或表。
1.用mysqldump恢复整个数据库
Mysqldump参数如下：
#mysqldump -u 用户名-p'密码' 数据库名 < 操作系统下文件名

举例：
先在Mysql里创建数据库
mysql> create database dzzw;
Query OK, 1 row affected (0.05 sec)

然后将备份的数据导入
C:\>mysql -u root -p dzzw < d:\dzzw2040728.sql
Enter password: *****

进入mysql 建立用户hs
C:\>mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 4.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
mysql> use dzzw;
Database changed
mysql> grant select,insert,update,delete on dzzw.* to hs@localhost identified by
'hsoft';
Query OK, 0 rows affected (0.16 sec)

mysql> grant select,insert,update,delete on dzzw.* to hs identified by 'hsoft';
Query OK, 0 rows affected (0.00 sec)

这样就成功恢复了数据库dzzw;
2. 使用直接拷贝备份的数据库恢复整个数据库
先关闭mysql数据库，把备份的数据库dzzw，放到 d:\mysql\data\下就行，
这样就把数据库dzzw恢复了。然后启动mysql数据库，进入mysql 建立用户hs
C:\>mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 4.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
mysql> use dzzw;
Database changed
mysql> grant select,insert,update,delete on dzzw.* to hs@localhost identified by
'hsoft';
Query OK, 0 rows affected (0.16 sec)

mysql> grant select,insert,update,delete on dzzw.* to hs identified by 'hsoft';
Query OK, 0 rows affected (0.00 sec)

这样就成功恢复了数据库dzzw;

windows下的数据库备份脚本

d:\mysql\bin\mysqldump -uroot -pcreymm --all-databases > d:\mysql\%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%%time:~9,2%.sql

d:\mysql\bin\mysqldump -uroot -pcreymm --all-databases > d:\mysql\%date:~0,4%%date:~5,2%%date:~8,2%%.sql
把password替换为你的密码，然后存成backup.cmd 或者 backup.bat 就可以按日期存储备份了

可根据情况调整日期和时间，比如调整为只显示日期如下

d:\mysql\bin\mysqldump -uroot -ppassword paper > d:\mysql\%date:~0,4%%date:~5,2%%date:~8,2%%.sql

d:\mysql\bin\mysqlcheck --all-databases --auto-repair -uroot -pcreymm 把password替换为你的密码，然后存成repair.cmd 或者 repair.bat 就可以修复所有数据库了

mysqldump -uroot -p --all-databases > d:\%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%%time:~9,2%.sql

如果要用到IP地址，端口号的话

cd C:\Program Files\MySQL\MySQL Server 4.1\bin
mysqldump.exe -h127.0.0.1 -uroot -padmin!@# -P3307 -t plonesite >d:\plonesite-%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%%time:~9,2%.sql