mysql

Discuss Applications
Post Reply
byrdman
administrator
administrator
Posts: 225
Joined: Thu May 08, 2003 1:59 pm
Location: In the cloud

mysql

Post by byrdman » Mon Dec 11, 2006 12:33 am

I am getting ready to move our website to a new server and need to make a backup and restore of our mysql database. I have googled and found several commands on how to do it, but I would like to know from the experts to do exactly what I need to do. I want to back up the current db with out deleting it and restore it to the new server exactly as it was...any takers? Thanks people!!

User avatar
Basher52
guru
guru
Posts: 923
Joined: Wed Oct 22, 2003 5:57 am
Location: .SE

Post by Basher52 » Mon Dec 11, 2006 2:54 am

this is what i do, dont know if its the correct thing to do, but it works
(i have tested to restore it)

mysqladmin -u root -pxxxx flush-tables
mysqldump -u root -pxxxx dbname > /tmp/db.sql

Hope Void can say if this is the best thing or if there is even better things to do :)

User avatar
Void Main
Site Admin
Site Admin
Posts: 5716
Joined: Wed Jan 08, 2003 5:24 am
Location: Tuxville, USA
Contact:

Post by Void Main » Mon Dec 11, 2006 4:19 am

Yeah, if you want to just backup one database I do this:

Code: Select all

mysqldump mydb --user=root --password=xxxx  | gzip -c > mydb.mysql.dmp.gz
Then after creating mydb on the new server I would restore the tables like this:

Code: Select all

zcat mydb.mysql.dmp.gz | mysql mydb --user=root --password=xxxx
To backup all databases I do this:

Code: Select all

mysqldump -A --user=root --password=xxxx | gzip -c > all.mysql.dmp.gz
Another thing you can do if you want an exact copy of what was on the old server you can just copy the /var/lib/mysql directory structure over (make sure permissions are kept), assuming the MySQL versions are the same.

User avatar
Basher52
guru
guru
Posts: 923
Joined: Wed Oct 22, 2003 5:57 am
Location: .SE

Post by Basher52 » Mon Dec 11, 2006 5:05 am

so to use the flush-tables is useless?
i read somewhere that you should do this just to be sure that all data are flushed from the cache, even though i think its very few time you need this, i thought... just in case, or?

User avatar
Void Main
Site Admin
Site Admin
Posts: 5716
Joined: Wed Jan 08, 2003 5:24 am
Location: Tuxville, USA
Contact:

Post by Void Main » Mon Dec 11, 2006 10:33 am

I didn't say it was useless but if you are going to do that you would probably also want to make sure you put a read lock on the tables you are backing up before flushing and then unlocking after the backup. It also depends on what type of tables you are using (myisam, inodb, etc) as to the "best" specific way to back them up. I have never had any problems using only the commands I mention on smaller, not heavily written to, mysql databases. At work we do have some larger MySQL databases (many GB) that are very heavily written to that require special backup options. There is a lot of information in the mysqldump man page:

http://voidmain.is-a-geek.net/man?param ... p&mode=man

User avatar
Basher52
guru
guru
Posts: 923
Joined: Wed Oct 22, 2003 5:57 am
Location: .SE

Post by Basher52 » Tue Dec 12, 2006 1:05 am

The db is a small db just for a webshop im trying to set up for my sisters husband and his small business, so the database wont be heavily written to.
I used phpmyadmin to check the database and it its a myisam db.
btw, what is the best type of database i should use, and when to use it?
or is this in the man page too :P i better read that again :D

User avatar
Void Main
Site Admin
Site Admin
Posts: 5716
Joined: Wed Jan 08, 2003 5:24 am
Location: Tuxville, USA
Contact:

Post by Void Main » Tue Dec 12, 2006 1:16 am

MyISAM is fine and the way you are backing up is fine. The only reason you might want to change anything is if you are running into performance problems and I can't imagine that you would on a small low utilized database as you describe.

User avatar
Basher52
guru
guru
Posts: 923
Joined: Wed Oct 22, 2003 5:57 am
Location: .SE

Post by Basher52 » Tue Dec 12, 2006 5:44 am

I was reading the man for mysqldump and found something I sure thought was used by the database.
in the description of --single-transaction I found this:
"It is useful only with transactional tables such as InnoDB and BDB"
does this mean that if I use MyISAM databases and has made an update with more than one table and that fails somewhere down the line, has the update made the tables inconsistent?
if so, have they fixed this in MySQL 5 or do I have to use other types of databases to get this function?
Last edited by Basher52 on Thu Dec 21, 2006 5:08 am, edited 1 time in total.

User avatar
Basher52
guru
guru
Posts: 923
Joined: Wed Oct 22, 2003 5:57 am
Location: .SE

Post by Basher52 » Thu Dec 21, 2006 5:08 am

Basher52 wrote:I was reading the man for mysqldump and found something I sure thought was used by the database.
in the description of --single-transaction I found this:
"It is useful only with transactional tables such as InnoDB and BDB"
does this mean that if I use MyISAM databases and has made an update with more than one table and that fails somewhere down the line, has the update made the tables inconsistent?
if so, have they fixed this in MySQL 5 or do I have to use other types of databases to get this function?
no one?

Post Reply