Tugas umum DBA untuk instans DB MySQL

Dalam konten berikut, Anda dapat menemukan deskripsi implementasi khusus Amazon RDS dari beberapa tugas DBA umum untuk instans DB yang menjalankan mesin database MySQL. Untuk memberikan pengalaman layanan terkelola, Amazon RDS tidak memberikan akses shell ke instans DB. Selain itu, Amazon RDS juga membatasi akses ke prosedur dan tabel sistem tertentu yang memerlukan hak istimewa tingkat lanjut.

Untuk informasi tentang cara bekerja dengan file log MySQL di Amazon RDS, lihat log SQL database saya.

Memahami pengguna yang telah ditentukan

Amazon RDS secara otomatis membuat beberapa pengguna yang telah ditentukan dengan RDS baru untuk instans MySQL DB. Pengguna yang telah ditentukan sebelumnya dan hak istimewa mereka tidak dapat diubah. Anda tidak dapat menghapus, mengganti nama, atau memodifikasi hak istimewa untuk pengguna yang telah ditentukan ini. Mencoba untuk melakukannya akan mengakibatkan kesalahan.

Model hak akses berbasis peran

Dimulai dengan RDS untuk MySQL versi 8.0.36, Anda tidak dapat memodifikasi tabel dalam database secara langsung. mysql Secara khusus, Anda tidak dapat membuat pengguna database dengan melakukan operasi bahasa manipulasi data (DHTML) pada grant tabel. Sebagai gantinya, Anda menggunakan pernyataan manajemen akun MySQL CREATE USER seperti,GRANT, REVOKE dan untuk memberikan hak istimewa berbasis peran kepada pengguna. Anda juga tidak dapat membuat jenis objek lain seperti prosedur tersimpan dalam basis data mysql. Anda masih dapat mengueri tabel mysql. Jika Anda menggunakan replikasi log biner, perubahan yang dilakukan langsung ke mysql tabel pada instans DB sumber tidak direplikasi ke cluster target.

Dalam beberapa kasus, aplikasi Anda mungkin menggunakan pintasan untuk membuat pengguna atau objek lain dengan melakukan penyisipan ke dalam tabel mysql. Jika demikian, ubah kode aplikasi Anda untuk menggunakan pernyataan yang sesuai seperti CREATE USER.

Untuk mengekspor metadata bagi pengguna database selama migrasi dari database MySQL eksternal, gunakan salah satu metode berikut:

Untuk menyederhanakan pengelolaan izin bagi banyak pengguna atau aplikasi, Anda dapat menggunakan pernyataan CREATE ROLE untuk membuat peran yang memiliki serangkaian izin. Kemudian, Anda dapat menggunakan pernyataan GRANT dan SET ROLE serta fungsi current_role untuk menetapkan peran ke pengguna atau aplikasi, mengganti peran saat ini, dan memeriksa peran mana yang berlaku. Untuk informasi selengkapnya tentang sistem izin berbasis peran di MySQL 8.0, lihat Using Roles dalam Panduan Referensi MySQL.

Dimulai dengan versi 8.0.36, RDS untuk MySQL menyertakan peran khusus yang memiliki semua hak istimewa berikut. Peran ini bernama rds_superuser_role. Pengguna administratif utama untuk setiap instans DB sudah memiliki peran ini diberikan. Peran rds_superuser_role mencakup hak akses berikut untuk semua objek basis data:

Definisi peran juga mencakup WITH GRANT OPTION sehingga pengguna administratif dapat memberikan peran tersebut kepada pengguna lain. Secara khusus, administrator harus memberikan hak istimewa yang diperlukan untuk melakukan replikasi log biner dengan cluster MySQL sebagai target.

SHOW GRANTS FOR rds_superuser_role@'%';

Ketika Anda memberikan akses dengan menggunakan peran di RDS untuk MySQL versi 8.0.36 dan yang lebih tinggi, Anda juga mengaktifkan peran dengan menggunakan pernyataan or. SET ROLE role_name SET ROLE ALL Contoh berikut menunjukkan caranya. Ganti nama peran yang sesuai untuk CUSTOM_ROLE.

# Grant role to user
mysql> GRANT CUSTOM_ROLE TO 'user'@'domain-or-ip-address'

# Check the current roles for your user. In this case, the CUSTOM_ROLE role has not been activated.
# Only the rds_superuser_role is currently in effect.
mysql> SELECT CURRENT_ROLE();
+--------------------------+
| CURRENT_ROLE()           |
+--------------------------+
| `rds_superuser_role`@`%` |
+--------------------------+
1 row in set (0.00 sec)

