> For the complete documentation index, see [llms.txt](https://docs.glesys.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.glesys.com/products/compute/guides-for-server-management/backing-up-and-restoring-mysql.md).

# Backing up and restoring MySQL

***

If you are using MySQL and care about protecting your data, we always recommend taking backups with [mysqldump](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html). 

## Backing up MySQL

To include all databases in the backup, you can use the following script.

{% code title="/usr/local/bin/mysqlbackup.sh" %}

```bash
#!/bin/sh
if [ -z $3 ]; then
    echo "Wrong syntax..."
    echo "use: $0 <mysql_root> <mysql_password> <dump_dir>"
    exit
fi
echo Dumping MySQL database to $3.
umask 077
rm $3/*
mkdir $3 &>/dev/null
cd $3
for i in `echo "SHOW DATABASES" |/usr/bin/mysql -s -u $1 -p$2`; do
     /usr/bin/mysqldump --single-transaction -f -u $1 -p$2 $i >$i.sql
    if [ $? -ne 0 ] ; then
            echo ERROR: Fail when dumping $i
    fi
done
du -sh $3
echo Databasedump done
```

{% endcode %}

After creating the above file, you need to make it executable:

{% code title="Command" %}

```
sudo chmod +x /usr/local/bin/mysqlbackup.sh
```

{% endcode %}

To run this script every day at 22:00, you can add it to your crontab. To edit your crontab, run `crontab -e`. Then, add a line like the following:

{% code title="crontab" %}

```
0 22 * * * /usr/local/bin/mysqlbackup.sh root YOUR_DATABASE_PASSWORD /root/mysqldumps
```

{% endcode %}

## Restoring MySQL

Assuming a system administrator has taken a proper backup with mysqldump, it can still be difficult to restore individual parts of a database. To make backups and restores as fast as possible, `mysqldump` optimizes the queries that are stored in the backup file.

To restore the **entire** database, run the following command:

{% code title="Command" %}

```
mysql -u -p < database.sql
```

{% endcode %}

If you want to extract and restore individual tables, you can do so using the following method.

{% code title="Command" %}

```
mysqldump --skip-extended-insert databas
```

{% endcode %}

This will output something similar to below:

{% code title="Output" %}

```
INSERT INTO `data1` VALUES (1,'abcdefghijklmn..');
INSERT INTO `data1` VALUES (2,'abcdefghijklmn..');
INSERT INTO `data1` VALUES (3,'abcdefghijklmn..');
INSERT INTO `data1` VALUES (4,'abcdefghijklmn..');
INSERT INTO `data1` VALUES (5,'abcdefghijklmn..');
INSERT INTO `data1` VALUES (6,'abcdefghijklmn..');
INSERT INTO `data1` VALUES (7,'abcdefghijklmn..');
INSERT INTO `data1` VALUES (8,'abcdefghijklmn..');
```

{% endcode %}

We do this because the regular `mysqldump database` command can easily become hard to read.

It’s fairly simple to solve, and if you want to restore specific rows, it’s also easier—you can pick those lines out of the dump file and execute them separately instead of reloading the entire database.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.glesys.com/products/compute/guides-for-server-management/backing-up-and-restoring-mysql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.