溧阳 做网站,手机代理,专门做民宿的网站有哪些,wordpress所有文章新窗口打开MariaDB安装配置、使用、授权、增删改查以及数据库备份与恢复
MariaDB安装配置、使用、授权、增删改查以及数据库备份与恢复_mariadb安装及配置教程-CSDN博客mariadb 恢复#xff1a;
ERROR! MySQL server PID file could not be found!
170104 23:04:21 InnoDB: The InnoD…MariaDB安装配置、使用、授权、增删改查以及数据库备份与恢复
MariaDB安装配置、使用、授权、增删改查以及数据库备份与恢复_mariadb安装及配置教程-CSDN博客mariadb 恢复
ERROR! MySQL server PID file could not be found!
170104 23:04:21 InnoDB: The InnoDB memory heap is disabled
先停止 MySQL 服务
日志log-error/var/log/mysql/error.log
配置文件错误mariadb 配置文件位置(/etc.my.cnf 或/etc/mysql/my.cnf)
端口冲突3306
权限问题chown -R mysql: /opt/mariadb/data
那么就有个简单的方法开启数据库了我们可以在etc/目录下的MySQL的配置文档 my.cnf 里的[mysqld]下添加一条语句
[mysqld]
innodb_force_recovery 1
之后重启lnmp强制恢复 InnoDB引擎。待InnoDB恢复之后再将这句话删除然后重启MYSQL服务即可。
不过在执行上述步骤之前请先通过SFTP将 /usr/local/mysql/var 整个目录备份到本地移动或删除 InnoDB 的数据文件通常在 MySQL 的数据目录下的 ibdata1, ib_logfile0, ib_logfile1 等文件或者是各自数据库的 .ibd 文件。
进行数据备份通常是备份 mysql 数据库下的 innodb_table_stats 和 innodb_index_stats 表。
重建或恢复数据库
以免强制恢复的过程中出现不可逆转的错误
innodb_force_recovery 是 MySQL 的 InnoDB 存储引擎的配置选项用于在 InnoDB 数据库文件损坏时尝试启动数据库。设置该参数的值为 1 到 6 之间的一个数字代表不同级别的恢复模式。在恢复模式下数据库可能只能读取而无法进行数据修改。
当设置 innodb_force_recovery 后数据库能够启动如果确定数据不再需要并且已经有了数据库的备份那么可以安全地删除或者重命名 InnoDB 的数据文件.ibd 文件或整个数据目录。
警告
只有在紧急情况下将innodb_force_recovery设为大于0的值你才能启动InnoDB并转储表。在进行此操作之前确保你有数据库的备份副本以备需要重建它。4及以上的值可以永久破坏数据文件。只有在数据库的独立物理副本的成功地测试了设置才能在生产服务器实例使用4及以上的innodb_force_recovery设置。当强制InnoDB恢复你应该总是以innodb_force_recovery1启动且仅在需要时增加值。
innodb_force_recovery默认为0(没有强制恢复的正常启动)。对于innodb_force_recovery允许的非零值是1至6。较大值包括较小值的功能。例如为3的值包括所有的值1和2的功能。
如果你能以innodb_force_recovery为3或更低值转储你的表那么你是比较安全的只有在损坏的个人页的一些数据会丢失。4或更大的值被认为是危险的因为数据文件可以被永久地损坏。值6被认为是严重的数据库页被留在一个陈旧的状态这反过来又可能带给B-trees和其它数据库结构更多的损坏。
最后用数据备份工具备份一次数据确保安全就可以了。
innodb_force_recovery 可以设置为 1-6大的值包含前面所有小于它的值的影响。 1 (SRV_FORCE_IGNORE_CORRUPT): 忽略检查到的 corrupt 页。尽管检测到了损坏的 page 仍强制服务运行。一般设置为该值即可然后 dump 出库表进行重建。 2 (SRV_FORCE_NO_BACKGROUND): 阻止主线程的运行如主线程需要执行 full purge 操作会导致 crash。 阻止 master thread 和任何 purge thread 运行。若 crash 发生在 purge 环节则使用该值。 3 (SRV_FORCE_NO_TRX_UNDO): 不执行事务回滚操作。 4 (SRV_FORCE_NO_IBUF_MERGE): 不执行插入缓冲的合并操作。如果可能导致崩溃则不要做这些操作。不要进行统计操作。该值可能永久损坏数据文件。若使用了该值则将来要删除和重建辅助索引。 5 (SRV_FORCE_NO_UNDO_LOG_SCAN): 不查看重做日志InnoDB 存储引擎会将未提交的事务视为已提交。此时 InnoDB 甚至把未完成的事务按照提交处理。该值可能永久性的损坏数据文件。 6 (SRV_FORCE_NO_LOG_REDO): 不执行前滚的操作。恢复时不做 redo log roll-forward。使数据库页处于废止状态继而可能引起 B 树或者其他数据库结构更多的损坏。 注意
为了安全当设置参数值大于 0 后可以对表进行 select, create, drop 操作,但 insert, update 或者 delete 这类操作是不允许的。MySQL 5.6.15 以后当 innodb_force_recovery 的值大于等于 4 的时候InnoDB 表处于只读模式。在值小于等于 3 时可以通过 select 来 dump 表可以 drop 或者 create 表。MySQL 5.6.27 后大于 3 的值也支持 DROP TABLE 如果事先知道哪个表导致了崩溃则可 drop 掉这个表。如果碰到了由失败的大规模导入或大量 ALTER TABLE 操作引起的 runaway rollback则可 kill 掉 mysqld 线程然后设置 innodb_force_recovery 3 使数据库重启后不进行 rollback。然后删除导致 runaway rollback 的表 如果表内的数据损坏导致不能 dump 整个表内容。那么附带 order by primary_key desc 从句的查询或许能够 dump 出损坏部分之后的部分数据 sql.gz还原mariadb_mysql 数据备份
命令行备份数据库
1、mysqldump命令进行备份。该命令将连接MySQL服务器并创建SQL转储文件该文件包含了重新创建数据库所必需的所有SQL语句。该命令的基本语法如下
$ mysqldump --opt -u [uname] -p[pass] [dbname] [backupfile.sql]
[uname] 数据库用户名
[pass] 数据库密码
[dbname] 数据库名
[backupfile.sql] 数据库备份文件名
[--opt] mysqldump选项
例如备份数据库Trunk2015到20151112.sql文件你可以简单地使用以下命令
$ mysqldump -u root -p Trunk2015 20151112.sql
如果有必要一次导出所有的数据库可使用--all-databases选项命令如下
$ mysqldump -u root -p --all-databases 20151112.sql
如果mysql数据库较大你可能希望能压缩mysqldump导出的SQL文件此时仅需使用如下的备份命令并通过管道连接到gzip此时便可导出gzip文件。
$ mysqldump -u root -p Trunk2015 | gzip -9 20151112.sql.gz
解压缩方法如下
$ gunzip 20151112.sql.gz
从命令行恢复MySQL数据库
通过上面的方法我们已经将数据库Trunk015备份至20151112.sql文件接下来我们恢复Trunk2015数据库
* 在目标机器上创建相应的数据库名
* 使用mysql命令载入sql文件
$ mysql -u [uname] -p[pass] [db_to_restore] [backupfile.sql]
例如我们现在恢复20151112.sql文件到Trunk2015数据库
$ mysql -u root -p Trunk2015 20151112.sql
如果是恢复压缩的备份文件可遵循以下格式
$ gunzip [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]
这里出现了错误
可以使用$ gunzip [backupfile.sql.gz] | mysql -u [uname] -p [dbname]换行输入密码
如果是恢复已经存在的数据库此时可使用mysqlimport命令该命令的语法格式如下
$ mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql] 一、备份常用操作基本命令
1、备份命令mysqldump格式
格式mysqldump -h主机名 -P端口 -u用户名 -p密码 –database 数据库名 文件名.sql
2、备份MySQL数据库为带删除表的格式
备份MySQL数据库为带删除表的格式能够让该备份覆盖已有数据库而不需要手动删除原有数据库。
mysqldump --add-drop-table -uusername -ppassword -database databasename backupfile.sql
3、直接将MySQL数据库压缩备份
mysqldump -hhostname -uusername -ppassword -database databasename | gzip backupfile.sql.gz
4、备份MySQL数据库某个(些)表
mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 backupfile.sql
5、同时备份多个MySQL数据库
mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 multibackupfile.sql仅仅备6、仅备份份数据库结构
mysqldump –no-data –databases databasename1 databasename2 databasename3 structurebackupfile.sql
7、备份服务器上所有数据库
mysqldump –all-databases allbackupfile.sql
8、还原MySQL数据库的命令
mysql -hhostname -uusername -ppassword databasename
9、还原压缩的MySQL数据库
gunzip
10、将数据库转移到新服务器
mysqldump -uusername -ppassword databasename | mysql –host*.*.*.* -C databasename
11、--master-data 和--single-transaction
在mysqldump中使用--master-data2会记录binlog文件和position的信息 。--single-transaction会将隔离级别设置成repeatable-commited
12、导入数据库
常用source命令用use进入到某个数据库mysqlsource d:\test.sql后面的参数为脚本文件。
13、查看binlog日志
查看binlog日志可用用命令 mysqlbinlog binlog日志名称|more
14、general_log
General_log记录数据库的任何操作查看general_log 的状态和位置可以用命令show variables like general_log% ,开启general_log可以用命令set global general_logon
二、增量备份
小量的数据库可以每天进行完整备份因为这也用不了多少时间但当数据库很大时就不太可能每天进行一次完整备份了这时候就可以使用增量备份。增量备份的原理就是使用了mysql的binlog志。
1、首先做一次完整备份
mysqldump -h10.6.208.183 -utest2 -p123 -P3310 --single-transaction --master-data2 testtest.sql这时候就会得到一个全备文件test.sql
在sql文件中我们会看到
-- CHANGE MASTER TO MASTER_LOG_FILEbin-log.000002, MASTER_LOG_POS107;是指备份后所有的更改将会保存到bin-log.000002二进制文件中。
2、在test库的t_student表中增加两条记录然后执行flush logs命令。这时将会产生一个新的二进制日志文件bin-log.000003bin-log.000002则保存了全备过后的所有更改既增加记录的操作也保存在了bin-log.00002中。
3、再在test库中的a表中增加两条记录然后误删除t_student表和a表。a中增加记录的操作和删除表a和t_student的操作都记录在bin-log.000003中。
三、恢复
1、首先导入全备数据
mysql -h10.6.208.183 -utest2 -p123 -P3310
2、恢复bin-log.000002
mysqlbinlog bin-log.000002 |mysql -h10.6.208.183 -utest2 -p123 -P3310
3、恢复部分 bin-log.000003
在general_log中找到误删除的时间点然后更加对应的时间点到bin-log.000003中找到相应的position点需要恢复到误删除的前面一个position点。
可以用如下参数来控制binlog的区间
--start-position 开始点 --stop-position 结束点
--start-date 开始时间 --stop-date 结束时间
找到恢复点后既可以开始恢复。
mysqlbinlog mysql-bin.000003 --stop-position208 |mysql -h10.6.208.183 -utest2 -p123 -P3310 centos 数据库恢复 一冷备通过cp拷贝恢复 拷贝全部目录的方法只适合两个同版本数据库如果两个版本不一致将会报错 1. 先把两边的数据库服务停掉 systemctl stop mariadb.service 2. 压缩数据库目录 完成备份 tar zcvf mysql.tar.gz /var/lib/mysql scp mysql.tar.gz 192.168.19.11:/root/ 3. 恢复 首先解压备份 tar xvf mysql.tar.gz 4. 然后复制到对应目录 #mv mysql/* /var/lib/mysql/ 5. 启动服务 systemctl start mariadb.service 6. 验证是否成功 MariaDB [(none)] select user,host,password from mysql.user where userxiapi; ------------------------------------------------------------- | user | host | password | ------------------------------------------------------------- | xiapi | localhost | *A02AA727CF2E8C5E6F07A382910C4028D65A053A | ------------------------------------------------------------- 1 row in set (0.00 sec) MariaDB [(none)] 二通过mysqldump加二进制日志恢复 这种方法可以拷贝在不同版本上。 1. 首先二级制日志独立存放 [mysqld] log-bin/data/mysql/mysql-bin 2. 完全备份并记录备份的二进制位置 mysqldump -A --master-data2 | gzip /backup/all_date %F.sql.gz -A代表备份全部数据库 -B代表备份指定数据库 3. 模拟完全备份后有数据做了更改将来用二进制日志恢复 #修改数据库 insert students (name,age,gender)value(mage,20,M); insert students (name,age,gender)value(wang,22,M); 4. 模拟数据库盘损坏 rm -rf /var/lib/mysql/* 5. 解压数据库 cd /backup gzip -d all_2019-11-25.sql.gz 6. 启动数据库 mysql_install_db --usermysql 重新生成数据库文件 systemctl start mariadb 启动数据库 7. 还原数据库 MariaDB [(none)]set sql_log_bin0; MariaDB [(none)]source /data/all_2019-11-25.sql 8. 二进制日志的备份 [rootcentos8 ~]#grep ^-- CHANGE MASTER TO /data/all_2019-11-25.sql -- CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS328; 查看完全备份的时候二进制文件位置以后恢复的时候代表从这之后的二进制文件是为完全备份的。 9. 二进制日志的恢复 [rootcentos8 mysql]#mysqlbinlog mysql-bin.000001 --start-position328 /backup/inc.sql 如果有多个二进制文件可采取追加的方式 [rootcentos8 mysql]#mysqlbinlog mysql-bin.000002 /backup/inc.sql 设置session级变量暂停二进制文件的记录然后恢复二进制日志的数据 MariaDB [(none)]set sql_log_bin0; MariaDB [(none)]source /backup/inc.sql 10. 分库备份 [rootcentos8 ~]#mysql -uroot -e show databases|sed -rn /^(Database|information_schema|performance_schema)$/!s#(.*)#mysqldump -B \1 | gzip /data/\1.sql.gz#p |bash [rootcentos8 ~]#mysql -uroot -e show databases|grep -Ev ^(Database|information_schema|performance_schema)$ | sed -rn s# (.*)#mysqldump -B \1 | gzip /data/\1.sql.gz#p |bash [rootcentos8 ~]#mysql -uroot -e show databases|grep -Ev ^(Database|information_schema|performance_schema)$|while read db;do mysqldump -B $db | gzip /data/$db.sql.gz;done [rootcentos8 ~]#for db in mysql -uroot -e show databases|grep -Ev ^(Database|information_schema|performance_schema)$;do mysqldump -B $db | gzip /data/$db.sql.gz;done 11. 误删除某表之后进行恢复 a. 暂停数据库 b. 首先恢复删除表前最近的一次完全备份 c. 然后备份从完全备份到现在的二进制日志 d. 二进制日志找到删表操作进行注释然后恢复 三mysqldump 创建通用选项 https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html mysqldump, mydumper, phpMyAdmin Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件 mysqldump:是MySQL的客户端命令通过mysql协议连接至mysql服务器进行备份 -A, --all-databases #备份所有数据库含create database -B, --databases db_name… #指定备份的数据库包括create database语句 -E, --events #备份相关的所有event scheduler -R, --routines #备份所有存储过程和自定义函数 --triggers #备份表相关触发器默认启用,用--skip-triggers不备份触发器 --default-character-setutf8 #指定字符集 --master-data[#] #此选项须启用二进制日志 #1所备份的数据之前加一条记录为CHANGE MASTER TO语句非注释不指定#默认为1 #2记录为注释的CHANGE MASTER TO语句 #此选项会自动关闭--lock-tables功能自动打开-x | --lock-all-tables功能除非开启-- single-transaction -F, --flush-logs #备份前滚动日志锁定表完成后执行flush logs命令,生成新的二进制日志文 件配合-A 或 -B 选项时会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新可通过和-- single-transaction或-x--master-data 一起使用实现此时只刷新一次二进制日志 --compact #去掉注释适合调试生产不使用 -d, --no-data #只备份表结构 -t, --no-create-info #只备份数据,不备份create table -n,--no-create-db #不备份create database可被-A或-B覆盖 --flush-privileges #备份mysql或相关时需要使用 -f, --force #忽略SQL错误继续执行 --hex-blob #使用十六进制符号转储二进制列当有包括BINARY VARBINARYBLOBBIT的数 据类型的列时使用避免乱码 -q, --quick #不缓存查询直接输出加快备份速度 四xtrabackup备份工具 xtrabackup的新版变化 xtrabackup版本升级到2.4后相比之前的2.1有了比较大的变化innobackupex 功能全部集成到 xtrabackup 里面只有一个 binary程序另外为了兼容考虑innobackupex作为 xtrabackup 的软链 接即xtrabackup现在支持非Innodb表备份并且 Innobackupex 在下一版本中移除建议通过 xtrabackup替换innobackupex 1. 备份选项 --user#该选项表示备份账号 --password#该选项表示备份的密码 --host#该选项表示备份数据库的地址 --databases#该选项接受的参数为数据库名如果要指定多个数据库彼此间需要以空格隔开 如xtra_test dba_test同时在指定某数据库时也可以只指定其中的某张表。 如mydatabase.mytable。该选项对innodb引擎表无效还是会备份所有innodb表 --defaults-file#该选项指定从哪个文件读取MySQL配置必须放在命令行第一个选项位置 --incremental#该选项表示创建一个增量备份需要指定--incremental-basedir --incremental-basedir#该选项指定为前一次全备份或增量备份的目录与--incremental同时使用 --incremental-dir#该选项表示还原时增量备份的目录 --includename#指定表名格式databasename.tablename 2. 预准备选项 --apply-log #一般情况下,在备份完成后数据尚且不能用于恢复操作因为备份的数据中可能会包含尚 未提交的事务或已经提交但尚未同步至数据文件中的事务。因此此时数据文件仍处理不一致状态。此选项作 用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态 - -use-memory #和--apply-log选项一起使用当prepare 备份时做crash recovery分配的内存 大小单位字节也可1MB,1M,1G,1GB等推荐1G --export#表示开启可导出单独的表之后再导入其他Mysql中 --redo-only#此选项在prepare base full backup往其中合并增量备份时候使用但不包括对最 后一个增量备份的合并 3. 还原选项 --copy-back#做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir --move-back#这个选项与--copy-back相似唯一的区别是它不拷贝文件而是移动文件到目的地。这 个选项移除backup文件用时候必须小心。使用场景没有足够的磁盘空间同事保留数据文件和Backup副本 --force-non-empty-directories #指定该参数时候使得innobackupex --copy-back或--moveback选项转移文件到非空目录已存在的文件不会被覆盖。如果--copy-back和--move-back文件需要从 备份目录拷贝一个在datadir已经存在的文件会报错失败 4. 注意事项 1. datadir 目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定否则- -copy-back选项不会覆盖 2. 在restore之前,必须shutdown MySQL实例不能将一个运行中的实例restore到datadir目录中 3. 由于文件属性会被保留大部分情况下需要在启动实例之前将文件的属主改为mysql这些文件将 属于创建备份的用户, 执行chown -R mysql:mysql /data/mysql,以上需要在用户调用 innobackupex之前完成 五xtrabackup 完全备份 装的数据库是mysql 5.7版本yum安装的xtrabackup是2.3.6版本使用的时候提示版本不支持 https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/ xtrabackup8版本 https://www.percona.com/downloads/Percona-XtraBackup-LATEST/ xtrabackup低版本 自己下载了一个percona-xtrabackup-24-2.4.16-1.el7.x86_64支持可用 yum history info 8 yum history undo 8 找到安装的动作进行取消安装 yum install percona-xtrabackup-24-2.4.16-1.el7.x86_64.rpm 安装rpm包并解决依赖可能需要epel源 备份 1. #/backup目录不需事先创建但是建议自己创建。 xtrabackup --userroot --passwordxiapi --backup --target-dir/backup 还原 2. 预准备确保数据一致提交完成的事务回滚未完成的事务 xtrabackup --prepare --target-dir/backup/ 3. 复制到数据库目录 注意数据库目录必须为空MySQL服务不能启动 不用指定copy的指定目录默认拷贝在配置文件datadir指定的目录下 xtrabackup --copy-back --target-dir/backup/ 4. 还原属性 chown -R mysql.mysql data/ 5. 启动服务 service mysqld start 六xtrabackup 完全备份增量备份 备份过程 1. 完全备份 mkdir /backup/ xtrabackup -uroot -pxiapi --backup --target-dir/backup/base 2. 第一次修改数据 模拟修改数据 3. 第一次增量备份 xtrabackup -uroot -pxiapi --backup --target-dir/backup/inc1 --incremental-basedir/backup/base 4. 拷贝到安全位置 还原过程 1. 预准备完成备份此选项--apply-log-only 阻止回滚未完成的事务 xtrabackup --prepare --apply-log-only --target-dir/backup/base 2. 合并第1次增量备份到完全备份 如果有多次增量备份则最后一次不要加 --apply-log-only xtrabackup --prepare --target-dir/backup/base --incremental-dir/backup/inc1 3. 复制到数据库目录注意数据库目录必须为空MySQL服务不能启动 service mysqld stop xtrabackup --copy-back --target-dir/backup/base 4. 还原属性 chown -R mysql.mysql /mysql/data/ 5. 启动服务 service mysqld start ————————————————
