نحوه ایجاد Replication MySQL

از بین رفتن اطلاعات همیشه یکی از ترسناک‌ترین اتفاقاتی است که ممکن است اتفاق بیافتد، اما وجود چند سرور پایگاه داده به ما کمک می‌کند که اگر یکی از سرورها دچار مشکل شود، اطلاعات از بین نرود و همواره در در دسترس باشد. در این زمینه موضوع Replication (به همگام سازی داده‌ها به طور همزمان در چند پایگاه داده مجزا، تکرار یا همگام‌سازی (replication) می‌گویند) از اهمیت زیادی برخوردار است. در این خصوص دیتابیس MySQL به عنوان یکی از مشهورترین سیستم‌های مدیریت دیتابیس، چندین ویژگی همگام‌سازی داده را به صورت پیش‌فرض ارائه می‌دهد که به شما اجازه می‌دهد چندین نسخه از داده‌های خود داشته باشید. اکنون اگر قصد ایجاد Replication در MySQL را دارید، در ادامه همراه ما باشید.

در ادامه توضیح می‌دهیم که چگونه می‌توان با ایجاد Replication، یک سرور MySQL را به عنوان پایگاه داده اصلی (منبع) تنظیم کرد و سپس یک سرور دیگر را به عنوان نسخه تکرار آن پیکربندی کرد.

فهرست مطالب

• همگام سازی یا Replication در MySQL چیست؟
  • روش‌های Replication در MySQL
• پیش نیاز‌های لازم برای ایجاد Replication در MySQL
• ایجاد Replication MySQL
  • مرحله 1. تنظیم فایروال سرور اصلی
  • مرحله 2. پیکربندی پایگاه داده اصلی
  • مرحله 3. ایجاد یک کاربر replication
  • مرحله 4. به‌دست آوردن مختصات لاگ باینری از منبع
    • اگر پایگاه داده منبع شما داده‌هایی برای انتقال ندارد:
  • اگر پایگاه داده شما داده‌هایی برای انتقال دارد:
  • مرحله 5. پیکربندی پایگاه داده کپی
  • مرحله 6. شروع و تست
• نتیجه‌گیری

همگام سازی یا Replication در MySQL چیست؟

در MySQL، همگام‌سازی (Replication) به این معناست که تغییرات انجام‌شده روی داده‌ها در پایگاه داده اصلی، در یک فایل به نام “لاگ باینری” ثبت می‌شوند. بنابراین وقتی یک نسخه‌ی پشتیبان (Replica) راه‌اندازی می‌شود، دو فرآیند اصلی ایجاد می‌کند:

1. I/O Thread: این فرآیند به پایگاه داده اصلی متصل می‌شود و تغییرات ثبت‌شده در لاگ باینری را خط به خط می‌خواند. سپس این تغییرات را در یک فایل لوکال به نام “relay log” روی سرور نسخه‌ی پشتیبان ذخیره می‌کند.
2. SQL Thread: این فرآیند تغییرات ذخیره‌شده در relay log را می‌خواند و آن‌ها را روی نسخه‌ی پشتیبان اعمال می‌کند.

روش‌های Replication در MySQL

MySQL دو روش اصلی برای همگام‌سازی داده‌ها دارد که تفاوت آن‌ها در نحوه‌ی پیگیری تغییرات است:

1. همگام‌سازی مبتنی بر موقعیت فایل لاگ باینری (Binary Log File Position-Based Replication):
   • این روش سنتی است و نیازمند این است که هنگام تنظیم نسخه‌ی پشتیبان، یک موقعیت خاص در فایل لاگ باینری (شامل نام فایل و موقعیت دقیق در آن) مشخص شود.
   • اگر موقعیت درست نباشد، نسخه‌ی پشتیبان ممکن است تمام تغییرات قدیمی را دوباره اعمال کند، که می‌تواند باعث مشکلاتی شود، به‌ویژه اگر فقط بخواهید تغییرات جدید را تکرار کنید.
   • این روش برای بسیاری از موارد ساده مناسب است، اما در تنظیمات پیچیده‌تر ممکن است مشکلاتی ایجاد کند.
2. همگام‌سازی مبتنی بر تراکنش (Transaction-Based Replication):
   • این روش جدیدتر است و از شناسه جهانی تراکنش (GTID) استفاده می‌کند. هر تراکنش در پایگاه داده اصلی یک GTID منحصر به فرد دریافت می‌کند.
   • وقتی یک تراکنش در پایگاه داده اصلی انجام می‌شود، GTID آن به همراه تغییرات به نسخه‌ی پشتیبان ارسال می‌شود.
   • اگر نسخه‌ی پشتیبان، تراکنشی با GTID تکراری دریافت کند، آن را نادیده می‌گیرد. این کار باعث می‌شود سازگاری بین پایگاه داده اصلی و نسخه‌ی پشتیبان حفظ شود.
   • مزیت بزرگ این روش این است که نسخه‌ی پشتیبان نیازی به دانستن موقعیت دقیق در فایل لاگ باینری ندارد و مدیریت تکرار ساده‌تر می‌شود.

