In this article I will try, briefly but to the point, to consider several options for how to easily and quickly make a database backup.
I offer three options:
- mysqdump (Command utility)
- phpmyadmin (Web interface for working with the database)
- Sypex Dumper (Script)
mysqldump -u -p > – the name of the user who has access to the database.
– user password.
– database name.
– file name, output dump.
An example of how to dump the mydb_forum database on a MySQL server:
mysqldump -uadmin -pSuperPassword mydb_forum > mydb_forum.sql
Restoring a previously obtained database dump is also easy.
mysql -uadmin -pSuperPassword mydb_forum< mydb_forum.sql
To dump all the databases that are on your MySQL server, use the following. example:
mysqldump -uAdmin -pMyPass --all-databases > all_databases.sql
Backup of database structuremysqldump --no-data --databases my_db1 my_db2 my_db3 > structurebackup.sql
Compress data on the gomysqldump -uAdmin -pSuperPass mydb | bzip2 -c > backup.sql.bz2 mysqldump -uAdmin -pSuperPass mydb | gzip -c > backup.sql.gz
PHPMyAdminPHPMyAdmin is a lightweight web interface for working with MySQL databases. You can use it on a server or home PC if you have PHP installed and configured. This is also a good option when you do not have access to the server via telnet/ssh. Download latest versionPHPMyAdmin package can be found here.
PHPMyAdmin Settings
Before we start backing up the database using PHPMyAdmin, I will describe how you can configure it. After unpacking and installing the PHPMyAdmin package, we need to edit the file
config.inc.php
Specify the full URL to the location where phpmyadmin is located
$cfg [ "PmaAbsoluteUri" ] = "http://myexample.com/phpmyadmin/" ;
phpmyadmin settings for working with the database
$cfg [ "Servers" ] [ $i ] [ "host" ] = "235.104.1.5" ; $cfg [ "Servers" ] [ $i ] [ "port" ] = "3306" ; $cfg [ "Servers" ] [ $i ] [ "connect_type" ] = "tcp" ; $cfg [ "Servers" ] [ $i ] [ "auth_type" ] = "config" ; $cfg [ "Servers" ] [ $i ] [ "user" ] = "admin" ; $cfg [ "Servers" ] [ $i ] [ "password" ] = "FvAk41P" ;
Backup MySQL database using PHPMyAdmin.To make a backup of a MySQL database using PHPMyAdmin, log into it (e.g. http://localhost/phpmyadmin/), on the left select the database that interests you, and click on the Export tab, you will be taken to something like this page:
As you can see, there are different settings here, the ability to select the type of future database dump, dump archiving, etc. Click on the “Go” button to start backing up the database, a window pops up asking you to indicate the location on your PC where the database dump will be saved, indicate and enjoy the database backup process. PHPMyAdmin. Restoring the database
The procedure is quite simple, go to phpmyadmin, click on Import, see the form, use it to select a local database dump file. When you click on “Go,” the database will be restored. Sypex Dumper Also very interesting, in my opinion, is a free script – “Sypex Dumper” written in PHP.
Any novice user or administrator who does not want to bother with various console utilities and multifunctional web interfaces can choose a convenient, simple and fully functional Sypex Dumper script.
Hello readers and subscribers of the site! Today I decided to talk about the most important thing, about a site backup, thanks to which all site data can be restored at any time of the day or night in the same form as before.
It would seem, what is so important about such a topic? Just think, some kind of copy of the site, after all, we pay money for hosting and he is obliged to be responsible for the safety of the content of the sites served.
There is some truth in this, but no one is protected from force majeure and imagine your surprise when one day you go to your website and see a similar error message instead.
Do you know what's the worst thing? It's that you haven't backed it up (files and database).
It is impossible to imagine that all your work and constant income ceased to exist due to such stupidity.
A simple example: you have been running a blog for several years, spending time every day writing articles and promoting it. An army of regular readers and subscribers has gathered around you, plus your site has already begun to make a profit, which allowed you to quit your job and become financially independent through the online network.
But for some reason, the server where the hard drive with your site is located stopped working, so you know hosting is just a virtual data storage, but in reality, somewhere on earth a special room was built with many modern computers and thousands of GB of memory.
You are paying for a piece of space on a remote hard drive. Now imagine that there was a fire in this room and all the data on the hard drive where your website was located was damaged.
The hosting provider will send you an apology and nothing more. Well, of course, you will have to start your career as an online entrepreneur from scratch. (
Who was more foresighted to take a backup copy of the site, restore the data on another hosting and forget about such an incident.
Personally, in my own skin, a few days ago I experienced the fear that I could lose everything in an instant when my hosting servers crashed and my blog was unavailable to visitors for some time.
Of course, I’m not a complete newbie and I know very well that all data needs to be copied from such incidents, but at that moment I hadn’t updated my blog backup for several months.
To be honest, it would be very disappointing to go back and write dozens of articles all over again. So think about it, and below I will show you in what ways a backup copy of site data is created.
Reasons why a site may disappear:
- Error when editing site files. You can easily mess up the code or make a mistake.
- Hosting down. There can be many reasons for this, starting with natural disasters and ending with the fact that the hosting itself allows itself to cheat clients. Anything can happen in life!
- Hacker or virus attack on the site. Now there are a lot of attackers whose targets are ordinary webmasters and their profitable projects.
Let's start with the fact that almost every hosting provider creates daily backups of all customer data.
And now the most interesting thing, in the next paragraph you need to set the period of time after which the plugin will automatically copy and send a backup copy of the database to your email.
I set the frequency to once a day and created a separate email account for this, so as not to clutter up my work address with archived copies of the blog database. As you can see, there is nothing complicated!
This is where I will end the post, I hope from it you will understand the pain and importance of periodically creating a copy of all site files and the MySQL database.
It is better to spend a couple of minutes of time than several months restoring a project from web archives. Bye
I would like to tell you in my article “Automatic backup of Mysql databases in Linux” how I made automatic backups of my databases in Mysql. Very useful information, especially if you don’t know this and need to make a backup.
1. Installing and configuring Mysql (I hope you have everything configured and ready to work, as there are databases for creating backups), I will provide information in my previous topics, maybe someone will need it:
There are many more topics, just search the site if necessary.
2. We create the actual backup of all databases.
First, let's create a folder, all our backups will be there, for this:
# mkdir /home/captain/backup && cd /home/captain/backup
Create a backup using the command:
# mysqldump -u root -proot -h 127.0.0.1 --all-databases | gzip >
PS: Guys, just don’t confuse the signs (` -tilde, which is similar to a regular quotation mark ‘). The mark has tildes written there.
We make sure that the file has already been created, and make sure that everything is fine:
# ls -lah
This is not always correct! So you should always think about it, did I make a backup? You need to add this command to crontab for automatic backup of mysql data, this is done like this:
# vim /etc/crontab
This option is suitable, but there is a problem, it is executed as the root user. It is advisable not to do this. The correct way would be:
# crontab -e # Automatic backup of Mysql databases on Linux 00 00 * * * /usr/bin/mysqldump -u root -proot -h 127.0.0.1 --all-databases | gzip > /home/captain/backup/my_database_backup_`date +%mm%dd%yy`.sql.gz
With this, I complete my topic “Automatic backup of Mysql databases in Linux” on my website https://site
Today I needed to set up automatic database backup from one hosting to another. I looked at several options for creating database backups and settled on the simplest - using the mysqldump utility. Now I will tell you how I did it.
What is the idea of backing up from one server to another?The idea is very simple - if one server goes down, the database can be restored from another. This is better than storing all backups on one server.
Ok, so how do we do this?And now a few words about how the backup process will take place. Let's say we have a server “A” on which there is a database, the backups of which we want to create. And there is server “B” on which we will save these backups. To make a database dump, just run the following command on the command line on the server:
mysqldump -u -p --extended-insert=false > site-$(date +%Y-%m-%d).sql
And to connect to a database on another server, just add the -h parameter, for example like this:
mysqldump -h 92.53.114.27 -u -p --extended-insert=false > site-$(date +%Y-%m-%d).sql
Well, for all this to work automatically, you need to create a cron job and write a small script in PHP that will maintain a certain number of files in the dump directory (why do we need hundreds of dumps?).
Let's start setting up automatic backup of the MySQL databaseFor work we need very little:
Write the code below into the database_backup.php file and save it on the server that will be the dump storage in any folder, let it be the /var/database_backups folder. Then create a folder where your dumps will be stored, let it be the following folder /var/database_backups/sitename .
$config = [ // ip address of the server from which we will copy the database "ip" => "11.11.111.11", // Path to the folder in which the database dumps will be located "path" => "/var/database_backups/sitename" , // Template for the name of the database dump file. Instead, the date will be substituted in the format 2015-04-19 "filenamePattern" => "dump_.sql", // Maximum number of dumps stored on the server "maxFilesCount" => 3, // Setting up a connection to the database "db" => [ " name" => "site", "user" => "root", "password" => "mysql", ], ]; $ip = !empty($config["ip"]) ? "-h $config" : ""; $filename = str_replace("", "$(date +%Y-%m-%d)", $config["filenamePattern"]); $command = "mysqldump $ip -u ($config["db"]["user"]) -p($config["db"]["password"]) --extended-insert=false ($config[ "db"]["name"]) > ($config["path"])/$filename"; exec($command); if (!empty($config["maxFilesCount"])) ( cleanDirectory($config["path"], $config["maxFilesCount"]); ) /** * Clears the directory of the files, leaving no more than $maxFilesCount number of files * * @param string $dir * @param string $maxFilesCount */ function cleanDirectory($dir, $maxFilesCount) ( $filenames = ; foreach(scandir($dir) as $file) ( $filename = " $dir/$file"; if (is_file($filename)) ( $filenames = $filename; ) ) if (count($filenames)
