vicidial.org

Hi,

I have the following build:
ViciBox_v11.x86_64-11.0.1.iso | Vicidial 2.14-892a Build 230726-0922 | Asterisk 16.30.0-vici | Single Server | No Digium/Sangoma Hardware | No Extra Software After Installation | Intel(R) Xeon(R) | CPU E3-1220 v3 @ 3.10GHz, 4 Core, 4 Thread, 8 GB RAM DDR3 1066/1333

I am having trouble with mydumper utliity stalling and then not backing up all the asterisk tables. It worked a week prior.

The only changes to the database are removing a user and adding one. But on changing the users, some potential problems were revealed. Here are my steps.

- It appears when we upgraded to ViciBox 11, the user [username] was removed from mysql.db but preserved on mysql.user , which killed our external database call script.
- I deleted [username] from mysql.user. DROP USER did not work since [username] was not in mysql.db

Next, I ran:

CREATE USER [username2]@[local ip] IDENTIFIED BY '[password]';
FLUSH PRIVILEGES;
GRANT SELECT ON asterisk TO [username2]@[local ip];
GRANT UPDATE ON asterisk.vicidial_users TO [username2]@[local ip];
GRANT UPDATE ON asterisk.phones TO [username2]@[local ip];

This allows me access from the local IP address to SELECT and batch update users and extensions, but when I run:

SELECT DISTINCT user FROM mysql.user;

...the user I just created, "[username2]", does not show. It does show on:

SELECT DISTINCT user FROM mysql.db;

And, this is the output I am getting after a mydumper database backup attempt, along with the file directory contents showing only two tables, which are corrupted.

vicibox11-tlc-onprem:~ # mydumper -t 4 -v 3 -c -o 20231006_asteriskDatabaseBackup --database asterisk
** Message: 18:53:31.010: MyDumper backup version: 0.15.1-3
** Message: 18:53:31.021: Connection via default library settings
** Message: 18:53:31.022: Connected to a MariaDB server
** Message: 18:53:31.022: Acquiring DDL lock
** Message: 18:53:31.023: Acquiring Global lock
** Message: 18:53:31.057: Started dump at: 2023-10-06 18:53:31
** Message: 18:53:31.057: conf created
** Message: 18:53:31.057: End job creation
** Message: 18:53:31.057: Waiting pid started
** Message: 18:53:31.057: Creating workers
** Message: 18:53:31.057: Starting Non-InnoDB tables
** Message: 18:53:31.058: Thread 1: connected using MySQL connection ID 2396
** Message: 18:53:31.058: Thread 3: connected using MySQL connection ID 2397
** Message: 18:53:31.058: Thread 2: connected using MySQL connection ID 2399
** Message: 18:53:31.058: Thread 4: connected using MySQL connection ID 2398
** Message: 18:53:31.058: Thread 2: Creating Jobs
** Message: 18:53:31.058: Thread 3: Creating Jobs
** Message: 18:53:31.058: Thread 3: dumping db information for `asterisk`
** Message: 18:53:31.059: Thread 1: Creating Jobs
** Message: 18:53:31.059: Thread 4: Creating Jobs
** Message: 18:53:31.059: Waiting database finish
** Message: 18:53:31.059: Written master status
** Message: 18:53:31.059: Multisource slave detected.

** (mydumper:7850): CRITICAL **: 02:13:17.853: Error showing tables on: asterisk - Could not execute query: Lost connection to MySQL server during query
** Message: 02:13:40.440: Thread 4: Schema queue
** Message: 02:13:55.804: Thread 3: Schema queue
** Message: 02:13:58.032: Shutdown jobs for less locking enqueued
** Message: 02:13:58.032: Thread 3: Schema Done, Starting Non-Innodb
** Message: 02:14:27.932: Thread 4: dumping schema create for `asterisk`
** Message: 02:13:55.804: Thread 2: Schema queue
** Message: 02:14:32.010: Thread 2: Schema Done, Starting Non-Innodb
** Message: 02:14:33.999: Non-InnoDB tables completed
** Message: 02:14:33.999: Starting InnoDB tables
** Message: 02:14:33.999: Thread 2: Non-Innodb Done, Starting Innodb
** Message: 02:14:33.999: Thread 3: Non-Innodb Done, Starting Innodb
** Message: 02:14:33.999: InnoDB tables completed
** Message: 02:14:34.017: Thread 1: Schema queue
** Message: 02:14:34.017: Thread 1: Schema Done, Starting Non-Innodb
** Message: 02:14:34.017: Thread 1: Non-Innodb Done, Starting Innodb

