Otu esi akwado/weghachite MySQL/MariaDB na PostgreSQL Iji Automysqlbackup na Autopostgresqlbackup Tools.
Ọ bụrụ na ị bụ onye nchịkwa nchekwa data (DBA) ma ọ bụ na-ahụ maka idobe, ịkwado, na iweghachi ọdụ data, ị maara na ị gaghị enwe ike idafu data. Ihe kpatara ya dị mfe: ịlafu data ọ bụghị nanị na ọ pụtara na ọ ga-efunahụ ozi dị mkpa, kamakwa ọ nwere ike imebi azụmahịa gị n'ụzọ ego.
N'ihi nke a, ị ghaghị ijide n'aka na:
1. A na-akwado nchekwa data gị kwa oge,
2. A na-echekwa ndabere ndị ahụ na ebe nchekwa, na
3. ị na-eme usoro mweghachi mgbe niile.
A ghaghị ileghara ọrụ ikpeazụ a anya, n'ihi na ị chọghị ịbanye n'ime isi okwu na-emeghị ihe ọ bụla kwesịrị ime n'ọnọdụ dị otú ahụ.
N'ime nkuzi a, anyị ga-ewebata gị ngwa abụọ mara mma iji kwado ọdụ data MySQL/MariaDB na PostgreSQL, n'otu n'otu: automysqlbackup na autopostgresqlbackup.
Ebe ọ bụ na nke ikpeazụ dabere na nke mbụ, anyị ga-elekwasị anya nkọwa anyị na automysqlbackup ma gosipụta ọdịiche dị na autopgsqlbackup, ma ọ bụrụ na ọ bụla.
A na-atụ aro ka ị chekwaa nkwado ndabere na mpaghara netwọkụ nke etinyere na ndekọ ndabere nke mere na ọ bụrụ na mkpọka sistemụ, a ka ga-ekpuchi gị.
Gụọ ntuziaka bara uru na MySQL:
Ịwụnye MySQL/MariaDB/PostgreSQL ọdụ data
1. Ntuziaka a na-eche na ị ga-enwerịrị MySQL/MariaDB/PostgreSQL atụ na-agba ọsọ, Ọ bụrụ na ọ bụghị, biko wụnye ngwugwu ndị a:
# 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. Ị nwere ule MySQL/MariaDB/PostgreSQL nchekwa data nke ị nwere ike iji (a dụrụ gị ọdụ ka ị ghara iji ma ọ bụ automysqlbackup ma ọ bụ autopostgresqlbackup na ebe mmepụta ruo mgbe ị matara ngwaọrụ ndị a).
Ma ọ bụghị ya, mepụta ọdụ data ihe atụ abụọ wee mejupụta data tupu ịga n'ihu. N'ime edemede a, m ga-eji ọdụ data na tebụl ndị a:
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);
Ịwụnye autoysqlbackup na autopgsqlbackup na CentOS 7 na Debian 8
3. Na Debian 8, ngwaọrụ abụọ ahụ dị na ebe nchekwa, yabụ ịwụnye ha dị mfe dịka ịgba ọsọ:
# aptitude install automysqlbackup autopostgresqlbackup
Ebe na CentOS 7 ị ga-achọ ibudata edemede nrụnye wee mee ha. Na ngalaba ndị dị n'okpuru, anyị ga-elekwasị anya naanị na ịwụnye, ịhazi na ịnwale ngwaọrụ ndị a na CentOS 7 ebe ọ bụ na maka Debian 8 - ebe ọ fọrọ nke nta ka ọ rụọ ọrụ na igbe ahụ, anyị ga-eme nkọwa ndị dị mkpa ma emechaa n'isiokwu a.
4. Ka anyị malite site na ịmepụta akwụkwọ ndekọ aha n'ime /opt ka ibudata edemede nrụnye wee mee ya:
# 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. faịlụ nhazi maka automysqlbackup dị n'ime /etc/automysqlbackup n'okpuru aha myserver.conf. Ka anyị lelee ntuziaka nhazi kacha mkpa:
# 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"
Ozugbo ị haziela automysqlbackup dị ka mkpa gị si dị, a na-adụ gị ọdụ ka ị lelee faịlụ README dị na /etc/automysqlbackup/README.
6. Mgbe ị dị njikere, gaa n'ihu ma mee mmemme ahụ, na-agafe faịlụ nhazi dị ka arụmụka:
# automysqlbackup /etc/automysqlbackup/myserver.conf
Nyocha ngwa ngwa nke ndekọ ndekọ ụbọchị niile ga-egosi na automysqlbackup agbala nke ọma:
# pwd # ls -lR daily
N'ezie ị nwere ike itinye ntinye crontab ka ọ na-agba ọsọ automysqlbackup n'oge nke ụbọchị kachasị dabara gị mkpa (1:30 nke ụtụtụ kwa ụbọchị na ihe atụ dị n'okpuru):
30 01 * * * /usr/local/bin/automysqlbackup /etc/automysqlbackup/myserver.conf
7. Ugbu a, ka anyị dobe nchekwa data mariadb_db na ebumnuche:
Ka anyị mepụta ya ọzọ ma weghachite ndabere. Na ngwa ngwa MariaDB, pịnye:
CREATE DATABASE mariadb_db; exit
Mgbe ahụ chọta:
# cd /var/backup/db/automysqlbackup/daily/mariadb_db # ls
Weghachite ndabere ahụ:
# 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;
Ịwụnye na ịhazi autopostgresqlbackup na CentOS 7
8. Ka autopostgresql rụọ ọrụ na-enweghị ntụpọ na CentOS 7, anyị ga-ebu ụzọ wụnye ụfọdụ dabere:
# yum install mutt sendmail
Ka anyị kwugharịa usoro ahụ dị ka ọ dị na mbụ:
# 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
Ka anyị mee ka edemede ahụ nwee ike ịrụ ọrụ wee malite/mee ka ọrụ ahụ nwee ike:
# chmod 755 autopostgresqlbackup.sh # systemctl start postgresql # systemctl enable postgresql
N'ikpeazụ, anyị ga-edezi uru ntọala ndekọ ndabere ka ọ bụrụ:
BACKUPDIR="/var/backup/db/autopostgresqlbackup"
Mgbe ị nwetachara faịlụ nhazi nke automysqlbackup, ịhazi ngwá ọrụ a dị mfe (nke ahụ bụ akụkụ nke ọrụ ahụ ka ọ dịịrị gị).
9. Na CentOS 7, n'adịghị ka Debian 8, autopostgresqlbackup kacha mma na-agba ọsọ dị ka onye ọrụ usoro postgres, yabụ iji mee nke ahụ, ị ga-agbanwe na akaụntụ ahụ ma ọ bụ tinye ọrụ cron na faịlụ crontab ya:
# crontab -u postgres -e
30 01 * * * /opt/autopostgresqlbackup/autopostgresqlbackup.sh
Akwụkwọ ndekọ ndabere, n'ụzọ, kwesịrị ịmepụta ya na ikike ya na ikike ya ga-edozi ya ugboro ugboro na 0770 na postgres (ọzọ, nke a agaghị adị mkpa na Debian):
# mkdir /var/backup/db/autopostgresqlbackup # chmod -R 0770 /var/backup/db/autopostgresqlbackup # chgrp -R postgres /var/backup/db/autopostgresqlbackup
Nsonazụ ahụ:
# cd /var/backup/db/autopostgresqlbackup # pwd # ls -lR daily
10. Ugbu a, ị nwere ike iweghachi faịlụ mgbe achọrọ (cheta ime nke a dị ka onye ọrụ postgres mgbe ịmegharịrị nchekwa data efu):
# gunzip -c postgresql_db_2015-09-02.Wednesday.sql.gz | psql postgresql_db
Atụmatụ na Debian 8
Dị ka anyị kwuru na mbụ, ọ bụghị naanị ntinye nke ngwaọrụ ndị a na Debian dị mfe karị, kamakwa nhazi nke ha. Ị ga-ahụ faịlụ nhazi na:
- Automysqlbackup: /etc/default/automysqlbackup
- Autopostgresqlbackup: /etc/default/autopostgresqlbackup
Nchịkọta
N'isiokwu a, anyị kọwara otú e si wụnye na-eji automysqlbackup na autopostgresqlbackup (ịmụta otú e si eji nke mbụ ga-enyere gị aka ịmụta nke abụọ), abụọ nnukwu nchekwa data ndabere ngwá ọrụ nwere ike ime ka ọrụ gị dị ka a DBA ma ọ bụ usoro nchịkwa/engineer. ukwuu mfe.
Biko mara na ị nwere ike ịgbasa n'isiokwu a site na ịtọlite ịma ọkwa email ma ọ bụ zipu faịlụ ndabere dị ka mgbakwunye site na email - ọ bụghị nke a chọrọ, mana ọ nwere ike ịbịarute aka mgbe ụfọdụ.
Dịka ndetu ikpeazụ, cheta na ikike nke faịlụ nhazi kwesịrị ka edobere ya kacha nta (0600 n'ọtụtụ ọnọdụ). Anyị na-atụ anya ịnụ ihe ị chere maka edemede a. Enwere onwe gị iji mpempe akwụkwọ dị n'okpuru dobe anyị ndetu.