1. 利用mysqldump进行逻辑备份
1)全逻辑备份:
mysqldump -uxxx -p --flush-logs --delete-master-logs --all-databases > alldb.sql (每天晚上进行一次全备)
2)增量备份:
mysqladmin flush-logs (每小时刷一下,保存起来,进行了一次增量备份)
3)缺点:
1> --all-databases 包含了 mysql 数据库,其中包含了权限的数据,所以我们应该加上 --flush-privileges,在恢复时,权限才能生效;
注意 --all-databases 包括了mysql数据库,但是不会包含 information_schema和performance_schema两个数据库。
2> 因为 mysqldump 默认启用了 --lock-tables,所以会导致在备份期间对所有表持有读锁: lock table tb read local,所以所有的update,delete语句
会被阻塞。但是select语句和insert语句不会被阻塞。
3> --delete-master-logs 备份之后,会执行 purge logs to 语句。删除了备份之后的master上的binary log. 一般而言,我们不建议随便删除binary log.
我们应该将它们保存起来,而不是直接删除。以防万一,要留条退路。
4> 该备份方式,虽然在整个备份过程中持有了 lock table tb read local,但是还是可以执行 insert 语句的。所以得到的不是一致性的备份。虽然得到的不是
一致性的备份,但是因为flush log之后,所有的操作 也会记入新的binary log,所以如果使用了所有新的binary log来进行完全恢复的话,最后恢复的数据
也是一致性的。当然不一致性的备份无法用于搭建slave。
如果要得到一致性的备份的话,需要使用 --lock-all-tables 或者使用 --single-transaction 选项。前者使用了全局读锁,不允许任何修改操作。后者使用
了事务的特性来得到一致性备份。
所以我们应该对上面的备份方式进行改良。
2. 使用mysqldump备份的最佳姿势
1)优化锁 和 得到一致性备份:
我们可以使用结合使用 --single-transaction 、--master-data=2 、--flush-logs 来达到将锁定时间大大减少的目的。同时有得到了一致性的备份,而且该一致性备份和 flush 的日志也是一致的;
2)去掉 --delete-master-logs 选项,改为在备份之后,将所有被刷新的 binary log 移到一个地方保存起来;
3)因为使用了 --single-transaction 选项,针对的只能是 innodb 数据,但是mysql数据是Myisam引擎的,所以我们最好将mysql数据库的备份分开来,
另外专门针对 mysql 数据库进行一次操作。当然不分开来备份,可能也没有问题。
4)还要加上 --routines 来备份存储过程和函数,触发器默认会备份。
优化之后,我们得到:
mysqldump -uxxx -p --single-transaction --master-data=2 --routines --flush-logs --databases db1 db2 db3 > alldb.sql;
mysqldump -uxxx -p --flush-privileges --databases mysql > mysql.sql;
如果将mysql也一起备份的话:
mysqldump -uxxx -p --single-transaction --master-data=2 --routines --flush-logs --flush-privileges --all-databases > alldb.sql;
3. 使用mysqldump来搭建slave环境
搭建slave环境,一般有两种方法,对于规模不大的库,可以采用mysqldump来搭建;对于规模很大的库,最好采用xtrabackup来搭建,速度要快很多。
1)首先 分别在master和slave上设置不同的server_id=1/101,启用master上的log-bin=1,启用slave上的relog-log=relay-bin; 在master上设置:
binlog_format=row;二进制日志的格式。maser上最好还设置sync_binlog=1 和 innodb_flush_log_at_trx_commit=1防止发生服务器崩溃时
导致复制破坏。在slave上最好还配置:read-only=1 和 skip-slave-start=1 前者可以防止没有super权限的用户在slave上进行写,后者防止在启动
slave数据库时,自动启动复制线程。以后需要手动start slave来启动复制线程。注意slave没有必要启用 log-bin=1,除非需要搭建二级slave。
2)在master上建立一个具有复制权限的用户:
grant replication slave, replication client on *.* to repl@’192.168.%.%’ identified by ‘123456’;
3)备份master上的数据库,迁移到slave上:
[root@localhost ~]# mysqldump -uroot -p --routines --flush-logs --master-data=2 --databases db2 db1>/root/backup.sql Enter password: [root@localhost ~]# scp /root/backup.sql 192.168.137.9:/tmp/backup.sql The authenticity of host '192.168.137.9 (192.168.137.9)' can't be established. RSA key fingerprint is a4:cd:c0:13:d1:8c:c0:a5:e7:c4:43:b5:95:17:af:d3. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.137.9' (RSA) to the list of known hosts. [email protected]'s password: backup.sql
因为slave的搭建需要一致性的备份,所以需要启用 --lock-all-tables(master-data=1/2会自动启用--lock-all-tables)或者--single-transaction;
另外还需要知道该一致性备份的数据,对应的master上的binary log的文件名,以及在该文件中的position,所以必须启用 master-data选项。
因为--master-data会启用--lock-all-tables 所以数据才是一致性的;但是导致了全局锁,不能进行任何修改操作;下面我们使用--single-transaction进行优化:
mysqldump -uroot -p --routines --flush-logs --single-transaction --master-data=2 --databases db1 db2 > /root/backup.sql; (--flush-logs非必须)
这样全局锁仅仅在备份的开始短暂的持有。不会再备份的整个过程中持有全局锁。
4)在slave上执行备份的脚本,然后连上master,开启复制线程:
执行sql脚本:
mysql> source /tmp/backup.sql
找到 --master-data 输出的 binary log 的文件名和postion:
[root@localhost ~]# head -50 /tmp/backup.sql ...... /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=809;
执行 change master to, start slave:
在salve上执行命令开始复制:
mysql> change master to master_host='192.168.137.8', master_user='repl', master_password='123456',
-> master_log_file='mysql-bin.000010', master_log_pos=809;
Query OK, 0 rows affected, 2 warnings (0.09 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
最后在slave上查看复制线程的状态:
mysql> show slave status\G
... ...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
slave_IO_Runing 和 slave_sql_runing 状态都是yes表示搭建成功。
5)replication涉及到的三个线程:
1> master上的 binlog dump(dump线程),即读取master上的binlog,发送到slave上的线程。
2> slave上的IO线程:读取slave上的relay log。
3> slave上的sql线程:执行IO线程读取的relay log的线程。
4. 使用mysqldump的备份进行 还原
下面使用 mysqldump 进行一个备份,然后删除 datadir, 然后使用备份sql脚本和binary log进行还原的过程。
1)首先进行一个全备:
mysqldump -uroot -p --single-transaction --master-data=2 --routines --flush-logs --databases gs ngx_lua > gs_ngx_lua_backup.sql;
数据库有两个库: gs , ngx_lua.
2)将 备份时刷新之后的 binary log 利用 mv 命令移动到安全的位置,也就是--master-data=2输出的日志文件,它之前的日志文件都存储到安全的位置:
[root@localhost ~]# head -n 50 gs_ngx_lua_backup.sql -- MySQL dump 10.13 Distrib 5.6.26, for linux-glibc2.5 (i686) -- -- Host: localhost Database: gs ... ...-- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000027', MASTER_LOG_POS=120;
也就是将 MASTER_LOG_FILE='mysql-bin.000027' 之前的日志都存储到其它位置。
然后执行:purge binary logs to 'mysql-bin.000027' 更新了 mysql-bin.index 中的索引信息,这里并没有删除binary log,因为它们已经被mv走了。
3)下面模拟一个 增量备份:
mysql> delete from user where id=5; Query OK, 1 row affected (0.02 sec) mysql> select * from user; +----+----------+------+-------------+----------+ | id | name| sex | phone | addr| +----+----------+------+-------------+----------+ | 1 | yuanfang | 1 | 14465463786 | hangzhou | | 2 | Andy| 0 | 14465463786 | beijing | | 3 | peter | 0 | 14465463786 | Hongkong | | 4 | lexin | 1 | 36565634 | shenzhen | +----+----------+------+-------------+----------+ 4 rows in set (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.11 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000027 | 370 | | mysql-bin.000028 | 120 | +------------------+-----------+ 2 rows in set (0.00 sec)
这里 flush logs 进行增量备份,然后将增量备份的 bianry log 文件 mysql-bin.000027 也存储起来。
然后在进行一条 delete 语句:
mysql> select * from user; +----+----------+------+-------------+----------+ | id | name| sex | phone | addr| +----+----------+------+-------------+----------+ | 1 | yuanfang | 1 | 14465463786 | hangzhou | | 2 | Andy| 0 | 14465463786 | beijing | | 3 | peter | 0 | 14465463786 | Hongkong | +----+----------+------+-------------+----------+ 3 rows in set (0.00 sec)
到这里数据库的最新状态是:user 表只有3条记录。
然后我们同样将 mysql-bin.000028 也存储起来。
4)然后我们删除 data-dir 目录中的所有文件,然后开始还原:
[root@localhost mysql]# pwd
/var/lib/mysql
[root@localhost mysql]# mv ./* /backup/before_delete/
此时 data-dir 目录是空的。然后我们试图使用 mysqladmin -uroot -p shutdown 来关闭mysqld,发现早不到 mysql.sock 无法关闭,我们只好使用
killall mysqld
来关闭。
5)然后进行数据库的初始化,然后开始还原:
进入到 /usr/local/mysql/script 目录,然后执行初始化:
./mysql_install_db --user=mysql --datadir=/var/lib/mysql --basedir=/usr/local/mysql
./mysql_install_db
初始化成功之后,执行:
/usr/local/mysql/bin/mysql_secure_installation
来进行密码设置。这一步可能会报错:找不到 /tmp/mysql.sock 文,解决方法,在/etc/my.cnf 中的[client], [mysql], [mysqld] 都进行下面的设置:
socket=/tmp/mysql.sock
然后重新执行:/usr/local/mysql/bin/mysql_secure_installation 就行了。
初始化完成之后,我们使用备份的sql脚本来进行恢复:
[root@localhost ~]# mysql -uroot -p
执行完成之后,发现 gs 和 ngx_lua 数据库都还原出来了,但是他们的数据不是最新的数据:
mysql> use gs; 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 user; +----+----------+------+----------------+----------+ | id | name| sex | phone| addr| +----+----------+------+----------------+----------+ | 1 | yuanfang | 1 | 14465463786 | hangzhou | | 2 | Andy| 0 | 14465463786 | beijing | | 3 | peter | 0 | 14465463786 | Hongkong | | 4 | lexin | 1 | 36565634 | shenzhen | | 5 | lexin2 | 1 | 43563436565634 | aomao | +----+----------+------+----------------+----------+ 5 rows in set (0.01 sec)
这是因为,我们还没有使用 mysql-bin.000027 和 mysql-bin.000028 两个binary log。mysql-bin.000027 是我们前面模拟的增量备份,而mysql-bin.000028 是 删除data-dir目录时,最新的binary log。依次应用了这两个binary log之后,数据库才能恢复到最新的状态。
6)应用 binary log:
[root@localhost backup]# pwd
/backup
[root@localhost backup]# mysqlbinlog mysql-bin.000027 > 27.sql
[root@localhost backup]# mysqlbinlog mysql-bin.000028 > 29.sql
mysqlbinlog mysql-bin.000027 > 27.sql 得到了 27.sql 和 28.sql 之后,使用 mysql 来执行:
mysql -uroot -p
然后查看数据:
mysql> mysql> select * from user; +----+----------+------+-------------+----------+ | id | name| sex | phone | addr| +----+----------+------+-------------+----------+ | 1 | yuanfang | 1 | 14465463786 | hangzhou | | 2 | Andy| 0 | 14465463786 | beijing | | 3 | peter | 0 | 14465463786 | Hongkong | | 4 | lexin | 1 | 36565634 | shenzhen | +----+----------+------+-------------+----------+ 4 rows in set (0.00 sec)
然应用 28.sql :
mysql -uroot -p
得到最新的状态:
mysql> select * from user; +----+----------+------+-------------+----------+ | id | name| sex | phone | addr| +----+----------+------+-------------+----------+ | 1 | yuanfang | 1 | 14465463786 | hangzhou | | 2 | Andy| 0 | 14465463786 | beijing | | 3 | peter | 0 | 14465463786 | Hongkong | +----+----------+------+-------------+----------+ 3 rows in set (0.00 sec)
可以看到,成功的还原到了删除 data-dir 目录之前的状态了。
5. mysqldump的 坑
如果对 mysqldump 的默认启用的选项不熟悉的话,可能会被默认启用的选项 --add-drop-table 给坑了。因为默认会生成 drop table if exist 语句。可能会导致数据的丢失。 --add-drop-database 默认没有启用。如果不想他生成 drop table 语句,可以加入 --skip-add-drop-table选项,或者 --add-ropt-table=0也可以。
6. 总结:
1)逻辑备份的最佳方法:
全备:
mysqldump -uxxx -p --single-transaction --master-data=2 --routines --flush-logs --databases db1 db2 db3 > alldb.sql;
mysqldump -uxxx -p --flush-privileges --databases mysql > mysql.sql;
如果将mysql也一起备份的话:
mysqldump -uxxx -p --single-transaction --master-data=2 --routines --flush-logs --flush-privileges --all-databases > alldb.sql;
有时,还需要加入:--default-character-set=utf8/utf8mb4 ,该选项一般也可以配置在/etc/my.cnf中。
增量备份:flush logs; 然后将binary log存储起来即可。
2)搭建slave时的最佳选项:
mysqldump -uxxx -p --single-transaction --master-data=2 --routines --databases db1 db2 db3 > alldb.sql;
搭建slave,没有必要 --flush-logs。当然搭建slave的最佳方式是使用 xtrabackup,物理备份。
3)使用mysqldump备份的sql脚本还原的方法:
先还原数据库,然后应用增量日志和最新日志,binary log在应用之前需要使用mysqlbinlog命令来处理。

