Yadda ake Ajiyayyen/Maida MySQL/MariaDB da PostgreSQL Amfani da Automysqlbackup da Autopostgresqlbackup Tools


Idan kai mai gudanar da bayanai ne (DBA) ko kuma ke da alhakin kiyayewa, tallafi, da maido da bayanan bayanai, kun san ba za ku iya samun damar rasa bayanai ba. Dalilin yana da sauƙi: rasa bayanai ba kawai yana nufin asarar mahimman bayanai ba, amma kuma yana iya lalata kasuwancin ku da kuɗi.

Don wannan dalili, dole ne ku tabbatar da cewa:

1. Ana adana bayananku akan lokaci-lokaci,
2. Ana adana waɗancan abubuwan ajiyar a wuri mai aminci, kuma
3. Kuna yin aikin gyaran jiki akai-akai.

Wannan aiki na ƙarshe bai kamata a yi watsi da shi ba, saboda ba kwa son shiga cikin babban al'amari ba tare da yin aiki da abin da ya kamata a yi a irin wannan yanayin ba.

A cikin wannan koyawa za mu gabatar muku da kyawawan abubuwan amfani guda biyu don adana bayanan MySQL/MariaDB da PostgreSQL, bi da bi: automysqlbackup da autopostgresqlbackup.

Tun da na karshen ya dogara ne akan tsohon, za mu mai da hankali kan bayanin mu akan automysqlbackup kuma mu haskaka bambance-bambance tare da autopgsqlbackup, idan akwai.

Ana ba da shawarar sosai don adana ma'ajin a cikin rabon hanyar sadarwa da aka ɗora a cikin kundin adireshi ta yadda idan aka sami faɗuwar tsarin, har yanzu za a rufe ku.

Karanta waɗannan jagororin masu amfani akan MySQL:

Shigar da MySQL/MariaDB/PostgreSQL Databases

1. Wannan jagorar yana ɗauka cewa dole ne ku sami MySQL/MariaDB/PostgreSQL misali yana gudana, Idan ba haka ba, da fatan za a shigar da fakiti masu zuwa:

# 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. Kuna da gwajin MySQL/MariaDB/PostgreSQL da za ku iya amfani da su (an shawarce ku da kada ku yi amfani da ko dai automysqlbackup ko autopostgresqlbackup a cikin yanayin samarwa har sai kun saba da waɗannan kayan aikin).

In ba haka ba, ƙirƙiri samfuran bayanai guda biyu kuma cika su da bayanai kafin a ci gaba. A cikin wannan labarin zan yi amfani da bayanan bayanai da tebur masu zuwa:

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);

Sanya autoysqlbackup da autopgsqlbackup a cikin CentOS 7 da Debian 8

3. A cikin Debian 8, duka kayan aikin suna samuwa a cikin ma'ajin, don haka shigar da su yana da sauƙi kamar gudu:

# aptitude install automysqlbackup autopostgresqlbackup

Ganin cewa a cikin CentOS 7 kuna buƙatar zazzage rubutun shigarwa kuma gudanar da su. A cikin sassan da ke ƙasa za mu mai da hankali ne kawai akan shigarwa, daidaitawa, da gwada waɗannan kayan aikin akan CentOS 7 tun don Debian 8 - inda suke kusan aiki daga cikin akwatin, za mu yi bayanin da ya dace daga baya a cikin wannan labarin.

4. Bari mu fara da ƙirƙirar kundin aiki a cikin /opt don zazzage rubutun shigarwa kuma gudanar da shi:

# 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. Fayil ɗin daidaitawa don automysqlbackup yana cikin /etc/automysqlbackup ƙarƙashin sunan myserver.conf. Bari mu duba mafi dacewa umarnin daidaitawa:

# 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"

Da zarar kun saita autoysqlbackup kamar yadda kuke buƙata, ana ba ku shawara mai ƙarfi don bincika fayil ɗin README da aka samo a /etc/automysqlbackup/README.

