{"id":2470,"date":"2022-04-24T12:49:28","date_gmt":"2022-04-24T16:49:28","guid":{"rendered":"https:\/\/cyburdial.net\/?p=2470"},"modified":"2025-03-09T12:10:41","modified_gmt":"2025-03-09T17:10:41","slug":"how-to-setup-a-slave-db-for-a-scratch-installed-vicidial","status":"publish","type":"post","link":"https:\/\/dialer.one\/index.php\/how-to-setup-a-slave-db-for-a-scratch-installed-vicidial\/","title":{"rendered":"How to – Setup a slave DB for a scratch installed ViciDial"},"content":{"rendered":"\n

How to – Setup a slave DB for a scratch installed ViciDial<\/h1>\n\n\n\n

This Article will go over how to setup a slave database server for your ViciDial cluster when using a scratch installed server bunch. For this particular example I have used Alma Linux 9.5<\/a> which installs Mysql 10.3.28, if you followed my scratch instructions. This process was a pain in the arse, but after much trial and error I was able to get it done and here is how I did it:<\/p>\n\n\n\n

Step 1 – Edit the MySQL config file<\/h2>\n\n\n\n

Add these lines to \/etc\/my.cnf below [mysqld] on both the master and slave database:<\/p>\n\n\n\n

log_bin=\/var\/lib\/mysql\/mysql-bin\nrelay_log=\/var\/lib\/mysql\/mysql-relay-bin\nbinlog_format=mixed\nbinlog_direct_non_transactional_updates=1\ndatadir = \/srv\/mysql\/data\nlog-bin=mysql-bin\nserver-id       = 1\nslave-skip-errors = 1032,1690,1062\nreplicate-ignore-db=mysql\nreplicate-do-db=asterisk\nslave_parallel_threads=40\nslave-parallel-mode=optimistic\nslave_parallel_max_queued=5242880\n<\/code><\/pre>\n\n\n\n
Step 2 – Create the slave user on both servers<\/h2>\n\n\n\n
Go into MySQL and create the slave user and do this on both servers<\/p>\n\n\n\n
CREATE USER 'slave'@'localhost' IDENTIFIED BY 'slave1234';\nCREATE USER 'slave'@'%' IDENTIFIED BY 'slave1234';\nGRANT SELECT, CREATE, INSERT, UPDATE, DELETE, ALTER, DROP, INDEX on asterisk.* TO slave@'%' IDENTIFIED BY 'slave1234';\nGRANT SELECT, CREATE, INSERT, UPDATE, DELETE, ALTER, DROP, INDEX on asterisk.* TO slave@localhost IDENTIFIED BY 'slave1234';\ngrant replication slave on . to slave@'%' identified by 'slave1234';\ngrant replication slave on . to slave@'localhost' identified by 'slave1234';\nflush privileges;<\/code><\/pre>\n\n\n
\n
\"\"
Create slave user on both the master and slave DB<\/figcaption><\/figure>\n<\/div>\n\n\n
Step 3 – Change some setting on the slave DB<\/h2>\n\n\n\n
Now we need to change some of the settings for the slave DB and add a couple more lines. Change the server-id to 2 instead of 1 in \/etc\/my.cnf and add the read_only=1 and report-host=whatever the slave DB host is<\/p>\n\n\n\n
server-id=2\n# read only yes\nread_only=1\n# define own hostname\nreport-host=slavedb.yourserver.com<\/code><\/pre>\n\n\n\n
Step 4 – Restart the MySQL services on both servers<\/h2>\n\n\n\n
Run the following command on both servers:<\/p>\n\n\n\n
service mysql restart<\/code><\/pre>\n\n\n\n
Step 5 – Create the MySQL dump to send over to the slave DB from the master<\/h2>\n\n\n\n
We need to create the MySQL dump and send it over to the slave using the scp command like so:<\/p>\n\n\n\n
mkdir \/home\/mariadb_backup\nmariabackup --backup --target-dir \/home\/mariadb_backup -u root<\/code><\/pre>\n\n\n
\n
\"\"
Create MySQL dump file<\/figcaption><\/figure>\n<\/div>\n\n\n
Once thats done go ahead and zip up the files and send the file over by typing the following:<\/p>\n\n\n\n
cd \/home\/mariadb_backup\nzip -r mysqldump.zip *\nscp mysqldump.zip 192.168.1.10:\/root\/\n<\/code><\/pre>\n\n\n\n
Why we are here, lets lock the MySQL tables on the master:<\/p>\n\n\n\n
mysql\nFLUSH TABLES WITH READ LOCK;\nexit<\/code><\/pre>\n\n\n\n
Step 6 – Connect to Slave DB and extract the files and load it into the slave<\/h2>\n\n\n\n
ssh into your slave db server and change directory to \/root then create the folder and extract the files like so:<\/p>\n\n\n\n
cd \/root\nmkdir mariadb_backup\nmv mysqldump.zip mariadb_backup\/\ncd mariadb_backup\/\nunzip mysqldump.zip\nsystemctl stop mariadb\nrm -rf \/var\/lib\/mysql\/*<\/code><\/pre>\n\n\n
\n
\"\"<\/figure>\n<\/div>\n\n\n
Now run the following commands that are BOLD <\/strong>to import the MySQL dump and set its position to match that of the master.<\/p>\n\n\n\n
# run prepare task before restore task (OK if [completed OK])\n[root@node01 ~]# mariabackup --prepare --target-dir \/root\/mariadb_backup<\/strong>\nmariabackup based on MariaDB server 10.3.28-MariaDB Linux (x86_64)\nmariabackup: cd to \/root\/mariadb_backup\/\n.....\n.....\n2019-11-29 19:38:20 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=1630833\nLast binlog file , position 0\n191129 19:38:21 completed OK!\n\n# run restore\n[root@node01 ~]# mariabackup --copy-back --target-dir \/root\/mariadb_backup<\/strong>\nmariabackup based on MariaDB server 10.3.28-MariaDB Linux (x86_64)\n191129 19:39:21 [01] Copying ibdata1 to \/var\/lib\/mysql\/ibdata1\n191129 19:39:21 [01]        ...done\n.....\n.....\n191129 19:39:21 [01] Copying .\/xtrabackup_binlog_pos_innodb to \/var\/lib\/mysql\/xtrabackup_binlog_pos_innodb\n191129 19:39:21 [01]        ...done\n191129 19:39:21 completed OK!\n\n[root@node01 ~]# chown -R mysql. \/var\/lib\/mysql<\/strong>\n[root@node01 ~]# systemctl start mariadb<\/strong>\n# confirm [File] and [Position] value of master log\n[root@node01 ~]# cat \/root\/mariadb_backup\/xtrabackup_binlog_info\nmysql-bin.000001        642     0-101-2\n\n# set replication\n[root@node01 ~]# mysql -u root -p<\/strong>\nEnter password:\nWelcome to the MariaDB monitor.  Commands end with ; or \\g.\nYour MariaDB connection id is 10\nServer version: 10.3.28-MariaDB-log MariaDB Server\n\nCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nMariaDB [(none)]> change master to<\/strong> \n    -> master_host='10.0.0.31',<\/strong>             # Master Host IP address\n    -> master_user='repl_user',<\/strong>             # replication user\n    -> master_password='password',<\/strong>          # replication user password\n    -> master_log_file='mysql-bin.000001',<\/strong>  # [File] value confirmed above\n    -> master_log_pos=642;<\/strong>              # [Position] value confirmed above\nQuery OK, 0 rows affected (0.58 sec)\n\n# start replication\nMariaDB [(none)]> start slave; <\/strong>\nQuery OK, 0 rows affected (0.00 sec)\n\n# show status\nMariaDB [(none)]> show slave status\\G<\/strong> \n*************************** 1. row ***************************\n                Slave_IO_State: Waiting for master to send event\n                   Master_Host: 10.0.0.31\n                   Master_User: repl_user\n                   Master_Port: 3306\n                 Connect_Retry: 60\n               Master_Log_File: mysql-bin.000001\n           Read_Master_Log_Pos: 642\n                Relay_Log_File: mariadb-relay-bin.000002\n                 Relay_Log_Pos: 555\n         Relay_Master_Log_File: mysql-bin.000001\n              Slave_IO_Running: Yes\n             Slave_SQL_Running: Yes\n               Replicate_Do_DB:\n           Replicate_Ignore_DB:\n            Replicate_Do_Table:\n        Replicate_Ignore_Table:\n       Replicate_Wild_Do_Table:\n   Replicate_Wild_Ignore_Table:\n                    Last_Errno: 0\n                    Last_Error:\n                  Skip_Counter: 0\n           Exec_Master_Log_Pos: 642\n               Relay_Log_Space: 866\n               Until_Condition: None\n                Until_Log_File:\n                 Until_Log_Pos: 0\n            Master_SSL_Allowed: No\n            Master_SSL_CA_File:\n            Master_SSL_CA_Path:\n               Master_SSL_Cert:\n             Master_SSL_Cipher:\n                Master_SSL_Key:\n         Seconds_Behind_Master: 0\n Master_SSL_Verify_Server_Cert: No\n                 Last_IO_Errno: 0\n                 Last_IO_Error:\n                Last_SQL_Errno: 0\n                Last_SQL_Error:\n   Replicate_Ignore_Server_Ids:\n              Master_Server_Id: 101\n                Master_SSL_Crl:\n            Master_SSL_Crlpath:\n                    Using_Gtid: No\n                   Gtid_IO_Pos:\n       Replicate_Do_Domain_Ids:\n   Replicate_Ignore_Domain_Ids:\n                 Parallel_Mode: conservative\n                     SQL_Delay: 0\n           SQL_Remaining_Delay: NULL\n       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I\/O thread to update it\n              Slave_DDL_Groups: 0\nSlave_Non_Transactional_Groups: 0\n    Slave_Transactional_Groups: 0\n1 row in set (0.000 sec)<\/code><\/pre>\n\n\n\n
Step 7 – Unlock the Master DB<\/h2>\n\n\n\n
Now go back into the master DB and unlock the tables:<\/p>\n\n\n\n
mysql
unlock tables;<\/p>\n\n\n\n
Step 8 – Set the Slave DB in ViciDIal<\/h2>\n\n\n\n
Now you can set the slave server info in ViciDial by going into the admin GUI and going into system settings. Set the slave DB server IP and choose which reports you want to display from it.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Well, this has been quite the nightmare to get it done and hopefully I can save some of you some time with this article. If you have any problems feel free to join our Discord Live Support Channel<\/a> and someone will be willing to help.<\/p>\n\n\n\n
-Chris aka carpenox<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
How to – Setup a slave DB for a scratch installed ViciDial This Article will go over how to setup a slave database server for your ViciDial cluster when using a scratch installed server bunch. For this particular example I have used Alma Linux 9.5 which installs Mysql 10.3.28, if you followed my scratch instructions. […]<\/p>\n","protected":false},"author":1,"featured_media":2475,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"content-type":"","_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[96],"tags":[533,537,536],"class_list":["post-2470","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-vicidial","tag-howto","tag-mysql","tag-vicidial"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/dialer.one\/wp-content\/uploads\/2022\/04\/image-11.png","jetpack-related-posts":[{"id":4086,"url":"https:\/\/dialer.one\/index.php\/how-to-setup-a-vicidial-cluster-with-scratch-installs\/","url_meta":{"origin":2470,"position":0},"title":"How to – Setup a ViciDial Cluster with scratch installs","author":"carpenox","date":"January 27, 2024","format":false,"excerpt":"How to - Setup a ViciDial Cluster with scratch installs This article is going to go over the steps I suggest to take in order to setup a ViciDial Cluster when using scratch install methods. I do things a little different when it comes to this to prepare for worst\u2026","rel":"","context":"In "CyburDial"","block_context":{"text":"CyburDial","link":"https:\/\/dialer.one\/index.php\/category\/cyburdial\/"},"img":{"alt_text":"Vicidial cluster","src":"https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2023\/12\/image-2.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2023\/12\/image-2.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2023\/12\/image-2.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2023\/12\/image-2.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":2394,"url":"https:\/\/dialer.one\/index.php\/how-to-setup-a-vicidial-cluster\/","url_meta":{"origin":2470,"position":1},"title":"How to – Setup a ViciDial Cluster","author":"carpenox","date":"March 12, 2022","format":false,"excerpt":"How to - Setup a ViciDial Cluster This article is going to go over the steps I suggest to take in order to setup a ViciDial Cluster. I do things a little different when it comes to this to prepare for worst case scenario that a server may have problems\u2026","rel":"","context":"In "CyburDial"","block_context":{"text":"CyburDial","link":"https:\/\/dialer.one\/index.php\/category\/cyburdial\/"},"img":{"alt_text":"ViciDial Cluster","src":"https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2022\/03\/image-4.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2022\/03\/image-4.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2022\/03\/image-4.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2022\/03\/image-4.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":2378,"url":"https:\/\/dialer.one\/index.php\/table-of-contents\/","url_meta":{"origin":2470,"position":2},"title":"Table of Contents – Knowledge Base","author":"carpenox","date":"March 7, 2022","format":false,"excerpt":"Welcome to my blog, some of you may know me from the ViciDial forums, I am carpenox and I am here to share my knowledge with everyone, for free!","rel":"","context":"In "Vicidial"","block_context":{"text":"Vicidial","link":"https:\/\/dialer.one\/index.php\/category\/vicidial\/"},"img":{"alt_text":"CyburDial","src":"https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2022\/02\/image.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2022\/02\/image.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2022\/02\/image.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2022\/02\/image.png?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2022\/02\/image.png?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":1504,"url":"https:\/\/dialer.one\/index.php\/how-to-backup-and-restore-your-vicidial-cyburdial-server\/","url_meta":{"origin":2470,"position":3},"title":"How to – Backup and Restore your Vicidial server","author":"carpenox","date":"February 4, 2021","format":false,"excerpt":"How to - Backup and Restore your Vicidial server Why would you need to backup and restore your vicidial server? Suppose you need to\u00a0migrate or duplicate Vicidial data\u00a0(campaigns, leads, phones and so on) from one\u00a0Vicibox\u00a0server to another. In case two\u00a0Vicidial\u00a0boxes of the same versions the process will be simpler. In\u2026","rel":"","context":"Similar post","block_context":{"text":"Similar post","link":""},"img":{"alt_text":"Cyburity - vicidial hosting","src":"https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2021\/02\/server1.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2021\/02\/server1.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2021\/02\/server1.jpg?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":1512,"url":"https:\/\/dialer.one\/index.php\/how-to-update-your-vicidial-version-with-subversion\/","url_meta":{"origin":2470,"position":4},"title":"How to – Update your Vicidial version with subversion","author":"carpenox","date":"February 4, 2021","format":false,"excerpt":"The version number shows in the top right corner. Updating Vicidial This is a pretty simple process. Only a few commands needs to be run in order to update your vicidial\/cyburdial version via the Linux Command Line Interface(CLI) cd \/usr\/src\/astguiclient svn checkout svn:\/\/svn.eflo.net:3690\/agc_2-X\/trunk cd trunk perl install.pl Then you may\u2026","rel":"","context":"With 2 comments","block_context":{"text":"With 2 comments","link":"https:\/\/dialer.one\/index.php\/how-to-update-your-vicidial-version-with-subversion\/#comments"},"img":{"alt_text":"SVN Update","src":"https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2021\/02\/versionupdate.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2021\/02\/versionupdate.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2021\/02\/versionupdate.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2021\/02\/versionupdate.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":2216,"url":"https:\/\/dialer.one\/index.php\/security-vulnerability-in-vicidial-codebase-update-now\/","url_meta":{"origin":2470,"position":5},"title":"Security vulnerability in Vicidial codebase – Update now!","author":"carpenox","date":"October 2, 2021","format":false,"excerpt":"Security vulnerability in Vicidial codebase - Update now! There is a major security hole in the vicidial codebase for any SVN version below 3509. Please see the email below. Vicidial exploit Please make sure you update your SVN and your OS, to update your SVN run these commands: cd \/usr\/src\/astguiclient\/trunksvn\u2026","rel":"","context":"Similar post","block_context":{"text":"Similar post","link":""},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2021\/10\/image.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2021\/10\/image.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2021\/10\/image.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dialer.one\/wp-content\/uploads\/2021\/10\/image.png?resize=700%2C400&ssl=1 2x"},"classes":[]}],"jetpack_likes_enabled":true,"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/dialer.one\/index.php\/wp-json\/wp\/v2\/posts\/2470","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dialer.one\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dialer.one\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dialer.one\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dialer.one\/index.php\/wp-json\/wp\/v2\/comments?post=2470"}],"version-history":[{"count":0,"href":"https:\/\/dialer.one\/index.php\/wp-json\/wp\/v2\/posts\/2470\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dialer.one\/index.php\/wp-json\/wp\/v2\/media\/2475"}],"wp:attachment":[{"href":"https:\/\/dialer.one\/index.php\/wp-json\/wp\/v2\/media?parent=2470"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dialer.one\/index.php\/wp-json\/wp\/v2\/categories?post=2470"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dialer.one\/index.php\/wp-json\/wp\/v2\/tags?post=2470"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}