DEV Community: SimpleBackups

Restore or Migrate your MySQL Database to Railway

Nour — Fri, 03 Nov 2023 14:42:08 +0000

Railway is a serverless platform that makes it easy to deploy and manage applications and databases. It offers a variety of features, including automatic scaling, high availability, and support for a variety of programming languages and frameworks.

How to Create a Railway MySQL Database

Navigate to the Railway Website and click on "Start new project".
You'll see a prompt asking you what type of project you want to create, type in MySQL and select "Provision MySQL" from the list.
Once the database is provisioned, click on it
Click on "Connect"
Scroll down to the "Available Variables" section and take note of the MYSQL_URL (your connection string), as you'll need it later.

How to Restore a MySQL Dump to Railway.app using SimpleRestore.io

SimpleRestore.io is a user-friendly tool that simplifies the process of restoring a MySQL database. Follow these steps:

Visit SimpleRestore.io
Click on "Select your database backup (.tar.gz)" and select your backup archive.
Enter your connection string that you noted down earlier.
Click "Restore" and wait for the process to complete.

(Bonus) How to Restore a Railway.app Dump on Your Local Database

If you want to restore the dump to your local MySQL database, follow these steps:

Log in to your local MySQL server.
Create a new database or select an existing one.
Use the mysql command to restore the dump:

mysql -u username -p database_name < dump_file.sql

Replace username, database_name, and dump_file.sql with your specific details.

FAQ

What is a MySQL Backup Service?

A MySQL backup service is a tool or platform that allows you to create copies of your MySQL databases, ensuring data protection and recovery in case of unexpected events or data loss.

What is a MySQL Connection String?

A MySQL connection string is a string that contains the information needed to connect to a MySQL database. It typically includes details like the host, port, username, password, and database name.

What is a MySQL Dump?

A MySQL dump is a file that contains a SQL script with all the commands needed to recreate a database. It includes table structures and data.

How to Restore a MySQL Backup?

To restore a MySQL backup, you need to have a dump file. You can use tools like SimpleRestore.io to easily upload and restore the backup to your desired database.

How to Create a MySQL Backup for Railway.app?

You can create a MySQL backup for Railway.app using the MyDumper tool or by using a backup service like SimpleBackups. Both methods involve generating a dump file that can be used for restoration.

Restore your MySQL Database on PlanetScale

Nour — Fri, 03 Nov 2023 14:41:45 +0000

PlanetScale offers a cloud-native, serverless, and multi-cloud MySQL database service. It's designed to provide high availability, scalability, and simplicity for your database needs. With features like automatic sharding, you can scale your database effortlessly.

How to Create a PlanetScale MySQL Database

Sign in to your PlanetScale account and navigate to the dashboard.
Click on "Create Database" and follow the prompts to set up your database.
When you're done, you'll be redirected to a page where you can create a new password, in order to grab your connection string, select "Rust" or "Prisma" as your framework or programming language.
Take note of your database connection string, as you'll need it later.

How to Restore a PlanetScale Dump on a Remote Database using SimpleRestore.io

SimpleRestore.io is a user-friendly tool that simplifies the process of restoring a MySQL database. Follow these steps:

Visit SimpleRestore.io
Click on "Select your database backup (.tar.gz)" and select your backup archive.
Enter your connection string that you noted down earlier.
Click "Restore" and wait for the process to complete.

(Bonus) How to Restore a PlanetScale Dump on Your Local Database

If you want to restore the dump to your local MySQL database, follow these steps:

Log in to your local MySQL server.
Create a new database or select an existing one.
Use the mysql command to restore the dump:

mysql -u username -p database_name < dump_file.sql

Replace username, database_name, and dump_file.sql with your specific details.

FAQ

What is a MySQL Backup Service?

A MySQL backup service is a tool or platform that allows you to create copies of your MySQL databases, ensuring data protection and recovery in case of unexpected events or data loss.

What is a MySQL Connection String?