MySQL是一种开源的关系型数据库管理系统,主要用于快速、可靠地存储和检索数据。其工作原理包括客户端请求、查询解析、执行查询和返回结果。使用示例包括创建表、插入和查询数据,以及高级功能如JOIN操作。常见错误涉及SQL语法、数据类型和权限问题,优化建议包括使用索引、优化查询和分表分区。

MySQL是一个开源的关系型数据库管理系统,适用于数据存储、管理、查询和安全。1.它支持多种操作系统,广泛应用于Web应用等领域。2.通过客户端-服务器架构和不同存储引擎,MySQL高效处理数据。3.基本用法包括创建数据库和表,插入、查询和更新数据。4.高级用法涉及复杂查询和存储过程。5.常见错误可通过EXPLAIN语句调试。6.性能优化包括合理使用索引和优化查询语句。

选择MySQL的原因是其性能、可靠性、易用性和社区支持。1.MySQL提供高效的数据存储和检索功能,支持多种数据类型和高级查询操作。2.采用客户端-服务器架构和多种存储引擎,支持事务和查询优化。3.易于使用,支持多种操作系统和编程语言。4.拥有强大的社区支持,提供丰富的资源和解决方案。

InnoDB的锁机制包括共享锁、排他锁、意向锁、记录锁、间隙锁和下一个键锁。1.共享锁允许事务读取数据而不阻止其他事务读取。2.排他锁阻止其他事务读取和修改数据。3.意向锁优化锁效率。4.记录锁锁定索引记录。5.间隙锁锁定索引记录间隙。6.下一个键锁是记录锁和间隙锁的组合,确保数据一致性。

