Tag: mysqldump

mysqldump Lost connection to MySQL server during query when dumping table
When taking backup of a MySQL database, i got error
```
root@server1:~# mysqldump --opt serverok_wp > serverok_wp.sql
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `wp_options` at row: 60831
root@server1:~#
```
This error happens when connection between your MySQL client and server is timing out during the mysqldump process.

To fix, increase the value of max_allowed_packet in both MySQL server and client configuration files. This parameter controls the maximum size of a single packet. The default value is 16M, but you can increase it up to 1G.

On Ubuntu MariaDB installation, edit MySQL client configuration file
```
vi /etc/mysql/mariadb.conf.d/50-client.cnf
```
Under [client], add
```
max_allowed_packet=1G
```
Next you need to update MySQL server configuration file
```
vi /etc/mysql/mariadb.conf.d/50-server.cnf
```
Under [mysqld] section, find entry for max_allowed_packet, update its value to 1G, if not found, add an entry.
```
max_allowed_packet=1G
```
Now restart MySQL
```
systemctl restart mysql
```
Back to mysqldump
July 9, 2023
mysqldump errno: 24 Can’t open file when using LOCK TABLES
When backing up a MySQL database using mysqldump command, I got the following error.
```
root@server12:~# mysqldump --opt DB_NAME > DB_NAME.sql
mysqldump: Got error: 1016: Can't open file: './DB_NAME/TABLE_NAME.frm' (errno: 24) when using LOCK TABLES
root@server12:~# 
```
The error is due to open_files_limit
```
mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 2558  |
+------------------+-------+
1 row in set (0.00 sec)

mysql> 
```
You need to edit mysql configuration file and increase value of open_files_limit.

Another way to fix the error is to use –lock-tables=false option when taking MySQL backup.

Example
```
mysqldump --opt DB_NAME --lock-tables=false > DB_NAME.sql
```
If you are using systemd, you may need to edit the service file. Identify which service file you are using with command
```
systemctl list-unit-files | egrep  'mysql|mariadb'
```
Now edit the service file with
```
systemctl edit mysqld
```
In the above command replace mysqld with the name of your service. This can be mariadb or mysql depending on your OS. You will get an editor, in the editor, paste the following, save and exit the editor.
```
[Service]
LimitNOFILE=infinity
LimitMEMLOCK=infinity
```
Restart MySQL
```
systemctl restart mysqld
```
To change open_file_limit on MariaDB, see MariaDB Change Open Files Limit

Back to MySQL Backup
December 8, 2022
Split mysqldump backup file into tables
I had to restore a large MySQL backup file. When restoring one of the table resulted in error. To debug the error, i wanted to split the MySQL backup taken using mysqldump into tables.

You can use csplit command to do this
```
csplit -s -ftable MYSQLDUMP_BACKUP_FILE_HERE "/-- Table structure for table/" {*}
```
This will generate files with name tableXX. First file table00 contains SQL commands used by all tables. Other files starting with table01 contains table related SQL file.

To rename the files into table name, run
```
for FILE in `ls -1 table*`; do TABLE=`head -n 1 $FILE | cut -d$'\x60' -f2`; mv $FILE $TABLE.sql; done
```
If you need to handle larger SQL file, there is a node.js project on github, that do the spliting of mysqldump file into tables.

https://github.com/vekexasia/mysqldumpsplit

See MySQL
December 4, 2020
Extract Backup of one database from mysqldump all databases
When you backup MySQL Server with command
```
mysqldump  --all-databases > all-db.sql
```
You get a MySQL backup file containing all databases.

If you want to generate MySQL backup for single database from this file, you can run
```
sed -n '/^-- Current Database: `DB_NAME`/,/^-- Current Database: `/p' all-db.sql > DB_NAME.sql
```
See mysqldump
August 12, 2018
mysqldump
mysqldump is a command used to backup MySQL databases.

To take backup, run
```
mysqldump --opt DB_NAME > DB_NAME.sql
```
To backup with triggers, routines, and events
```
mysqldump --opt --triggers --routines --events --single-transaction DB_NAME > DB_NAME.sql
```
–opt combines many options. It is same as adding –add-drop-table, –add-locks, –create-options, –disable-keys, –extended-insert, –lock-tables, –quick, and –set-charset.

–extended-insert option will group together all INSERT operations for a table. This makes the backup file smaller and makes restoration faster. I restored a mysqldump bakcup, it take me 2 hours to restore. Same database backup with –extended-insert option take only 10 minutes to restore. If you want a separate INSERT for each row, then use –skip-extended-insert or –complete-insert option

Backup All Databases

To backup all databases, run
```
mysqldump --events --routines --triggers --all-databases | gzip -9 > "$(date +%F-%H%m%S)"-mysql-backup.sql.gz
```
To backup MySQL databases into separate files, run
```
mkdir /root/mysqlbackup/
for DB in $(mysql -Be "show databases" | grep -v 'row\|information_schema\|Database\|performance_schema') ; do
    mysqldump --opt --events --routines --triggers ${DB}  > /root/mysqlbackup/${DB}.sql
done
```
If you need to compress the sql file. use
```
mkdir /root/mysqlbackup/
for DB in $(mysql -Be "show databases" | grep -v 'row\|information_schema\|Database\|performance_schema') ; do
    mysqldump --skip-lock-tables --events --routines --triggers ${DB} | gzip -9 > /root/mysqlbackup/"$(date +%F-%H%m%S)"-${DB}.sql.gz
done
```
Backup Database Structure only
```
mysqldump --no-data DB_NAME > DB_NAME.sql
```
Backup Only routines
```
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt DB_NAME > DB_NAME-routines.sql
```
Related Posts
August 12, 2018