6. Lokacin da ka shirya, ci gaba da gudanar da shirin, wuce fayil ɗin sanyi a matsayin hujja:

# automysqlbackup /etc/automysqlbackup/myserver.conf

Bincike mai sauri na kundin adireshin yau da kullun zai nuna cewa automysqlbackup ya gudana cikin nasara:

# pwd
# ls -lR daily

Tabbas zaku iya ƙara shigarwar crontab don gudanar da automysqlbackup a lokacin rana wanda ya fi dacewa da bukatunku (1:30 na safe kowace rana a cikin misalin da ke ƙasa):

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

7. Yanzu bari mu sauke mariadb_db database da gangan:

Bari mu sake ƙirƙira shi kuma mu dawo da madadin. A cikin MariaDB faɗakarwa, rubuta:

CREATE DATABASE mariadb_db;
exit

Sannan gano wuri:

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

Kuma mayar da madadin:

# 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;

Shigarwa da daidaita autopostgresqlbackup a cikin CentOS 7

8. Domin autopostgresql yayi aiki mara kyau a cikin CentOS 7, zamu buƙaci shigar da wasu abubuwan dogaro da farko:

# yum install mutt sendmail

Sa'an nan kuma bari a maimaita tsari kamar da:

# 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

Bari mu sanya rubutun aiwatarwa kuma mu fara/kunna sabis:

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

A ƙarshe, za mu gyara ƙimar saitin kundin adireshi zuwa:

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

Bayan samun ta fayil ɗin sanyi na automysqlbackup, saita wannan kayan aiki yana da sauƙi (waɗannan ɓangaren aikin ya rage naku).

9. A cikin CentOS 7, sabanin Debian 8, autopostgresqlbackup shine mafi kyawun aiki azaman mai amfani da tsarin postgres, don haka don yin hakan ya kamata ku canza zuwa wannan asusun ko ƙara aikin cron a cikin fayil ɗin crontab:

# crontab -u postgres -e
30 01 * * * /opt/autopostgresqlbackup/autopostgresqlbackup.sh

A madadin, ta hanya, yana buƙatar ƙirƙirar kuma izini da ikon mallakar rukuni dole ne a saita shi akai-akai zuwa 0770 da postgres (kuma, wannan ba zai zama dole ba a Debian):

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

Sakamakon:

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

10. Yanzu zaku iya dawo da fayilolin lokacin da ake buƙata (tuna don yin wannan azaman postgres mai amfani bayan sake ƙirƙirar bayanan da ba komai):

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

Abubuwan da ke cikin Debian 8

Kamar yadda muka ambata a baya, ba kawai shigar da waɗannan kayan aikin a cikin Debian ya fi sauƙi ba, har ma da daidaitawar su. Za ku sami fayilolin daidaitawa a:

  1. Automysqlbackup: /etc/default/automysqlbackup
  2. Autopostgresqlbackup: /etc/default/autopostgresqlbackup

Takaitawa

A cikin wannan labarin mun bayyana yadda ake shigarwa da amfani da automysqlbackup da autopostgresqlbackup (koyan yadda ake amfani da na farko zai taimake ka ka mallaki na biyu kuma), manyan kayan aikin adana bayanai guda biyu waɗanda zasu iya yin ayyukanka a matsayin DBA ko tsarin gudanarwa/injiniya. yafi sauki.

Lura cewa za ku iya faɗaɗa kan wannan batu ta hanyar saita sanarwar imel ko aika fayilolin ajiya azaman haɗe-haɗe ta imel - ba a buƙata sosai ba, amma na iya zuwa da amfani wani lokaci.

A matsayin bayanin kula na ƙarshe, tuna cewa ya kamata a saita izini na fayilolin sanyi zuwa mafi ƙanƙanta (0600 a mafi yawan lokuta). Muna sa ran jin ra'ayin ku game da wannan labarin. Jin kyauta don sauke mana bayanin kula ta amfani da fom ɗin da ke ƙasa.