Restore Database From Another Disk
I'm wanting to restore my old database by copying the contents of the old /var/lib/mysql folder to the new server, but this isn't working.
Is there a documented procedure for doing this? I'm using InnoDB and MyISAM.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
How Big ( Disk Size ) Would This Database Be: 4.2 Billion Rows
I have a database in MySQL version 5.0.19 that I'm making. Its structure is: CREATE TABLE `big` ( `keyval` int(10) unsigned zerofill NOT NULL, `name` varchar(20) default NULL, `avail` enum('y','n') default NULL, PRIMARY KEY (`keyval`), KEY `keyval` (`keyval`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; The 'keyval' will be filled with every value from 00000000 to 4294967295 ( 4.3 billion rows ), and assume worse case of 20 characters in each 'name' element and 'y' or 'n' always set in the 'avail' colmn.
Release Disk Space By Drop Database ?
OS. linux 2.4.22 MYsql : 4.0.18-standard I created a database and populate 2 big InnoDB tables, the total size is > 3G. After all testing, I droped the database . But, the harddisk space is not release to the filesystem, Why, How could I recover that ?
Release Disk Space By Drop Database
OS. linux 2.4.22 MYsql : 4.0.18-standard I created a database and populate 2 big InnoDB tables, the total size is > 3G. After all testing, I droped the database . But, the harddisk space is not release to the filesystem, Why, How could I recover that.
How To Restore Database
I think i will have to da a full resortation of a database. lets assume old database is deleted. So I go to my hosting control panel and create a new mysql database Use same username and password as used in original open phpmyadmin and do to the new database and select import-then import the file that I have as a backup--i have a file that I exported as a text file-a full databse export. it is about 4 mb so I hope can do it at one time Is that basically how I can do it??
Database Restore Help
im trying to construct a database using a backup file i made aout a year ago when i use phpmyadmin to upload the file i get an error on the first table it trys to create CREATE TABLE `cal` ( `id` bigint( 20 ) NOT NULL AUTO_INCREMENT , `author` varchar( 32 ) NOT NULL default '', `subj` varchar( 64 ) NOT NULL default '', `txt` text NOT NULL , `typ` tinyint( 4 ) NOT NULL default Ɔ', `dmy` varchar( 8 ) NOT NULL default '', `hm` varchar( 4 ) NOT NULL default '', `wday` tinyint( 4 ) NOT NULL default Ɔ', `location` varchar( 64 ) NOT NULL default '', `approved` char( 1 ) NOT NULL default '', `tz` int( 11 ) NOT NULL default Ɔ', `dat` bigint( 20 ) NOT NULL default Ɔ', `mkt` bigint( 20 ) NOT NULL default Ɔ', PRIMARY KEY ( `id` ) , KEY `typ` ( `typ` ) , KEY `wday` ( `wday` ) , KEY `dmy` ( `dmy` ) , KEY `mkt` ( `mkt` ) ) ENGINE = MYISAM DEFAULT CHARSET = latin1; With this msg MySQL said: #1064 - You have an error in your SQL syntax near ') ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 20 this was a backup straight from phpmyadmin some time ago, can someone tell me why it wont restore?
How To Restore A Database (.bak)
Recently I downloaded a ASP script, so I can use on my website, however in the instructions, it tells me the following : - Restore the bbdf.bak SQL database with SQL 2005 (any edition) Management Tools Now I have the file bbdf.bak, but I am not sure what I am suppose to do. I have downloaded and installed Microsofts SQL 2005, but am totally lost as to what I am suppose to open and type .... ? ? I would of thought it would be easier for them to just give me a .mdb file or something which I could use...
Restore Of Database
How do I go about restoring multiple databases at the same time. I know if I needed to restore 1 database I can do something like. mysql db_name < backup-file.sql but what if backup-file.sql consists of multiple databases (ie it was created using mysqldump-all-databases) , which database would I specify in the db_name section?
SQL Database Restore
I upgraded to Tiger and it damaged my SQL database. I have a backup file that is about 80 mb. The backup file was created with PHPmyAdmin and is in .SQL format. When I try to use PHPmyAdmin to load this .SQL file and restore my database, I get an error message that says NO SQL QUERY. The file stops executing and NO data is restored. Is this because the file is too big ?? Is there an application that will break the .SQL file down into smaller parts ?? Is there a way to restore the data using the .SQL file and a direct SQL command line entry ??
Database Restore
How may I restore *.sql databases from linux shell? I've read the use of mysql command I don't have installed. This is a remote server with preinstalled Apache-php-mysql.
Cannot Restore A Database
I have a text file that contains the output from mysqldump. I try to restore the database with: Quote: mysql -u root -p <password>1472 database-name < name_01_06_2007.sql and mysql produces a message as if the command is incorrect. The message is: Quote: mysql Ver 14.12 Distrib 5.0.27, for redhat-linux-gnu (i686) using readline 5.0 Copyright (C) 2002 MySQL AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Usage: mysql [OPTIONS] [database] How do I restore a database after mysqldump? I read an article published in devshed and I followed their steps but...
Backup And Restore Database
i did a backup of mysql database using the command line utility mysqldump. this is the command i used "mysqldump -u root -h <hostname> --databases <dbname> > <backupfile> when i try to restore this using mysql command line client it gives a error after some time the message says that a error has happened on a line number and it states that the table columns does not match the values in the insert statmenet. i havent done any cahnges to the backup file i just dumped and restored thats all but when i tried this with mysql administrator gui tool it worked fine.
Database Restore Space
i want to increase space of the database.. while restoring.. wen i click import it was '(Max: 2,048KiB)' i want to increase this space to.. higher
Cannot Restore Mysql Database
My host suddenly went out of business, and deleted all my files without a notice. The only thing I have now is a week old back up Now everytime I'm with a new host, and created a new database, to restore the old one over it, but everytime I try to restore, using ssh, I get that dreadful error message: " ERROR 1062 at line 238892: Duplicate entry '189864' for key 1"
Get Trouble With Restore Database
I truly get trouble with my database. I transfer from a shared hosting to another one. Thus, I make a backup file called svduhoc.sql. My database is so large over 160MB. Of course we can not restore database by phpMyAdmin. Therefore, I use another way. Although I have tried many times, I couldnt restore. I even asked for the help from techincal support of my new shared hosting. However they told me that there could be a problem and recommended me to come over here to get your help. Both of us: technical support and me got the same error at line 11923. Whenever we restor, it quits at the same the line 11923. So what should I have to do now? I really get mad and stress because I couldn not lose this database. It is very important to me. If anyone can fix this problem and successfully restore my database, I will pay $50 bucks even though I am poor student now. Because it is very important then I need all your help. You guys can contact me here or pm or email "tony_hai@hotmail.com"
InnoDB Constraints And Database Restore
We use InnoDB tables and foreign key constraints extensively. The mysqldump backs up the database tables in alphabetical order with foreign key constraints defined in the create statement of each table. These foreign key constraints are violated at the time of restore. We have tried the following two solutions... (1) We have tried to backup the database tables in the order of their dependencies. This works but the backup scripts need to be maintained carefully as new tables are added/removed from the database. (2) phpMyAdmin export does the database dump and puts the table constraints as ALTER statements at the end of the dump. Unfortunately, the phpMyAdmin dumps cannot be automated and I have not been able to push constraints to the end of the backup using mysqldump.
MySQL Database Restore Error
I decided to download a db and upload it back to the server just out of curiosity. My site is now missing the top menu and components menu in the backend of Mambo. This site is for a client and I'm in big trouble here. Code:
Error 1044 On Database Restore
I'm trying to move a database from one machine to another. I dumped the database and successfully loaded it on one machine, but I can't do it on another machine. I have the database set up. I created the user to access the database, and gave all privs with grant option for that database. But when I go to run the command to restore the database, I get the following error: $ mysql -u user -p database < database.sql Enter password: ERROR 1044 at line 26: Access denied for user: 'user@localhost' to database 'database' I don't understand. Looking at the sql file, the line is: /*!40000 ALTER TABLE accidents DISABLE KEYS */; LOCK TABLES accidents WRITE; UNLOCK TABLES; /*!40000 ALTER TABLE accidents ENABLE KEYS */; Why can I do this on one machine but get an error on the other? The users are set up the same. Actually, using mysqlcc, I can't even get the user to have the lock tables priv, even though I'm root granting the priv, it just refuses to take. Yet, the machine it worked on seems to be set up the same way and I didn't have a problem. Can someone point me in the right direction to a solution?
Error 1044 On Database Restore
I'm trying to move a database from one machine to another. I dumped the database and successfully loaded it on one machine, but I can't do it on another machine. I have the database set up. I created the user to access the database, and gave all privs with grant option for that database. But when I go to run the command to restore the database, I get the following error: $ mysql -u user -p database < database.sql Enter password: ERROR 1044 at line 26: Access denied for user: 'user@localhost' to database 'database' I don't understand. Looking at the sql file, the line is: /*!40000 ALTER TABLE accidents DISABLE KEYS */; LOCK TABLES accidents WRITE; UNLOCK TABLES; /*!40000 ALTER TABLE accidents ENABLE KEYS */; Why can I do this on one machine but get an error on the other? The users are set up the same. Actually, using mysqlcc, I can't even get the user to have the lock tables priv, even though I'm root granting the priv, it just refuses to take. Yet, the machine it worked on seems to be set up the same way and I didn't have a problem. Code:
InnoDB Constraints And Database Restore
We use InnoDB tables and foreign key constraints extensively. The mysqldump backs up the database tables in alphabetical order with foreign key constraints defined in the create statement of each table. These foreign key constraints are violated at the time of restore. We have tried the following two solutions. (1) We have tried to backup the database tables in the order of their dependencies. This works but the backup scripts need to be maintained carefully as new tables are added/removed from the database. (2) phpMyAdmin export does the database dump and puts the table constraints as ALTER statements at the end of the dump. Unfortunately, the phpMyAdmin dumps cannot be automated and I have not been able to push constraints to the end of the backup using mysqldump.
Database Restore / MySQL 4.1 -- InnoDB
I have got a MySQL 4.1 that we use wit vBulletin.... We had a crash last week and I've been able to restore the data up to September 2005 as it stands. Now, I'm under the impression I could rebuild the data from the logs but I cannot seem to figure out how to do that based on the tools and instructions posted. I've got my ib_log files (ib_log0 and ib_log1) also my ibdata file, which is full of all the data i'm trying to get restored. Opening it up in hex editor, I can see posts and information right up to the moment the database crashed. What steps should I take to restore the data from the log/data files? I've tried several different steps, but I'm goofing it up somewhere I guess. Are there some other tools available to help with this? This site was a 'non-revenue' site, so I didn't exactly make all of the redundancy arrangements that I would normally do for an Oracle/MSSQL or any other live business database. I'm ateast going to enable mirroring this time around and move the backups to another location.
Font Error After Restore Database
I have a forum which is use Vietnamese language and input is utf-8. It work normall in mysql 4.0 but after i upgrade to mysql 4.1 it has some strange error in font. Some character like á à é broken and become ỿ . :( . In the end i found a stupid solutions, that was i copy a /usr/local/mysql/mysql/databasename/ ( a folder which is contain database in mysql ) from mysql 4.0 and put to mysql4.1 folder. Everything work well again. And now a real big problem happen. I backuped my database with : mysqldump data data.sql and drop everything to fix my server, after fixing my server i restore database use : mysql database < database.sql and a old problem appear again :( . Some character broken and apear as ỿ . I dont know what to do now becauase that was my only backup at this time. I'v google a week already and could not find any solutions.
Backup/Restore Mysql Database From Remote Server To Local Pc
Im trying to download mysql database "dbproducts1" which is residing in my webhost's server (Linux). From telnet i backed up using "mysqldump -u -p dbproducts1 > dbproducts1.sql" Then i downloaded this file via ftp to my local pc(WinXP). Now on my local pc to restore database i went to command prompt and i did following: mysql -u -p dbproducts1 < dbproducts1.sql I got following error: ERROR 1064 (42000) at line 380: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-workshop ( title varchar(250) NOT NULL default '', decription longtext NOT ' at line 1 I opened the phpmyadmin and found that dbproducts1 database was restored but with only few tables i.e. not all tables in the db was restored. Can somebody please help me to backup/restore this database. I checked the mysql server version and found following: server version: 5.0.24a-community in my local winxp pc server version: 3.23.36 in my webhost's server (Linux)
MYSQL Database Alteration, Repair And Restore Takes Huge Time
I am trying to run alteration queries on one of my mysql tables which has more then 22,00,0000 lakh records. Its been 23 hours and the process is still running (I have to close all the sites running on server due to same). My server specifications: Red Hat 9.0 Pentium 4 3.0 GHz 2 GB Ram, Burst RAM 5 GB Running webserver, mail server as well. Is there a way I can view the minute process details, as what table record is being updated ?
Disk Performance
We have an application which tends to get very disk-io bound, spending large amounts of processor time in "iowait" during select queries. Given that writes are relatively infrequent, which would the experienced admins recommend for the disk configuration: Raid5 or Raid10? Raid0 is even an option, since we have a backup server and could easily add another, but it's not preferable unless the read performance would increase substantially. The hardware looks like this: Dell PowerEdge 6650, 4x2.8ghz CPU, 12gb RAM, 4x36gb internal drives, raid 5 Powervault 220S, 14x73gb 15K drives, dual-connected to the server. Current configuration is Raid5. MySQL 4.0.18, RHEL3 (2.4.21-27.0.1.ELsmp) and now using the megaraid2 driver. There is probably room for optimization in the application, but that's currently out of our control. We may even move to a different database platform, but if MySQL can handle the load I'd just as soon not.
Running MtSQL On A RAM Disk
I am trying to set up the same thing but have been stopped right from the get go as I can not get a larger than 2G RAM disk going (using a 64 bit Ubuntu installation). I am hoping someone who has gone through the basic set up might get me squared away to start. An offline conversation would be fine and perhaps save the rest of y'all wading through said basics.
Disk Quota In MySQL
Is there a way to assign a limit on the size of a specific database? For example database A can take up no more than 500 MB. One of my databases can potentially grow without any bounds and possibly stop other critical databases from operating. We are using MySQL 4.0.21 on Linux. We use innodb tables.
Data Kept On The Hard Disk
my mysql database went down... had to reinstall it... where is all the data for the tables kept - i got the /var/lib/mysql/ bit but thats only the tables
Showing Disk Usage
How can I see how much disk space is being occupied by an InnoDB table? Looking into /var/lib/mysql doesn't seem to work anymore for InnoDB tables. Please tell also if there is a way to see how much space is occupied by a particular row or column of a table.
Data Location On Hard Disk
I have just installed MySQL 5.0 and finished the first test from a database application. I saw that the data is stored in c:program...MySQl... I am not happy with that. I want MySQL stores all data in a map of my own choice.
Flush A Heap Table To Disk?
As the subject says, is it possible to flush the contents of a heap table to disk? For example if I have data that I want to be inserted in to a heap table (for optimisation reasons) but I don't want to risk losing *all* the data if mysql crashes. Is it possible to perform something like a flush so that if mysql does crash some of the data is saved.
Ibdata1 Is Close To Disk Size
I am running MySQL 4.0.13 with my-medium.cnf (attached) on a Solaris 8, 4x450 Mhz, 4gb RAM. All my tables are InnoDB. Currently my ibdata1 is 1.8 gb. I have about 1 gig space left on the same partition. Also it is excruciatingly slow now. How do prevent the ibdata1 from filling up the disk partition ? How do I increase the speed on my MySQL? Code:
Copying Table To Disk During Execution
I seem to be having an issue with a query's results being paged out to disk during runtime. I notice in processlist it saying "Copying to tmp table on disk". select col1, col2, count(ip) as 'COUNT' from db.table group by 2 order by 1, 3; None of the columns are a primary/foreign or an index. This query returns 92K rows and has taken anywhere from 50 seconds to run to over 1.5 minutes. The table size on disk is around 33MB. After doing some research i found that i should be changing the tmp_table_size system variable. Originally it was set to the MySQL default, 33554432. I set the varaible to 90,000,000 and it still started paging out to the disk (this was the only query running at the time). I would think that 90MB in space would have been enough to handle the query since that would assume over 600K row size. I actually had some trouble setting the tmp_table_size for the global variable but was able to change it as a session variable. I have also tried altering the query_cache variable (which has only slowed the query down - currently it sits at a size of 50MB) and also the sort_buffer_size but neither have made a difference. What is the magic variable that i should be messing with?
Free Disk Space On DELETE
I'm building a huge DB. One particular table can contain as much as 125 million records. I need to DELETE records older than a year. That part works fine but it doesn't free any space on my hard drive (C:/). I tried OPTIMIZE TABLE and it still won't free hard disk space.
Moving Mysql To Another Hard Disk
I have a web based intranet application (php) that uses a mysql database. The server has just had a new drive added to it and am looking for a way to move the database from the current c drive to this new drive.I'm looking for a way to either: move the entire database to the new drive (in which case do i have to do anything within the code - the database name is still the same and i'll presume another drive is still localhost can) Can i just make Mysql save data into the new drive (and also move just the old data file to the new drive leaving everything else as it was).
Ibdata1 Is Close To Disk Size
I am running MySQL 4.0.13 with my-medium.cnf (attached) on a Solaris 8, 4x450 Mhz, 4gb RAM. All my tables are InnoDB. Currently my ibdata1 is 1.8 gb. I have about 1 gig space left on the same partition. Also it is excruciatingly slow now. How do prevent the ibdata1 from filling up the disk partition ? How do I increase the speed on my MySQL? [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K myisam_sort_buffer_size = 8M query_cache_size = 16M query_cache_limit = 8M log-bin server-id = 1 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /usr/local/mysql/var/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /usr/local/mysql/var/ #innodb_log_arch_dir = /usr/local/mysql/var/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 16M #innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 5M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 ( I had to comment the innodb to stop crashing ) [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
Flush A Heap Table To Disk
For example if I have data that I want to be inserted in to a heap table (for optimisation reasons) but I don't want to risk losing *all* the data if mysql crashes. Is it possible to perform something like a flush so that if mysql does crash some of the data is saved.
MyISAM Table And Sort Order On The Disk
I ran the myisamchk tool with the options --sort-index --sort-records=1 against an MYI file. Is there something I can use to confirm what this command did? I would like to see what the sort order on the disk for each table in my database.
MyISAM Table And Sort Order On The Disk
I ran the myisamchk tool with the options --sort-index --sort-records=1 against an MYI file. Is there something I can use to confirm what this command did? I would like to see what the sort order on the disk for each table in my database. I am using MySQL 4.0.20a-nt running on a Windows 2000 box.
How Do I Reclaim Disk Space In Data Folder
I have 5 databases: - mysql - test - product1 - product2 - product3 The hard disk on my server was near full, so I decided to delete databases product2 and product3 as I no longer need them. I dropped the databases product2 and I noticed the disk space was not freeed by MySQL after dropping this database. The tables in databases product2 and product3 are INNODB. What is the best method to reclaim my disk space when dropping entire databases?
Disk Usage Increases After Dropping A Large Index?
I have a linux server which is hosting several very large (~20GB) databases. In order to save some disk space I dropped an index on one of the larger (InnoDB) tables because it is no longer needed. It took about 30 minutes for MySQL to drop the index, during which time I saw the free disk space decrease. I didn't think anything of it while the drop operation was running because I figured it was some kind of temp file being used by the drop (even though the temp directory for mysql is supposed to be on another partition). To my surprise, after the index was dropped, not only did I not have more free space as I expected, but in fact I had about 2 GB less than I did before I dropped the index. Huh? So now I'm down 1 index and 2 GB of space. How does dropping a db object actually increases the amount of disk used by mysql? Is there anyway I can reclaim this space? MySQL version is 4.0.15
Restore Db From 4.1.12 To 3.23
I've a backup from my database running on mysql 4.1.12 Now i want to restore this to an old machine running 3.23. How to do this. I get several errors.
DB Restore
I appear to be getting the following error message everytime I want to restore this database. The message is as follows: Unable to restore database web1_wordp : ERROR 1064 (42000) at line 754: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '22:17:15)' at line 1 I'm not quite sure what this means :confused: - How would I correct it?
Restore
A customer passed me her backup, for me to open in my MySql. They are 50Mb of backup. MySQLAdministrator is being long a lot, very even to make the restoration in my machine (1kbps!!!!)
Restore
I've got a table with over 400,000 records. I did a mysqldump to ensure I could restore it in case of problems. Sure enough, problems arose and I needed to do a restore. The mysqldump created the usual create database, create table and one gigantic insert statement, where all 400,000+ records are inserted. I started my restore around 10pm or so yesterday and was surprised to wake up this morning and find it still running. Considering the dump itself took a couple of seconds, why is the restore taking so long? I'm building a website that's going to have tons of traffic. A database restore that's going to take multiple hours seems to be unacceptable. One way to speed up the process would have been to simply copy the actual database files and just stick them back in the data directory.
Mysql Restore
I have been running a mysql backend to phpBB on my home Gentoo based linux server. I recently switched the mysql and phpBB to run on a dedicated web server on my home network. I was using mysql version 4.1.14 and then 4.1.14-r1 on my new web server. The problem is that the character set seems to have gotten corrupted during the backup and restore process. The forum is based in French and hence a lot of the special characters have gotten messed up. I have even tried copying the my.cfg file from the old server to the new one but with no effect.
Stalled Restore
I Exported my MySQL database (60KB) (MySQL 4.0.24-standard) from iPowerweb hosting site. I know want to restore it to my MySQL (mysql-4.1.13a-win32.zip) on my home server. I loged on with MySQL Administrator, went to Restore, Opened .sql file, hit Start Restore, and it gets stuck at 9598 number of bytes each time I attempt it. Total is 60812 Bytes.
|
TRACKER
