爱站程序员基地mysqldump备份数据库
1、备份数据库
[root@Server2 ~]# mysqldump -p’Gy@123456’ \\
–all-databases
–single-transaction
–routines
–triggers
–master-data=1
–flush-logs>/data/date +%F-mysql-all.sql
2、恢复数据库数据
[root@Server2 data]# systemctl stop mysqld
[root@Server2 data]# rm -rf /var/lib/mysql/*
[root@Server2 data]# systemctl start mysqld 启动数据库，新密码在/var/log/mysqld.log
[root@Server2 data]# mysql -p’新密码’
mysql< alter user root@‘localhost’ identified by ‘Gy@123456’; 修改密码
mysql< set SQL_LOG_BIN=0;恢复数据日志不加入binlog日志
mysql< source /date/sql路径.sql
3、通过二进制文件恢复数据
mysqlbinlog Server4-bin.000003 Server4-bin.000004 | mysql -p’GaoYong@123’
mysqlbinlog local1-bin.000003 –start-position=154 | mysql -uroot -p’GaoYong@123’
mysqlbinlog local1-bin.000003 –stop-position=778 | mysql -uroot -p’GaoYong@123’
mysqlbinlog local1-bin.000003 –start-position=780 | mysql -uroot -p’GaoYong@123’
