Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

Filter by
Sorted by
Tagged with
3
votes
2answers
5k views

Use mysqldump to backup table with a new table name

Is there a way to back up a table using mysqldump so the table that is being backed up can have a new name in the sql script created by mysqldump. So that If I back up the table 'test_table' like so. ...
0
votes
1answer
74 views

Mysqldump backup size issue - 5.6

I'm using MySQL 5.6 with about 5 databases on linux. I have scheduled daily full backups using mysqldump. All databases use InnoDB engine. What I observe is that the backup size seems to vary ...
1
vote
2answers
35 views

MySQL restore dump takes too long to complete, how DBAs deal/replicate large databases?

Due to coronavirus my company is forced to scale down and cut costs. Long story short, I went the route of least disruption I set the original database server (that's going to be turned off for good ...
-1
votes
0answers
23 views

mysqldump options mariadb [closed]

i see my previous dba script for mysqldump mysqldump --defaults-extra-file=/etc/my.cnf.d/mysqldump.cnf -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=...
0
votes
1answer
647 views

Error: ERROR 1047 (08S01) at line 6955: WSREP has not yet prepared node for application use

While restoring a dump from a CloudSQL instance into a node of a Percona xtradb cluster, I am getting this error. The line 6955 in the .sql dump file is in bold below: DROP TABLE IF EXISTS `...
1
vote
1answer
197 views

Importing a mysqldump from MySQL 5.0.95 to MariaDB 10.0.36: Some tables incomplete

I have an ancient MySQL setup (5.0.95) that I'm trying to shut down. We've mysqldumped the databases we need, and we're trying to import them into MariaDB (10.0.36). We're running into some odd quirks,...
0
votes
1answer
20 views

How to dump / restore mysql.user [closed]

I'm able to dump the table of users from one server with mysqldump -u root -p -h [host address] mysql user > user_table_dump.sql but how can I restore these users into my localhost enviroment? I ...
0
votes
0answers
26 views

MySql uncomplated Upgrade Error

I faced showed bellow error every apt upgrade. An mysql server frequently down. I try backup my database with mysqlpump, this backup not contain tables values. Only table construct. How can I fix this ...
0
votes
1answer
43 views

MariaDB/MySQL: Import from dump (.sql) file remotely via network

After I set my.ini file following the answer by Rolando here : https://dba.stackexchange.com/a/83385/191579 , today I managed to import about 40GB+ sqldump file in around 2-3 hours only. While on a ...
2
votes
1answer
131 views

MySQL replication backup

How to take MySQL Master-Master and Master-Slave replication backup?. I have setup Master-Master replication with a separate slave for each master on Ubuntu. What happens if, I issue or schedule ...
0
votes
2answers
53 views

How import mysqldump .sql file into MySQL 5.7 when exported from MySQL 8

I accidentally installed MySQL 8 and did a ton of work there, creating a lot of data. I then backed up all of the databases using mysqldump. Later, I realized that I needed to downgrade back to MySQL ...
0
votes
1answer
30 views

MySQL: Importing databases from older version

I'm in the process of moving everything from a very old production server to a new one. I'm now trying to copy the MySQL database over to the new system. I have very limited knowledge of MySQL, BTW. ...
2
votes
1answer
813 views

Database size twice as large after mysql dump and re-import

After exporting and re-importing a mysql database, the size of the database on disk appears to be quadrupling in size. Any ideas why this my be? This is occurring when importing the sql dump right ...
0
votes
0answers
21 views

Convert SQL database to a newer version

My new hosting has been having issues importing my wordpress database and are getting errors. The current export is.. -- MySQL dump 10.16 Distrib 10.1.31-MariaDB, for debian-linux-gnu (x86_64) And ...
0
votes
1answer
111 views

Batch query a lot of MySQLdump files

I have a website for an event. I set up a cron job to back up the database every hour. The backup command is mysqldump -u"$DB_USER" -p"$DB_PASS" --database "$DB_NAME" >"${OUTPUT_DIR}/${DB_NAME}.$(...
0
votes
1answer
93 views

Constant MysQL crashes. How to fix?

MysQL is frequently going down. Error logs contain: [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it | 149 occurrences [ERROR] Native table 'performance_schema'....
1
vote
1answer
159 views

Grabbing SQL Dump of Master DB Without Downtime

I'm curious whether downtime will be necessary to grab a SQL dump of my master database. Right now, I'm in the process of rebuilding my one slave. There is actually only one database from master that ...
0
votes
1answer
203 views

Why do I have two collations in mysqldump, under stored procs?

When I run mysqldump --routines I'm seeing multiple collations for my stored procs. Neither my DB nor columns use utf8mb (I understand the benefits of utf8mb over utf8 but let's leave it for now). /*...
0
votes
1answer
109 views

mysqldump is ignoring option '--databases' due to invalid value

I have this MySQL database named capsule_corporation_database. Then, I have this my.cnf file in my home directory: ~/.my.cnf [client] host=localhost user=trunks password=mai database=...
0
votes
1answer
498 views

mysqldump from AWS RDS slowing down

Im taking a db backup from AWS RDS (MySQL) slave from an EC2 (m5.large - 2c/8GB) in same AZ using - mysqldump -hxx -u xx -p --extended-insert --single-transaction --max_allowed_packet=1G db > ...
-2
votes
0answers
14 views

Exporting Mysql binary logs file data to txt file

I want to copy data of binary file to .txt file [ec2-user@ip-172-31-28-70 mysql]$ sudo mysqlbinlog tajamul.000006 > /var/www/tajamul.txt It give me the following error. -bash: /var/www/...
0
votes
1answer
163 views

mysqldump vs mysqlbackup

I am using MySQL v5.6.x. and using mysqldump takes a long time to dump and restore the db and does not seem practical for DB size larger than 50GB. It looks like the mysqlbackup utility that comes ...
0
votes
1answer
156 views

MySql Moving from development to production into a different user

I have a project that need to be moved to production (for the first time). The database includes few tens of tables and procedures/functions. They were all created under the ROOT user (yes, bad ...
0
votes
1answer
21 views

MySQLdump Exclude master-data

Is it possible to exclude the master data from a mysql dump? From the manual it seems like the only options are commented out or present. I don't need the data at all because it is going to a static ...
1
vote
3answers
129 views

mysql innodb dump restore slows down after upgrade mysql 5.7 to 8.0

I'm struggeling the last two days with the following issue: Restoring a ~1GB database dump with only innodb tables on a mysql 5.7 server takes about 5 minutes. After upgrading the same server to ...
2
votes
1answer
158 views

Proper Way to Remove Mysql Tables

I was going to ask why mysqldump was trying to write dropped tables but I found that answer from this question, How do you remove reported non existing tables from mysql?. Removing the .frm files ...
0
votes
1answer
86 views

Restore of database using Mysql DUmp

How would i verify that whatever the backup i have taken is correctly restored or not. EG: I am taking backup on live system where inserts are going on.backup size is around 50 GB.I have started the ...
-1
votes
1answer
22 views

-bash: PURGE: command not found

I want to delete binary log files following are my binary log files binary2.000001 binary2.000002 i want to delete binary2.000002 mysql> PURGE BINARY LOGS TO 'binary2.000002'; It shows me:...
1
vote
3answers
30 views

mysqldump interrupts always with same file size

mysqldump -h127.0.0.1 -u user -p'password' -t db mail --where 'status=3' > /var/www/project/storage/web/dump2.sql I try it on ubuntu 18.04, mysql 5.7.29. And the file is always 141.8MB. Data just ...
0
votes
1answer
285 views

mysql diff and add diff records into new database

I have two database on two different hosts: host A has database A and table A and host B has database B and table B and now "host A/database A/table A" table A contains records with primary key 1 ...
2
votes
0answers
100 views

What kind of complications would I face downgrading from MySQL 8 to MySQL 5?

Let's assume I wanted to downgrade to from MySQL 8 to MySQL 5.7. I know this isn't supported, but what kind of complications am I likely to encounter. The docs say, The only supported alternative ...
0
votes
1answer
119 views

Can we use MariaDB mysqldump CLI to take dump from MySQL Community version?

MySQL server version: mysqld Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL)) MariaDB mysqldump CLI version: mysqldump Ver 10.17 Distrib 10.3.12-MariaDB, for Linux (x86_64) I am ...
3
votes
2answers
3k views

fix `mysqldump: Error 2013: Lost connection` on “cloud” server

I have 6 months of backups left to perform and a single day can take up 200MB in raw mysqldump --extended-insert=false --complete-insert form. If I try to dump more than a day worth of data I get ...
0
votes
1answer
200 views

How speed up mysql dump without data?

There are a lot of questions (1,2,3) about speed up MySql restoring. But all of them speed up data import. Is there a way to speed up schema tables creation? For example dump iundepended table in ...
0
votes
1answer
130 views

mysqldump can write to /tmp and /var/tmp but nowhere else

I have various mysql databases on different servers and at different versions. I'm using the same mysqldump command across Linux versions without issue except for this new Red Hat 7.6 server. Command:...
0
votes
2answers
52 views

How to dump a large database?

I try to take a backup of a huge MySQL database via mysqldump. The database has millions of rows with a total size of 1.3TB. CREATE TABLE PageContents ( ID int(11) unsigned, Added date, Text ...
1
vote
2answers
399 views

Determine size of mysqldump prior to dumping

This question is a followup on this previous question. Q: How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump? I have tried using the most upvoted ...
0
votes
2answers
71 views

How to force mysqldump to produce /*!40101 SET character_set_client = utf8mb4 */;

I am attempting to dump my database, which is set to, and only contains, utf8mb4 and appropriate collations. I use the following line: mysqldump --add-drop-table --add-locks --allow-keywords --...
0
votes
1answer
255 views

mysql database migration of schemas with updated data and structures

I am a newbie with MySQL database dumps and am currently trying to work with data migration between a source schema and a target schema. Assuming the source schema is defined by (...
79
votes
5answers
105k views

MySQL any way to import a huge (32 GB) sql dump faster?

I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual: mysql -uroot dbname < dbname.sql It is taking too long. ...
3
votes
1answer
4k views

How to make mysqldump use /*!40101 SET character_set_client = utf8mf4 */;?

When I use mysqldump to export mysql database, it always produce a dump.sql containing ...some other things... /*!40101 SET character_set_client = utf8*/; ...some other things... This is the ...
0
votes
1answer
2k views

Automysqlbackup fix error “Skipping the data of table mysql.event. Specify the --events option explicitly”

I need to fix the mysqldump warning "-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.". I found that its because of not specifying whether or not to add ...
1
vote
2answers
424 views

Large MYSQL DB on single file

I have a large MYSQL innodb database (115GB) running on single file mode in MySQL server. I NEED to move this to file per table mode to allow me to optimize and reduce the overall DB size. Im ...
218
votes
14answers
183k views

How can I monitor the progress of an import of a large .sql file?

I am importing a 7 GB foobar.sql to restore a table in a local database. $ mysql -h localhost -u root 'my_data' < foobar.sql $ mysql --version /usr/local/mysql/bin/mysql Ver 14.12 Distrib 5.0....
0
votes
1answer
17 views

mismatch between server schema content and exported sql rows [duplicate]

I am using MySQL 5.6.44 within phpMyAdmin on Go Daddy shared hosting and experimenting with some duplicate table data. I am trying to figure out, when I export my table from phpMyAdmin the exported ...
1
vote
1answer
220 views

Import Windows MySQL dump on a Debian Linux server

I created a test database on a Windows computer with MySQL 5.7 and filled it with many data records. Now I created a database dump with mysqldump and wanted to import it on my server (Debian Jessie, ...
0
votes
2answers
716 views

Galera / InnoDB: how can “mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Lock wait timeout exceeded” happen?

Galera 3 node cluster with MariaDB 5.5 error mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Lock wait timeout exceeded; try restarting transaction (1205) backup command mysqldump --...
0
votes
1answer
7k views

mysqldump Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

I am having some difficulty with my MySQL database. It appears the server suffered from a power outage during the night. As a result, MySQL wouldn't start when it reboots. I viewed the error log ...
0
votes
1answer
446 views

Unable to export 8.0.1 MySQL database using MySQL Workbench 8.0

I'm unable to export my 8.0.1 MySQL database using MySQL Workbench 8.0. When I go to perform the export via the "Administration" table, with all the default settings (I didn't change anything ...
0
votes
1answer
157 views

how to dump via mysqldump hugh table into chunks

i have very hugh table , around 100 M records and 100 GB in a dump file , when i try to restore it it to a different DB i get sql query lost connection , i want to try and dump this table into chunks ...

1
2 3 4 5
13