Linux - GeneralThis Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
I am running 2 test servers, both RHEL4 with Nanhant 1
The SQL server on it is 4.1.10a
I used the mysqldump on the actual server, got the dump, then did a scp to the other server. I tried to restore the database on the latter server, but upon running the command, this prompts out
Quote:
[root@backupdb tmp]# mysqldump -uroot -p articles < articles2.dump
-- MySQL dump 10.9
--
-- Host: localhost Database: articles
-- ------------------------------------------------------
-- Server version 4.1.10a
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Database (empty) is already created. I tried to create the first table in it, the dump restoring ran furthur (ran past the "Drop Table if exist") and after that it stops again
It has been a while since i did this but looking at what you have put the command your using to import is wrong.
From what i remember you need to use mysqldump to export the database as you have
scp the file to the new machine
then just use the mysql client (i.e. mysql not mysqldump) to re-import the data.
mysqldump -uroot -p articles < articles2.dump
should be
mysql -uroot -p articles < articles2.dump
the mysqldump command dumps the database as SQL, using the normal mysqlclient should then apply all the SQL statements in the new (empty) database and recreate the database.
Using --opt, it locks table etc.. whats the purpose of locking table? will it unlock on restore?
If I use mysqldump on database that is being written to, will it update itself, or would I risk having broken relation?
I used --opt, it adds "drop table if exist" to my file. (Without --opt it does too actually.. hmm)
Is there a way to dump it such that it creates the database too? So when I restore, I don't need to create the database, rather I just dump the file to mysql and it will create from the database downwards
Locking the table simply means that whilst the table is being dumped it is locked so that users logged into the database at the time can not make changes to the table whilst its being dumped. This is done to give consistency of data and the tables are automatically unlocked after the dump is complete.
adding the command --databases or --all-databases will cause the create database statements to be included. see man mysqldump which shows you the options available.
Originally posted by cardy Locking the table simply means that whilst the table is being dumped it is locked so that users logged into the database at the time can not make changes to the table whilst its being dumped. This is done to give consistency of data and the tables are automatically unlocked after the dump is complete.
adding the command --databases or --all-databases will cause the create database statements to be included. see man mysqldump which shows you the options available.
Yup I just realised that. We did a test to cron the job, to do a mysqldump with gzip, then scp over to another server to store it.
Now its time to bring it to the datacentre and test with the actual data.
Whats the best way to ensure the output usually from scp gets thrown away and not stored.. or best still .. write to a file.
I got a darn simple script running.. you guys got any tips?
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.