Webmasters tell you how to back up key data

One of the most important tasks of a webmaster or website maintainer is to ensure data security and stable operation.

There are many types of data maintenance. Today we only talk about the underlying data and back up mysq. speaking of mysql databases, the latest mysql version is 6. series added some features,: http://dev.mysql.com/downloads/

Mysql 6 has not been widely used. Today, we do not specifically recommend or introduce mysql 6. We recommend several versions.

Php 5.2.11 + mysql5.0.8x

Php5.3.x + 5.1.x

Php5.1.4 is also frequently used. It is a combination of large or super large websites and can be used stably,

This includes the websites currently being maintained, which are used by large sites and small sites. It can be said that they are relatively stable and recommended for use ..

Of course, mysql6 also has many new features, such as supporting more Unicode character sets and adding backup database and RESTORE statements for BACKUP and restoration. I believe they will be applied in the future.

Okay. Let's start with Mysql backup.

Let's divide it into three points.

First, the importance of regular backup,
Second, select the backup time,
Third, backup methods and methods, divided into windows and linux servers

First, the importance of regular backup

Force majeure factors, such as fires, earthquakes, floods, and other factors that cause data loss. Of course, hard disk decommission may occur, or even damage to data caused by software damage, regular data backup is very important.
For example, if my XX server is in the XX server room, I suddenly find that some servers in the room are carrying illegal information. If we want to back up the server room, I will be confused. if regular backup only consumes some human and financial resources, you can change the location to another machine.

In particular, in the current network environment, the importance of regular backup, I believe you have a deep understanding.

Second, select the backup time.

Backup time selection, because the backup process will have a certain amount of time and resource requirements, so it is generally recommended to choose between 3 to 6 o'clock in the night? Why do you need to back up at this time, because the minimum number of visitors and the largest number of server resources in this time period can be ensured more smoothly.

Isn't it exhausting to back up data so late every day? Of course, it is impossible for humans to back up data every day. Therefore, there is an automatic backup method, for example, setting a daily backup task to be executed at 03:30 a.m. (The method will be discussed later). What the webmaster needs to do is regularly download the backup data of the server.

Third, backup methods and methods.

It can be divided into windows and linux servers.

Speaking of the backup methods and methods, there are quite a few in Google. Here we will also introduce you to the General and common methods, including the implementation methods.

The following are commonly used:

PW back-end built-in backup method, mysqldump backup, database file backup method. Phpmyadmin backup method, (mysqlhotcopy can only run in unix-like)

Here, we will add that the background backup provided by pw is the same as the pma backup. The volume splitting function is provided by PW to facilitate import. Next we will take out the first several backup methods for a detailed introduction.

Backup provided by PW backend

This function evolved from pma. It can be described in one word as easy! , Convenient and fast. easy to operate. you can select table backup separately. You can back up all Forum data tables with one click. For webmasters, you can select one click and back up data with one click. The program automatically backs up and packs data volumes until the backup is completed. we recommend that you use it!

Another point is that pw back-end built-in backup, which almost avoids garbled data transfer. Cross-database version compatibility issues. Other backup methods are recommended when the data size is very large.

Mysqldump backup method,

This type of backup does not need to stop mysql, so you can directly back up some technical requirements, of course, through today's explanation, I hope everyone understands this method. we can use mysql backup in windows and linux for demonstration.

This method is used for large data backup. To back up data using this method, you need to first understand the following parameters. -- default-character-set = charset

Specifies the character set used for data export. If the data table does not use the default latin1 character set, this option must be specified during data export. Otherwise, garbled characters will occur after data is imported again.

-- Disable-keys

Tell mysqldump to add the; and; statement at the beginning and end of the INSERT statement, which can greatly improve the speed of the INSERT statement because it re-creates the index after all data is inserted. This option is only applicable to MyISAM tables.

-- Lock-all-tables,-x

