Mysqldump dengan prosedur dan pemicu

Utilitas klien mysqldump melakukan backup logis, menghasilkan satu set pernyataan SQL yang dapat dieksekusi untuk mereproduksi definisi objek database asli dan data tabel. Itu membuang satu atau lebih database MySQL untuk cadangan atau transfer ke server lain. Perintah mysqldump juga dapat menghasilkan keluaran dalam CSV, teks yang dibatasi lainnya, atau format XML. Meskipun tampak seperti file yang dapat dieksekusi kecil, cakupannya lebih besar untuk mengakomodasi berbagai kasus penggunaan

mysqldump menerima berbagai opsi koneksi, Opsi DDL, Opsi Format, Opsi Penyaringan, Opsi Kinerja, Opsi Transaksional, dll

Posting ini akan menunjukkan cara menggunakan mysqldump untuk menyertakan dan mengecualikan tampilan, pemicu, dan rutinitas dari pencadangan atau pemulihan

Sintaksis
mysqldump -h -u -p
—-hex-blob –triggers –routines dbname > schema. sql

Contoh
mysqldump -h 127. 0. 0. 1 -u root -p –hex-blob –triggers –routines dbname > schema. sql
mysqldump -h 127. 0. 0. 1 -u root -pMyPassword –hex-blob –triggers –routines dbname > skema. sql
mysqldump -h instance-live. uvxyzcynxmabbaz. eu-timur-2. rds. amazonaw. com -u root -p –hex-blob –triggers –routines dbname > schema. sql

Catatan. Karena mysqldump tidak membedakan antara VIEW dan TABLE, Anda tidak perlu menentukan –view karena perintah juga akan menyertakan tampilan. Karena tidak ada opsi seperti itu, itu akan memberikan kesalahan jika Anda menentukannya

Catatan lain. Pada perintah di atas, –hex-blob juga ditentukan yang biasanya membuang kolom biner yang menggunakan notasi heksadesimal

Apa itu Prosedur Tersimpan?

Prosedur tersimpan, menurut definisi, adalah segmen kode SQL deklaratif yang disimpan dalam katalog database dan dapat dipanggil nanti oleh program, pemicu, atau bahkan prosedur tersimpan.

Apa itu Pemicu?

Pemicu adalah prosedur khusus yang digerakkan oleh peristiwa, mereka disimpan dan dikelola oleh database. Pemicu adalah prosedur SQL yang memulai tindakan pada suatu peristiwa (Seperti INSERT, DELETE atau UPDATE) terjadi

mysqldump akan mencadangkan secara default semua pemicu tetapi BUKAN prosedur/fungsi yang tersimpan. Ada 2 parameter mysqldump yang mengontrol perilaku ini

--routines - FALSE secara default

--triggers - BENAR secara default

Ini berarti bahwa jika Anda ingin menyertakan dalam skrip cadangan yang ada juga pemicu dan prosedur tersimpan, Anda hanya perlu menambahkan parameter baris perintah --routines

Prosedur dan Rutinitas Tersimpan Cadangan

Kita perlu menentukan --routines untuk mengambil cadangan prosedur tersimpan dengan data dan tabel

Perintah berikut akan mengambil cadangan seluruh database termasuk prosedur tersimpan. Misalnya, nama database Anda adalah "mydb"

mysqldump -u root -p --routines mydb > mydb. sql

Untuk mengambil cadangan hanya Stored Procedures dan Triggers (Exclude table and data ) gunakan perintah berikut

mysqldump --routines --no-create-info --no-data --no-create-db --lewati-opt mydb > mydb. sql

mysqldump -uroot -p --skip-triggers mydb> mydb. sql

Mengembalikan Prosedur

Untuk mengembalikan prosedur tersimpan dalam database cukup gunakan perintah berikut, Tapi pastikan Anda telah mengambil cadangan dengan benar sebelum mengembalikannya untuk menghindari kehilangan data

Pada bagian kedua dan terakhir dari praktik terbaik mysqldump kami, kami akan berbicara tentang cara menangani migrasi dan impor untuk objek dan tampilan program yang disimpan dari database MySQL Anda. Untuk membaca lebih lanjut tentang prasyarat agar operasi dump dan pemulihan berhasil untuk database MySQL yang besar, lihat bagian pertama dari seri blog 2 bagian ini

Praktik Terbaik mysqldump

