MySQL Data Backup

MySQL is an open-source relational database management system (RDBMS) that uses the most common database management language-Structured Query Language (SQL) for database management. Let's try to think about what's most important in a production environment. If the hardware of our server is broken and can be repaired or replaced, the software problem can be repaired or reinstalled, but what if the data is gone? This may be the scariest thing, I feel in the production environment should be nothing more important than the data. So how do we ensure that data is not lost or lost and can be quickly recovered? As long as you read this, you should be able to mysql in the implementation of data backup and recovery can have a certain understanding.

Create a new database and make a full backup

ls /usr/local/mysql/data/school/  //会生成三个数据文件分别为db.opt    //属性文件info.frm  //表的结构文件info.ibd  //表的数据文件tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/   //全部进行备份mysql-2018-07-04.tar.xz  //就会在备份的路径中生成一个xz格式的压缩包当恢复的时候 则删除原有data 所有文件tar Jxvf /opt/mysql-2018-07-04.tar.xz -C  当备份回去注意文件的属主和属组

Backup

mysqldump -u root -p school > /opt/school.sql /备数据库mysqldump -u root -p --databases school mysql > /opt/school-mysql.sql  //同时备份多个数据库mysqldump -u root -p --opt --all-databases > /opt/all-mysql.sql  //备份所有数据库mysqldump -u root -p school info > /opt/school-info.sql  //备份school 里面的info表mysqldump -u root -p -d school info > /opt/desc-info.sql  //备份表的结构 不会数据插入

Restore database table columns in MySQL from the Linux character interface

注：在Linux字符界面中恢复的前提是得有一个要恢复的数据库同名的数据才恢复数据库mysql -u root -p school < /opt/school-info.sql //在恢复表的时候一应是要先有数据库才可以恢复

Restore "source" command in MySQL mode

mysql> drop table info;  ///删除表mysql> source /opt/school-info.sql  //导入回来就OK了mysql> show tables;  //查看表

Incremental backup to open binary log files

log-bin=mysql-bin   //在MySQL主配置中服务端里面添加开启二进制日志文件mysqladmin -u root -p flush-logs2  //刷新并且生成二进制日志insert into info (name,score) values (‘lisi‘,88);   //添加了李四mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p  //使用二进制日志进行恢复查看二进制文件mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002--base64-output=decode-rows -v //使用64位编码进行解码 按行进行读取 -v 显示出来

MySQL Data Backup