MySql 数据备份
相关命令的执行都在windows服务器下,且工作目录就是mysql server的bin目录下.
1、mysqldump 全备份
mysqldump -h127.0.0.1 -u用户名 -p"密码"
数据库名称 有表的话写表名称
--single-transaction
--master-data=2
--flush-logs
>C:\Users\Administrator\Desktop\mysqldump\dh_log.sql
-h后面跟的是需要全备份的远程mysql服务器ip地址
-u后面跟的是登录的用户名
-p后面跟的是登录密码注意要用双引号括起来
接着后面写需要备份的数据库名称,如果需要备份某个表,则在后面再写表名称即可
--single-transaction 备份时指定不锁定表
--master-data 备份时,会向备份文件写入此次操作备份到哪个binlog文件的哪个position,因为在备份期间数据库还在运行,这部分数据只能通过binlog来恢复.
--flush-logs 执行备份前切断当前binlog和数据库的联系,在备份期间写入的数据都会写入新的binlog文件中,方便后面通过binlog恢复数据.
>C:\Users\Administrator\Desktop\mysqldump\dh_log.sql 指定sql的生成路径.
2、恢复备份文件
mysql -uroot -p"root" 数据库名称<备份文件的路径
注意:如果是第一次同步,需要新建数据库
3、恢复完成之后
(1)、执行如下代码
show master status;
记录下当前的binlog和position
(2)、执行如下代码
Flush Logs;
记录下当前的binlog和position
以防故障的情况发生
(1)、优化
2.1、减少数据库操作
关闭二进制日志：去掉log-bin配置选项。
减小事务提交后日志刷新频率：innodb_flush_log_at_trx_commit=0每秒1次。
2.2、增加日志尺寸
较大的事务日志缓冲区,减少写入日志的I/O操作：innodb_log_buffer_size
较大的日志文件，减少检查点和写入I/O:innodb_log_file_size
2.3、增加写入线程
InnoDB引擎写操作的I/O线程数：innodb_write_io_threads(不要超过CPU核心数)
2.4、增加缓存池
innodb_buffer_pool_size
innodb_buffer_pool_chunk_size
innodb_buffer_pool_instances
2.5、关闭双写缓冲区(doublewrite)
启动MySQL示例时，使用--innodb-doublewrite=0选项参数。
3、通过gitbash(因为是windows环境)查看备份节点终止的binlogfile和position
grep -i "CHANGE MASTER" dh_log.sql
输出如下:
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000064', MASTER_LOG_POS=155;
4、常见错误
ERROR 2006 (HY000) at line XX: MySQL server has gone away
解决方案
修改my.ini以下配置
max_allowed_packet=32M
5、如果需要通过canal同步,这时候如果先通过mysqldump导出、在通过mysql导入的方式,必须先重放mysql导入的binlog,然后在执行如下操作
(1)、执行如下代码
show master status;
记录下当前的binlog和position
(2)、执行如下代码
Flush Logs;
记录下当前的binlog和position
以防故障的情况发生
开启slave,之后在通过上面记录的点位,还原开启slave产生的binlog.
