[opensuse] Q: Copying /var/lib/mysql to another PC
Hi ! I have an old accounting system running on SuSE 42.x and MySQL 5.6. Can I just copy all mysql files from /etc and /var/lib/mysql to another PC with Leap 15 and MySQL 5.6 ? This MySQL DB have a truckload of tables and developer of the software doesn't offer much help right now. Thanks in advance. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
* Andrei Verovski
Hi !
I have an old accounting system running on SuSE 42.x and MySQL 5.6.
Can I just copy all mysql files from /etc and /var/lib/mysql to another PC with Leap 15 and MySQL 5.6 ?
This MySQL DB have a truckload of tables and developer of the software doesn't offer much help right now.
I have not done it and am defnitely not knowledgable re mysql, but: https://www.digitalocean.com/community/tutorials/how-to-migrate-a-mysql-data... google is quite helpful. appears quite straight-forward and really not complicated -- (paka)Patrick Shanahan Plainfield, Indiana, USA @ptilopteri http://en.opensuse.org openSUSE Community Member facebook/ptilopteri Registered Linux User #207535 @ http://linuxcounter.net Photos: http://wahoo.no-ip.org/piwigo paka @ IRCnet freenode -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Le 05/12/2018 à 22:22, Andrei Verovski a écrit :
Hi !
I have an old accounting system running on SuSE 42.x and MySQL 5.6.
Can I just copy all mysql files from /etc and /var/lib/mysql to another PC with Leap 15 and MySQL 5.6 ?
This MySQL DB have a truckload of tables and developer of the software doesn't offer much help right now.
Thanks in advance.
try it it often works jdd -- http://dodin.org -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
On 12/5/2018 3:22 PM, Andrei Verovski wrote:
Hi !
I have an old accounting system running on SuSE 42.x and MySQL 5.6.
Can I just copy all mysql files from /etc and /var/lib/mysql to another PC with Leap 15 and MySQL 5.6 ?
This MySQL DB have a truckload of tables and developer of the software doesn't offer much help right now.
Thanks in advance.
Short answer -- yes you can -- but, there is a better way. Simply use `mysqldump` to dump all tables in the database to a bz2 file and then move the bz2 file the new computer and import the database there. For example to completely move your "toys" database (example name) you wold $ mysqldump toys | bzip2 -c > toysbackup.bz2 The move toysbackup.bz2 to your new machine and to restore it, simply do $ mysql toys < <(bzcat toysbackup.bz2) Done. This way mysql can catch and handle any table version differences as part of the import rather than trying to sort out table versions in raw /var/lib/mysql files. I did copy /var/lib/mysql files a couple of time during the SuSE 7 days, but since, I just dump to bz2 or xz and move the compressed archive of all tables and the import on the next machine. -- David C. Rankin, J.D.,P.E. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Op donderdag 6 december 2018 10:54:27 CET schreef David C. Rankin:
On 12/5/2018 3:22 PM, Andrei Verovski wrote:
Hi !
I have an old accounting system running on SuSE 42.x and MySQL 5.6.
Can I just copy all mysql files from /etc and /var/lib/mysql to another PC with Leap 15 and MySQL 5.6 ?
This MySQL DB have a truckload of tables and developer of the software doesn't offer much help right now.
Thanks in advance.
Short answer -- yes you can -- but, there is a better way. Simply use `mysqldump` to dump all tables in the database to a bz2 file and then move the bz2 file the new computer and import the database there.
For example to completely move your "toys" database (example name) you wold
$ mysqldump toys | bzip2 -c > toysbackup.bz2
The move toysbackup.bz2 to your new machine and to restore it, simply do
$ mysql toys < <(bzcat toysbackup.bz2)
Done. This way mysql can catch and handle any table version differences as part of the import rather than trying to sort out table versions in raw /var/lib/mysql files. I did copy /var/lib/mysql files a couple of time during the SuSE 7 days, but since, I just dump to bz2 or xz and move the compressed archive of all tables and the import on the next machine. Another option is to install and use phpmyadmin on both machines. Export the databases on machine 1 to .sql files, import them on machine 2.
-- Gertjan Lettink a.k.a. Knurpht openSUSE Board Member openSUSE Forums Team -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Hi ! Thanks to everyone who replied with advice ! I have phpmyadmin on both servers, yet my concern was following - mysql database users and permissions. This accounting & inventory control system have a truckload of databases (not single, but many), each with many tables, and I have no idea how they relate to each other. So this is why I asked if its possible to move the whole setup as is, by means of copying /var/lib/mysql, in order to avoid any mishaps. Looks like it should work. On 12/6/18 3:00 PM, Knurpht-openSUSE wrote:
Op donderdag 6 december 2018 10:54:27 CET schreef David C. Rankin:
On 12/5/2018 3:22 PM, Andrei Verovski wrote:
Hi !
I have an old accounting system running on SuSE 42.x and MySQL 5.6.
Can I just copy all mysql files from /etc and /var/lib/mysql to another PC with Leap 15 and MySQL 5.6 ?
This MySQL DB have a truckload of tables and developer of the software doesn't offer much help right now.
Thanks in advance. Short answer -- yes you can -- but, there is a better way. Simply use `mysqldump` to dump all tables in the database to a bz2 file and then move the bz2 file the new computer and import the database there.
For example to completely move your "toys" database (example name) you wold
$ mysqldump toys | bzip2 -c > toysbackup.bz2
The move toysbackup.bz2 to your new machine and to restore it, simply do
$ mysql toys < <(bzcat toysbackup.bz2)
Done. This way mysql can catch and handle any table version differences as part of the import rather than trying to sort out table versions in raw /var/lib/mysql files. I did copy /var/lib/mysql files a couple of time during the SuSE 7 days, but since, I just dump to bz2 or xz and move the compressed archive of all tables and the import on the next machine. Another option is to install and use phpmyadmin on both machines. Export the databases on machine 1 to .sql files, import them on machine 2.
-- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Op donderdag 6 december 2018 17:31:45 CET schreef Andrei Verovski:
Hi !
Thanks to everyone who replied with advice !
I have phpmyadmin on both servers, yet my concern was following - mysql database users and permissions. This accounting & inventory control system have a truckload of databases (not single, but many), each with many tables, and I have no idea how they relate to each other.
So this is why I asked if its possible to move the whole setup as is, by means of copying /var/lib/mysql, in order to avoid any mishaps.
Looks like it should work.
Just did a small test ( 4 db's, 4 users ). Stopped the mysql service, replaced the original with a copy, restarted the service and all looks well.
On 12/6/18 3:00 PM, Knurpht-openSUSE wrote:
Op donderdag 6 december 2018 10:54:27 CET schreef David C. Rankin:
On 12/5/2018 3:22 PM, Andrei Verovski wrote:
Hi !
I have an old accounting system running on SuSE 42.x and MySQL 5.6.
Can I just copy all mysql files from /etc and /var/lib/mysql to another PC with Leap 15 and MySQL 5.6 ?
This MySQL DB have a truckload of tables and developer of the software doesn't offer much help right now.
Thanks in advance.
Short answer -- yes you can -- but, there is a better way. Simply use `mysqldump` to dump all tables in the database to a bz2 file and then move the bz2 file the new computer and import the database there.
For example to completely move your "toys" database (example name) you wold
$ mysqldump toys | bzip2 -c > toysbackup.bz2
The move toysbackup.bz2 to your new machine and to restore it, simply do
$ mysql toys < <(bzcat toysbackup.bz2)
Done. This way mysql can catch and handle any table version differences as part of the import rather than trying to sort out table versions in raw /var/lib/mysql files. I did copy /var/lib/mysql files a couple of time during the SuSE 7 days, but since, I just dump to bz2 or xz and move the compressed archive of all tables and the import on the next machine.
Another option is to install and use phpmyadmin on both machines. Export the databases on machine 1 to .sql files, import them on machine 2.
-- Gertjan Lettink a.k.a. Knurpht openSUSE Board Member openSUSE Forums Team -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Hi ! Copied this weekend /var/lib/mysql and /etc/mysql*, everything works fine. Thanks to everyone ! On 12/6/18 6:36 PM, Knurpht-openSUSE wrote:
Op donderdag 6 december 2018 17:31:45 CET schreef Andrei Verovski:
Hi !
Thanks to everyone who replied with advice !
I have phpmyadmin on both servers, yet my concern was following - mysql database users and permissions. This accounting & inventory control system have a truckload of databases (not single, but many), each with many tables, and I have no idea how they relate to each other.
So this is why I asked if its possible to move the whole setup as is, by means of copying /var/lib/mysql, in order to avoid any mishaps.
Looks like it should work. Just did a small test ( 4 db's, 4 users ). Stopped the mysql service, replaced the original with a copy, restarted the service and all looks well. On 12/6/18 3:00 PM, Knurpht-openSUSE wrote:
On 12/5/2018 3:22 PM, Andrei Verovski wrote:
Hi !
I have an old accounting system running on SuSE 42.x and MySQL 5.6.
Can I just copy all mysql files from /etc and /var/lib/mysql to another PC with Leap 15 and MySQL 5.6 ?
This MySQL DB have a truckload of tables and developer of the software doesn't offer much help right now.
Thanks in advance. Short answer -- yes you can -- but, there is a better way. Simply use `mysqldump` to dump all tables in the database to a bz2 file and then move the bz2 file the new computer and import the database there.
For example to completely move your "toys" database (example name) you wold
$ mysqldump toys | bzip2 -c > toysbackup.bz2
The move toysbackup.bz2 to your new machine and to restore it, simply do
$ mysql toys < <(bzcat toysbackup.bz2)
Done. This way mysql can catch and handle any table version differences as part of the import rather than trying to sort out table versions in raw /var/lib/mysql files. I did copy /var/lib/mysql files a couple of time during the SuSE 7 days, but since, I just dump to bz2 or xz and move the compressed archive of all tables and the import on the next machine. Another option is to install and use phpmyadmin on both machines. Export
Op donderdag 6 december 2018 10:54:27 CET schreef David C. Rankin: the databases on machine 1 to .sql files, import them on machine 2.
-- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
On 12/06/2018 10:31 AM, Andrei Verovski wrote:
Hi !
Thanks to everyone who replied with advice !
I have phpmyadmin on both servers, yet my concern was following - mysql database users and permissions. This accounting & inventory control system have a truckload of databases (not single, but many), each with many tables, and I have no idea how they relate to each other.
You can use the same `mysqldump --all-databases` to dump your entire system. The reason you compress the data from .sql to .bz2 or .xz is to minimize the size (from 500-1000%). The .sql tables are text (and some very sparse). You can easily get 5:1 or more by simply compressing the data. Now with 32G smart cards, it's not nearly as critical as it was with floppies.... Also look at adding the `--add-drop-database` and `--add-drop-table` options. -- David C. Rankin, J.D.,P.E. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Knurpht-openSUSE wrote:
Op donderdag 6 december 2018 10:54:27 CET schreef David C. Rankin:
On 12/5/2018 3:22 PM, Andrei Verovski wrote:
Hi !
I have an old accounting system running on SuSE 42.x and MySQL 5.6.
Can I just copy all mysql files from /etc and /var/lib/mysql to another PC with Leap 15 and MySQL 5.6 ?
This MySQL DB have a truckload of tables and developer of the software doesn't offer much help right now.
Thanks in advance.
Short answer -- yes you can -- but, there is a better way. Simply use `mysqldump` to dump all tables in the database to a bz2 file and then move the bz2 file the new computer and import the database there.
For example to completely move your "toys" database (example name) you wold
$ mysqldump toys | bzip2 -c > toysbackup.bz2
The move toysbackup.bz2 to your new machine and to restore it, simply do
$ mysql toys < <(bzcat toysbackup.bz2)
Done. This way mysql can catch and handle any table version differences as part of the import rather than trying to sort out table versions in raw /var/lib/mysql files. I did copy /var/lib/mysql files a couple of time during the SuSE 7 days, but since, I just dump to bz2 or xz and move the compressed archive of all tables and the import on the next machine.
Another option is to install and use phpmyadmin on both machines. Export the databases on machine 1 to .sql files, import them on machine 2.
Haha, perhaps slight overkill :-) (anyway, doesn't phpmyadmin just use mysqldump underneath?) With compatible or near-enough compatible, I have always copied the files directly. Otherwise I would go with David's suggestion of using mysqldump. -- Per Jessen, Zürich (10.0°C) http://www.dns24.ch/ - free dynamic DNS, made in Switzerland. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Le 06/12/2018 à 17:41, Per Jessen a écrit :
With compatible or near-enough compatible, I have always copied the files directly. Otherwise I would go with David's suggestion of using mysqldump.
anyway, a backup with mysqldump is mandatory... jdd -- http://dodin.org -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
participants (6)
-
Andrei Verovski -
David C. Rankin -
jdd＠dodin.org -
Knurpht-openSUSE -
Patrick Shanahan -
Per Jessen