A MySQL connection string is a string that contains the information needed to connect to a MySQL database. It typically includes details like the host, port, username, password, and database name.

What is a MySQL Dump?

A MySQL dump is a file that contains a SQL script with all the commands needed to recreate a database. It includes table structures and data.

How to Restore a MySQL Backup?

To restore a MySQL backup, you need to have a dump file. You can use tools like SimpleRestore.io to easily upload and restore the backup to your desired database.

How to Create a MySQL Backup for PlanetScale?

You can create a MySQL backup for PlanetScale using the MyDumper tool or by using a backup service like SimpleBackups. Both methods involve generating a dump file that can be used for restoration.

The Complete Redis Backup Guide (with examples)

Laurent Lemaire — Mon, 15 Aug 2022 08:45:05 +0000

In this article, we’ll see how you a real case example of a Redis backup process, end-to-end.

We'll cover how to configure a Redis backup, which binaries you should use and the most important settings you need to be aware of, as well as how to store your backup remotely and how to restore it.

Let's get started!

What is Redis?

Redis is an open source in-memory key-value store written in C.
Redis stands for Remote Dictionary Server and is used as a database, cache, queue system, and message broker.

Redis is fast because its data is stored in memory, meaning, unlike traditional databases Redis doesn't need to access the disk.
While writing this article I learned that Redis is often called a "Data Structure Server" because it provides data types that are similar to those in modern programming languages.

Some data structures that Redis provides are Strings, Lists, Sets, Hashes, and Sorted Sets.

Redis Data Types →

What is Redis used for?

Even though Redis could be used as your primary Database it's usually not what it is used for.
Here are the most common use cases for Redis:

Cache: Redis is used as a cache, which is a fast way to store data in memory.
Session storage: Redis is used to store session data. Writing and reading data out of Redis is super fast which makes it an ideal candidate for session storage.
Message queue

There are a lot of different use cases for Redis, but these are the most common ones.

Where is Redis database stored?

As stated above Redis is storing its data in memory. But depending on your use case Redis can copy the data on your disk.
This comes obviously handy when you have a large amount of data, and you need to be able to restore it and this is also why you might be needing to back it up.

Redis regularly dumps its data to an RDB file on the disk based on how snapshots are configured.

Redis configuration
The


 file contains your Redis configuration.

The configuration file is located at

 ```/etc/redis/redis.config```

 and straighforward: it's a list of instructions.
You'll find a section called

 ```#### SNAPSHOTTING ####```

 on which you can define if Redis should snapshot its data to the dis and how often it should do it.



```redis.config
save 60 1000

This configuration will make Redis dump the dataset to the disk every 60 seconds if at least 1000 keys are changed.

Learn more about Redis Configuration →

Redis also works with AOF (Append-Only File) which is a way to store the data on the disk by logging all write operations received by the server.
AOS won't be covered in this article but it is worth knowing it exists, especially when you'll need to backup and restore your data.

How to back up Redis data

Making a Redis backup is pretty easy. You'll need to make a fresh copy of the RDB file, compress it and save it somewhere safe.

Redis offers 2 methods to "force" a snapshot:


: This will force a snapshot to be taken synchronously.
-

 ```BGSAVE```

: This will force a snapshot to be taken asynchronously.

The easiest way is to use the

 ```SAVE```

 method but it will block all other operations until the snapshot is done.
Using

 ```BGSAVE```

 will make the server continue to accept commands and will not block other operations but you'll have to figure out when the snapshot is this one is asynchronous.

**So, if you want to make a backup you'll need to do the following:**
1. Review / update your Redis configuration.
2. Create a snapshot for your Redis data (known as a "dump"/"rdb file").
3. Save the RDB file to a remote location

### 1. Review / update your Redis configuration.

You'll need to know where your snapshot file will be generated using the redis-cli command described below.

The default location of your Redis config file is

 ```/etc/redis/redis.config```

.
You can also use this command to find the location of your Redis config file:

 ```redis-cli config get dir```

.

You can find the configuration file here [https://redis.io/topics/config](https://redis.io/topics/config).

### 2. Create a snapshot for your Redis data.

#### <u>Using redis-cli

 ```SAVE```

 command</u>
This method will work synchronously to make a snapshot of your Redis database.
Just ssh into your server and run the following command:

Log in to the database command line interface:

redis-cli




You might have to authenticate to your database:


```shell{promptHost: 127.0.0.1}
auth YOUR_PASSWORD_HERE

