Not a problem. Well, not yet. mysqldump works by SELECTing all the data and generating a dump file from that. For InnoDB tables the SELECTs read the data into the "buffer pool". That expands to a maximum as indicated by innodb_buffer_pool_size but never shrinks. That setting should be tuned to use up most of the remaining memory, but not go past all of RAM. (Going past would lead to swapping, which is terrible for performance.) Some guesses based on the graph: The "working set" of your data is under half of the data. (The graph is lower before 2/12.) Today, the graph is touching 100% -- Suggest lowering that setting a little so it won't start swapping. There is no need for a restart. How much RAM do you have? How much data do you have? What is the setting of innodb_buffer_pool_size?

For large databases, recovery is faster when the data dir is backed up as files. When an SQL dump is restored, all the data is put back by using INSERT statements, which involves parsing the queries, converting data to the storage format, updating indexes etc. When restoring the raw database files, one simply needs to copy the files and start the server.

You probably want to use https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/mysqlbackup.incremental.html more frequently than your Full backups. Differential backups only backup the changes to your database since the last Full backup ran: Performing a series of differential backups. Each https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/glossary.html#glos_differential_backup includes all the changes made to the data since the last full backup was performed. To restore data up to, for example, time t, you simply restore first the full backup, and then, on top of it, the differential backup taken for time t. Incremental backups only backup the changes since the last Full or last Incremental type of backup ran: Perform a series of incremental backup. Each https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/glossary.html#glos_incremental_backup only includes the changes since the previous backup, which can itself be a full or incremental backup. The first backup in an incremental series is always then a differential backup; but after that, each incremental backup only contains the changes made since that last incremental backup. Each subsequent incremental backup is thus usually smaller in size than a differential backup, and is faster to make; that allows you to make very frequent incremental backups, and then enables you to restore the database to a more precise point in time when necessary. So either are generally quicker to run than a Full backup, since they don't backup unchanged data.

There is a very strong likelihood you need one more grant: GRANT SELECT ON mysql.proc TO `dump`@`localhost`; REASON : Dumping Stored Procedures requires reading from mysql.proc UPDATE 2021-12-25 12:20 According to https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_routines --routines, -R Include stored routines (procedures and functions) for the dumped databases in the output. This option requires the global SELECT privilege. The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to create the routines. You may need to adjust grants as follow GRANT PROCESS,SELECT ON *.* TO `dump`@`localhost` or create a separate user for the sole purpose of dumping stored procedures GRANT PROCESS,SELECT ON *.* TO `dump_storedprocs`@`localhost`

