> 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.