mysql

[byrdman]

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!!

[Basher52]

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

[Void Main]

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.

[Basher52]

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?

[Void Main]

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

[Basher52]

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 i better read that again

[Void Main]

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.

[Basher52]

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?

[Basher52]

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?