Home ›
Backing up, restoring, making test databases with MySQL command lineBacking up, restoring, making test databases with MySQL command line
Submitted by Benjamin Melançon on July 24, 2007 - 7:22pm
increase phpmyadmin upload size limit
forget it.
mysql from command line
mysqldump -u internetbar_live -p
--ignore-table=name
worked- (few misstarts, didn't like having a password included, placement of db name)
mysqldump -u internetbar_live -p --ignore-table=internetbar_live.ibcdru_watchdog internetbar_live > internetbar_backup.sql
One line. One goddamn line. And you can get the password you need from settings.php
mysqldump -u anderson_live -p --ignore-table=anderson_live.cdm_org_watchdog anderson_live > anderson_backup.sql
That's all one line.
Even faster:
mysqldump -u dbuser -pThePasswordString dbname > dbbackup.sql
Note that that's a -p followed by the password, without a space.
Question: Is it possible to choose structure only for certain tables while making a MySQL dump?
(Note: if you get a 1045 access denied message, check if your password has weird characters.)
for extra safety, get it off the server:
Ebony-697:~/Documents/Web Sites Data/COA News & FSTV/media reform job ben$ scp ben@66.135.37.243:/var/www/drupal-5-live/sites/democraticmedia.ca/anderson_backup.sql anderson_backup.sql
mysql -u [username] -p [password] [database_to_restore] < [backupfile]
mysql -u internetbar_test -p internetbar_test < internetbar_backup.sql
Afterward, of course, you have to do this:
CREATE TABLE agaricdesign_com_ibcdru_watchdog (
wid int(11) NOT NULL auto_increment,
uid int(11) NOT NULL default '0',
type varchar(16) NOT NULL default '',
message longtext NOT NULL,
severity tinyint(3) unsigned NOT NULL default '0',
link varchar(255) NOT NULL default '',
location text NOT NULL,
referer varchar(128) NOT NULL default '',
hostname varchar(128) NOT NULL default '',
timestamp int(11) NOT NULL default '0',
PRIMARY KEY (wid),
KEY type (type)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=860656 ;
and this will increase as we exclude content from more tables (cache, )
and this must be automated
along with all steps for copying a database.
mysqldump -u anderson_live -p --ignore-table=anderson_live.cdm_org_watchdog anderson_live > anderson_backup.sql
That's all one line.
Even faster:
mysqldump -u dbuser -pThePasswordString dbname > dbbackup.sql
Note that that's a -p followed by the password, without a space.
Question: Is it possible to choose structure only for certain tables while making a MySQL dump?
(Note: if you get a 1045 access denied message, check if your password has weird characters.)
for extra safety, get it off the server:
Ebony-697:~/Documents/Web Sites Data/COA News & FSTV/media reform job ben$ scp ben@66.135.37.243:/var/www/drupal-5-live/sites/democraticmedia.ca/anderson_backup.sql anderson_backup.sql
mysql -u [username] -p [password] [database_to_restore] < [backupfile]
mysql -u internetbar_test -p internetbar_test < internetbar_backup.sql
Afterward, of course, you have to do this:
CREATE TABLE `agaricdesign_com_ibcdru_watchdog` (
`wid` int(11) NOT NULL auto_increment,
`uid` int(11) NOT NULL default '0',
`type` varchar(16) NOT NULL default '',
`message` longtext NOT NULL,
`severity` tinyint(3) unsigned NOT NULL default '0',
`link` varchar(255) NOT NULL default '',
`location` text NOT NULL,
`referer` varchar(128) NOT NULL default '',
`hostname` varchar(128) NOT NULL default '',
`timestamp` int(11) NOT NULL default '0',
PRIMARY KEY (`wid`),
KEY `type` (`type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=860656 ;
and this will increase as we exclude content from more tables (cache, )
and this *must be automated*
along with all steps for copying a database. 
Comments
Might as well post why phpMyAdmin is not an option
One of the errors that forced me from PHPMyAdmin, even after laboriously cutting down the file to fit the silly upload limit...
Post new comment