怎么恢复MySQL数据库

云数据库 RDS是阿里云提供的即开即用的关系型数据库服务,兼容了MySQL、SQL Server、PostgreSQL、PPAS(高度兼容Oracle)四种数据库引擎。在传统数据库的基础上,阿里云RDS提供了强大丰富的功能从而保证了高可用性、高安全性以及高性能。此外,RDS还提供了诸多便利功能提升了RDS的易用性。
如今数据对每个用户来说都是非常宝贵的财产,在使用阿里云RDS产品过程中,用户对数据库误操作引起的损失可以通过数据恢复功能最大程度地减少。接下来,我们会针对怎么恢复阿里云RDS数据库,进行做如下操作(以下操作以MySQL为列,不同引擎的操作方式请参考阿里云官方文档):
一、首先下载备份文件
629792c516da63c783d9f4492664cce71be66a2f
29e637e3e23e21dcd3705052262379250a792e43
二、其次,解压备份文件到指定目录
 1.下载 Xtrabackup:
 http://www.percona.com/downloads/XtraBackup/XtraBackup-2.0.6/RPM/rhel6/x86_64/percona-xtrabackup-2.0.6-521.rhel6.x86_64.rpm
 2.安装依赖包:
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL mysql-devel
 3.安装 Xtrabackup:
rpm -ivh percona-xtrabackup-2.0.6-521.rhel6.x86_64.rpm
 4.将hins190855_xtra_20150312120203.tar.gz解压到指定目录(不会自动新建子目录,要先设计好)
 mkdir /home/mysql/mysqldb cd /home/mysql/mysqldb
 tar -izxvf hins190855_xtra_20150312120203.tar.gz
三、接着,恢复数据到一致的状态
 1.innobackupex --defaults-file=./backup-my.cnf --apply-log ./
 2.删除此目录下的mysql,重新生成mysql系统库从而拥有数据库的root权限
   rm -rf mysql    (这里删除mysql目录是为了以后连接数据库权限)
 3.更新目录权限
   chown -R mysql:mysql /home/mysql/mysqldb/  (备份文件所属目录)  并检查文件所属为mysql用户
注意:我们需要配置my.cnf ,如果说你之前linux服务器就已经有一个实例,那么现在你还要创建一个实例(也就是一台多个mysql服务)
/usr/local/lnmp/mysql/bin/mysql_install_db --user=mysql --datadir=/mysql2/
 vi /etc/my.cnf
[client] 
default-character-set = utf8
[mysqld_multi]
mysqld = /usr/local/lnmp/mysql/bin/mysqld_safe
mysqladmin = /usr/local/lnmp/mysql/bin/bin/mysqladmin
user = root
[mysqld1]
port = 3306
socket = /tmp/mysql.sock
datadir = /date/lnmp/mysql
pid-file = /date/lnmp/mysql/mysql1.pid
log-error = /date/lnmp/log1/mysql_error.log
skip-locking
max_connections = 600
event_scheduler=ON
server-id = 1
[mysqld2]
port = 3307
socket = /tmp/mysql2.sock
datadir = /mysql2
pid-file = /mysql2/mysql.pid
log-error = /date/lnmp/log1/mysql_error2.log
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1048576000      
(注:最后三行很重要,可在/mysql2/mysql-my.cnf中复制过来即可,不然innodb表打不开)
启动实例
/usr/local/lnmp/mysql/bin/mysqld_multi --defaults-file=/etc/my.cnf start 1
/usr/local/lnmp/mysql/bin/mysqld_multi --defaults-file=/etc/my.cnf start 2 
357786a71b4c20365b033dde92e83a42e89cc447
停止实例方法
kill -9 `ps -ef | grep 3307 | grep -v grep | awk '{print $2}' | xargs`
修改mysql数据库用户名
mysql -uroot -proot -S /tmp/mysql2.sock
grant all on *.* to root@'localhost' identified by 'password'
4.以上第3步是针对一台多个实例,如果就一个实例的话就不需要那么麻烦,直接进行第3步,如下:
vi /etc/my.cnf
/wdzj/lnmp/mysql/my.cnf
[client]
default-character-set = utf8
#password       = your_password
port            = 3306
socket          = /wdzj/lnmp/mysql/mysql.sock
 # Here follows entries for some specific programs
 # The MySQL server
[mysqld]
port            = 3306
socket          = /wdzj/lnmp/mysql/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
datadir         = /home/mysql/mysqldb
max_connections=1000
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1048576000
四、最后,重新生成ROOT用户密码及权限
1. 重置ROOT密码:
service mysql start
cd /wdzj/lnmp/mysql
./bin/mysqladmin -u root password 'linux'
2. 为ROOT用户赋权
grant all on *.* to root@'localhost' identified by 'linux'  WITH GRANT OPTION;
flush privileges;
 grant all on *.* to root@'%' identified by 'linux'  WITH GRANT OPTION;
flush privileges;
(建立新用户及赋权的语句要记得
grant all privileges on test.test to test@localhost identified by 'test' ;) 

参考
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页