本站使用了 Pjax 等基于 JavaScript 的开发技术,但您的浏览器已禁用 JavaScript,请开启 JavaScript 以保证网站正常显示!

MySQL全量备份

当数据库跑了较长时间后,存储的数据将越来越多,这时候往往也意味着,一旦数据库服务器出现宕机等相关状况,将给我们的业务带来巨大的影响,甚至可能是具备一定的毁灭性的,因此,即使对数据库进行备份是极其重要的。接下来,我们一起来学习全量备份的实现方式。

环境说明:

  • 数据库服务器:172.18.250.208
  • 备用服务器:172.18.251.4

备份数据库服务器

请保留出处

  • 首先,我们先在数据库服务器上创建一个数据库,并写入一些数据
[root@node1 ~]# yum -y install mariadb-server #安装数据库
[root@node1 ~]# systemctl enable mariadb #设定为开机自启动
[root@node1 ~]# systemctl start mariadb #启动服务
[root@node1 ~]# mysql_secure_installation #初始化数据库
[root@node1 ~]# mysql -uroot -pguomai
MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use mydb;
Database changed
MariaDB [mydb]> create table test (
    -> id int primary key not null auto_increment,
    -> name varchar(30),
    -> comment varchar(100)
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [mydb]> insert into test values (1,"xiaomhua","fujian"),(2,"xiaoming","beijing"),(3,"xiaohong","hainan");
Query OK, 3 rows affected (0.30 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [mydb]> select * from mydb.test;
+----+----------+---------+
| id | name     | comment |
+----+----------+---------+
|  1 | xiaomhua | fujian  |
|  2 | xiaoming | beijing |
|  3 | xiaohong | hainan  |
+----+----------+---------+
3 rows in set (0.00 sec)
  • 备份
[root@node1 ~]# mkdir -p /mydata/backups #准备备份目录
[root@node1 ~]# yum -y install percona-xtrabackup #安装备份工具,注意,该工具需要epel源的支持
[root@node1 ~]# innobackupex --user=root --password=guomai --host=localhost /mydata/backups/ #以root用户的身份将本机所有数据库备份到指定目录下

命令执行结束后,如看到下图样式的completed OK!说明备份成功,否则失败。

mysqlbackupall

  • 查看是否备份成功
[root@node1 ~]# tree /mydata/backups/ -L 2 #备份文件的树形结构图
/mydata/backups/
└── 2017-11-19_06-07-40
    ├── backup-my.cnf
    ├── ibdata1
    ├── mydb
    ├── mysql
    ├── performance_schema
    ├── xtrabackup_checkpoints
    ├── xtrabackup_info
    └── xtrabackup_logfile

4 directories, 5 files
  • 导出备份

出于安全考虑,我们不应将备份的数据放置在数据库服务器上,否则一旦数据库服务器出现宕机等意外,就功亏一篑了。一般建议存储到特定的存储设备或内网备份专用服务器上,这里,为了演示方便,我们之间将备份数据传输到备用的恢复服务器上。

This article is not authorized to post. Get authorization.
[root@node1 ~]# scp -r /mydata/backups/2017-11-19_06-07-40 root@172.18.251.4:/backup

恢复到目标服务器

  • 安装Mariadb Server
[root@node2 ~]# yum -y install mariadb-server
[root@node2 ~]# systemctl enable mariadb #注意,做全量恢复时,目标SQL服务器上不应启动数据库服务,也不要初始化
  • 安装备份恢复工具
[root@node2 ~]# yum -y install percona-xtrabackup
  • 执行恢复作业(假定,原先数据库服务器宕机了)
[root@node2 ~]# cd /backup/2017-11-19_06-07-40/
[root@node2 2017-11-19_06-07-40]# innobackupex --apply-log ./
171119 06:28:14 innobackupex: Starting the apply-log operation

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.3.6 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )
xtrabackup: cd to /backup/2017-11-19_06-07-40/
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1601620)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 0 and 0 in ibdata files do not match the log sequence number 1601620 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages 
InnoDB: from the doublewrite buffer...
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 1601620

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1601630
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 5 MB
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=1601630
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 1602060
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1602070
171119 06:28:18 completed OK!

