We’re rewarding the question askers & reputations are being recalculated! Read more.
4

MariaDB [theDBName]> SOURCE C:\Users\myUserName\theDBName_data.dump Either of the following should be correct: MariaDB [theDBName]> SOURCE C:\\Users\\myUserName\\theDBName_data.dump MariaDB [theDBName]> SOURCE C:/Users/myUserName/theDBName_data.dump


3

If you take time to run mysqldump, even with all default arguments, you will see that triggers in the dump file are created after all insert statements that populate your tables. So no, there will be no duplication, your data after restoring the dump will be the same as in the source database.


3

Here is the summary of what I understood between both the backup tools. The definition for mysqldump given in manual page The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or ...


3

I have created both linux and windows scripts to restore the specific tables from the dump file: linux (bash script): #!/bin/bash # Where to restore db_host='localhost' db_name='adhoctuts' db_user='root' db_pass='Adhoctuts2018#' dump_file='/root/scripts/dump_ignore.sql' # Associative table list array as source_table=>destination_table pairs declare -...


2

I ran into similar problem importing a 1.2G SQL File and it took about 10 hours on my Laptop (Core i-5 x64 8G RAM) Of course I was multitasking. To find out the progress, I'd query every hour using this query select table_name as "Table", round(((data_length + index_length) / 1024 / 1024), 2) AS "SIZE" from information_schema.TABLES where table_schema = "...


2

SHOW TABLE STATUS; and then run show warnings; will show you what all tables are corrupted.


2

This is for taking a dump of a specific table mysqldump -h 'hostname' -vv -u'user' -p'password' database_name table_name | gzip > table_name.sql.gz


2

The sys schema is not bundled with 10.3, but seems to be planned for 10.5. If you don't use/need it, you can safely remove it: DROP DATABASE sys;. If you do need it, you need to try resolving the issue outlined in the error message. For a start you can do SHOW CREATE VIEW sys.host_summary; to see which tables, views and functions it's referencing, as well as ...


2

Consider writing a script (in your favorite language) to read the dump file one line at a time and send that line to STDOUT, which will be piped into mysql. And add a tiny sleep in the loop that does that. To get fancier, you could fetch Seconds_behind_master to tweak the sleep up or down. (I think at least one of Percona's tools does this.)


2

major Release Candidate General Availability 8.0 2017-09-21 8.0.3 2018-04-19 8.0.11 5.7 2015-04-08 5.7.7 2015-10-21 5.7.9 5.6 2012-09-29 5.6.7 2013-02-05 5.6.10 5.5 2010-09-13 5.5.6 2010-12-03 5.5.8 5.1 2007-09-24 5.1.22 2008-11-14 5.1.30 5.0 2005-09-22 5.0.13 (I don't have MariaDB's GA dates.) You can try to ...


2

By this script you can backup all of mysql's users except root: mysql -BNe "select concat('\'',user,'\'@\'',host,'\'') from mysql.user where user != 'root'" | \ while read uh; do mysql -BNe "show grants for $uh" | sed 's/$/;/; s/\\\\/\\/g'; done > grants.sql So, if you just need to export a specific user like (u_1) mysql -BNe "select concat('\'',user,'\...


2

It can be done for .gz dumps this way: (echo "SET SESSION SQL_LOG_BIN=0;"; gzip -dc dump.sql.gz) | mysql Or if you copy a database right from a remote server: (echo "SET SESSION SQL_LOG_BIN=0;"; mysqldump --host your_host --verbose --compress my_database) | mysql my_database It's implied that a user and a password for both mysqldump and mysql are added ...


1

If you can tolerate a little downtime you can make a snapshot and share the snapshot with the new account. This is a simple and cheap approach though you will be down for the time it takes to make the snapshot, share and then restore it and then point your clients to the new instance. For a solution with much less downtime, if you are using RDS you can ...


1

I ran into the same issue and regarding the "why" this happens, my theory is that it's due to network overhead of opening/closing connections. My theory is that mysqldump needs to open and closes a lot of connections and that's what's slowing it down. I ran it on the machine itself and it took 10 seconds. From another machine, through a VPN, it took like 10 ...


1

Tablenames that are added in (future) version of WordPress should not be a problem when you are scripting this solution If a take a look at my 'test' database: SELECT TABLE_NAME, CASE TABLE_NAME WHEN 'big' THEN "i<10" ELSE "1" END AS filter FROM information_schema.tables WHERE TABLE_SCHEMA='test'; I do see something like this: +----------------...


1

Keeping compressed archives of binlogs is a very common way of achieving point in time recovery for your database, assuming you have a full backup from which you can roll forward. Just copying away the binary logs with a remote transfer procedure (e.g. scp) would be fine- they can just be applied as is; however, it may create inconveniences if the binary ...


1

I raised this with Oracle BUG 96072, and they have responded with "I have analysed it and I consider it a welcome feature request." So I am taking it from that, that this is an oversight, and there is currently no option to use mysqldump to build a multi-source database. So for the moment it seems the options are: Export MySQLdump to a file, then ...


1

In MyISAM, there is no implementation difference between PRIMARY and UNIQUE. (I don't know why it would do what it did.) You should upgrade to InnoDB.


1

--single-transaction is assuring the consistency of the whole instance so yes, across multiple database. It assumes most tables are transactional (i.e. innodb). For myisam tables these are still read locked briefly for the time taken to dump them. The mysqldump snapshot method is in the manual.


1

MySQL dump has only a single where clause which makes the use hard in two tables like you do. One way would be in t1, create a generated column called id that is effectively user_id. ALTER TABLE t1 ADD id UNSIGNED INT GENERATED ALWAYS AS (user_id), ADD INDEX id_key ( id) Adjust type to match the type of user_id. ref: https://dev.mysql.com/doc/refman/5.7/...


1

You could isolate the dump by placing it on a replication slave and stopping the replication before the dump. As table modification isn't transactional, changing the isolation level won't help. If you keep the binary logs (and use --master-data) with mysqldump, you could revert to the previous backup and replay the binary logs. This would be immune to the ...


1

LVM (Logical Volume Management) With that, you can take a snapshot of a disk drive "instantly". This give you a full test database. I would recommend stopping MySQL, do the snapshot, then restart. The snapshot, itself, takes seconds, regardless of how big the disk drive is. (cf "Copy On Write" technology) Unformatuely, LVM needs to be set up when you ...


1

Look at this bugreport: https://bugs.centos.org/view.php?id=15723 Long story short: kernel 3.10.0-957 shows much higher disk util than the previous kernel


1

innodb_log_buffer_size should be no more than 1% of RAM. key_buffer_size should be only 30M, assuming you do not have any active MyISAM tables. Those changes will let you increase innodb_buffer_pool_size to 7G, which might help. Do you have any FULLTEXT or SPATIAL indexes? FOREIGN KEYs? Stored programs (probably not, since you did not ask for them to be ...


1

I believe you should be able to specify a session variable by placing it in a backup.cnf file: max_statement_time=10000 And then using the --defaults-file=backup.cnf parameter to mysqldump. (You can also place user credentials in that file so that you don't have to show the username and password on the command-line, which is a potential security issue.)


1

When dumping / pumping MySQL databases, the output script generates this as a temporary structure first, and later below at the end of the script, it drops this and create the actual view itself, something like this: ...DROP VIEW IF EXISTS myview ... If you end up with the final view still selecting 1s, it would be probably because database name mismatch,...


1

It's really important to keep the conditional-execution comments. But if you absolutely know that the MySQL version that will load the dump is greater or equal to the one that creates it, you can remove the "comment" part with this: sed -r s'#/\*![0-9]{5} ?([^*]*)\*/#\1#'g It will convert lines such as /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; to SET ...


1

one way to help speed the import is to lock the table while importing. use the --add-locks option to mysqldump. mysqldump --add-drop-table --add-locks --database db > db.sql or you could turn on some useful parameters with --opt this turns on a bunch of useful things for the dump. mysqldump --opt --database db > db.sql If you have another storage ...


Only top voted, non community-wiki answers of a minimum length are eligible