Home IT Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ mysqldump implement database logical backup     - Linux, Apache Web site security settings (Linux)

- Ubuntu Tutorial: E: Failed to get lock / var / lib / apt / lists / lock - open (Linux)

- The array of C language (Programming)

- SQL in the specific internal Oracle process (Database)

- The Java way to stop a thread of execution (Programming)

- Oracle 11g to create a second instance on Linux (Database)

- Flask installation environment (Linux)

- Linux kernel VLAN study notes (Programming)

- The Sublime Text 3 configuration file (Linux)

- Configuring ftp server and nfs server under Linux (Server)

- CentOS6 install Redis (Database)

- Gentoo: existing preserved libs problem solving (Linux)

- How to compare PDF files on Ubuntu (Linux)

- Use libcurl library download url image (Programming)

- CentOS7 boot systemd introduction and use of management (Linux)

- Linux and Windows Security Topics (Linux)

- Ubuntu 14.04 installation configuration GNOME Classic interface (Linux)

- The free command in Linux (Linux)

- CentOS 7 Change Hostname (Linux)

- Java, on the dfile.encoding Systemproperty (Programming)

  mysqldump implement database logical backup
  Add Date : 2018-11-21      
  Mysqldump is mysql tools used for logical backup. In the process of database backup, you need to include a backup, the database itself, data, binary files (used for database even point reduction).

Following is a brief introduction to the next.

Backup starts:

1: landing mysql process, will need to enter a password, enter the password each time if you want to avoid tedious, you can create .my.cnf hidden files in the login user's home directory to root user as an example

vi /root/.my.cnf


user = 'root'

password = 'exit'

host = 'localhost'

Again until landing time, landing mysql can directly enter the system.

2: Back up the database

mysql database connection

show master status; // that is used to view the current binary log file.

mysqldump -all-databases -lock-all-tables -flush-logs-master-data = 2> / root`date +% F-% H-% M-% S`.sql

Now the database backup, backup binary log file below, after the backup or data binary log files have been refreshed, says a new binary log file is generated, the file name is the serial number on a binary file plus one.

Binary log files will be backed up before the first flush

mysqlbinlog /data/mydata/mysql-bin.000005> /root/1.sql

mysql database connections increment

use stu;

insert into score (name, score) values ( 'xiaozhang', 96);

select * from score;


One day after the last incremental backup

mysqlbinlog /data/mydata/mysql-bin.000006>/root/2.sql

3: Analog database damage.

cd / data / mydata

rm -rf *

killall mysqld // forced to close off all the processes mysql.

Re-initialize the database

cd / usr / local / mysql

scripts / mysql_install-db --user = mysql -datadir = / data / mydata

Before it restores the database to connect to mysql, the binary log file function off.


set sql_log_bin = 0 // Binary logging does not need to open the database restore process.


mysql < / root / `date +% F-% H-% M-% S`.sql // just backed up the database.

mysql < /root/1.sql // import binary log files

mysql < /root/2.sql // import binary incremental backup files

The reduction is complete, the connection open mysql binary log function.


set sql_log_bin = 1

purge binary logs to 'mysql-bin.000006'; // delete No binary log file.

Well, mysqldump logic also previously said that. After continued.
- How to upgrade to Ubuntu 14.04 Linux Kernel 4.4.1 LTS (Linux)
- Connect to the Oracle Database Help class (Database)
- Git 2.5 increase in the working tree, triangle improved workflow performance (Linux)
- How do I upgrade to Ubuntu 15.04 (Beta) (Linux)
- Oracle Shared Server Configuration (Database)
- Mount and unloading disks under Linux (Linux)
- Linux into single user mode to modify the administrator password (Linux)
- Ubuntu 14.04 forget solution root password (Linux)
- Linux terminal program running in the background (Linux)
- Use CutyCapt to convert HTML pages to png images on Linux (Linux)
- MySQL concat function is SQL injection (Linux)
- Json Applications of FastJson (Programming)
- To build PHP environment (Nginx + MariaDB + PHP7) under CentOS 6.5 (Server)
- Binary tree and some basic operations with binary list (Programming)
- Jigsaw project will solve the problem of Java JAR hell Mody (Programming)
- Oracle Duplicate build DataGuard (Database)
- Linux Detailed instructions alias settings (Linux)
- Ubuntu Live CD by updating Grub resume boot the Boot Menu (Linux)
- Linux support exFAT and NTFS (Linux)
- Boost notes --Thread - problems encountered in the initial use on Ubuntu (Programming)
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.