[CentOS] Using Mariadb databases from old server

[Richard]

------------ Original Message ------------
> Date: Saturday, May 30, 2015 12:27:26 AM -0400
> From: Ted Miller <tedlists at 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.
> 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
> 

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