MySQL查询性能不佳的原因主要包括没有使用索引、查询优化器选择错误的执行计划、表设计不合理、数据量过大和锁竞争。 1.没有索引导致查询缓慢,添加索引后可显着提升性能。 2.使用EXPLAIN命令可以分析查询计划,找出优化器错误。 3.重构表结构和优化JOIN条件可改善表设计问题。 4.数据量大时,采用分区和分表策略。 5.高并发环境下,优化事务和锁策略可减少锁竞争。

在数据库优化中,应根据查询需求选择索引策略:1.当查询涉及多个列且条件顺序固定时,使用复合索引;2.当查询涉及多个列但条件顺序不固定时,使用多个单列索引。复合索引适用于优化多列查询,单列索引则适合单列查询。

要优化MySQL慢查询,需使用slowquerylog和performance_schema:1.启用slowquerylog并设置阈值,记录慢查询;2.利用performance_schema分析查询执行细节,找出性能瓶颈并优化。

MySQL和SQL是开发者必备技能。1.MySQL是开源的关系型数据库管理系统,SQL是用于管理和操作数据库的标准语言。2.MySQL通过高效的数据存储和检索功能支持多种存储引擎,SQL通过简单语句完成复杂数据操作。3.使用示例包括基本查询和高级查询,如按条件过滤和排序。4.常见错误包括语法错误和性能问题,可通过检查SQL语句和使用EXPLAIN命令优化。5.性能优化技巧包括使用索引、避免全表扫描、优化JOIN操作和提升代码可读性。


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

AI Hentai Generator
免费生成ai无尽的。

热门文章

热工具

Atom编辑器mac版下载
最流行的的开源编辑器

ZendStudio 13.5.1 Mac
功能强大的PHP集成开发环境

DVWA
Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序,非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具,帮助Web开发人员更好地理解保护Web应用程序的过程,并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞,难度各不相同。请注意,该软件中

WebStorm Mac版
好用的JavaScript开发工具

安全考试浏览器
Safe Exam Browser是一个安全的浏览器环境,用于安全地进行在线考试。该软件将任何计算机变成一个安全的工作站。它控制对任何实用工具的访问,并防止学生使用未经授权的资源。