All Questions
64 questions
1
vote
1
answer
68
views
How to import MySQL RAW Database files without connection error
unfortunately I did not create a 'dump' file with the backup (Data Export) process on MySQL Workbench 8.0.26.
When I copy the old database files to the new Data directory, the server fails to start ...
0
votes
0
answers
40
views
import db and users from mysql 5
There's a working Mysql 5 database that I want to import into a MariaDB on another machine, including tables, data, users and privileges.
Is There a pretty simple way to do that? I'm not so expert in ...
- 1
0
votes
0
answers
15
views
What is a good mechanism to get production database data into other environments along with de identifying it
I am sure this is a solved problem and many people would have a great process already in place.
How do I easily get data in my production server to other environments like staging and testing. We ...
1
vote
1
answer
622
views
TablePlus exporting without error but can not import it back
Exporting a table with 22,245 rows using TablePlus, after which TablePlus tells me "Export completed successfully".
If I create a new database and try to import the file I just exported, I ...
- 141
1
vote
0
answers
197
views
How to export MySQL in a format accepted by DigitalOcean database clusters?
I am struggling with importing my databases to Digital Ocean database clusters. They have a setting that requires the primary key to be defined in the CREATE TABLE statement, but all my tools (...
- 141
1
vote
0
answers
19
views
Incorrect values after export xml from DB for table bigint(20)
This below is part of my script code for export data from table to xml.
query:
$query = "SELECT p.id, p.slug, p.sku, p.price, p.vat_rate, p.stock, p.brand, p.ean, p.gender,
pd.title, pd....
1
vote
1
answer
3k
views
Saving output from INNER JOIN into a new table in MySQL
So I have a query in MySQL with an INNER JOIN which goes as follows:
SELECT * FROM `distributors` INNER JOIN `manufacturers` WHERE distributors.manufacturer_id = manufacturers.manufacturer_id
How do ...
- 123
0
votes
1
answer
635
views
Exporting FoxPro DBF Files
I am attempting to export data stored by a client's Visual FoxPro database to be used in a MySQL context. The data given is a folder of .dbf, .cdx, and .tbk files, as well as a few .dct, .dcx, and ....
- 101
1
vote
1
answer
495
views
How do I bulk export data from an RDBMS without killing performance?
I'm building an app that lets the user export their data. When they click the Export button, we need to do a SELECT * WHERE user_id =, save it to a CSV file, and download it. There may be hundreds of ...
- 113
0
votes
1
answer
82
views
Out of disk space while dropping columns from a mysql table
So, my setup is a little weird. I've got a laptop with 250 GB of memory and a table with roughly 100 million rows (InnoDB), which occupies around 130 GB of memory.
The thing is, this table includes ...
- 111
0
votes
0
answers
374
views
How to move/copy a CSV file saved in /var/lib/mysql-files without root on a shared hosting server
I'm working with a server that I'm helping someone with to extract data from their database. The database server is no a shared hosting plan as is very very slow to run queries. Query that takes 3 ...
- 111
0
votes
2
answers
4k
views
MySQL Dump only views, triggers, events and procedures
how are you?
I need to generate a file with all triggers, events, procedures from a server's databases and tried the two commands below
mysqldump -u root -p --all-databases --host=127.0.0.1 --no-data
...
2
votes
3
answers
6k
views
Export and import a table without a specific column
I'm trying to export a table with an Auto Increment rowid column and then import it in another server.
Considering that the column is primary key and can't accept duplicate keys if i import rows after ...
- 149
0
votes
0
answers
479
views
Force double quotes in phpMyAdmin export dump
I have a varchar column in my table but when I export it in phpMyAdmin (4.6.6deb5ubuntu0.5), the values are quoted with single quotes. How do I force double quotes? I don't see a setting.
CREATE TABLE ...
- 101
0
votes
1
answer
875
views
MySQL: carriage-return in query breaks my import
I use simple echo "select * from tab" | mysql --batch --quick --skip-column-names .... > /tmp/file from mysql to get data.
Then, import to postgres using \copy tab FROM /tmp/file ...
- 533
0
votes
2
answers
2k
views
mysqlimport imports NULL values unwantedly as int 0
I would like to understand how to create a decent back-up file by using mysql and mysqlimport. I know that I can use mysqldump and mysql, but I'm learning to use the combination: mysql and mysqlimport....
- 452
0
votes
1
answer
259
views
Use data from Mysql to ElasticSearch with Logstash
I'm using logstash for use my mysql database in ElasticSearch
My conf is the next.
input {
jdbc {
jdbc_connection_string => "jdbc:mysql://[ip]:3306/nextline_dev"
jdbc_user =&...
- 121
0
votes
1
answer
348
views
Copy Table from one MySQL server to another
Is there a way to copy or export a large table from MySQL server to postgres without affecting servers performance.
I mean the table is very large and using mysqldump or any select query has bad ...
- 103
-1
votes
1
answer
825
views
MySQL user export PASSWORD is showing up as <secret>
I am trying to export all users from old MySQL server to new.
All the methods that I tried shows the PASSWORD as <secret>
pt-show-grants
phpMyAdmin 4.8.1
https://serverfault.com/questions/...
0
votes
1
answer
293
views
Is there any scenario where a exported (.sql) database dump could be bigger in size, than the database itself?
I'm a developer in a local company, and I'm currently working on a project which involves migrating a website to my company's server, from some other hosting provider.
For some reason this other ...
user149325
0
votes
2
answers
103
views
Filesystem copying inactive DBs on active MySQL server
My question is related to this question and answer:
Copying datadir directory enough for backing up a database with InnoDB tables?
I want to use a filesystem copy on a whole DB without shutting down ...
- 101
3
votes
1
answer
1k
views
Partial copy of huge database
I have started working in a project which has a big database, around 300GB. For security reasons I can not access database from my local web app. So I need to copy last 100,000 from each table.
To ...
- 181
2
votes
1
answer
756
views
Storing the last updated date of a row in a different table
I need to do a daily export of all of the new rows or updated rows from a legacy database into the new database.
The problem I am running into is that not all of the tables on the legacy database ...
0
votes
2
answers
2k
views
MySQL Import & Replace via CSV
I have a MySQL database table named items with 10,000+ records. Of these records, I would like to export only records where item_isbn IS NULL.
SELECT * FROM Items WHERE item_isbn IS NULL produces a ...
- 231
0
votes
1
answer
2k
views
Any program / command to see a preview of .sql dump file?
If I have [multiple] .sql dump files, is there a command or tool that would allow me to see a preview of the file ?
e.g: db-preview backup.sql
tables (40)
----------
- users (1200)
-...
- 204
1
vote
1
answer
1k
views
Dump MySQL table to csv file (utf8mb4_unicode_ci)
I have MySQL table with columns (character-set: utf8, collation: utf8mb4_unicode_ci) to be exported to a .csv file.
mysqldump -u *** -p -t -T/tmp/ dbname tablename --default-character-set=utf8mb4
...
- 11
0
votes
1
answer
422
views
How to transform results of a select statement formatted as XML?
When I run the command
mysql EvePlanataryExpenses --xml -uuser -p -e "SELECT sTransactionDateTime, SUM(sQuantity * sPrice) as 'profit' FROM sell GROUP BY sMonth;" |sed '/^\s*$/d'
I get the following ...
3
votes
2
answers
4k
views
mysql export with partial data
How do I export the structure of a mysql database I have, keeping the structure for all the tables but only exporting the data for some of the tables?
After exporting how do I import it into another ...
- 213
1
vote
0
answers
157
views
MySQL dump only has SET commands, no data
I've done an export of a Wordpress database. The MySQL file that was exported only contains the Host, Database name, server version and SET commands.
Pastebin: http://pastebin.com/pFgdPDRa
Another ...
- 11
1
vote
1
answer
352
views
I'm seeing inconsisntecies in table data after exporting from one server and importing to another, why is this?
I'm quite new to the process of importing/exporting databases.
I'm using Maria DB, and HeidiSQL to run queries, etc.
I exported an entire database from our dev server, and loaded it into our live ...
- 181
1
vote
0
answers
3k
views
How to export views and import it in other DB?
I am trying to export the database and everything is fine but I am not able to export the VIEWS.
Is it possible to export the views from sql queries and import it?
- 141
-1
votes
1
answer
2k
views
How to fix invalid length in all tables of MS SQL Server 2008 (R2) database
Task is simple: I want to export all SQL Server database tables using SQL Server Management Studio 2008 R2 to MySQL (or any other format that MySQL can take, e.g Excel, CSV).
I have tried the ...
0
votes
1
answer
820
views
How to import a single table in Windows environment
I am running Windows 7 with xampp. I can easily import a database with the following command:
mysql -u user -p database < backup.sql
The problem is that I have a database around 8.5 GB, which ...
- 11
7
votes
1
answer
2k
views
Nothing to do, no schemas or tables selected on export
I'm using MySql Workbench 6.3.6, on Windows 10, which has all updates.
I have previously been able to get a data dump from this database. Now I can't get any from any of the schemas I have open in ...
user83619
40
votes
4
answers
24k
views
mysqldump vs mysqlpump
I just heard about mysqlpump - it looks like it was released with MySQL 5.7, however MySQL 5.7 still includes mysqldump. They're both backup programs, but could anyone summarize the main differences? ...
- 541
3
votes
2
answers
4k
views
Best way to export excel table into MySQL db
I'm trying to export an Excel table which contains about 400 records of HR stuff (e.g. name, surname, age, job, ecc.) into a MySQL db.
I'm actually using mysql for excel and started reading a db book,...
- 31
1
vote
2
answers
1k
views
Sending a database dump
Quite new to db admin here and I have a quick question.
I am collaborating with another colleague off site and they would like me to send them a dump of our (1.5GB sql db) database via email as they ...
- 231
1
vote
2
answers
19k
views
How to export an Access database and import to MySQL?
I am working on a Java project that sources data from an MS Access database, but I want to use MySQL because I have found it to be very easy to manage. How can I export an Access database that has ...
- 11
1
vote
1
answer
379
views
How can I append new data from a particular mySQL table to an excel spreadsheet?
We have a database trigger that automatically adds new clients to a table when they first contact the server, it takes a number of unique identifiers that the client provides on first contact and ...
- 181
0
votes
3
answers
5k
views
MySQL Exporting a query to text file via Cron Job
I'm in the beginning stages of hacking together code that syncs two completely different pieces of software/webware. I'm looking for a way to run a SQL query and output the results to a text file. I'd ...
- 111
1
vote
2
answers
773
views
How to export image filenames from Wordpress database?
I have a Wordpress install that's using about 10,000 images. I'd like to export all filenames used in Wordpress to a file, filenames.log
filenames.log would look like this:
car.jpg
boat.jpg
milf.jpg
...
- 13
2
votes
1
answer
810
views
Exported CSV from MySQL doesn't appear in specified directory
I'm trying to figure out the syntax to export the results of a MySQL query to the desktop as a CSV file via SSH. Here's what I'm trying:
SELECT * INTO OUTFILE 'C:\Users\Jim Smith\Desktop\zyzyz.csv'
...
- 21
5
votes
4
answers
22k
views
How can I export just the structure of a MySQL database?
I use Ubuntu 14.10 and PhpMyAdmin.
I have a 'big' MySQL database, the size is 900MB. I need just the structure of this database, to use it on my local machine. How can I export the structure only?
- 71
8
votes
2
answers
8k
views
Mysqldump from production database, every morning
Our Db is about 2GB in size.
We run a scheduled job at 3am every morning, when traffic is pretty low, where we export the whole db and sftp it off site.
Is it bad practice to do this on a production ...
- 363
2
votes
3
answers
3k
views
Creating a MySQL db from an Informix database
There is an existing database that was created in Informix. I would like to export it and then import it into MySQL.
I tried to export it using dbschema and it created the dbase.sql file and several ...
- 21
0
votes
1
answer
6k
views
mysql dump a table to remote host [closed]
i want to dump a table from localhost host (with public ip), here is the command so far, the command will be run on localhost
mysqldump --opt --host localhost --user=username --password=password ...
- 109
0
votes
1
answer
744
views
How can I normalize this database?
I've got legacy ecommerce database, which is awfully designed and I need to move all products from it to new solution. But I stuck with how product properties stored. So it basically has tens of ...
- 111
0
votes
2
answers
2k
views
I can't import my database into mysql
I have a website based on drupal and I tried to export its database via both Backup wizard and phpmyadmin. the database was exported but I can't import it on my localhost database.this the error
...
- 101
3
votes
1
answer
3k
views
MySQL - Export two columns from large table without causing a lock
I am a sysadmin who doesn't do a ton of dba stuff. For a project, I have access to a production server--which I am not the sysadmin for--with an enormous 40,000,000 row >10 GB MySQL InnoDB table. I ...
- 133
3
votes
1
answer
6k
views
Using Workbench to export. Views no longer work
I'm using Workbench 5.2 from my production DB machine (Ubuntu Linux). I've been trying to export. First try.. I had the entire schema selected including all tables and views in the right hand window....
- 395