• Bagian 1 – Prasyarat MySQL

Mengimpor prosedur, fungsi, dan pemicu tersimpan Anda

Secara default, mysqldump mengimpor tampilan dan pemicu. Namun itu tidak mengimpor prosedur, fungsi, dan acara. Untuk mengimpor prosedur dan fungsi, opsi --routines_ harus ditentukan, dan untuk mengimpor peristiwa, opsi --events harus ditentukan

1. Mengimpor pemicu

Mysqldump akan mencoba membuang semua pemicu di database Anda secara default. Agar dapat menghapus triggers tabel, Anda harus memiliki  hak istimewa untuk tabel tersebut. Jika pengguna dump tidak memiliki hak istimewa ini, pemicu akan dilewati dan mysqldump tidak akan menimbulkan kesalahan apa pun. Jadi jangan kaget jika Anda tidak melihat pemicu apa pun yang diimpor ke database tujuan Anda

2. Mengimpor acara

Untuk mengimpor acara, Anda perlu menentukan opsi --events saat menjalankan utilitas mysqldump. Opsi ini memerlukan  hak istimewa untuk database tersebut. Sekali lagi, mysqldump akan diam-diam melewatkan acara jika pengguna dump tidak memiliki hak istimewa ini, bahkan jika Anda telah menentukan opsi –event saat menjalankan mysqldump

3. Mengimpor fungsi dan prosedur tersimpan

Untuk mengimpor rutinitas, Anda perlu menentukan opsi --routines saat menjalankan utilitas mysqldump. Opsi ini memerlukan

CREATE [email protected]'%' VIEW mydb.V1 AS SELECT * FROM solution_table;
CREATE [email protected]'%' VIEW mydb.V2 AS SELECT * FROM V1 where num1=10;

3. 1 Mengimpor fungsi non deterministik

Program tersimpan yang memodifikasi data disebut non deterministik jika tidak menghasilkan hasil yang dapat diulang. Contoh fungsi rand(). Sangat menantang untuk menggunakan fungsi seperti itu dalam pengaturan yang direplikasi, karena dapat menghasilkan data yang berbeda pada sumber dan replika. Untuk mengontrol kemungkinan seperti itu, MySQL menerapkan batasan tertentu pada pembuatan fungsi jika log biner diaktifkan

Secara default, agar pernyataan 

CREATE [email protected]'%' VIEW mydb.V1 AS SELECT * FROM solution_table;
CREATE [email protected]'%' VIEW mydb.V2 AS SELECT * FROM V1 where num1=10;

CREATE [email protected]'%' VIEW mydb.V1 AS SELECT * FROM solution_table;
CREATE [email protected]'%' VIEW mydb.V2 AS SELECT * FROM V1 where num1=10;

CREATE [email protected]'%' VIEW mydb.V1 AS SELECT * FROM solution_table;
CREATE [email protected]'%' VIEW mydb.V2 AS SELECT * FROM V1 where num1=10;

CREATE [email protected]'%' VIEW mydb.V1 AS SELECT * FROM solution_table;
CREATE [email protected]'%' VIEW mydb.V2 AS SELECT * FROM V1 where num1=10;

ERROR 1418 (HY000) at line 181: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_funable)

Jadi jika fungsi Anda tidak dideklarasikan sebagai deterministik pada sumbernya, dan pencatatan biner diaktifkan pada tujuan Anda, Anda akan melihat kesalahan di atas selama pemulihan dump. Oleh karena itu, penting untuk memahami sifat deterministik fungsi Anda di muka. Jika yakin bahwa fungsi Anda bersifat deterministik, Anda perlu mengaktifkan  konfigurasi di tujuan Anda sebelum operasi pemulihan. Saat diaktifkan, MySQL memungkinkan pembuatan fungsi tersebut bahkan saat logging biner diaktifkan

4. Karakteristik KEAMANAN SQL dari rutinitas dan tampilan yang disimpan

MySQL mengizinkan

CREATE [email protected]'%' VIEW mydb.V1 AS SELECT * FROM solution_table;
CREATE [email protected]'%' VIEW mydb.V2 AS SELECT * FROM V1 where num1=10;

CREATE [email protected]'%' VIEW mydb.V1 AS SELECT * FROM solution_table;
CREATE [email protected]'%' VIEW mydb.V2 AS SELECT * FROM V1 where num1=10;

