mysql> create user xtbakup@'localhost' identified by 'oracle'; mysql> grant reload,process,lock tables,replication client on *.* to xtbakup@localhost; 四、执行全库备份
[root@my01 ~]# service mysql stop Shutting down MySQL.. [root@my01 db]# ls mysql xtbakup [root@my01 db]# mv mysql/ mysql_bak/ [root@my01 db]# ls mysql_bak xtbakup
查看数据库状态
[root@my01 ~]# service mysql status MySQL is not running [root@my01 ~]# service mysql start Starting MySQL.Logging to '/data/db/mysql/1221/mariadb.log'. 2018-03-20T03:26:56.919210Z mysqld_safe Directory '/data/db/mysql/1221' for UNIX socket file don't exists. ERROR! The server quit without updating PID file (/data/db/mysql/1221/my01.pid).
IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!".
innobackupex version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7) xtrabackup: cd to /data/db/xtbakup/2018-03-20_16-02-00/ xtrabackup: This target seems to be not prepared yet. InnoDB: Number of pools: 1 xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(9239084) xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 8388608 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 8388608 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) InnoDB: PUNCH HOLE support available InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Uses event mutexes InnoDB: GCC builtin __sync_synchronize() is used for memory barrier InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Number of pools: 1 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of buffer pool InnoDB: page_cleaner coordinator priority: -20 InnoDB: Highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 9239084 InnoDB: Doing recovery: scanned up to log sequence number 9239093 (0%) InnoDB: Doing recovery: scanned up to log sequence number 9239093 (0%) InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: xtrabackup: Last MySQL binlog file position 434, file name mysql-bin.000008 InnoDB: Creating shared tablespace for temporary tables InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... InnoDB: File './ibtmp1' size is now 12 MB. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) are active. InnoDB: 5.7.13 started; log sequence number 9239093 InnoDB: xtrabackup: Last MySQL binlog file position 434, file name mysql-bin.000008
xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 9239112 InnoDB: Number of pools: 1 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 InnoDB: PUNCH HOLE support available InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Uses event mutexes InnoDB: GCC builtin __sync_synchronize() is used for memory barrier InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Number of pools: 1 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of buffer pool InnoDB: page_cleaner coordinator priority: -20 InnoDB: Setting log file ./ib_logfile101 size to 48 MB InnoDB: Setting log file ./ib_logfile1 size to 48 MB InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 InnoDB: New log files created, LSN=9239112 InnoDB: Highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 9239564 InnoDB: Doing recovery: scanned up to log sequence number 9239573 (0%) InnoDB: Doing recovery: scanned up to log sequence number 9239573 (0%) InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: xtrabackup: Last MySQL binlog file position 434, file name mysql-bin.000008 InnoDB: Removed temporary tablespace data file: "ibtmp1" InnoDB: Creating shared tablespace for temporary tables InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... InnoDB: File './ibtmp1
[root@my01 ~]# cd /data/ [root@my01 data]# ls db kafka-logs zookeeper [root@my01 data]# cd db/ [root@my01 db]# ls mysql mysql_bak xtbakup [root@my01 db]# chown -R mysql.mysql mysql [root@my01 db]# ll total 0 drwxr-x---. 3 mysql mysql 18 Mar 20 11:29 mysql drwxr-xr-x. 3 mysql mysql 18 Aug 18 2017 mysql_bak drwxr-xr-x. 3 root root 33 Mar 20 11:03 xtbakup [root@my01 db]# service mysql start Starting MySQL.Logging to '/data/db/mysql/1221/mariadb.log'. SUCCESS!
[root@my01 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or /g. Your MySQL connection id is 3 Server version: 5.7.19-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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>
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t | | test | +----------------+ 2 rows in set (0.00 sec)
mysql> select count(*) from test; +----------+ | count(*) | +----------+ | 81920 | +----------+ 1 row in set (0.11 sec)
IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!".
innobackupex version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7) xtrabackup: cd to /data/db/xtbakup/2018-03-20_16-02-00/ xtrabackup: This target seems to be already prepared. InnoDB: Number of pools: 1 xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'. xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) InnoDB: PUNCH HOLE support available InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Uses event mutexes InnoDB: GCC builtin __sync_synchronize() is used for memory barrier InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Number of pools: 1 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of buffer pool InnoDB: page_cleaner coordinator priority: -20 InnoDB: Highest supported file format is Barracuda. InnoDB: Removed temporary tablespace data file: "ibtmp1" InnoDB: Creating shared tablespace for temporary tables InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... InnoDB: File './ibtmp1' size is now 12 MB. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) are active. InnoDB: 5.7.13 started; log sequence number 9262120 InnoDB: xtrabackup: Last MySQL binlog file position 1842, file name mysql-bin.000001
xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 9262139 InnoDB: Number of pools: 1 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 InnoDB: PUNCH HOLE support available InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Uses event mutexes InnoDB: GCC builtin __sync_synchronize() is used for memory barrier InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Number of pools: 1 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of buffer pool InnoDB: page_cleaner coordinator priority: -20 InnoDB: Highest supported file format is Barracuda. InnoDB: Removed temporary tablespace data file: "ibtmp1" InnoDB: Creating shared tablespace for temporary tables InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... InnoDB: File './ibtmp1' size is now 12 MB. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) are active. InnoDB: 5.7.13 started; log sequence number 9262139 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 9262158 180320 16:17:40 completed
[root@my01 ~]# service mysql status ERROR! MySQL is not running [root@my01 ~]# service mysql start Starting MySQL.Logging to '/data/db/mysql/1221/mariadb.log'. SUCCESS! [root@my01 ~]# [root@my01 ~]# [root@my01 ~]# [root@my01 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or /g. Your MySQL connection id is 3 Server version: 5.7.19-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> mysql> mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | scott | | sys | | test | +--------------------+ 6 rows in set (0.01 sec)
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t | | t1 | | test | +----------------+ 3 rows in set (0.00 sec)
