Bii o ṣe le ṣe Afẹyinti/Mu pada MySQL/MariaDB ati PostgreSQL Lilo Automysqlbackup ati Awọn irinṣẹ Autopostgresqlbackup

Ti o ba jẹ olutọju ibi-ipamọ data (DBA) tabi ti o ni iduro fun mimu, ṣe afẹyinti, ati mimu-pada sipo awọn apoti isura data, o mọ pe o ko le irewesi lati padanu data. Idi naa jẹ rọrun: sisọnu data kii ṣe tumọ si isonu ti alaye pataki nikan, ṣugbọn tun le ba iṣowo rẹ jẹ olowo.

Fun idi eyi, o gbọdọ rii daju nigbagbogbo pe:

1. awọn apoti isura infomesonu rẹ ni a ṣe afẹyinti lori ipilẹ igbakọọkan,
2. awọn ifipamọ wọnyẹn wa ni fipamọ ni aaye ailewu, ati
3. o ṣe awọn adaṣe imupadabọ nigbagbogbo.

Iṣẹ ṣiṣe ti o kẹhin yii ko yẹ ki o ṣe aṣemáṣe, bi o ko ṣe fẹ lati sa sinu ọrọ pataki kan lai ṣe adaṣe ohun ti o nilo lati ṣe ni iru ipo bẹẹ.

Ninu ẹkọ yii a yoo ṣe agbekalẹ ọ si awọn ohun elo ti o wuyi meji lati ṣe afẹyinti MySQL/MariaDB ati awọn apoti isura data PostgreSQL, lẹsẹsẹ: automysqlbackup ati autopostgresqlbackup.

Niwọn igba ti igbehin da lori iṣaaju, a yoo fojusi alaye wa lori automysqlbackup ati ṣe afihan awọn iyatọ pẹlu autopgsqlbackup, ti o ba jẹ rara rara.

O ni iṣeduro niyanju lati tọju awọn ifipamọ ni ipin nẹtiwọọki kan ti a gbe sinu itọsọna afẹyinti nitorina pe ni iṣẹlẹ ti jamba eto-jakejado, iwọ yoo tun bo.

Ka atẹle awọn itọsọna to wulo lori MySQL:

Fifi MySQL/MariaDB/PostgreSQL Awọn apoti isura infomesonu sii

1. Itọsọna yii dawọle pe o gbọdọ ni apẹẹrẹ MySQL/MariaDB/PostgreSQL ti n ṣiṣẹ, Ti kii ba ṣe bẹ, jọwọ fi awọn idii wọnyi sii:

# yum update && yum install mariadb mariadb-server mariadb-libs postgresql postgresql-server postgresql-libs

# aptitude update && aptitude install mariadb-client mariadb-server mariadb-common postgresql-client postgresql postgresql-common

2. O ni ibi ipamọ data MySQL/MariaDB/PostgreSQL ti o le lo (o gba ọ niyanju lati MA lo boya automysqlbackup tabi autopostgresqlbackup ni agbegbe iṣelọpọ titi iwọ o fi mọ awọn irinṣẹ wọnyi).

Bibẹẹkọ, ṣẹda awọn apoti isura infomesonu meji ki o ṣe agbejade wọn pẹlu data ṣaaju ṣiṣe. Ninu nkan yii Emi yoo lo awọn apoti isura data wọnyi ati awọn tabili:

CREATE DATABASE mariadb_db;
CREATE TABLE tecmint_tbl (UserID INT AUTO_INCREMENT PRIMARY KEY, 
UserName VARCHAR(50), 
IsActive BOOL);

CREATE DATABASE postgresql_db;
CREATE TABLE tecmint_tbl (
UserID SERIAL PRIMARY KEY,
UserName VARCHAR(50),
IsActive BOOLEAN);

Fifi automysqlbackup ati autopgsqlbackup sori ẹrọ ni CentOS 7 ati Debian 8

3. Ninu Debian 8, awọn irinṣẹ mejeeji wa ni awọn ibi ipamọ, nitorinaa fifi wọn sii rọrun bi ṣiṣiṣẹ:

# aptitude install automysqlbackup autopostgresqlbackup

Lakoko ti o wa ni CentOS 7 iwọ yoo nilo lati ṣe igbasilẹ awọn iwe afọwọkọ fifi sori ẹrọ ati ṣiṣe wọn. Ninu awọn abala isalẹ a yoo fojusi iyasọtọ lori fifi sori ẹrọ, tunto, ati idanwo awọn irinṣẹ wọnyi lori CentOS 7 nitori fun Debian 8 - nibiti wọn fẹrẹ ṣiṣẹ lati apoti, a yoo ṣe awọn alaye pataki ni igbamiiran ni nkan yii.

