Blog
轻松一下?

AppleStar

关于mysql大数据库的备份的一点体会

原贴地址:
http://wenew.com.ru/phpwind/read.php?tid=130974


我不知道怎么定义大数据库,我的数据库现有近8万个帖子,116M,不知道算不算大数据库。由于所用web空间不大,不可能用phpwind论坛的数据库备份程序。于是用cpanel的backup压缩下载了数据库,压缩包约45M。在本地,用winrar程序解压缩后,得到一个133M左右的文件,这是一个mysql语句的文本文件,phpwind程序的数据还原程序不能使用。我摸索着试用了两种方法,下面介绍一下:

一,用phpmyadmin程序导入

1.启动mysql服务程序,web服务程序,从浏览器进入phpmyadmin,先择数据库,然后再选择SQL,即在此数据库中运行 SQL 查询,“或文本文件位置”,“浏览...”中选择解开的数据库文本文件,开始执行,结果不成功。

2.
(1)调整c:\winddows\php.ini的参数,
程序最多允许使用内存量 memory_limit 200M
POST最大字节数 post_max_size 200M
允许最大上传文件 upload_max_filesize 200M
程序最长运行时间 max_execution_time 3000000秒

(2)调整phpmyadmin的根目录下的config.inc.php的参数
$cfg['Servers'][$i]['compress']     = TRUE;     // Use compressed protocol for the MySQL connection
                                  // (requires PHP >= 4.3.0)
$cfg['PersistentConnections']   = TRUE; // use persistent connections to MySQL database
$cfg['IgnoreMultiSubmitErrors'] = TRUE; // if set to true, PMA continues computing multiple-statement queries

(3)再次按“1”的方法运行,仍不成功。

(4)用filesplit将分割成约每块3M,用文件编辑器打开第一个分割文件,看最后一个sql语句是否被切断,如果被切断了,从第二个被分割的文件前部分剪出那条语句的余下部分,补贴在这条语句以形成一条完整的sql语句文本文件。如些类推,整理好分割文件。再用“1”的方法导入,成功。

(5)存在的问题:
(一),导入速度很慢,原因可能是,又是需要web服务器,又是浏览器,又是php运行,又是mysql服务程序。
(二),用filesplit分割文件时,将双字节的汉字切开,结果有些帖子成了乱码,只是这个帖子中的一部分。

 

二,在dos模式下用mysql.exe导入

1.停止所有web服务,关闭浏览器,进入dos窗口,到mysql程序的目录的子目录bin下,如c:>mysql>bin>输入命令,如下:

c:>mysql>bin>mysql --user=你的mysql用户名 --password=你的mysql密码 --database=你要导入的数据库名

(没有请先建数据库,其实在mysql的data目录中建个文件夹就行)

回车,进入到mysql.

2.出现的提示符为

mysql>

输入命令

mysql>source 包含sql命令的数据库文本文件

以上是文本文件在当前目录(bin)中的情况,也可以写出路径。

回车,开始逐条导入。

我的机子性能差,数据库导入花了1个多小时,但完全成功了。恢复成功后,此数据库所占目录为116M。


三,一点说明

1.如果能直接操作远程服务器上的mysql数据库目录,可以停止mysql服务后,直接下载或压缩下载整个数据所在的目录,到本地后解开即可。在本地使用时,用户名,密码当然要保持一致。

2.我的配置是winme操作系统,web服务器是pws4.0,phpmyadmin2.56,硬件配置也很低。我能成功在本地恢复mysql数据库,大家应该都没问题。方法可能对windows系统的mysql数据库备份与恢复有点帮助。

四,附录
(-)恢复数据时用的MYSQL.EXE的参数

C:\MYSQL\BIN\MYSQL.EXE

Ver 11.16 Distrib 4.0.0-alpha, for Win95/Win98 (i32)
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Usage: C:\MYSQL\BIN\MYSQL.EXE [OPTIONS] [database]

-?, --help         Display this help and exit.
-A, --no-auto-rehash No automatic rehashing. One has to use 'rehash' to
              get table and field completion. This gives a quicker
              start of mysql and disables rehashing on reconnect.
-B, --batch         Print results with a tab as separator, each row on
              a new line. Doesn't use history file.
--character-sets-dir=...
                Directory where character sets are located.
-C, --compress     Use compression in server/client protocol.
-D, --database=..     Database to use.
--default-character-set=...
                Set the default character set.
-e, --execute=...   Execute command and quit. (Output like with --batch)
-E, --vertical     Print the output of a query (rows) vertically.
-f, --force       Continue even if we get an sql error.
-g, --no-named-commands
              Named commands are disabled. Use \* form only, or
                use named commands only in the beginning of a line
                ending with a semicolon (;) Since version 10.9 the
                client now starts with this option ENABLED by
                default! Disable with '-G'. Long format commands
                still work from the first line.
-G, --enable-named-commands
                Named commands are enabled. Opposite to -g.
-i, --ignore-space     Ignore space after names.
-h, --host=...     Connect to host.
-H, --html         Produce HTML output.
-X, --xml         Produce XML output.
-L, --skip-line-numbers
                Don't write line number for errors.
--no-tee         Disable outfile. See interactive help (\h) also.
-n, --unbuffered     Flush buffer after each query.
-N, --skip-column-names
                Don't write column names in results.
-O, --set-variable var=option
              Give a variable an . --help lists variables.
-o, --one-database     Only update the default database. This is useful
              for skipping updates to other database in the update
              log.
-p[password], --password[=...]
              Password to use when connecting to server
              If password is not given it's asked from the tty.
-W, --pipe         Use named pipes to connect to server

