Moving a MYSQL database from one machine to another

[xvszero]

Anyone know a simple way to do this? APPARENTLY it's not like Access where you can just cut and paste. I guess you need to do some sort of data dump? And then something with the inload file or whatever?

Confused.

What about rights on the databases, how does that change on a new machine with (presumibly) different MYSQL users set up?

[skywalker2208]

Mysql dump and reading it back into the database. (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html)

[xvszero]

Let me see if I understand this. If I just do something like...

mysqldump db_name > backup-file.sql

It's going to create an SQL file that contains code to create the database, create the tables, and insert (row by row?) all of my data into it? Where does it actually store this sql file?

Will it also keep all of the indexes and other such things?

And I'm still confused about how it handles rights, how do you specific which users have rights to the database when you load it onto the new machine?

[Phill Pafford]

A couple of questions:


What OS is your server (currently on and moving to)
version of MySQL
How do you have access (Remote, Local, web)


as for where the file is located, that's up to you

This is a command you would run on the command line. ( I think it works with Windows as well), also you might need to pass the user/password parameters as well. more info on this! (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html)

mysqldump db_name > /path/you/choose/backup-file.sql

If you have access other than through you host or phpMyAdmin, like you can use the MySQL GUI tools. You can use the MySQL Administrator app to run a backup of you DB's.

[xvszero]

Current

Server: Suse Linux 10.3
MySQL version: 5.0.26

Moving to

Server: Suse Linux 10.3
MySQL version: ??? (Probably the same since we used the same Suse 10.3 on all servers)

I'm not sure exactly what you mean by access. When I log into MYSQL I just use command line and a username and password our (now fired and gone) Linux guy set up for me. When PHP connects to my database it uses localhost as the dbhost and my username and password.

[Phill Pafford]

Cool so I assume your user has root access, my next question is do you connect to the DB on the same machine or do you remote into it from another machine?

If you can remote into it from another machine (Say windows), download the GUI tools MySQL provides for free and just use the MySQL Admin tool to do the backup.

Or

you can use the command line option as well.

mysqldump –-user your_user –-password=your_password --opt --all-databases > /path/to/file/all_databases.sql

to restore on another machine use this command

mysql -u your_user -pyour_password < /path/to/file/all_databases.sql