Command failed with error - ERROR 1449 (HY000) at line 206 in file: '/mysql_data/mysqldump/sqldump_1582457155758.sql': The user specified as a definer ('admin'@'%') does not exist.

Command failed with error - ERROR 1449 (HY000) at line 206 in file: '/mysql_data/mysqldump/sqldump_1582457155758.sql': The user specified as a definer ('admin'@'%') does not exist.

Command failed with error - ERROR 1449 (HY000) at line 206 in file: '/mysql_data/mysqldump/sqldump_1582457155758.sql': The user specified as a definer ('admin'@'%') does not exist.

Command failed with error - ERROR 1449 (HY000) at line 206 in file: '/mysql_data/mysqldump/sqldump_1582457155758.sql': The user specified as a definer ('admin'@'%') does not exist.

Command failed with error - ERROR 1449 (HY000) at line 206 in file: '/mysql_data/mysqldump/sqldump_1582457155758.sql': The user specified as a definer ('admin'@'%') does not exist.

Command failed with error - ERROR 1449 (HY000) at line 206 in file: '/mysql_data/mysqldump/sqldump_1582457155758.sql': The user specified as a definer ('admin'@'%') does not exist.

Command failed with error - ERROR 1449 (HY000) at line 206 in file: '/mysql_data/mysqldump/sqldump_1582457155758.sql': The user specified as a definer ('admin'@'%') does not exist.

Jika Anda memulihkan rutinitas atau tampilan yang tersimpan, Anda perlu memastikan akun pengguna yang menentukan ada di database tujuan Anda dengan hibah yang sesuai. Jika tidak, Anda akan mengalami kegagalan selama pemulihan

Mari kita tunjukkan ini dengan contoh yang terkait dengan tampilan

Misalkan Anda memiliki Views V1 dan V2 yang didefinisikan seperti di bawah ini

CREATE [email protected]'%' VIEW mydb.V1 AS SELECT * FROM solution_table;
CREATE [email protected]'%' VIEW mydb.V2 AS SELECT * FROM V1 where num1=10;

Perhatikan bahwa tampilan dibuang secara default oleh mysqldump dan jika Anda tidak memiliki pengguna 'admin' di tujuan Anda, Anda akan menemukan kesalahan di bawah ini selama operasi pemulihan

Command failed with error - ERROR 1449 (HY000) at line 206 in file: '/mysql_data/mysqldump/sqldump_1582457155758.sql': The user specified as a definer ('admin'@'%') does not exist.

Perhatikan bahwa ini tidak hanya cukup untuk memastikan pengguna ada, tetapi pengguna harus memiliki hak istimewa yang sesuai untuk menjalankan tampilan. Misalnya, jika pengguna 

Command failed with error - ERROR 1449 (HY000) at line 206 in file: '/mysql_data/mysqldump/sqldump_1582457155758.sql': The user specified as a definer ('admin'@'%') does not exist.

Command failed with error - ERROR 1449 (HY000) at line 206 in file: '/mysql_data/mysqldump/sqldump_1582457155758.sql': The user specified as a definer ('admin'@'%') does not exist.

'/mysql_data/mysqldump/sqldump_1582456858033.sql':View 'mydb.V2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them.

Tertarik dengan solusi MySQL yang dikelola sepenuhnya?

Untuk mempelajari lebih lanjut tentang bagaimana penyedia DBaaS seperti ScaleGrid dapat membantu Anda mengelola database MySQL Anda, lihat halaman MySQL kami. Lihat bagaimana ScaleGrid dapat membuat Anda lebih fokus pada pengembangan produk Anda, dan lebih sedikit pada pengelolaan database

Ringkasan

Dalam seri blog 2 bagian ini, kami membahas prasyarat penting yang perlu Anda tangani untuk memastikan keberhasilan migrasi data dan program tersimpan Anda. ScaleGrid MySQL hosting menangani panduan ini untuk memberikan pengalaman yang lancar saat mengimpor data Anda ke platform ScaleGrid. Silakan berbagi dengan kami pengalaman dan praktik terbaik yang Anda adopsi untuk migrasi data MySQL

Bisakah kita menggunakan pemicu dalam prosedur tersimpan?

Bagaimana cara membuang pemicu di MySQL?

Bagaimana Anda memanggil prosedur menggunakan pemicu?

Bagaimana Anda akan mengambil cadangan prosedur dan fungsi pemicu saja?