[SOLVED] Migrating databases larger than 8,192KiB?

colak · 2014-03-06 10:59:31

This is now solved and instructions can be found here

Time has come to move away from txd and I am migrating to Kaizen Garden

Could someone post an idiot friendly step by step guide on how we can import mysql databases larger than the max 8,192KiB stipulated in phpMyAdmin?

At the moment I have all the dbs in .sql format backed up in my computer.

Last edited by colak (2014-03-08 19:43:56)

juanjonavarro · 2014-03-06 11:39:37

Export the database in gzip format
Upload it to the new server, in directory tmp (create it if not exists)
ssh to your new server
Type: cd tmp
Type: gzip -d name_of_your_file.sql.gz
Type: mysql -h localhost -u username -p dbname < name_of_your_file.sql

Notes:

Replace “localhost” with mysql hostname if not localhost
Replace username with mysql username
Replace dbname with mysql database name
The command asks you for the password. This is the mysql password, not your ssh password
All this data is the same you put in your config.php file

lazyadmin · 2014-03-06 11:46:24

I like to use BigDump for tasks like these.

colak · 2014-03-06 12:13:07

Thanks so much guys. I’ll start with Juanjo’ suggestion and move on to bigdump if I’m stuck.

gaekwad · 2014-03-07 14:59:05

Yiannis – if you’re still using OS X, you could use SequelPro and avoid phpMyAdmin entirely. You might also get some info from this thread that might help.

colak · 2014-03-08 07:10:31

I have a problem at the moment as I don’t know the ssh connection addresses to KG

juanjonavarro · 2014-03-08 09:40:24

If your DNS is already pointing to KG, you can simply ssh your domain.

If not, you need to review your welcome message or ask your administrator.

colak · 2014-03-08 10:34:31

Hi, I changed all the name servers at my registrar’s yesterday and they point to KG but the sites are still in limbo returning blank pages. I guess I’ll wait a few more hours so as to allow for the change to happen before I start panicking.

gaekwad · 2014-03-08 11:20:54

Yiannis, I’m seeing www.neme.org now appearing on 23.105.43.31, but neme.org (no www) doesn’t resolve yet. www.neme.org is resolving with challenger.kaizengarden.us, so you’re nearly there. Perhaps check your DNS records to check the non-www address will resolve.

colak · 2014-03-08 17:17:23

Hey Pete

neme.org is now online but I still have problems with the forum subdomain at forum.neme.org.

What do you use to check the ips of sites? The whois I check my websites with do not return any ip addresses, just the name servers.

colak · 2014-03-08 19:04:40

Ok. Here’s what I did to migrate the large dbs from TXD to KG. I am posting it here should anybody need it.

Backing up in TXD.

step1. Connect via ftp and create a directory in the root of your account and call it backups
step2. In the command line (if you use a mac launch the Terminal app) type:

ssh accountname@server.textdrive.us

step3. you will be asked for the password
step4. when the server opens type

mysqldump -u accountname -p[mypassword] db_name > /users/home/accountname/backups/dbb.sql

step5. Compress your database as it will help with the download. In the command line type:

gzip -c /users/home/accountname/backups/dbb.sql >  /users/home/accountname/backups/dbb.sql.gz

step6. Go back to your ftp and download the compressed database from the backups folder

Migrating to KG

step1. Connect via ftp and create a folder called backup in the root of your account
step2. Upload your databases in .gz compression in the backup folder
step3. Connect to KG by typing

ssh username@server.kaizengarden.us

Type in your password when prompted.

step4. Un Zip your database online by typing

gunzip -c /users/home/accountname/backup/dbb.sql.gz > /users/home/accountname/backup/dbb.sql

step5. Type

mysql -u username -p[mypassword] db_name < /users/home/username/backup/dbb.sql

If all went well you would have all your database records in place.

In the directions above change:

server > server name
accountname> to your account name
[mypassword]> to your account password without the square brackets.
db_name> to your database name
dbb> to the name you want the backup to be

Last edited by colak (2014-03-08 20:09:28)