4. Jẹ ki a bẹrẹ nipa ṣiṣẹda ilana iṣẹ inu /opt lati ṣe igbasilẹ iwe afọwọkọ sori ẹrọ ki o ṣiṣẹ:

# mkdir /opt/automysqlbackup
# cd /opt/automysqlbackup
# wget http://ufpr.dl.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%203.0/automysqlbackup-v3.0_rc6.tar.gz
# tar zxf automysqlbackup-v3.0_rc6.tar.gz
# ./install.sh

5. Faili iṣeto fun automysqlbackup wa ni inu/ati be be lo/automysqlbackup labẹ orukọ myserver.conf. Jẹ ki a wo awọn itọsọna iṣeto ti o yẹ julọ:

# Username to access the MySQL server
CONFIG_mysql_dump_username='root'
# Password
CONFIG_mysql_dump_password='YourPasswordHere'
# Host name (or IP address) of MySQL server
CONFIG_mysql_dump_host='localhost'
# Backup directory
CONFIG_backup_dir='/var/backup/db/automysqlbackup'
# List of databases for Daily/Weekly Backup e.g. ( 'DB1' 'DB2' 'DB3' ... )
# set to (), i.e. empty, if you want to backup all databases
CONFIG_db_names=(AddYourDatabase Names Here)
# List of databases for Monthly Backups.
# set to (), i.e. empty, if you want to backup all databases
CONFIG_db_month_names=(AddYourDatabase Names Here)
# Which day do you want monthly backups? (01 to 31)
# If the chosen day is greater than the last day of the month, it will be done
# on the last day of the month.
# Set to 0 to disable monthly backups.
CONFIG_do_monthly="01"
# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
# Set to 0 to disable weekly backups.
CONFIG_do_weekly="5"
# Set rotation of daily backups. VALUE*24hours
# If you want to keep only today's backups, you could choose 1, i.e. everything older than 24hours will be removed.
CONFIG_rotation_daily=6
# Set rotation for weekly backups. VALUE*24hours. A value of 35 means 5 weeks.
CONFIG_rotation_weekly=35
# Set rotation for monthly backups. VALUE*24hours. A value of 150 means 5 months.
CONFIG_rotation_monthly=150
# Include CREATE DATABASE statement in backup?
CONFIG_mysql_dump_create_database='no'
# Separate backup directory and file for each DB? (yes or no)
CONFIG_mysql_dump_use_separate_dirs='yes'
# Choose Compression type. (gzip or bzip2)
CONFIG_mysql_dump_compression='gzip'
# What would you like to be mailed to you?
# - log   : send only log file
# - files : send log file and sql files as attachments (see docs)
# - stdout : will simply output the log to the screen if run manually.
# - quiet : Only send logs if an error occurs to the MAILADDR.
CONFIG_mailcontent='quiet'
# Email Address to send mail to? ([email )
CONFIG_mail_address='root'
# Do you wish to encrypt your backups using openssl?
#CONFIG_encrypt='no'
# Choose a password to encrypt the backups.
#CONFIG_encrypt_password='password0123'
# Command to run before backups (uncomment to use)
#CONFIG_prebackup="/etc/mysql-backup-pre"
# Command run after backups (uncomment to use)
#CONFIG_postbackup="/etc/mysql-backup-post"

Lọgan ti o ba ti tunto automysqlbackup gẹgẹ bi awọn aini rẹ, o gba ni imọran niyanju lati ṣayẹwo faili README ti a rii ni/ati be be lo/automysqlbackup/README.

6. Nigbati o ba ṣetan, lọ siwaju ati ṣiṣe eto naa, ni gbigbasilẹ faili iṣeto bi ariyanjiyan:

# automysqlbackup /etc/automysqlbackup/myserver.conf

Ayewo yara ti itọsọna ojoojumọ yoo fihan pe automysqlbackup ti ṣiṣẹ ni aṣeyọri:

# pwd
# ls -lR daily

Dajudaju o le ṣafikun titẹsi crontab lati ṣiṣẹ automysqlbackup ni akoko kan ti ọjọ ti o baamu awọn aini rẹ julọ (1:30 am ni gbogbo ọjọ ni apẹẹrẹ isalẹ):

30 01 * * * /usr/local/bin/automysqlbackup /etc/automysqlbackup/myserver.conf

7. Nisisiyi ẹ jẹ ki a ju data mariadb_db silẹ lori idi:

Jẹ ki a tun ṣẹda lẹẹkansi ki o mu afẹyinti pada. Ninu iyara MariaDB, tẹ:

CREATE DATABASE mariadb_db;
exit

Lẹhinna wa:

# cd /var/backup/db/automysqlbackup/daily/mariadb_db
# ls

Ati mu afẹyinti pada:

# mysql -u root -p mariadb_db < daily_mariadb_db_2015-09-01_23h19m_Tuesday.sql
# mysql -u root -p
MariaDB [(none)]> USE mariadb_db; 
MariaDB [(none)]> SELECT * FROM tecmint_tb1;

