preamble
I overheard a post in the community the other day (remembering to clear the air oncedata warehouseThe first step in the process of deleting libraries is to delete libraries (the process of deleting libraries), which is an unintentional deletion of libraries. As mentioned in the article, most of the time we just hear about the deletion of libraries, and have never encountered it, but what if it is, I am afraid that it is too late to regret, and mysql has notoracle recovery mechanism, so backup becomes a very necessary operation.
Since I have no relevant operational experience, I will start from scratch on how to data, after all, I still cherish my little blog.
concrete operation
I've exported sql files before, but I've done it directly with theNavicat Exporting is done, but this time I want to implement an automatic backup, preferably written as a script.
The basic idea: use commands to transfer database data from thedocker Exported from a container, named with a timestamp. Keep it for a maximum of 7 days, expired files are automatically deleted.
Export mysql data
indirect export
mysql export data command is still quite simple: mysqldump -u username -p database name > exported file name, but this is thelinux Ours is inside the docker, so you have to go into the container first and then execute the above command. Then you will be surprised to find that the exported file is inside your container, and then you can copy it from the container to your host. This creates a lot of sql files on the container, which you need to clean up in time for writing timed tasks.
direct export
The above method is feasible, but too much trouble, is there a one-step approach? Obviously there is, the command is like this: docker exec -it [docker container name/ID] mysqldump -u [database username] -p [database password] [database name] > [export table path], for example, my docker exec -it mysql mysqldump -uroot - p123456 solo > /var/www/. If no errors are reported, the exported database files will go to the directory you specified.
Write a script to run
I don't know how to write Shell Scripts, so I've learned how to do it in 30 minutes.
Indirect export (when exporting indirectly, please create the relevant file directory first)
/bin/sh# Enter the container. /bin/sh# Enter the container
docker exec -i mysql bash< /mysqlData/$(date +%Y%m%d).sql#Delete data older than 1 day find /mysqlData/ -mtime +1 -type f | xargs rm -rfexitEOF
# Copy the data from the backup in docker to the host.
docker cp mysql:/mysqlData/$(date +%Y%m%d).sql /var/www/html/solo/sqlData/
# Delete data older than 7 days
find /var/www/html/solo/sqlData -mtime +7 -type f | xargs rm -rf
Direct export (exec does not need to specify -it when exporting)
#! /bin/sh# Go to the file where you want to keep the data
cd /var/www/html/solo/sqlData
# Export today's sql
docker exec mysql mysqldump -uroot -p123123 solo >`date +%Y%m%d%H%M%S`.sql
# Delete sql from 7 days ago (+ followed by days, N days ago, specify directory after find)
find . -mtime +7 -type f | xargs rm -rf
postscript
The exported sql file will have some version notes and other information, such a sql file will sometimes have problems when importing, it will report errors, you need to delete the useless information in the sql file before importing.
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
This is not just one or two sentences in sql, it's all over the place. Here's a quick rule that matches to all /*!40101XXXXX*/; in the sql file, by the rule \/\*\! [0-9]+\s[\s\S]+? \s\*\/;\n to realize fast replacement.
The result after the replacement is shown below, how comfortable it is.