[root@node2 2017-11-19_06-07-40]# innobackupex --copy-back ./ #将当前目录下的数据拷贝到相应目录 
171119 06:29:03 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

innobackupex version 2.3.6 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )
171119 06:29:03 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
171119 06:29:03 [01]        ...done
171119 06:29:03 [01] Copying ib_logfile1 to /var/lib/mysql/ib_logfile1
171119 06:29:04 [01]        ...done
171119 06:29:04 [01] Copying ibdata1 to /var/lib/mysql/ibdata1
171119 06:29:04 [01]        ...done
171119 06:29:04 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
171119 06:29:04 [01]        ...done
171119 06:29:04 [01] Copying ./performance_schema/events_waits_history_long.frm to /var/lib/mysql/performance_schema/events_waits_history_long.frm
171119 06:29:04 [01]        ...done
171119 06:29:04 [01] Copying ./performance_schema/setup_instruments.frm to /var/lib/mysql/performance_schema/setup_instruments.frm
171119 06:29:04 [01]        ...done
171119 06:29:04 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /var/lib/mysql/performance_schema/events_waits_summary_global_by_event_name.frm
171119 06:29:04 [01]        ...done
171119 06:29:04 [01] Copying ./performance_schema/setup_consumers.frm to /var/lib/mysql/performance_schema/setup_consumers.frm
171119 06:29:04 [01]        ...done
171119 06:29:04 [01] Copying ./performance_schema/events_waits_summary_by_instance.frm to /var/lib/mysql/performance_schema/events_waits_summary_by_instance.frm
171119 06:29:04 [01]        ...done
171119 06:29:04 [01] Copying ./performance_schema/file_instances.frm to /var/lib/mysql/performance_schema/file_instances.frm
171119 06:29:04 [01]        ...done
171119 06:29:05 [01] Copying ./performance_schema/file_summary_by_instance.frm to /var/lib/mysql/performance_schema/file_summary_by_instance.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /var/lib/mysql/performance_schema/events_waits_summary_by_thread_by_event_name.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./performance_schema/performance_timers.frm to /var/lib/mysql/performance_schema/performance_timers.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./performance_schema/rwlock_instances.frm to /var/lib/mysql/performance_schema/rwlock_instances.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./performance_schema/db.opt to /var/lib/mysql/performance_schema/db.opt
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./performance_schema/events_waits_current.frm to /var/lib/mysql/performance_schema/events_waits_current.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /var/lib/mysql/performance_schema/file_summary_by_event_name.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./performance_schema/cond_instances.frm to /var/lib/mysql/performance_schema/cond_instances.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./performance_schema/threads.frm to /var/lib/mysql/performance_schema/threads.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./performance_schema/setup_timers.frm to /var/lib/mysql/performance_schema/setup_timers.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./performance_schema/events_waits_history.frm to /var/lib/mysql/performance_schema/events_waits_history.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./performance_schema/mutex_instances.frm to /var/lib/mysql/performance_schema/mutex_instances.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mydb/test.frm to /var/lib/mysql/mydb/test.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mydb/db.opt to /var/lib/mysql/mydb/db.opt
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/procs_priv.MYD to /var/lib/mysql/mysql/procs_priv.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/help_category.MYI to /var/lib/mysql/mysql/help_category.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/help_relation.MYI to /var/lib/mysql/mysql/help_relation.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/user.MYD to /var/lib/mysql/mysql/user.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/tables_priv.frm to /var/lib/mysql/mysql/tables_priv.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/proc.frm to /var/lib/mysql/mysql/proc.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/ndb_binlog_index.frm to /var/lib/mysql/mysql/ndb_binlog_index.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/servers.frm to /var/lib/mysql/mysql/servers.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/proc.MYD to /var/lib/mysql/mysql/proc.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/time_zone_leap_second.MYI to /var/lib/mysql/mysql/time_zone_leap_second.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/servers.MYD to /var/lib/mysql/mysql/servers.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/func.MYD to /var/lib/mysql/mysql/func.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/help_relation.frm to /var/lib/mysql/mysql/help_relation.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/general_log.CSM to /var/lib/mysql/mysql/general_log.CSM
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/user.MYI to /var/lib/mysql/mysql/user.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/time_zone.MYD to /var/lib/mysql/mysql/time_zone.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/general_log.frm to /var/lib/mysql/mysql/general_log.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/time_zone.MYI to /var/lib/mysql/mysql/time_zone.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/plugin.MYD to /var/lib/mysql/mysql/plugin.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/help_topic.MYI to /var/lib/mysql/mysql/help_topic.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/time_zone_name.MYD to /var/lib/mysql/mysql/time_zone_name.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/time_zone_transition_type.frm to /var/lib/mysql/mysql/time_zone_transition_type.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/help_keyword.MYD to /var/lib/mysql/mysql/help_keyword.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/help_topic.frm to /var/lib/mysql/mysql/help_topic.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/host.MYI to /var/lib/mysql/mysql/host.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/db.MYD to /var/lib/mysql/mysql/db.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/time_zone_transition.MYD to /var/lib/mysql/mysql/time_zone_transition.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/time_zone_transition.MYI to /var/lib/mysql/mysql/time_zone_transition.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/time_zone_transition_type.MYI to /var/lib/mysql/mysql/time_zone_transition_type.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/help_category.frm to /var/lib/mysql/mysql/help_category.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/slow_log.CSM to /var/lib/mysql/mysql/slow_log.CSM
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/help_keyword.frm to /var/lib/mysql/mysql/help_keyword.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/user.frm to /var/lib/mysql/mysql/user.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/proxies_priv.MYI to /var/lib/mysql/mysql/proxies_priv.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/time_zone_leap_second.frm to /var/lib/mysql/mysql/time_zone_leap_second.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/time_zone_transition_type.MYD to /var/lib/mysql/mysql/time_zone_transition_type.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/proc.MYI to /var/lib/mysql/mysql/proc.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/event.frm to /var/lib/mysql/mysql/event.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/columns_priv.MYI to /var/lib/mysql/mysql/columns_priv.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/help_relation.MYD to /var/lib/mysql/mysql/help_relation.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/event.MYI to /var/lib/mysql/mysql/event.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/func.MYI to /var/lib/mysql/mysql/func.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/ndb_binlog_index.MYD to /var/lib/mysql/mysql/ndb_binlog_index.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/time_zone_leap_second.MYD to /var/lib/mysql/mysql/time_zone_leap_second.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/plugin.MYI to /var/lib/mysql/mysql/plugin.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/db.MYI to /var/lib/mysql/mysql/db.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/plugin.frm to /var/lib/mysql/mysql/plugin.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/columns_priv.frm to /var/lib/mysql/mysql/columns_priv.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/tables_priv.MYD to /var/lib/mysql/mysql/tables_priv.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/columns_priv.MYD to /var/lib/mysql/mysql/columns_priv.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/time_zone_name.frm to /var/lib/mysql/mysql/time_zone_name.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/host.MYD to /var/lib/mysql/mysql/host.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/slow_log.CSV to /var/lib/mysql/mysql/slow_log.CSV
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/general_log.CSV to /var/lib/mysql/mysql/general_log.CSV
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/proxies_priv.MYD to /var/lib/mysql/mysql/proxies_priv.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/help_keyword.MYI to /var/lib/mysql/mysql/help_keyword.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/tables_priv.MYI to /var/lib/mysql/mysql/tables_priv.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/slow_log.frm to /var/lib/mysql/mysql/slow_log.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/proxies_priv.frm to /var/lib/mysql/mysql/proxies_priv.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/procs_priv.frm to /var/lib/mysql/mysql/procs_priv.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/ndb_binlog_index.MYI to /var/lib/mysql/mysql/ndb_binlog_index.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/procs_priv.MYI to /var/lib/mysql/mysql/procs_priv.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/help_category.MYD to /var/lib/mysql/mysql/help_category.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/servers.MYI to /var/lib/mysql/mysql/servers.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/time_zone_name.MYI to /var/lib/mysql/mysql/time_zone_name.MYI
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/event.MYD to /var/lib/mysql/mysql/event.MYD
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/func.frm to /var/lib/mysql/mysql/func.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/host.frm to /var/lib/mysql/mysql/host.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/time_zone_transition.frm to /var/lib/mysql/mysql/time_zone_transition.frm
171119 06:29:05 [01]        ...done
171119 06:29:05 [01] Copying ./mysql/time_zone.frm to /var/lib/mysql/mysql/time_zone.frm
171119 06:29:05 [01]        ...done
171119 06:29:06 [01] Copying ./mysql/db.frm to /var/lib/mysql/mysql/db.frm
171119 06:29:06 [01]        ...done
171119 06:29:06 [01] Copying ./mysql/help_topic.MYD to /var/lib/mysql/mysql/help_topic.MYD
171119 06:29:06 [01]        ...done
171119 06:29:06 completed OK!
  • 修改权限
