本文最后更新于 2020 年 3 月 9 日,文章已超过 6 个月!内容可能已失效,请自行测试 ~
Mysql配置
1.安装
[root@localhost ~]# tar xf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz -C /usr/src/
[root@localhost ~]# ln -s /usr/src/mysql-5.6.40-linux-glibc2.12-x86_64/ /usr/local/mysql
[root@localhost ~]# ln -s /usr/local/mysql/bin/* /usr/local/bin/
[root@localhost ~]# cp /usr/src/mysql-5.6.40-linux-glibc2.12-x86_64/support-files/mysql.server /etc/init.d/mysqld #复制启动文件
[root@localhost ~]# chmod +x /etc/init.d/mysqld
[root@localhost ~]# useradd -M -s /sbin/nologin mysql
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql/
[root@localhost ~]# cp /usr/src/mysql-5.6.40-linux-glibc2.12-x86_64/support-files/my-default.cnf /etc/my.cnf #拷贝配置文件
[root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql #初始化
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
.. SUCCESS!
[root@localhost ~]# mysqladmin -uroot password '123456' #更改root密码
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.40 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> create database temp;
Query OK, 1 row affected (0.00 sec)
mysql> use temp;
Database changedmysql> create table xt (name varchar(12) not null,pwd varchar(32) not null);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into xt values('zhangsan','1');
Query OK, 1 row affected (0.02 sec)
mysql> insert into xt values('lisi','2');
Query OK, 1 row affected (0.01 sec)
mysql> insert into xt values('chengwu','3');
Query OK, 1 row affected (0.00 sec)
mysql> select * from xt;
+----------+-----+
| name | pwd |
+----------+-----+
| zhangsan | 1 |
| lisi | 2 |
| chengwu | 3 |
+----------+-----+
3 rows in set (0.00 sec)
mysql> quit
Bye
Xtrabackup配置
1.安装
[root@localhost ~]# yum -y install perl-DBD-MySQL perl-Digest-MD5
[root@localhost ~]# wget http://ftp.tu-chemnitz.de/pub/linux/dag/redhat/el6/en/x86_64/rpmforge/RPMS/libev-4.15-1.el6.rf.x86_64.rpm
[root@localhost ~]# wget https://dl.fedoraproject.org/pub/epel/7/x86_64/Packages/p/percona-xtrabackup-2.3.6-1.el7.x86_64.rpm
[root@localhost ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm percona-xtrabackup-2.3.6-1.el7.x86_64.rpm
2.完整备份及恢复
[root@localhost ~]# innobackupex --user=root --password=123456 /backup/mysqlbackup --no-timestamp #完整备份
[root@localhost ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
[root@localhost ~]# rm -rf /usr/local/mysql/data/* #模拟数据丢失
[root@localhost ~]# innobackupex --user=root --password=123456 --copy-back /backup/mysqlbackup
180712 20:41:54 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: )
Error: datadir must be specified.
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
datadir = /usr/local/mysql/data #指定数据库路径
[root@localhost ~]# innobackupex --user=root --password=123456 --copy-back /backup/mysqlbackup #完整备份恢复
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
ERROR! The server quit without updating PID file (/usr/local/mysql/data/localhost.localdomain.pid).
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql/data/
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
[root@localhost ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.40 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> use temp;
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> select * from xt;
+----------+-----+
| name | pwd |
+----------+-----+
| zhangsan | 1 |
| lisi | 2 |
| chengwu | 3 |
+----------+-----+
3 rows in set (0.01 sec)
mysql> quit
Bye
3.增量备份
[root@localhost ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.40 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> use temp;
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> insert into xt values('cs1','4');
Query OK, 1 row affected (0.00 sec)
mysql> insert into xt values('cs2','5');
Query OK, 1 row affected (0.00 sec)
mysql> quit
Bye
[root@localhost ~]# innobackupex --user=root --password=123456 --incremental /backup/temp --incremental-basedir=/backup/mysqlbackup --no-timestamp #增量备份
准备恢复完整备份(校验)
[root@localhost ~]# innobackupex --user=root --password=123456 --apply-log --redo-only /backup/mysqlbackup/ #合并之前校验数据
准备恢复增量备份(校验)
[root@localhost ~]# innobackupex --user=root --password=123456 --apply-log --redo-only /backup/mysqlbackup/ --incremental-dir=/backup/temp #合并之前校验数据
[root@localhost ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
[root@localhost ~]# innobackupex --user=root --password=123456 --copy-back /backup/mysqlbackup/
180712 20:49:56 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: )
Original data directory /usr/local/mysql/data is not empty!
[root@localhost ~]# rm -rf /usr/local/mysql/data/*
[root@localhost ~]# innobackupex --user=root --password=123456 --copy-back /backup/mysqlbackup/
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql/data
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
SUCCESS!
[root@localhost ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.40 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> use temp;
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> select * from xt;
+----------+-----+
| name | pwd |
+----------+-----+
| zhangsan | 1 |
| lisi | 2 |
| chengwu | 3 |
| cs1 | 4 |
| cs2 | 5 |
+----------+-----+
5 rows in set (0.00 sec)
版权属于:宇宙最帅的男人
作品采用:《 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 》许可协议授权