![\"\"](\"http:\/\/www.serveridol.com\/wp-content\/uploads\/2017\/04\/MySQL_backup.jpg\" "\\"\\"")
<\/a><\/p>\nAfter a Googling it is identify that MySQL Enterprise version have all those features to fast backup and restore with better performance. But it is not freely available to download :-p <\/strong>. It is identified that their backup involve the following facts<\/p>\n Note: It is not possible to take single database backup\/restore using MEB tool(did not tried :-)) <\/em><\/p>\n MEB Backup Logic <\/strong><\/p>\n a. It\u2019s possible to backup Online without having any table lock or performance change.But recommending it to execute on off business hours. MEB Restore Logic<\/strong><\/p>\n a. <\/strong>Apply-log and uncompress option will decompress the data files in backup location and make it ready for further action. This will decompress the files in backup location without over writing it. But I found that huge compressed files will be deleted once it is decompressed.<\/p>\n b.<\/strong> Copy backup option will be copy the actual data files from backup location those are decompressed and move it to \u201c\u2013datadir<\/strong>\u201d path which mention in the my.cnf <\/strong>file.<\/p>\n MEB backup involves three steps if we compress the backup. <\/strong><\/p>\n 1. How do I backup the MySQL server using MEB <\/strong><\/p>\n \u2013backup-dir :<\/em> Location of backup files to be stored 170421 00:31:53 mysqlbackup: INFO: Copying the database directory ‘Docs’ ————————————————————- mysqlbackup completed OK!<\/p>\n<\/div>\n<\/div>\n 2. How to Decompress the backup <\/strong><\/p>\n \u2013backup-dir :<\/em> backup location which MEB push the backup earlier.<\/p>\n \u00a0mysqlbackup: INFO: Creating 1 buffers each of size 65678. mysqlbackup completed OK! with 1 warnings<\/p>\n<\/div>\n<\/div>\n 3. How do I restore the MEB backup <\/strong><\/p>\n Restoration involves lots of steps. First we need to stop the MySQL service and remove the data directory. Pls take a copy of mysql database and other tables if you required. Mysql db backup can help to retain the existing permission if you are using same db names on target location.<\/p>\n a. Stop the Mysql service after taking the necessary backup. [root@ ~]# \/opt\/meb-3.8\/bin\/mysqlbackup –backup-dir=”\/var\/dbbackup\/backup-2017-04-21″ \u00a0copy-back \u00a0–datadir=\/var\/lib\/mysql –show-progress=file:\/tmp\/mebProgress`date +%Y-%m-%d-%H-%M`\u200b.log \u00a0 \u00a0 2>&1 | tee \/tmp\/mysqlrestore.`date +%Y-%m-%d-%H-%M`.log mysqlbackup: INFO: Starting with following command line … IMPORTANT: Please check that mysqlbackup run completes successfully. ——————————————————————– ——————————————————————– mysqlbackup: INFO: Uses posix_fadvise() for performance optimization. Now you would need to grant the permission to MySQL data folder and then start it.<\/p>\n ############################# Pls note MEB IS NOT FREELY DOWNLOADABLE ###############################<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":" We are maintaining a huge MySQL database server which having more than 3TB data. It\u2019s very had for us to backup, move and restore those databases from one host to another or once network to another due to size constrains. Moreover any of these activities would be involved lots of time and work precision during …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[34,36],"tags":[],"class_list":["post-3013","post","type-post","status-publish","format-standard","hentry","category-server","category-36"],"_links":{"self":[{"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/posts\/3013","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/comments?post=3013"}],"version-history":[{"count":0,"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/posts\/3013\/revisions"}],"wp:attachment":[{"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/media?parent=3013"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/categories?post=3013"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/tags?post=3013"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nb. MySQL Enterprise Backup (MEB) compress the backup in .bz<\/strong> format (This is optional and we\u2019ve chosen this since we have TB storage) and store it on \u2013backup-dir<\/strong> location. Hence the backup size will be noticeable less than the actual data storage and portable.<\/p>\n
\n\u2013socket :<\/em> Will identify the running sever and collect the mysql data location to be backed up.
\n\/tmp\/mysqlbackupFull and \u2013show-progress=file :<\/strong> These options will capture all the MEB output for identifying the progress. We uses a screen session from Linux box to trigger the backup as it will take 6-8 hours to complete.<\/p>\n
\n170421 00:31:53 mysqlbackup: INFO: Copying the database directory ‘mysql’
\n170421 00:31:53 mysqlbackup: INFO: Copying the database directory ‘performance_schema’
\n170421 00:31:53 mysqlbackup: INFO: Copying the database directory ‘test’
\n170421 00:31:53 mysqlbackup: INFO: Copying the database directory ‘test1’
\n170421 00:31:53 mysqlbackup: INFO: Copying the database directory ‘tests’
\n170421 00:31:53 mysqlbackup: INFO: Completing the copy of all non-innodb files.
\n170421 00:31:54 mysqlbackup: INFO: Progress: 20 of 20 MB; state: Copying metadata to image; compression: –
\n170421 00:31:54 mysqlbackup: INFO: A copied database page was modified at 5312129472.
\n(This is the highest lsn found on page)
\nScanned log up to lsn 5312132652.
\nWas able to parse the log up to lsn 5312132652.
\nMaximum page number for a log record 428
\n170421 00:31:54 mysqlbackup: INFO: All tables unlocked
\n170421 00:31:54 mysqlbackup: INFO: All MySQL tables were locked for 0.914 seconds.
\n170421 00:31:54 mysqlbackup: INFO: Progress: 20 of 20 MB; state: Completed; compression: –
\n170421 00:31:54 mysqlbackup: INFO: Compressed 18 MB of data files to 1092 kbytes (compression 94.08%).
\n170421 00:31:54 mysqlbackup: INFO: Compress Backup operation completed successfully.
\n170421 00:31:54 mysqlbackup: INFO: Backup created in directory ‘\/var\/dbbackup\/backup-2017-04-21\/’<\/p>\n
\nParameters Summary
\n————————————————————-
\nStart LSN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0: 5312129024
\nEnd LSN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0: 5312132652
\n————————————————————-<\/p>\n
\n170421 00:48:02 mysqlbackup: INFO: Apply-log operation starts with following threads
\n1 read-threads \u00a0 \u00a01 process-threads
\n170421 00:48:02 mysqlbackup: INFO: ibbackup_logfile’s creation parameters:
\nstart lsn 5312129024, end lsn 5312132652,
\nstart checkpoint 5312129472.
\nInnoDB: Doing recovery: scanned up to log sequence number 5312132652
\nmysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database…
\nInnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 Setting log file size to 536870912
\nInnoDB: Progress in MB: 100 200 300 400 500
\nSetting log file size to 536870912
\nInnoDB: Progress in MB: 100 200 300 400 500
\n170421 00:48:05 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to
\nlsn 5312132652.
\n170421 00:48:05 mysqlbackup: INFO: The first data file is ‘\/var\/dbbackup\/backup-2017-04-21\/datadir\/ibdata1’
\nand the new created log files are at ‘\/var\/dbbackup\/backup-2017-04-21\/datadir’
\n170421 00:48:05 mysqlbackup: INFO: Apply-log operation completed successfully.
\n170421 00:48:05 mysqlbackup: INFO: Full backup prepared for recovery successfully.<\/p>\n
\nb. Delete\/move the data folder path shown at \u2018\u2013datadir\u2019<\/strong> variable in my.cnf<\/strong> file.
\nc. Apply the below command and change the path \u2013datadir with your location.<\/p>\n
\nMySQL Enterprise Backup version 3.8.2 [2013\/06\/18]
\nCopyright (c) 2003, 2012, Oracle and\/or its affiliates. All Rights Reserved.<\/p>\n
\n\/opt\/meb-3.8\/bin\/mysqlbackup
\n–backup-dir=\/var\/dbbackup\/backup-2017-04-21 copy-back
\n–datadir=\/var\/lib\/mysql
\n–show-progress=file:\/tmp\/mebProgress2017-04-21-00-49\u200b.log<\/p>\n
\nAt the end of a successful ‘copy-back’ run mysqlbackup
\nprints “mysqlbackup completed OK!”.<\/p>\n
\nServer Repository Options:
\n——————————————————————–
\ndatadir = \/var\/lib\/mysql
\ninnodb_data_home_dir = \/var\/lib\/mysql
\ninnodb_data_file_path = ibdata1:10M:autoextend
\ninnodb_log_group_home_dir = \/var\/lib\/mysql
\ninnodb_log_files_in_group = 2
\ninnodb_log_file_size = 512M
\ninnodb_page_size = Null
\ninnodb_checksum_algorithm = innodb<\/p>\n
\nBackup Config Options:
\n——————————————————————–
\ndatadir = \/var\/dbbackup\/backup-2017-04-21\/datadir
\ninnodb_data_home_dir = \/var\/dbbackup\/backup-2017-04-21\/datadir
\ninnodb_data_file_path = ibdata1:10M:autoextend
\ninnodb_log_group_home_dir = \/var\/dbbackup\/backup-2017-04-21\/datadir
\ninnodb_log_files_in_group = 2
\ninnodb_log_file_size = 536870912
\ninnodb_page_size = 16384
\ninnodb_checksum_algorithm = innodb<\/p>\n
\nmysqlbackup: INFO: Creating 14 buffers each of size 16777216.
\n170421 00:49:50 mysqlbackup: INFO: Copy-back operation starts with following threads
\n1 read-threads \u00a0 \u00a01 write-threads
\n170421 00:49:50 mysqlbackup: INFO: Copying the log file ‘ib_logfile0’
\n170421 00:49:50 mysqlbackup: INFO: Copying the log file ‘ib_logfile1’
\n170421 00:49:50 mysqlbackup: INFO: Copying \/var\/dbbackup\/backup-2017-04-21\/datadir\/ibdata1.
\n170421 00:49:50 mysqlbackup: INFO: Copying \/var\/dbbackup\/backup-2017-04-21\/datadir\/fmi\/Document.ibd.
\n170421 00:49:50 mysqlbackup: INFO: Copying \/var\/dbbackup\/backup-2017-04-21\/datadir\/fmiDocument\/DocumentContentTypes.ibd.
\n170421 00:49:50 mysqlbackup: INFO: Copying \/var\/dbbackup\/backup-2017-04-21\/datadir\/fmi\/adsdsy.ibd.
\n170421 00:49:50 mysqlbackup: INFO: Copying \/var\/dbbackup\/backup-2017-04-21\/datadir\/testasd\/test.ibd.
\n170421 00:49:50 mysqlbackup: INFO: Copying the database directory ‘fmiDocument’
\n170421 00:49:50 mysqlbackup: INFO: Copying the database directory ‘mysql’
\n170421 00:49:50 mysqlbackup: INFO: Copying the database directory ‘performance_schema’
\n170421 00:49:50 mysqlbackup: INFO: Copying the database directory ‘test’
\n170421 00:49:50 mysqlbackup: INFO: Copying the database directory ‘test1’
\n170421 00:49:50 mysqlbackup: INFO: Copying the database directory ‘testDss’
\n170421 00:49:50 mysqlbackup: INFO: Completing the copy of all non-innodb files.
\n170421 00:49:50 mysqlbackup: INFO: Copying the log file ‘ib_logfile0’
\n170421 00:49:51 mysqlbackup: INFO: Copying the log file ‘ib_logfile1’
\n170421 00:49:53 mysqlbackup: INFO: Copy-back operation completed successfully.
\n170421 00:49:53 mysqlbackup: INFO: Finished copying backup files to ‘\/var\/lib\/mysql’
\nmysqlbackup completed OK!
\n[root@ ~]#<\/p>\n<\/div>\n<\/div>\n
\nmysqlbackup completed OK!
\n[root@fina-peak-qa01 ~]# chown -R mysql.mysql \/var\/lib\/mysql
\n[root@fina-peak-qa01 ~]# chmod -R 770 \/var\/lib\/mysql
\n[root@fina-peak-qa01 ~]# service mysql start
\nStarting MySQL.. SUCCESS!
\n[root@fina-peak-qa01 ~]#<\/div>\n<\/div>\n