# Activate all roles associated with this user using SET ROLE.
# You can activate specific roles or all roles.
# In this case, the user only has 2 roles, so we specify ALL.
mysql> SET ROLE ALL;
Query OK, 0 rows affected (0.00 sec)

# Verify role is now active
mysql> SELECT CURRENT_ROLE();
+--------------------------------------------------+
| CURRENT_ROLE()                                   |
+--------------------------------------------------+
| `CUSTOM_ROLE`@`%`,`rds_superuser_role`@`%` |
+--------------------------------------------------+

Hak istimewa dinamis

Hak istimewa dinamis adalah hak istimewa MySQL yang dapat Anda berikan secara eksplisit dengan menggunakan pernyataan. GRANT Bergantung pada versi RDS untuk MySQL Anda, RDS memungkinkan Anda untuk memberikan hanya hak istimewa dinamis tertentu. RDS melarang beberapa hak istimewa ini karena dapat mengganggu operasi database tertentu, seperti replikasi dan pencadangan.

Tabel berikut menunjukkan hak istimewa mana yang dapat Anda berikan untuk versi MySQL yang berbeda. Jika Anda memutakhirkan dari versi MySQL yang lebih rendah dari 8.0.36 ke versi 8.0.36 atau lebih tinggi, Anda mungkin harus memperbarui kode aplikasi Anda jika pemberian hak istimewa tertentu tidak lagi diizinkan.

Mengakhiri sesi atau kueri

Anda dapat mengakhiri sesi atau kueri pengguna di instans DB dengan menggunakan perintah rds_kill dan rds_kill_query. Pertama, hubungkan ke instans DB MySQL Anda, lalu jalankan perintah yang sesuai seperti yang ditunjukkan berikut ini. Untuk informasi selengkapnya, lihat Menghubungkan ke instans DB yang menjalankan mesin basis data MySQL.

CALL mysql.rds_kill(thread-ID)
CALL mysql.rds_kill_query(thread-ID) 

Misalnya, untuk mengakhiri sesi yang berjalan di thread 99, ketikkan hal berikut:

CALL mysql.rds_kill(99); 

Untuk mengakhiri kueri yang sedang berjalan pada thread 99, ketikkan hal berikut:

CALL mysql.rds_kill_query(99); 

Melewati kesalahan replikasi saat ini

Anda dapat melewati kesalahan pada replika baca jika kesalahan tersebut menyebabkan replika baca Anda berhenti merespons dan kesalahan tersebut tidak memengaruhi integritas data Anda.

SHOW REPLICA STATUS\G 

Anda dapat melewati kesalahan pada replika baca dengan cara berikut.

Memanggil prosedur mysql.rds_skip_repl_error

Amazon RDS menyediakan prosedur tersimpan yang dapat Anda panggil untuk melewati kesalahan pada replika baca Anda. Pertama, hubungkan ke replika baca Anda, lalu jalankan perintah yang sesuai seperti yang ditunjukkan berikut ini. Untuk informasi selengkapnya, lihat Menghubungkan ke instans DB yang menjalankan mesin basis data MySQL.

Untuk melewati kesalahan, jalankan perintah berikut.

CALL mysql.rds_skip_repl_error; 

Perintah ini tidak berpengaruh jika Anda menjalankannya di instans DB sumber, atau di replika baca yang belum mengalami kesalahan replikasi.

Untuk informasi selengkapnya, seperti versi MySQL yang mendukung mysql.rds_skip_repl_error, lihat mysql.rds_skip_repl_error.

Mengatur parameter slave_skip_errors

Untuk melewati satu atau beberapa kesalahan, Anda dapat mengatur parameter slave_skip_errors statis pada replika baca. Anda dapat mengatur parameter ini untuk melewati satu atau beberapa kode kesalahan replikasi spesifik. Saat ini, Anda dapat mengatur parameter ini hanya untuk instans DB RDS for MySQL 5.7. Setelah Anda mengubah pengaturan untuk parameter ini, pastikan untuk melakukan boot ulang instans DB Anda agar pengaturan baru dapat diterapkan. Untuk informasi tentang cara mengatur parameter ini, lihat dokumentasi MySQL.

Sebaiknya atur parameter ini dalam grup parameter DB terpisah. Anda dapat mengaitkan grup parameter DB ini hanya dengan replika baca yang perlu melewati kesalahan. Mengikuti praktik terbaik ini akan mengurangi potensi dampak pada instans DB dan replica baca lainnya.