(No space for the passowrd and it's flag)

[xvszero]

I remote from my Windows desktop machine onto the server, which is on the same network.

I'm trying to learn this for more than just my specific situation at the moment though, so I may not always be able to install a GUI on any machine... I'd just prefer to do command line stuff as much as possible.

I just want the one specific database though, there are a ton of them on there and I don't need the others.

Hmm. On a sort of but not quite related note, I also have a personal website on Startlogic, do places like that generally let you make your own backups? I think they probably restrict it and try to charge you for them to make them for you. I know they require you to use their specific SQL window to access the database.

[Phill Pafford]

Well I think you should do a backup of all tables in the Db. this is just a safety step to make sure you don't lose anything if you need it later down the road. It's better to have it than to be sorry and have to start from scratch.

You could do a ALL DB backup and then do another backup with individual tables.


What about rights on the databases, how does that change on a new machine with (presumibly) different MYSQL users set up?


Also the MySQL users and permissions are stored in a MySQL table as well called mysql. I would recommend copying this over as well just to be on the safe side.

As for you personal hosting company you could use phpMyAdmin or something like this (http://www.webyog.com/blog/2008/09/30/manage-mysql-server-at-yahoo-small-business-and-other-shared-hosting-providers/).

[xvszero]

Yeah I want all the tables for the database, I just mean that I only need the one database moved to the new server. We already had some guy come in and set up nightly backups on all databases.

How would one copy over the MYSQL table?

[Phill Pafford]

mysqldump –-user your_user –-password=your_password --opt database_name > /path/to/file/one_table.sql

to restore on another machine use this command

mysql -u your_user -pyour_password < /path/to/file/one_table.sql

[xvszero]

Gah now I'm even more confused. What part of that is specifying that the mysql table with permissions be copied over?

Sorry, I'm slow.

[Phill Pafford]

Ok lets break it down


mysqldump –-user your_user –-password=your_password --opt your_table_name > /path/to/file/one_table.sql


mysqldump <-- the dump command for mysql
--user your_user <-- replace your_user with your user name that has the permission to access the table with root privilages
--password=your_password <-- replace your_password with your password assigned to the user you have specified in the user name field. Make sure there is no space before or after the = sign
--opt <-- Specifying the --opt argument when backing up our database should theoretically give us the fastest possible dump for reading back into MySQL server (the "opt" stands for optimize). When we specify the --opt argument, the mysqldump utility creates a more sophisticated set of dump commands, which includes the "DROP TABLE IF EXISTS" statement to delete the table from the database if it already exists when the dump file is being used to restore the database. The dump also includes several table locking statements.
your_table_name <-- this would be the table you would like to back up
> <-- this is the command symbol to pipe the output to a file
/path/to/file/ <-- this is a file location TBD by you /home/username/ for example
one_table.sql <-- you can name this anything with the .sql extension



What part of that is specifying that the mysql table with permissions be copied over?

What do you mean by this?

would this be the user accessing the MySQL table to do the dump or the new user who would need access to the new table on the new MySQL DB?


for maybe a more in depth description on MySQL Dump click Here (http://www.devarticles.com/c/a/MySQL/Backing-Up-Your-MySQL-Databases-With-MySQLDump/).

[xvszero]

You said this...


Also the MySQL users and permissions are stored in a MySQL table as well called mysql. I would recommend copying this over as well just to be on the safe side.

I'm just a bit confused on how I actually copy permissions over. And what would happen if I didn't is there some default if the database doesn't have its own permissions copied over?

[Phill Pafford]

Permissions in the MySQL DB are for users and what permissions they have for a given table. So if you need to preserve the current permissions for other users on the table you would like to copy over, I would recommend copying that DB.

If you just need to table and can assign new users to access the new DB, then I would just create them as needed on the new DB.

[xvszero]

I'm still slightly confused. Is this MYSQL permissions a seperate database that contains access rights for all databases on the machine, or a table in each database that contains access rights for just that database? My understanding is the first.

If so, basically I would just copy over two databases, the one I'm developing on and the MYSQL one with the permissions? Is the MYSQL one just named "MYSQL"? The new machine will be a clean build of Suse so no worries about overwriting, but what if I was copying to a machine that already had databases with their own permissions and such?

[Phill Pafford]

To answer your question MySQL has it's own database to keep track of users and their permissions. It has several tables in the database.

My question would be, "Why do you need to keep the old systems user permissions"?

If the users have to access the new database they will need to change their host name settings. so why not just issue a new user with new permissions at the same time?

Unless your talking about 100's of users.

Could you explain why a little more?

[xvszero]

I guess I don't really understand permissions, and someone else set them up before, so I want to make sure I can still access the database with all the code I have written.

[Phill Pafford]

So if it's just your user that needs to access the database, then it's no problem. You can just set up another user on the new database with the correct permissions to the table(s)/database(s). No need to copy over the MySQL table that holds the permissions for the old user.

If you had 100's of users then it would be a good investment to transfer over the data/permissions but if it's just you then it should be fine just to copy of the table.

MySQL has some great documentation on this from their site (http://dev.mysql.com/doc/refman/5.0/en/adding-users.html).

[xvszero]

But how do you determine what the "correct" permissions are? I have like... zero experience with permissions. And the guy who set them up before was fired, so I can't really ask him how he set it up.

[Phill Pafford]

I suggest you download MySQL on your local machine and play around with permission settings and creating new users. it's not that difficult. Looks like you just need some experience.

Look at MySQL site to further understand how to set permissions.

You could just use the root user but I would recommend against it.

[Phill Pafford]

They have some nice GUI tools as well that could be very helpful in testing you local machine

[xvszero]

I'd probably just set up the same username and password used to connect to my code, I just hope it still connects correctly.

GUIs hmm... I try to avoid them unless absolutely needed.