MySQL Backup / Copy Backup / Restore Backup using mysqlbackup enterprise utility.

1) Add following startup parameters to my.cnf on source server under [mysqlbackup].
user = mysql_backup_user
password
socket=/var/lib/mysql/mysql.sock

2) Take full backup as image file.

#mysqlbackup --backup-dir=/usr/siva/mysql_full_backup --backup-image=full.bki backup-to-image

3) Copy the backup folder to target server. (make sure the destination directyory has right permissions)

#scp -r /usr/siva/mysql_full_backup/ siva@192.168.159.143:/usr/siva/ 

4) Stop the mysql service on destination.

systemctl stop mysqld 

5) Remove all the files in mysql data directory.

#rm -rf /var/lib/mysql/ 

6) Grant owner permissions to the account used to access the backup image file location.

#chown -R siva:siva /usr/siva/

7) Use following command to restore full by applying the logs.

#mysqlbackup --datadir=/var/lib/mysql --backup-image=/usr/siva/mysql_full_backup/full.bki --backup-dir=/usr/siva/mysql_full_backup copy-back-and-apply-log

8) Bring the service online. (Initially it failed to bring the service online, fixes are provided below)

#systemctl start mysqld 

  Issues encountered while bringing the service online.
              i)  Can't change dir to '/var/lib/mysql/' (Errcode: 13 - Permission denied)
             Fix:   #chown -R mysql:mysql /var/lib/mysql  (add permissiona to mysql user)
ii) SELinux is preventing /usr/sbin/mysqld from getattr access on the file /var/lib/mysql/ibdata1.
             Fix: #restorecon -R -v /var/lib

9) Now you should be able to successfully bring the services online.

MySQL InnoDB File Architecture

Keep in mind that /var/lib/mysql/ibdata1 is the busiest file in the InnoDB infrastructure. It normally houses six types of information:

• Table Data
• Table Indexes
• MVCC (Multiversioning Concurrency Control) Data
• Rollback Segments
• Undo Space
• Table Metadata (Data Dictionary)
• Double Write Buffer (background writing to prevent reliance on OS caching)
• Insert Buffer (managing changes to non-unique secondary indexes)
• Please find below architecture for complete details,

Access MySQL from remote machine / If remote login to MySQL fails.

By default the firewall is enabled on linux environment. We have two options to configure the firewall,

1) Turn off the firewall completely.
2) Allow remote access on the port where MySQL is listening on running.

a) Follow below steps to go with option (1).

#Disable the firewall service:
 $systemctl disable firewalld

#Stop the firewall service:
 $systemctl stop firewalld

#Verify the firewall status:
 $systemctl status firewalld

b) Follow below steps to go with option (2).

#Add the port to access list by executing following command
 $sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent

#Now reload the updated firewall configurations.
 $sudo firewall-cmd --reload

#Now if you run the check you will see the ports added to exception list.
$sudo firewall-cmd --list-all

  public (default, active)
  interfaces: eno16777736
  sources:
  services: dhcpv6-client ssh
  ports: 3306/tcp
  masquerade: no
  forward-ports:
  icmp-blocks:
  rich rules:

How to reset / recover MySQL root password

There are many ways recovering or resetting MySQL root password but the one i like & follow is below procedure,

1) Update MySQL configuration file to add "skip-grant-tables"(without quotes) under [mysqld] section. When the server is restarted this option causes the MySQL service / process to start without using the privilege system at all, which gives anyone with access to the server unrestricted access to all databases.

2) Now restart MySQL process / service issuing following command.

$ sudo systemctl restart mysqld

3) Once the server is restarted you can change the password of the root user issuing following command in sequence,

[siva@mysql ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 Server version: 5.7.14 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. 
Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> UPDATE mysql.user SET authentication_string=PASSWORD("xxxyyy") WHERE User='root';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

4) Now, you have successfully updated the root password. You have to secure the MySQL access by removing "skip-grant-tables" from my.cnf file & restart MySQL service.

5) Now you should be able to login to MySQL using updated root password.

6) But once you try to perform any operations it may throw below exception & solution follows, (here when you execute alter you may retain same passowrd or change the password based on your policy requirements)

mysql> select * from mysql.user where user="root";
ERROR 1820 (HY000):
You must reset your password using ALTER USER statement before executing this statement.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxyyyzzz';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

Install / Configure MySQL Enterprise Backup Utility