پیش نیاز‌های لازم برای ایجاد Replication در MySQL

برای دنبال کردن این راهنما، به موارد زیر نیاز دارید:

1. دو سرور با سیستم‌عامل اوبونتو 20.04: هر دو سرور باید یک کاربر غیر root داشته باشند که دسترسی‌های مدیریتی (sudo) دارد. همچنین، هر دو سرور باید فایروال‌هایی داشته باشند که با UFW پیکربندی شده‌اند.
2. نصب MySQL:
   • روی سرور اول، MySQL را به عنوان پایگاه داده اصلی نصب می‌کنید.
   • روی سرور دوم، MySQL را به عنوان نسخه‌ی پشتیبان (Replica) تنظیم می‌کنید.
3. دستورات اجرایی:
   • دستوراتی که باید روی سرور اصلی اجرا شوند، با کد source:~$ شروع می‌شوند.
   • دستوراتی که باید روی سرور نسخه‌ی پشتیبان اجرا شوند، با کد replica:~$ شروع می‌شوند.
4. انتقال داده‌ها (اختیاری):
   • اگر می‌خواهید داده‌ها را از یک پایگاه داده موجود به نسخه‌ی پشتیبان منتقل کنید، باید یک Snapshot از پایگاه داده اصلی بگیرید و آن را به سرور نسخه‌ی پشتیبان کپی کنید.
   • برای این کار، بهتر است کلیدهای SSH را روی سرور اصلی تنظیم کنید و سپس مطمئن شوید که کلید عمومی سرور اصلی به سرور نسخه‌ی پشتیبان کپی شده است.

به طور خلاصه، این راهنما به شما کمک می‌کند تا یک پایگاه داده MySQL را روی دو سرور تنظیم کنید، یکی به عنوان سرور اصلی و دیگری به عنوان نسخه‌ی پشتیبان، و در صورت نیاز داده‌ها را بین آن‌ها منتقل کنید. این اقدام مشکلات را برطرف کرده و همگام‌سازی را دقیق‌تر و مدیریت‌پذیرتر می‌کند.

به خاطر داشته باشید که این فقط یک توضیح کلی از نحوه عملکرد replication در MySQL است؛ MySQL گزینه‌های زیادی را برای بهینه‌سازی تنظیمات همگام‌سازی شما ارائه می‌دهد. این راهنما نحوه تنظیم همگام‌سازی مبتنی بر موقعیت فایل لاگ باینری را توضیح می‌دهد.

مستندات MySQL و بسیاری از دستورات در نسخه 8 این برنامه با اصطلاحات «منبع (source)» و «نسخه (replicas)» شناخته می‌شوند.

ایجاد Replication MySQL

مرحله 1. تنظیم فایروال سرور اصلی

اگر تنظیم سرور را انجام داده باشید، باید فایروالی با استفاده از UFW بر روی هر دو سرور خود پیکربندی کرده باشید. این فایروال به امنیت هر دو سرور کمک می‌کند، اما فایروال سرور اصلی، هرگونه تلاش برای اتصال از سمت نسخه MySQL شما را مسدود می‌کند.

برای تغییر این وضعیت، شما باید یک قانون به UFW اضافه کنید که اجازه دهد اتصالات از replicas به فایروال سرور اصلی برقرار شود. برای این کار، می‌توانید دستوری مشابه دستور زیر را روی سرور اصلی خود اجرا کنید.

sudo ufw allow from replica_server_ip to any port 3306

این دستور به‌طور خاص اجازه می‌دهد که هر اتصالی که از آدرس IP سرور نسخه (که با replica_server_ip نمایش داده می‌شود) به پورت پیش‌فرض MySQL (یعنی 3306) برقرار شود.

replica_server_ip را با آدرس IP واقعی سرور replicas خود جایگزین کنید. اگر قانون به‌درستی اضافه شده باشد، پیام زیر را خواهید دید:

Output
Rule added

پس از این مرحله، نیازی به تغییر قوانین فایروال سرور replicas نیست، زیرا این سرور هیچ اتصالی را دریافت نمی‌کند و اتصالات خروجی به سرور MySQL اصلی نیز توسط UFW مسدود نمی‌شوند. حالا می‌توانید به روزرسانی تنظیمات سرور MySQL اصلی برای فعال‌سازی replication داده‌ها بپردازید.

مرحله 2. پیکربندی پایگاه داده اصلی

برای اینکه پایگاه داده MySQL اصلی شما شروع به replication داده‌ها کند، باید چند تغییر در تنظیمات آن ایجاد کنید. در اوبونتو 20.04، فایل پیکربندی پیش‌فرض سرور MySQL به نام mysqld.cnf است و در دایرکتوری /etc/mysql/mysql.conf.d/ قرار دارد. این فایل را با ویرایشگر متن مورد علاقه‌تان روی سرور اصلی باز کنید. در اینجا، ما از ویرایشگر nano استفاده خواهیم کرد.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

