Dołącz do nas!

Wszystkie znaki interpunkcyjne za wyjątkiem kropek, łączników, znaków podkreśleń i spacji są niedozwolone.
Adres e-mail, koniecznie prawidłowy. Będą nań przesyłane wszystkie wiadomości e-mail od systemu. Adres nie jest udostępniany publicznie i będzie wykorzystywany jedynie w wypadku prośby o przesłanie nowego hasła lub do przesyłania informacji o nowościach czy innych komunikatów.
CAPTCHA
To pytanie ma na celu ochronę przed automatycznym spamowaniem oraz ustalenie, czy odwiedzający stronę jest człowiekiem.
Image CAPTCHA
Wpisz kod widoczny powyżej.

Logowanie

Nazwa użytkownika witryny Project Envision.
Hasło powiązane z nazwą użytkownika.
Prześlij nowe hasło

Backup and Restore MySQL Database

The possibility to backup and restore database is crucial for every Database Administrator. Below article presents the usage of mysqldump command line program for making backups of mysql databases. The restore of such a backup is done by mysql client utility.

Backup

Using mysqldump you can backup just one specific database or you can backup all databases from MySQL server. For security purposes we should create a user who sole responsibility will be making backups. The required privileges are SELECT and LOCK TABLES on desired databases. CREATE USER command was added in MySQL 5.0.2 and it is used to create user we will use for making backups. Then we use grant command to assign necessary privileges to our user, The string on *.* (star dot star) is used to assign those privileges to our user for all databases on MySQL server.

mysql> create user backupuser@localhost identified by 'b@ckupP@ssw0rd';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select, lock tables on *.* to backupuser@localhost;
Query OK, 0 rows affected (0.01 sec)

To backup database called example we can use below command

mysqldump --opt -u backupuser -pb@ckupP@ssw0rd example > example.sql

The option –opt is a shortened for of following options:

--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.

--add-drop-table Add a DROP TABLE statement before each CREATE TABLE statement
--add-locks Surround each table dump with LOCK TABLES and UNLOCK TABLES statements
--create-options Include all MySQL-specific table options in the CREATE TABLE statements.
--disable-keys For each table, surround the INSERT statements with disable and  enable keys statements
--extended-insert Use multiple-row INSERT syntax that include several VALUES lists
--lock-tables Lock all tables before dumping them
--quick Retrieve rows for a table from the server a row at a time, which means without memory buffering.
--set-charset Add SET NAMES default_character_set to the output

There are many more options you can specify, for details check MySql Documentation or manual page for mysqldum (man mysqldump). As of MySQL 5.0 –opt is the default option.

Restore

To restore database backed up using mysqldump program we will use mysql client program. We usually restore databases as the user root, because permissions to drop objects that are being imported are required.

mysql -u root -pP@ssw0rd example < example.sql

If we want to restore our database to different database, for example to create a development database, we have to create the new database first. If we wont do it we the following error will be returned.

linq:~/test# mysql -u root -pP@ssw0rd example2 < example.sql ERROR 1049 (42000): Unknown database 'example2'

To successfully import our dump we must create example2 database, unless we are using –all-databases option or –databases, but this is a topic for another post.

linq:~/test# mysql -u root -pP@ssw0rd 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3311
Server version: 5.0.51a-24+lenny1 (Debian) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database example2;
Query OK, 1 row affected (0.00 sec) mysql> exit
Bye
linq:~/test# mysql -u root -prootP@ssw0rd example2 < example.sql
Categories: