134
use --no-tablespaces
see mysqldump-Documentation
mysqldump requires at least the SELECT privilege for dumped tables,
SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES
if the --single-transaction option is not used, and (as of MySQL
8.0.21) PROCESS if the --no-tablespaces option is not used.
Certain options might require other privileges ...
131
Percona's Vadim Tkachenko made this fine Pictorial Representation of InnoDB
You definitely need to change the following
innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0
Why these settings ?
innodb_buffer_pool_size will cache frequently read data
...
66
tl;dr Breaking change introduced in minor MySQL update, use --no-tablespaces option in mysqldump from now on (recommended) or add the global PROCESS privilege to the user running the command.
I experienced the same issue on some of my machines. Not all at once, not on all commands, not on all users.
Why not all at once?
Turns out this is a breaking change ...
61
Based on this blog post
http://ilikekillnerds.com/2014/08/fixing-sequel-pro-sql-encoding-error-for-imported-sql-files/
When importing your database dump, you need to select Western (Mac OS Roman) as the encoding format for the file to import without issue.
48
I know it's really late, but I found this question when I faced the same problem. So, in case anyone need, I found two possible solutions, either:
your user is missing the LOCK privilege, so you should ask your database administrator to grant it to you
run the same mysqldump command, simply adding the --single-transaction flag, eg. mysqldump --single-...
42
You need to collect all the database names into a space delimited list. Use that for mysqldump
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
...
41
You can also import an .sql file as an already connected user to the database manner :
mysql> use your_database_name;
mysql> source file.sql;
38
If you've already started the import, you can execute this command in another window to see the current size of your databases. This can be helpful if you know the total size of the .sql file you're importing.
SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MiB"
FROM information_schema.TABLES GROUP ...
34
You are going to find this shocking, but you only need one major option : --opt
What is --opt ?
This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be ...
30
Best Practices to take MySQL server backup:
MySQL Replication
Setup Replication in MySQL. You will have to setup Master and Slave server. All read-writes to the DB could go to your Slave Server.
Advantage of having Replication is you can take a backup from your slave server without interrupting Master server, Your application will continue to work on Master ...
26
No mysqldump -all-databases does not include all objects
mysqldump --help
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.
So mysqldump with --all-databases only dumps all the databases.
In order to migrate all the databases to a new server, you should take a complete ...
25
You have to use the mysql client to reload
mysql -u root -p -Ddatabasename < /home/databasename_bkup.sql
Another way to reload would be
mysql -u root -p -Ddatabasename
then from the MySQL prompt, do this
mysql> source /home/databasename_bkup.sql
If you would like the mysqldump to drop and recreate the database for you, create the dump like ...
25
Since I happened to be researching this myself, here's a summary of what I found.
According to a 2015 blog post from the MySQL dev team, the main advantages of mysqlpump are that it can use multiple threads in parallel to speed up the dumping and that it doesn't share mysqldump's backwards compatibility requirements, which should open the door for further ...
20
You can dump database with SequelPro (Mac OSX) with File > Export (⇧⌘E) option.
18
Use grep to exclude databases you don't want:
candidates=$(echo "show databases" | mysql | grep -Ev "^(Database|mysql|performance_schema|information_schema)$")
mysqldump --databases $candidates
From looking at https://stackoverflow.com/questions/19354870/bash-command-line-and-input-limit it seems like you'll be able to handle long lines. Otherwise you can ...
17
You can do it simply using below command:
mysqldump -uusername -ppassword dbname \
--ignore-table=schema.tablename1 \
--ignore-table=schema.tablename2 \
--ignore-table=schema.tablename3 > mysqldump.sql
17
I have also been looking into the differences between these two utilities, and in addition to what is mentioned in @Neil's answer, it seems that mysqlpump also has fewer options than mysqldump, such as not allowing for the following:
--compatible=
--tab, and its related options of:
--fields-terminated-by=
--fields-enclosed-by=
--fields-optionally-enclosed-...
15
Do you really need the entire database to be restored? If you don't, my 2c:
You can extract specific tables to do your restore on "chunks". Something like this:
zcat your-dump.gz.sql | sed -n -e '/DROP TABLE.*`TABLE_NAME`/,/UNLOCK TABLES/p' > table_name-dump.sql
I did it once and it took like 10 minutes to extract the table I needed - my full restore ...
15
I could easily suggest changing InnoDB settings which might be a littel heavy-handed just to get a mysqldump to work. You may not like what I am about the suggest, but I believe it's your best (only) option. Here it goes:
SUGGESTION #1 : Disable extended inserts
The default setting for mysqldump would include clumping together hundreds or thousands of rows ...
14
Every 2 seconds you will see the processes running.
watch 'echo "show processlist;" | mysql -uuser -ppassword';
If you want it less frequent then add -n x where x is the number of seconds. 5 seconds would be:
watch -n 5 'echo "show processlist;" | mysql -uuser -ppassword';
13
According to the MySQL Documentation
mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.
Thus, to run
mysqldump --no-data --routines -...
12
You cannot rely on DISABLE KEYS; and ENABLE KEYS; for InnoDB because it is not implemented in the InnoDB Storage Engine. Running ALTER TABLE ... DISABLE KEYS; and ALTER TABLE ... ENABLE KEYS; were designed for MyISAM. As it says in the MySQL Documentation for ALTER TABLE:
If you use ALTER TABLE on a MyISAM table, all nonunique indexes are created in a ...
12
When you are using mysqldump, you can get the binary logs coordinates of the backup at the point-in-time of the the mysqldump's start.
Just add the --master-data option
mysqldump --master-data=2 --flush-logs --single-transaction...
Using --master-data=2 will record the binary log filename and position in the form of a CHANGE MASTER TO command. It is ...
11
If you just want to check if it is stalled you can query
show processlist;
and see what is being executed.
11
So for some tables it looks like
Well, it should be that way for all the tables.
That message is generated by the --verbose option, and you should see it on all the tables, when mysqldump makes its second pass through the database.
Because views can't reference nonexistent objects, mysqldump iterates through all of the tables in each database, twice, if ...
10
In reality, you could just script it yourself using mysqldump.
Here my posts using the --where option for mysqldump:
Aug 15, 2011 : Is it possible to mysqldump a subset of a database required to reproduce a query?
Mar 14, 2012 : Can I mysqldump an entire database while using where='condition' on individual tables?
Jul 15, 2013 : Export just rows ...
10
The --single-transaction option of mysqldump does do a FLUSH TABLES WITH READ LOCK prior to starting the backup job but only under certain conditions. One of those conditions is when you also specify the --master-data option.
In the source code, from mysql-5.6.19/client/mysqldump.c at line 5797:
if ((opt_lock_all_tables || opt_master_data ||
(...
10
A common case for this error is when the same table appears more than one in the mysqldump list of tables:
mysqldump [options] TableFoo ... TableFoo ...
10
since the database size is large you should make the dump command like the following:
mysqldump -u USER -p --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > OUTPUT.gz
--quick:This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than ...
10
performance_schema: Not necessary. It may contain performance metrics that are only good for the time you were using the instrumentation. It would serve no purpose to load collected metrics from the past into another MySQL instance or into the same MySQL instance at a much later time.
mysql : BE VERY CAREFUL HERE !!! If you backup mysql, you can only reload ...
Only top voted, non community-wiki answers of a minimum length are eligible