در داخل این فایل، به دنبال دستور bind-address بگردید. به‌طور پیش‌فرض، این دستور به شکل زیر خواهد بود:

. . .
bind-address            = 127.0.0.1
. . .

آدرس 127.0.0.1 یک آدرس خاص در شبکه‌های کامپیوتری است که به آن لوپ‌بک (Loopback) می‌گویند. این آدرس به معنی “همان کامپیوتری که روی آن کار می‌کنید” یا به اصطلاح localhost است. اگر در تنظیمات MySQL، آدرس bind-address را روی 127.0.0.1 قرار دهید، MySQL فقط به درخواست‌هایی پاسخ می‌دهد که از همان سرور (کامپیوتر) خودش ارسال شده‌اند. یعنی فقط برنامه‌هایی که روی همان سرور نصب شده‌اند می‌توانند به MySQL وصل شوند.

فرض کنید شما می‌خواهید یک سرور دیگر (مثلاً یک سرور replica) به این MySQL وصل شود تا داده‌های جدید را از آن دریافت کند. اگر MySQL فقط به دستورات 127.0.0.1 گوش دهد، سرور replica نمی‌تواند به آن وصل شود، زیرا replica روی یک سرور دیگر است و نه روی همان سرور اصلی.

برای اینکه سرور replica بتواند به MySQL اصلی وصل شود، باید MySQL اصلی را طوری تنظیم کنید که به درخواست‌های خارجی (از سرورهای دیگر) هم پاسخ دهد. پس به جای 127.0.0.1، باید آدرس IP سرور اصلی را در تنظیمات bind-address قرار دهید. این آدرس IP می‌تواند آدرس داخلی شبکه (مثلاً 192.168.1.100) یا آدرس عمومی سرور (اگر replica در شبکه دیگری است) باشد.

پس از این تغییر، تنظیمات bind-address به شکل زیر خواهد بود:

. . .
bind-address            = source_server_ip
. . .

server-id یک تنظیم مهم در MySQL است که به هر سرور یک شماره شناسه منحصر به فرد اختصاص می‌دهد. این شناسه به MySQL کمک می‌کند تا سرورهای مختلف را در یک سیستم replication (همگام‌سازی داده‌ها بین سرورها) از هم تشخیص دهد. هر سرور، چه اصلی و چه replica، باید یک server-id منحصر به فرد داشته باشد. این کار باعث می‌شود MySQL بتواند سرورها را از هم تشخیص دهد و داده‌ها را به درستی همگام‌سازی کند.

به طور پیش‌فرض، این دستور در تنظیمات MySQL غیرفعال است. یعنی اگر به فایل تنظیمات MySQL نگاه کنید، احتمالاً خطی شبیه به این را می‌بینید:

. . .
# server-id             = 1
. . .

علامت # در ابتدای خط نشان می‌دهد که این دستور غیرفعال است و MySQL از آن استفاده نمی‌کند. برای فعال‌کردن server-id، باید خط مربوطه را در فایل تنظیمات MySQL پیدا کنید و علامت # را از ابتدای آن حذف کنید.

. . .
server-id               = 1
. . .

دقت کنید که هر سرور MySQL در سیستم replication باید یک server-id متفاوت داشته باشد. مثلاً سرور اصلی می‌تواند server-id = 1 داشته باشد و سرور replica می‌تواند server-id = 2 باشد.

log_bin یک تنظیم مهم در MySQL است که مشخص می‌کند فایل‌های لاگ باینری (Binary Log) کجا ذخیره شوند و چه نامی داشته باشند. در سیستم‌های replication در MySQL، سرور replica باید فایل‌های لاگ باینری سرور اصلی را بخواند تا بفهمد چه تغییراتی در داده‌ها رخ داده است و آن‌ها را روی خودش اعمال کند. اگر log_bin غیرفعال باشد، سرور اصلی هیچ لاگی ثبت نمی‌کند و در نتیجه سرور replica نمی‌تواند داده‌ها را همگام‌سازی کند. به طور پیش‌فرض، این دستور در تنظیمات MySQL غیرفعال است.

برای فعال‌کردن log_bin، باید خط مربوطه را در فایل تنظیمات MySQL پیدا کنید و علامت # را از ابتدای آن حذف کنید. پس از فعال‌سازی، خط به این شکل درمی‌آید:

. . .
log_bin                       = /var/log/mysql/mysql-bin.log
. . .

این تنظیم مشخص می‌کند که فایل‌های لاگ باینری در مسیر /var/log/mysql/ ذخیره شوند و نام آن‌ها با mysql-bin شروع شود.