bici · 2014-03-08 19:29:44

colak wrote #279582:

[mypassword]> to your account password

db_name> to your database name

dbb> to the name you want the backup to be

thanks. Just to clarify if my password was secret would I enclose it in the [ ] i.e [secret] ?

colak · 2014-03-08 19:35:50

Hi bici, No the password should not be enclosed in the square brackets. if the password was secret the command would read

mysqldump -u accountname -psecret db_name > /users/home/accountname/backups/dbb.sql

the reason I enclosed it in the brackets was to emphasize the -p part which precedes it. I added this clarification to the original post.

Last edited by colak (2014-03-08 20:10:23)

gaekwad · 2014-03-09 09:35:54

colak wrote #279578:

What do you use to check the ips of sites? The whois I check my websites with do not return any ip addresses, just the name servers.

My computer here is OS X using Google Public DNS. Doing a dig on neme.org gives me this:

$ dig neme.org

; <<>> DiG 9.8.3-P1 <<>> neme.org
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 33992
;; flags: qr rd ra; QUERY: 1, ANSWER: 0, AUTHORITY: 1, ADDITIONAL: 0

;; QUESTION SECTION:
;neme.org.			IN	A

;; AUTHORITY SECTION:
neme.org.		1799	IN	SOA	dns1.kaizengarden.net. dns-admin.kaizengarden.net. 2014030810 3600 300 3600 3600

;; Query time: 4965 msec
;; SERVER: 10.1.1.1#53(10.1.1.1)
;; WHEN: Sun Mar  9 09:25:25 2014
;; MSG SIZE  rcvd: 93

I still don’t get a ping response:

$ ping neme.org
ping: cannot resolve neme.org: Unknown host

However, using the www.neme.org hostname gives me different results:

$ dig www.neme.org

; <<>> DiG 9.8.3-P1 <<>> www.neme.org
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 10506
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 0

;; QUESTION SECTION:
;www.neme.org.			IN	A

;; ANSWER SECTION:
www.neme.org.		21262	IN	A	23.105.43.31

;; Query time: 26 msec
;; SERVER: 10.1.1.1#53(10.1.1.1)
;; WHEN: Sun Mar  9 09:28:14 2014
;; MSG SIZE  rcvd: 46

And the ping:

$ ping www.neme.org
PING www.neme.org (23.105.43.31): 56 data bytes
Request timeout for icmp_seq 0
Request timeout for icmp_seq 1
Request timeout for icmp_seq 2
Request timeout for icmp_seq 3
^C
--- www.neme.org ping statistics ---
5 packets transmitted, 0 packets received, 100.0% packet loss

At least it knows where the site is – I’m presuming ping is disabled as the site seems to load fine in a browser. Regarding forum.neme.org:

$ dig forum.neme.org

; <<>> DiG 9.8.3-P1 <<>> forum.neme.org
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 46799
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 0

;; QUESTION SECTION:
;forum.neme.org.			IN	A

;; ANSWER SECTION:
forum.neme.org.		21599	IN	A	23.105.43.31

;; Query time: 2519 msec
;; SERVER: 10.1.1.1#53(10.1.1.1)
;; WHEN: Sun Mar  9 09:30:09 2014
;; MSG SIZE  rcvd: 48

Which is fine. Again, ping falls silent but does at least resolve:

$ ping forum.neme.org
PING forum.neme.org (23.105.43.31): 56 data bytes
Request timeout for icmp_seq 0
Request timeout for icmp_seq 1
Request timeout for icmp_seq 2
Request timeout for icmp_seq 3
^C
--- forum.neme.org ping statistics ---
5 packets transmitted, 0 packets received, 100.0% packet loss

It looks like the non-www prefix of neme.org isn’t playing nice, at least on my computer. I just tried to view your site (www and non-www) over lynx from a Media Temple server and neither seem to work, though that might be down to tardy DNS updates.

colak · 2014-03-09 09:51:20

Hi Pete it seems that the whole of KG is down

Textpattern CMS

Textpattern CMS support forum

#1 2014-03-06 10:59:31