Bekerja dengan tablespace InnoDB untuk meningkatkan waktu pemulihan crash

Setiap tabel di MySQL terdiri dari definisi, data, dan indeks tabel. Mesin penyimpanan InnoDB MySQL menyimpan data dan indeks tabel di tablespace. InnoDB membuat tablespace bersama global yang berisi kamus data dan metadata relevan lainnya, serta dapat berisi data dan indeks tabel. InnoDB juga dapat membuat tablespace terpisah untuk setiap tabel dan partisi. Tablespace terpisah ini disimpan dalam file berekstensi .ibd dan judul setiap tablespace berisi nomor pengidentifikasi unik.

Amazon RDS menyediakan parameter dalam grup parameter MySQL yang disebut innodb_file_per_table. Parameter ini mengontrol apakah InnoDB menambahkan data dan indeks tabel baru ke tablespace bersama (dengan menetapkan nilai parameter ke 0) atau ke tablespace terpisah (dengan menetapkan nilai parameter ke 1). Amazon RDS menetapkan nilai default untuk parameter innodb_file_per_table ke 1, yang memungkinkan Anda meletakkan tabel InnoDB terpisah dan mengklaim ulang penyimpanan yang digunakan oleh tabel tersebut untuk instans DB. Biasanya, penetapan parameter innodb_file_per_table ke 1 adalah pengaturan yang direkomendasikan.

Anda harus menetapkan parameter innodb_file_per_table ke 0 ketika Anda memiliki tabel dalam jumlah besar. Misalnya, Anda memiliki lebih dari 1.000 tabel saat menggunakan penyimpanan SSD standar (magnetik) atau SSD umum atau lebih dari 10.000 tabel saat Anda menggunakan penyimpanan IOPS yang Tersedia. Saat Anda menetapkan parameter ini ke 0, tablespace terpisah tidak dibuat dan hal ini dapat meningkatkan waktu yang diperlukan untuk pemulihan crash basis data.

MySQL memproses setiap file metadata, yang mencakup tablespace, selama siklus pemulihan crash. Waktu yang diperlukan oleh MySQL untuk memproses informasi metadata di tablespace bersama dapat diabaikan, dibandingkan dengan waktu yang diperlukan untuk memproses ribuan file tablespace ketika ada beberapa tablespace. Karena nomor tablespace disimpan di judul setiap file, waktu gabungan untuk membaca semua file tablespace tersebut bisa mencapai beberapa jam. Misalnya, pemrosesan sejuta tablespace InnoDB pada penyimpanan standar selama siklus pemulihan crash dapat memakan waktu mulai lima hingga delapan jam. Dalam beberapa kasus, InnoDB dapat menentukan apakah pembersihan tambahan diperlukan setelah siklus pemulihan crash selesai sehingga siklus pemulihan crash akan dimulai lagi. Hal ini akan memperpanjang waktu pemulihan. Perlu diperhatikan bahwa siklus pemulihan crash juga melibatkan transaksi rolling back, perbaikan halaman yang rusak, dan operasi lain selain pemrosesan informasi tablespace.

Karena parameter innodb_file_per_table berada di grup parameter, Anda dapat mengubah nilai parameter dengan mengedit grup parameter yang digunakan oleh instans DB Anda tanpa perlu melakukan boot ulang instans DB. Setelah pengaturan diubah, misalnya, dari 1 (buat tabel terpisah) ke 0 (gunakan tablespace bersama), tabel InnoDB baru akan ditambahkan ke tablespace bersama sedangkan tabel yang ada tetap memiliki tablespace terpisah. Untuk memindahkan tabel InnoDB ke tablespace bersama, Anda harus menggunakan perintah ALTER TABLE.

Memigrasikan beberapa tablespace ke tablespace bersama

Anda dapat memindahkan metadata tabel InnoDB dari tablespace-nya ke tablespace bersama. Hal ini akan membuat ulang metadata tabel sesuai dengan pengaturan parameter innodb_file_per_table. Pertama, hubungkan ke instans MySQL DB Anda, lalu jalankan perintah yang sesuai seperti yang ditunjukkan berikut. Untuk informasi selengkapnya, lihat Menghubungkan ke instans DB yang menjalankan mesin basis data MySQL.

ALTER TABLE table_name ENGINE = InnoDB, ALGORITHM=COPY; 

Misalnya, kueri berikut mengembalikan pernyataan ALTER TABLE untuk setiap tabel InnoDB yang tidak ada di tablespace bersama.