Fifi ati tunto autopostgresqlbackup ni CentOS 7

8. Ni ibere fun autopostgresql lati ṣiṣẹ laisi abawọn ni CentOS 7, a yoo nilo lati fi sori ẹrọ diẹ ninu awọn igbẹkẹle akọkọ:

# yum install mutt sendmail

Lẹhinna jẹ ki o tun ṣe ilana bi tẹlẹ:

# mkdir /opt/autopostgresqlbackup
# cd /opt/autopostgresqlbackup
# wget http://ufpr.dl.sourceforge.net/project/autopgsqlbackup/AutoPostgreSQLBackup/AutoPostgreSQLBackup-1.0/autopostgresqlbackup.sh.1.0
# mv autopostgresqlbackup.sh.1.0 /opt/autopostgresqlbackup/autopostgresqlbackup.sh

Jẹ ki o jẹ ki iwe afọwọkọ ṣiṣẹ ati bẹrẹ/mu iṣẹ ṣiṣẹ:

# chmod 755 autopostgresqlbackup.sh
# systemctl start postgresql
# systemctl enable postgresql

Lakotan, a yoo satunkọ iye ti eto itọsọna itọsọna afẹyinti si:

BACKUPDIR="/var/backup/db/autopostgresqlbackup"

Lẹhin nini nipasẹ faili iṣeto ti automysqlbackup, tito leto ọpa yii rọrun pupọ (apakan iṣẹ naa ni a fi silẹ fun ọ).

9. Ni CentOS 7, ni ilodi si Debian 8, autopostgresqlbackup jẹ ṣiṣe ti o dara julọ bi olumulo eto postgres, nitorinaa lati le ṣe eyi o yẹ ki o yipada si akọọlẹ yẹn tabi ṣafikun iṣẹ cron kan si faili crontab rẹ:

# crontab -u postgres -e

30 01 * * * /opt/autopostgresqlbackup/autopostgresqlbackup.sh

Iwe itọsọna afẹyinti, nipasẹ ọna, nilo lati ṣẹda ati awọn igbanilaaye rẹ ati nini ẹgbẹ ni a gbọdọ ṣeto ni igbakọọkan si 0770 ati awọn ifiweranṣẹ (lẹẹkansi, eyi kii yoo ṣe pataki ni Debian):

# mkdir /var/backup/db/autopostgresqlbackup
# chmod -R 0770 /var/backup/db/autopostgresqlbackup
# chgrp -R postgres /var/backup/db/autopostgresqlbackup

Esi ni:

# cd /var/backup/db/autopostgresqlbackup
# pwd
# ls -lR daily

10. Bayi o le mu awọn faili pada sipo nigbati o ba nilo rẹ (ranti lati ṣe eyi bi awọn ifiweranṣẹ olumulo lẹhin atunda aaye data ofo):

# gunzip -c postgresql_db_2015-09-02.Wednesday.sql.gz | psql postgresql_db

Awọn akiyesi ni Debian 8

Gẹgẹbi a ti mẹnuba tẹlẹ, kii ṣe fifi sori awọn irinṣẹ wọnyi nikan ni Debian jẹ titọ siwaju sii, ṣugbọn tunto awọn atunto tiwọn. Iwọ yoo wa awọn faili iṣeto ni:

1. Automysqlbackup:/ati be be lo/aiyipada/automysqlbackup
2. Autopostgresqlbackup:/etc/aiyipada/autopostgresqlbackup

Akopọ

Ninu àpilẹkọ yii a ti ṣalaye bii o ṣe le fi sori ẹrọ ati lilo automysqlbackup ati autopostgresqlbackup (kikọ ẹkọ bi o ṣe le lo akọkọ yoo ṣe iranlọwọ fun ọ lati ṣakoso keji pẹlu), ibi ipamọ data nla meji ti o ṣe afẹyinti awọn irinṣẹ ti o le ṣe awọn iṣẹ rẹ bi DBA tabi alabojuto eto/ẹnjinia eto Elo rọrun.

Jọwọ ṣe akiyesi pe o le faagun lori koko yii nipa siseto awọn iwifunni imeeli tabi fifiranṣẹ awọn faili afẹyinti bi awọn asomọ nipasẹ imeeli - kii ṣe dandan ni ibeere, ṣugbọn o le wa ni ọwọ nigbakan.

Gẹgẹbi akọsilẹ ikẹhin, ranti pe awọn igbanilaaye ti awọn faili iṣeto ni o yẹ ki o ṣeto si o kere julọ (0600 ni ọpọlọpọ awọn ọran). A n reti lati gbọ ohun ti o ro nipa nkan yii. Ni idaniloju lati sọ akọsilẹ wa silẹ ni lilo fọọmu ni isalẹ.