How Do I Backup Large Database?
I have a forum with over 130k members and the database is quite large. I'm on a shared server through hostmonster.com
My question is, how do I completely backup this forum database so if anything ever happens, I can restore every single detail.
Is there a way to do so that doesnt require codin or anything?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Live Backup Of Large Table On Production Server
I have a production server serving ads on our website using Openads (currently version 2.0) via PHP, MySQL, and Apache. It's been operational for over a year now and the database, two tables in particular, have grown to a few hundred megs. It's live and we can't afford any downtime for this server as it serves our ads to generate revenue for us. Whenever an ad is viewed, a new recorded is added to the database. However, in order to do a backup, usually mysqldump, that requires locking the table for several minutes, during which time, it locks the tables, and any httpd (in our case all of them) processes depending on those tables. Finally, the point of this post, DOES ANYONE KNOW A WAY OF BACKING UP OR DOING A MYSQLDUMP WITHOUT LOCKING THE TABLES for a very large database? Can I just run a query that will get me all the information without locking the tables (relational problems shouldn't be a problem yet cause its usually only 1 table being written to).
View Replies !
View Related
No 'BACKUP TABLE' Nor 'BACKUP DATABASE' In MySQL 6.0.4
I've installed MySQL 6.0.4-alpha and trying to restore some tables found that there's no BACKUP/RESTORE TABLE/DATABASE, it returns 'You have an error in your SQL syntax' and to check the corresponding version manual, but in the documentation area it is only 6.0 (does it apply to 6.0.4?) What can I do? I've exported from a different database version as text and I will try to insert as text, but how can I make future backup/restore?
View Replies !
View Related
BACKUP DATABASE * TO '/tmp/all.backup'; - DOES NOT WORK
I have troubles with 'BACKUP DATABASE', I thought it was problem of syntax, but have tried even the samples in http://dev.mysql.com/doc/refman/6.0/en/backup-database.html and the command is not recognized $ mysql --version mysql Ver 14.14 Distrib 6.0.4-alpha, for pc-linux-gnu (i686) using EditLine wrapper mysql> BACKUP DATABASE * TO '/tmp/all.backup'; ERROR 1064 (42000): 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 'BACKUP DATABASE * TO '/tmp/all.backup'' at line 1 Have tried 'BACKUP TABLE' but does not work either and documentation tells it has been removed...
View Replies !
View Related
VERY Large Database
I have such question - how I should split large data for the better performance? Its seems to be old question, but can't find it in search Lets say if I have 10000 items, each have 10000 records - at the start and much more later. Main operations - INSERT/SELECT (search for the records on some item by key) DELETE/UPDATE almost will not be used (because of the implementation) I see few possible options 1. Create 100 databases, in each 100 tables, in each 10000 records (records for the 1 item). 2. Create 1 large table with internal index. As I understand, main difference between 1 and 2, that in case 2 main load will be drived to the mysql and index, in case 1 main load will be drived to the FAT (or whatever else used at the server). But before mysql will use index large file (or its index) will need to be loaded, so in case 2 FAT will be loaded too. IMHO option 1 is better from performance side.
View Replies !
View Related
Extracting Data From Large SINGLE-table Database To MULT-table Relational Database
I have a very large single-table database of articles that I want to convert to a multi-table, relational database. The existing single-table database contains fields for article author, article source, and article category, where several 'author', 'source', and 'category' IDs repeat dozens of times for hundreds of different articles. I want to create seperate tables for author, source, and category and populate the new tables by extracting data from the original single-table database by unique ID field. I figured out how to use INSERT and SELECT to pull data in new tables, but can't figure out how to pull only a single instance of a unique author, source, and category to create master reference tables for author/source/category.
View Replies !
View Related
Import Large Database
I saved my website databgase to my home computer. I am setting up a local version of the site, and need to import the database. I have mysql and everything set up. But when I try to run the sql script that I exported from my site's control panel, I get an out of memory error. What is the proper way to import this database? Do I need to increase some memory setting?
View Replies !
View Related
Large DataBase Columns
I am developing, what i consider to be, an extremely large database, using mysql and php. im concerned that one of my sql tables will be too large. tonight, im running some tests, but i was hoping the forums could toss me some good advise. The table will be around 1500 columns long, and about 500 rows on average. each column will not be any larger than 20 varchars. i've never created anything beyond 30 columns. is this beyond what's recommended with using mysql ??? i know the queries may take a little bit to load, but is this simply a bad idea anyway, or am i just getting nervous for no reason at all.
View Replies !
View Related
Large Database Table
I am using MYSQL in order to develope a database table that currently has about 20 million records. The table grows on a daily basis and is expected to grow to a size of about 300 million records.I wanted to get some suggestions in order to handle such large database. What kind of indexes are best? When should splitting the table be considered? What are ways of achieving good performance of queries?
View Replies !
View Related
Large MySQL Database
There are many threads on here about large db designs etc but I'm looking for a little information I have been unable to find anywhere. What sort of design to places like facebook/myspace use for their database(s). Is it best to have a table for user info (id, username, email, etc) then have a table for every feature of your site (blogs, pictures, friends, comments etc etc) or do they have a table for every user. I would assume the first as a table for every user would soon grow very very large and become inefficeint.
View Replies !
View Related
Split Large Database
I made a backup from cpanel and install a forum and website in localhost, with easyphp. Everthing works fine, but i need to install using pypmyadmin my database. But phpmyadmin only import files at 8 Mb. My database are larger, almost 20Mb. I would like to know how to split my database in small files, in order to import from phpmyadmin. Maybe exist a software with a grafic interface?
View Replies !
View Related
Reduce Large Database
I have run into some issues as of late.I have a php program that stores information 4 times / day for multiple items in my database. This has been running for quite some time now and there are many, many records in the database. The records are the same most of the time for each 'item', but it is used to archive information. At this point there are so many records that when I run queries from either PHP or a .NET application, I am receiving errors (which I think is really just the database timing out, but giving me different errors instead). I think this because when I run a smaller query, the code runs just fine. So, I wrote a program in .NET that goes thru the whole database on an 'item' basis, iterates through and compares, if the current item is identical (besides timestamp) to the previous, then the current is deleted. This would probably move the records down from about 2 million to about 1000. However, this query will not run because the database is just too big (that's my guess)
View Replies !
View Related
Movement Of A Large Database Over A Wan
I have a need to transfer a 80GB live database over the Internet (one colo to another colo, no VPN's established). Then keep it consistent up to the colo cutover date. I understand how to setup the replication once the initial copy is made, but my problem is over how to get the initial backup from colo1 to colo2....while maintaining the ability to keep both databases consistent. My initial thoughts... Can one lock the master DB, take a backup, establish SSH replication between the two servers, transfer the backup file (WAN, FedEx, etc..), and then import the backup file once it's transfered from A to B. From how I see it, the DB will be taken down in a consistent state and replication will run for all new transactions. I'll miss all the historical data, but that will be solved once the backup is transfered and imported.
View Replies !
View Related
Large Database In Mysql
I have a database with two tables in MySQL 4.1. Die one tabel contains about 300,000 records and the other table contains about 70,000 records. I tried joining and subquering (very simple sintax) these two tables and received no result. The processor of the server was being tied down. I went to a bigger computer with the same results. I then tried minimizing the tables and had to go to about 10000 and 8000 records respectively, before it worked. I did not try indexes, because I wanted to make a comparison between no indexes and with indexes. Granted I did not tweak many server variables, but shouldn't this just make the query slower and not completely tie it up?
View Replies !
View Related
Is A 100mb Database Large Enough?
I run a forum on www.TheFloorPro.com and just added a gallery add-on. Images are stored on the server as files rather than in the database, but I am still concerned about the database size. After doing a database backup today, I worry that my allowed 100mb database will become too small in a short time. It is already at about 10mb in just over 2 months time. My host is 1and1 and they allow 100 databases of 100mb each, which I don't understand. Don't know why I would need a hundred of them and don't know why the one or 2 that I might need are restricted to 100mb.
View Replies !
View Related
Question About A Large Database
a quick overview of what I'm trying to do. I have a database with about 3.5 million unique items. There is a table that covers which categories each item belongs to. The average item belongs to 3 categories.....the categories are used for navigation on a website. The question - is it better to split each entry for each category into a its own row (~10m rows) or to have each unique item and list all of it's categories in a delimited fashion (category2323|category23489|category382233) in a single row for each category and only have around 3.5m rows.
View Replies !
View Related
Uploading A Large Database
Can someone please comment on how to host a large database - a database of about 500 MBs. Is a database of this size going to work on a shared hosting environment or is a dedicated server a must? And, how would you go about upload or importing the database to the host? I could be wrong, but I don't believe phpMyAdmin will allow importing a file over 50 MBs.
View Replies !
View Related
Large Database Problem
I am developing a PHP & Mysql application. I am using one big table to collect data and some other small tables. The problem is that with the growing number of the rows in the big table my application is getting slower and slower. The application executes a lot of INSERTS almost all the time ( while collecting data). And then it calculates and summarize the data into statistical reports (this is done on demand at realtime). I opimize my queries with EXPLAIN as much as I can and rewrite a lot of them and it became faster but only for some time. I read about the MyIsam storage machine that its locking mechanism is on table level so when an INSERT is executing the table is locked for SELECTs so they have to wait. If that is so is there any solution to this problem? As more of the SELECTS are used to fetch number of rows or other statistical information I am thinking of writing a module to proccess and calculate the data (for example once a day) and to store it in summary tables and then fetch the summarized information for the reports. Is the a correct approach or its a waste of time? And there is one more question. Accept for the summarize data one part of my application uses also the raw data, usually a small part of it (let say 100 rows). I was thinking with the processition and calculation of the data to perform also dublication of the information (maybe optimize it a little bit) in another table to use later for that kind of queries. Do you have any other suggestions. (A couple of month ago I tried to summarize the data while inserting it but it wasn't a good idea) My table right now has 67000 rows and it works just fine when I am summarizing on about 10000 rows but it is geting really slow for 25000 rows. I expect to reach 50,000,000 rows for an year and the reports are going to be on 3,000,000 rows at least. And some more information to help you help me . I execute each of the queries alone and they work fast but some of my reports (web pages) need to exequte 10 queries and it takes 10-15 seconds to load. All of the reports are made depending on a selected timeframe so do you know any working schema about storing data depending on time.
View Replies !
View Related
Migrating Large Database
We've recently upgraded our DB server, and I now have to move our fairly large (10GB) DB to the new server. The DB consists of both MyISAM and InnoDB tables. The largest table is a 9GB InnoDB table with binary image data. What's the best way to a) back the data up, b) migrate the db to the new server?
View Replies !
View Related
Large Database To Handle
I need to store more than 500 million records in a single table. These are basically historical data of different stock market for last 40 years. Initially I thought about partitioning for each company. But there are more than 1024 companies. At the same time if I partition the database based on year then to show the chart for a particular company I need to call almost all the partitions.
View Replies !
View Related
Join Tables On A Large Database 200 Meg
i am trying to work out the most efficient way to list say multipl= e categories of entries, the database is quite large about 200 meg.=20 I would like to know if using join tables is more efficient than storing th= e keys in a varchar field then within the second loop doing a where in (1,2= ,3,4) where the 1,2,3,4 are the stored category keys in the varchar field = rather than a where in (1), where 1 is the pirmaryID of the entry for insta= nce ?
View Replies !
View Related
Large Database & Distinct Values
I have a rather large database table that has a column with maybe 20 different values repeated on and off throughout the database (around 4000 records total). Could someone shoot me a query example of how I can pull each value from this column once to create the list of the 20 or so reoccuring values? Lets call the column work_type. I think I'm maybe just missing knowledge of a particular query command for the WHERE portion maybe?
View Replies !
View Related
Changing Field Of Large Database
I have a fairly large database of aprox 7 million records. One of the columns is of type TEXT and I was wondering if it would be possible to change this to MEDIUMTEXT or LONGTEXT without complications? Is this doable or is it discourraged to perform this operation on such a large database? The field is not indexed, it's simply there containing text and now I want to stuff even more text into it.
View Replies !
View Related
Large Database With Many Concurrent Users
We are looking into how to setup MySQL to have the best performance for mostly reads (around 70%) and some writes (around 30%). The database size is expected to start around 200GB for the first year and expand to around 1.5TB in 4 years. There will be around 350 concurrent users starting off, and it will expand to around 700 concurrent users within 4 years. It seems that clustering is out of the question due to RAM requirements. One scenario I thought about involved having around 5 servers with replication setup to store a copy of the DB on every server. Another way involves a SAN, but I'm not sure how MySQL supports a SAN to store a DB that size while allowing just one copy of the DB to be located on the SAN. In other words, all MySQL servers access one DB on a SAN. Does anyone know which scenario would work best, or if there are better alternatives out there?
View Replies !
View Related
Making Large Test Database
I was using mysqldump to handle my backups. I had a script that ran nightly and would dump my tables and copy them over to a directory, which then got backed up. This worked great when our DB was only 400mb. We have since grown past 2Gb and my mysqldumps keep losing the connecton the mysql server. I'm not sure if this is due to the size of the db, mysql, or scripts timing out. To test, I'd like to create varying sizes of dbs from 2gb to 10gb to test backups with my forseen growth. How do I create a db with erronous data that isn't customer information to test? We aren't using innodb exclusively, so we needsomething that can make a mexed up db.
View Replies !
View Related
Upload A Large Database To A New Server?
I have been developing a web app on my local server and i am now trying to import a large database (1.7million rows) to the production server. i have exported it using phpmyadmin, no worries but i can only upload a tiny 8mb to the productions server (hosted with one and one). i emailed them but changing php.ini or .htaccess files doesnt help as the database is hosted on a different server to the php files to which i have access.I considered writing a script to step through the rows on my localhost and post them like form data to the new site, then writing a script to insert them from the new server, but with 1. 7 million rows, this will take forever, and i'm not entirely confident about this idea at all.
View Replies !
View Related
Backup Database
I Have been using mysql for a while. I downloaded this from mysql site and I created/edit whatever only on the command prompt. My question is, since my database is huge now. I want to copy that database into somewhere else that is safe cause I am going to upgrade my codes, just to prevent if something goes wrong then i can get back my tables and datas. How can I do that?
View Replies !
View Related
Number Of User Accounts For Large Database
I have a large database (816 tables with 11 fields each, each field could hold up to 50 entires) and I'd like to know if each table should have its own user account (created in Cpanel and used in the config file to access the database). May sound like a simple question to some but I need to know in case we have a lot of traffic and 1 user account is not "enough" for all visitors to access the database info.
View Replies !
View Related
Handling Large Database, Spreading The Load
For speed and performance issues, I am thinking about using two servers to handle a single mySQL database. Is this possible? If so, how would I go about doing it? Lets say hypothetically that one of my tables has millions of rows, what would I do to be able to spread the load on my two servers?
View Replies !
View Related
Fastest Way To Search A Large Database Table
I have a table of about 10 million rows and need to search 4 of the columns. Right now I am using mysql's built in fulltext searching capabilities, but as far as speed, it isn't cutting it. I'm getting anywhere from 10-50 seconds for a single search depending on the length of the query. Plus, since the results are paginated, the query needs to be run twice. I was just thinking there must be a method that is faster than this....
View Replies !
View Related
Database Backup And Synchronization
How do I synchronize MySQL table data of my home PC with latest data from a remote server ? My home PC is on a very slow internet connection, so implementing replication will cause long time read lock on remote server, which is not desirable. What are the tools for manual sync of tables?
View Replies !
View Related
Database Backup And Reinstall
I have MySQL (4.0.13) installed on a Windows 2000 Server, interacting happily with ColdFusion MX. However, my hard disk system has collapsed, and I need to back up all data before a virgin OS is installed. My question: how do I back up a database, and reinstall it, using MySQL?
View Replies !
View Related
Automatic Database Backup.
Currently Im using phpMyadmin export feature to create a .sql file that I download as a backup of my database.Is there any way I can automatically get the server to create this backup file for me, and email it to me? I am on linux hosting. There is a button in 'Cpanel' called cron jobs which I think could help but Im not sure how to use them.
View Replies !
View Related
Database Backup From Shell
when i use mysqldump command for taking backup of my database from shell , it works , command is mysqldump databasename > path to store But i want to take backup of my database at "mysql" prompt. As my hosting company , logs me in direct to mysql prompt VIA putty. I have no access to shell. I also donot want to use phpmyadmin or other GUI softwares.
View Replies !
View Related
Mysqldump To Backup Database
In MySQL Command Line Client, I want to back up the database (diary) and the table (d2008), so I typed:PHP Code: mysqldump diary d2008 > backup.sql But I got this error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version... I am using version 5.0. I have also tried entering passwords, usernames and many other formats described on mysql website and manual, but none seemed to work.
View Replies !
View Related
Mysql Database Backup
i need to backup my MYSQL DB i found the following at http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html but i dont realy understand it. Example: mysqldump --add-drop-table -u dbuser -pPW DBNAME > /tmp/examp-back.sql 1. what is "--add-drop-table"? what does it do? do i need it? 2. is it possible to just stop the DB and then copy the MYSQL DB folder? and how is the restore if i just copy? 3.is there a switch to backup all DB's? like "--alldatabases"?
View Replies !
View Related
Backup A Small Database.
I'm looking to backup a small mysql database. The problem is mysqldump does not exist on the server I'm trying to export the database from. Does a php script exist which I could use to do essentially the same thing as mysqldump?
View Replies !
View Related
Copy The Backup Database
I had backup the database. When I use the backup database, so I copy it into the c:mysqldata. When I check the content of the backup database, the content is belong to new database, But now I want use back the old one. so how?
View Replies !
View Related
Cant Backup Database (Greyed Out)
I installed mySQL Administrator, but when I select the backup section to try it out, I can never select anything on the corrosponding tabs, everything is greyed out. Im still new to databases but im guessing the root user should have access to backups?
View Replies !
View Related
Online Database Backup
(1) Can I use mysql admin gui 5.0 to backup/Restore a databas when some users still connect to a database (Online) (2) Can I copy the database file directly as a backup when some users still connect to a database (online)
View Replies !
View Related
Complete Database Backup
I want to run a complete backup of my database on one machine and restore it on another, but i don't know what the commands or procedure is, can anyone help please? There isn't an issue either with it being online/offline, its only a database for a small desktop application.
View Replies !
View Related
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.
View Replies !
View Related
Partial Database Backup
I am developing a hosting solution for a popular open source script: on the website users can sign up to get their own "virtual" copy of the script. There is a single database, but own tables for each user. Database tables engine is currently MyISAM, but I am going to change it to InnoDB. What I need is to let each user download a backup of own data, and restore it when s/he wants. The users shouldn't be able to change other users' tables structure/contents. Also, the backup should be encrypted, so they can't simply check out its contents. One of the possible solutions is to tar the tables files, encrypt the archive, and that would be the backup. But in case user decrypts the archive, s/he would be able to change table structure and other details, making the system function improperly. Of course, as encryption algorithm strength grows, it becomes harder to encrypt the archive and make the system work incorrectly and/or change other users' info. Should I just use some strong encryption algorithm and tar the table files? Does InnoDB allow to work with table files of an open database? Or should I lock the required tables for that?
View Replies !
View Related
How Do I Set Up A Regular Backup Of My Database?
I know I can do it manually in phpMyAdmin, but is there a way to set up a scheduled back-up say once a day? It would be even better if I could set up some sort of cycle where at any one time I'd have 7 versions backed up (Monday-Sunday) and each day that backup would overwrite its old version (that is, on Monday the db backup would be saved over the old Monday backup, and on Tuesday it would be saved over the Tuesday backup). Just in case something goes unnoticed for a couple days I would have the ability to jump back to a given day within a week.
View Replies !
View Related
Automatic Database Backup
Well i don't really know if this should be posted here, as i use mysql along with php but i believe it is more relevant to mysql forums. Is there any way of automatically backing up a db, say once a week? Kind of a programmed process lets say..
View Replies !
View Related
|
TRACKER
