AJNS
asked on
Moving mysql to another server
I'm going to be moving an application that relies on a mysql server to a new server.
This is going to be a cut over so I need to know I can bring the new one up when I turn the old server off. I am really a newbie with databases and to be honest don't know what I'm doing with them but usually I can get them moved around.
I've been testing it and I can't seem to get the database moved. I used mysql to create a dump file. I then import the dump file but I can't connect to the database.
This is a windows machine and there is one service running called mysql. When I look at the mysql database there are 7 actual databases. Lets call them 1 through 7. In my app there is a property page asking for the datbase name (database 1) username and password and ODBC 32. I have been creating a dump of database 1.
On the new server I install mysql (here is one of the parts I'm not sure about) and at the end of the install it wants to run a configuration wizard. Should I run that? I have tried it with and without. Once that is done I run the import of the sql dump.
I then install the mysql odbc connector and setup the connection. I log into the app, set the properties on the database page and all seems good. Until I try to use the app to access the database. At that point I get db errors.
If there is a better way to do this I would appreciate any help with the steps. I'm not even sure the above is the way to go. And detailed steps would be great, don't assume I know anything LOL
This is going to be a cut over so I need to know I can bring the new one up when I turn the old server off. I am really a newbie with databases and to be honest don't know what I'm doing with them but usually I can get them moved around.
I've been testing it and I can't seem to get the database moved. I used mysql to create a dump file. I then import the dump file but I can't connect to the database.
This is a windows machine and there is one service running called mysql. When I look at the mysql database there are 7 actual databases. Lets call them 1 through 7. In my app there is a property page asking for the datbase name (database 1) username and password and ODBC 32. I have been creating a dump of database 1.
On the new server I install mysql (here is one of the parts I'm not sure about) and at the end of the install it wants to run a configuration wizard. Should I run that? I have tried it with and without. Once that is done I run the import of the sql dump.
I then install the mysql odbc connector and setup the connection. I log into the app, set the properties on the database page and all seems good. Until I try to use the app to access the database. At that point I get db errors.
If there is a better way to do this I would appreciate any help with the steps. I'm not even sure the above is the way to go. And detailed steps would be great, don't assume I know anything LOL
ASKER
I better come clean. I didn't create the dump. Another employee created the dumps and I don't know what what he used. I didn't know it mattered.
I was only using those dumps because they were easy to access from a usb hard drive without needing to bother the production server.
So I guess there is a blank slate and we can start right from creating the sql dump. I would not mind bringing over all the databases. This app doesn't use all of them but it wouldn't be bad to have them. And if that makes the new server as close to an exact copy of the first then great.
I was only using those dumps because they were easy to access from a usb hard drive without needing to bother the production server.
So I guess there is a blank slate and we can start right from creating the sql dump. I would not mind bringing over all the databases. This app doesn't use all of them but it wouldn't be bad to have them. And if that makes the new server as close to an exact copy of the first then great.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Ok I'll try that for the dumps.
What is the best way to import the dumps. And when I install mysql on the new server what's the best process? Do I just install it or do I create a database to put the dump in?
What is the best way to import the dumps. And when I install mysql on the new server what's the best process? Do I just install it or do I create a database to put the dump in?
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Ok so I made the dump file and that was fine.
Then I imported it using your command. It was telling me there was no such database. So I used the add database; command to create a database called "database1". Then I ran your command mysql -u root - p database1 < D:\database1dump.sql It worked fine.
The database is over 400mb's so it took a while. When I when to the bin and data folders where mysql is installed I expected to find files for the database. There is nothing there, just he test and mysql default databases.
Shouldn't there have been files for the database there? If I go back to the command prompt and login into mysql as root and run show databases; it shows the database but where is it? It spend quite a lot of time copying so I assumed I would see something.
Thanks for the patience.
Then I imported it using your command. It was telling me there was no such database. So I used the add database; command to create a database called "database1". Then I ran your command mysql -u root - p database1 < D:\database1dump.sql It worked fine.
The database is over 400mb's so it took a while. When I when to the bin and data folders where mysql is installed I expected to find files for the database. There is nothing there, just he test and mysql default databases.
Shouldn't there have been files for the database there? If I go back to the command prompt and login into mysql as root and run show databases; it shows the database but where is it? It spend quite a lot of time copying so I assumed I would see something.
Thanks for the patience.
ASKER
So I installed my app, created the ODBC connection (it doesn't like the mysql connection) and voila, I can see the data. I'm really confused about where mysql put the data I imported but clearly it imported it.
It's working which is great but could you help me understand this part of it.
It's working which is great but could you help me understand this part of it.
If there is no "datadir" entry in your my.cnf config file, MySQL will have put the data
in a default location.
I am able to issue this command on our MySQL server as root user to display the location:
show variables like 'datadir';
in a default location.
I am able to issue this command on our MySQL server as root user to display the location:
show variables like 'datadir';
ASKER
Your commands worked like a charm, thanks so much.
Lets assume you told it to dump all databases. In such a case it would have
dumped the MYSQL database.
After you import the database, it will import the MYSQL database which controls
all the accounting information. The password for the root account "root" will be
changed to the same password as it has on the server you migrated it from.