Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ mysqldump implement database logical backup     - Ubuntu use three methods to install Ruby (Linux)

- Android Custom View password box example (Programming)

- How to create a someone project on github (Linux)

- Oracle 10g relations with the constraint of column properties NULLABLE (Database)

- Python Django direct implementation of sql statement (Programming)

- Spring WebSocket Comments (Programming)

- Mount NFS network file system (Linux)

- Arrow keys, backspace key garbled in Python-2.7.5 Interactive Mode under CentOS 5.8 (Linux)

- Ubuntu 14.10 installation GNOME 3.14 (Linux)

- Linux (CentOS) SSH login without password authentication (Linux)

- MySQL query optimization: profile function (Database)

- Disk Management LVM (Linux)

- Linux virtual machines to solve end MySQL database can not remote access (Database)

- Netfilter / Iptables Comments (Linux)

- Increase Linux system security --chattr (Linux)

- BCP importing and exporting large amounts of data Practice (Database)

- How SSHfs mount a remote file system on Linux (Linux)

- Linux file compression and file system packaged with instructions (Linux)

- Linux System Getting Started Learning: rename multiple files in Linux (Linux)

- Chkconfig command Detailed service is added and shut down the system in two ways to start service under Linux (Linux)

  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 modify the Ubuntu terminal user name changed to red color (Linux)
- Python2.7.7 source code analysis (Programming)
- Docker build their own private warehouses (Linux)
- Windows and Ubuntu dual system, repair of two ways UEFI boot (Linux)
- Turning off the interface eth0: error: Disconnect the device 'eth0' (Linux)
- C ++ inline functions (Programming)
- Git you do not know about some of the things (Linux)
- Some MySQL interview questions (Database)
- Install the latest development version of Wine on RedHat and Debian-based systems (Linux)
- DataGuard a hardware issue warnings found (Database)
- Swift defined type conversion and type aliases (typealias) (Programming)
- CentOS 5.5 kernel upgrade installation iftop (Linux)
- Talk about the Linux ABI compatibility Application (Linux)
- To install Internet security firewall Raiders (Linux)
- Java input and output common class Scanner (Programming)
- MySQL function: group_concat () function (Database)
- 12 Linux Process Management Commands (Linux)
- Java multi-threaded shared communications variables (Programming)
- Laravel configuration PhpStorm + Xdebug + Chrome settings Debug Environment (Server)
- socket busy poll of Linux kernel 3.11 to avoid sleep switch (Linux)
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.