** (mydumper:7850): CRITICAL **: 02:14:51.472: Error dumping create database (asterisk): MySQL server has gone away
** Message: 02:14:51.472: Thread 4: Schema Done, Starting Non-Innodb
** Message: 02:14:51.472: Thread 4: Non-Innodb Done, Starting Innodb
** Message: 02:14:51.472: Non-InnoDB dump complete, releasing global locks
** Message: 02:14:51.485: Global locks released
** Message: 02:14:51.485: Thread 3: shutting down
** Message: 02:14:51.485: Thread 1: shutting down
** Message: 02:14:51.485: Thread 4: shutting down
** Message: 02:14:51.485: Thread 2: shutting down
** Message: 02:15:16.025: Waiting threads to complete
** Message: 02:15:16.052: Queue count: 0 0 0 0 0
** Message: 02:15:16.052: Main connection closed
** Message: 02:15:16.076: Finished dump at: 2023-10-07 02:15:16
You have new mail in /var/spool/mail/root
vicibox11-tlc-onprem:~ # ls
.asterisk_history .config .lesshst .ssh .vim .zypper.conf bin mbox
.bash_history .gnupg .mysql_history .subversion .viminfo 20231006_asteriskDatabaseBackup foo
You have new mail in /var/spool/mail/root
vicibox11-tlc-onprem:~ # cd 20231006_asteriskDatabaseBackup/
vicibox11-tlc-onprem:~/20231006_asteriskDatabaseBackup # ls
asterisk-schema-create.sql asterisk-schema-create.sql.gz metadata

--------------------------------------------------------------------------------------------------
Is there anything else I need to do to get rid of [username] in the database?

Why doesn't SELECT DISTINCT user FROM mysql.user; show the user I just created, "[username2]"?

Should I manually add [username2] to mysql.user with

INSERT INTO mysql.user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_prov, Super_priv,
Create_tmp_table_priv, Lock_tables_priv, Executive_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv, ssl_type, ssl_cipher, x509_issuer,
x509_subject, max_questions, max_updates, max_connections, max_user_connections, plugin, authentication_string, password_expired, is_role, default_role, max_statement_time)
VALUES ('[local ip]', 'reader', password('[password]'), 'Y', 'N', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', NULL, NULL, NULL, NULL, 0, 0, 0, 0, NULL, NULL, NULL, 'N', 'N', NULL, 0.000000)
);

?? This makes me nervous because if it's not tied to the mysql.db instance of the user "[username2]" and we GRANT future privileges, I doubt that the mysql.user table will update correctly since we hand-inserted the row.

Could this be causing our backup issue?

Or, is there a better backup program that works with OpenSUSE and MySQL?

Why not use the built in tool at /usr/share/astguiclient/ADMIN_backup.pl ?

carpenox wrote:Why not use the built in tool at /usr/share/astguiclient/ADMIN_backup.pl ?

I will try that. And, I'd also still like to know how to backup mysql tables when needed, and to know if my database has issues with the strange add and drop user behavior mentioned above.

mysqldump -ucron -p1234 asterisk table1 table2 > dumpname.sql

carpenox wrote:mysqldump -ucron -p1234 asterisk table1 table2 > dumpname.sql

Thank you! I will try that after hours tonight

That worked, thanks!

Np bro, anytime