I'm running CentOS-7, but I left some MySQL databases on my old CentOS-6.5 partition which I'd like to retrieve. I assume they are contained in the file /var/lib/mysql/ibdata1 ?
Could I just copy this file to /var/lib/mysql in CentOS-7? Or is there some way Mariadb or phpMyAdmin can import mysql databases from a server that is no longer running?
On 29/05/2015 04:52 AM, Timothy Murphy wrote:
I'm running CentOS-7, but I left some MySQL databases on my old CentOS-6.5 partition which I'd like to retrieve. I assume they are contained in the file /var/lib/mysql/ibdata1 ?
Could I just copy this file to /var/lib/mysql in CentOS-7? Or is there some way Mariadb or phpMyAdmin can import mysql databases from a server that is no longer running?
The C6 partition is part of the new server, so you are able to mount it and copy files from it, is this correct? Have you done something with the MariaDB or it's still clean installation?
Todor Petkov wrote:
I'm running CentOS-7, but I left some MySQL databases on my old CentOS-6.5 partition which I'd like to retrieve. I assume they are contained in the file /var/lib/mysql/ibdata1 ?
Could I just copy this file to /var/lib/mysql in CentOS-7? Or is there some way Mariadb or phpMyAdmin can import mysql databases from a server that is no longer running?
The C6 partition is part of the new server, so you are able to mount it and copy files from it, is this correct? Have you done something with the MariaDB or it's still clean installation?
I did add something to Mariadb on the new CentOS-7 system, but I don't mind deleting it and starting again. I can mount the partition with the old mysql files on it. Could I just copy the contents of /mnt/var/lib/mysql to the new system? There are files with the same name, eg ibdata1, on both systems. Could I have an ibdata2 ?
Any suggestions gratefully received.
On 29/05/2015 02:00 PM, Timothy Murphy wrote:
I did add something to Mariadb on the new CentOS-7 system, but I don't mind deleting it and starting again. I can mount the partition with the old mysql files on it. Could I just copy the contents of /mnt/var/lib/mysql to the new system? There are files with the same name, eg ibdata1, on both systems. Could I have an ibdata2 ?
Any suggestions gratefully received.
If you don't care about the new data, you can:
1) shut down MariaDB 2) rename /var/lib/mysql to /var/lib/mysql.orig 3) copy the old directory /var/lib/mysql - /mnt/var/lib/mysql in your case - do not forget the permissions! - to /var/lib/mysql 4) systemctl start mysql (or mysqld, can't remember) 5) run mysql_upgrade - this is a script, which changes the mysql.user etc tables with the new columns 6) There are some issues with PHP for example, when you have an user in mysql system tables, that has an old hash password, so you need to reenter the password to update the hash
I did it many times this way and everything went smoothly.
Good luck and don't forget to make some backups just in case;)
On 05/29/2015 07:00 AM, Timothy Murphy wrote:
Todor Petkov wrote:
I'm running CentOS-7, but I left some MySQL databases on my old CentOS-6.5 partition which I'd like to retrieve. I assume they are contained in the file /var/lib/mysql/ibdata1 ?
Could I just copy this file to /var/lib/mysql in CentOS-7? Or is there some way Mariadb or phpMyAdmin can import mysql databases from a server that is no longer running?
The C6 partition is part of the new server, so you are able to mount it and copy files from it, is this correct? Have you done something with the MariaDB or it's still clean installation?
I did add something to Mariadb on the new CentOS-7 system, but I don't mind deleting it and starting again. I can mount the partition with the old mysql files on it. Could I just copy the contents of /mnt/var/lib/mysql to the new system? There are files with the same name, eg ibdata1, on both systems. Could I have an ibdata2 ?
This is what I ran into trying to clone a web server on C7 (doing this from memory): There is something in the database file /var/lib/mysql that has to match something elsewhere on the machine. Apparently the match is created during the mariadb-server.rpm installation. I found two ways to transfer a /var/lib/mysql file successfully. 1. Transfer the file before installing mariadb-server.rpm or 2. After copying the file over the existing one, yum reinstall mariadb-server
Hope this helps, Ted Miller, Indiana, USA
------------ Original Message ------------
Date: Saturday, May 30, 2015 12:27:26 AM -0400 From: Ted Miller tedlists@sbcglobal.net
On 05/29/2015 07:00 AM, Timothy Murphy wrote:
Todor Petkov wrote:
I'm running CentOS-7, but I left some MySQL databases on my old CentOS-6.5 partition which I'd like to retrieve. I assume they are contained in the file /var/lib/mysql/ibdata1 ?
Could I just copy this file to /var/lib/mysql in CentOS-7? Or is there some way Mariadb or phpMyAdmin can import mysql databases from a server that is no longer running?
The C6 partition is part of the new server, so you are able to mount it and copy files from it, is this correct? Have you done something with the MariaDB or it's still clean installation?
I did add something to Mariadb on the new CentOS-7 system, but I don't mind deleting it and starting again. I can mount the partition with the old mysql files on it. Could I just copy the contents of /mnt/var/lib/mysql to the new system? There are files with the same name, eg ibdata1, on both systems. Could I have an ibdata2 ?
This is what I ran into trying to clone a web server on C7 (doing this from memory): There is something in the database file /var/lib/mysql that has to match something elsewhere on the machine. Apparently the match is created during the mariadb-server.rpm installation. I found two ways to transfer a /var/lib/mysql file successfully.
- Transfer the file before installing mariadb-server.rpm
or 2. After copying the file over the existing one, yum reinstall mariadb-server
Hope this helps, Ted Miller, Indiana, USA
The approach you can/need to take depends a bit on the type of db table you have. If it's a MyISAM type you can (generally) simply move the various db files from one machine to another. See:
https://dev.mysql.com/doc/refman/5.0/en/copying-databases.html
for some specific notes. I always move the whole directory.
If they are InnoDB tables, see the notes at:
https://dev.mysql.com/doc/refman/5.6/en/innodb-migration.html
for details.
Look at:
http://serverfault.com/questions/487159/what-is-the-ibdata1-file-in-my-var-lib-mysql-directory
for details on what's in the ibdata1 file.
You need to have mysql/mariadb shut down when you move the files.
- Richard
Richard wrote:
The approach you can/need to take depends a bit on the type of db table you have. If it's a MyISAM type you can (generally) simply move the various db files from one machine to another. See:
https://dev.mysql.com/doc/refman/5.0/en/copying-databases.html
for some specific notes. I always move the whole directory.
If they are InnoDB tables, see the notes at:
https://dev.mysql.com/doc/refman/5.6/en/innodb-migration.html
for details.
Look at:
http://serverfault.com/questions/487159/what-is-the-ibdata1-file-in-my-var-lib-mysql-directory
for details on what's in the ibdata1 file.
You need to have mysql/mariadb shut down when you move the files.
Thanks very much for your advice.
Actually I had already taken a rather longer path: I installed a MySQL server on my Fedora laptop, and transferred the directory /var/lib/mysql from the dead computer. This ran perfectly, so I could export the table in question, and install it on my server.
-
Richard -
Ted Miller -
Timothy Murphy -
Todor Petkov