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.