در نهایت، به انتهای فایل بروید تا دستور binlog_do_db که غیرفعال شده است (با علامت # شروع می‌شود) را پیدا کنید. این دستور به MySQL می‌گوید که فقط تغییرات مربوط به پایگاه داده‌های مشخص‌شده را در فایل‌های لاگ باینری ثبت کند.

. . .
# binlog_do_db          = include_database_name

به جای include_database_name، نام پایگاه داده‌ای که می‌خواهید replication شود را بنویسید.

نکته: اگر می‌خواهید در MySQL بیش از یک پایگاه داده را replication کنید، می‌توانید برای هر پایگاه داده‌ای که می‌خواهید اضافه کنید، یک دستور binlog_do_db دیگر اضافه کنید. این آموزش فقط بر روی همگام‌سازی یک پایگاه داده تمرکز دارد، اما اگر بخواهید چند پایگاه داده را همگام‌سازی کنید، به این شکل خواهد بود:

. . .
binlog_do_db          = db
binlog_do_db          = db_1
binlog_do_db          = db_2

به‌طور متناوب، می‌توانید مشخص کنید که کدام پایگاه داده‌ها نباید توسط MySQL همگام سازی یا replication شوند. برای این کار، کافی است برای هر پایگاه داده‌ای که نمی‌خواهید همگام‌سازی شود، یک دستور binlog_ignore_db اضافه کنید.

. . .
binlog_ignore_db          = db_to_ignore

پس از انجام این تغییرات، فایل را ذخیره کرده و ببندید. سپس، سرویس MySQL را با استفاده از دستور زیر دوباره راه‌اندازی کنید:

sudo systemctl restart mysql

با این کار، این نمونه MySQL آماده است تا به عنوان پایگاه داده منبع عمل کند و سرور MySQL دیگری آن را replication کند. اما قبل از اینکه بتوانید تنظیمات مربوط به همگام‌سازی را انجام دهید، هنوز چند مرحله دیگر باید روی منبع انجام دهید تا مطمئن شوید که سیستم همگام‌سازی به درستی کار می‌کند. اولین مرحله این است که یک کاربر مخصوص MySQL بسازید که تمام کارهای مربوط به فرآیند همگام‌سازی را انجام دهد.

مرحله 3. ایجاد یک کاربر replication

در هر محیط replication در MySQL، هر کپی (replica) با یک نام کاربری و رمز عبور به پایگاه داده منبع متصل می‌شود. کپی‌ها می‌توانند با هر پروفایل کاربری MySQL موجود در پایگاه داده منبع که دسترسی‌های لازم را دارد، متصل شوند. اما در این بخش، نحوه ایجاد یک کاربر مخصوص برای این کار توضیح داده خواهد شد.

برای شروع، وارد محیط MySQL شوید:

source:~$ sudo mysql

نکته: اگر یک کاربر مخصوص MySQL ایجاد کرده‌اید که با استفاده از رمز عبور وارد می‌شود، می‌توانید با استفاده از دستوری مانند این، به MySQL خود متصل شوید:

source:~$ mysql -u sammy -p

نام “sammy” را با نام کاربر مخصوص خود جایگزین کنید و رمز عبور این کاربر را زمانی که از شما خواسته شد، وارد کنید.

به یاد داشته باشید که برخی از عملیات در این راهنما، از جمله چند مورد که باید روی سرور کپی (replica) انجام شوند، نیاز به دسترسی‌های بالاتر دارند. به همین دلیل، ممکن است راحت‌تر باشد که به عنوان یک کاربر با دسترسی های مدیریتی وارد شوید، مانند دستوری که قبلاً با sudo mysql استفاده کردید. اما اگر می‌خواهید در طول این راهنما از یک کاربر MySQL با دسترسی کمتر استفاده کنید، این کاربر باید حداقل دسترسی‌های CREATE USER، RELOAD، REPLICATION CLIENT، REPLICATION SLAVE و REPLICATION_SLAVE_ADMIN را داشته باشد.

از طریق خط فرمان، یک کاربر جدید MySQL بسازید. در مثال زیر، نام کاربری که ایجاد می‌شود “replica_user” است، اما می‌توانید نام دلخواه خود را انتخاب کنید. حتماً “replica_server_ip” را به آدرس IP عمومی سرور کپی (replica) خود تغییر دهید و “password” را به یک رمز عبور قوی که خودتان انتخاب می‌کنید، تغییر دهید.

mysql> CREATE USER 'replica_user'@'replica_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

به یاد داشته باشید که این دستور مشخص می‌کند که کاربر “replica_user” از افزونه احراز هویت “mysql_native_password” استفاده خواهد کرد. می‌توانید به جای آن از مکانیزم پیش‌فرض احراز هویت MySQL، یعنی “caching_sha2_password” استفاده کنید، اما این کار نیاز به برقراری یک ارتباط رمزگذاری شده بین منبع و کپی دارد.

پس از ایجاد کاربر جدید، به او دسترسی‌های مناسب را بدهید. حداقل، یک کاربر رپلیکا MySQL باید مجوزهای REPLICATION SLAVE را داشته باشد.

GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'replica_server_ip';

بعد از این کار، بهتر است دستور “FLUSH PRIVILEGES” را اجرا کنید. این دستور حافظه‌ای که سرور برای دستورات قبلی “CREATE USER” و “GRANT” ذخیره کرده بود را آزاد می‌کند.

mysql> FLUSH PRIVILEGES;

با این کار، شما راه‌اندازی یک کاربر replication را در نمونه MySQL منبع خود به پایان رسانده‌اید. اما هنوز از محیط MySQL خارج نشوید. آن را باز نگه دارید، زیرا در مرحله بعدی برای به‌دست آوردن اطلاعات مهمی درباره فایل لاگ باینری (binary log file) پایگاه داده منبع به آن نیاز دارید.

مرحله 4. به‌دست آوردن مختصات لاگ باینری از منبع

به یاد داشته باشید که MySQL با کپی کردن رویدادهای پایگاه داده از فایل لاگ باینری منبع، replication را انجام می‌دهد. برای اینکه مطمئن شوید هنگام گرفتن مختصات فایل Binary Log، هیچ کاربری داده‌ها را تغییر نمی‌دهد، باید پایگاه داده را قفل کنید. قفل کردن باعث می‌شود کاربران نتوانند داده‌ها را بخوانند یا بنویسند و این کار باعث ایجاد زمان خاموشی (Downtime) می‌شود. البته این خاموشی موقتی است و پس از انجام مراحل، پایگاه داده دوباره باز می‌شود.

برای قفل کردن پایگاه داده، از خط فرمان MySQL دستور خاصی اجرا می‌شود که تمام جدول‌های باز را می‌بندد و آن‌ها را قفل می‌کند. این کار باعث می‌شود هیچ تغییری در داده‌ها ایجاد نشود تا مختصات فایل Binary Log به درستی گرفته شود.

شما باید هنوز هم محیط MySQL سرور منبع خود را از پایان مرحله قبلی باز داشته باشید. از خط فرمان، دستور زیر را اجرا کنید که تمام جدول‌های باز در هر پایگاه داده روی نمونه منبع شما را می‌بندد و آن‌ها را قفل می‌کند:

mysql> FLUSH TABLES WITH READ LOCK;

سپس دستور زیر را اجرا کنید تا اطلاعات وضعیت فعلی فایل‌های لاگ باینری منبع را به شما نشان دهد:

SHOW MASTER STATUS;

شما در خروجی خود جدولی شبیه به این مثال را خواهید دید:

Output
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      899 | db           |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

بعد از یادداشت کردن این اطلاعات، باید تصمیم بگیرید که آیا پایگاه داده اصلی (منبع) داده‌هایی دارد که می‌خواهید به پایگاه داده رپلیکا منتقل کنید یا خیر. اگر داده‌هایی دارید که باید به رپلیکا منتقل شوند، مراحل خاصی را باید انجام دهید. اگر داده‌های موجود مهم نیستند یا پایگاه داده اصلی خالی است، می‌توانید مستقیماً به مرحله بعد بروید.

اگر پایگاه داده منبع شما داده‌هایی برای انتقال ندارد:

اگر نمونه MySQL منبع شما به تازگی نصب شده است یا داده‌ای ندارد که بخواهید به کپی‌ها منتقل کنید، می‌توانید در این مرحله جدول‌ها را به کمک دستور زیر باز کنید:

mysql> UNLOCK TABLES;

اگر هنوز این کار را نکرده‌اید، می‌توانید در حالی که محیط MySQL باز است، پایگاه داده‌ای که می‌خواهید replication کنید را بسازید. همانطور که در مثال مرحله 2 گفته شد، با استفاده از دستور زیر می‌توانید یک پایگاه داده به نام “db” ایجاد کنید:

CREATE DATABASE db;

Output
Query OK, 1 row affected (0.01 sec)

بعد از آن، محیط MySQL را ببندید:

mysql> exit

پس از آن، می‌توانید به مرحله بعدی بروید.

اگر پایگاه داده شما داده‌هایی برای انتقال دارد:

اگر در پایگاه داده اصلی شما داده‌هایی وجود دارد که می‌خواهید به پایگاه داده رپلیکا منتقل کنید، باید از آن داده‌ها یک Snapshot بگیرید. این کار با استفاده از ابزار mysqldump انجام می‌شود. این ابزار تمام داده‌ها و ساختار پایگاه داده را در یک فایل ذخیره می‌کند که بعداً می‌توانید آن را به دیتابیس کپی منتقل کنید.

برای اینکه مطمئن شوید داده‌ها در حین ایجاد Snapshot تغییر نمی‌کنند، پایگاه داده باید قفل باشد. این قفل‌کردن باعث می‌شود کاربران نتوانند داده‌ها را تغییر دهند. اگر در همان پنجره‌ای که پایگاه داده را قفل کرده‌اید، تغییراتی ایجاد کنید یا از محیط خارج شوید، قفل به‌طور خودکار باز می‌شود. این اتفاق می‌تواند مشکلاتی ایجاد کند، زیرا کاربران دیگر می‌توانند داده‌ها را تغییر دهند و این باعث می‌شود Snapshot شما با داده‌های واقعی پایگاه داده هماهنگ نباشد.

برای جلوگیری از باز شدن قفل پایگاه داده، باید یک پنجره یا تب جدید در دستگاه خود باز کنید. این کار به شما این امکان را می‌دهد که بدون باز کردن قفل پایگاه داده، Snapshot ایجاد کنید. از طریق این پنجره جدید، یک اتصال SSH به سروری که پایگاه داده اصلی (منبع) روی آن قرار دارد، برقرار کنید. در این اتصال SSH، از ابزار mysqldump برای ایجاد Snapshot از پایگاه داده استفاده کنید.

از پنجره یا تب جدید، یک SSH session جدید به سرور میزبان نمونه MySQL منبع خود باز کنید:

ssh sammy@source_server_ip

سپس، از پنجره یا تب جدید، پایگاه داده خود را با استفاده از ابزار mysqldump اکسپورت کنید. در مثال زیر، یک فایل کپی به نام db.sql از پایگاه داده‌ای به نام db ایجاد می‌شود، اما حتماً نام پایگاه داده خود را به جای آن وارد کنید. همچنین، توجه داشته باشید که این دستور را باید در محیط bash اجرا کنید، نه در محیط MySQL:

source:~$ sudo mysqldump -u root db > db.sql

بعد از این مرحله، می‌توانید این پنجره یا تب ترمینال را ببندید و به پنجره اول برگردید که باید هنوز محیط MySQL باز باشد. از قسمت فرمان MySQL، پایگاه داده‌ها را باز کنید تا دوباره قابل نوشتن شوند:

UNLOCK TABLES;

سپس می‌توانید از محیط MySQL خارج شوید.

حالا می‌توانید فایل Snapshot خود را به سرور کپی (Replica) ارسال کنید. فرض کنید که کلیدهای SSH را روی سرور منبع (Source) تنظیم کرده‌اید و کلید عمومی سرور منبع را به فایل authorized_keys سرور کپی اضافه کرده‌اید. در این صورت، می‌توانید به‌طور امن این کار را با استفاده از دستور scp انجام دهید:

scp db.sql sammy@replica_server_ip:/tmp/

مطمئن شوید که به جای “sammy” نام کاربری مدیریتی که روی سرور کپی (Replica) ایجاد کرده‌اید را وارد کنید و به جای “replica_server_ip” آدرس IP سرور کپی را بنویسید. همچنین توجه داشته باشید که این دستور، Snapshot را در پوشه /tmp/ سرور کپی قرار می‌دهد.

پس از ارسال Snapshot به سرور کپی، به آن سرور وارد شوید:

ssh sammy@replica_server_ip

سپس با دستور زیر محیط MySQL را باز کنید:

replica:~$ sudo mysql

از قسمت فرمان، به کمک دستور زیر پایگاه داده جدیدی بسازید که قرار است از سرور منبع (Source) کپی شود:

mysql> CREATE DATABASE db;

نیازی نیست که جدول‌هایی بسازید یا این پایگاه داده را با داده‌های نمونه پر کنید. همه این کارها وقتی که پایگاه داده را با استفاده از Snapshot که تازه ایجاد کرده‌اید، وارد می‌کنید، انجام می‌شود. در عوض، از محیط MySQL خارج شوید. سپس Snapshot پایگاه داده را وارد کنید:

replica:~$ sudo mysql db < /tmp/db.sql

اکنون سرور کپی (Replica) شما تمام داده‌های موجود از پایگاه داده منبع (Source) را دارد. می‌توانید آخرین مرحله این راهنما را انجام دهید تا سرور Replica خود را برای شروع همگام سازی تغییرات جدیدی که بر روی پایگاه داده منبع انجام می‌شود، پیکربندی کنید.

مرحله 5. پیکربندی پایگاه داده کپی

در این مرحله، شما باید تنظیمات پایگاه داده کپی (Replica) را به‌روزرسانی کنید تا با پایگاه داده منبع (Source) همگام شود. این کار شامل تغییر فایل تنظیمات MySQL به نام mysqld.cnf روی سرور کپی است.

1. باز کردن فایل تنظیمات MySQL روی سرور کپی:
   • ابتدا فایل تنظیمات MySQL را روی سرور کپی باز کنید. این فایل معمولاً در مسیر /etc/mysql/mysql.conf.d/mysqld.cnf قرار دارد.
   • برای باز کردن فایل، از دستور زیر استفاده کنید:

replica:~$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

1. تنظیم server-id منحصر به فرد:
   • هر نمونه MySQL در یک تنظیمات کپی‌برداری باید یک server-id منحصر به فرد داشته باشد. این مقدار باید با server-id پایگاه داده منبع متفاوت باشد.
   • در فایل تنظیمات، به دنبال خط server-id بگردید، آن را از حالت کامنت خارج کنید و مقدار آن را به یک عدد صحیح مثبت تغییر دهید. مثلاً:

server-id               = 2

1. به‌روزرسانی log_bin و binlog_do_db:
   • مقادیر log_bin و binlog_do_db را به‌روزرسانی کنید تا با مقادیری که در فایل تنظیمات پایگاه داده منبع تعیین کرده‌اید، همخوانی داشته باشد.
   • این مقادیر معمولاً به شکل زیر هستند:

log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = db

1. اضافه کردن relay-log:
   • یک دستور relay-log اضافه کنید تا محل فایل لاگ کپی (Replica) مشخص شود. این خط را در انتهای فایل تنظیمات اضافه کنید

relay-log = /var/log/mysql/mysql-relay-bin.log

1. ذخیره و بستن فایل تنظیمات:
   • پس از اعمال تغییرات، فایل را ذخیره کرده و ببندید.
2. راه‌اندازی مجدد سرویس MySQL:
   • برای اعمال تغییرات، سرویس MySQL را روی سرور کپی دوباره راه‌اندازی کنید:

sudo systemctl restart mysql

1. آماده‌سازی برای کپی‌برداری داده‌ها:
   • پس از راه‌اندازی مجدد سرویس MySQL، حالا پایگاه داده کپی آماده است تا داده‌ها را از پایگاه داده منبع کپی کند.

مرحله 6. شروع و تست

در این مرحله، هر دو نمونه MySQL شما به‌طور کامل برای Replication پیکربندی شده‌اند. برای شروع همگام سازی داده‌ها از منبع، به کمک دستور زیر به محیط MySQL روی سرور کپی بروید:

sudo mysql

از خط فرمان، دستور زیر را اجرا کنید که چندین تنظیم Replication در MySQL را به‌طور همزمان پیکربندی می‌کند. پس از اجرای این دستور، هنگامی که همگام سازی را در این نمونه فعال کنید، سعی می‌کند به آدرس IP که بعد از SOURCE_HOST آمده، با نام کاربری و رمز عبوری که بعد از SOURCE_USER و SOURCE_PASSWORD آمده، متصل شود. همچنین به دنبال یک فایل لاگ باینری با نام مشخص شده در SOURCE_LOG_FILE می‌گردد و از موقعیت مشخص شده در SOURCE_LOG_POS شروع به خواندن می‌کند.

مطمئن شوید که به‌جای source_server_ip، آدرس IP سرور منبع خود را وارد کنید. همچنین، replica_user و password باید با کاربر ریپلیکا که در مرحله 2 ایجاد کرده‌اید، مطابقت داشته باشد؛ و mysql-bin.000001 و 899 باید با مختصات لاگ باینری که در مرحله 3 به‌دست آورده‌اید، همخوانی داشته باشد.

شاید بخواهید این دستور را در یک ویرایشگر متن تایپ کنید قبل از اینکه آن را روی سرور کپی اجرا کنید تا بتوانید به‌راحتی همه اطلاعات مربوطه را جایگزین کنید.

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='source_server_ip',
SOURCE_USER='replica_user',
SOURCE_PASSWORD='password',
SOURCE_LOG_FILE='mysql-bin.000001',
SOURCE_LOG_POS=899;

پس از آن، سرور کپی (Replica) را فعال کنید:

START REPLICA;

اگر تمام جزئیات را به‌درستی وارد کرده باشید، این نمونه شروع به همگام سازی از هر تغییری که در پایگاه داده db روی سرور منبع انجام شود، خواهد کرد.

برای مشاهده جزئیات وضعیت فعلی سرور کپی، می‌توانید دستور زیر را اجرا کنید. استفاده از \G در این دستور باعث می‌شود متن به‌صورت خواناتری نمایش داده شود:

SHOW REPLICA STATUS\G;

این دستور اطلاعات زیادی را بازمی‌گرداند که می‌تواند در زمان عیب‌یابی مفید باشد:

Output
*************************** 1. row ***************************
             Replica_IO_State: Waiting for master to send event
                  Source_Host: 138.197.3.190
                  Source_User: replica_user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000001
          Read_Source_Log_Pos: 1273
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 729
        Relay_Source_Log_File: mysql-bin.000001
. . .

توجه: اگر سرور کپی شما در اتصال به سرور منبع مشکل داشته باشد یا همگام‌سازی به‌طور ناگهانی متوقف شود، ممکن است یک رویداد در فایل لاگ باینری سرور منبع مانع از همگام‌سازی شود. در این شرایط، می‌توانید از دستور SET GLOBAL SQL_SLAVE_SKIP_COUNTER استفاده کنید تا یک تعداد مشخصی از رویدادها را پس از موقعیت فایل لاگ باینری که در دستور قبلی تعریف کرده‌اید، نادیده بگیرید. به‌عنوان مثال، دستور زیر فقط اولین رویداد را نادیده می‌گیرد:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

بعد از این، باید دوباره سرور کپی (Replica) را راه‌اندازی کنید.

START REPLICA;

همچنین، اگر بخواهید همگام سازی را متوقف کنید، می‌توانید با اجرای دستور زیر بر روی سرور کپی این کار را انجام دهید:

STOP REPLICA;

حالا سرور کپی‌شده‌ی شما (نسخه‌ی پشتیبان) در حال همگام‌سازی با سرور اصلی است. این یعنی هر تغییری که در پایگاه داده‌ی اصلی ایجاد کنید، به‌طور خودکار در سرور کپی‌شده هم اعمال می‌شود.

برای اینکه مطمئن شوید همگام‌سازی به‌درستی کار می‌کند، می‌توانید یک جدول نمونه در پایگاه داده‌ی اصلی ایجاد کنید و سپس بررسی کنید که آیا این جدول در سرور کپی‌شده هم ظاهر شده است یا نه. اگر جدول در هر دو سرور وجود داشت، یعنی همگام‌سازی به‌درستی انجام شده است.

ابتدا با باز کردن MySQL shell بر روی ماشین منبع شروع کنید:

sudo mysql

با اجرا دستور زیر پایگاه داده‌ای را که انتخاب کرده‌اید تا همگام شود، انتخاب کنید:

USE db;

سپس یک جدول در آن پایگاه داده ایجاد کنید. دستور SQL زیر، جدولی به نام example_table با یک ستون به نام example_column ایجاد می‌کند:

CREATE TABLE example_table (
example_column varchar(30)
);

Output
Query OK, 0 rows affected (0.03 sec)

اگر دوست دارید، می‌توانید مقداری داده نمونه به این جدول اضافه کنید:

INSERT INTO example_table VALUES
('This is the first row'),
('This is the second row'),
('This is the third row');

Output
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

پس از ایجاد جدول و اضافه کردن داده‌های نمونه به‌صورت اختیاری، به MySQL shell سرور کپی برگردید و پایگاه داده همگام شده را انتخاب کنید:

USE db;

سپس دستور SHOW TABLES را اجرا کنید تا تمامی جداول موجود در پایگاه داده انتخاب شده را لیست کنید:

SHOW TABLES;

اگر همگام سازی به‌درستی کار کند، جدول جدیدی که به منبع اضافه کرده‌اید، در خروجی این دستور نمایش داده خواهد شد:

Output
+---------------+
| Tables_in_db  |
+---------------+
| example_table |
+---------------+
1 row in set (0.00 sec)

همچنین، اگر مقداری داده نمونه به جدول در منبع اضافه کرده‌اید، می‌توانید با استفاده از کوئری زیر بررسی کنید که آیا آن داده‌ها نیز کپی‌برداری شده‌اند یا خیر:

SELECT * FROM example_table;

در SQL، علامت ستاره (*) به‌عنوان اختصاری برای “همه ستون‌ها” استفاده می‌شود. بنابراین، این کوئری به‌طور اساسی به MySQL می‌گوید که هر ستونی را از example_table برگرداند. اگر همگام سازی به‌درستی کار کند، این عملیات داده‌ها را در خروجی خود برمی‌گرداند:

SELECT * FROM example_table;

این کوئری تمامی داده‌های موجود در جدول example_table را نمایش می‌دهد. اگر داده‌های نمونه‌ای که قبلاً اضافه کرده‌اید به درستی همگام سازی شده باشند، باید در نتیجه این کوئری قابل مشاهده باشند.

Output
+------------------------+
| example_column         |
+------------------------+
| This is the first row  |
| This is the second row |
| This is the third row  |
+------------------------+
3 rows in set (0.00 sec)

اگر بعد از انجام مراحل، جدول نمونه یا داده‌هایی که به پایگاه داده‌ اصلی اضافه کرده‌اید، در سرور همگام شده نمایش داده نشوند، ممکن است مشکلی در پیکربندی همگام‌سازی (Replication) وجود داشته باشد.

برای تشخیص مشکل، می‌توانید از دستور SHOW REPLICA STATUS\G استفاده کنید. این دستور اطلاعاتی درباره‌ی وضعیت همگام سازی نشان می‌دهد و به شما کمک می‌کند تا علت خطا را پیدا کنید.

همچنین، می‌توانید از خدمات پشتیبانی دیتابیس استفاده کنید و به داکیومنت عیب یابی replication سایت MySQL مراجعه کنید. در این مستندات، راهنمایی‌ها و پیشنهاداتی برای رفع مشکلات replication وجود دارد که می‌توانند به شما کمک کنند.

نتیجه‌گیری

با دنبال کردن مراحل این آموزش، شما یک سیستم Replication در MySQL راه‌اندازی کرده‌اید که از روش همگام‌سازی مبتنی بر موقعیت فایل لاگ باینری استفاده می‌کند. در این سیستم، یک سرور به عنوان منبع (Source) و یک سرور دیگر به عنوان کپی (Replica) عمل می‌کند.

اما توجه داشته باشید که این روش فقط یکی از راه‌های ممکن برای پیکربندی همگام سازی در MySQL است. MySQL گزینه‌های دیگری هم برای همگام سازی ارائه می‌دهد که می‌توانید بسته به نیازهای خود از آن‌ها استفاده کنید تا یک سیستم همگام‌سازی بهینه‌تر ایجاد کنید.

علاوه بر این، ابزارهای دیگری مانند Galera Cluster نیز وجود دارند که می‌توانند ویژگی‌های همگام‌سازی داخلی MySQL را گسترش دهند و قابلیت‌های بیشتری به شما بدهند.

منبع: digitalocean.com