[root@node2 2017-11-19_06-07-40]# ll /var/lib/mysql/ #查看当前数据库数据目录权限
total 28688
-rw-r----- 1 root root 18874368 Nov 19 06:29 ibdata1
-rw-r----- 1 root root  5242880 Nov 19 06:29 ib_logfile0
-rw-r----- 1 root root  5242880 Nov 19 06:29 ib_logfile1
drwx------ 2 root root     4096 Nov 19 06:29 mydb
drwx------ 2 root root     4096 Nov 19 06:29 mysql
drwx------ 2 root root     4096 Nov 19 06:29 performance_schema
-rw-r----- 1 root root      450 Nov 19 06:29 xtrabackup_info
[root@node2 2017-11-19_06-07-40]# chown -R mysql:mysql /var/lib/mysql/ #修改mysql为属主属组
[root@node2 2017-11-19_06-07-40]# ll /var/lib/mysql/
total 28688
-rw-r----- 1 mysql mysql 18874368 Nov 19 06:29 ibdata1
-rw-r----- 1 mysql mysql  5242880 Nov 19 06:29 ib_logfile0
-rw-r----- 1 mysql mysql  5242880 Nov 19 06:29 ib_logfile1
drwx------ 2 mysql mysql     4096 Nov 19 06:29 mydb
drwx------ 2 mysql mysql     4096 Nov 19 06:29 mysql
drwx------ 2 mysql mysql     4096 Nov 19 06:29 performance_schema
-rw-r----- 1 mysql mysql      450 Nov 19 06:29 xtrabackup_info
[root@node2 2017-11-19_06-07-40]# systemctl start mariadb
[root@node2 2017-11-19_06-07-40]# systemctl restart mariadb
[root@node2 2017-11-19_06-07-40]# mysql -uroot -pguomai -hlocalhost
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select * from mydb.test;
+----+----------+---------+
| id | name     | comment |
+----+----------+---------+
|  1 | xiaomhua | fujian  |
|  2 | xiaoming | beijing |
|  3 | xiaohong | hainan  |
+----+----------+---------+
3 rows in set (0.00 sec)

 继续浏览关于 MySql 的文章

 本文最后更新于 2018/05/09 14:00:19,可能因经年累月而与现状有所差异

 引用转载请注明:数字漩涡 > 数据库 > MySQL全量备份

发现共鸣

如是说:你的名字那么普通也就我听到会心头一怔。