SAVE

The output will be something like this:

OK
(1.23s)

You can then exit the redis-cli by typing


.

At this stage, the

 ```RDB```

 file will be saved in

 ```/var/lib/redis/```

 and will be named

 ```dump.rdb```

.

#### <u>Using redis-cli

 ```BGSAVE```

 command</u>

Using the asynchronous dump function, you'll need to make sure you are aware of the end of the process.
One way to do it is to use

 ```inotifywait```

 which will notify you when a change to the dump file is made.

### 3. Automate your Redis backups and store them on AWS S3

As stated in [Redis documentation](https://redis.io/docs/manual/persistence/), it's safe to copy the RDB file even if used by your running server:

> Redis is very data backup friendly since you can copy RDB files while the database is running: the RDB is never modified once produced, and while it gets produced it uses a temporary name and is renamed into its final destination atomically using rename(2) only when the new snapshot is complete.
>
> This means that copying the RDB file is completely safe while the server is running. This is what we suggest:
>
> - Create a cron job in your server creating hourly snapshots of the RDB file in one directory, and daily snapshots in a different directory.
> - Every time the cron script runs, make sure to call the find command to make sure too old snapshots are deleted: for instance you can take hourly snapshots for the latest 48 hours, and daily snapshots for one or two months. Make sure to name the snapshots with date and time information.
> - At least one time every day make sure to transfer an RDB snapshot outside your data center or at least outside the physical machine running your Redis instance.


We'll create a script that will create our dump file, then upload to Amazon S3.

#### <u>Create a shell script that will dump the Redis database</u>


```shell
cd ~
mkdir scripts
cd scripts
nano redis_backup.sh

Copy and paste the script below to it:

#!/bin/bash
rdb_file="/FOLDER_TO_YOUR_REDIS_DUMP_FILE/redis/dump.rdb"
redis_cli="/usr/bin/redis-cli"

DIR=`date +%d-%m-%y`
DEST=~/redis_backups/$DIR
mkdir $DEST

echo save| $redis_cli
exit 1

Send the Redis DUMP to an AWS S3 bucket

Append to


 the following:



```shell
BUCKET_NAME="YOUR_S3_BUCKET_NAME"
aws s3 cp $rdb_file s3://YOUR_S3_BUCKET/redis_backups/ && echo "Backup copied to S3"

Schedule the script to run every day at midnight

First, let's CHMOD the script to make it executable:

chmod +x ~/scripts/db_sync.sh

Then create a cron job to run the script every day at midnight:

crontab -e

0 0 * * * ~/scripts/redis_backup.sh # take a backup every midnight

How to restore a Redis backup

Now that you've made a backup, we'll see how to restore it from a


 file.

**We recommend you first try this on a fresh Redis server**

### Make sure AOF is disabled
AOF stands for Append-Only File, which will instruct Redis to log all operations in a `.aof` file.
Since we're restoring a backup, we need to disable AOF before restoring the data as we don't want Redis to log all these operations.

Open your configuration file (

```redis.config```

) and make sure

 ```appendonly```

 is set to

 ```no```

.

appendonly no




### Stopping the Redis server

Before being able to replace the

 ```dump.rdb```

 file, you'll need to stop the Redis server.



```shell
sudo service redis-server stop

Restoring the Redis database

Prior to restoring the database, you can rename the existing dump.rdb file in order to have a restore point in case something goes wrong.

sudo cp /home/redis/dump.rdb /home/redis/dump.rdb.bak

You can then copy the backup rdb file as follows:

sudo cp /redis_backups/20220810/dump.rdb /home/redis/dump.rdb

And finally make sure to apply the right permissions to the dump.rdb file:

sudo chmod 660 /home/redis/dump.rdb

Re-starting Redis server

sudo service redis-server start

Additional Resources

Official Redis documentation: https://redis.io/docs/manual/persistence/
Service to automate your Redis backups: https://simplebackups.com/redis-backup/

Conclusion

We've seen how to back up your Redis database and restore it, and we've seen how to automate the process.

SimpleBackups will save you a lot of time setting up scripts, ensuring they run without problems, all without code or maintenance.
It will alert you when things go wrong, and allows you to store your backups on many cloud storage services like Google, DigitalOcean, Wasabi, Dropbox, and more…

Cloud Storage Comparison - Best Providers 2021

Laurent Lemaire — Thu, 07 Oct 2021 13:59:58 +0000

We've created a list of all major (and our preferred) providers, together with their prices using the region US-EAST-1 as reference.

If you're looking to store your data on a Cloud Storage Provider, you've got plenty of choices.

Prices are often changing and what each provider offer is different.
Once you know your needs in terms of storage/frequency of access (...), you'll easily be able to identify which provider fits you the best.

This list doesn't contain all the specificities each provider offers as we tried to summarize what most people are looking for when it comes to evaluating an Object Block Storage provider.

If you want to see any additional information in this comparison just let us know.

Object Storage Providers Comparison

Name	Price /GB/month	Min price/month	Price/1000 requests	Price Ingress	Price Egress	Link	Extra
AWS S3	$0.02	-	$0.01	$0.00	$0.09/GB/month (1GB/m included)	https://aws.amazon.com/s3/pricing/	- 12 first months free - 5GB free
AWS S3 - Infrequent access	$0.01	-	$0.01	$0.00		https://aws.amazon.com/s3/pricing/
AWS Glacier	$0.00	-	$0.03	$0.00	$0.01/GB	https://aws.amazon.com/s3/pricing/ https://aws.amazon.com/s3/pricing/
Wasabi	$0.01	-	$0.00	$0.00	$0.00	https://wasabi.com/cloud-storage-pricing/#cost-estimates
Blackblaze	$0.01	-	$0.00	$0.00	$0.01/GB	https://www.backblaze.com/b2/cloud-storage-pricing.html
DigitalOcean Spaces	$0.02	$5 (250GB included)	$0.00	$0.00	$0.00	https://www.digitalocean.com/pricing/	- Minimum 5$ - 1 TB of outbound transfer (additional 0.01/GB) - Additional storage 0.02/GB
Dropbox	$0.00	$12 (5TB included)	$0.00	$0.00	$0.00	https://www.dropbox.com/plans?tab=work#features	- Reference: Plan standard - Minimum 12$/m
Google Cloud Storage	$0.02	-	$0.01	$0.00	$0.12	https://cloud.google.com/storage/pricing	- Reference: Standard - 5GB free + 5000 operations
FileBase	$0.01	-	$0.00	$0.00	0.0059/GB (1TB included)	https://filebase.com/	- Minimum 5.99/m
Azure Blob Storage	$0.02	-	$0.0065-$0.0005	$0.00	n/a	https://azure.microsoft.com/en-us/pricing/details/storage/blobs/#overview	- Reference: Hot Storage
Exoscale	$0.02	-	$0.00	$0.00	$0.02	https://www.exoscale.com/pricing/#storage
UpCloud	$0.02	-	$0.00	$0.00	$0.01	https://upcloud.com/pricing/#object-storage	- Reference 250GB plan
Vultr	$0.02	-	$0.00	$0.00	$0.01/GB (1TB included)		- Minimu: 5$ (250gb)

This list is maintained and updated monthly - **last update: 20/09/2021)* *

Restore MongoDB Atlas Collection using MongoDB Compass

zOxta — Wed, 06 Oct 2021 12:40:44 +0000

So you already have your backup and decided to restore this on your MongoDB Atlas database? In this tutorial, we will tackle restoring a MongoDB Collection into MongoDB Atlas using MongoDB Compass.

Prerequisites

MongoDB Atlas Cluster
MongoDB Compass Installed
A MongoDB database collection backup; if you don't have one, SimpleBackups can help you backing up your MongoDB Atlas

MongoDB Atlas is a fully-managed cloud database developed by the same people that build MongoDB.

MongoDB Compass is a GUI that allows you to analyze your documents and displays rich structures within your collections.

In this article we will go through connecting to our MongoDB Atlas database using MongoDB Compass and importing a sample collection into your MongoDB Atlas database.

Grab your MongoDB Atlas Database Credentials

Head over to https://cloud.mongodb.com/v2/ then choose your MongoDB cluster and click on "Connect" on the cluster you want to connect to.

When the list of option pops up, make sure you choose the MongoDB Compass option.

From this screen, you can copy the connection string at the bottom which includes the connection details (username, password placeholder, database host and so on).

Note: this is the connection string you can add to SimpleBackups when backing up your MongoDB Atlas database.

Connect to MongoDB Atlas via MongoDB Compass

Paste the connection string you obtained from the previous step.

Note: make sure you replace <password> from the connection string by the actual password of your MongoDB Atlas user.

Import MongoDB Atlas collection via MongoDB Compass

When you connect successfully, you will see all databases under your MongoDB on the left as shown above. Select the database and the collection you want to import the data into. In this case, we had a MongoDB database called myDatabase and a collection called myCollection.

When you select the target collection form the left hand side, click "ADD DATA" and then "Import File".

Finally, on the screen below, you can select the JSON document (MongoDB collection) you want to import.

The Complete mysqldump Guide (with examples)

zOxta — Tue, 08 Dec 2020 22:55:52 +0000

Databases are essential tools to collect and store mountains of data. All complex applications use them to store information.
There are many database solutions out there, but MySQL is one of the most popular. Using MySQL is vital to keeping these databases secure and running smoothly.

Managing and backing up servers and databases can all be done in MySQL.
To help these complex processes run smoothly, utilities have been developed. Mysqldump is one of those tools meant to make the lives of developers easier.

In this guide, we are going to walk you through what utility is, how to use it, identify some common errors, and provide some clear examples along the way so that you will be able to use mysqldump effectively.

What is mysqldump?

MySQL is a database system that has been around for years and continues to be one of the most popular choices for websites. It's open-source and agile. Developers can use these databases to store anything a website may need. The information stored in online databases can range from consumer info and simple text to picture galleries to network information.

Mysqldump is part of the relational database package for MySQL. It is used to back up all the data in a database into a single text file. These files or "dumps" can be generated for individual databases or a collection of them. The text in the file is displayed as a set of SQL statements that can later be reconstructed into its original state.

The purpose of this tool is to export a database to a backup file or to move the database to another web host. Other delimited text formats like XML and CSV can also be generated with mysqldump. These simple queries make the backup process easier.

The importance of backing up data

Companies that hope to run smoothly need pristine copies of their data at different points in time. Without a backup strategy, there is nothing to protect them in the case of a disaster. The ease in which the data can be lost forever is too much to cope with as data can easily become corrupted or get lost over time. Malicious intent and natural disasters are not a requirement for worst-case scenarios to transpire.

Having backups at periodic intervals gives the company the ability to rewind the clock by reloading the previous database. If something breaks or fails, this acts as a lifeline for the system. The company also has data versioning available. Different versions of the database and product are available to go back to. Critical changes that later prove to break the system can be undone, then you can restore the old versions without the problem.

By backing up everything, migrations to new servers or development environments transpire without the fear that data will be lost.

How to use mysqldump

By using mysqldump, a developer can get a hold of the .sql file that serves as a back up for the entire database. To use the tool, the developer needs access to the server running the instance of MySQL. The required privileges have to be given to export anything. The user credentials for the database will also be needed, including the username and password.

The three ways that mysqldump is used to export data includes:

Exporting specific tables in a MySQL database
Exporting single databases
Exporting the entire MySQL server

Exporting MySQL tables

Make sure you are on a machine that has MySQL installed. You will also need a valid database user with -at minimum- full read access privileges. This should do for basic options, but more advanced commands may require additional privileges. With that in order, launch a terminal where you will send the command to back up the tables.

Match your inputs to conform to the following command structure:

mysqldump [options] db_name [table_name ...]

For the live command, replace [options] with the valid option names or flags. These will most likely include -u and -p, which stands for user and password. When using more than one option, be careful of the order they are listed in because they will be processed in order from first to last. Here, [table_name…] will be replaced by the name of the table you are exporting. Different tables must be separated by spaces. The following example is for backing up tables called mystery and cash, and the name of the database is db_cooper.

mysqldump -u username -p db_cooper mystery cash > file_name.sql

You will then provide the password for the database user because it is not passed along with the -p flag. The > character indicates the output redirection that will create the dump file. Here, file_name is what the final file will be called.

Exporting a MySQL database

The steps for exporting a database are very close to those for exporting a table. There is just a small change in the format of the command. You will need the same server access and credentials.

Match your inputs to conform to the following command structure:

mysqldump -u username -p --databases db_larry db_curly db_moe > file_name.sql

The database you will export comes after the --databases option. The space character separates multiple databases.

Exporting a MySQL server

The command is very similar for entire servers as well.

Match your inputs to conform to the following command structure:

mysqldump -u username -p --all-databases

The command itself is pretty basic, with --all-databases indicating that everything on the server should be dumped. If there are specific requirements, that is where the options come in for the command. Adding -compatible will make the file that gets exported compatible with older MySQL servers or database systems.

Developers using PowerShell on Windows will need to include -result-file as an option. This will specify the file name and make sure that the output is in ASCII format so that it will load correctly later.

Other common options include adding --no-data will only back up the database structure, using --no-create-info backs up the database without any structure.

Importing a mysqldump

Importing a .sql file is straight forward. The only kink is to make sure the target server has a blank database before importing anything.

Match your inputs to conform to the following command structure:

mysql -u root -pmypassword wpdb < db_backup.sql

The mysqlimport command will also work on databases you want to restore that already exists on the target machine:

mysqlimport -u root -pmypassword wpdb < wpdb_backup.sql

You also have the option to import all databases by running a command that looks like this:

mysql -u root -pmypassword < alldb_backup.sql

What does the --quick flag do?

Mysqldump can operate in one of two ways.

The tool can go grab all data at once, buffer it in memory, and then dump it.
It dumps the tables row by row.

The second method is important when dealing with large tables. By using the --quick flag, mysqldump reads large databases without needing large amounts of RAM to fit the full table into the memory.
This ensures that the databases will be read and copied correctly on systems with small amounts of RAM and large data sets.

Does the "where" clause work with mysqldump?

Yes, this clause works with the command line. This makes it easy to set conditions on the data you need to dump from the database. If there is a large enterprise that has been in business for decades that wants to pull the information after April 27, 2017, then this clause allows that to happen. The where clause passes a string for the condition and grabs the specific records requested.

mysqldump -u root -pmypassword wpdb --tables thetable --where="date_created > '2017-04-27'" > wpdb_myrecord.sql

Troubleshooting common errors

Along the way you may face some MySQL common errors that are -to some degree- easy to mitigate. We will share below some of these errors and how to solve them.

Error 2013: lost connection to mysql server during query when dumping table

To fix this issue, you need to go into the MySQL configuration file and increase some values. When those are added, save and close the file, then restart MySQL for the changes to take effect.

The values you need to adjust are:

max_allowed_packet
net_write_timeout
net_read_timeout
innodb_buffer_pool_size

The adjustments to the file will be under the [mysqld] and [mysqldump] sections and will look like this:

[mysqld]
innodb_buffer_pool_size=100M
max_allowed_packet=1024M

[mysqldump]
max_allowed_packet=1024M
net_read_timeout=3600
net_write_timeout=3600

Error 2020: got packet bigger than 'max_allowed_packet' bytes when dumping table

If the database you need to back up is large, and the file size ends up bigger thant the maximum allowed packet size, this error pops up.

This error can be fixed by going into the MySQL configuration file and increasing max_allowed_packet value in the [mysqld] and [mysqldump] sections. Save and close the file when finished, then restart MySQL for the changes to take effect.

The changes will look like this:

[mysqld]
max_allowed_packet=desired-value

[mysqldump]
max_allowed_packet=desired-value

Table does not exist (1146), couldn't execute 'show create table x'

There may be times when you delete a table during backing up. If this is the case, you can restrict certain tables from the mysqldump command with the --ignore-table option. To identify the table, you will have to state both the database and table names.

mysqldump -u root -pmypassword example_db --ignore-table=name_of_table > db_backup.sql

By listing the option multiple times, you can ignore multiple tables:

mysqldump -u root -pmypassword example_db --ignore-table=table --ignore-table=tableaux > db_backup.sql

Selecting the database returns 'unknown database'

This error happens most often when you use the -p flag in the command line with the password and there is a space in between -p and mypassword. If this happens when using "root" as the user with a password of "base-face", there will be an error stating "Unknown database base-face."

The correct input would look like this:

mysqldump -u root -pbase-face wpdb > wpdb_backup.sql

Error 1044 when selecting the database

If the user trying to do the dump doesn't have the privileges necessary to access the database, this error occurs. Logging into MySQL and assigning those privileges to the user will fix the issue. Enter command:

mysql -u root -p

Then enter the correct password, and proceed to grant privileges to the selected user.

GRANT ALL PRIVILEGES ON wpdb.* TO 'not_a_hacker'@'inconspicuous_host';

After that, flush the privileges and exit from MySQL by entering the command:

FLUSH PRIVILEGES;

EXIT;

Access denied for user when trying to connect to mysqldump

This error has several possible causes. Here's three of the most common causes of the issue.

Wrong mysqldump command

If you are using the wrong command, then this error will appear. The command may be mostly correct but it's missing a critical ingredient in the mysqldump format. The basic command will look like this:

mysqldump -u user -pmypasword database > database.sql

If you fail to specify a username or password then it will spit back the following message:

mysqldump: Got error: 1045: "Access denied for user 'user' @ 'localhost' (using password: NO)" when trying to connect

Remote host not allowed to connect to database

This error comes up if the backup is trying to be done on a remote server. The configurations for MySQL are set to disallow external connections. Here, the localhost is the only one allowed to make a backup. This is a security measure, so it's a good measure to have but if you need to change this, go to configurations and change MySQL to allow connections from a remote host.

Wrong user credentials

If you try to use the wrong username and password combination while connecting to the database, this error happens. MySQL can't verify that the request is authentic and returns the error. You'll have to make the request again with proper credentials, make sure there aren't any typos in your original command as that is the easiest mistake to make.

Conclusion

Mysqldump is a useful tool to help back up databases with minimal commands. One command allows the entire database to be spit out into a single text file. The tool is versatile enough to back up the parts of the database that is needed and comes with a variety of options to change the data you need to save.

Automated MySQL Backups using SimpleBackups

SimpleBackups.com is a database and website backup automation tool. It automates MySQL backups using mysqldump to securely dump your MySQL databases and send them offsite to the cloud for storage. When you need to ensure your MySQL backups are secure, you can trust SimpleBackups to take care of it for you.

The Ultimate MySQL Database Backup Script

Laurent Lemaire — Sat, 13 Jun 2020 09:43:12 +0000

Any database, needs a backup.

This article is part of “The Ultimate Backup Script” series we are creating to provide you with database backup scripts that not only allow you to create database backups, but also upload the backup dumps to Amazon S3 and automate the process daily.

Why we need a database backup?

One might think why backup is necessary for my database? The answer is simple, backup creates a copy of your physical, logical, and operational data. Which you can store at any safe place such as Amazon S3. This copy comes into use if the running database gets corrupted. Database backup can include files like control files, datafiles, and archived redo logs.

Why Amazon S3 for backup?

For this tutorial, we have chosen Amazon S3 as it is a very common choice. You can do the same thing if you would like to use another clould storage provider. The instructions won’t differ a lot as long as the cloud provider is S3-compatible.

Below we defined some less known terms that we used in the article:

What is Cron?

Cron is a software utility that offers time-based job scheduling. It supports Unix computer operating systems. To set up software environments, the developer uses Cron. He/she schedules commands or shell scripts so that they run at chosen times. It could be daily, once a week, or any interval as desired.

What is Chmod?

The chmod a short command of ‘change mode’ enables the admin to set rules for file handling. In other words, with the help of a “chmod” system call. An administrator can change the access permissions of file system objects.

Database Backup Script for MySQL and Dumping to Amazon S3

You can automate the creation of backup and storing it to Amazon S3 within a few minutes. Below bullets brief about what you are going to learn in this part of the article:

Create a script that automates the MySQL backup directory creation
Upload/sync the backups with Amazon S3
Cron will run this command every day (to back up)

Step 1: Generating a shell script which will dump the MySQL database

cd ~
mkdir scripts
cd scripts
nano db_backup.sh

Copy and paste the script below to it

#!/bin/bash
DIR=`date +%d-%m-%y`
DEST=~/db_backups/$DIR
mkdir $DEST

mysqldump -h mysql_hostname -u mysql_user  -p"mysql_password" database_name > dbbackup.sql

Now chomd the script to allow it to for execution

chmod +x ~/scripts/db_backup.sh

Step 2: Creating the shell script which sync the backups with Amazon S3

nano db_sync.sh

Copy and paste the script below to it

#!/bin/bash
/usr/local/bin/aws s3 sync ~/db_backups s3://my-bucket-name

Now chmod the script to allow it for execution

chmod +x ~/scripts/db_sync.sh

Step 3: Creating the folder in Amazon S3 for the database dumps

cd ~
mkdir db_backups

Step 4: Time to configure the AWS CLI

Before installing the AWS CLI you need to installpython-pi. Type the following commands:

apt-get update
apt-get -y install python-pip
curl "https://bootstrap.pypa.io/get-pip.py" -o "get-pip.py"

Install the AWS CLI

Type the following command:

pip install awscli

Step 5: Time to set up AWS key & Secret

Configuration and credential file settings

cd ~
mkdir .aws
nano ~/.aws/config

Paste in key_idand secret_access_key as shown below

[default]
aws_access_key_id=AKIAIOSFODNN7EXAMPLE
aws_secret_access_key=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY

Step 6: Set up the Cron (to automate the process)

crontab -e

Paste the below commands at the bottom to automate the process

0 0 * * * ~/scripts/db_backup.sh # take a backup every midnight
0 2 * * * ~/scripts/db_sync.sh # upload the backup at 2am

This way the backup script will run and also sync with Amazon S3 daily.

Conclusion

Hence, by using these scripts you can achieve 3 goals:

Creating the database backup via a shell script
uploading the dump to Amazon S3
also automating this process using Cron.

Have you tried SimpleBackups.io yet?

SimpleBackups will save you a lot of time setting up scripts and ensuring they run without problems. It will alert you when things go wrong, and allows you store your backups on many cloud storage services like Google, DigitalOcean, Wasabi, Dropbox, and more…