Before starting the export, submit a request to lock all tables in all databases to ensure data consistency. This is a global read lock and the -- single-transaction and -- lock-tables options are automatically disabled.

-- Hex-blob

Export binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include BINARY, VARBINARY, and BLOB.

-- Triggers

Export the trigger at the same time. This option is enabled by default. Use -- skip-triggers to disable it.

For example, my local backup.

usr/local/mysql/bin/mysqldump -uroot -proot \--default-character-set=gbk --opt --extended-insert=false \--triggers –R --hex-blob -x wind7> phpwind75-0119.sql

The above command can be in mysql> input. Backup database wind7 into phpwind75-0119. SQL files.

The most common method is the tool backup method, such as phpmyadmin, PMA for short,

The disadvantage of this tool is that when the backup data is large, the export is easy to false and the import is difficult. the volume sharing function is not available. I personally do not recommend using this tool for backup, but it can be used to view some table content or to back up some fields separately.

Pma is more used for database management. We will not introduce it here. Next I will describe a method suitable for webmasters with independent servers.

Direct Copy of database files. recommended for individuals

This method is the fastest. You must stop or ensure that the data modification operation is not triggered. if the database table is modified during the file system backup process, it enters the inconsistent state of the file subject of the backup table, and it will be meaningless for future recovery tables. remember this!

Direct Copy of the database backup method is faster than mysqldump backup, but the requirement is high. It must be mysql after 3.23 and the file must be in MyISAM format, rather than ISAM format. I am not worried about this paragraph. The current popularity is basically satisfying!

Mysqldump generates text files that can be transplanted to other machines, and even those machines with different hardware structures, but the backup speed is slow. This is also a different advantage of the two backup methods. Speaking of this, we have to say that we strongly recommend a unified and easy-to-understand backup file naming mechanism for the backup file name requirements. Such as backup1 and buckup2 are not particularly meaningful.

When you implement data recovery, you will waste time figuring out what is in the file. You may find it useful to use the database name and date to form the backup file name, including the backup name in mysqldump. Example: Wind7-2010-01-19 (Database-backup time)

After talking about this, we have two scripts for you to perform practical exercises.

Note: This script and program will be officially released. Please rest assured.

Windows
@ Echo off
D:
CD backup
Rename mysqlbk. SQL "% DATE %". SQL
"D: \ soft \ mysql \ bin \ mysqldump"-uroot -- password = root -- all-database> d:/backup/mysqlbk. SQL

Save the above Code as backup. bat and add it to the scheduled task. Set the task to be executed at. [How can I add the scheduled task to the win server? Google? Haha, I won't talk about it here]

Linux

This column assumes that the phpwind data is backed up under/usr/local/mysql/var in the/usr/local/mysql directory. Create the mysqlpw. sh script under/root. The script is as follows:

#!/bin/shcd /home/phpwindphpwindfile=phpwind-$(date +%Y%m%d).tar.gz/usr/local/mysql/share/mysql/mysql.server stoptar zcvf $phpwindfile /usr/local/mysql/var/phpwind/usr/local/mysql/share/mysql/mysql.server start 

This script can be placed in the crontab and automatically executed at every day, for example: 00 04 ***/root/mysqlpw. sh)

The above two scripts are all completed with automatic backup. windows can be loaded into scheduled tasks for execution, such as starting at half past three a.m. The same is true for linux. You can execute a backup in the early morning with the built-in crontab settings.

Let's talk about the meaning of the script so that you can understand the principle of script execution.

For Windows (linux scripts with annotations), the backup files are stored in d: \ backup.

Ren mysqlbk. SQL "% DATE %". SQL

Change the name of the file.

"D: \ soft \ mysql \ bin \ mysqldump"-uroot -- password = root, -- all-database> d:/backup/mysqlbk. SQL

Here is full backup. You can also select a single database such as wind7. Well, after automatic backup is finished, I will talk about AI backup. Haha.