mysql数据库恢复 覆盖吗_MySQL数据库恢复

现在最重要的是如何将原来的数据恢复出来? WEB和FTP比较容易解决，直接将硬盘作为从盘挂到其它的机子上，将数据copy出来就可以了。

SVN也比较方面， 使用“svnadmin dump”和“svnadmin reload”

MySQL的数据如何恢复？首先要找到mysql数据库的位置。我原来使用的是Fedora 8。看看是不是在/var/lib/mysql/mysql下面。

将原来数据库文件copy出来，然后编辑 /etc/my.cnf, 修改mysql数据库目录，试试看看行不行？

将/var/lib/mysql/打包，放到Fedora 15下面，mysql版本是“mysql  Ver 14.14 Distrib 5.1.47“

运行/sbin/service msyqld start, 有错误：

110725 09:40:32 mysqld_safe Starting mysqld daemon with databases from /home/frank/Templates/mysql

/usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist

110725  9:40:32 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

110725  9:40:32  InnoDB: Started; log sequence number 0 43665

110725  9:40:32 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'host' is read only

110725 09:40:32 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

将myql下BugFree目录copy到/var/lib/mysql下面，运行mysqld

mysql> use BugFree;

Database changed

mysql> show tables;

ERROR 1018 (HY000): Can't read dir of './BugFree/' (errno: 13)

看来这个方法也不行。

使用"mysql_install_db":

[root@aladdin log]# mysql_install_db --user=mysql --datadir=/home/frank/Templates/mysql

Installing MySQL system tables...

ERROR: 1  Can't create/write to file '/home/frank/Templates/mysql/mysql/plugin.MYI' (Errcode: 13)

110725 10:34:19 [ERROR] Aborting

110725 10:34:19 [Note] /usr/libexec/mysqld: Shutdown complete

Installation of system tables failed!  Examine the logs in

/home/frank/Templates/mysql for more information.

You can try to start the mysqld daemon with:

shell> /usr/libexec/mysqld --skip-grant &

and use the command line tool /usr/bin/mysql

to connect to the mysql database and look at the grant tables:

shell> /usr/bin/mysql -u root mysql

mysql> show tables

Try 'mysqld --help' if you have problems with paths.  Using --log

gives you a log in /home/frank/Templates/mysql that may be helpful.

Please consult the MySQL manual section

'Problems running mysql_install_db', and the manual section that

describes problems on your OS.  Another information source are the

MySQL email archives available at http://lists.mysql.com/.

Please check all of the above before mailing us!  And remember, if

you do mail us, you MUST use the /usr/bin/mysqlbug script!

[root@aladdin log]#

更换目录，再试一次

[root@aladdin mysql]# mysql_install_db --user=mysql --datadir=/opt/mysql_maya/mysql/

Installing MySQL system tables...

OK

Filling help tables...

OK

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'

/usr/bin/mysqladmin -u root -h aladdin.leadtek.com.cn password 'new-password'

Alternatively you can run:

/usr/bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default.  This is

strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

启动mysql还是失败，查看log:

110725 10:46:55 mysqld_safe Starting mysqld daemon with databases from /opt/mysql_maya

110725 10:46:55  InnoDB: Operating system error number 13 in a file operation.

InnoDB: The error means mysqld does not have the access rights to

InnoDB: the directory.

InnoDB: File name ./ibdata1

InnoDB: File operation call: 'open'.

InnoDB: Cannot continue operation.

110725 10:46:55 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

似乎是权限问题

更改权限

chown mysql:mysql -R mysql_maya/

然后启动mysql

[root@aladdin opt]# /sbin/service mysqld restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

再验证一下mysql中的数据

[root@aladdin opt]# mysql --socket=/opt/mysql_maya/mysql.sock -u root -p

成功连接

下面将Bugfree中的数据备份出来

备份mysql数据库数据

[root@aladdin opt]# mysqldump --socket=/opt/mysql_maya/mysql.sock -u root -pxxxxxx --opt BugFree > bugfree.sql

然后再将数据库导入到新的mysql服务器中。

最后再导入mysql的备份机制