PHPmyadmin: Export Database: Tips?
Using PHPmyAdmin, is there any tips one can give me when exporting? I plan on using this data in a Textpattern or Drupal or Similar web app/cms/blog... what would be the best way to export the data for ease of importing into (for example) Textpattern... Drupal maybe?
View Complete Forum Thread with Replies
Related Forum Messages:
Tips / Advice For Searching A Database
I'm building a search facility that'll be the main focus of my website, with the plan being that it'll be the first port of call for people to browse the products, similar to Amazon. Apart from doing a couple of LIKE commands, is there any advice people can offer and maybe some example queries that will return the most useful results to the user? I'll be searching the following fields: product_name image_name product_description
View Replies !
Phpmyadmin Export
This is a phpmyadmin question more than anything, but I hope someone may be able to help. When creating a dump of a normalized database with 26 innodb tables, phpmyadmin export seems to only export the table structure/data in alphabetical order, rather than FK contraint order. Thus when importing the dump of the data, I need to manually go through and select the order to make sure the constraints hold up. Has anyone else seen this? I'm running phpmyadmin 2.5.4 on both linux and win xp.
View Replies !
How Can I Export A MySQL Table With A Different Name Using Phpmyadmin?
How can I export a MySQL table with a different name using phpmyadmin? I want to export nuke_bbsearch_wordmatch and all it contents to phpbb_search_wordmatch This table in a different db. Fields are the same just the name needs to be changed. When exporting. I have been editting the rest of the table I need manually bt editting them in notepad but the table 4 columns and has 170,000 entries. Example: I need to change entires like these 170,000 times INSERT INTO `nuke_bbsearch_wordmatch` VALUES (4146, 192, 0); to INSERT INTO `phpbb_search_wordmatch` VALUES (4146, 192, 0);
View Replies !
Failing To Import An Export From PhpMyAdmin
My hosting company has MySQL 5 and so do I on my local Fedora Core 5 Linux machine. However, when I import a dump file from their enviornment into mine, I get this error ERROR 1064 (42000) at line 21428: 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 'TABLE `EC_SAVED_ORDER_LINE_ITEMS` ADD CONSTRAINT `FK1_SAVED_ORDER_LINE_ITEMS` ' at line 3 This is the line (and those around it) from my file. It is complaining about the first line. What is the problem? Code:
View Replies !
PhpMyAdmin Export With Temporary Tables
I have a multi-statement sql script that creates two temporary tables and then runs a query on them. The query runs fine.However, I want to use phpMyAdmin to export the data to .csv format.The export gui only runs the select statement, not the create temporary table statements,so when it tries to select from the temporary tables it does not find them. What do I have to do to get this functionality to work (besides using persistant tables).
View Replies !
PhpMyAdmin Export -> Mysql Import
I have an mysql database that I can export from phpMyAdmin but I'm having trouble importing it into a local mysql instance. I can export the file as sql (several SQL compatibility modes), but when I try to import it using mysql my_databse_name < exported_php_myadmin_file.sql -u root -p Errors like: ERROR 1062 (23000) at line 45: Duplicate entry '1' for key 1 In phpMyAdmin, if I select the database of interest and then choose "export", I get the "View dump (schema) of database" view. From there I can save the file as any one of several different SQL compatibility modes: NONE, ANSI...MYSQL323, MYSQL40..., TRADITIONAL I tried exporting as several of these that I thought might work, but all fail at the command line import above.
View Replies !
Multibyte Language Data Export From PhpMyAdmin
Hope this is the right forum for this post. I'm working on a couple of sites that are in Japanese. I have set database's default encoding to UTF-8 and all the tables' collation is set to utf8_general_ci. This set up is workking fine as far as the site is concerned. However when I export the data from in SQL or CSV file it the Japanese text parts turn into something illegible.
View Replies !
PhpMyAdmin File Export Creates Squares !
I'm doing an SQL dump using phpMyAdmin where I leave all the standard settings and choose 'save as file' with no compression. However when I open the downloaded file all does not look well ! It's full of squares and I cannot import it anywhere because of syntax errors. I tried the phpMyAdmin documentation but there's no search and the Wiki on their site is practically empty. Any ideas where the squares are coming from ??
View Replies !
Phpmyadmin Export Problem With Order Of Table Creation
This is a phpmyadmin question more than anything, but I hope someone may be able to help. When creating a dump of a normalized database with 26 innodb tables, phpmyadmin export seems to only export the table structure/data in alphabetical order, rather than FK contraint order. Thus when importing the dump of the data, I need to manually go through and select the order to make sure the constraints hold up. Has anyone else seen this? I'm running phpmyadmin 2.5.4 on both linux and win xp.
View Replies !
How To Export Data From Phpmyadmin Into LOAD DATA INFILE Syntax
I have a table with a lot of data (several millions of records). I would like to export the data using phpmyadmin into a SQL file. I know that phpmyadmin has an export feature. I need the exported data to be in the LOAD DATA INFILE format after the export. Is there a way to achieve this? Regular export uses the INSERT INTO ... syntax for the data.
View Replies !
PhpMyAdmin: Export Table - Import Table
I find in PhpMyAdmin where I can export a table, however when I click the "GO" button where is the exported file? And how do I name it? When I go to import a table I can select the file to import and that makes sense. My goal is to take a table from one database and use it in another database, so I hope I am in the correct place?
View Replies !
5.X Optimization Tips
I have a unix box that has 32G of RAM. I want to optimize MySQL because right now it's pretty slow using the default settings. Do you have any configuration settings that you recommend to maximize MySQL [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 512M max_allowed_packet = 16M table_cache = 512 sort_buffer_size = 512M read_buffer_size = 8M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 512M thread_cache_size = 8 query_cache_size = 64M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 16 # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 2G innodb_additional_mem_pool_size = 20M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 16M [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M Edited 1 time(s). Last edit at 01/26/2009 03:46PM by John Doe.
View Replies !
Performance Tips
I'm working with 5.0.29 and have a model with 35 tables. When I click a table for modification or move the element into de model my computer lost performance and WB take a while to respond (enought to desesperate in daily use). Have a tips page to check posible reasons or tips for a better performance?. My pc have a Intel dual CPU E210 2GHz and 2MB RAM, then I thinks that is enougth for run a normal program. Please send me tips. System info from MySQL WM MySQL Workbench OSS for Windows version 5.0.29 Cairo Version: 1.5.12 Rendering Mode: GDI Rendering OpenGL Driver Version: Not Detected OS: Windows XP CPU: 2x Intel(R) Pentium(R) Dual CPU E2180 @ 2.00GHz, 2.0 GB RAM Video adapter info: Adapter type: NVIDIA GeForce 7300 GT Chip Type: GeForce 7300 GT BIOS String: Version 5.73.22.62.72 Video Memory: 262144 KB
View Replies !
MySQL Tips And Tricks
If you have an interesting MySQL Tip or Trick, I would be interested in posting it. The following link (GPU Free Documentation License) contains advice on such standard features as "showing a process list" and "killing a particular" process, how to do transactions, interface with Perl, Java and C, remove duplicates entries in a table, rollback transactions, merge multiple tables into one, update foreign keys in a multi-user environment, create a UUID (same as GUID for MSSQL users), monitor connections with "tcpdump", create a C or C++ API, encrypt and decrypt data (des_decrypt, and des_encrypt), generate a virtual row count, monitor and show all "select, insert, create etc. statements" with binlogs, and using Spatial Extensions like SELECT X(GeomFromText('Point(5.02 7.9)')); with explanations. Code:
View Replies !
Best Options For Database Export
In exporting the data from my live db, I am wondering what would be the best dump options to use. My problem is that I am finding the import on my localhost to be slower than expected. Are their recommended options for the export that I could use in order to make the import quicker?
View Replies !
Tips For Improving The Performance Of Mysql
I really hope I can get some general advice and suggestions about how I can improve the performance of our mysql server. We have a dedicated webhost running a number of quite highly used websites. They all use mysql quite heavily at times. Recently we've had complete mysql gridlock and had to restart the server. What are the steps I can take to improve performance? This is what i've done so far. 1. Optimise queries - i've been checking the queries we are running and trying to cache the results in a session rather than generating all the time. I've also installed something called mytop which is basically the same as the standard unix top command, except for mysql processlist and shows you the queries currently running and the time elapsed. 2. I have run explain on my queries and put in indexes all over the place to try and make lookups quicker. Things i'm considering doing 1. Reducing the amount of data in the database and perhaps rotating the tables slightly, our biggest tables are approaching 2 million records and linking these together is i'm sure causing some of the problem, especially when we have to run a SUM query on them, even with good indexes. 2. Moving the database off the webserver and onto its own dedicated mysql server. 3. Load balancing the database and somehow clustering more than one database server, but this would cause serious headaches so not seriously considering unless it was thought the best solution. 4. Upgrading mysql... We are currently running version 4.0.27, should we upgrade to 5? Will this give us performance boost? 5. Re-compiling... After a google search I discovered that potentially we could recompile mysql with a better options set and perhaps improve performance. 6. Buying a book on mysql optimisation Any suggestions?
View Replies !
Any Tips For Improving This Query Time?
Is there anything I can do to improve execution time for this query? mysql> SELECT webpageUrl, webpageName, COUNT(*) AS `count` FROM _1_log GROUP BY webpageUrl ORDER BY `count` DESC LIMIT 5; +---------------------------------------------------------+---------------------------------------------------------------+-------+ | webpageUrl | webpageName | count | +---------------------------------------------------------+---------------------------------------------------------------+-------+ | http://www.w3counter.com/ | W3Counter - Free Web Counter, Web Stats, Live Analytics | 2490 | | http://www.w3counter.com/stats/visitors/4071/day/1000/0 | Visitors Overview - www.pitbullmortgageschool.com/ | 1496 | | http://www.w3counter.com/stats/4071/v_daily | W3Counter - Visitors by Date - www.pitbullmortgageschool.com/ | 1130 | | http://www.w3counter.com/stats/ | Your Websites | 1124 | | http://www.w3counter.com/stats/websites | W3Counter - Websites | 800 | +---------------------------------------------------------+---------------------------------------------------------------+-------+ 5 rows in set (0.88 sec) mysql> explain SELECT webpageUrl, webpageName, COUNT(*) AS `count` FROM _1_log GROUP BY webpageUrl ORDER BY `count` DESC LIMIT 5; +----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+ | 1 | SIMPLE | _1_log | ALL | NULL | NULL | NULL | NULL | 29422 | Using temporary; Using filesort | +----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+ 1 row in set (0.00 sec) It averages 0.5-3 seconds throughout the day (it's run often enough that it shows up in my slow query log multiple times an hour for exceeding 1 second). Adding a partial index on webpageUrl doesn't affect anything, and URLs can be quite long. Is there anything I can do to speed up that query? The table is updated too often to maintain separate url/count tables -- can't spare the resources.
View Replies !
2 Databases - Export Certain Parts To Another Database
I have a database with all users profile information, but i would like to export ceritain parts into another database, and the layout and colum names in the second database will be differnt. How would i do this on all users, then when a new users signs up, it will happen to them?
View Replies !
Export/Import MySQL Database
I am writing an application usig PHP/MySQL on my local development box. Now that the app is complete, I am ready to move it into production and live on the domain. What is the best way to go about moving that database from the local MySQL platform onto my hosting?Should I export it into a SQL file and rebuilt it on the host?
View Replies !
How To Export Entire Database Via Code?
I have built a custom cms which allows them to export their data, which is fine. However, I want them to be able to export the entire database, including schema, create table statements etc, as a .sql file, in much the same way as phpmyadmin allows you to. However, I want to do it in code (c#), so all they have to do is click a button 'backup database' within the cms and save the .sql somewhere.
View Replies !
Export MySQL Database To Word Document
This is something I've seen on forums like Invision Power Board, there is an option to save the thread as a document. What I have is a PHP/MySQL system that has contact details for around 2000 people, far too many to have in one print view page. The entries are limited to 100 per page, after a 100 entries the PHP script creates additional pages by calling the next 100 entries with the $_GET function. For the entire thing there's about 20 pages. What i want to do is have a simply link option that can be clicked which pulls all the records from the database and exports them as a Word document which you can save to your desktop and preferrably have the ability to define the formatting of the page within the PHP so you can define what text will be bold how it will be aligned, ow the records will be ordered etc. Does anyone know of a way to do this?
View Replies !
Export Updates Made In Excel Via Odbc To MySql Database?
I recently was able to import a table from MySql into Excel using the ODBC driver and trying a couple different techniques until it worked. I am now wondering if there is a way of exporting changes made in excel so that they are reflected back in the MySQL database. I also got the connection to work in access and am aware that what I am asking can be accomplished by using access in combination with excel, just would like to know if everything can be done in excel.
View Replies !
A New Database In Phpmyadmin
I want to creat a new database in phpmyadmin like this: CREATE TABLE users ( username varchar(30), password varchar(32)); But I only can put this commands in other already created. How can I creat one new like this?
View Replies !
Phpmyadmin And Its Mysql Database
I notice in phpmyadmin that there is a database called mysql. Am I correct in guessing that this is where phpmyadmin stores the database users and passwords to connect to the databases? If so, how can I access this database with a php/mysql script? The reason I ask is that we have a live dvd that installs linux with many services such as free radius and phpmyadmin up and running. problem is that the usernames and passwords are always the same on every machine. Is this a security issue if we cannot let the user update the passwords, ie can some bad guy hack this if he knows we all have the same user name and password?
View Replies !
Phpmyadmin :: Import Access Database
i have a access database that wich i want to convert to mysql. first, using Access i export it to .txt after that i try to import it using phpmyadmin and it keeps saying "file could not be read" i did the same with an excel file converting it first to CSV (comma separeted value) and then when importing it to phpmyadmin i get the same error.
View Replies !
Updating Database Records Through PhpMyAdmin
I have recently been asked to make SEO changes to a site built with PHP and MySQL. The site is controlled with the webprogram 'cpanel' which is where i access the phpMyAdmin from. I am not sure how to update records so that I can make changes to the page titles and meta tags. When I type in the code and press 'go' I keep getting these errors. import.php: Missing parameter: import_type (FAQ 2.8) import.php: Missing parameter: format (FAQ 2.8) I am not really sure if I am missing a major step as I have never atmysqlted any changes before.
View Replies !
Importing Member Database Via PHPMyAdmin
I have a member database which contains usernames, password, names, contact info, etc. I have been able to successfully import it into MySQL via PHPMyAdmin, my question is how do I turn the passwords column into a MD5 value for each of the 5215 rows? The passwords in the spreadsheet are their raw values. I need them to be MD5 values. Is there something that I can do in PHPMyAdmin to edit the passwords column to turn them into MD5 values or is there something that I should do with the spreadsheet before importing it?
View Replies !
Importing Database Through Phpmyadmin Error
I have been running a script on another hosting service for a while and recently decided to change hosts. Before moving across I did a database backup and got myself a nice mysql file ready to import at my new host - DreamHost. I installed the script, went into phpmyadmin and tried importing it, but I'm faced with this error message: SQL query: CREATE TABLE offers( id int( 11 ) NOT NULL AUTO_INCREMENT , name text NOT NULL , image text NOT NULL , url text NOT NULL , description text NOT NULL , SHOW tinyint( 4 ) NOT NULL default Ƈ', PRIMARY KEY ( id ) ) TYPE = MYISAM ; MySQL said: Documentation #1064 - 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 'show tinyint(4) NOT NULL default Ƈ', PRIMARY KEY (id) ) TYPE=MyISAM' at line 7 I don't have much experience with mysql stuff, so am a bit confused. Am I doing something wrong?
View Replies !
Phpmyadmin Mysqldump And Wordpress Database
Hey I have a wordpress database on my vps server that i want to move to my shared server account. The database is originally on the vps which uses phpmyadmin 2.8.2.4, so I go to export and export the database. Then I log onto my shared server that has phpmyadmin 2.6.4, and I import tha wordpress database. I get this error in return:
View Replies !
PhpMyAdmin Unable To Access New Database After Creation
I am a bit confused about something in phpMyAdmin. By default, there are two databases: test & mysql. I am able to access these via a php script w/o any problems. However, when I create a new database I am unable to connect to it and more disturbing is the fact that the newly created dB does not appear in the drop-down menu (LH side) along with the two default databases. I know the new db exists because if I try to create a new db with the same name, it says it already exists. Any idea what is going on here?
View Replies !
How Do You Connect A Phpmyadmin Database To A Website Page
I've worked with websites for a few years, but have never been involved with databases. I've managed to create a database in my phpMyAdmin - the versions are below: phpMyAdmin - 2.8.0.2 * MySQL client version: 4.1.10 * Used PHP extensions: mysql After I created the database, it gave me some code below, but am not sure what the next few steps are: PHP $dbh=mysql_connect ("localhost", "practice_saz", "<PASSWORD HERE>") or die ('I cannot connect to the database because: ' . mysql_error()); mysql_select_db ("practice_test");
View Replies !
MySQL Database Table - PhpMyAdmin - FLUSH
We are having problems with our banner ads - clicks deliver you to our homepage rather than the assigned URL. Our banners run on php/mysql based software and I believe the problem is a build up of logs and that cronjobs set to deal with them are not functioning as they should. Knowing very little about such things and awaiting tech support to look into it and resolve the issue I poked around the database and noticed the "Table Maintenance" area and a "FLUSH" link. MySQL says this clears the cache. I'm tempted to flush the tables I think are having problems to see if that resolves it temporarily but don't want to mess anything up. Any thoughts on all this? Will "FLUSH" do anything I might regret?
View Replies !
PHPMyAdmin :: Database Not Showing On The Left Side Menu
I have mysql and php loaded on my apache server. (In house server, Linux). I created a new database by typing in the name in create a new database box in PHPMyAdmin. Well the first time I did that it created the database and went to the page where I add tables, which I did. Now, when I load phpmyAdmin again the database does not show up on the left hand side where all the other databases are listed. What gives? But when I go back again to recreate the Database I get an error saying database already exists.
View Replies !
Export To CSV
I need to extract a query result to CSV. Currently I have to get the hosting provider to run the query for me "SELECT ... INTO OUTFILE ..." and then email me the file. I can only access the mysql server via the command line utilities from another remote linux box (our webserver). Ideally I would run a command on the web server which would create the file locally (on the web server) so that I can then grab it via ftp. Can I do this with any of the mysql utils? If not I can just write a script to run the query and output it in csv format to a file but it seems silly to do that if one of the utils already supports this.
View Replies !
Export DB
i am attempting to export a DB so i can migrate a site from one server to another. However everytime I export the DB and load it into the new one, the new one says 0 bytes / queries.
View Replies !
|
TRACKER
