Mysql database backup code

Database Backup is what every WEB developer and database administrator must do. Today we want to talk about the logical backup in the mysql database and back up the database into a text file, let's talk about three database backup methods for all of you.

Database tutorial backupThis is what every WEB developer and database administrator must do. What we want to talk about today is the logical backup in the mysql tutorial database, which backs up the database into a text file, let's talk about three database backup methods for all of you.

1. Back up the specified database or some tables of the database.

Shell> mysqldump [options] db_name [tables]

2. Back up one or more databases

Shell> mysqldum [options] --- database db1 [db2 db3...]

Iii. Back up all databases

Shell> mysqldump [options] -- all-database

If no table is specified, all tables in the database are exported by default. Let's take a look at the mysqldump instance.

Back up all databases

[BKjia. c0m @ localhost ~] $ Mysqldump-uroot-p -- all-database> d: www. bKjia. c0m111cn. SQL;

Enter password: 123456;

Backup database test

[BKjia. c0m @ localhost ~] $ Mysqldump-uroot-p -- test> d: www. bKjia. c0m111cn. SQL;

Back up the table emp in database test;

[BKjia. c0m @ localhost ~] $ Mysqldump-uroot-p -- test emp> d: www. bKjia. c0m111cn. SQL;

All tables in the backup database test are separated by commas, as shown below:

[BKjia. c0m @ localhost ~] # Mysqldump-uroot-T/bKjia. c0m test emp -- fields-terminated-','

[BKjia. c0m @ localhost ~] # More emp.txt

1, z1

2, z2

3, z3

There are many options for mysqldump, which can be viewed with help.

Note: To ensureDatabase BackupConsistency. The myisam database storage engine needs to add the-l parameter during backup, indicating that all tables are locked. During the backup, all tables can only be read but cannot be updated. However, for innodb and dbd, we can use a better option -- single-transaction. This option award allows the innodb Storage engine to get a snapshot so that the backup database can be consistent.

The original tutorial on this site is reproduced and indicated by www. bKjia. c0m