Untuk instans DB MySQL 5.7:

SELECT CONCAT('ALTER TABLE `', 
REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', 
REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query 
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES 
WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');

Untuk instans DB MySQL 8.0:

SELECT CONCAT('ALTER TABLE `', 
REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', 
REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query 
FROM INFORMATION_SCHEMA.INNODB_TABLES 
WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');

Pembuatan ulang tabel MySQL untuk memindahkan metadata tabel tersebut ke tablespace bersama memerlukan ruang penyimpanan tambahan sementara guna membuat ulang tabel. Jadi, instans DB harus memiliki ruang penyimpanan yang tersedia. Selama pembuatan ulang, tabel dikunci dan tidak dapat diakses oleh kueri. Untuk tabel kecil atau tabel yang jarang diakses, hal ini mungkin tidak menjadi masalah. Untuk tabel besar atau tabel yang sering diakses dalam lingkungan yang sangat serentak, Anda dapat membuat ulang tabel pada replika baca.

Anda dapat membuat replika baca dan memigrasi metadata tabel ke tablespace bersama pada replika baca. Meskipun pernyataan ALTER TABLE memblokir akses pada replika baca, instans DB sumber tidak terpengaruh. Instans DB sumber akan terus membuat log binernya, sementara replika baca mengalami lag selama proses pembuatan ulang tabel. Karena pembuatan ulang ini memerlukan ruang penyimpanan tambahan dan file log pemutaran ulang bisa menjadi besar, Anda harus membuat replika baca dengan alokasi penyimpanan yang lebih besar dari instans DB sumber.

Guna membuat replika baca dan membuat ulang InnoDB untuk menggunakan tablespace bersama, lakukan langkah-langkah berikut:

Mengelola Global Status History

MySQL mempertahankan banyak variabel status yang memberikan informasi tentang operasinya. Nilai variabel tersebut dapat membantu Anda mendeteksi masalah penguncian atau memori pada instans DB. Nilai variabel status ini bersifat kumulatif sejak instans DB dimulai terakhir kali. Anda dapat menetapkan ulang sebagian besar variabel status ke 0 dengan menggunakan perintah FLUSH STATUS.

Agar dapat memantau nilai ini dari waktu ke waktu, Amazon RDS menyediakan serangkaian prosedur yang akan mengambil snapshot nilai-nilai variabel status ini dari waktu ke waktu dan menuliskannya ke tabel, beserta perubahan apa pun yang dibuat sejak snapshot terakhir. Infrastruktur ini, yang disebut Global Status History (GoSH), diinstal di seluruh instans DB MySQL mulai versi 5.5.23. GoSH dinonaktifkan secara default.

Untuk mengaktifkan GoSH, aktifkan penjadwal peristiwa terlebih dahulu dari grup parameter DB dengan menetapkan parameter event_scheduler ke ON. Untuk instans DB MySQL yang menjalankan MySQL 5.7, tetapkan juga parameter show_compatibility_56 ke 1. Untuk informasi tentang cara membuat dan memodifikasi grup parameter DB, lihat Grup parameter untuk RDS. Untuk informasi tentang dampak dari pengaktifan parameter ini, lihat show_compatibility_56 di Panduan Referensi MySQL 5.7.

Anda kemudian dapat menggunakan prosedur dalam tabel berikut untuk mengaktifkan dan mengonfigurasi GoSH. Pertama, hubungkan ke instans DB MySQL Anda, lalu jalankan perintah yang sesuai seperti yang ditunjukkan berikut. Untuk informasi selengkapnya, lihat Menghubungkan ke instans DB yang menjalankan mesin basis data MySQL. Untuk setiap prosedur, jalankan perintah berikut dan ganti nama prosedur:

CALL procedure-name; 

Tabel berikut mencantumkan semua prosedur yang dapat Anda gunakan untuk nama prosedur di perintah sebelumnya.

Saat GoSH berjalan, Anda dapat mengueri tabel tujuan penulisannya. Misalnya, untuk mengueri rasio hit kumpulan buffer Innodb, Anda dapat menjalankan kueri berikut:

select a.collection_end, a.collection_start, (( a.variable_Delta-b.variable_delta)/a.variable_delta)*100 as "HitRatio" 
    from mysql.rds_global_status_history as a join mysql.rds_global_status_history as b on a.collection_end = b.collection_end
    where a. variable_name = 'Innodb_buffer_pool_read_requests' and b.variable_name = 'Innodb_buffer_pool_reads'