-P, --port=...     Port number to use for connection.
-q, --quick         Don't cache result, print it row by row. This may
              slow down the server if the output is suspended.
              Doesn't use history file.
-r, --raw         Write fields without conversion. Used with --batch
-s, --silent         Be more silent.
-S --socket=...     Socket file to use for connection.
-t, --table         Output in table format.
-T, --debug-info     Print some debug info at exit.
--tee=...         Append everything into outfile. See interactive help
                (\h) also. Does not work in batch mode.
-u, --user=#         User for login if not current user.
-U, --safe-updates[=#], --i-am-a-dummy[=#]
              Only allow UPDATE and DELETE that uses keys.
-v, --verbose         Write more. (-v -v -v gives the table output format)
-V, --version         Output version information and exit.
-w, --wait         Wait and retry if connection is down.

Default options are read from the following files in the given order:
C:\WINDOWS\my.ini C:\my.cnf
The following groups are read: mysql client
The following options may be given as the first argument:
--print-defaults     Print the program argument list and exit
--no-defaults         Don't read default options from any options file
--defaults-file=#     Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read

Possible variables for option --set-variable (-O) are:
connect_timeout     current : 0
max_allowed_packet   current : 16777216
net_buffer_length   current : 16384
select_limit       current : 1000
max_join_size       current : 1000000

 

(二)备份数据库时用的MYSQLDUMP.EXE的参数

C:\MYSQL\BIN\MYSQLDUMP.EXE

Ver 8.17 Distrib 4.0.0-alpha, for Win95/Win98 (i32)
By Igor Romanenko, Monty, Jani & Sinisa
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Dumping definition and data mysql database or table
Usage: C:\MYSQL\BIN\MYSQLDUMP.EXE [OPTIONS] database [tables]
OR   C:\MYSQL\BIN\MYSQLDUMP.EXE [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR   C:\MYSQL\BIN\MYSQLDUMP.EXE [OPTIONS] --all-databases [OPTIONS]

-A, --all-databases   Dump all the databases. This will be same as
              --databases with all databases selected.
-a, --all         Include all MySQL specific create options.
-#, --debug=...     Output debug log. Often this is 'd:t:o,filename`.
--character-sets-dir=...
                Directory where character sets are
-?, --help         Display this help message and exit.
-B, --databases     To dump several databases. Note the difference in
              usage; In this case no tables are given. All name
              arguments are regarded as databasenames.
              'USE db_name;' will be included in the output
-c, --complete-insert Use complete insert statements.
-C, --compress     Use compression in server/client protocol.
--default-character-set=...
                Set the default character set
-e, --extended-insert Allows utilization of the new, much faster
                INSERT syntax.
--add-drop-table     Add a 'drop table' before each create.
--add-locks         Add locks around insert statements.
--allow-keywords     Allow creation of column names that are keywords.
--delayed-insert     Insert rows with INSERT DELAYED.
--master-data       This will cause the master position and filename to
                be appended to your output. This will automagically
                enable --first-slave.
-F, --flush-logs     Flush logs file in server before starting dump.
-f, --force         Continue even if we get an sql-error.
-h, --host=...     Connect to host.
-l, --lock-tables   Lock all tables for read.
--no-autocommit     Wrap tables with autocommit/commit statements.
-n, --no-create-db   'CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;'
                will not be put in the output. The above line will
                be added otherwise, if --databases or
                --all-databases option was given.
-t, --no-create-info     Don't write table creation info.
-d, --no-data         No row information.
-O, --set-variable var=option
                give a variable a . --help lists variables
--opt               Same as --add-drop-table --add-locks --all
                --extended-insert --quick --lock-tables
-p, --password[=...]     Password to use when connecting to server.
                If password is not given it's solicited on the tty.

-W, --pipe         Use named pipes to connect to server
-P, --port=...     Port number to use for connection.
-q, --quick         Don't buffer query, dump directly to stdout.
-Q, --quote-names     Quote table and column names with `
-r, --result-file=... Direct output to a given file. This option should be
                used in MSDOS, because it prevents new line '\n'
                from being converted to '\n\r' (newline + carriage
                return).
-S, --socket=...     Socket file to use for connection.
--tables         Overrides option --databases (-B).
-T, --tab=...       Creates tab separated textfile for each table to
                given path. (creates .sql and .txt files).
                NOTE: This only works if mysqldump is run on
                    the same machine as the mysqld daemon.
-u, --user=#         User for login if not current user.
-v, --verbose         Print info about the various stages.
-V, --version         Output version information and exit.
-w, --where=         dump only selected records; QUOTES mandatory!
-x, --first-slave   Locks all tables across all databases.
EXAMPLES: "--where=user='jimf'" "-wuserid>1" "-wuserid<1"
Use -T (--tab=...) with --fields-...
--fields-terminated-by=...
                Fields in the textfile are terminated by ...
--fields-enclosed-by=...
                Fields in the importfile are enclosed by ...
--fields-optionally-enclosed-by=...
                Fields in the i.file are opt. enclosed by ...
--fields-escaped-by=...
                Fields in the i.file are escaped by ...
--lines-terminated-by=...
                Lines in the i.file are terminated by ...

Default options are read from the following files in the given order:
C:\WINDOWS\my.ini C:\my.cnf
The following groups are read: mysqldump client
The following options may be given as the first argument:
--print-defaults     Print the program argument list and exit
--no-defaults         Don't read default options from any options file
--defaults-file=#     Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read

Possible variables for option --set-variable (-O) are:
max_allowed_packet   current : 25165824
net_buffer_length   current : 1047551 

提交评论


安全码
刷新