1) Identify the right package for your operating system & download. I have choose "MySQL Enterprise Backup 4.0.3 RPM for Oracle Linux / RHEL 7 x86 (64bit)" based on my operating system.

2) Execute the following command to install,

$yum install meb-4.0.3-el7.x86_64.rpm

3) If you type the command 'mysqlbackup' now you will get command not found error this is expected because the system doesn't know where the executable is located. By default the executable's are installed at location "/opt/mysql/meb-4.0"

$cd /opt/mysql/meb-4.0

4) We have to execute following command to add the path to environment variable where the system looks for all executable's when you try to execute a command. (note this is valid until your session is live, to make it permanent you have to add this to user's .bash_profile file)

$PATH=$PATH:/opt/mysql/meb-4.0/bin

5) Now execute following command to run the backup of mysql server, (I have included complete shell output)

$mysqlbackup --user=root --password  --port=3306  --socket=/var/lib/mysql/mysql.sock --backup-dir=/usr/siva/mysql_backup_new/ backup-and-appl
MySQL Enterprise Backup version 4.0.3 Linux-3.8.13-118.4.2.el7uek.x86_64-x86_64 [2016/08/04]
Copyright (c) 2003, 2016, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...
 mysqlbackup --user=root --password --port=3306
        --socket=/var/lib/mysql/mysql.sock
        --backup-dir=/usr/siva/mysql_backup_new/ backup-and-apply-log

 mysqlbackup: INFO:
Enter password:
 mysqlbackup: INFO: MySQL server version is '5.7.14-log'
 mysqlbackup: INFO: MySQL server compile os version is 'Linux'
 mysqlbackup: INFO: Got some server configuration information from running server.

IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'backup-and-apply-log' run mysqlbackup
           prints "mysqlbackup completed OK!".

160831 19:56:33 mysqlbackup: INFO: MEB logfile created at /usr/siva/mysql_backup_new/meta/MEB_2016-08-31.19-56-33_backup_apply_log.log

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir = /var/lib/mysql/
  innodb_data_home_dir =
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = /var/lib/mysql/
  innodb_log_files_in_group = 2
  innodb_log_file_size = 50331648
  innodb_page_size = 16384
  innodb_checksum_algorithm = crc32
  innodb_buffer_pool_filename = ib_buffer_pool
  innodb_undo_directory = /var/lib/mysql/
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir = /usr/siva/mysql_backup_new/datadir
  innodb_data_home_dir = /usr/siva/mysql_backup_new/datadir
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = /usr/siva/mysql_backup_new/datadir
  innodb_log_files_in_group = 2
  innodb_log_file_size = 50331648
  innodb_page_size = 16384
  innodb_checksum_algorithm = crc32
  innodb_buffer_pool_filename = ib_buffer_pool
  innodb_undo_directory = /usr/siva/mysql_backup_new/datadir
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

 mysqlbackup: INFO: Unique generated backup id for this is 14726985936584861

 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
160831 19:56:33 mysqlbackup: INFO: Full Backup operation starts with following threads
                1 read-threads    6 process-threads    1 write-threads