This occurs when there are spaces or special characters because the space creates a separation in the CMD parameters. Ex:cmd.exe /C D:\FuncoesMySQL\mysqldump -uroot ... It is interpreted as:cmd. exe: Program that will run/C: First parameterD:\FuncoesMySQL\mysqldump: Second parameter-uroot: Third parameter... and so onWith space (for example, let's say that the folder is "MySQL Funcoes"), it considers:cmd.exe /C D:\Funcoes MySQL\mysqldump -uroot ... It is interpreted as:cmd. exe: Program that will run/C: First parameterD:\Funcoes: Second parameterMySQL\mysqldump: Second parameter-uroot: Fourth parameter... and so onSoon it will not interpret the parameters correctly resulting in the error.To prevent this, simply pass the path between double quotes (""), which will be interpreted all content as a single parameter, e.g.cmd.exe /C "D:\Funcoes MySQL\mysqldump" -uroot ...

C# to open and the CMD and run the command:System.Diagnostics.Process.Start("cmd.exe", "/C \"C:\\Program Files\\MySQL\\MySQL Workbench 8.0 CE\\mysqldump.exe\" --column-statistics=0 -uroot -p1234 -hservidor -P3306 bancomysql > D:\banco.sql"); C# to open PowerShell:System.Diagnostics.Process.Start("powershell.exe", "/C \"C:\\Program Files\\MySQL\\MySQL Workbench 8.0 CE\\mysqldump.exe\" --column-statistics=0 -uroot -p1234 -hservidor -P3306 bancomysql > D:\banco.sql"); The important 'and the "/C" command that copies the entire string to the front and paste to the CMD/Shell.

O mysqldumphas a flag exactly for this:--ignore-table Example of use:mysqldump -u username -p database --ignore-table=meubanco.tabelaCorrupta > database.sql Remembering that if necessary you can use the --ignore-table more than once:mysqldump -u username -p database --ignore-table=db.tab1 --ignore-table=db.tab2 ... See more details in https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_ignore-table .

I noticed you used the tag mysqldump. Since you have not informed the operating system you are using, I will assume it is Linux - that is the most common - If it isn't, you can use the content of this response to adapt to your environment. You will need to log in with a user and password by the tool mysqldump if you want to do any operation.It is not ideal to run any script that a stranger on the internet wrote as root. So I won't advise you to do that. But if you have multiple banks to be backed up and this is properly separated in your respective users, use the credentials of root will allow you to run the script once for all banks. How did you mention that the 54 banks belong to the user plant652, recommend that you perform as such. Remember, of course, that for that you will need the password.I don't know of any magic command to backup all MySQL schemas by default.For example, I created this script to solve your (and my) problem:#!/bin/bash read -p "Input the desired MySQL user for backup: " USER export USER=${USER} if [ -z "${USER}" ]; then exit fi read -p "Input specified user password: " PASSWORD export PASSWORD=${PASSWORD} if [ -z "${PASSWORD}" ]; then exit fi export MYSQL_BACKUP_DIR="/backups/mysql" mkdir -p ${MYSQL_BACKUP_DIR}/date +%Y/%m/%d #rm "$MYSQL_BACKUP_DIR/*gz" > /dev/null 2>&1 databases=mysql -h 127.0.0.1 -P3306 -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database for db in $databases; do if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then echo "Dumping database: $db" mysqldump -h 127.0.0.1 -P3306 -u $USER -p$PASSWORD --databases $db > ${MYSQL_BACKUP_DIR}/date +%Y/%m/%d/$db-date +%Y-%m-%d.sql gzip ${MYSQL_BACKUP_DIR}/date +%Y/%m/%d/$db-date +%Y-%m-%d.sql tar -cvf ${MYSQL_BACKUP_DIR}/date +%Y.tar ${MYSQL_BACKUP_DIR}/date +%Y fi done unset PASSWORD; unset USER; Now, I'll explain. So that the user and password are not stored in the shell history (and don't need to use hacks like putting space in front of the command, which is no guarantee of anything or other techniques out of default or even relying on the memory in deleting the bash_history) did what every time the script was executed, it prompts the user and the password to be used (in case I opt for the root that will see all schemas) and defines the location where backups will be saved, in this case /backups/mysql.It creates folders and organizes backup by YYY-MM-DD, compact with the tool gunzip and then create the largest archives (which in my case is important) separated per year to be able to make a simple download of the whole year. It overwrites the files, does not optimize the use of space and commits some other possible sins that are not relevant to me.At the end of the script you can see that it discards the values of the variables that stored the user and password, in an attempt to keep this as safe as possible within the simple scope of the script.If you don't want to type this every time, probably your only solution is to store in plain text within the script and save it on the disk. But we can all imagine that this idea is not very legal..._.It is worth mentioning the most important part of this all:databases=mysql -h 127.0.0.1 -P63306 -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database for db in $databases; do if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then echo "Dumping database: $db" mysqldump -h 127.0.0.1 -P3306 -u $USER -p$PASSWORD --databases $db > ${MYSQL_BACKUP_DIR}/date +%Y/%m/%d/$db-date +%Y-%m-%d.sql gzip ${MYSQL_BACKUP_DIR}/date +%Y/%m/%d/$db-date +%Y-%m-%d.sql tar -cvf ${MYSQL_BACKUP_DIR}/date +%Y.tar ${MYSQL_BACKUP_DIR}/date +%Y fi done Here he takes the name of all schemas (which of course have its user-affected visibility whose credentials are being used by the tool), then loops and deletes the possible schemas that you do not want to include in the backup, most notably the schemas protected or MySQL standards. From that final list he then makes a mysqldump for each schema and save/package as explained earlier.Important remember: The user root what I mean in this answer at no time is root the operating system, and yes database. I found it important to highlight this for internet visitors who can go through here in the future.

Hi.I made an automation of this by creating a .bat file with the commands and configuring the execution in the Windows Task Scheduler, follow below my bat file:@echo off cls REM Define o usuário e senha do banco de dados set dbUser=xxx set dbPassword=xxx REM Define a pasta que será feito o backup no padrão ...<dia do mês><hora atual> set backupDir=C:\Backup\MySQL%date:~0,2%%time:~0,2%\ REM Nome do arquivo que será gerado set file=xxx.sql REM Caminho dos executáveis do mysqldump.exe, para executar o dump, e do 7z.exe, para compactar o arquivo set mysqldump="C:\wamp64\bin\mysql\mysql5.7.14\bin\mysqldump.exe" set zip="C:\Program Files\7-Zip\7z.exe" REM Cria a pasta de backup caso não exista if not exist "%backupDir%" ( mkdir "%backupDir%" ) REM Executa o dump, aqui precisa configurar o host e o nome do banco de dados (locais com xxx) %mysqldump% --host="xxx" --user=%dbUser% --password=%dbPassword% xxx > "%backupDir%%file%" REM Compacta o arquivo com o dump %zip% a -tgzip "%backupDir%%file%.gz" "%backupDir%%file%" REM Exclui o arquivo .sql original del "%backupDir%%file%" In case, it creates a folder for the day of the month, dumps and compacts the file.

You can try this command:mysqldump --extended-insert=FALSE This response was found in this link: https://stackoverflow.com/a/12439368/6762369

If you want to RESTAURATE an existing backup file, you can use the following command:#mysql -u root -p nomedobd < /home/nmedobd.sql If you want to READ a backup file, you can use the following command:#mysqldump -u root -p nomedobd > /home/nmedobd.sql I hope I helped. Any question, leave a comment.

To backup a specific table you have to pass after the bank name the table name ex:public static void makeBackup() throws IOException{ String caminho = "C:\\Program Files\\MySQL\\MySQL Workbench 6.3 CE\\mysqldump.exe"; ProcessBuilder pb = new ProcessBuilder(caminho, "--user=root", "--password=root", "meuBD minhaTabela", "--result-file=" + "C:\\Users\\Usuário\\Documents\\NetBeansProjects\\meuPrograma\\" + "Backup.txt"); pb.start(); } Now if you want to take specific fields there I do not know how to do through mysqldump, a solution for this would be the SELECT INTO OUTFILE Ex: SELECT column_a, column_b, column_a+column_b INTO OUTFILE '~/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table;

A user can be created with read permission (select) specifically for certain tables and this does not imply to give access to the entire database to it. Second https://serverfault.com/a/22718 from Server Fault, to perform the dumping he would also need at least permission lock tables.However, when giving read permission to specific tables, don't worry if the user can do the dumping of these tables, after all it can simply run one select in the entire table and save the results without difficulties.

Bruno, do you store any kind of log in the bank? It may be that there is some table that is varying in size, you can do the following test, instead of storing in a file only, you can separate in several files that are relative to each table and then analyze which table is this difference.I always use mysql workbench to export in various files, did not try to do otherwise.

The solution to my case has been to reset root, so I had to disable the root password. I created a my.ini(mysql-init.txt) in I stopped the mysql80 service and introduced the following command to C:\Program Files\MySQL\MySQL Server 8.0\mysqld --defaults-file"=C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --init-file=D:\\mysql-init.txt Where I indict my.ini by default and tell him the new one I created.Then I have restored the service, after I have entered with the new password established in my mysql-init.txt and finally after recovering root with the relevant changes I have removed the .txt I created.Content of mysql-init.txt:ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword'; I hope you can serve someone who has root problems in mysql windows, a greeting!!

Checking my code, I realized I had to indicate what the route was. Dump, so add a setDumpBinaryPath in my function handle of me class BackupBaseDatosCommand, I stay the following way:public function handle() { $config = ConfigGeneral::all()-&gt;first(); $filename = '\backup_'.env('DB_DATABASE').'_'.date('Y').''.date('m').''.date('d').'_'.date('H').'_'.date('i').'_'.date('s').'.sql'; //File::put($filename, ''); MySql::create() -&gt;setDumpBinaryPath(env('DUMP_BINARY_PATH')) -&gt;setDbName(env('DB_DATABASE')) -&gt;setUserName(env('DB_USERNAME')) -&gt;setPassword(env('DB_PASSWORD')) -&gt;setHost(env('DB_HOST')) -&gt;setPort(env('DB_PORT')) -&gt;dumpToFile($config-&gt;ruta_salva.''.$filename); } I hope it'll help someone if this problem comes up.

The Percona xtrabackup solution that you linked in your question is a good solution. It is a hot backup and doesn't lock innodb tables. I would prefer that to mysqldump in most cases. Did you try xtrabackup? Have questions about using it?

But the link also recommends XtraBackup from Percona, which is faster, no locks on tables that are already in use and gains no time for restoration. For 500GB of data I prefer Percona XtraBackup to be quick and efficient unless you want to convert them to innodb_file_per_table model if it wasn't from the existing DB server. Below link explains on how to proceed. http://www.percona.com/doc/percona-xtrabackup/innobackupex/creating_a_backup_ibk.html

You could have asked mysql for all individual table sizes SELECT table_schema,table_name,data_length,index_length FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql'); Summary by Storage Engine SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize, SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 3 pw) A ORDER BY TSize; Summary by Database SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM (SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize, SUM(XSize) SXSize,SUM(TSize) STSize FROM (SELECT table_schema DB, data_length DSize,index_length XSize,data_length+index_length TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA GROUP BY DB WITH ROLLUP) AA,(SELECT 3 pw) BB ORDER BY (SDSize+SXSize); Summary by Database/Storage Engine SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size" FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score, IF(ISNULL(engine)=1,10,0) engine_score, IF(ISNULL(table_schema)=1, 'ZZZZZZZZZZZZZZZZ',table_schema) schemaname, IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2, "Storage for All Databases",IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1, CONCAT("Storage for ",B.table_schema),CONCAT(B.engine," Tables for ", B.table_schema))) Statistic, CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') DataSize, CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') IndexSize, CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') TableSize FROM (SELECT table_schema,engine, SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,(SELECT 3 pw) A) AA ORDER BY schemaname,schema_score,engine_score; Please send these queries to your IT people and have them email the results back Please keep in mind that mysqldumps do not contain indexes. A mysqldump is simply a logical representation of the data plus the commands and directive to crate the table, load the table, and make indexes. What get generated physically upon restore can be known by these queries before launching the mysqldump.

I would suggest that this is indeed indicative of a problem with db3. By default, mysqldump generates "extended" insert statements, containing more than one row's worth of insert per line. INSERT INTO table_name VALUES (...), (...), (...), ...; This is an optimization, since multiple inserts in a single statement are much faster than executing individual insert statements. But, you can disable this behavior using the --skip-extended-insert option. Using that option for backups isn't a good idea, since they restore much more slowly, but this option does make dump files that are much easier to read with your eyeballs and much easier to search through for a specific record using grep or a similar tool. Dump the database using this option and then search through the file for the duplicate keys that are throwing errors and it seems likely that you'll find duplicate rows or rows with duplicate keys that should have been unique... which means underlying table trouble in db3. I can't think of a way this could happen with InnoDB but with MyISAM it's distinctly possible. There are a couple of other mysqldump options that might also be useful: --replace generates a file writing the statements as REPLACE INTO instead of INSERT INTO which would result in the duplicate key records occurring later in the file replacing the conflicting records that occurred earlier in the file, without generating an error --insert-ignore generates a file with the statements written as INSERT IGNORE INTO instead of INSERT INTO, which would cause the duplicate key records occurring earlier in the file to be persisted in the restored tables, as the later conflicting records would be ignored, not inserted, and would not generate an error.