MySql 5.7 Backup Script

MySql 5.7 Backup Script

• 1.
  Bu posta YazılımÇöplüğü web günlüğünde yayımlandı - saat: 14:30:08 tarih: 04.02.2016 MySQL Backup Script Merhaba Arkadaşlar, Bugün ki konumuz Mysql database üzerinde Backup ve Restore işlemleri. Database Uzmanları için en elzem işlerinden biridir Backup. Verilerin güvenle saklanabilmesi sonrasında geri dönüş için çok önemlidir. Bunun için alacağınız Backup türü farklılık doğurabilir. Bugün temel backup yöntemlerini göreceğiz. Database_Name = yazilimcoplugu User_Name = root Root pass = qwerty Backup dizin = /home/backup/ Standart Backup shell> mysqldump [options] > dump.sql shell> mysqldump [options] db_name [tbl_name ...] shell> mysqldump [options] --databases db_name ... shell> mysqldump [options] --all-databases # mysqldump –uUSER_NAME –p Database_Name > Backup’ın alınacağı Dizin # mysqldump –uroot –p yazilimcoplugu > /home/backup/backup.sql
• 2.
  # mysqldump –uroot–p yazilimcoplugu tablo1 > /home/backup/backup.sql # mysqldump –uroot –p yazilimcoplugu tablo1,tablo2 > /home/backup/backup.sql Yukarıda standart bir backup yönetimini gördük. Bu backup içerisinde database’e ait bütün tabloların yeniden oluşturulması için “Create Table …” ve oluşturulan tablonun içene bilgilerin eklenebilmesi için “Insert Into …” yazılarının olduğunu görürsünüz. Fonksiyonel Backup mysqldump --protocol=tcp --host=hostname --user=db_username --complete-insert=TRUE -- replace=TRUE --port=3306 --default-character-set=utf8 --single-transaction=TRUE --no-create- info=TRUE --skip-triggers –uUSER_NAME –p Database_Name > /home/backup/backup.sql mysqldump --complete-insert=TRUE --replace=TRUE --port=3306 --default-character-set=utf8 --single- transaction=TRUE --no-create-info=TRUE --skip-triggers - uroot -p yazilimcoplugu tablo1 > /home/backup/backup.sql Yukarıda aldığımız yedek bazı opsiyonları kullanarak almamızı sağlıyor. Bu backupta tabloların oluşturulma durumu yoktur. Yani aldığınız yedekte Create table ve insert into table1 ifadelerini bulamazsınız. Bu backup oluşturulan tablo üzerinden Replace yapılmasını sağlar. Bu anlamda daha hızlı yedek almanız söz konusu olabilir.
• 3.
  Aşağıda kullanabileceğiniz opsiyonlarbulunmaktadır. Dilediğinizi kullanabilirsiniz. Detaylara ihtiyaç duyarsanız benimle iletişime geçebilirsiniz. mysqldump Options Format Description Introduced Remove d --add-drop-database Add DROP DATABASE statement before each CREATE DATABASE statement --add-drop-table Add DROP TABLE statement before each CREATE TABLE statement --add-locks Surround each table dump with LOCK TABLES and UNLOCK TABLES statements --all-databases Dump all tables in all databases --allow-keywords Allow creation of column names that are keywords --apply-slave- statements Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output 5.5.3 --bind-address Use specified network interface to connect to MySQL Server 5.5.8 --character-sets-dir Directory where character sets are installed --comments Add comments to dump file --compact Produce more compact output --compatible Produce output that is more compatible with other database systems or with older MySQL servers --complete-insert Use complete INSERT statements that include column names --compress Compress all information sent between client and server --create-options Include all MySQL-specific table options in CREATE TABLE statements --databases Interpret all name arguments as database names
• 4.
  Format Description Introduced Remove d --debugWrite debugging log --debug-check Print debugging information when program exits --debug-info Print debugging information, memory, and CPU statistics when program exits --default-auth Authentication plugin to use 5.5.9 --default-character- set Specify default character set --defaults-extra-file Read named option file in addition to usual option files --defaults-file Read only named option file --defaults-group- suffix Option group suffix value --delayed-insert Write INSERT DELAYED statements rather than INSERT statements --delete-master-logs On a master replication server, delete the binary logs after performing the dump operation --disable-keys For each table, surround INSERT statements with statements to disable and enable keys --dump-date Include dump date as "Dump completed on" comment if -- comments is given --dump-slave Include CHANGE MASTER statement that lists binary log coordinates of slave's master 5.5.3 --enable-cleartext- plugin Enable cleartext authentication plugin 5.5.47 --events Dump events from dumped databases --extended-insert Use multiple-row INSERT syntax --fields-enclosed-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