160831 19:56:33 mysqlbackup: INFO: System tablespace file format is Barracuda.
160831 19:56:33 mysqlbackup: INFO: Starting to copy all innodb files...
160831 19:56:33 mysqlbackup: INFO: Found checkpoint at lsn 2530912.
160831 19:56:33 mysqlbackup: INFO: Starting log scan from lsn 2530816.
160831 19:56:33 mysqlbackup: INFO: Copying log...
160831 19:56:33 mysqlbackup: INFO: Log copied, lsn 2530921.
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/ibdata1 (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/engine_cost.ibd (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/gtid_executed.ibd (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/help_category.ibd (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/help_keyword.ibd (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/help_relation.ibd (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/help_topic.ibd (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/innodb_index_stats.ibd (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/innodb_table_stats.ibd (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/plugin.ibd (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/server_cost.ibd (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/servers.ibd (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/slave_master_info.ibd (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/slave_relay_log_info.ibd (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/slave_worker_info.ibd (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/time_zone.ibd (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/time_zone_leap_second.ibd (Barracuda file format).
160831 19:56:33 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/time_zone_name.ibd (Barracuda file format).
160831 19:56:34 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/time_zone_transition.ibd (Barracuda file format).
160831 19:56:34 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/time_zone_transition_type.ibd (Barracuda file format).
160831 19:56:34 mysqlbackup: INFO: Copying /var/lib/mysql/repl01/first.ibd (Barracuda file format).
160831 19:56:34 mysqlbackup: INFO: Copying /var/lib/mysql/sys/sys_config.ibd (Barracuda file format).
160831 19:56:34 mysqlbackup: INFO: Completing the copy of innodb files.
160831 19:56:34 mysqlbackup: INFO: Starting to copy Binlog files...
160831 19:56:34 mysqlbackup: INFO: Copying /var/lib/mysql/mysql-bin.000001.
160831 19:56:34 mysqlbackup: INFO: Copying /var/lib/mysql/mysql-bin.000002.
160831 19:56:34 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
160831 19:56:34 mysqlbackup: INFO: Starting to lock all the tables...
160831 19:56:34 mysqlbackup: INFO: All tables are locked and flushed to disk
160831 19:56:34 mysqlbackup: INFO: Copying /var/lib/mysql/mysql-bin.000003.
160831 19:56:34 mysqlbackup: INFO: Completed the copy of binlog files...
160831 19:56:34 mysqlbackup: INFO: Opening backup source directory '/var/lib/mysql'
160831 19:56:34 mysqlbackup: INFO: Starting to backup all non-innodb files in
        subdirectories of '/var/lib/mysql'
160831 19:56:34 mysqlbackup: INFO: Copying the database directory 'mysql'
160831 19:56:34 mysqlbackup: INFO: Copying the database directory 'performance_schema'
160831 19:56:34 mysqlbackup: INFO: Copying the database directory 'repl01'
160831 19:56:34 mysqlbackup: INFO: Copying the database directory 'sys'
160831 19:56:34 mysqlbackup: INFO: Completing the copy of all non-innodb files.
160831 19:56:34 mysqlbackup: INFO: A copied database page was modified at 2530912.
          (This is the highest lsn found on page)
          Scanned log up to lsn 2530921.
          Was able to parse the log up to lsn 2530921.
160831 19:56:34 mysqlbackup: INFO: All tables unlocked
160831 19:56:34 mysqlbackup: INFO: All MySQL tables were locked for 0.642 seconds.
160831 19:56:34 mysqlbackup: INFO: Reading all global variables from the server.
160831 19:56:34 mysqlbackup: INFO: Completed reading of all global variables from the server.
 mysqlbackup: INFO: Writing config file for server '5.7.14-log'.
160831 19:56:34 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /usr/siva/mysql_backup_new/
160831 19:56:34 mysqlbackup: INFO: Full Backup operation completed successfully.
160831 19:56:34 mysqlbackup: INFO: Backup created in directory '/usr/siva/mysql_backup_new/'
160831 19:56:34 mysqlbackup: INFO: MySQL binlog position: filename mysql-bin.000003, position 154

-------------------------------------------------------------
   Parameters Summary
-------------------------------------------------------------
   Start LSN                  : 2530816
   End LSN                    : 2530921
-------------------------------------------------------------


 mysqlbackup: INFO: Creating 14 buffers each of size 65536.
160831 19:56:34 mysqlbackup: INFO: Apply-log operation starts with following threads
                1 read-threads    1 process-threads
 mysqlbackup: INFO: Using up to 100 MB of memory.
160831 19:56:34 mysqlbackup: INFO: ibbackup_logfile's creation parameters:
          start lsn 2530816, end lsn 2530921,
          start checkpoint 2530912.
 mysqlbackup: INFO: InnoDB: Doing recovery: scanned up to log sequence number 2530921.
 mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database...
.
InnoDB: 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 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
 mysqlbackup: INFO: InnoDB: Setting log file size to 50331648.
 mysqlbackup: INFO: InnoDB: Setting log file size to 50331648.
160831 19:56:36 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to
          lsn 2530921.
 mysqlbackup: INFO: Last MySQL binlog file position 0 154, file name mysql-bin.000003:154
160831 19:56:36 mysqlbackup: INFO: The first data file is '/usr/siva/mysql_backup_new/datadir/ibdata1'
          and the new created log files are at '/usr/siva/mysql_backup_new/datadir'
160831 19:56:36 mysqlbackup: INFO: Apply-log operation completed successfully.
160831 19:56:36 mysqlbackup: INFO: Full backup prepared for recovery successfully.

mysqlbackup completed OK!