전체 백업
mariabackup --backup --no-lock --user="계정아이디" --password="계정_비밀번호" --target-dir= 백업_폴더_경로전체 백업 명령어이다.
- --no-lock을 통해 백업 할 때 테이블이 잠기는 것을 막는다.
결과
[00] 2023-09-15 14:13:57 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
[00] 2023-09-15 14:13:57 Using server version 10.2.43-MariaDB-log
mariabackup based on MariaDB server 10.2.44-MariaDB Linux (x86_64)
[00] 2023-09-15 14:13:57 uses posix_fadvise().
[00] 2023-09-15 14:13:57 cd to /var/lib/mysql/
[00] 2023-09-15 14:13:57 open files limit requested 0, set to 1024
[00] 2023-09-15 14:13:57 mariabackup: using the following InnoDB configuration:
[00] 2023-09-15 14:13:57 innodb_data_home_dir =
[00] 2023-09-15 14:13:57 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2023-09-15 14:13:57 innodb_log_group_home_dir = ./
[00] 2023-09-15 14:13:57 InnoDB: Using Linux native AIO
2023-09-15 14:13:57 140446462982336 [Note] InnoDB: Number of pools: 1
[00] 2023-09-15 14:13:57 mariabackup: Generating a list of tablespaces
[00] 2023-09-15 14:13:57 >> log scanned up to (1683174)
[01] 2023-09-15 14:13:57 Copying ibdata1 to /root/backup/ibdata1
[01] 2023-09-15 14:13:57 ...done
[01] 2023-09-15 14:13:57 Copying ./mysql/innodb_table_stats.ibd to /root/backup/mysql/innodb_table_stats.ibd
[01] 2023-09-15 14:13:57 ...done
[01] 2023-09-15 14:13:57 Copying ./mysql/innodb_index_stats.ibd to /root/backup/mysql/innodb_index_stats.ibd
[01] 2023-09-15 14:13:57 ...done
[01] 2023-09-15 14:13:57 Copying ./mysql/gtid_slave_pos.ibd to /root/backup/mysql/gtid_slave_pos.ibd
[01] 2023-09-15 14:13:57 ...done
[01] 2023-09-15 14:13:57 Copying ./dbtest/datetest.ibd to /root/backup/dbtest/datetest.ibd
[01] 2023-09-15 14:13:57 ...done
[01] 2023-09-15 14:13:57 Copying ./dbtest/item.ibd to /root/backup/dbtest/item.ibd
[01] 2023-09-15 14:13:57 ...done
[00] 2023-09-15 14:13:58 Starting to backup non-InnoDB tables and files
[00] 2023-09-15 14:13:58 >> log scanned up to (1683174)
[01] 2023-09-15 14:13:58 Copying ./mysql/db.opt to /root/backup/mysql/db.opt
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/db.frm to /root/backup/mysql/db.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/db.MYI to /root/backup/mysql/db.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/db.MYD to /root/backup/mysql/db.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/host.frm to /root/backup/mysql/host.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/host.MYI to /root/backup/mysql/host.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/host.MYD to /root/backup/mysql/host.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/user.frm to /root/backup/mysql/user.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/user.MYI to /root/backup/mysql/user.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/user.MYD to /root/backup/mysql/user.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/roles_mapping.frm to /root/backup/mysql/roles_mapping.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/roles_mapping.MYI to /root/backup/mysql/roles_mapping.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/roles_mapping.MYD to /root/backup/mysql/roles_mapping.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/func.frm to /root/backup/mysql/func.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/func.MYI to /root/backup/mysql/func.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/func.MYD to /root/backup/mysql/func.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/plugin.frm to /root/backup/mysql/plugin.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/plugin.MYI to /root/backup/mysql/plugin.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/plugin.MYD to /root/backup/mysql/plugin.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/servers.frm to /root/backup/mysql/servers.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/servers.MYI to /root/backup/mysql/servers.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/servers.MYD to /root/backup/mysql/servers.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/tables_priv.frm to /root/backup/mysql/tables_priv.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/tables_priv.MYI to /root/backup/mysql/tables_priv.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/tables_priv.MYD to /root/backup/mysql/tables_priv.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/columns_priv.frm to /root/backup/mysql/columns_priv.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/columns_priv.MYI to /root/backup/mysql/columns_priv.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/columns_priv.MYD to /root/backup/mysql/columns_priv.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/help_topic.frm to /root/backup/mysql/help_topic.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/help_topic.MYI to /root/backup/mysql/help_topic.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/help_topic.MYD to /root/backup/mysql/help_topic.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/help_category.frm to /root/backup/mysql/help_category.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/help_category.MYI to /root/backup/mysql/help_category.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/help_category.MYD to /root/backup/mysql/help_category.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/help_relation.frm to /root/backup/mysql/help_relation.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/help_relation.MYI to /root/backup/mysql/help_relation.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/help_relation.MYD to /root/backup/mysql/help_relation.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/help_keyword.frm to /root/backup/mysql/help_keyword.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/help_keyword.MYI to /root/backup/mysql/help_keyword.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/help_keyword.MYD to /root/backup/mysql/help_keyword.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/time_zone_name.frm to /root/backup/mysql/time_zone_name.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/time_zone_name.MYI to /root/backup/mysql/time_zone_name.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/time_zone_name.MYD to /root/backup/mysql/time_zone_name.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/time_zone.frm to /root/backup/mysql/time_zone.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/time_zone.MYI to /root/backup/mysql/time_zone.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/time_zone.MYD to /root/backup/mysql/time_zone.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/time_zone_transition.frm to /root/backup/mysql/time_zone_transition.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/time_zone_transition.MYI to /root/backup/mysql/time_zone_transition.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/time_zone_transition.MYD to /root/backup/mysql/time_zone_transition.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/time_zone_transition_type.frm to /root/backup/mysql/time_zone_transition_type.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/time_zone_transition_type.MYI to /root/backup/mysql/time_zone_transition_type.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/time_zone_transition_type.MYD to /root/backup/mysql/time_zone_transition_type.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/time_zone_leap_second.frm to /root/backup/mysql/time_zone_leap_second.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/time_zone_leap_second.MYI to /root/backup/mysql/time_zone_leap_second.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/time_zone_leap_second.MYD to /root/backup/mysql/time_zone_leap_second.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/proc.frm to /root/backup/mysql/proc.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/proc.MYI to /root/backup/mysql/proc.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/proc.MYD to /root/backup/mysql/proc.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/procs_priv.frm to /root/backup/mysql/procs_priv.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/procs_priv.MYI to /root/backup/mysql/procs_priv.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/procs_priv.MYD to /root/backup/mysql/procs_priv.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/general_log.frm to /root/backup/mysql/general_log.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/general_log.CSM to /root/backup/mysql/general_log.CSM
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/general_log.CSV to /root/backup/mysql/general_log.CSV
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/slow_log.frm to /root/backup/mysql/slow_log.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/slow_log.CSM to /root/backup/mysql/slow_log.CSM
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/slow_log.CSV to /root/backup/mysql/slow_log.CSV
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/event.frm to /root/backup/mysql/event.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/event.MYI to /root/backup/mysql/event.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/event.MYD to /root/backup/mysql/event.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/innodb_table_stats.frm to /root/backup/mysql/innodb_table_stats.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/innodb_index_stats.frm to /root/backup/mysql/innodb_index_stats.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/proxies_priv.frm to /root/backup/mysql/proxies_priv.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/proxies_priv.MYI to /root/backup/mysql/proxies_priv.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/proxies_priv.MYD to /root/backup/mysql/proxies_priv.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/table_stats.frm to /root/backup/mysql/table_stats.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/table_stats.MYI to /root/backup/mysql/table_stats.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/table_stats.MYD to /root/backup/mysql/table_stats.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/column_stats.frm to /root/backup/mysql/column_stats.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/column_stats.MYI to /root/backup/mysql/column_stats.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/column_stats.MYD to /root/backup/mysql/column_stats.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/index_stats.frm to /root/backup/mysql/index_stats.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/index_stats.MYI to /root/backup/mysql/index_stats.MYI
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/index_stats.MYD to /root/backup/mysql/index_stats.MYD
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./mysql/gtid_slave_pos.frm to /root/backup/mysql/gtid_slave_pos.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./test/db.opt to /root/backup/test/db.opt
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./performance_schema/db.opt to /root/backup/performance_schema/db.opt
[01] 2023-09-15 14:13:58 ...done
[00] 2023-09-15 14:13:58 Writing query_log/db.opt
[00] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./dbtest/db.opt to /root/backup/dbtest/db.opt
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./dbtest/datetest.frm to /root/backup/dbtest/datetest.frm
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./dbtest/item.frm to /root/backup/dbtest/item.frm
[01] 2023-09-15 14:13:58 ...done
[00] 2023-09-15 14:13:58 Finished backing up non-InnoDB tables and files
[01] 2023-09-15 14:13:58 Copying ./aria_log_control to /root/backup/aria_log_control
[01] 2023-09-15 14:13:58 ...done
[01] 2023-09-15 14:13:58 Copying ./aria_log.00000001 to /root/backup/aria_log.00000001
[01] 2023-09-15 14:13:58 ...done
[00] 2023-09-15 14:13:58 Waiting for log copy thread to read lsn 0
[00] 2023-09-15 14:13:59 >> log scanned up to (1683174)
[00] 2023-09-15 14:13:59 Writing xtrabackup_binlog_info
[00] 2023-09-15 14:13:59 ...done
[00] 2023-09-15 14:13:59 The latest check point (for incremental): '1683165'
mariabackup: Stopping log copying thread.[00] 2023-09-15 14:13:59 >> log scanned up to (1683174)
[00] 2023-09-15 14:14:00 >> log scanned up to (1683174)
[00] 2023-09-15 14:14:00 Copying ib_buffer_pool to /root/backup/ib_buffer_pool
[00] 2023-09-15 14:14:00 ...done
[00] 2023-09-15 14:14:00 Backup created in directory '/root/backup/'
[00] 2023-09-15 14:14:00 MySQL binlog position: filename '.000013', position '333', GTID of the last change '0-1-27'
[00] 2023-09-15 14:14:00 Writing backup-my.cnf
[00] 2023-09-15 14:14:00 ...done
[00] 2023-09-15 14:14:00 Writing xtrabackup_info
[00] 2023-09-15 14:14:00 ...done
[00] 2023-09-15 14:14:00 Redo log (from LSN 1683165 to 1683174) was copied.
[00] 2023-09-15 14:14:00 completed OK!
정상적으로 백업이 된 것은 알 수 있다.
백업 폴더에 과연 무엇이 있을까?
drwxr-xr-x 7 root root 297 Sep 15 14:14 .
dr-xr-x---. 7 root root 222 Sep 15 14:04 ..
-rw-r----- 1 root root 16384 Sep 15 14:13 aria_log.00000001
-rw-r----- 1 root root 52 Sep 15 14:13 aria_log_control
-rw-r----- 1 root root 325 Sep 15 14:14 backup-my.cnf
drwx------ 2 root root 92 Sep 15 14:13 dbtest
-rw-r----- 1 root root 1220 Sep 15 14:14 ib_buffer_pool
-rw-r----- 1 root root 12582912 Sep 15 14:13 ibdata1
-rw-r----- 1 root root 2560 Sep 15 14:14 ib_logfile0
drwx------ 2 root root 4096 Sep 15 14:13 mysql
drwx------ 2 root root 20 Sep 15 14:13 performance_schema
drwx------ 2 root root 20 Sep 15 14:13 query_log
drwx------ 2 root root 20 Sep 15 14:13 test
-rw-r----- 1 root root 19 Sep 15 14:13 xtrabackup_binlog_info
-rw-r----- 1 root root 77 Sep 15 14:14 xtrabackup_checkpoints
-rw-r----- 1 root root 536 Sep 15 14:14 xtrabackup_infodbtest라는 디렉터리가 있는데 이게 내가 테스트하는 테이블이다.
drwx------ 2 root root 92 Sep 15 14:13 .
drwxr-xr-x 7 root root 297 Sep 15 14:14 ..
-rw-r----- 1 root root 475 Sep 15 14:13 datetest.frm
-rw-r----- 1 root root 98304 Sep 15 14:13 datetest.ibd
-rw-r----- 1 root root 65 Sep 15 14:13 db.opt
-rw-r----- 1 root root 1246 Sep 15 14:13 item.frm
-rw-r----- 1 root root 98304 Sep 15 14:13 item.ibd뭔가 다양한 정보가 있는 것 같다.