• 5.
  Format Description Introduced Remove d --fields-escaped-by Thisoption is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE --fields-optionally- enclosed-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE --fields-terminated- by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE --first-slave Deprecated; use --lock-all-tables instead 5.5.3 --flush-logs Flush MySQL server log files before starting dump --flush-privileges Emit a FLUSH PRIVILEGES statement after dumping mysql database --force Continue even if an SQL error occurs during a table dump --help Display help message and exit --hex-blob Dump binary columns using hexadecimal notation --host Host to connect to (IP address or hostname) --ignore-table Do not dump given table --include-master- host-port Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump- slave 5.5.3 --insert-ignore Write INSERT IGNORE rather than INSERT statements --lines-terminated-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE --lock-all-tables Lock all tables across all databases
• 6.
  Format Description Introduced Remove d --lock-tables Lockall tables before dumping them --log-error Append warnings and errors to named file --master-data Write the binary log file name and position to the output -- max_allowed_packet Maximum packet length to send to or receive from server --net_buffer_length Buffer size for TCP/IP and socket communication --no-autocommit Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements --no-create-db Do not write CREATE DATABASE statements --no-create-info Do not write CREATE TABLE statements that re-create each dumped table --no-data Do not dump table contents --no-defaults Read no option files --no-set-names Same as --skip-set-charset --no-tablespaces Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output --opt Shorthand for --add-drop-table -- add-locks --create-options --disable- keys --extended-insert --lock-tables --quick --set-charset. --order-by-primary Dump each table's rows sorted by its primary key, or by its first unique index --password Password to use when connecting to server --pipe On Windows, connect to server using named pipe --plugin-dir Directory where plugins are installed 5.5.9
• 7.
  Format Description Introduced Remove d --port TCP/IPport number to use for connection --print-defaults Print default options --protocol Connection protocol to use --quick Retrieve rows for a table from the server a row at a time --quote-names Quote identifiers within backtick characters --replace Write REPLACE statements rather than INSERT statements --result-file Direct output to a given file --routines Dump stored routines (procedures and functions) from dumped databases --set-charset Add SET NAMES default_character_set to output --shared-memory- base-name The name of shared memory to use for shared-memory connections --single-transaction Issue a BEGIN SQL statement before dumping data from server --skip-add-drop- table Do not add a DROP TABLE statement before each CREATE TABLE statement --skip-add-locks Do not add locks --skip-comments Do not add comments to dump file --skip-compact Do not produce more compact output --skip-disable-keys Do not disable keys --skip-extended- insert Turn off extended-insert --skip-opt Turn off options set by --opt --skip-quick Do not retrieve rows for a table from the server a row at a time --skip-quote-names Do not quote identifiers --skip-set-charset Do not write SET NAMES statement --skip-triggers Do not dump triggers
• 8.
  Format Description Introduced Remove d --skip-tz-utcTurn off tz-utc --socket For connections to localhost, the Unix socket file to use --ssl Enable secure connection --ssl-ca Path of file that contains list of trusted SSL CAs --ssl-capath Path of directory that contains trusted SSL CA certificates in PEM format --ssl-cert Path of file that contains X509 certificate in PEM format --ssl-cipher List of permitted ciphers to use for connection encryption --ssl-key Path of file that contains X509 key in PEM format --ssl-verify-server- cert Verify server certificate Common Name value against host name used when connecting to server --tab Produce tab-separated data files --tables Override --databases or -B option --triggers Dump triggers for each dumped table --tz-utc Add SET TIME_ZONE='+00:00' to dump file --user MySQL user name to use when connecting to server --verbose Verbose mode --version Display version information and exit --where Dump only rows selected by given WHERE condition --xml Produce XML output Kaynak : https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html Yazan : Hızlan ERPAK