CODEWITHSUNDEEP
mysqlphpmyadminwamp
chas
chas

Reputation: 1

how can I export 4.5 GB table from mysql?

I have a table which has 38.406.168 rows and according to size in phpmyadmin 4.5GB. I want to see the last row of the table. Unfortunately I couldn't use select * from ... limit 38.406.166,1 or even I couldn't use select count(*) from ... function.

I changed my.ini in wamp server, but still I get mysql server has gone away error while attempting execute one of these queries. BTW; I couldn't even set an index on ID to make these processes much quicker.

My last try was to export the table to look at the last row. However, It just shows me 123MB of the file.

What should I do? Please help me. The features of the computer is 2.93 GHz, 3.50GB Here is my my.ini file:

# The MySQL server
[wampmysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 2000M
table_cache = 4096
sort_buffer_size = 2000M
net_buffer_length = 8K
read_buffer_size = 2000M
read_rnd_buffer_size = 2000M
myisam_sort_buffer_size = 2000M
basedir=c:/wamp/bin/mysql/mysql5.1.36
log-error=c:/wamp/logs/mysql.log
datadir=c:/wamp/bin/mysql/mysql5.1.36/data
(.. these parts are deleted, since there is nothing to set as value)
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = C:\mysql\data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = C:\mysql\data/
#innodb_log_arch_dir = C:\mysql\data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 10M
#innodb_log_buffer_size = 64M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 180

[mysqldump]
quick
max_allowed_packet = 160M

Thank you so much for your help

Upvotes: 0

Views: 1523

Answers (2)

Sliq
Sliq

Reputation: 16494

I tried a lot of stuff and ended up with those 2 working:

  1. Simply mirror the database via mysql's internal master-slave-functions (try google, you ll find good tutorials) onto a simple backup server (most cheap hosting packages will work if they have ssh access)

  2. Try http://www.mysqldumper.net/, the best tool to copy & split huge databases into 100mb-parts. This simple open source tool did everything that "professional" backup scripts couldn't do.

Upvotes: 1

Nick Clark
Nick Clark

Reputation: 4467

You will want to use the mysqldump command to do this. Here is what I do in linux, but I think it will translate to Windows (I see that you're running WAMP).

mysqldump --opt --force -Q --user=[your_user] -p [database_name] > dump.sql

You may need to change directory to where the mysqldump file is located:

cd c:\path\to\mysql\bin

Upvotes: 0

Related Questions